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
Community Manager

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.

Forteen 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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog