In this short scratch i want to show you two rule to masking data using GoldenGate.
Source extract capture test data:
table tuser.*;
- Data masking on replicat level:
map TUSER.tab3, target TUSER.tab4, &
SQLEXEC (ID sub3, &
QUERY 'select decode(:PANval,null,''IsNull'',substr(:PANval,1,6))||decode(:PANval,null,''Value'',substr(:PANval,-4)) subpan from dual', &
PARAMS (PANval=PANID)), &
colmap(USEDEFAULTS,PANID=@getval(sub3.subpan));
MAP TUSER.tab3, TARGET TUSER.tab3, colmap(USEDEFAULTS,PANID=@STRCAT(@STREXT(PANID,1,6),'******',@STREXT(PANID,13,16)));How it works:
On source i made inserts:
insert into TUSER.tab3(PANID,DESCSTR) values('1444555566667779','testPAN');
insert into TUSER.tab3(PANID,DESCSTR) values('2444555566667779','testPAN');
insert into TUSER.tab3(PANID,DESCSTR) values('3444555566667779','testPAN');
commit;Now, in receiver, target TUSER.tab4 use map rule with SQLEXEC and QUERY clause, and target TUSER.tab3 use map rule with only Goldengate function.
Result on receiver:
SQL> select panid, 'tab4' as tabname from TUSER.tab4
2 union all
3 select panid, 'tab3' as tabname from TUSER.tab3
4 ;
PANID TABNAME
-------------------- -------
1444557779 tab4
2444557779 tab4
3444557779 tab4
144455******7779 tab3
244455******7779 tab3
344455******7779 tab3
6 rows selectedHow data looks like on Kafka side(can also be receiver) after masking if using rule:
MAP TUSER.tab3, TARGET TUSER.tab3, colmap(USEDEFAULTS,PANID=@STRCAT(@STREXT(PANID,1,6),'******',@STREXT(PANID,13,16)));Result on Kafka:
{"table":"TUSER.TAB3","op_type":"I","op_ts":"2021-06-27 16:17:20.980191","current_ts":"2021-06-27T16:17:26.971000","pos":"00000000000000003108","after":{"PANID":"144455******7779","DESCSTR":"testPAN"}}
{"table":"TUSER.TAB3","op_type":"I","op_ts":"2021-06-27 16:17:23.980065","current_ts":"2021-06-27T16:17:29.983000","pos":"00000000000000003258","after":{"PANID":"244455******7779","DESCSTR":"testPAN"}}
{"table":"TUSER.TAB3","op_type":"I","op_ts":"2021-06-27 16:17:27.980179","current_ts":"2021-06-27T16:17:33.992000","pos":"00000000000000003409","after":{"PANID":"344455******7779","DESCSTR":"testPAN"}}
krotovks.com 