topic Multiple tables with different schemas to one table in Talend Studio

Multiple tables with different schemas to one table

Anonymous — Tue, 29 Jan 2013 04:54:24 GMT

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"

customer_xml
id, name, phone
3,"bryan", "888-888-000"

Output
customer
id, name, status, phone
1,"john", "inactive",null
2,"mike", "inactive",null
3,"bryan", "active", "888-888-000"
4,null, "inactive", null

I have tried many different combinations with tMap but nothing really has worked, any ideas how it can be done?
BTW I am using TIS Version: 5.1.2

Re: Multiple tables with different schemas to one table

Anonymous — Tue, 29 Jan 2013 11:13:39 GMT

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

Re: Multiple tables with different schemas to one table

Anonymous — Tue, 29 Jan 2013 18:01:29 GMT

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.

Re: Multiple tables with different schemas to one table

Anonymous — Wed, 30 Jan 2013 06:07:57 GMT

Hi,
Here is a reference about tMap on Talend Help Center.
Hope it will give you a little help
Best regards
Sabrina