topic Re: Gather values to create a single lookup query? in Talend Studio

Gather values to create a single lookup query?

Anonymous — Mon, 05 Nov 2012 12:46:24 GMT

I am needing to bash some data from a local MySQL db against our enterprise teradata. I'd like to do this without having to cache a multi-million record table from the teradata warehouse to do the lookups. Is there a component or method I can use to get my key fields from my local db and do a single query lookup in the other db?

An example of this would be
1. Get account id's from MySQL
2. List id's in query for teradata as "WHERE account_id IN ('id1', 'id2', 'id3')"
3. join rows from both db in output
Are there components that can accomplish this?

Re: Gather values to create a single lookup query?

Anonymous — Mon, 05 Nov 2012 18:19:57 GMT

Hi
tMap is the component you are looking for? Do an join on tMap on mysql.id and account_id.
Shong

Re: Gather values to create a single lookup query?

Anonymous — Mon, 05 Nov 2012 18:27:45 GMT

Shong,
I've used tMap and it loads the entire table before performing the look up. I can't use it quite like that, the lookup table will have more than 17 million rows, for the query in use, it would take several minutes (and a whole lot of disk space).
I need to filter in the lookup table by making a derived table (using the id column in the mysql table). Essentially, I need some way to use the id column from mysql as a parameter the job can insert into the teradata query as an "IN ('value1','value2','value3') format, that i can then join with tmap

Re: Gather values to create a single lookup query?

Anonymous — Tue, 06 Nov 2012 16:21:13 GMT

I think what I need for my query is use Context variables.
Is there a tutorial for using them, or can someone show me an example of how to use context variables to make a WHERE clause that generates an IN list?