One Star

select from multiple tables to export to multiple csv files

I did search the forum before posting this but was not able to find something quite helpful on exactly what I am trying to do. I wanted to check it others have any idea and if this can efficiently be achieved by using Talend. I am using TOS.
I have a list of tables that I need to archive some data from. So there are around 20 tables (MySQL) and I would need to select records that are older than 10 weeks and export them to csv files. I would then need to delete these records from the same 20 tables. So I started my POC with one table. I am using a tMysqlInput per table to do the select and able to export to csv successfully. I am then using a tMysqlRow component to delete from the same table.
However, to do this for 20 tables is quite tedious and then what it tomorrow I want to do this for another 5 tables?? I would need to create this subjob again for each tabled. Is there a better way to achieve this in talend?
Thanks
5 REPLIES
Seventeen Stars

Re: select from multiple tables to export to multiple csv files

One problem you have is the probably different schemas for every table. In the enterprise edition exists a solution for it called dynamic schema (the database component creates the schema at runtime based on the result set metadata and the file output component can handle this kind of schema. In such scenario you need to write one job for all because you need only the table name and nothing else.
One Star

Re: select from multiple tables to export to multiple csv files

Thanks for the quick reply. Yes, the schema is little different for each table, however, one thing common with all the tables is the "date_loaded" field. So I would be selecting all records based on this date field. Just adding this thought, in case that may help with some alternative.
Four Stars

Re: select from multiple tables to export to multiple csv files

Hi,
If metadata is different for all the tables, then you can't use a main flow for writing data into the csv file. Another approach is to go by mysql way to export data into the file..
- create a sample csv file with all the required queries with table name
- Iterate through each row to generate the file using tnysqlrow component
Using this way, you can add or remove as many rows as you need.
Thanks
Vaibhav
One Star

Re: select from multiple tables to export to multiple csv files

i want to make mutiple mysql table from multiple csv file according to their name. if table already exist drop that table or give an error. this work is in one job only .
One Star

Re: select from multiple tables to export to multiple csv files

how to solve problem of memory exceeded for large number of column in  file or table .
please help with some tutorial.
thanks.