You are not logged in.

#1 2012-06-01 10:14:29

guenterpretterhofer
Member
3 posts

Comparing 2 columns in 2 databases

Tags: [database]

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

Offline

#2 2012-06-14 13:56:27

shong
Talend Team


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


Email:shong@talend.com
Choose Talend, Enjoy Talend!
New & Event: Talend Help Center
Talend-->the leader of open source data management and application integration solutions!

Offline

#3 2012-06-14 20:29:56

guenterpretterhofer
Member
3 posts

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

Offline

#4 2012-06-15 07:34:09

shong
Talend Team


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


Email:shong@talend.com
Choose Talend, Enjoy Talend!
New & Event: Talend Help Center
Talend-->the leader of open source data management and application integration solutions!

Offline

#5 2012-06-17 21:27:21

guenterpretterhofer
Member
3 posts

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

Offline

#6 2012-06-18 04:11:57

shong
Talend Team


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


Email:shong@talend.com
Choose Talend, Enjoy Talend!
New & Event: Talend Help Center
Talend-->the leader of open source data management and application integration solutions!

Offline

Board footer