topic Re: extract all tables from database in Talend Studio

extract all tables from database

houssem_B — Mon, 08 Apr 2019 14:40:49 GMT

Hello everyone

I have more than 70 tables in my database(Mysql), so I want to extract all of them in the same job.

I used to extract them with tDBInput (one component for each table) but since the number of tables is in growth I'm searching for an optimized solution.

Thanks

Re: extract all tables from database

Anonymous — Mon, 08 Apr 2019 15:32:31 GMT

Which product are you using (Talend Open Source or Talend paid for edition)? Where are you intending on sending the data?

Re: extract all tables from database

houssem_B — Mon, 08 Apr 2019 15:38:44 GMT

i'm using Talend Open Source

Re: extract all tables from database

Anonymous — Mon, 08 Apr 2019 15:46:00 GMT

What is the target of the data?

Re: extract all tables from database

houssem_B — Mon, 08 Apr 2019 16:54:30 GMT

also a Mysql database

tale.PNG

Re: extract all tables from database

Anonymous — Mon, 08 Apr 2019 17:43:41 GMT

You are going to struggle with this. You may as well just take a database backup and load it onto your target db.

Unfortunately you really cannot ignore schemas with Talend. The Enterprise Edition comes with dynamic schema functionality which would likely have helped here. But since you do not have that and you need to keep your column types (you can't arbitrarily dump out to Varchar....to a flat file), you will have to create a job for each table. However, this shouldn't take too long if you are mapping like to like with no transformation.

Re: extract all tables from database

houssem_B — Mon, 08 Apr 2019 17:50:10 GMT

okey then i'll go with the traditional solution

thanks a lot

Re: extract all tables from database

Anonymous — Tue, 09 Apr 2019 06:09:11 GMT

@houssem_B

My colleague @groupproductmanagement has created a good article to do dynamic ingestion for Oracle database. If you can make the changes to read from MySQL configuration tables instead of Oracle, you should be able to do the dynamic ingestion for your use case too.

https://community.talend.com/t5/Design-and-Development/Dynamic-ingestion-of-data-to-an-Oracle-database/ta-p/154657

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: extract all tables from database

houssem_B — Tue, 09 Apr 2019 09:00:42 GMT

@nthampi thank you very much i'll try it now and see if it works

Re: extract all tables from database

Anonymous — Tue, 09 Apr 2019 10:42:42 GMT

Cool solution @nthampi, but this will still require the data to be retrieved from the source system before it is loaded into the target. By the time the jobs have been written to export the data from the multiple tables, they may as well simply push it to the target db.

Re: extract all tables from database

Anonymous — Tue, 09 Apr 2019 10:45:46 GMT

I agree with you @rhall 🙂

It was a solution created for one of the customer. Since it might be interesting, we converted it to a KB article.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: extract all tables from database

akumar2301 — Tue, 09 Apr 2019 13:12:07 GMT

To make it dynamically ,

select column details for each table dynamically with table INFORMATION_SCHEMA and with these information

create select statement which returns concatenation of all columns.

E.g if table "test" has column id and name

Creare you sql sentence like
Select concat_ws(‘;’,id,name) from test

Create this sql for each table in iteration and for each sql statement run tdbinput having schema column name “allcolumn”.

Now tfileoutputdelimited and dump result. You will have all tables in separate file with columns semicolon separated.

https://stackoverflow.com/questions/23348170/mysql-concatenating-all-columns