AWS Database Migration Service
User Guide (Version API Version 2016-01-01)

Using Table Mapping to Specify Task Settings

Table mapping uses several types of rules to specify the data source, source schema, data, and any transformations that should occur during the task. You can use table mapping to specify individual tables in a database to migrate and the schema to use for the migration. In addition, you can use filters to specify what data from a given table column you want replicated. You can use transformations to modify the data written to the target database.

Specifying Table Selection and Transformations by Table Mapping from the Console

You can use the AWS Management Console to perform table mapping, including specifying table selection and transformations. On the console, use the Where section to specify the schema, table, and action (include or exclude). Use the Filter section to specify the column name in a table and the conditions that you want to apply to a replication task. Together, these two actions create a selection rule.

You can include transformations in a table mapping after you have specified at least one selection rule. You can use transformations to rename a schema or table, add a prefix or suffix to a schema or table, or remove a table column.

The following example shows how to set up selection rules for a table called Customers in a schema called EntertainmentAgencySample. You create selection rules and transformations on the Guided tab. This tab appears only when you have a source endpoint that has schema and table information.

To specify a table selection, filter criteria, and transformations using the AWS console

  1. Sign in to the AWS Management Console and choose AWS DMS. If you are signed in as an AWS Identity and Access Management (IAM) user, you must have the appropriate permissions to access AWS DMS. For more information on the permissions required, see IAM Permissions Needed to Use AWS DMS.

  2. On the Dashboard page, choose Tasks.

  3. Choose Create Task.

  4. Enter the task information, including Task name, Replication instance, Source endpoint, Target endpoint, and Migration type. Choose Guided from the Table mappings section.

    
                                Schema and table selection
  5. In the Table mapping section, choose the schema name and table name. You can use "%" as a wildcard value when specifying the table name. Specify the action to be taken, to include or exclude data defined by the filter.

    
                                Schema and table selection
  6. Specify filter information using the Add column filter and the Add condition links.

    1. Choose Add column filter to specify a column and conditions.

    2. Choose Add condition to add additional conditions.

    The following example shows a filter for the Customers table that includes AgencyIDs between 01 and 85.

    
                                Schema and table selection
  7. When you have created the selections you want, choose Add selection rule.

  8. After you have created at least one selection rule, you can add a transformation to the task. Choose add transformation rule.

    
                                transformation rule
  9. Choose the target that you want to transform, and enter the additional information requested. The following example shows a transformation that deletes the AgencyStatus column from the Customer table.

    
                                transformation rule
  10. Choose Add transformation rule.

  11. (Optional) Add additional selection rules or transformations by choosing add selection rule or add transformation rule. When you are finished, choose Create task.

    
                                transformation rule

Specifying Table Selection and Transformations by Table Mapping Using JSON

You can create table mappings in the JSON format. If you create a migration task using the AWS DMS Management Console, you can enter the JSON directly into the table mapping box. If you use the CLI or API to perform migrations, you can create a JSON file to specify the table mappings that you want to apply during migration.

You can specify what tables or schemas you want to work with, and you can perform schema and table transformations. You create table mapping rules using the selection and transformation rule types.

Note

For Amazon S3 targets, you can also tag S3 objects mapped to selected tables and schemas using the post-processing rule type and the add-tag rule action. For more information, see Amazon S3 Object Tagging.

Selection Rules and Actions

Using table mapping, you can specify what tables or schemas you want to work with by using selection rules and actions. For table mapping rules that use the selection rule type, the following values can be applied.

Parameter Possible Values Description
rule-type selection Define at least one selection rule when specifying a table mapping.
rule-id A numeric value. A unique numeric value to identify the rule.
rule-name An alphanumeric value. A unique name to identify the rule.
rule-action include, exclude, explicit Include or exclude the object or objects selected by the rule. If explicit is specified, you can select and include only one object that corresponds to an explicitly specified table and schema.
object-locator An object with the following parameters:
  • schema-name – The name of the schema.

  • table-name – The name of the table.

The name of each schema and table to which the rule applies. If the rule-action is either include or exclude, you can use the "%" percent sign as a wildcard for all or part of the value of each object-locator parameter. Thus, you can match these items:

  • A single table in a single schema

  • A single table in some or all schemas

  • Some or all tables in a single schema

  • Some or all tables in some or all schemas

If the rule-action is explicit, you can only specify the exact name of a single table and schema (with no wildcards).

load-order A positive integer. The maximum value is 2147483647. Indicates the priority for loading tables. Tables with higher values are loaded first.
filters An array of objects. One or more objects for filtering the source. You specify object parameters to filter on a single column in the source. You specify multiple objects to filter on multiple columns. For more information, see Using Source Filters.

Example Migrate All Tables in a Schema

The following example migrates all tables from a schema named Test in your source to your target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "Test", "table-name": "%" }, "rule-action": "include" } ] }

Example Migrate Some Tables in a Schema

The following example migrates all tables except those starting with DMS from a schema named Test in your source to your target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "Test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "selection", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "Test", "table-name": "DMS%" }, "rule-action": "exclude" } ] }

Example Migrate a Specified Single Table in Single Schema

The following example migrates the Customer table from the NewCust schema in your source to your target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "NewCust", "table-name": "Customer" }, "rule-action": "explicit" } ] }

Note

You can explicitly select on multiple tables and schemas by specifying multiple selection rules.

Example Migrate Tables in a Set Order

The following example migrates two tables. Table loadfirst (with priority 2) is migrated before table loadsecond.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "Test", "table-name": "loadfirst" }, "rule-action": "include", "load-order": "2" }, { "rule-type": "selection", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "Test", "table-name": "loadsecond" }, "rule-action": "include", "load-order": "1" } ] }

Transformation Rules and Actions

You use the transformation actions to specify any transformations you want to apply to the selected schema or table. Transformation rules are optional.

For table mapping rules that use the transformation rule type, the following values can be applied.

Parameter Possible Values Description
rule-type transformation, table-settings A value that applies the rule to the object specified by the selection rule.
rule-id A numeric value. A unique numeric value to identify the rule.
rule-name An alphanumeric value. A unique name to identify the rule.
object-locator An object with the following parameters:
  • schema-name – The name of the schema.

  • table-name – The name of the table.

  • table-tablespace-name – The name of an existing table tablespace.

  • index-tablespace-name – The name of an existing index tablespace.

The name of each schema, table, table tablespace, and index tablespace to which the rule applies. You can use the "%" percent sign as a wildcard for all or part of the value of each object-locator parameter. Thus, you can match these items:

  • A single table in a single schema

  • A single table in some or all schemas

  • Some or all tables in a single schema

  • Some or all tables in some or all schemas

In addition, the table-tablespace-name or index-tablespace-name parameter is only available to match an Oracle source endpoint. You can specify either table-tablespace-name or index-tablespace-name in a single rule, but not both. Thus, you can match either of the following items:

  • One, some, or all table tablespaces

  • One, some, or all index tablespaces

rule-action
  • rename

  • remove-column

  • convert-lowercase, convert-uppercase

  • add-prefix, remove-prefix, replace-prefix

  • add-suffix, remove-suffix, replace-suffix

  • define-primary-key

The transformation you want to apply to the object. All transformation rule actions are case-sensitive.
rule-target schema, table, column, table-tablespace, index-tablespace The type of object you are transforming.

The table-tablespace or index-tablespace value is only available for an Oracle target endpoint. Also, specify the value that matches the corresponding parameter you specify as part of the object-locator: table-tablespace-name or index-tablespace-name.

value An alphanumeric value that follows the naming rules for the target type. The new value for actions that require input, such as rename.
old-value An alphanumeric value that follows the naming rules for the target type. The old value for actions that require replacement, such as replace-prefix.
primary-key-def An object with the following parameters:
  • name – The name of a new primary key or unique index for the table.

  • (Optional) origin – The type of unique key to define: primary-key (the default) or unique-index.

  • columns – An array of strings listing the names of columns in the order they appear in the primary key or unique index.

With the rule action set to define-primary-key and the rule target set to table, this parameter defines the name, type, and content of a unique key on the transformed table. By default, the unique key is defined as a primary key.
parallel-load
  • partitions-auto

  • subpartitions-auto

  • none

A value that determines how to segment a table while loading. You can segment the table by partitions or subpartitions.

Example Rename a Schema

The following example renames a schema from Test in your source to Test1 in your target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "Test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "rename", "rule-target": "schema", "object-locator": { "schema-name": "Test" }, "value": "Test1" } ] }

Example Rename a Table

The following example renames a table from Actor in your source to Actor1 in your target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "Test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "rename", "rule-target": "table", "object-locator": { "schema-name": "Test", "table-name": "Actor" }, "value": "Actor1" } ] }

Example Rename a Column

The following example renames a column in table Actor from first_name in your source to fname in your target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "4", "rule-name": "4", "rule-action": "rename", "rule-target": "column", "object-locator": { "schema-name": "test", "table-name": "Actor", "column-name" : "first_name" }, "value": "fname" } ] }

Example Rename an Oracle Table Tablespace

The following example renames the table tablespace named SetSpace for a table named Actor in your Oracle source to SceneTblSpace in your Oracle target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "Play", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "rename", "rule-target": "table-tablespace", "object-locator": { "schema-name": "Play", "table-name": "Actor", "table-tablespace-name: "SetSpace" }, "value": "SceneTblSpace" } ] }

Example Rename an Oracle Index Tablespace

The following example renames the index tablespace named SetISpace for a table named Actor in your Oracle source to SceneIdxSpace in your Oracle target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "Play", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "rename", "rule-target": "table-tablespace", "object-locator": { "schema-name": "Play", "table-name": "Actor", "table-tablespace-name: "SetISpace" }, "value": "SceneIdxSpace" } ] }

Example Remove a Column

The following example transforms the table named Actor in your source to remove all columns starting with the characters col from it in your target endpoint.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "remove-column", "rule-target": "column", "object-locator": { "schema-name": "test", "table-name": "Actor", "column-name": "col%" } }] }

Example Convert to Lowercase

The following example converts a table name from ACTOR in your source to actor in your target endpoint.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "convert-lowercase", "rule-target": "table", "object-locator": { "schema-name": "test", "table-name": "ACTOR" } }] }

Example Convert to Uppercase

The following example converts all columns in all tables and all schemas from lowercase in your source to uppercase in your target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "convert-uppercase", "rule-target": "column", "object-locator": { "schema-name": "%", "table-name": "%", "column-name": "%" } } ] }

Example Add a Prefix

The following example transforms all tables in your source to add the prefix DMS_ to them in your target endpoint.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "add-prefix", "rule-target": "table", "object-locator": { "schema-name": "test", "table-name": "%" }, "value": "DMS_" }] }

Example Replace a Prefix

The following example transforms all columns containing the prefix Pre_ in your source to replace the prefix with NewPre_ in your target endpoint.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "replace-prefix", "rule-target": "column", "object-locator": { "schema-name": "%", "table-name": "%", "column-name": "%" }, "value": "NewPre_", "old-value": "Pre_" } ] }

Example Remove a Suffix

The following example transforms all tables in your source to remove the suffix _DMS from them in your target endpoint.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "remove-suffix", "rule-target": "table", "object-locator": { "schema-name": "test", "table-name": "%" }, "value": "_DMS" }] }

Example Define a Primary Key

The following example defines a primary key named ITEM-primary-key on three columns of the ITEM table migrated to your target endpoint.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "inventory", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "define-primary-key", "rule-target": "table", "object-locator": { "schema-name": "inventory", "table-name": "ITEM" }, "primary-key-def": { "name": ITEM-primary-key, "columns": [ "ITEM-NAME", "BOM-MODEL-NUM", "BOM-PART-NUM" ] } }] }

Example Define a Unique Index

The following example defines a unique index named ITEM-unique-idx on three columns of the ITEM table migrated to your target endpoint.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "inventory", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "define-primary-key", "rule-target": "table", "object-locator": { "schema-name": "inventory", "table-name": "ITEM" }, "primary-key-def": { "name": ITEM-unique-idx, "origin": unique-index, "columns": [ "ITEM-NAME", "BOM-MODEL-NUM", "BOM-PART-NUM" ] } }] }

Example Segment a Table for Loading

The following example segments a table in your source to load or unload the table more efficiently.

{ "rules": [{ "rule-type": "table-settings", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "table1" }, "parallel-load": { "type": "partitions-auto" } }] }

Using Source Filters

You can use source filters to limit the number and type of records transferred from your source to your target. For example, you can specify that only employees with a location of headquarters are moved to the target database. Filters are part of a selection rule. You apply filters on a column of data.

Source filters must follow these constraints:

  • A selection rule can have no filters or one or more filters.

  • Every filter can have one or more filter conditions.

  • If more than one filter is used, the list of filters is combined as if using an AND operator between the filters.

  • If more than one filter condition is used within a single filter, the list of filter conditions is combined as if using an OR operator between the filter conditions.

  • Filters are only applied when rule-action = 'include'.

  • Filters require a column name and a list of filter conditions. Filter conditions must have a filter operator and a value.

  • Column names, table names, and schema names are case-sensitive.

Creating Source Filter Rules in JSON

You can create source filters using the JSON filters parameter of a selection rule. The filters parameter specifies an array of one or more JSON objects. Each object has parameters that specify the source filter type, column name, and filter conditions. These filter conditions include one or more filter operators and filter values.

The following table shows the parameters for specifying source filtering in a filters object.

Parameter

Value

filter-type

source

column-name

The name of the source column you want the filter applied to. The name is case-sensitive.

filter-conditions

An array of one or more objects containing a filter-operator parameter and an appropriate value parameter, depending on the filter-operator value.

filter-operator

This parameter can have one of the following values:

  • ste – less than or equal to

  • gte – greater than or equal to

  • eq – equal to

  • between – equal to or between two values

value or

start-value

end-value

The value of the filter-operator parameter. If the filter-operator has a value other than between, use value. If the filter-operator is set to between, provide two values, one for start-value and one for end-value.

The following examples show some common ways to use source filters.

Example Single Filter

The following filter replicates all employees where empid >= 100 to the target database.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "employee" }, "rule-action": "include", "filters": [{ "filter-type": "source", "column-name": "empid", "filter-conditions": [{ "filter-operator": "gte", "value": "100" }] }] }] }

Example Multiple Filter Operators

The following filter applies multiple filter operators to a single column of data. The filter replicates all employees where (empid <=10) OR (empid is between 50 and 75) OR (empid >= 100) to the target database.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "employee" }, "rule-action": "include", "filters": [{ "filter-type": "source", "column-name": "empid", "filter-conditions": [{ "filter-operator": "ste", "value": "10" }, { "filter-operator": "between", "start-value": "50", "end-value": "75" }, { "filter-operator": "gte", "value": "100" }] }] }] }

Example Multiple Filters

The following filter applies multiple filters to two columns in a table. The filter replicates all employees where (empid <= 100) AND (dept= tech) to the target database.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "employee" }, "rule-action": "include", "filters": [{ "filter-type": "source", "column-name": "empid", "filter-conditions": [{ "filter-operator": "ste", "value": "100" }] }, { "filter-type": "source", "column-name": "dept", "filter-conditions": [{ "filter-operator": "eq", "value": "tech" }] }] }] }

Filtering by Time and Date

When selecting data to import, you can specify a date or time as part of your filter criteria. AWS DMS uses the date format YYYY-MM-DD and the time format YYYY-MM-DD HH:MM:SS for filtering. The AWS DMS comparison functions follow the SQLite conventions. For more information about SQLite data types and date comparisons, see Datatypes In SQLite Version 3 in the SQLite documentation.

The following example shows how to filter on a date. It replicates all employees where empstartdate >= January 1, 2002 to the target database.

Example Single Date Filter

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test", "table-name": "employee" }, "rule-action": "include", "filters": [{ "filter-type": "source", "column-name": "empstartdate", "filter-conditions": [{ "filter-operator": "gte", "value": "2002-01-01" }] }] }] }