Comparing 2 columns in 2 databases

Hi all,
hope you can give my some hints how to handle the following with Talend:
How can I compare 2 columns in 2 different databases with each other?
e.g. there is database1, table1, column1 and database2, table2, column2.
Is there an easy way to compare these values in Talend?
Please note that I do not have a database link, so I cannot compare the values within a single SQL statement.
Therefore I have to connect to two databases.
I thought of doing this comparison with tMap component - although tMap main purpose is for data integration.
Making the main connection to database 1 and the lookup connection to database 2.
Is this the right way? Or is there a better one?
Please note also that I have simplified my request. In reality I will have to do more complex comparison.
Something like comarisons with regular expressions, substrings, logical AND, OR, IF.. THEN... and so on.
thanks for your help
Günter

6 REPLIES
Community Manager

Re: Comparing 2 columns in 2 databases

Hi Günter
Whats' is your expected result? I am not sure you should use the DI or the DQ product, in order to give you an accurate answer, can you give us an example to explain your request?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Comparing 2 columns in 2 databases

Hi Shong,
thanks for your answer.
When comparing 2 columns c1, c2 in 2 different databases db1, db2 the expected result is to get back the values of columns c1, c2. And would be useful if I could also easily filter these values of c1, c2 that differ. I also thought of storing the results in a db history table. So that I can generate reports which show if data quality is improving.
Regards
Günter
Community Manager

Re: Comparing 2 columns in 2 databases

Hi
TOS4DQ does not support that comparing columns in different database, as workaround, you can do like this:
1. Migrate one of table with the column to another database with TOS4DI so that you has the two columns in the same database.
2. Compare the two columns with TOS4DQ, view the not matched rows. (see pic1)
3. Export the not matched rows to a file.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Comparing 2 columns in 2 databases

Hi Shong,
thanks for the example how to do column comparison with TOS4DQ. Copying table is not an option in my case. As there are many big tables and many columns to compare.
Is it an option to use tMap with TOS4DI?
Using the main connection to get data from database 1 and the lookup connection to get data from database 2?
Best regards
Günter
Community Manager

Re: Comparing 2 columns in 2 databases

Hi Günter
Yes, of course, you can compare the two columns from different database with TOS4DI, just do an inner join on tMap, get the matched and unmatched rows. If you don't know how to do an inner join on tMap, please see this topic:
http://talendforge.org/forum/viewtopic.php?id=2202
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Comparing 2 columns in 2 databases

Hi ,
I would like to compare the values picked from two different columns from two different databases(say Db2 and sql). Can i add customised java code for comparison operation?
But how do i integrate with the job . ?
Thanks,
Murali