topic Re: compare and migrate two postgresql tables of two different DB in Talend Studio

compare and migrate two postgresql tables of two different DB

Anonymous — Tue, 30 Dec 2014 21:40:22 GMT

Hi all,
I'm new in Talend and I want to compare 2 columns in 2 different databases then migrate 2 tables. This is an example of what I aim do:
- In BD1 I have table T1 wich contains many columns like : id | name
1 | A
2 | B
And T2 wich contains only 2 columns the ids of T1 and the ids of another table like this : id1 | id2
1 | 11
2 | 12
- In DB2 I have a table T3 wich is similar to T1 with the same names but with different ids : id | name
20 | A
21 | B
and T4 similar to T2 but empty .
1. Frist I have to compare the column name of T1 and T3 (if T1.name = T2.name) then
2. migrate T4 wich must be like this : id1 | id2
20 | 11
21 | 12
Is there a way in Talend Open Studio to do that ! Help me please.
Thank you,
Emna

Re: compare and migrate two postgresql tables of two different DB

Anonymous — Wed, 31 Dec 2014 03:54:12 GMT

Hi,
If I understand your requirment very well, you can use TalendHelpCenter:tMap to achieve your goal.
To make it clear, I have designed a demo job. Hope it will be helpful for you.
Please see my screenshots
Best regards
Sabrina

Re: compare and migrate two postgresql tables of two different DB

Anonymous — Thu, 01 Jan 2015 18:08:36 GMT

Hi Sabrina, thank you for help !
I tried your example but that wasn't the expected result , there where only 3 rows are migrated, I simplified the problem but actually this is the real situation :
the table T3('groups_users') contains the ids of the groups and the users wich is a many2many relation between 2 tables 'groups' and 'users', The table users is the same in the 2 DB this is not the case for the table 'groups'
in the table groups we have the columns 'id', 'name' and 'category_id', there are some similar names but for different 'category_id' like this example :
id | name | category_id
1 | A | 6
2 | A | 224
3 | B | 56
the column category_id contains the ids of another table 'model_category' in this table we find many columns too like id, name .. (same names in the 2 DB)
what I aim do is to find the id of the group in the other DB to migrate the table T3('groups_users') :
we have the same names of the 'model_category' in the 2DB but with different id same case tof the table 'groups ' so
if DB1.model_category.name = DB2.model_category.name then :
if DB1.group.name= DB2.group.name (for the same category_model)
then migrate T3
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PS: T3 doesn't contain a primary key, I defined it
This screenshots contain the test that I did. Please take a look on that and help me to resolve it.
Best regards,
Amna