DMS CDC task to Redshift

0

HI

I am trying to set up a DMS task responsible for migrating only the CDC from My SQL to Redshift . The target DB is a new DB on Redshift with no tables .

When I start the job , I see the schema getting generated on the target DB , but the change data entries are absent . Instead , I see a awsdms_apply_exceptions and awsdms_changes.. tables getting created in the public DB , which states an error ODBC return code : 100

I also see an update statement from the error logs that probably failed because there are no rows in the parent table

Question : Can the CDC changes be captured alone from DMS to Redshift or does it always have to be a full load task followed by a CDC task ?

asked a year ago992 views
2 Answers
0

Hi, I use DMS CDC more with relational databases and not with Redshift. With rdbms, you can only use CDC after the initial full load.

It is confirmed by documentation: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Task.CDC.html

You can create an AWS DMS task that captures ongoing changes from the source data 
store. You can do this capture while you are migrating your data. You can also create 
a task that captures ongoing changes after you complete your initial (full-load) migration 
to a supported target data store. This process is called ongoing replication or change data capture (CDC). 
AWS DMS uses this process when replicating ongoing changes from a source data store. This process 
works by collecting changes to the database logs using the database engine's native API.

The rationale in rdbms is that if you receive a CDC record for an update of an existing row and it doesn't exist (as you didn't make the initial laod), DMS triggers an error

To achieve what you want, you would need to transform such actions (update -> insert in rdbms context. skip deletes, etc.) . I don't thik that DMS transformation rules allow for that: see https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html

profile pictureAWS
EXPERT
answered a year ago
  • At S3 level , DMS allows to create separate tasks writing to separate S3 prefixes for a Full Load and CDC task . I was expecting something similar is possible in the DB world as well . Thanks for your answer.

0

To answer your question - YES - the CDC changes can be captured alone from DMS to Redshift.

However, if you have not run the initial load then the CDC operation for update or delete will result in error on Redshift since the original record does not exist in Redshift so there is nothing to apply this CDC to.

This is why generally there is an initial load followed by CDC to keep the target updated incrementally.

profile pictureAWS
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions