Using Amazon S3 as a target for Amazon Database Migration Service

You can migrate data to Amazon S3 using Amazon DMS from any of the supported database sources. When using Amazon S3 as a target in an Amazon DMS task, both full load and change data capture (CDC) data is written to comma-separated value (.csv) format by default. For more compact storage and faster query options, you also have the option to have the data written to Apache Parquet (.parquet) format.

Amazon DMS names files created during a full load using an incremental hexadecimal counter—for example LOAD00001.csv, LOAD00002..., LOAD00009, LOAD0000A, and so on for .csv files. Amazon DMS names CDC files using timestamps, for example 20141029-1134010000.csv. For each source table that contains records, Amazon DMS creates a folder under the specified target folder (if the source table is not empty). Amazon DMS writes all full load and CDC files to the specified Amazon S3 bucket. You can control the size of the files that Amazon DMS creates by using the MaxFileSize endpoint setting.

The parameter bucketFolder contains the location where the .csv or .parquet files are stored before being uploaded to the S3 bucket. With .csv files, table data is stored in the following format in the S3 bucket, shown with full-load files.

database_schema_name/table_name/LOAD00000001.csv
database_schema_name/table_name/LOAD00000002.csv
...
database_schema_name/table_name/LOAD00000009.csv
database_schema_name/table_name/LOAD0000000A.csv
database_schema_name/table_name/LOAD0000000B.csv
...database_schema_name/table_name/LOAD0000000F.csv
database_schema_name/table_name/LOAD00000010.csv
...

You can specify the column delimiter, row delimiter, and other parameters using the extra connection attributes. For more information on the extra connection attributes, see Endpoint settings when using Amazon S3 as a target for Amazon DMS at the end of this section.

You can specify a bucket owner and prevent sniping by using the ExpectedBucketOwner Amazon S3 endpoint setting, as shown following. Then, when you make a request to test a connection or perform a migration, S3 checks the account ID of the bucket owner against the specified parameter.

--s3-settings='{"ExpectedBucketOwner": "AWS_Account_ID"}'

When you use Amazon DMS to replicate data changes using a CDC task, the first column of the .csv or .parquet output file indicates how the row data was changed as shown for the following .csv file.

I,101,Smith,Bob,4-Jun-14,New York
U,101,Smith,Bob,8-Oct-15,Los Angeles
U,101,Smith,Bob,13-Mar-17,Dallas
D,101,Smith,Bob,13-Mar-17,Dallas
        

For this example, suppose that there is an EMPLOYEE table in the source database. Amazon DMS writes data to the .csv or .parquet file, in response to the following events:

Note that by default for CDC, Amazon DMS stores the row changes for each database table without regard to transaction order. If you want to store the row changes in CDC files according to transaction order, you need to use S3 endpoint settings to specify this and the folder path where you want the CDC transaction files to be stored on the S3 target. For more information, see Capturing data changes (CDC) including transaction order on the S3 target.

To control the frequency of writes to an Amazon S3 target during a data replication task, you can configure the cdcMaxBatchInterval and cdcMinFileSize extra connection attributes. This can result in better performance when analyzing the data without any additional overhead operations. For more information, see Endpoint settings when using Amazon S3 as a target for Amazon DMS

Prerequisites for using Amazon S3 as a target

Before using Amazon S3 as a target, check that the following are true:

To set up this account access, ensure that the role assigned to the user account used to create the migration task has the following set of permissions.

For prerequisites for using validation with S3 as a target, see S3 target validation prerequisites.

Limitations to using Amazon S3 as a target

The following limitations apply when using Amazon S3 as a target:

For limitations for using validation with S3 as a target, see Limitations for using S3 target validation.

Security

To use Amazon S3 as a target, the account used for the migration must have write and delete access to the Amazon S3 bucket that is used as the target. Specify the Amazon Resource Name (ARN) of an IAM role that has the permissions required to access Amazon S3.

Amazon DMS supports a set of predefined grants for Amazon S3, known as canned access control lists (ACLs). Each canned ACL has a set of grantees and permissions that you can use to set permissions for the Amazon S3 bucket. You can specify a canned ACL using the cannedAclForObjects on the connection string attribute for your S3 target endpoint. For more information about using the extra connection attribute cannedAclForObjects, see Endpoint settings when using Amazon S3 as a target for Amazon DMS. For more information about Amazon S3 canned ACLs, see Canned ACL.

The IAM role that you use for the migration must be able to perform the s3:PutObjectAcl API operation.

Using Apache Parquet to store Amazon S3 objects

The comma-separated value (.csv) format is the default storage format for Amazon S3 target objects. For more compact storage and faster queries, you can instead use Apache Parquet (.parquet) as the storage format.

Apache Parquet is an open-source file storage format originally designed for Hadoop. For more information on Apache Parquet, see https://parquet.apache.org/.

To set .parquet as the storage format for your migrated S3 target objects, you can use the following mechanisms:

Amazon S3 object tagging

You can tag Amazon S3 objects that a replication instance creates by specifying appropriate JSON objects as part of task-table mapping rules. For more information about requirements and options for S3 object tagging, including valid tag names, see Object tagging in the Amazon Simple Storage Service User Guide. For more information about table mapping using JSON, see Specifying table selection and transformations rules using JSON.

You tag S3 objects created for specified tables and schemas by using one or more JSON objects of the selection rule type. You then follow this selection object (or objects) by one or more JSON objects of the post-processing rule type with add-tag action. These post-processing rules identify the S3 objects that you want to tag and specify the names and values of the tags that you want to add to these S3 objects.

You can find the parameters to specify in JSON objects of the post-processing rule type in the following table.

When you specify multiple post-processing rule types to tag a selection of S3 objects, each S3 object is tagged using only one tag-set object from one post-processing rule. The particular tag set used to tag a given S3 object is the one from the post-processing rule whose associated object locator best matches that S3 object.

For example, suppose that two post-processing rules identify the same S3 object. Suppose also that the object locator from one rule uses wildcards and the object locator from the other rule uses an exact match to identify the S3 object (without wildcards). In this case, the tag set associated with the post-processing rule with the exact match is used to tag the S3 object. If multiple post-processing rules match a given S3 object equally well, the tag set associated with the first such post-processing rule is used to tag the object.

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": "5",
            "rule-name": "5",
            "object-locator": {
                "schema-name": "aat2",
                "table-name": "STOCK"
            },
            "rule-action": "include"
        },
        {
            "rule-type": "post-processing",
            "rule-id": "41",
            "rule-name": "41",
            "rule-action": "add-tag",
            "object-locator": {
                "schema-name": "aat2",
                "table-name": "STOCK"
            },
            "tag-set": [
              {
                "key": "tag_1",
                "value": "value_1"
              },
              {
                "key": "tag_2",
                "value": "value_2"
              },
              {
                "key": "tag_3",
                "value": "value_3"
              }                                     
           ]
        }
    ]
}

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "1",
            "object-locator": {
                "schema-name": "%",
                "table-name": "%"
            },
            "rule-action": "include"
        },
        {
            "rule-type": "post-processing",
            "rule-id": "21",
            "rule-name": "21",
            "rule-action": "add-tag",
            "object-locator": {
                "schema-name": "%",
                "table-name": "%",
            },
            "tag-set": [
              { 
                "key": "dw-schema-name",
                "value":"${schema-name}"
              },
              {
                "key": "dw-schema-table",
                "value": "my_prefix_${table-name}"
              }
            ]
        },
        {
            "rule-type": "post-processing",
            "rule-id": "41",
            "rule-name": "41",
            "rule-action": "add-tag",
            "object-locator": {
                "schema-name": "aat",
                "table-name": "ITEM",
            },
            "tag-set": [
              {
                "key": "tag_1",
                "value": "value_1"
              },
              {
                "key": "tag_2",
                "value": "value_2"
              }           ]
        }
    ]
}

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "1",
            "object-locator": {
                "schema-name": "retail",
                "table-name": "ITEM"
            },
            "rule-action": "include"
        },
        {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "1",
            "object-locator": {
                "schema-name": "wholesale",
                "table-name": "ITEM"
            },
            "rule-action": "include"
        },
        {
            "rule-type": "post-processing",
            "rule-id": "21",
            "rule-name": "21",
            "rule-action": "add-tag",
            "object-locator": {
                "schema-name": "%",
                "table-name": "ITEM",
            },
            "tag-set": [
              { 
                "key": "dw-schema-name",
                "value":"${schema-name}"
              },
              {
                "key": "dw-schema-table",
                "value": "my_prefix_ITEM"
              },
              {
                "key": "${schema-name}_ITEM_tag_1",
                "value": "value_1"
              },
              {
                "key": "${schema-name}_ITEM_tag_2",
                "value": "value_2"
              }
            ]
    ]
}

Creating Amazon KMS keys to encrypt Amazon S3 target objects

You can create and use custom Amazon KMS keys to encrypt your Amazon S3 target objects. After you create a KMS key, you can use it to encrypt objects using one of the following approaches when you create the S3 target endpoint:

To encrypt Amazon S3 target objects using a KMS key, you need an IAM role that has permissions to access the Amazon S3 bucket. This IAM role is then accessed in a policy (a key policy) attached to the encryption key that you create. You can do this in your IAM console by creating the following:

The following procedures describe how to do this.

You have now created the new policy to access Amazon S3 resources for encryption with a specified name, for example DMS-S3-endpoint-access.

You have now created the new role to access Amazon S3 resources for encryption with a specified name, for example, DMS-S3-endpoint-access-role.

You have now created a new KMS key with a specified alias (for example, DMS-S3-endpoint-encryption-key). This key enables Amazon DMS to encrypt Amazon S3 target objects.

Using date-based folder partitioning

Amazon DMS supports S3 folder partitions based on a transaction commit date when you use Amazon S3 as your target endpoint. Using date-based folder partitioning, you can write data from a single source table to a time-hierarchy folder structure in an S3 bucket. By partitioning folders when creating an S3 target endpoint, you can do the following:

You can enable date-based folder partitioning when you create an S3 target endpoint. You can enable it when you either migrate existing data and replicate ongoing changes (full load + CDC), or replicate data changes only (CDC only). When you migrate existing data and replicate ongoing changes, only ongoing changes will be partitioned. Use the following target endpoint settings:

The following example shows how to enable date-based folder partitioning, with default values for the data partition sequence and the delimiter. It uses the --s3-settings '{json-settings}' option of the Amazon CLI.create-endpoint command.

   --s3-settings '{"DatePartitionEnabled": true,"DatePartitionSequence": "YYYYMMDD","DatePartitionDelimiter": "SLASH"}'
            

Parallel load of partitioned sources when using Amazon S3 as a target for Amazon DMS

You can configure a parallel full load of partitioned data sources to Amazon S3 targets. This approach improves the load times for migrating partitioned data from supported source database engines to the S3 target. To improve the load times of partitioned source data, you create S3 target subfolders mapped to the partitions of every table in the source database. These partition-bound subfolders allow Amazon DMS to run parallel processes to populate each subfolder on the target.

To configure a parallel full load of an S3 target, S3 supports three parallel-load rule types for the table-settings rule of table mapping:

For more information on these parallel-load rule types, see Table and collection settings rules and operations.

For the partitions-auto and partitions-list rule types, Amazon DMS uses each partition name from the source endpoint to identify the target subfolder structure, as follows.

bucket_name/bucket_folder/database_schema_name/table_name/partition_name/LOADseq_num.csv

Here, the subfolder path where data is migrated and stored on the S3 target includes an additional partition_name subfolder that corresponds to a source partition with the same name. This partition_name subfolder then stores one or more LOADseq_num.csv files containing data migrated from the specified source partition. Here, seq_num is the sequence number postfix on the .csv file name, such as 00000001 in the .csv file with the name, LOAD00000001.csv.

However, some database engines, such as MongoDB and DocumentDB, don't have the concept of partitions. For these database engines, Amazon DMS adds the running source segment index as a prefix to the target .csv file name, as follows.

.../database_schema_name/table_name/SEGMENT1_LOAD00000001.csv
.../database_schema_name/table_name/SEGMENT1_LOAD00000002.csv
...
.../database_schema_name/table_name/SEGMENT2_LOAD00000009.csv
.../database_schema_name/table_name/SEGMENT3_LOAD0000000A.csv

Here, the files SEGMENT1_LOAD00000001.csv and SEGMENT1_LOAD00000002.csv are named with the same running source segment index prefix, SEGMENT1. They're named as so because the migrated source data for these two .csv files is associated with the same running source segment index. On the other hand, the migrated data stored in each of the target SEGMENT2_LOAD00000009.csv and SEGMENT3_LOAD0000000A.csv files is associated with different running source segment indexes. Each file has its file name prefixed with the name of its running segment index, SEGMENT2 and SEGMENT3.

For the ranges parallel-load type, you define the column names and column values using the columns and boundaries settings of the table-settings rules. With these rules, you can specify partitions corresponding to segment names, as follows.

"parallel-load": {
    "type": "ranges",
    "columns": [
         "region",
         "sale"
    ],
    "boundaries": [
          [
               "NORTH",
               "1000"
          ],
          [
               "WEST",
               "3000"
          ]
    ],
    "segment-names": [
          "custom_segment1",
          "custom_segment2",
          "custom_segment3"
    ]
}

Here, the segment-names setting defines names for three partitions to migrate data in parallel on the S3 target. The migrated data is parallel-loaded and stored in .csv files under the partition subfolders in order, as follows.

.../database_schema_name/table_name/custom_segment1/LOAD[00000001...].csv
.../database_schema_name/table_name/custom_segment2/LOAD[00000001...].csv
.../database_schema_name/table_name/custom_segment3/LOAD[00000001...].csv

Here, Amazon DMS stores a series of .csv files in each of the three partition subfolders. The series of .csv files in each partition subfolder is named incrementally starting from LOAD00000001.csv until all the data is migrated.

In some cases, you might not explicitly name partition subfolders for a ranges parallel-load type using the segment-names setting. In these case, Amazon DMS applies the default of creating each series of .csv files under its table_name subfolder. Here, Amazon DMS prefixes the file names of each series of .csv files with the name of the running source segment index, as follows.

.../database_schema_name/table_name/SEGMENT1_LOAD[00000001...].csv
.../database_schema_name/table_name/SEGMENT2_LOAD[00000001...].csv
.../database_schema_name/table_name/SEGMENT3_LOAD[00000001...].csv
...
.../database_schema_name/table_name/SEGMENTZ_LOAD[00000001...].csv

Endpoint settings when using Amazon S3 as a target for Amazon DMS

You can use endpoint settings to configure your Amazon S3 target database similar to using extra connection attributes. You specify the settings when you create the target endpoint using the Amazon DMS console, or by using the create-endpoint command in the Amazon CLI, with the --s3-settings '{"EndpointSetting": "value", ...}' JSON syntax.

The following table shows the endpoint settings that you can use with Amazon S3 as a target.

Using Amazon Glue Data Catalog with an Amazon S3 target for Amazon DMS

Amazon Glue is a service that provides simple ways to categorize data, and consists of a metadata repository known as Amazon Glue Data Catalog. You can integrate Amazon Glue Data Catalog with your Amazon S3 target endpoint and query Amazon S3 data through other Amazon services such as Amazon Athena. Amazon Redshift works with Amazon Glue but Amazon DMS doesn't support that as a pre-built option.

To generate the data catalog, set the GlueCatalogGeneration endpoint setting to true, as shown in the following Amazon CLI example.

aws dms create-endpoint --endpoint-identifier s3-target-endpoint 
            --engine-name s3 --endpoint-type target--s3-settings '{"ServiceAccessRoleArn": 
            "your-service-access-ARN", "BucketFolder": "your-bucket-folder", "BucketName": 
            "your-bucket-name", "DataFormat": "parquet", "GlueCatalogGeneration": true}'

For a Full load replication task that includes csv type data, set IncludeOpForFullLoad to true.

Don't use GlueCatalogGeneration with PreserveTransactions and CdcPath. The Amazon Glue crawler can't reconcile the different schemas of files stored under the specified CdcPath.

For Amazon Athena to index your Amazon S3 data, and for you to query your data using standard SQL queries through Amazon Athena, the IAM role attached to the endpoint must have the following policy:

Using data encryption, parquet files, and CDC on your Amazon S3 target

You can use S3 target endpoint settings to configure the following:

Amazon KMS key settings for data encryption

The following examples show configuring a custom KMS key to encrypt your S3 target objects. To start, you might run the following create-endpoint CLI command.

aws dms create-endpoint --endpoint-identifier s3-target-endpoint --engine-name s3 --endpoint-type target 
--s3-settings '{"ServiceAccessRoleArn": "your-service-access-ARN", "CsvRowDelimiter": "\n", 
"CsvDelimiter": ",", "BucketFolder": "your-bucket-folder", 
"BucketName": "your-bucket-name", 
"EncryptionMode": "SSE_KMS", 
"ServerSideEncryptionKmsKeyId": "arn:aws:kms:us-east-1:111122223333:key/72abb6fb-1e49-4ac1-9aed-c803dfcc0480"}'

Here, the JSON object specified by --s3-settings option defines two parameters. One is an EncryptionMode parameter with the value SSE_KMS. The other is an ServerSideEncryptionKmsKeyId parameter with the value of arn:aws:kms:us-east-1:111122223333:key/72abb6fb-1e49-4ac1-9aed-c803dfcc0480. This value is an Amazon Resource Name (ARN) for your custom KMS key. For an S3 target, you also specify additional settings. These identify the server access role, provide delimiters for the default CSV object storage format, and give the bucket location and name to store S3 target objects.

By default, S3 data encryption occurs using S3 server-side encryption. For the previous example's S3 target, this is also equivalent to specifying its endpoint settings as in the following example.

aws dms create-endpoint --endpoint-identifier s3-target-endpoint --engine-name s3 --endpoint-type target
--s3-settings '{"ServiceAccessRoleArn": "your-service-access-ARN", "CsvRowDelimiter": "\n", 
"CsvDelimiter": ",", "BucketFolder": "your-bucket-folder", 
"BucketName": "your-bucket-name", 
"EncryptionMode": "SSE_S3"}'

For more information about working with S3 server-side encryption, see Protecting data using server-side encryption.

Settings for using .parquet files to store S3 target objects

The default format for creating S3 target objects is .csv files. The following examples show some endpoint settings for specifying .parquet files as the format for creating S3 target objects. You can specify the .parquet files format with all the defaults, as in the following example.

aws dms create-endpoint --endpoint-identifier s3-target-endpoint --engine-name s3 --endpoint-type target 
--s3-settings '{"ServiceAccessRoleArn": "your-service-access-ARN", "DataFormat": "parquet"}'

Here, the DataFormat parameter is set to parquet to enable the format with all the S3 defaults. These defaults include a dictionary encoding ("EncodingType: "rle-dictionary") that uses a combination of bit-packing and run-length encoding to more efficiently store repeating values.

You can add additional settings for options other than the defaults as in the following example.

aws dms create-endpoint --endpoint-identifier s3-target-endpoint --engine-name s3 --endpoint-type target
--s3-settings '{"ServiceAccessRoleArn": "your-service-access-ARN", "BucketFolder": "your-bucket-folder",
"BucketName": "your-bucket-name", "DataFormat": "parquet", "EncodingType: "plain-dictionary", "DictPageSizeLimit": 3,072,000,
"EnableStatistics": false }'

Here, in addition to parameters for several standard S3 bucket options and the DataFormat parameter, the following additional .parquet file parameters are set:

Capturing data changes (CDC) including transaction order on the S3 target

By default when Amazon DMS runs a CDC task, it stores all the row changes logged in your source database (or databases) in one or more files for each table. Each set of files containing changes for the same table reside in a single target directory associated with that table. Amazon DMS creates as many target directories as database tables migrated to the Amazon S3 target endpoint. The files are stored on the S3 target in these directories without regard to transaction order. For more information on the file naming conventions, data contents, and format, see Using Amazon S3 as a target for Amazon Database Migration Service.

To capture source database changes in a manner that also captures the transaction order, you can specify S3 endpoint settings that direct Amazon DMS to store the row changes for all database tables in one or more .csv files created depending on transaction size. These .csv transaction files contain all row changes listed sequentially in transaction order for all tables involved in each transaction. These transaction files reside together in a single transaction directory that you also specify on the S3 target. In each transaction file, the transaction operation and the identity of the database and source table for each row change is stored as part of the row data as follows.

operation,table_name,database_schema_name,field_value,...

Here, operation is the transaction operation on the changed row, table_name is the name of the database table where the row is changed, database_schema_name is the name of the database schema where the table resides, and field_value is the first of one or more field values that specify the data for the row.

The example following of a transaction file shows changed rows for one or more transactions that involve two tables.

I,Names_03cdcad11a,rdsTempsdb,13,Daniel
U,Names_03cdcad11a,rdsTempsdb,23,Kathy
D,Names_03cdcad11a,rdsTempsdb,13,Cathy
I,Names_6d152ce62d,rdsTempsdb,15,Jane
I,Names_6d152ce62d,rdsTempsdb,24,Chris
I,Names_03cdcad11a,rdsTempsdb,16,Mike

Here, the transaction operation on each row is indicated by I (insert), U (update), or D (delete) in the first column. The table name is the second column value (for example, Names_03cdcad11a). The name of the database schema is the value of the third column (for example, rdsTempsdb). And the remaining columns are populated with your own row data (for example, 13,Daniel).

In addition, Amazon DMS names the transaction files it creates on the Amazon S3 target using a time stamp according to the following naming convention.

CDC_TXN-timestamp.csv

Here, timestamp is the time when the transaction file was created, as in the following example.

CDC_TXN-20201117153046033.csv

This time stamp in the file name ensures that the transaction files are created and listed in transaction order when you list them in their transaction directory.

To control the frequency of writes to an Amazon S3 target during a data replication task, you can configure the CdcMaxBatchInterval and CdcMinFileSize settings. This can result in better performance when analyzing the data without any additional overhead operations. For more information, see Endpoint settings when using Amazon S3 as a target for Amazon DMS

Indicating source DB operations in migrated S3 data

When Amazon DMS migrates records to an S3 target, it can create an additional field in each migrated record. This additional field indicates the operation applied to the record at the source database. How Amazon DMS creates and sets this first field depends on the migration task type and settings of includeOpForFullLoad, cdcInsertsOnly, and cdcInsertsAndUpdates.

For a full load when includeOpForFullLoad is true, Amazon DMS always creates an additional first field in each .csv record. This field contains the letter I (INSERT) to indicate that the row was inserted at the source database. For a CDC load when cdcInsertsOnly is false (the default), Amazon DMS also always creates an additional first field in each .csv or .parquet record. This field contains the letter I (INSERT), U (UPDATE), or D (DELETE) to indicate whether the row was inserted, updated, or deleted at the source database.

In the following table, you can see how the settings of the includeOpForFullLoad and cdcInsertsOnly attributes work together to affect the setting of migrated records.

When includeOpForFullLoad and cdcInsertsOnly are set to the same value, the target records are set according to the attribute that controls record settings for the current migration type. That attribute is includeOpForFullLoad for full load and cdcInsertsOnly for CDC load.

When includeOpForFullLoad and cdcInsertsOnly are set to different values, Amazon DMS makes the target record settings consistent for both CDC and full load. It does this by making the record settings for a CDC load conform to the record settings for any earlier full load specified by includeOpForFullLoad.

In other words, suppose that a full load is set to add a first field to indicate an inserted record. In this case, a following CDC load is set to add a first field that indicates an inserted, updated, or deleted record as appropriate at the source. In contrast, suppose that a full load is set to not add a first field to indicate an inserted record. In this case, a CDC load is also set to not add a first field to each record regardless of its corresponding record operation at the source.

Similarly, how DMS creates and sets an additional first field depends on the settings of includeOpForFullLoad and cdcInsertsAndUpdates. In the following table, you can see how the settings of the includeOpForFullLoad and cdcInsertsAndUpdates attributes work together to affect the setting of migrated records in this format.

Target data types for S3 Parquet

The following table shows the Parquet target data types that are supported when using Amazon DMS and the default mapping from Amazon DMS data types.

For additional information about Amazon DMS data types, see Data types for Amazon Database Migration Service.