Loading multiple table from source into multiple table into target in Talend

Six Stars

Loading multiple table from source into multiple table into target in Talend

I have around 25 tables to load to target with same structure & uses same
logic for loading. I have prepared one job which does that, but it's a
long process to design all the table. Is there any way to pass the table
name and load to target, basically a small job(in size).

 

I am using Talend open studio & mysqlDB.

 

Any help & suggestion is truly appreciated 

Community Manager

Re: Loadind multiple table from source into multiple table into target in Talend

Are you talking about 25 tables in the same database which are all of the same structure, but have different table names? Are these tables your sources? Are you loading them all into one table as your target? If so you can do this quite easily. You will need a list of your source table names in a database table or a flat file (your choice) and you will need to read that into a Talend job. Connect a tFlowToIterate to that source component. Then connect an iterate link between the tFlowToIterate component and your source db component. If your list of table names is returned to the tFlowToIterate in a column called "tablename" and the row connecting to the tFlowToIterate is called "row1", then you can reference your table name in your db component's SQL query like below.....

 

"SELECT
Column1,
Column2,
Column3,
Column4
FROM " + ((String)globalMap.get("row1.tablename")) + "
WHERE......"

For every iteration of the tFlowToIterate, you will retrieve every row from the tablename provided to that query.

 

Hope that helps

 

 

Six Stars

Re: Loading multiple table from source into multiple table into target in Talend

Thanks for the reply, I asked for something like below.

server 1 db1 table1 >>>> server 2 db1 table1

server 1 db1 table2 >>>> server 2 db1 table2

.......

 

server 1 db1 table25 >>>> server 2 db1 table25

 

Basically, both table in the DB for both server as same structure, just doing a tmap and sending to target.

 

I have just attached the image for example purpose, like this I have 25 tables. 

Community Manager

Re: Loadind multiple table from source into multiple table into target in Talend

The way I suggested will still work. You will just have to use the globalMap variable I used in the SQL query as the "Table Name" parameter in your database output table.

Six Stars

Re: Loadind multiple table from source into multiple table into target in Talend

Would you mind to just provide the layout of the job design.

 

Thanks a lot

Community Manager

Re: Loadind multiple table from source into multiple table into target in Talend

A rough layout would look like below.....

demolayout.png

 

You can replace the tFileInputDelimited_1 with a database component holding your list of table names if you want. That isn't important. It is the tFlowToIterate and the globalMap variable usage in the db components that is key.

Two Stars

Re: Loadind multiple table from source into multiple table into target in Talend

Please can you help in step by step method, i am using global variable here but not getting the data from database. 

I am iterating the list of tables and trying to push data into mysql DB 

Thanks in advance!

 

Two Stars

Re: Loadind multiple table from source into multiple table into target in Talend

Can we use this for tables having different schema from MSSQL to MYSQL

Please let me know 

Thanks 

Highlighted
Community Manager

Re: Loadind multiple table from source into multiple table into target in Talend

There is a limit as to what can be done with a single job in Talend. You cannot infer schema changes within the same job unless you are using the subscription version of the product. This gives you access to the Dynamic Schema. I talk about a method of using that here:

https://www.talend.com/blog/2019/11/11/migrate-data-between-databases-one-job-using-dynamic-schema/

2019 GARTNER 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

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