topic tMap multiple lookups best design in Talend Studio

tMap multiple lookups best design

Anonymous — Sat, 16 Nov 2024 13:47:51 GMT

Scenario:
Process incomming fact rows from an (main) input (tOracleInput, for example) with several lookups (lookup links), using a tMap
Issues:
One or more lookups may be unsuccessful.
checking the "inner join" on lookups causes subsequent lookups in the inputs to be skipped (not good)
Desired design:
Output records in which ALL lookups are successful to 1 output (output_success)
Output records in which one OR MORE lookups are unsuccessful, ouput to a separate output (output_rejects). ALL lookups need to be attemped in the tMap
In a subsequent component(s), resolve any NULL keys in the output_rejects to "default" keys (via "default lookups") and then output to output_resolved
Merge output_success with output_resolved, and then tOuput to database

Alternate design:
Output all records to output_1, regardless of failure of any one lookup
In a subsequent component(s), resolve any NULL keys in the output_1 to "default" keys (via "default lookups") and then re-output

Is there a best practice on this in consideration of the "inner join" issue described above?
Dave

Re: tMap multiple lookups best design

Anonymous — Thu, 10 Sep 2009 08:14:09 GMT

Hi,
Instead of using tMap for lookup I use tJoin for for all the lookups and place the rejected records in log file.
For making a better and uniform suructure along with time value in the log file I use tMap for the rejected records and place the records in log file
I feel it is better for debugging, maintaining a job.
Thanks and Regards,
pravu Mishra.

Re: tMap multiple lookups best design

Anonymous — Thu, 10 Sep 2009 17:17:10 GMT

Hi,
Instead of using tMap for lookup I use tJoin for for all the lookups and place the rejected records in log file.
For making a better and uniform suructure along with time value in the log file I use tMap for the rejected records and place the records in log file
I feel it is better for debugging, maintaining a job.
Thanks and Regards,
pravu Mishra.

What my goal is, is to process all rows in one place, for which one OR MORE "lookups" have failed upstream. Maybe, I can link a series of tJoins together (one for each lookup). If a lookup fails, I would place a key value of -1 (tag as a logical failure) in the lookup key. However, I would want ALL rows to continue to the next tJoin (as if successful), and so on. Finally, after the last tJoin, I could usesomething like a tJavaRow to test for any -1 values that exist in any key columns. I would then send those rows to a tMap which does lookups for DEFAULT vaules. I would then want to merge back all corrected rows (with the default values substituted) back in the main stream (of success rows), which woud go to a final tmap for output.
How does that sound?

Re: tMap multiple lookups best design

Anonymous — Fri, 11 Sep 2009 06:47:37 GMT

It sounds good.

If a lookup fails, I would place If a lookup fails, I would place a key value of -1 (tag as a logical failure) in the lookup key. However, I would want ALL rows to continue to the next tJoin (as if successful), and so on. Finally, after the last tJoin, I could usesomething like a tJavaRow to test for any -1 values that exist in any key columns. I would then send those rows to a tMap which does lookups for DEFAULT vaules. I would then want to merge back all corrected rows (with the default values substituted) back in the main stream (of success rows), which woud go to a final tmap for output.
in the lookup key. However, I would want ALL rows to continue to the next tJoin (as if successful), and so on. Finally, after the last tJoin, I could usesomething like a tJavaRow to test for any -1 values that exist in any key columns. I would then send those rows to a tMap which does lookups for DEFAULT vaules. I would then want to merge back all corrected rows (with the default values substituted) back in the main stream (of success rows), which woud go to a final tmap for output.

do not have any idea about the mentioned things by you as these are related to your data and the way you need to process those.
We may use the rejected option of tJoin for storing the rejected records(lookup fails mentioned by you) into a log file or do some thing( process again for some other condition).
can you send a screen shot of your job, so that I can understand better about your job.
Thanks and Regards,
Pravu Mishra.

Re: tMap multiple lookups best design

Anonymous — Fri, 11 Sep 2009 15:44:58 GMT

It sounds good.
If a lookup fails, I would place If a lookup fails, I would place a key value of -1 (tag as a logical failure) in the lookup key. However, I would want ALL rows to continue to the next tJoin (as if successful), and so on. Finally, after the last tJoin, I could usesomething like a tJavaRow to test for any -1 values that exist in any key columns. I would then send those rows to a tMap which does lookups for DEFAULT vaules. I would then want to merge back all corrected rows (with the default values substituted) back in the main stream (of success rows), which woud go to a final tmap for output.
in the lookup key. However, I would want ALL rows to continue to the next tJoin (as if successful), and so on. Finally, after the last tJoin, I could usesomething like a tJavaRow to test for any -1 values that exist in any key columns. I would then send those rows to a tMap which does lookups for DEFAULT vaules. I would then want to merge back all corrected rows (with the default values substituted) back in the main stream (of success rows), which woud go to a final tmap for output.