Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Other: HANA Timekey style replication in Snowflake

Jack Freeman User

Hello,

I understand that there is a limitation regarding supplemental logging available from HANA as part of LDP Replication, and that this makes the current TimeKey method of replication not possible.

However can we potentially explore a snowflake specific work around for this issue? Most notably when using burst tables i believe there exists a work around to the merge operation that HVR will perform that would allow LDP to write inserts rather than updates.

At its core HVR has the capability on integrate to potentially insert a new record instead of an in situ update.

See worked example sql below with comments. Basically the workflow would be to update the merge statement to change from "merge when matched update" to an insert using the existing most recent record in the target table. Also happy to discuss in more detail.

--Current operations followed using SoftDelete method for HANA




--Step one COPY from stage into burst table 

--no change 




--Step 2 Operation Types -21

-- Operation type 21

-- 21 – Poor insert, with missing values

insert into "t_sapabap1_cdpos__b" (

  "hvr_tx_seq",

  "hvr_tx_countdown",

  "hvr_op",

  "hvr_cap_loc",

  "hvr_cap_tstamp",

  "hvr_cap_user",

  "hvr_rowid",

  "MANDANT",

  "OBJECTCLAS",

  "OBJECTID",

  "CHANGENR",

  "TABNAME",

  "TABKEY",

  "FNAME",

  "CHNGIND",

  "TEXT_CASE",

  "UNIT_OLD",

  "UNIT_NEW",

  "CUKY_OLD",

  "CUKY_NEW",

  "VALUE_NEW",

  "VALUE_OLD",

  "_DATAAGING",

  "_CHANGE_TYPE",

  "_CHANGE_TIME",

  "_CHANGE_SEQUENCE",

  "HVR_Operation_Type",

  "REPL_Integration_Sequence_Number",

  "REPL_Delete_Timestamp",

  "REPL_EffectiveToDate",

  "REPL_Insert_Timestamp",

  "REPL_Update_Timestamp",

  "hvr_chain_id",

  "hvr_colval_mask")

select

  bur_21."hvr_tx_seq",

  bur_21."hvr_tx_countdown",

  41,

  bur_21."hvr_cap_loc",

  bur_21."hvr_cap_tstamp",

  bur_21."hvr_cap_user",

  bur_21."hvr_rowid",

  bas_."MANDANT",

  bas_."OBJECTCLAS",

  bas_."OBJECTID",

  bas_."CHANGENR",

  bas_."TABNAME",

  bas_."TABKEY",

  bas_."FNAME",

  bas_."CHNGIND",

  bas_."TEXT_CASE",

  bas_."UNIT_OLD",

  bas_."UNIT_NEW",

  bas_."CUKY_OLD",

  bas_."CUKY_NEW",

  bas_."VALUE_NEW",

  bas_."VALUE_OLD",

  bas_."_DATAAGING",

  bur_21."_CHANGE_TYPE",

  bas_."_CHANGE_TIME",

  bas_."_CHANGE_SEQUENCE",

  bur_21."HVR_Operation_Type",

  bur_21."REPL_Integration_Sequence_Number",

  bas_."REPL_Delete_Timestamp",

  bur_21."REPL_EffectiveToDate",

  bur_21."REPL_Insert_Timestamp",

  bas_."REPL_Update_Timestamp",

  bur_21."hvr_chain_id",

  'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvmmvv'

from "t_sapabap1_cdpos__b" bur_21

join "t_sapabap1_cdpos__b" bur_20 

on (bur_20."hvr_chain_id" = bur_21."hvr_chain_id")

join "T_SAPABAP1_CDPOS" bas_ 

on (bas_."hvr_rowid"=bur_20."hvr_rowid")

where bur_20."hvr_op"=20 

  and bur_21."hvr_op"=21 

  and bas_."hvr_rowid" > 106691041 --<<this is the min base table value

  and bas_."hvr_rowid" < 149370372 --<<this is the max base table value

-- joins the burst table to itself using alias 21 and 20 and inserts a new row into the burst table with operation 41 (per email • 41 – Variant of 21 with augmented values)

-- It uses all of the current base table values only changing the updated value

-- essentially creates a duplicate record of the burst table, using the base table values where burst table values not present 

--

   

-- Step 3 -- Delete operations to be completed   

-- 20 – Poor delete, with missing values (from email) --bur_."hvr_op" + 100,adds 100 to the delete op

--

--

insert into "t_sapabap1_cdpos__b" (

  "hvr_tx_seq",

  "hvr_tx_countdown",

  "hvr_op",

  "hvr_cap_loc",

  "hvr_cap_tstamp",

  "hvr_cap_user",

  "hvr_rowid",

  "MANDANT",

  "OBJECTCLAS",

  "OBJECTID",

  "CHANGENR",

  "TABNAME",

  "TABKEY",

  "FNAME",

  "CHNGIND",

  "TEXT_CASE",

  "UNIT_OLD",

  "UNIT_NEW",

  "CUKY_OLD",

  "CUKY_NEW",

  "VALUE_NEW",

  "VALUE_OLD",

  "_DATAAGING",

  "_CHANGE_TYPE",

  "_CHANGE_TIME",

  "_CHANGE_SEQUENCE",

  "HVR_Operation_Type",

  "REPL_Integration_Sequence_Number",

  "REPL_Delete_Timestamp",

  "REPL_EffectiveToDate",

  "REPL_Insert_Timestamp",

  "REPL_Update_Timestamp",

  "hvr_chain_id",

  "hvr_colval_mask")

select

  bur_."hvr_tx_seq",

  bur_."hvr_tx_countdown",

  bur_."hvr_op" + 100,

  bur_."hvr_cap_loc",

  bur_."hvr_cap_tstamp",

  bur_."hvr_cap_user",

  bur_."hvr_rowid",

  bas_."MANDANT",

  bas_."OBJECTCLAS",

  bas_."OBJECTID",

  bas_."CHANGENR",

  bas_."TABNAME",

  bas_."TABKEY",

  bas_."FNAME",

  bas_."CHNGIND",

  bas_."TEXT_CASE",

  bas_."UNIT_OLD",

  bas_."UNIT_NEW",

  bas_."CUKY_OLD",

  bas_."CUKY_NEW",

  bas_."VALUE_NEW",

  bas_."VALUE_OLD",

  bas_."_DATAAGING",

  bas_."_CHANGE_TYPE",

  bas_."_CHANGE_TIME",

  bas_."_CHANGE_SEQUENCE",

  bur_."HVR_Operation_Type",

  bur_."REPL_Integration_Sequence_Number",

  null,

  null,

  null,

  null,

  bur_."hvr_chain_id",

  'vvvvvvvvvvvvvvvvvvvvvvvvvvvmmmmmmv'

from "t_sapabap1_cdpos__b" bur_

join "T_SAPABAP1_CDPOS" bas_ on (

   bas_."hvr_rowid"=bur_."hvr_rowid")

where

  bur_."hvr_op" IN (20) and bas_."hvr_rowid" > 106691041 and bas_."hvr_rowid" < 149370372




   --inserts a record into the burst table for delete operation

   --uses all base table records  for the standard columns

   --operation type 20




--step 4 merge delete records from burst back to base table




merge into "T_SAPABAP1_CDPOS" bas_ 

using "t_sapabap1_cdpos__b" bur_ 

on (  bas_."hvr_rowid"=bur_."hvr_rowid" 

and bur_."hvr_op"=20 

and bas_."hvr_rowid" > 106691041 

and bas_."hvr_rowid" < 149370372) 

when matched then update 

set bas_."HVR_Operation_Type"= bur_."HVR_Operation_Type" ,

 bas_."REPL_Integration_Sequence_Number"= bur_."REPL_Integration_Sequence_Number" ,

 bas_."REPL_Delete_Timestamp"= ( bur_."hvr_cap_tstamp" ) ,

 bas_."REPL_EffectiveToDate"= (NULL) ,

 bas_."REPL_EffectiveFromDate"= ( bur_."hvr_cap_tstamp" ) ,

 bas_."REPL_Managed_Flag"= ('Y') ,

 bas_."REPL_Delete_Flag"=1




----step 5 insert new records from burst to base table

insert into "T_SAPABAP1_CDPOS" ("hvr_rowid" ,

 "MANDANT" ,

 "OBJECTCLAS" ,

 "OBJECTID" ,

 "CHANGENR" ,

 "TABNAME" ,

 "TABKEY" ,

 "FNAME" ,

 "CHNGIND" ,

 "TEXT_CASE" ,

 "UNIT_OLD" ,

 "UNIT_NEW" ,

 "CUKY_OLD" ,

 "CUKY_NEW" ,

 "VALUE_NEW" ,

 "VALUE_OLD" ,

 "_DATAAGING" ,

 "_CHANGE_TYPE" ,

 "_CHANGE_TIME" ,

 "_CHANGE_SEQUENCE" ,

 "HVR_Operation_Type" ,

 "REPL_Integration_Sequence_Number" ,

 "REPL_Delete_Timestamp" ,

 "REPL_EffectiveToDate" ,

 "REPL_Insert_Timestamp" ,

 "REPL_Update_Timestamp" ,

 "REPL_EffectiveFromDate" ,

 "REPL_Managed_Flag" ,

 "REPL_Delete_Flag") select bur_."hvr_rowid" ,

 bur_."MANDANT" ,

 bur_."OBJECTCLAS" ,

 bur_."OBJECTID" ,

 bur_."CHANGENR" ,

 bur_."TABNAME" ,

 bur_."TABKEY" ,

 bur_."FNAME" ,

 bur_."CHNGIND" ,

 bur_."TEXT_CASE" ,

 bur_."UNIT_OLD" ,

 bur_."UNIT_NEW" ,

 bur_."CUKY_OLD" ,

 bur_."CUKY_NEW" ,

 bur_."VALUE_NEW" ,

 bur_."VALUE_OLD" ,

 bur_."_DATAAGING" ,

 bur_."_CHANGE_TYPE" ,

 bur_."_CHANGE_TIME" ,

 bur_."_CHANGE_SEQUENCE" ,

 bur_."HVR_Operation_Type" ,

 bur_."REPL_Integration_Sequence_Number" ,

 bur_."REPL_Delete_Timestamp" ,

 (NULL) ,

 ( bur_."hvr_cap_tstamp" ) ,

 bur_."REPL_Update_Timestamp" ,

 ( bur_."hvr_cap_tstamp" ) ,

 ('Y') ,

 0 from "t_sapabap1_cdpos__b" bur_ where bur_."hvr_op"=41










-- From above, we could modify step 4.

-- Using the below prior suggested logic to change from an update statement to an insert statement




 -- I've added sample here below as well as a worked example with code 

-- from after the etc.




merge into "SANDBOX"."SCHEMA_A"."T_SAPABAP1_CDPOS"  bas

using (select bas."hvr_rowid" 

        ,NVL(REPLACE("colA",'',NULL),bas."colA") "colA"  --Added NVL and REPLACE fuctions. This allows for updates that may come as part of the delete operation to be included (IF THERE ARE UPDATES) and if null, to remain as previous value

,NVL(REPLACE("colB",'',NULL),bas."colB") "colB"  --Added NVL and REPLACE fuctions. This allows for updates that may come as part of the delete operation to be included (IF THERE ARE UPDATES) and if null, to remain as previous value

       ,bur."hvr_op" 

   from "SANDBOX"."SCHEMA_A"."T_SAPABAP1_CDPOS" bas

       join "SANDBOX"."SCHEMA_A"."t_sapabap1_cdpos__b" bur --CALL THE BURST TABLE HERE IN SUBQUERY JOINING TABLE TO BURST TABLE

       on bas."hvr_rowid"=bur."hvr_rowid"

       and bur."hvr_op"=20) bur_ --ADD OPERATION TYPE FILTER TO GET SPECIFIC OPERATIONS AND REDUCE COMPUTE

on (  bas."hvr_rowid"=bur_."hvr_rowid" 

and bas."hvr_op"= bur_."hvr_op"

)

  when not matched then insert

("hvr_rowid"

 , "colA"

 , "colB"

,"HVR_Operation_Type"

,"REPL_Integration_Sequence_Number"

,"REPL_Delete_Timestamp" 

,"REPL_EffectiveToDate"

,"REPL_EffectiveFromDate" 

,"REPL_Managed_Flag" 

,"REPL_Delete_Flag" 

  .... etc.

 )

Values

(

  bur_."hvr_rowid"

, bur_."colA"

, bur_."colB"

, bur_."HVR_Operation_Type"

, bur_."REPL_Integration_Sequence_Number"

, bur_."hvr_cap_tstamp" 

, (NULL) 

, bur_."hvr_cap_tstamp"

, 'Y' 

, 1 

)

  .... etc.
  
 

 

Please sign in to leave a comment.

Comments

0 comments