Start a new topic
Answered

Delete operation does not work.

I want to publish a record deletion throw Data Notification, and want to perform delete operation through sql and rest API.






Best Answer

Data deletion can be done through Sql and through Rest Api


Golden record deletion applies to all entities and is similar to deleting a golden record from an application.


To publish a golden record deletion, you must load the following columns into the SA table (even for ID and fuzzy matched entities):


B_LOADID: Load this column with the Load ID provided by the GET_NEW_LOADID function call, or with the ID of the Continuous Load. Use the GET_CONTINUOUS_LOADID function to retrieve a continuous load ID from the continuous load name.


B_DELETETYPE: Load this column with the SOFT_DELETE or HARD_DELETE value depending on the type of delete that you want to trigger.


B_DELETEOPERATION: Delete operation ID. This ID must be a unique value for each deleted record. For example, use a UUID that you convert to a string (e.g.: Using the functions RAWTOHEX(SYS_GUID())).


       4.B_CLASSNAME: Load this column with the name of the entity of the records to delete.    



Below is the code used for deletion

do
$$
declare
v_load_id int;
begin
v_load_id := semarchy_repository.get_new_loadid (
'test' /* Data Location name. case sensitive. Found in Application Builder. */
,'manual_etl_script' /* Informational program name. Identifies the middleware. */
,'load_data_update' /* Informational. Describes the load. */
,'manual_etl_user' /* User initializing the load. (Not found in Application Builder.) */
);
raise notice '%', v_load_id;
end;
$$;

insert into SEM_5_3_7_DATA.SA_PRODUCT (
B_LOADID,
ID,
B_CLASSNAME,
B_DELETETYPE,
B_DELETEOPERATION
)
select
12112,
112222111,
'Product',
'HARD_DELETE',
RAWTOHEX(SYS_GUID())
from SEM_5_3_7_DATA.GD_PRODUCT where ID = '112222111';



do
$$
declare
v_batch_id int;
begin
v_batch_id := semarchy_repository.submit_load (
65 /* Load ID returned by get_new_loadid from Step 1 */
,'DDJ_sample' /* Must exactly match a defined integration job. Found in Application Builder. */
,'manual_etl_user' /* Must exactly match the value used in get_new_loadid in Step 1. */
);
raise notice '%', v_batch_id;
end;
$$;




For deletion through rest api  You can use the endpoint to load data, with the DELETE_DATA action, in order to publish record deletions. 



1 Comment

Answer

Data deletion can be done through Sql and through Rest Api


Golden record deletion applies to all entities and is similar to deleting a golden record from an application.


To publish a golden record deletion, you must load the following columns into the SA table (even for ID and fuzzy matched entities):


B_LOADID: Load this column with the Load ID provided by the GET_NEW_LOADID function call, or with the ID of the Continuous Load. Use the GET_CONTINUOUS_LOADID function to retrieve a continuous load ID from the continuous load name.


B_DELETETYPE: Load this column with the SOFT_DELETE or HARD_DELETE value depending on the type of delete that you want to trigger.


B_DELETEOPERATION: Delete operation ID. This ID must be a unique value for each deleted record. For example, use a UUID that you convert to a string (e.g.: Using the functions RAWTOHEX(SYS_GUID())).


       4.B_CLASSNAME: Load this column with the name of the entity of the records to delete.    



Below is the code used for deletion

do
$$
declare
v_load_id int;
begin
v_load_id := semarchy_repository.get_new_loadid (
'test' /* Data Location name. case sensitive. Found in Application Builder. */
,'manual_etl_script' /* Informational program name. Identifies the middleware. */
,'load_data_update' /* Informational. Describes the load. */
,'manual_etl_user' /* User initializing the load. (Not found in Application Builder.) */
);
raise notice '%', v_load_id;
end;
$$;

insert into SEM_5_3_7_DATA.SA_PRODUCT (
B_LOADID,
ID,
B_CLASSNAME,
B_DELETETYPE,
B_DELETEOPERATION
)
select
12112,
112222111,
'Product',
'HARD_DELETE',
RAWTOHEX(SYS_GUID())
from SEM_5_3_7_DATA.GD_PRODUCT where ID = '112222111';



do
$$
declare
v_batch_id int;
begin
v_batch_id := semarchy_repository.submit_load (
65 /* Load ID returned by get_new_loadid from Step 1 */
,'DDJ_sample' /* Must exactly match a defined integration job. Found in Application Builder. */
,'manual_etl_user' /* Must exactly match the value used in get_new_loadid in Step 1. */
);
raise notice '%', v_batch_id;
end;
$$;




For deletion through rest api  You can use the endpoint to load data, with the DELETE_DATA action, in order to publish record deletions. 



Login to post a comment