tMap lookup escaping duplicated rows

[Anonymous]

Hello,

I'm creating a job that will read a file that contain some information to be inserted in a fact table (star schema db architecture). To insert a record in a fact table, first I need to get a lot of Primary Keys from dimension tables to insert in fact table. The question is if exist some way to temporary store the records duplicated to not call the select many times as the number of records in the file.

For example, using the screenshot below, I read a file that contains 5000 records but only 3 different banks, so I don't want to call the select statement from get_bank 5000 times, I only want to call it 3 times.

Anyone knows how can I perform this?

Thank you!

[alang1]

I'm new here, but had a similar question once.  I found this topic (https://help.talend.com/reader/QrWkwPvKmKybs~JNLeBt3Q/0cxOOexBaPZxESQY8ucsug) on Handling Lookups.  But honestly, I thought the document was lacking detail explaining how each option behaves.  I'm pretty sure I tried them, but didn't trust that they were working as expected.  I'm sorry to say that I don't remember how I ended up.  In any case, I thought I would point it out in case it helps.  Here is a snip from that page.  As it states at the bottom, maybe there is more info under the tMap reference.

[Anonymous]

Thank you for the quick reply @alang,

Yeah, this will work... but to use this feature, I need to perform a select query without a where clause to bring all data inside the table that I'm using on the lookup. With a large amount of data, I think this will cause a JVM Heap Memory error. For now the table is small, but in the future this will have more than 10 million records.

Anyone has another answer?

Luiz Ramos

[Anonymous · Accepted solution]

After some reading in the Talend documentation, I found that the tMap already do this for us. Is only need to select the option "Reload at each row (cache)" in the tMap lookup settings.

For more information please see: https://help.talend.com/reader/iUlm0MDXr5kWsTan1oPozg/C9gANC9ZnbIrGSvLsc~qbA

Luiz Ramos