Multiple tables with different schemas to one table
I have searched the forum and I haven't found a solution the following scenario.
I have 3 or more tables with a lot of overlapping columns but the actual schema is different for each one.
One of the three tables might contain new rows, so left join won't work I need something more like a union.
With an output with all the columns and I also want set some column update priority, something like
row1.status != null? row1.status : row2.status != null? row2.status : row3.status
for example
customer_csv
id,fist_name, status
1,"john", "active"
2,"mike", "inactive"
3,"bryan", "active"
customer_api
id, status
1, "inactive"
3, "active"
4, "inactive"
try thinking about as SQL left joins the wording that you have used to define your requirement is wrong. could you please clarify your experience (sql , app developer)? UNION datasets will give you a different result.
that is what tMap is for.
connect into tMap the following: customer_csv is used as the main input
customer_api as a lookup then in tMap use the ID column to join the data sets and add the column you want to the output
Thanks for your reply, I am Java developer with very little experience working with ETL tools.
You are right, I need something more like Full Outer Join, since all three tables might have new records.
If I use left outer join the customer id = 4 will be left out because is not on the customer_csv table.