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

The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

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, views, and schemas you want to work with. You can also perform table, view, and schema transformations and specify settings for how AWS DMS loads individual tables and views. You create table-mapping rules for these options using the following rule types:

  • selection rules – Identify the types and names of source tables, views, and schemas to load. For more information, see Selection Rules and Actions.

  • transformation rules – Specify certain changes or additions to particular source tables and schemas on the source before they are loaded on the target. For more information, see Transformation Rules and Actions.

  • table-settings rules – Specify how DMS tasks load the data for individual tables. For more information, see Table-Settings Rules and Operations.

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, views, and schemas you want to work with by using selection rules and actions. For table-mapping rules that use the selection rule type, you can apply the following values.

Parameter Possible Values Description
rule-type selection A selection rule. 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 A value that includes or excludes 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.

  • (Optional) table-typetable | view | all, to indicate if table-name refers only to tables, views, or both tables and views. The default is table.

    AWS DMS loads views only in a full-load task. If you have only full-load and change data capture (CDC) tasks, configure at least one full-load-only task to load your views.

The name of each schema and table or view to which the rule applies. You can also specify if a rule includes only tables, only views, or both tables and views. 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 for the schema-name and table-name parameter. Thus, you can match these items:

  • A single table or view in a single schema

  • A single table or view in some or all schemas

  • Some or all tables and views in a single schema

  • Some or all tables and views in some or all schemas

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

The supported sources for views include:
  • Oracle

  • Microsoft SQL Server

  • MySQL

  • PostgreSQL

  • IBM Db2 LUW

  • SAP Adaptive Server Enterprise (ASE)

Note

AWS DMS never loads a source view to a target view. A source view is loaded to an equivalent table on the target with the same name as the view on the source.

load-order A positive integer. The maximum value is 2,147,483,647. The priority for loading tables and views. Tables and views 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" } ] }

Example Migrate Some Views in a Schema

The following example migrates some views from a schema named Test in your source to equivalent tables in your target.

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

Example Migrate All Tables and Views in a Schema

The following example migrates all tables and views from a schema named report in your source to equivalent tables in your target.

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

Transformation Rules and Actions

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

For table-mapping rules that use the transformation rule type, you can apply the following values.

Parameter Possible Values Description
rule-type transformation A value that applies the rule to each object specified by the selection rule. Use transformation unless otherwise noted.
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 or view.

  • 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 or view, 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 or view in a single schema

  • A single table or view in some or all schemas

  • Some or all tables and views in a single schema

  • Some or all tables and views 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 or view.

  • (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.

This parameter can defines the name, type, and content of a unique key on the transformed table or view. It does so when the rule action is set to define-primary-key and the rule target is set to table. By default, the unique key is defined as a primary key.

Example Rename a Schema

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

{ "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.

{ "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.

{ "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.

{ "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.

{ "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.

{ "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.

{ "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.

{ "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.

{ "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" ] } }] }

Table-Settings Rules and Operations

You use table settings to specify any settings that you want to apply to the selected table or view for a specified operation. Table-settings rules are optional.

For table-mapping rules that use the table-settings rule type, you can apply the following parameters.

Parameter Possible Values Description
rule-type table-settings A value that applies the rule to a table or view 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 or view.

The name of a specific schema and table or view (no wildcards).

parallel-load An object with the following parameters:
  • type – Specifies whether parallel loading is enabled. If it is, this parameter also specifies the mechanism to identify the table or view partitions, subpartitions, or other segments to load in parallel. Partitions are segments that are already defined and identified by name in the source table or view. For Oracle endpoints only, subpartitions are an additional level of segments that are already defined and identified by name in the source table or view. You can identify other segments in the table-settings rule by specifying boundaries in the range of values for one or more table or view columns.

  • partitions – When type is partitions-list, this value specifies all the partitions to load in parallel.

  • subpartitions – For Oracle endpoints only, when type is partitions-list this value specifies all the subpartitions to load in parallel.

  • columns – When type is ranges, this value specifies the names of columns used to identify range-based segments to load in parallel.

  • boundaries – When type is ranges, this value specifies the values of the columns used to identify range-based segments to load in parallel.

A value that specifies a parallel load (multithreaded) operation on the table or view identified by the object-locator option. In this case, you can load in parallel in any of these ways:

  • By segments specified by all available partitions or subpartitions.

  • By selected partitions and subpartitions.

  • By range-based segments that you specify.

For more information on parallel load, see Using Parallel Load for Selected Tables and Views.

type One of the following for parallel-load:
  • partitions-auto – All partitions of the table or view are loaded in parallel. Every partition is allocated to its own thread.

  • subpartitions-auto – (Oracle endpoints only) All subpartitions of the table or view are loaded in parallel. Every subpartition is allocated to its own thread.

  • partitions-list – All specified partitions of the table or view are loaded in parallel. For Oracle endpoints only, all specified subpartitions of the table or view are loaded in parallel. Each partition and subpartition that you specify is allocated to its own thread. You specify the partitions and subpartitions to load in parallel by partition names (partitions) and subpartition names (subpartitions).

  • ranges – All specified segments of the table or view are loaded in parallel. Each table or view segment that you identify is allocated to its own thread. You specify these segments by column names (columns) and column values (boundaries).

  • none – The table or view is loaded in a single-threaded task (the default), regardless of its partitions or subpartitions. For more information, see Creating a Task.

The mechanism to identify the table or view partitions, subpartitions, or segments to load in parallel.
partitions When type is partitions-list, this is an array of strings that specify the names of partitions to load in parallel. The names of partitions to load in parallel.
subpartitions (Oracle endpoints only) When type is partitions-list, this is an array of strings that specifies the names of subpartitions to load in parallel. The names of subpartitions to load in parallel.
columns When type is ranges, an array of strings set to the names of columns that identify range-based table or view segments to load in parallel. The names of columns that identify range-based table or view segments to load in parallel.
boundaries When type is ranges, an array of column-value arrays. Each column-value array contains column values in the quantity and order specified by columns. A column-value array specifies the upper boundary of a table or view segment. Each additional column-value array adds the upper boundary for one additional table or view segment. All such range-based table or view segments load in parallel. Column values that identify range-based table or view partitions to load in parallel.
lob-settings An object with the following parameters:
  • mode – Specifies the migration handling mode for LOBs.

  • bulk-max-size – Specifies the maximum size of LOBs, depending on the mode setting.

A value that specifies LOB handling for the table or view identified by the object-locator option. The specified LOB handling overrides any task LOB settings for this table or view only. For more information on using the LOB settings parameters, see Specifying LOB Settings for a Selected Table or View.
mode Specifies the migration handling for LOBs in the specified table or view using the following values:
  • limited – (Default) This value sets migration to limited LOB mode, with all LOBs migrated inline together with all other column data types in the table or view. Use this value when replicating mostly small LOBs (100 MB or less). In addition, specify a bulk-max-size value (zero is invalid). All migrated LOBs greater than bulk-max-size are truncated to the size that you set.

  • unlimited – This value sets migration to full LOB mode. Use this value when all or most of the LOBs that you want to replicate are larger than 1 GB. If you specify a bulk-max-size value of zero, all LOBs are migrated in standard full LOB mode. In this form of unlimited mode, all LOBs are migrated separately from other column data types using a lookup from the source table or view. If you specify a bulk-max-size value greater than zero, all LOBs are migrated in combination full LOB mode. In this form of unlimited mode, LOBs greater than bulk-max-size are migrated using a source table or view lookup, similar to standard full LOB mode. Otherwise, LOBs up to and including this size are migrated inline, similar to limited LOB mode. No LOB is ever truncated in unlimited mode, regardless of the form you use.

  • none – All table or view LOBs are migrated according to the task LOB settings.

    For more information on the task LOB settings, see Target Metadata Task Settings.

    For more information on how to migrate LOBs and how to specify these task LOB settings, see Setting LOB Support for Source Databases in an AWS DMS Task.

The mechanism used to migrate LOBs.
bulk-max-size The effect of this value depends on the mode. The maximum size of LOBs in kilobyte increments. Specify this option only if you need to replicate small LOBs or if the target endpoint doesn't support unlimited LOB size.

Using Parallel Load for Selected Tables and Views

To speed up migration and make it more efficient, you can use parallel load for selected tables and views. In other words, you can migrate a single segmented table or view using several threads in parallel. To do this, AWS DMS splits a full-load task into threads, with each table segment allocated to its own thread.

Using this parallel-load process, you can first have multiple threads unload multiple tables and views in parallel from the source endpoint. You can then have multiple threads migrate and load the same tables and views in parallel to the target endpoint. For some database engines, you can segment the tables and views by existing partitions or subpartitions. Otherwise, you can segment any table or view by ranges of column values that you specify.

Parallel load is supported for the following source endpoints:

  • Oracle

  • Microsoft SQL Server

  • MySQL

  • PostgreSQL

  • IBM Db2

  • SAP Adaptive Server Enterprise (ASE)

Parallel load is supported for the following target endpoints:

  • Oracle

  • Microsoft SQL Server

  • MySQL

  • PostgreSQL

  • Amazon Redshift

  • SAP Adaptive Server Enterprise (ASE)

  • Amazon DynamoDB

  • Amazon Kinesis Data Streams

  • Amazon Elasticsearch Service

To specify the maximum number of tables and views to load in parallel, use the MaxFullLoadSubTasks task setting. To specify the maximum number of threads per table or view for a parallel-load task, use the ParallelLoadThreads task setting. To specify the buffer size for a parallel load task, use the ParallelLoadBufferSize task setting. The availability and settings of ParallelLoadThreadsand ParallelLoadBufferSize depend on the target endpoint.

For more information on the ParallelLoadThreads and ParallelLoadBufferSize settings, see Target Metadata Task Settings. For more information on the MaxFullLoadSubTasks setting, see Full-Load Task Settings. For information specific to target endpoints, see the related topics.

To use parallel load, create a table-mapping rule of type table-settings with the parallel-load option. Within the table-settings rule, you can specify the segmentation criteria for a single table or view that you want to load in parallel. To do so, set the type parameter of the parallel-load option to one of several options. How to do this depends on how you want to segment the table or view for parallel load:

  • By partitions – Load all existing table or view partitions using the partitions-auto type. Or load only selected partitions using the partitions-list type with a specified partitions array.

  • (Oracle endpoints only) By subpartitions – Load all existing table or view subpartitions using the subpartitions-auto type. Or load only selected subpartitions using the partitions-list type with a specified subpartitions array.

  • By segments that you define – Load table or view segments that you define by using column-value boundaries. To do so, use the ranges type with specified columns and boundaries arrays.

To identify additional tables or views to load in parallel, specify additional table-settings objects with parallel-load options.

In the following procedures, you can find out how to code JSON for each parallel-load type, from the simplest to the most complex.

To specify all table or view partitions, or all table or view subpartitions

  • Specify parallel-load with either the partitions-auto type or the subpartitions-auto type (but not both).

    Every table or view partition or subpartition is then automatically allocated to its own thread.

    Note

    Parallel load includes partitions or subpartitions only if they are already defined for the table or view.

To specify selected table or view partitions, subpartitions, or both

  1. Specify parallel-load with the partitions-list type.

  2. (Optional) Include partitions by specifying an array of partition names as the value of partitions.

    Each specified partition is then allocated to its own thread.

  3. (Optional, for Oracle endpoints only) Include subpartitions by specifying an array of subpartition names as the value of subpartitions.

    Each specified subpartition is then allocated to its own thread.

    Note

    Parallel load includes partitions or subpartitions only if they are already defined for the table or view.

You can specify table or view segments as ranges of column values. When you do so, be aware of these column characteristics:

  • Specifying indexed columns significantly improves performance.

  • You can specify up to 10 columns.

  • You can't use columns to define segment boundaries with the following AWS DMS data types: DOUBLE, FLOAT, BLOB, CLOB, and NCLOB

  • Records with null values aren't replicated.

To specify table or view segments as ranges of column values

  1. Specify parallel-load with the ranges type.

  2. Define a boundary between table or view segments by specifying an array of column names as the value of columns. Do this for every column for which you want to define a boundary between table or view segments.

    The order of columns is significant. The first column is the most significant and the last column is least significant in defining each boundary, as described following.

  3. Define the data ranges for all the table or view segments by specifying a boundary array as the value of boundaries. A boundary array is an array of column-value arrays. To do so, take the following steps:

    1. Specify each element of a column-value array as a value that corresponds to each column. A column-value array represents the upper boundary of each table or view segment that you want to define. Specify each column in the same order that you specified that column in the columns array.

      Note

      Enter values for DATE columns in the format supported by the source.

    2. Specify each column-value array as the upper boundary, in order, of each segment from the bottom to the next-to-top segment of the table or view. If any rows exist above the top boundary that you specify, these rows complete the top segment of the table or view. Thus, the number of range-based segments is potentially one more than the number of segment boundaries in the boundary array. Each such range-based segment is allocated to its own thread.

      Note

      All of the non-null data is replicated, even if you don't define data ranges for all of the columns in the table or view.

    For example, suppose you define three column-value arrays for columns COL1, COL2, and COL3 as follows.

    COL1 COL2 COL3
    10 30 105
    20 20 120
    100 12 99

    You have defined three segment boundaries for a possible total of four segments.

    To identify the ranges of rows to replicate for each segment, the replication instance applies a search to these three columns for each of the four segments. The search is like the following:

    Segment 1

    Replicate all rows where the following is true: The first two-column values are less than or equal to their corresponding Segment 1 upper boundary values. Also, the values of the third column are less than its Segment 1 upper boundary value.

    Segment 2

    Replicate all rows (except Segment 1 rows) where the following is true: The first two-column values are less than or equal to their corresponding Segment 2 upper boundary values. Also, the values of the third column are less than its Segment 2 upper boundary value.

    Segment 3

    Replicate all rows (except Segment 2 rows) where the following is true: The first two-column values are less than or equal to their corresponding Segment 3 upper boundary values. Also, the values of the third column are less than its Segment 3 upper boundary value.

    Segment 4

    Replicate all remaining rows (except the Segment 1, 2, and 3 rows).

    In this case, the replication instance creates a WHERE clause to load each segment as follows:

    Segment 1

    ((COL1 < 10) OR ((COL1 = 10) AND (COL2 < 30)) OR ((COL1 = 10) AND (COL2 = 30) AND (COL3 < 105)))

    Segment 2

    NOT ((COL1 < 10) OR ((COL1 = 10) AND (COL2 < 30)) OR ((COL1 = 10) AND (COL2 = 30) AND (COL3 < 105))) AND ((COL1 < 20) OR ((COL1 = 20) AND (COL2 < 20)) OR ((COL1 = 20) AND (COL2 = 20) AND (COL3 < 120)))

    Segment 3

    NOT ((COL1 < 20) OR ((COL1 = 20) AND (COL2 < 20)) OR ((COL1 = 20) AND (COL2 = 20) AND (COL3 < 120))) AND ((COL1 < 100) OR ((COL1 = 100) AND (COL2 < 12)) OR ((COL1 = 100) AND (COL2 = 12) AND (COL3 < 99)))

    Segment 4

    NOT ((COL1 < 100) OR ((COL1 = 100) AND (COL2 < 12)) OR ((COL1 = 100) AND (COL2 = 12) AND (COL3 < 99)))

Specifying LOB Settings for a Selected Table or View

You can set task LOB settings for one or more tables by creating a table-mapping rule of type table-settings with the lob-settings option for one or more table-settings objects.

Specifying LOB settings for selected tables or views is supported for the following source endpoints:

  • Oracle

  • Microsoft SQL Server

  • MySQL

  • PostgreSQL

  • IBM Db2, depending on the mode and bulk-max-size settings, described following

  • SAP Adaptive Server Enterprise (ASE), depending on the mode and bulk-max-size settings, as described following

Specifying LOB settings for selected tables or views is supported for the following target endpoints:

  • Oracle

  • Microsoft SQL Server

  • MySQL

  • PostgreSQL

  • SAP ASE, depending on the mode and bulk-max-size settings, as described following

Note

You can use LOB data types only with tables and views that include a primary key.

To use LOB settings for a selected table or view, you create a table-mapping rule of type table-settings with the lob-settings option. Doing this specifies LOB handling for the table or view identified by the object-locator option. Within the table-settings rule, you can specify a lob-settings object with the following parameters:

  • mode – Specifies the mechanism for handling LOB migration for the selected table or view as follows:

    • limited – The default limited LOB mode is the fastest and most efficient mode. Use this mode only if all of your LOBs are small (within 100 MB in size) or the target endpoint doesn't support an unlimited LOB size. In addition, if you use limited all LOBs need to be within the size that you set for bulk-max-size.

      In this mode for a full load task, the replication instance migrates all LOBs inline together with other column data types as part of main table or view storage. However, the instance truncates any migrated LOB larger than your bulk-max-size value to the specified size. For a change data capture (CDC) load task, the instance migrates all LOBs using a source table lookup, as in standard full LOB mode (see the following). It does so regardless of LOB size.

      Note

      You can migrate views for full-load tasks only.

    • unlimited – The migration mechanism for full LOB mode depends on the value you set for bulk-max-size as follows:

      • Standard full LOB mode – When you set bulk-max-size to zero, the replication instance migrates all LOBs using standard full LOB mode. This mode requires a lookup in the source table or view to migrate every LOB, regardless of size. This approach typically results in a much slower migration than for limited LOB mode. Use this mode only if all or most of your LOBs are large (1 GB or larger).

      • Combination full LOB mode – When you set bulk-max-size to a nonzero value, this full LOB mode uses a combination of limited LOB mode and standard full LOB mode. That is for a full load task, if a LOB size is within your bulk-max-size value, the instance migrates the LOB inline as in limited LOB mode. If the LOB size is greater than this value, the instance migrates the LOB using a source table or view lookup as in standard full LOB mode. For a change data capture (CDC) load task, the instance migrates all LOBs using a source table lookup, as in standard full LOB mode (see the following). It does so regardless of LOB size.

        Note

        You can migrate views for full-load tasks only.

        This mode results in a migration speed that is a compromise between the faster, limited LOB mode and the slower, standard full LOB mode. Use this mode only when you have a mix of small and large LOBs, and most of the LOBs are small.

        This combination full LOB mode is available only for the following endpoints:

        • IBM Db2 as source

        • SAP ASE as source or target

      Regardless of the mechanism you specify for unlimited mode, the instance migrates all LOBs fully, without truncation.

    • none – The replication instance migrates LOBs in the selected table or view using your task LOB settings. Use this option to help compare migration results with and without LOB settings for the selected table or view.

    If the specified table or view has LOBs included in the replication, you can set the BatchApplyEnabled task setting to true only when using limited LOB mode.

    In some cases, you might set BatchApplyEnabled to true and BatchApplyPreserveTransaction to false. In these cases, the instance sets BatchApplyPreserveTransaction to true if the table or view has LOBs and the source and target endpoints are Oracle.

  • bulk-max-size – Set this value to a zero or non-zero value in kilobytes, depending on the mode as described for the previous items. In limited mode, you must set a nonzero value for this parameter.

    The instance converts LOBs to binary format. Therefore, to specify the largest LOB you need to replicate, multiply its size by three. For example, if your largest LOB is 2 MB, set bulk-max-size to 6,000 (6 MB).

Table-Settings Examples

Following, you can find some examples that demonstrate the use of table settings.

Example Load a Table Segmented by Partitions

The following example loads a SALES table in your source more efficiently by loading it in parallel based on all its partitions.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "HR", "table-name": "SALES" }, "parallel-load": { "type": "partitions-auto" } } ] }

Example Load a Table Segmented by Subpartitions

The following example loads a SALES table in your Oracle source more efficiently by loading it in parallel based on all its subpartitions.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "HR", "table-name": "SALES" }, "parallel-load": { "type": "subpartitions-auto" } } ] }

Example Load a Table Segmented by a List of Partitions

The following example loads a SALES table in your source by loading it in parallel by a particular list of partitions. Here, the specified partitions are named after values starting with portions of the alphabet, for example ABCD, EFGH, and so on.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "HR", "table-name": "SALES" }, "parallel-load": { "type": "partitions-list", "partitions": [ "ABCD", "EFGH", "IJKL", "MNOP", "QRST", "UVWXYZ" ] } } ] }

Example Load an Oracle Table Segmented by a Selected List of Partitions and Subpartitions

The following example loads a SALES table in your Oracle source by loading it in parallel by a selected list of partitions and subpartitions. Here, the specified partitions are named after values starting with portions of the alphabet, for example ABCD, EFGH, and so on. The specified subpartitions are named after values starting with numerals, for example 01234 and 56789.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "HR", "table-name": "SALES" }, "parallel-load": { "type": "partitions-list", "partitions": [ "ABCD", "EFGH", "IJKL", "MNOP", "QRST", "UVWXYZ" ], "subpartitions": [ "01234", "56789" ] } } ] }

Example Load a Table Segmented by Ranges of Column Values

The following example loads a SALES table in your source by loading it in parallel by segments specified by ranges of the SALES_NO and REGION column values.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "HR", "table-name": "SALES" }, "parallel-load": { "type": "ranges", "columns": [ "SALES_NO", "REGION" ], "boundaries": [ [ "1000", "NORTH" ], [ "3000", "WEST" ] ] } } ] }

Here, two columns are specified for the segment ranges with the names, SALES_NO and REGION. Two boundaries are specified with two sets of column values (["1000","NORTH"] and ["3000","WEST"]).

These two boundaries thus identify the following three table segments to load in parallel:

Segment 1

Rows with SALES_NO less than or equal to 1,000 and REGION less than "NORTH". In other words, sales numbers up to 1,000 in the EAST region.

Segment 2

Rows other than Segment 1 with SALES_NO less than or equal to 3,000 and REGION less than "WEST". In other words, sales numbers over 1,000 up to 3,000 in the NORTH and SOUTH regions.

Segment 3

All remaining rows other than Segment 1 and Segment 2. In other words, sales numbers over 3,000 in the WEST region.

Example Load Two Tables: One Segmented by Ranges and Another Segmented by Partitions

The following example loads a SALES table in parallel by segment boundaries that you identify. It also loads an ORDERS table in parallel by all of its partitions, as with previous examples.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "HR", "table-name": "SALES" }, "parallel-load": { "type": "ranges", "columns": [ "SALES_NO", "REGION" ], "boundaries": [ [ "1000", "NORTH" ], [ "3000", "WEST" ] ] } }, { "rule-type": "table-settings", "rule-id": "3", "rule-name": "3", "object-locator": { "schema-name": "HR", "table-name": "ORDERS" }, "parallel-load": { "type": "partitions-auto" } } ] }

Example Load a Table with LOBs Using Limited LOB Mode

The following example loads an ITEMS table including LOBs in your source using limited LOB mode (the default) with a maximum nontruncated size of 100 MB. Any LOBs that are larger than this size are truncated to 100 MB. All LOBs are loaded inline with all other column data types.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "INV", "table-name": "ITEMS" }, "lob-settings": { "bulk-max-size": "100000" } } ] }

Example Load a Table with LOBs Using Standard Full LOB Mode

The following example loads an ITEMS table in your source, including all its LOBs without truncation, using standard full LOB mode. All LOBs, regardless of size, are loaded separately from other data types using a lookup for each LOB in the source table.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "INV", "table-name": "ITEMS" }, "lob-settings": { "mode": "unlimited" "bulk-max-size": "0" } } ] }

Example Load a Table with LOBs Using Combination Full LOB Mode

The following example loads an ITEMS table in your source, including all its LOBs without truncation, using combination full LOB mode. All LOBs within 100 MB in size are loaded inline along with other data types, as in limited LOB mode. All LOBs over 100 MB in size are loaded separately from other data types. This separate load uses a lookup for each such LOB in the source table, as in standard full LOB mode.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "INV", "table-name": "ITEMS" }, "lob-settings": { "mode": "unlimited" "bulk-max-size": "100000" } } ] }

Example Load a Table with LOBs Using the Task LOB Settings

The following example loads an ITEMS table in your source, including all LOBs, using its task LOB settings. The bulk-max-size setting of 100 MB is ignored and left only for a quick reset to limited or unlimited mode.

{ "rules": [{ "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "table-settings", "rule-id": "2", "rule-name": "2", "object-locator": { "schema-name": "INV", "table-name": "ITEMS" }, "lob-settings": { "mode": "none" "bulk-max-size": "100000" } } ] }

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, view 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" }] }] }] }