topic Re: how to achieve CDC manually for capturing inserts and updates in TOS in Talend Studio

how to achieve CDC manually for capturing inserts and updates in TOS

rkaruppiah — Sat, 16 Nov 2024 10:57:33 GMT

HI
AM USING TOS OPEN EDITION FOR BIG DATA 5.6.2. I KNOW ENTERPRISE VERSION HAS CDC FEATURE ENABLED BUT OUR ORGANIZATION DOES NOT WANT TO GO WITH ENTERPRISE VERSION FOR NOW. IS THERE IS LINKS WHERE I CAN FIND THE STEPS TO ACHIEVE CDC MANUALLY IN TALEND FOR BIG DATA 5.6.2 OPEN STUDIO?
BELOW IS MY REQUIREMENT:
Am using Tos 5.6.2 open studio version. I understand CDC feature is enabled only in TIS. I m new and need to know the detailed steps on capturing Insert and updated rows on NEED BASIS USING TOS OPEN STUDIO ONLY. I m using Oracle 11. It would be great help if you can provide the flow with detailed step by step from you.

TORACLEINPUT(1)
|LOOKUP
v LOOKUP
TORACLEINPUT --------> TMAP2 <----------------HIVE TABLE (2)
(SOURCE TBL) |
|
IDENTIFY NEWLY INSERTED RECORDS
|
|
IDENTIFY ONLY CHANGED RECORDS BY (IDENTIFY BY EACH COLUMNS)
|
|
CONSOLIDATE BOTH INSERTED AND UPDATED RECORDS IN A SEPERATE TABLE WITH UPDATE FLAG AND TIMESTAMP
|
|
LOAD THE CDC DATA INTO THE ORACLE OUTPUT TABLE USING TORACLEOUTPUT COMPONENT

APPRECIATE YOUR HELP.
Thanks,
Raja

Re: how to achieve CDC manually for capturing inserts and updates in TOS

Anonymous — Mon, 09 Nov 2015 08:36:29 GMT

Hi
Without CDC, you have to do an inner join between source table and target table to capture all the changed data including inserted data, updated data and deleted data.
inserted data:
source table--main--tmap--do an inner join and get the unmatched data (inserted data)
|
lookup
|
target table
deleted data:
target table--main--tMap--do an inner join and get the unmatched data (deleted data)
|
lookup
|
source table
updated data:
source table--main--tmap--do an inner join (identify by all columns) and get the unmatched data (updated data)
|
lookup
|
target table

Best regards
Shong

Re: how to achieve CDC manually for capturing inserts and updates in TOS

rkaruppiah — Mon, 09 Nov 2015 18:42:07 GMT

Thanks. For Insert and Delete records i m able to get it. For finding updated records, can i explain a bit more how can i identify by identify by all columns(you mentioned).
Can you explain the scenario with more detail for getting updated records field by field.
Example: My table has more 50 fields and i have fields like acctno acctname, acctrange, startdate enddate address1 address2 zip role designation salarycarddetails etc . Also i need to include custom field for performing complex calculations in few fields.
sourcetable:
ano acctrange startdate enddate
1 000-099 10/01/2015 10/31/2015
2 100-199 10/01/2015 12/31/2015

my lookup table contains data as below:
ano acctrange startdate enddate
1 000-099 10/01/2015 10/31/2015
2 101-299 10/01/2015 12/31/2015 (look up tablerow updated for acct no :2)
2 101-299 10/01/2015 11/30/2015 (look up tablerow updated for acct no :2)
3 300-399 12/01/2015 12/31/2015
like this
how do i need to arrive this for each FIELDS(acctno, acctrange,startdate,enddate) mentioned above. Any of the field values can change and appended records as above.

Please let me know if you need clarity.
Thanks,
rk

Re: how to achieve CDC manually for capturing inserts and updates in TOS

Anonymous — Wed, 11 Nov 2015 08:18:06 GMT

Hi
See the below screenshot, it gets the updated records in main table.

Re: how to achieve CDC manually for capturing inserts and updates in TOS

rkaruppiah — Wed, 18 Nov 2015 05:50:12 GMT

Thanks. For Oracle look up table this is fine. How to handle the similar functionality with hive lookup tables.