# 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.
When working with table mapping, you can use filters to specify data that you want replicated from table columns. In addition, you can use transformations to modify selected schemas, tables, or views before they are written to the target database.
**Topics**
+ [
# Specifying table selection and transformations rules from the console
](CHAP_Tasks.CustomizingTasks.TableMapping.Console.md)
+ [
# Specifying table selection and transformations rules using JSON
](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.md)
+ [
# Selection rules and actions
](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Selections.md)
+ [
# Wildcards in table mapping
](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Wildcards.md)
+ [
# Transformation rules and actions
](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.md)
+ [
# Using transformation rule expressions to define column content
](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.md)
+ [
# Table and collection settings rules and operations
](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.md)
+ [
# Using data masking to hide sensitive information
](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Masking.md)
**Note**
When working with table mapping for a MongoDB source endpoint, you can use filters to specify data that you want replicated, and specify a database name in place of the `schema_name`. Or, you can use the default `"%"`.
# Specifying table selection and transformations rules 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.
**Note**
AWS DMS doesn't support more than one transformation rule per schema level, table level, or column level.
The following procedure shows how to set up selection rules, based on a table called **Customers** in a schema called **EntertainmentAgencySample**.
**To specify a table selection, filter criteria, and transformations using the console**
1. Sign in to the AWS Management Console and open the AWS DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.aws.amazon.com/dms/v2/).
If you are signed in as an IAM user, make sure that you have the appropriate permissions to access AWS DMS. For more information about the permissions required, see [IAM permissions needed to use AWS DMS](security-iam.md#CHAP_Security.IAMPermissions).
1. On the **Dashboard** page, choose **Database migration tasks**.
1. Choose **Create Task**.
1. In the **Task configuration** section, enter the task information, including **Task identifier**, **Replication instance**, **Source database endpoint**, **Target database endpoint**, and **Migration type**.
![\[Schema and table selection\]](http://docs.aws.amazon.com/dms/latest/userguide/images/datarep-create-task-20.png)
1. In the **Table mapping** section, enter the schema name and table name. You can use "%" as a wildcard value when specifying the schema name or the table name. For information about other wildcards you can use, see [Wildcards in table mapping](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Wildcards.md). Specify the action to be taken, to include or exclude data defined by the filter.
![\[Schema and table selection\]](http://docs.aws.amazon.com/dms/latest/userguide/images/datarep-Tasks-selecttransfrm.png)
1. Specify filter information using the **Add column filter** and the **Add condition **links.
1. Choose **Add column filter** to specify a column and conditions.
1. 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\]](http://docs.aws.amazon.com/dms/latest/userguide/images/datarep-Tasks-filter.png)
1. When you have created the selections you want, choose **Add new selection rule**.
1. After you have created at least one selection rule, you can add a transformation to the task. Choose **add transformation rule**.
![\[transformation rule\]](http://docs.aws.amazon.com/dms/latest/userguide/images/datarep-Tasks-transform1.png)
1. 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\]](http://docs.aws.amazon.com/dms/latest/userguide/images/datarep-Tasks-transform2.png)
1. Choose **Add transformation rule**.
1. Choose **Create task**.
**Note**
AWS DMS doesn't support more than one transformation rule per schema level, table level, or column level.
# Specifying table selection and transformations rules using JSON
To specify the table mappings that you want to apply during migration, you can create a JSON file. If you create a migration task using the console, you can browse for this JSON file or enter the JSON directly into the table mapping box. If you use the CLI or API to perform migrations, you can specify this file using the `TableMappings` parameter of the `CreateReplicationTask` or `ModifyReplicationTask` API operation.
AWS DMS can only process table mapping JSON files up to 2 MB in size. We recommend that you keep the mapping rule JSON file size below the 2 MB limit while working with DMS tasks. This prevents unexpected errors during task creation or modification. When a mapping rule file exceeds the 2 MB limit, we recommend that you split the tables across multiple tasks to reduce the size of the mapping rule file so that it stays below this limit.
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](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Selections.md).
+ `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](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.md).
Also, to define content of new and existing columns, you can use an expression within a transformation rule. For more information, see [Using transformation rule expressions to define column content](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.md).
+ `table-settings` rules – Specify how DMS tasks load the data for individual tables. For more information, see [Table and collection settings rules and operations](CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.md).
**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](CHAP_Target.S3.md#CHAP_Target.S3.Tagging).
For the targets following, you can specify how and where selected schemas and tables are migrated to the target using the `object-mapping` rule type:
Amazon DynamoDB – For more information, see [Using object mapping to migrate data to DynamoDB](CHAP_Target.DynamoDB.md#CHAP_Target.DynamoDB.ObjectMapping).
Amazon Kinesis – For more information, see [Using object mapping to migrate data to a Kinesis data stream](CHAP_Target.Kinesis.md#CHAP_Target.Kinesis.ObjectMapping).
Apache Kafka – For more information, see [Using object mapping to migrate data to a Kafka topic](CHAP_Target.Kafka.md#CHAP_Target.Kafka.ObjectMapping).
# 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.
**Warning**
Do not to include any sensitive data within these rules.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Selections.html)
**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"
}
]
}
```
You can explicitly select on multiple tables and schemas by specifying multiple selection rules.
**Example Migrate tables in a set order**
Tables and views are migrated according to their load-order values, with higher values receiving priority in the migration sequence. The following example migrates two tables, `loadfirst` with a priority value of 2 and `loadsecond` with a priority value of 1, the migration task would first process the `loadfirst` table before proceeding to the `loadsecond` table. This prioritization mechanism ensures that dependencies between database objects are respected during the migration process.
```
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "Test",
"table-name": "loadsecond"
},
"rule-action": "include",
"load-order": "1"
},
{
"rule-type": "selection",
"rule-id": "2",
"rule-name": "2",
"object-locator": {
"schema-name": "Test",
"table-name": "loadfirst"
},
"rule-action": "include",
"load-order": "2"
}
]
}
```
**Note**
`load-order` is applicable for table initialization. The load of a successive table won't wait for a previous table load to complete if `MaxFullLoadSubTasks` is greater than 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"
}
]
}
```
# Wildcards in table mapping
This section describes wildcards you can use when specifying the schema and table names for table mapping.
| Wildcard | Matches |
| --- |--- |
| % | Zero or more characters |
| \$1 | A single character |
| [\$1] | A literal underscore character |
| [ab] | A set of characters. For example, [ab] matches either 'a' or 'b'. |
| [a-d] | A range of characters. For example,[a-d] matches either 'a', 'b', 'c', or 'd'. |
For Oracle source and target endpoints, you can use the `escapeCharacter` extra connection attribute to specify an escape character. An escape character allows you to use a specified wildcard character in expressions as if it was not wild. For example, `escapeCharacter=#` allows you to use '\$1' to make a wildcard character act as an ordinary character in an expression as in the this sample code.
```
{
"rules": [
{
"rule-type": "selection",
"rule-id": "542485267",
"rule-name": "542485267",
"object-locator": { "schema-name": "ROOT", "table-name": "TEST#_T%" },
"rule-action": "include",
"filters": []
}
]
}
```
Here, the '\$1' escape character makes the '\$1' wildcard character act as a normal character. AWS DMS selects tables in the schema named `ROOT`, where each table has a name with `TEST_T` as its prefix.
# 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.
## Limitations
+ You cannot apply more than one transformation rule action against the same object (schema, table, column, table-tablespace, or index-tablespace). You can apply several transformation rule actions on any level as long as each transformation action is applied against a different object. However, this restriction is not applicable when using data masking transformation rules where you can have another transformation like `ADD-COLUMN` or `CHANGE-DATA-TYPE` for the same column.
+ Table names and column names in transformation rules are case-sensitive. For example, you must provide table names and column names for an Oracle or Db2 database in upper-case.
+ Transformations are not supported for column names with Right-to-Left languages.
+ Transformations cannot be performed on columns that contain special characters (e.g. \$1, \$1, /, -) in their name.
+ The only supported transformation for columns that are mapped to BLOB/CLOB data types is to drop the column on the target.
+ AWS DMS doesn't support replicating two source tables to a single target table. AWS DMS replicates records from table to table, and from column to column, according to the replication task’s transformation rules. The object names must be unique to prevent overlapping.
For example, a source table has a column named `ID` and the corresponding target table has a pre-existing column called `id`. If a rule uses an `ADD-COLUMN` statement to add a new column called `id`, and a SQLite statement to populate the column with custom values, this creates a duplicate, ambiguous object named `id` and is not supported.
+ When creating a transformation rule, we recommend using the `data-type` parameter only when the selection rules specify multiple columns, for instance, when you set `column-name` to `%`. We don't recommend using `data-type` for selecting a single column.
+ AWS DMS does not support transformation rules where source and target objects (tables) are on the same database/schema. Using the same table as both source and target in a transformation rule can lead to unexpected and potentially harmful results, including but not limited to unintended alterations to the table data, modification of table structures or even tables getting dropped.
## Values
For table-mapping rules that use the transformation rule type, you can apply the following values.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html)
## Examples
**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 Add a column**
The following example adds a `datetime` column to the table `Actor` in schema `test`.
```
{
"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-column",
"rule-target": "column",
"object-locator": {
"schema-name": "test",
"table-name": "actor"
},
"value": "last_updated",
"data-type": {
"type": "datetime",
"precision": 6
}
}
]
}
```
**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"
]
}
}]
}
```
**Example Change data type of target column**
The following example changes the data type of a target column named `SALE_AMOUNT` from an existing data type to `int8`.
```
{
"rule-type": "transformation",
"rule-id": "1",
"rule-name": "RuleName 1",
"rule-action": "change-data-type",
"rule-target": "column",
"object-locator": {
"schema-name": "dbo",
"table-name": "dms",
"column-name": "SALE_AMOUNT"
},
"data-type": {
"type": "int8"
}
}
```
**Example Add a before image column**
For a source column named `emp_no`, the transformation rule in the example following adds a new column named `BI_emp_no` in the target.
```
{
"rules": [{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "%",
"table-name": "%"
},
"rule-action": "include"
},
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-target": "column",
"object-locator": {
"schema-name": "%",
"table-name": "employees"
},
"rule-action": "add-before-image-columns",
"before-image-def": {
"column-prefix": "BI_",
"column-suffix": "",
"column-filter": "pk-only"
}
}
]
}
```
Here, the following statement populates a `BI_emp_no` column in the corresponding row with 1.
```
UPDATE employees SET emp_no = 3 WHERE BI_emp_no = 1;
```
When writing CDC updates to supported AWS DMS targets, the `BI_emp_no` column makes it possible to tell which rows have updated values in the `emp_no` column.
# Using transformation rule expressions to define column content
To define content for new and existing columns, you can use an expression within a transformation rule. For example, using expressions you can add a column or replicate source table headers to a target. You can also use expressions to flag records on target tables as inserted, updated, or deleted at the source.
**Topics**
+ [
## Adding a column using an expression
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-adding)
+ [
## Flagging target records using an expression
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-Flagging)
+ [
## Replicating source table headers using expressions
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-Headers)
+ [
## Using SQLite functions to build expressions
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-SQLite)
+ [
## Adding metadata to a target table using expressions
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-Metadata)
## Adding a column using an expression
To add columns to tables using an expression in a transformation rule, use an `add-column` rule action and a `column` rule target.
The following example adds a new column to the `ITEM` table. It sets the new column name to `FULL_NAME`, with a data type of `string`, 50 characters long. The expression concatenates the values of two existing columns, `FIRST_NAME` and `LAST_NAME`, to evaluate to `FULL_NAME`. The `schema-name`, `table-name`, and expression parameters refer to objects in the source database table. `Value` and the `data-type` block refer to objects in the target database table.
```
{
"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-column",
"rule-target": "column",
"object-locator": {
"schema-name": "Test",
"table-name": "ITEM"
},
"value": "FULL_NAME",
"expression": "$FIRST_NAME||'_'||$LAST_NAME",
"data-type": {
"type": "string",
"length": 50
}
}
]
}
```
## Flagging target records using an expression
To flag records in target tables as inserted, updated, or deleted in the source table, use an expression in a transformation rule. The expression uses an `operation_indicator` function to flag records. Records deleted from the source aren't deleted from the target. Instead, the target record is flagged with a user-provided value to indicate that it was deleted from the source.
**Note**
The `operation_indicator` function works only on tables that have a primary key on both source and target database.
For example, the following transformation rule first adds a new `Operation` column to a target table. It then updates the column with the value `D` whenever a record is deleted from a source table.
```
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-target": "column",
"object-locator": {
"schema-name": "%",
"table-name": "%"
},
"rule-action": "add-column",
"value": "Operation",
"expression": "operation_indicator('D', 'U', 'I')",
"data-type": {
"type": "string",
"length": 50
}
}
```
## Replicating source table headers using expressions
By default, headers for source tables aren't replicated to the target. To indicate which headers to replicate, use a transformation rule with an expression that includes the table column header.
You can use the following column headers in expressions.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html)
The following example adds a new column to the target by using the stream position value from the source. For SQL Server, the stream position value is the LSN for the source endpoint. For Oracle, the stream position value is the SCN for the source endpoint.
```
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-target": "column",
"object-locator": {
"schema-name": "%",
"table-name": "%"
},
"rule-action": "add-column",
"value": "transact_id",
"expression": "$AR_H_STREAM_POSITION",
"data-type": {
"type": "string",
"length": 50
}
}
```
The following example adds a new column to the target that has a unique incrementing number from the source. This value represents a 35 digit unique number at task level. The first 16 digits are part of a timestamp, and the last 19 digits are the record\$1id number incremented by the DBMS.
```
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-target": "column",
"object-locator": {
"schema-name": "%",
"table-name": "%"
},
"rule-action": "add-column",
"value": "transact_id",
"expression": "$AR_H_CHANGE_SEQ",
"data-type": {
"type": "string",
"length": 50
}
}
```
## Using SQLite functions to build expressions
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.
**Note**
Instead of the concept of tables and views, MongoDB and DocumentDB databases store data records as documents that are gathered together in *collections*. So then, when migrating from a MongoDB or DocumentDB source, consider the range segmentation type of parallel load settings for selected *collections* rather than tables and views.
**Topics**
+ [
### Using a CASE expression
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-SQLite.CASE)
+ [
### Examples
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-SQLite.Ex)
Following, you can find string functions that you can use to build transformation rule expressions.
| String functions | Description |
| --- | --- |
| `lower(x)` | The `lower(x)` function returns a copy of string *`x`* with all characters converted to lowercase. The default, built-in `lower` function works for ASCII characters only. |
| `upper(x)` | The `upper(x)` function returns a copy of string *`x`* with all characters converted to uppercase. The default, built-in `upper` function works for ASCII characters only. |
| `ltrim(x,y)` | The `ltrim(x,y)` function returns a string formed by removing all characters that appear in y from the left side of x. If there is no value for y, `ltrim(x)` removes spaces from the left side of x. |
| `replace(x,y,z)` | The `replace(x,y,z)` function returns a string formed by substituting string z for every occurrence of string y in string x. |
| `rtrim(x,y)` | The `rtrim(x,y)` function returns a string formed by removing all characters that appear in y from the right side of x. If there is no value for y, `rtrim(x)` removes spaces from the right side of x. |
| `substr(x,y,z)` | The `substr(x,y,z)` function returns a substring of the input string `x` that begins with the `y`th character, and which is *`z`* characters long. If *`z`* is omitted, `substr(x,y)` returns all characters through the end of string `x` beginning with the `y`th character. The leftmost character of `x` is number 1. If *`y`* is negative, the first character of the substring is found by counting from the right rather than the left. If *`z`* is negative, then the `abs(z)` characters preceding the `y`th character are returned. If `x` is a string, then the characters' indices refer to actual UTF-8 characters. If `x` is a BLOB, then the indices refer to bytes. |
| trim(x,y) | The `trim(x,y)` function returns a string formed by removing all characters that appear in `y` from both sides of `x`. If there is no value for `y`, `trim(x)` removes spaces from both sides of `x`. |
Following, you can find LOB functions that you can use to build transformation rule expressions.
| LOB functions | Description |
| --- | --- |
| `hex(x)` | The `hex` function receives a BLOB as an argument and returns an uppercase hexadecimal string version of the BLOB content. |
| `randomblob (N)` | The `randomblob(N)` function returns an `N`-byte BLOB that contains pseudorandom bytes. If *N* is less than 1, a 1-byte random BLOB is returned. |
| `zeroblob(N)` | The `zeroblob(N)` function returns a BLOB that consists of `N` bytes of 0x00. |
Following, you can find numeric functions that you can use to build transformation rule expressions.
| Numeric functions | Description |
| --- | --- |
| `abs(x)` | The `abs(x)` function returns the absolute value of the numeric argument `x`. The `abs(x)` function returns NULL if *x* is NULL. The `abs(x)` function returns 0.0 if **x** is a string or BLOB that can't be converted to a numeric value. |
| `random()` | The `random` function returns a pseudorandom integer between -9,223,372,036,854,775,808 and \$19,223,372,036,854,775,807. |
| `round (x,y)` | The `round (x,y)` function returns a floating-point value *x* rounded to *y* digits to the right of the decimal point. If there is no value for *y*, it's assumed to be 0. |
| `max (x,y...)` | The multiargument `max` function returns the argument with the maximum value, or returns NULL if any argument is NULL. The `max` function searches its arguments from left to right for an argument that defines a collating function. If one is found, it uses that collating function for all string comparisons. If none of the arguments to `max` define a collating function, the `BINARY` collating function is used. The `max` function is a simple function when it has two or more arguments, but it operates as an aggregate function if it has a single argument. |
| `min (x,y...)` | The multiargument `min` function returns the argument with the minimum value. The `min` function searches its arguments from left to right for an argument that defines a collating function. If one is found, it uses that collating function for all string comparisons. If none of the arguments to `min` define a collating function, the `BINARY` collating function is used. The `min` function is a simple function when it has two or more arguments, but it operates as an aggregate function if it has a single argument. |
Following, you can find NULL check functions that you can use to build transformation rule expressions.
| NULL check functions | Description |
| --- | --- |
| `coalesce (x,y...)` | The `coalesce` function returns a copy of its first non-NULL argument, but it returns NULL if all arguments are NULL. The coalesce function has at least two arguments. |
| `ifnull(x,y)` | The `ifnull` function returns a copy of its first non-NULL argument, but it returns NULL if both arguments are NULL. The `ifnull` function has exactly two arguments. The `ifnull` function is the same as `coalesce` with two arguments. |
| `nullif(x,y)` | The `nullif(x,y)` function returns a copy of its first argument if the arguments are different, but it returns NULL if the arguments are the same. The `nullif(x,y)` function searches its arguments from left to right for an argument that defines a collating function. If one is found, it uses that collating function for all string comparisons. If neither argument to nullif defines a collating function, then the `BINARY` collating function is used. |
Following, you can find date and time functions that you can use to build transformation rule expressions.
| Date and time functions | Description |
| --- | --- |
| `date(timestring, modifier, modifier...)` | The `date` function returns the date in the format YYYY-MM-DD. |
| `time(timestring, modifier, modifier...)` | The `time` function returns the time in the format HH:MM:SS. |
| `datetime(timestring, modifier, modifier...)` | The `datetime` function returns the date and time in the format YYYY-MM-DD HH:MM:SS. |
| `julianday(timestring, modifier, modifier...)` | The `julianday` function returns the number of days since noon in Greenwich on November 24, 4714 B.C. |
| `strftime(format, timestring, modifier, modifier...)` | The `strftime` function returns the date according to the format string specified as the first argument, using one of the following variables: `%d`: day of month `%H`: hour 00–24 `%f`: \$1\$1 fractional seconds SS.SSS `%j`: day of year 001–366 `%J`: \$1\$1 Julian day number `%m`: month 01–12 `%M`: minute 00–59 `%s`: seconds since 1970-01-01 `%S`: seconds 00–59 `%w`: day of week 0–6 sunday==0 `%W`: week of year 00–53 `%Y`: year 0000–9999 `%%`: % |
Following, you can find a hash function that you can use to build transformation rule expressions.
| Hash function | Description |
| --- | --- |
| `hash_sha256(x)` | The `hash` function generates a hash value for an input column (using the SHA-256 algorithm) and returns the hexadecimal value of the generated hash value. To use the `hash` function in an expression, add `hash_sha256(x)` to the expression and replace *`x`* with the source column name. |
### Using a CASE expression
The SQLite `CASE` expression evaluates a list of conditions and returns an expression based on the result. Syntax is shown following.
```
CASE case_expression
WHEN when_expression_1 THEN result_1
WHEN when_expression_2 THEN result_2
...
[ ELSE result_else ]
END
# Or
CASE
WHEN case_expression THEN result_1
WHEN case_expression THEN result_2
...
[ ELSE result_else ]
END
```
### Examples
**Example of adding a new string column to the target table using a case condition**
The following example transformation rule adds a new string column, `emp_seniority`, to the target table, `employee`. It uses the SQLite `round` function on the salary column, with a case condition to check if the salary equals or exceeds 20,000. If it does, the column gets the value `SENIOR`, and anything else has the value `JUNIOR`.
```
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-action": "add-column",
"rule-target": "column",
"object-locator": {
"schema-name": "public",
"table-name": "employee"
},
"value": "emp_seniority",
"expression": " CASE WHEN round($emp_salary)>=20000 THEN ‘SENIOR’ ELSE ‘JUNIOR’ END",
"data-type": {
"type": "string",
"length": 50
}
}
```
**Example of adding a new string column to the target table using a SUBSTR function**
The following example transformation rule adds a new string column using SQLite operators or functions to define the data in a column. This approach involves using SQLite functions to transform the GUID data loaded from Oracle to UUID format before inserting it into the Postgresql target table.
Following rule uses the SQLite substring (SUBSTR), hexadecimal function (HEX), and lowercase (LOWER) functions to break the GUID data into several groups separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits.
Here is the sample source data and output on target post processing through transformation rule:
**Source Table (Oracle GUID format)**
T\$1COL2
```
06F6949D234911EE80670242AC120002
1A2B3C4D5E6F11EE80670242AC120003
F5E4D3C2B1A011EE80670242AC120004
```
**Target Table (PostgreSQL UUID format)**
T\$1COL2\$1TMP
```
06f6949d-2349-11ee-8067-0242ac120002
1a2b3c4d-5e6f-11ee-8067-0242ac120003
f5e4d3c2-b1a0-11ee-8067-0242ac120004
```
```
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-action": "add-column",
"rule-target": "column",
"object-locator": {
"schema-name": "SPORTS",
"table-name": "TEST_TBL_2"
},
"value": "t_col2_tmp",
"expression": "CASE LOWER(SUBSTR(HEX($T_COL2), 1, 8) || '-' || SUBSTR(HEX($T_COL2), 9, 4) || '-' || SUBSTR(HEX($T_COL2), 13, 4) || '-' || SUBSTR(HEX($T_COL2), 17, 4) || '-' || SUBSTR(HEX($T_COL2), 21, 12)) WHEN '----' THEN NULL ELSE LOWER(SUBSTR(HEX($T_COL2), 1, 8) || '-' || SUBSTR(HEX($T_COL2), 9, 4) || '-' || SUBSTR(HEX($T_COL2), 13, 4) || '-' || SUBSTR(HEX($T_COL2), 17, 4) || '-' || SUBSTR(HEX($T_COL2), 21, 12)) END",
"data-type": {
"type": "string",
"length": 60
}
}
```
**Example of adding a new date column to the target table**
The following example adds a new date column, `createdate`, to the target table, `employee`. When you use the SQLite date function `datetime`, the date is added to the newly created table for each row inserted.
```
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-action": "add-column",
"rule-target": "column",
"object-locator": {
"schema-name": "public",
"table-name": "employee"
},
"value": "createdate",
"expression": "datetime ()",
"data-type": {
"type": "datetime",
"precision": 6
}
}
```
**Example of adding a new numeric column to the target table**
The following example adds a new numeric column, `rounded_emp_salary`, to the target table, `employee`. It uses the SQLite `round` function to add the rounded salary.
```
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-action": "add-column",
"rule-target": "column",
"object-locator": {
"schema-name": "public",
"table-name": "employee"
},
"value": "rounded_emp_salary",
"expression": "round($emp_salary)",
"data-type": {
"type": "int8"
}
}
```
**Example of adding a new string column to the target table using the hash function**
The following example adds a new string column, `hashed_emp_number`, to the target table, `employee`. The SQLite `hash_sha256(x)` function creates hashed values on the target for the source column, `emp_number`.
```
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-action": "add-column",
"rule-target": "column",
"object-locator": {
"schema-name": "public",
"table-name": "employee"
},
"value": "hashed_emp_number",
"expression": "hash_sha256($emp_number)",
"data-type": {
"type": "string",
"length": 64
}
}
```
## Adding metadata to a target table using expressions
You can add the metadata information to the target table by using the expressions following:
+ `$AR_M_SOURCE_SCHEMA` – The name of the source schema.
+ `$AR_M_SOURCE_TABLE_NAME` – The name of the source table.
+ `$AR_M_SOURCE_COLUMN_NAME` – The name of a column in the source table.
+ `$AR_M_SOURCE_COLUMN_DATATYPE` – The data type of a column in the source table.
**Example of adding a column for a schema name using the schema name from the source**
The example following adds a new column named `schema_name` to the target by using the schema name from the source.
```
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-action": "add-column",
"rule-target": "column",
"object-locator": {
"schema-name": "%",
"table-name": "%"
},
"rule-action": "add-column",
"value":"schema_name",
"expression": "$AR_M_SOURCE_SCHEMA",
"data-type": {
"type": "string",
"length": 50
}
}
```
# Table and collection settings rules and operations
Use table settings to specify any settings that you want to apply to a selected table or view for a specified operation. Table-settings rules are optional, depending on your endpoint and migration requirements.
Instead of using tables and views, MongoDB and Amazon DocumentDB databases store data records as documents that are gathered together in *collections*. A single database for any MongoDB or Amazon DocumentDB endpoint is a specific set of collections identified by the database name.
When migrating from a MongoDB or Amazon DocumentDB source, you work with parallel load settings slightly differently. In this case, consider the autosegmentation or range segmentation type of parallel load settings for selected collections rather than tables and views.
**Topics**
+ [
## Wildcards in table-settings are restricted
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.Wildcards)
+ [
## Using parallel load for selected tables, views, and collections
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.ParallelLoad)
+ [
## Specifying LOB settings for a selected table or view
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.LOB)
+ [
## Table-settings examples
](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.Examples)
For table-mapping rules that use the table-settings rule type, you can apply the following parameters.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.html)
## Wildcards in table-settings are restricted
Using the percent wildcard (`"%"`) in `"table-settings"` rules is not supported for source databases as shown following.
```
{
"rule-type": "table-settings",
"rule-id": "8",
"rule-name": "8",
"object-locator": {
"schema-name": "ipipeline-prod",
"table-name": "%"
},
"parallel-load": {
"type": "partitions-auto",
"number-of-partitions": 16,
"collection-count-from-metadata": "true",
"max-records-skip-per-page": 1000000,
"batch-size": 50000
}
}
```
If you use `"%"` in the `"table-settings"` rules as shown, then AWS DMS returns the exception following.
```
Error in mapping rules. Rule with ruleId = x failed validation. Exact
schema and table name required when using table settings rule.
```
In addition, AWS recommends that you don't load a great number of large collections using a single task with `parallel-load`. Note that AWS DMS limits resource contention as well as the number of segments loaded in parallel by the value of the `MaxFullLoadSubTasks` task settings parameter, with a maximum value of 49.
Instead, specify all collections for your source database for the largest collections by specifying each `"schema-name"` and `"table-name"` individually. Also, scale up your migration properly. For example, run multiple tasks across a sufficient number of replication instances to handle a great number of large collections in your database.
## Using parallel load for selected tables, views, and collections
To speed up migration and make it more efficient, you can use parallel load for selected relational tables, views, and collections. In other words, you can migrate a single segmented table, view, or collection 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, views, and collections in parallel from the source endpoint. You can then have multiple threads migrate and load the same tables, views, and collections in parallel to the target endpoint. For some database engines, you can segment the tables and views by existing partitions or subpartitions. For other database engines, you can have AWS DMS automatically segment collections according to specific parameters (autosegmentation). Otherwise, you can segment any table, view, or collection 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 LUW
+ SAP Adaptive Server Enterprise (ASE)
+ MongoDB (only supports the autosegmentation and range segmentation options of a parallel full load)
+ Amazon DocumentDB (only supports the autosegmentation and range segmentation options of a parallel full load)
For MongoDB and Amazon DocumentDB endpoints, AWS DMS supports the following data types for columns that are partition keys for the range segmentation option of a parallel full load.
+ Double
+ String
+ ObjectId
+ 32 bit integer
+ 64 bit integer
Parallel load for use with table-setting rules are supported for the following target endpoints:
+ Oracle
+ Microsoft SQL Server
+ MySQL
+ PostgreSQL
+ Amazon S3
+ SAP Adaptive Server Enterprise (ASE)
+ Amazon Redshift
+ MongoDB (only supports the autosegmentation and range segmentation options of a parallel full load)
+ Amazon DocumentDB (only supports the autosegmentation and range segmentation options of a parallel full load)
+ Db2 LUW
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 the supported targets of a parallel-load task, define more segments using column-value boundaries.
**Important**
`MaxFullLoadSubTasks` controls the number of tables or table segments to load in parallel. `ParallelLoadThreads` controls the number of threads that are used by a migration task to execute the loads in parallel. *These settings are multiplicative*. As such, the total number of threads that are used during a full load task is approximately the result of the value of `ParallelLoadThreads `multiplied by the value of `MaxFullLoadSubTasks` (`ParallelLoadThreads` **\$1** `MaxFullLoadSubtasks)`.
If you create tasks with a high number of Full Load sub tasks and a high number of parallel load threads, your task can consume too much memory and fail.
To specify the maximum number of threads per table for Amazon DynamoDB, Amazon Kinesis Data Streams, Apache Kafka, or Amazon Elasticsearch Service targets, use the `ParallelLoadThreads` target metadata task setting.
To specify the buffer size for a parallel load task when `ParallelLoadThreads` is used, use the `ParallelLoadBufferSize` target metadata task setting.
The availability and settings of `ParallelLoadThreads` and `ParallelLoadBufferSize` depend on the target endpoint.
For more information about the `ParallelLoadThreads` and `ParallelLoadBufferSize` settings, see [Target metadata task settings](CHAP_Tasks.CustomizingTasks.TaskSettings.TargetMetadata.md). For more information about the `MaxFullLoadSubTasks` setting, see [Full-load task settings](CHAP_Tasks.CustomizingTasks.TaskSettings.FullLoad.md). 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, view, or collection 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, view, or collection for parallel load:
+ By partitions (or segments) – Load all existing table or view partitions (or segments) using the `partitions-auto` type. Or load only selected partitions using the `partitions-list` type with a specified partitions array.
For MongoDB and Amazon DocumentDB endpoints only, load all or specified collections by segments that AWS DMS automatically calculates also using the `partitions-auto` type and additional optional `table-settings` parameters.
+ (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, view, or collection segments that you define by using column-value boundaries. To do so, use the `ranges` type with specified `columns` and `boundaries` arrays.
**Note**
PostgreSQL endpoints support only this type of a parallel load. MongoDB and Amazon DocumentDB as a source endpoints support both this range segmentation type and the autosegmentation type of a parallel full load (`partitions-auto`).
To identify additional tables, views, or collections 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, view, or collection 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, view, or collection partition (or segment) or subpartition is then automatically allocated to its own thread.
For some endpoints, parallel load includes partitions or subpartitions only if they are already defined for the table or view. For MongoDB and Amazon DocumentDB source endpoints, you can have AWS DMS automatically calculate the partitions (or segments) based on optional additional parameters. These include `number-of-partitions`, `collection-count-from-metadata`, `max-records-skip-per-page`, and `batch-size`.
**To specify selected table or view partitions, subpartitions, or both**
1. Specify `parallel-load` with the `partitions-list` type.
1. (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.
**Important**
For Oracle endpoints, make sure partitions and subpartitions aren't overlapping when choosing them for parallel load. If you use overlapping partitions and subpartitions to load data in parallel, it duplicates entries, or it fails due to a primary key duplicate violation.
1. (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, view, or collection segments as ranges of column values**
1. Specify `parallel-load` with the `ranges` type.
1. 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.
1. 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.
Enter values for DATE columns in the format supported by the source.
1. 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.
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 that you define three column-value arrays for columns COL1, COL2, and COL3 as follows.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.html)
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. Also 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).
**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 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"
}
}
]
}
```
**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"
}
}
]
}
```
# Using data masking to hide sensitive information
To conceal sensitive data stored in one or more columns of the tables being migrated, you can leverage Data Masking transformation rule actions. Starting from version 3.5.4, AWS DMS allows the use of data masking transformation rule actions in table mapping, enabling you to alter the contents of one or more columns during the migration process. AWS DMS loads the modified data into the target tables.
AWS Database Migration Service provides three options for data masking transformation rule actions:
+ Data Masking: Digits Mask
+ Data Masking: Digits Randomize
+ Data Masking: Hashing Mask
These data masking transformation rule actions can be configured in the table mapping of your replication task, similar to other transformation rules. The rule target should be set to the column level.
## Masking numbers in column data with a masking character
The "Data Masking: Digits Mask" transformation rule action allows you to mask numerical data in one or more columns by replacing digits with a single ASCII printable character that you specify (excluding empty or whitespace characters).
Here's an example that masks all digits in the `cust_passport_no` column of the `customer_master` table with the masking character `'#'` and loads the masked data into the target table:
```
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "cust_schema",
"table-name": "%"
},
"rule-action": "include"
},
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-target": "column",
"object-locator": {
"schema-name": "cust_schema",
"table-name": "customer_master",
"column-name": "cust_passport_no"
},
"rule-action": "data-masking-digits-mask",
"value": "#"
}
]
}
```
For example, if the column `cust_passport_no` in the source table contains the record "C6BGJ566669K", the AWS DMS task will write this data to the target table as `"C#BGJ######K"`.
## Replacing numbers in the column with random numbers
The transformation rule "Data Masking: Digits Randomize" allows you to replace each numerical digit in one or more columns with a random number. In the following example, AWS DMS replaces every digit in the `cust_passport_no` column of the source table `customer_master` with a random number and writes the modified data to the target table:
```
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "cust_schema",
"table-name": "%"
},
"rule-action": "include"
},
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-target": "column",
"object-locator": {
"schema-name": "cust_schema",
"table-name": "customer_master",
"column-name": "cust_passport_no"
},
"rule-action": "data-masking-digits-randomize"
}
]
}
```
For example, the AWS DMS task will transform the value `"C6BGJ566669K"` in the `cust_passport_no` column of the source table to `"C1BGJ842170K"` and write it to the target database.
## Replacing column data with hash value
The transformation rule "Data Masking: Hashing Mask" allows you to replace the column data with a hash generated using the `SHA256` algorithm. The length of the hash will always be 64 characters, hence the target table column length should be 64 characters at minimum. Alternatively, you can add a `change-data-type` transformation rule action to the column to increase the width of the column in the target table.
The following example generates a 64-character long hash value for the data in the `cust_passport_no` column of the source table `customer_master` and loads the transformed data to the target table after increasing the column length:
```
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "cust_schema",
"table-name": "%"
},
"rule-action": "include"
},
{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-target": "column",
"object-locator": {
"schema-name": "cust_schema",
"table-name": "customer_master",
"column-name": "cust_passport_no"
},
"rule-action": "change-data-type",
"data-type": {
"type": "string",
"length": "100",
"scale": ""
}
},
{
"rule-type": "transformation",
"rule-id": "3",
"rule-name": "3",
"rule-target": "column",
"object-locator": {
"schema-name": "cust_schema",
"table-name": "customer_master",
"column-name": "cust_passport_no"
},
"rule-action": "data-masking-hash-mask"
}
]
}
```
For example, if the column `cust_passport_no` of the source table contains value `“C6BGJ566669K”`, AWS DMS task will write a hash `“7CB06784764C9030CCC41E25C15339FEB293FFE9B329A72B5FED564E99900C75”` to the target table.
## Limitations
+ Each Data Masking transformation rule option is supported for specific AWS DMS data types only:
+ Data Masking: Digits Mask is supported for columns of data types: `WSTRING` and `STRING`.
+ Data Masking: Digits Randomize is supported for columns of data types: `WSTRING, STRING; NUMERIC, INT1, INT2, INT4, and INT8 ` with unsigned counterparts.
+ Data Masking: Hashing Mask is supported for columns of data types: `WSTRING` and `STRING`.
To learn more about the mapping of AWS DMS data types to your source engine's data types, refer to the data type mapping of your source engine with AWS DMS data types. See source data types for [Source data types for Oracle](CHAP_Source.Oracle.md#CHAP_Source.Oracle.DataTypes), [Source data types for SQL Server](CHAP_Source.SQLServer.md#CHAP_Source.SQLServer.DataTypes), [Source data types for PostgreSQL](CHAP_Source.PostgreSQL.md#CHAP_Source-PostgreSQL-DataTypes), and [Source data types for MySQL](CHAP_Source.MySQL.md#CHAP_Source.MySQL.DataTypes).
+ Using a Data Masking rule action for a column with an incompatible data type will cause an error in the DMS task. Refer to DataMaskingErrorPolicy in DMS task settings to specify the error handling behavior. For more information about `DataMaskingErrorPolicy`, see [Error handling task settings](CHAP_Tasks.CustomizingTasks.TaskSettings.ErrorHandling.md).
+ You may add a change-data-type transformation rule action to change the data type of the column to a compatible type if your source column type is not supported for the masking option you plan to use. The `rule-id` of the `change-data-type` transformation should be a smaller number than the rule-id of the masking transformation so that the data type change happens before masking.
+ Use Data Masking: Hashing Mask action for masking Primary Key/ Unique Key/ Foreign Key columns, as the generated hash value will be unique and consistent. Other two masking options cannot guarantee uniqueness.
+ While Data Masking: Digits Mask and Data Masking: Digits Randomize affect only the digits in the column data and does not affect the length of data, Data Masking: Hashing Mask modifies the entire column, length of data changes to 64 characters. Hence, the target table to be created accordingly or a change-data-type transformation rule should be added for the column which is being masked.
+ Columns with Data Masking transformation rule action specified are excluded from data validation in AWS DMS. If the Primary Key/ Unique Key columns are masked, data validation will not be run for this table; validation status of such table will be equal to `No Primary key`.