One Star eve
One Star

Inner Join Reject in a tMap

Hi,
I have 2 inner join in my tMap.
I would like to distinguish both types of rejection.
In the documentation there is an example with Perl : add a filter line on the Inner Rejection output => not defined $Owners_data.
My project is in java (TOS 2.4.0).
What is the expression in java for the filter line?
Tags (1)
7 REPLIES

Re: Inner Join Reject in a tMap

Any updates on this ? I am also facing same issue.
Anuradha
Community Manager

Re: Inner Join Reject in a tMap

Hello
Please see my screenshot.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Inner Join Reject in a tMap

Thanks for the reply shong.
Actually I had done all this .
But my query is how to divide inner join rejection output to multiple outputs when using multiple lookups.
I need to load a staging table verify its foreign key constraints, reject records for which foreign key doesnot exists and log the error records with reason.

Currently I have an Input Table CUSTOMERS_STG and this is mapped with to Lookup tables GENDER and AGE_GROUP. I had placed Inner Joins. And also clicked on InnerJoinReject by clicking the purple arrow. It generates rejected records for Inner Join Rejection Output. But I want to divide the Inner Rejection output into two outputs so that I can define reason for rejection for each output.
For doing the same in TALEND user guide an example has been given but it applies to Perl.
As per the example For GENDER_ID rejection we need to place a filter in GENDER rejection output as
=> not defined $GENDER_data and
Similary for AGE_GROUP join rejection we need to place a filter in AGE_GROUP rejection output as
=> not defined $AGE_GROUP_data
But this applies to Perl. I had created a Java Project so whats the equivalent for the same in Java Projects.
I had searched for the same on the forum but could not find anything.
Thanks & Regds
Anuradha
Employee

Re: Inner Join Reject in a tMap

Hello,

You can't do this with tMap reject process and inner join option : Lookups are processing step by step. When the first one not macth, the second is not processed ! you haven't all mismatch

To acheve your goal ( in java ) :
1) uncheck inner join for lookup and disable lookup inner join reject.
2) enable all expression filter for all output :
add a filter like "GENDER_ID==null" for all lookup output ( to select only reject with GENDER, use the key column as condition )
add a filter in main output with negation of all lookup condition : !(GENDER_ID==null||AGE_GROUP_ID==null .... )
=> you have a output validate, and all reject record by foreing key.

Re: Inner Join Reject in a tMap

Can I do this by using multiple tMap each doing Inner Join with just 1 Lookup table and resulting in 1 reject output table for each tMap?
Like 1 tMap doing join with GENDER table then after this another tMap for Age_Group and so on for all Lookups required.
CUSTOMER_STG-->tMap1 with Lookup GENDER-->tMap2 with Lookup Age_GROUP-->Target Table Load
Here tMap1 will have its own rejection output and tMap2 will have its own rejection output.
Will this effect performance??
Employee

Re: Inner Join Reject in a tMap

Yes, you can use more one tMap !
Performance : more java code but not significant with exec time

Re: Inner Join Reject in a tMap

Hi,
I tried applying the solution with mutiple tMaps to validate each foreign key and update each rejected record with a valid reason, but as soon as some rejection comes due to any lookup, entire flow becomes slow.
what is happenings is like for example i have following flow.

tMySQLInput2 tMySQLInput3
(Gender Lookuptable) (AddressLookuptable)
| |
t_MySQLInp1(CustomerStg)-->tMap4------>tMap5-->tUniqueRow-->tMySQLOutpu6-Customer
| |
Rejects Rejects
| |
tMySQLOutput7 tMySQLOutput8
(Update CustomerStg (Update CustomerStg Table)
table)
Now here
t_MySQLInp1-- Reads data from CustomerStg table in MySQL
tMySQLInput2--Reads data from Gender table
tMySQLInput3--Reads data from Address table
tMap4-- Performs a lookup between Customer Stg and Gender table and moves reject records to tMySQLOutput7 which updates CustomerStg with status 0 and desc as Gender not found. And all correct records are passed to tMap5 for further lookup
tMap5-- Performs a lookup between dataset received from tMap4 and Address table and moves reject records to tMySQLOutput8 which updates CustomerStg with status 0 and desc as Address not found. And all correct records are passed to tUniqRow for further validation
tUniqRow-- Finds unique records and load them to target CUSTOMER table with tMySQLOutput6
tMySQLOutput6--Loads data to target CUSTOMER table.

Now if some records are read from CustomerStg which are having Invalid Gender Id then the speed with it was processing rows/s gets reduced drastically. Sometimes from say 150row/s it comes to 4 row/s.
Can you please suggest the reson?
If this is not the correct way of doing such Foreign Key/Unique record checking then please suggest some other way of implementing the same.
Or would appreciate if you could elaborate a bit more on the solution provided earlier:
*********************************************************************************************
Hello,

You can't do this with tMap reject process and inner join option : Lookups are processing step by step. When the first one not macth, the second is not processed ! you haven't all mismatch

To acheve your goal ( in java ) :
1) uncheck inner join for lookup and disable lookup inner join reject.
2) enable all expression filter for all output :
add a filter like "GENDER_ID==null" for all lookup output ( to select only reject with GENDER, use the key column as condition )
add a filter in main output with negation of all lookup condition : !(GENDER_ID==null||AGE_GROUP_ID==null .... )
=> you have a output validate, and all reject record by foreing key.
*********************************************************************************************
We are doing data migration and such foreign key and unique record checking is to be done for all tables to be loaded to MySQL.
So we are trying to implement a optimized solution easy to implement and maintain.
Would really appreciate if you guys can help us out.

Thanks & Regds,
Anuradha.