How to make an inner join between table CDC with changes and a source table?

Highlighted
Four Stars

How to make an inner join between table CDC with changes and a source table?

Hello,

I have an Oracle database where I will monitor a table with changes (CDC).

I want to solve 2 problems in my design:

Problem 1)

I want to make a cross between a CDC table and a table with a lot of data, they are not the same table. I need speed and low memory consumption.

 

Problem 2)

Not all CDC table changes can be processed in the current run, so I want to prevent data loss during extraction.

 

Solution Problem 1)
I have to cross this table with another table but the lookup table has more than 11 million records.

I have thought several options to achieve the goal:

Option a) Extract the big table with TOracleInput and TOracleCDC for the table with changes and use a TMap to make the inner join.
Problem: High memory consumption when extracting data.

Option b) Perform the inner join as an SQL query between the table with changes and the large data table and I only have to extract the joined results.

 

Problem 2)
Due to synchronization problems I may have some changes that can not be processed in that run because the process that updates me the data that allow inner join are not available during that time.

Then I would have to save the results to delete the changes I could process with
tHashInput (Changes processed) -> tOracleOuput (DELETE Changes in table with CDC)
Is it possible to delete changes to a CDC table using tOracleOuput?

I need to keep the changes in the CDC table because I don't want all the changes from the last run to be deleted.

 

Example
change1 from CDC-> the change can be processed
change2 from CDC-> cannot yet process change
change3 from CDC -> change cannot be processed yet

 

So if you just process change1, then I want CDC to be such that way:

change2 of CDC
change3 ofCDC

 

In the next run of the job I would extract them again along with the new changes.

I don't know if anyone can help me design Talend to achieve my goal.

Thank you,

 

 

Highlighted
Moderator

Re: How to make an inner join between table CDC with changes and a source table?

Hello,

In this community post, https://community.talend.com/t5/Design-and-Development-Internal/Does-Oracle-CDC-Xstream-mode-support... it suggest to keep "Table with CDC" value blank to get all the changes with the object name, and the filter there.  Do you think that is a possible approach here?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog