Is it possible to retrieve Data Definition Language (DDL) statements from the Database Migration Service (DMS) when the target endpoint is S3?

Hello Bhavesh,

Does DMS support replication to an S3 target endpoint?

Yes, AWS Database Migration Service (DMS) supports using an Amazon S3 bucket as the target endpoint for replication tasks.

If yes, how does it work with S3 as the target?

When you create a replication task in DMS, you can select an S3 bucket as the target endpoint. DMS will replicate the necessary data, metadata, and transaction logs from the source database and write them to the S3 bucket.

The data is organized within the S3 bucket based on the database, schema, and table name. DMS creates folders with this naming convention to replicate the database schema and make the data self-describing within S3. For tasks with a continuous replication mode, DMS monitors the source database for DDL changes like create, drop, rename operations on tables. It automatically applies these changes to keep the target schema in sync. Some key things to note - DMS doesn't support querying or joining data from the S3 bucket. The bucket acts only as a target for replication. Also, ensure necessary IAM permissions are given to allow DMS access to the S3 bucket.

I want to get all DDLs from MYSQL through AWS DMS. Create table Drop table Rename table Truncate table Add column Drop column Rename column Change column data type

Yes, AWS DMS supports DDL statements. Supported DDL statements include the following:

Create table
Drop table
Rename table
Truncate table
Add column
Drop column
Rename column
Change column data type

You can use task settings to configure the way DMS handles DDL behavior during change data capture (CDC).

References:-

[1] DDL statements supported by AWS DMS - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.SupportedDDL.html

[2] Task settings for change processing DDL handling - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.DDLHandling.html

How can I retrieve Data Definition Language (DDL) statements from Database Migration Service (DMS) in S3? I inserted 100 records into the 'xyz' table in MySQL. I can see the Parquet file in S3. Next, I truncated the 'xyz' table in MySQL, but nothing happened to the file in S3. How can I ensure that the 'truncate table' operation is reflected in S3?

Hello Bhavesh,

(1) How can I retrieve Data Definition Language (DDL) statements from Database Migration Service (DMS) in S3?

To retrieve DDL statements from DMS, you can enable Time Travel logs in DMS. When enabled, DMS will capture all DDL statements executed on the source database and replicate them to the target database. The DDL statements are recorded in CSV log files stored in an S3 bucket. These log files contain detailed information about each transaction including the schema, table, statement executed etc. You can directly access and query these log files from S3 using tools like Amazon Athena. This allows you to track and analyze all the DDL changes during the migration process.

[+] Using the Time Travel logs - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.TimeTravel.LogSchema.html

(2) I inserted 100 records into the 'xyz' table in MySQL. I can see the Parquet file in S3. Next, I truncated the 'xyz' table in MySQL, but nothing happened to the file in S3. How can I ensure that the 'truncate table' operation is reflected in S3?

When you truncate a table in MySQL, it removes all rows from the table, but it does not reclaim the space allocated for the table. As a result, the Parquet file in S3 will not be automatically deleted or modified because the space used by the data is not reclaimed by the truncate operation. '

To ensure that the truncate operation is reflected in S3, you have a few options:

• Use AWS DMS to migrate data from MySQL to S3 in a continuous replication setup. DMS will capture the DDL statements like truncate from the source database and replicate them to the target. This ensures the S3 files are always in sync with the MySQL table.
• Manually delete the Parquet file from S3 after truncating the table in MySQL. You can do this using the AWS Management Console, AWS CLI, or an SDK.
• Implement a process to automatically delete or modify the Parquet file in S3 when the table is truncated in MySQL. This could involve using AWS Lambda to trigger a function when the table is truncated, which then deletes or modifies the file in S3 accordingly.

I am getting this error. Time travel is not supporting when source engine is MYSQL. Is there another way to get DDL statement? where can I see the aws_dms_exceptions table ?