Gather values to create a single lookup query?

One Star

Gather values to create a single lookup query?

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

Re: Gather values to create a single lookup query?

Hi
tMap is the component you are looking for? Do an join on tMap on mysql.id and account_id.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Gather values to create a single lookup query?

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
One Star

Re: Gather values to create a single lookup query?

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?