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
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
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.
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.
A rough layout would look like below.....
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.
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!
Can we use this for tables having different schema from MSSQL to MYSQL
Please let me know
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:
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema
Pick up some tips and tricks with Context Variables