topic Re: Create a generic job to access multiple tables in Talend Studio

Create a generic job to access multiple tables

Anonymous — Sun, 02 Dec 2018 18:27:49 GMT

Hi,

I have a use case where I have to access multiple tables from a database and create an output files.

Exp: below are 3 table that I would like to query from my job to extract data

loans, deposits,securities

the schema for above mentioned tables are different

Basically i want to create generic job(as i may need to extract data from other tables in future) where my select query can be controlled from a context variable or can be handled outside of the job with out hard coding the "select query" within the tDBcomponents in Talend

Also in my select query I would like to pass my table name and other key attributes through context during run time , to avoid hard coding with in the job.

exp: select * from sw.deposits where account_type='deposits'

sw.deposits & account_type should pass during run time.

Any suggestions.

Many thanks,

Brat

Re: Create a generic job to access multiple tables

Anonymous — Mon, 03 Dec 2018 09:25:46 GMT

Assuming that this is using the open source edition of Talend, you can do it this way....

1) Assign your SQL query to a context variable that is used in your DB component. This can be done by reading from a file or a database. This is totally up to you how you decide to store your query.

2) Create a schema in your DB component which is full of String columns. Create as many columns as you think may possibly be needed in your biggest table. They must all be String.

3) When writing your queries, ensure that you convert ALL columns to varchar in the queries.

After you have got this far, the rest is easy. You are simply writing to a flat file, so the data does not require a strict type and no transformation will be needed.

Re: Create a generic job to access multiple tables

vapukov — Mon, 03 Dec 2018 19:09:22 GMT

in addition to @rhall - even in subscription version it works properly only with string and integer

it possible to write dynamic code for parse universal structure, but generally must faster - create 3 separate jobs for each table, much faster and much stable

Re: Create a generic job to access multiple tables

Anonymous — Tue, 04 Dec 2018 16:30:48 GMT

Thanks Vapukov for your suggestion

Re: Create a generic job to access multiple tables

Anonymous — Tue, 04 Dec 2018 16:31:34 GMT

thank you rhall
Thanks,
Brat