how would you use tMap to handle intersection 3 parts (left only, inner, right only) with 3 corresponding distinct actions?
Hello all,
I was not sure how to title this post. Let me try to express the context and the expected behaviour.
Suppose we have 2 databases DB1 and DB2, we have a table DB1.src_tbl and DB2.target_tbl, in each table a primary key, lets call it "PK" in both.
the DB1.src table may see new rows, and disappearing rows over time (no update)
the DB2.target table is the result of some talend process, keeping track on a asynch schedule of the elements from DB1.src matching some criteria.
so once the talend job executes, some DB1.src.PK are copied over to the DB2.target.PK along with some data.
at some point between the execution of the talend job the DB1.src, can see new rows appearing, or existing rows disappear, but existing rows are never updated.
When the talend job executes its expected to insert in the DB2.target table the new PK that exist in DB1.src table but not yet in DB2.target.
Also it's expected that exiting PK in DB2.target missing from DB1.src be removed.
the 3rd case is when the same PK exist in both DB1.src and DB2.target, then talend job doesn't touch the DB2.target table for that row.
To summarise the behaviour it'd look like this at any given point in time before Talend Job doing the alignment kicks in.
DB1.src.attr1
DB1.src.attr2
DB1.src.PK
DB2.target.PK
DB2.target.attr3
Talend action on DB2.target
133
abc
1
add
132
def
2
add
534
ghk
3
3
xyz
keep
4
yua
delete
11
opq
5
5
dgf
keep
6
kjy
delete
I was looking at tMap to help me perfom the filtering logic adding missing entries in DB2.target that exist in DB1.src, along with removing existing rows from DB2.target that no longer have matching PK key value in DB1.src
But as of now, i can't figure out how to do that.
I'm seeking here for help in finding appropriate approach, and hope this use case can help others facing similar issues
Hi
If i understand your requirement well, you need to do inner join and and get the unmatched rows two times, the first time:
DB2.target.PK--main--tMap---
|lookup
DB2.SRC.PK
Do an inner join based on PK and get the unmatched PK, these PK do not exists in DB2.SRC, will be deleted in DB2.target.
Refer to this article to learn how to do an inner join and get unmatched rows on tMap.
https://community.talend.com/t5/Design-and-Development/Doing-an-inner-join-using-a-tMap-component/ta...
The second time
DB2.SRC.PK--main--tMap---
|lookup
DB2.SRC.PK
Do an inner join based on PK, get the unmatched rows, these PK do not exist in DB2 target and will be inserted into DB2 target.
