Filter delete operations with AWS DMS | Amazon Web Services
aws.amazon.com/blogs/database/filter-delete-operations-with-aws-dms/
1 Users
0 Comments
41 Highlights
0 Notes
Tags
Top Highlights
If our AWS DMS task performs both a full load and change data capture (CDC), we must add the includeOpForFullLoad parameter to our target S3 endpoint settings.
Filter delete operations with AWS DMS
two options to configure AWS DMS to filter deletes when replicating data to the target database.
first strategy uses Amazon Simple Storage Service (Amazon S3),
the second strategy uses AWS DMS transformation rules to flag delete operations during replication.
This configuration allows you to selectively retain deleted data in the target database for auditing purposes, even when the source undergoes regular cleanup operations.
Use Amazon S3 as a staging environment
One way to filter deletes with AWS DMS is to use Amazon S3 as a staging environment.
DMS migrates inserts and updates and ignores deletes from the source database to an S3 bucket.
S3 acts as an intermediate system that filters out the deletes from applying to the final target by using the CdcInsertsAndUpdates endpoint setting available with Amazon S3 as target.
When you use the cdcInsertsAndUpdates S3 target endpoint setting, an additional first field is added, which indicates whether the row was inserted (I) or updated (U) at the source database.
How AWS DMS creates and sets this field depends on the migration task type and the settings of includeOpForFullLoad, cdcInsertsOnly, and cdcInsertsAndUpdates
DMS creates an additional field in our load files, which ensures that the columns in the load and CDC files are consistent.
Without this parameter, the load and CDC files will have different numbers of columns, which can cause the following error if you try to use the data from both files together:
[SOURCE_UNLOAD ]E: Failed to write record id: 2, Number of values: 6 is not equal to number of columns: 5. [1020417] (file_unload.c:477)
example
Now, let’s say we perform the following DML operations on this table
following data in our source table:
-- Update a row
-- Delete a row
Ready to highlight and find good content?
Glasp is a social web highlighter that people can highlight and organize quotes and thoughts from the web, and access other like-minded people’s learning.