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
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?
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.
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.
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?
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:
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 . ?