Create a generic job to access multiple tables

Four Stars

Create a generic job to access multiple tables

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

Highlighted
Sixteen Stars

Re: Create a generic job to access multiple tables

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.

Thirteen Stars

Re: Create a generic job to access multiple tables

in addition to @rhall_2_0 - 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

 

-----------
Four Stars

Re: Create a generic job to access multiple tables

Thanks Vapukov for your suggestion

Four Stars

Re: Create a generic job to access multiple tables

thank you rhall
Thanks,
Brat

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.