book

Talend Open Studio Cookbook

Name: Talend Open Studio Cookbook
Author: Rick Barton
ISBN: 9781782167266

by Rick Barton

October 2013

Beginner to intermediate

270 pages

5h 41m

English

Packt Publishing

Read now

Unlock full access

Talend Open Studio Cookbook
Table of Contents
Talend Open Studio Cookbook
Credits
About the Author
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers and moreWhy Subscribe?Free Access for Packt account holders
Preface
What this book covers
What you need for this book
Who this book is for

Conventions
Reader feedback
Customer support
Downloading the example codeErrataPiracyQuestions
1. Introduction and General Principles
Before you begin
Installing the software
How to do it…
Enabling tHashInput and tHashOutput
How to do it…
2. Metadata and Schemas
IntroductionSchema metadataSchemasRepository schemasGeneric schemasShared schemasGenerated data sourcesFixed schemas and columns
Hand-cranking a built-in schema
Getting readyHow to do it…How it works…There’s more...Date patternsNullable elements
Propagating schema changes
Getting readyHow to do it…How it works…There’s more…
Creating a generic schema from the existing metadata
How to do it…How it works…
Cutting and pasting schema information
Getting readyHow to do it…How it works…There’s more…
Dropping schemas to empty components
Getting readyHow to do it…How it works…There’s more…
Creating schemas from lists
Getting readyHow to do it...How it works…There’s more…
3. Validating Data
Introduction
Enabling and disabling reject flows
Getting readyHow to do it…How it works…There's more...See also
Gathering all rejects prior to killing a job
Getting readyHow to do it…How it works…There's more...See also
Validating against the schema
Getting readyHow to do it…How it works…
Rejecting rows using tMap
Getting readyHow to do it…How it works…There's more…
Checking a column against a list of allowed values
Getting readyHow to do it…How it works…There's more…See also
Checking a column against a lookup
Getting readyHow to do it…How it works…
Creating validation rules for more complex requirements
Getting readyHow to do it…How it works…There's more…See also
Creating binary error codes to store multiple test results
Getting readyHow to do it…How it works…There's more…Decrypting the error code
4. Mapping Data
IntroductionThe tMap componentSingle line of codeBatch versus real time
Simple mapping and tMap time savers
Getting readyHow to do it...How it works...There's more…
Creating tMap expressions
Getting readyHow to do it...How it works...There's more…Testing expressionsExpression editorGetting around the 'one line' limitationSee Also
Using the ternary operator for conditional logic
Getting readyHow to do it...Single ternary expression: if-then-elseTernary in ternary: if-then-elsif-then-elseHow it works…There's more…
Using intermediate variables in tMap
Getting readyHow to do it…How it works…There's more…
Filtering input rows
Getting readyHow to do it...How it works…There's more…
Splitting an input row into multiple outputs based on input conditions
Getting readyHow to do it...How it works…There's more…
Joining data using tMap
Getting readyHow to do it...How it works…There's more…See Also
Hierarchical joins using tMap
Getting readyHow to do it...How it works…
Using reload at each row to process real-time / near real-time data
Getting readyHow to do it...How it works…Loading the data into memoryThe globalMap keyThe WHERE clauseThe resultThere's more…
5. Using Java in Talend
Introduction
Performing one-off pieces of logic using tJava
Getting readyHow to do it…How it works…See also
Setting the context and globalMap variables using tJava
Getting readyHow to do it…How it works…There's more…See also
Adding complex logic into a flow using tJavaRow
Getting readyHow to do it…How it works…
Creating pseudo components using tJavaFlex
Getting readyHow to do it…How it works…There's more…
Creating custom functions using code routines
Getting readyHow to do it…How it works…There's more…See also
Importing JAR files to allow use of external Java classes
Getting readyHow to do it…How it works…There's more…
6. Managing Context Variables
IntroductionTransportable codeContext variablesCommon values in contextsPassing command line parametersSetting context variables in the codeDatabase context variables
Creating a context group
How to do it...How it works...There’s more…Context typesPrompt for variable values using the tree mode
Adding a context group to your job
Getting readyHow to do it...How it works…There’s more…
Adding contexts to a context group
Getting readyHow to do it...There’s more…
Using tContextLoad to load contexts
Getting readyHow to do it...How it works...There’s more…Print operationsWarningsContext file location
Using implicit context loading to load contexts
Getting readyHow to do it...How it works...There’s more…
Turning implicit context loading on and off in a job
Getting readyHow to do it...How it works...
Setting the context file location in the operating system
Getting readyHow to do it...How it works…There’s more…Variable not presentImplicit context load
7. Working with Databases
Introduction
Setting up a database connection
Getting readyHow to do it...How it works...There's more…Using the connectionAlways create database connectionsConnection namesContext
Importing the table schemas
Getting readyHow to do it…How it works...There's more…
Reading from database tables
Getting readyHow to do it…Selected rows and columnsMultiple tables and complex queriesHow it works…There's more…Efficiency versus readabilitySQL stringSQL style
Using context and globalMap variables in SQL queries
Getting readyHow to do it…How it works…There's more…The globalMap variablesDeveloping the queryReloading at each row
Printing your input query
Getting readyHow to do it…How it works…There's more…
Writing to a database table
Getting readyHow to do it…How it works…There's more…Creating tablesUpdate and delete keysBatchesBulk loadingBulk loading to temp table
Printing your output query
Getting readyHow to do it…How it works…There's more…
Managing database sessions
Getting readyHow to do it…How it works…ExecutionsThere's more…Multiple outputsDon't forget the commitCommitting but not closing
Passing a session to a child job
Getting readyHow to do it…How it works…
Selecting different fields and keys for insert, update, and delete
Getting readyHow to do it…How it works…There's more...UpdatingDeleting
Capturing individual rejects and errors
Getting readyHow to do it…How it works…There's more…Die on errorEfficiencyError management
Database and table management
Getting readyHow to do it…How it works…There's more…
Managing surrogate keys for parent and child tables
Getting readyHow to do it…How it works…There's more...Added efficiency using hashMap key tableRangesSequencesAuto increment keysThe LastInsertId componentAuto increment procedure
Rewritable lookups using an in-process database
BackgroundGetting readyHow to do it…How it works…In-memory componentsInitialize the datatMapWrite backThere's more…MemorySee also
8. Managing Files
Introduction
Appending records to a file
Getting readyHow to do it...How it works...There's more…Concatenating files using the append method
Reading rows using a regular expression
Getting readyHow to do it...How it works...There's more…
Using temporary files
Getting readyHow to do it...How it works...There's more…See also
Storing intermediate data in the memory using tHashMap
Getting readyHow to do it...How it works...There's more…
Reading headers and trailers using tMap
Getting readyHow to do it...How it works...There's more…
Reading headers and trailers with no identifiers
Getting readyHow to do it...How it works...
Using the information in the header and trailer
Getting readyHow to do it...Validation subjobUse the header information subjobHow it works...Validating using the trailer informationUsing the header information in the detailThere's more…
Adding a header and trailer to a file
Getting readyHow to do it...How it works...There's more…See also
Moving, copying, renaming, and deleting files and folders
Getting readyHow to do it...Copying a file to another directoryCopying file to a different nameRenaming a fileMoving a fileDeleting a fileHow it works...There's more…
Capturing file information
Getting readyHow to do it...How it works...There's more…
Processing multiple files at once
Getting readyHow to do it...How it works...There's more…
Processing control/validation files
Getting readyHow to do it...How it works...There's more…
Creating and writing files depending on the input data
Getting readyHow to do it...How it works...tJavaRow code explainedThere's more…
9. Working with XML, Queues, and Web Services
Introduction
Using tXMLMap to read XML
Getting readyHow to do it...How it works...There's more…Document objectsXML Structure
Using tXMLMap to create an XML document
Getting readyHow to do it...How it works...There's more…
Reading complex hierarchical XML
Getting readyHow to do it...How it works...There's more…Managing the relationshipsFile informationXML to database mappingXPATHWeb service XML
Writing complex XML
Understanding the XML structureNodeMethodJava DOMGetting readyHow to do it...How it works...So here we go…tWriteXMLFieldCode utilitiestFlowToIteratetHash componentsXPATH ConditionPutting it all togetherThere's more…Job "shape"
Calling a SOAP web service
Getting ready...How to do it...How it works...There’s more…Decoding the responseUsing web service calls in-flow
Calling a RESTful web service
Getting readyHow to do it...How it works...There's more…
Reading and writing to a queue
Getting readyHow to do it...How it works...There's more…
Ensuring lossless queues using sessions
Getting readyHow to do it...How it works...There's more…
10. Debugging, Logging, and Testing
IntroductionDebuggingLoggingTesting
Find the location of compilation errors using the Problems tab
Getting readyHow to do it...How it works...There's more…
Locating execution errors from the console output
Getting readyHow to do it...How it works...There's more…See also
Using the Talend debug mode – row-by-row execution
Getting readyHow to do it...How it works...There's more…See also
Using the Java debugger to debug Talend jobs
Getting readyHow to do it...How it works...There's more…
Using tLogRow to show data in a row
Getting readyHow to do it...How it works...There's more…
Using tJavaRow to display row information
Getting readyHow to do it...How it works...There's more…
Using tJava to display status messages and variables
Getting readyHow to do it...How it works...
Printing out the context
Getting readyHow to do it...How it works...There's more…
Dumping the console output to a file from within a job
Getting readyHow to do it...How it works...There's more…
Creating simple test data using tRowGenerator
Getting readyHow to do it...How it works...There's more…
Creating complex test data using tRowGenerator, tFlowToIterate, tMap, and sequences
Getting readyHow to do it...How it works...There's more…
Creating random test data using lookups
Getting readyHow to do it...How it works...There's more…
Creating test data using Excel
Getting readyHow to do it...How it works...There's more…
Testing logic – the most-used pattern
Getting readyHow to do it...How it works...There's more…
Killing a job from within tJavaRow
Getting readyHow to do it...How it works...
11. Deploying and Scheduling Talend Code
IntroductionContext VariablesExecutable codeManaging job dependencies within Talend
Creating compiled executables
How to do it...How it works…
Using a different context
Getting readyHow to do it…How it works…There's more…
Adding command-line context parameters
Getting readyHow to do it…How it works…There's more…
Managing job dependencies
Getting readyHow to do it…How it works…There's more…Die on errorAdding error checks to the scheduleRestartability
Capturing and acting on different return codes
Getting readyHow to do it…How it works…There's more…
Returning codes from a child job without tDie
Getting readyHow to do it…How it works…There's more…
Passing parameters to a child job
Getting readyHow to do it…How it works…There's more
Executing non-Talend objects and operating system commands
Getting readyHow to do it…How it works…There's more…
12. Common Mistakes and Other Useful Hints and Tips
Introduction
My tab is missing
How to do it…Show view:Reset the perspective
Finding the code routine
How to do it…
Finding a new context variable
How to do it…
Reloads going missing at each row global variable
How to do it...
Dragging component globalMap variables
Some complex date formats
Capturing tMap rejects
Adding job name, project name, and other job specific information
Printing tMap variables
Stopping memory errors in Talend
Increasing the memory allocated to a jobReducing lookup dataUsing hashMap/in-memory tablesSplitting the jobDropping data to diskSplit the filesHardware solutions
A. Common Type Conversions
B. Management of Contexts
Introduction
Manipulating contexts in Talend Open Studio
Understanding implicit context loading
Understanding tContextLoad
Manually checking and setting contexts
Index

Content preview from Talend Open Studio Cookbook

Using context and globalMap variables in SQL queries

It is possible to use parameters passed or created in a job to drive the results of a SQL query. This short recipe shows how.

Getting ready

Open the job jo_cook_ch07_0020_contextInQuery, which is a copy of the job from the previous recipe.

How to do it…

The steps to be performed are as follows:

Open the tMysqlInput component.
Change the final line to HAVING SUM(o.`orderTotal`) > "+context.minOrderValue.
Run the job and you will be prompted for a value.
Enter 10 and click on Ok.
You should see that the result set contains orders where the sum of the lines is > 10.

How it works…

The SQL statement used by tMysqlInput is held in a string, so can be manipulated just as any other string in Java. Thus, we can ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781782167266

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills