Enrich CSV file with data from MySQL

Highlighted
One Star

Enrich CSV file with data from MySQL

Hi,
I'm quite a noob at Talend. I just wanted to enrich a csv file with data from a (rather big) db. I wish I could iterate row by row, execute a select on db an SQL request, and then fetch the few data I miss for each row.
How can I do that ?
Thanks for help.
Regards,
Yann
One Star

Re: Enrich CSV file with data from MySQL

tFileInputDelimited -> tMap -> tFileOutputDelimited
^
|
tMysqlInput
Use tFileInputDelimited to read in the CSV file. This becomes the main flow into a tMap. Use a tmysqlInput to read the data from the mySql DB. This is called a "lookup" flow. Inside the tMap, use the key fields from the mainflow to join to the mySql lookup flow. In the output from the tMap, coy the fields from the main flow, and any new fields from the lookup flow.
READ THE MANUAL for details of how to use a tMap. There is an excellent description of how to do exactly what you are trying to do, including examples.
One Star

Re: Enrich CSV file with data from MySQL

Hi Wayne,
Thanks for your help. I just can figure out how not to load the whole DB before injecting to tMap (and thus generating an out of memory error, for it is a 20 Gb DB)...
I might have a solution using iterator, but I cant' find out how I could do that (iterate on csv rows, requesting mySQL, merging row, writing to delimited file).
One Star

Re: Enrich CSV file with data from MySQL

Hi,
Just figured it out :
tInputFileDelimited -----row1-----> tFlowToIterate ------------> tMySQLInput ---out1---> tMap --out2----> tOutputFileDelimited
(tMySQLIput uses row1 entry as input for sql request)
(tMap merges row1 + out1)
tOutputfileDemilimited is append of course

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog