Transformation rules and actions - AWS Database Migration Service

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.

Note

AWS DMS doesn't support more than one transformation rule per schema level or per table level. However, AWS DMS does support more than one transformation rule per column level.

Performing multiple rule actions on columns that include converting to lowercase can prevent some transformations from happening correctly. For example, using the convert-lowercase and change-data-type rule actions together might not succeed. Instead, use the convert-lowercase rule action separately.

Column names in transformation rules are case-sensitive. For example, you must provide column names for an Oracle database in upper case.

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.

  • column-name – The name of an existing column.

  • data-type – The name of an existing column data type.

The name of each schema, table or view, table tablespace, index tablespace, and column 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, except data-type. 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

  • One or more columns in the specified table or tables, view or views, and schema or schemas.

  • The columns with a given data-type when multiple columns are specified. For the possible values of data-type, see data-type described following in this table.

Also, 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

add-column, include-column, remove-column

rename

convert-lowercase, convert-uppercase

add-prefix, remove-prefix, replace-prefix

add-suffix, remove-suffix, replace-suffix

define-primary-key

change-data-type

add-before-image-columns

include-column

The transformation you want to apply to the object. All transformation rule actions are case-sensitive.

The add-column value of the rule-action parameter adds a column to a table. When used with the expression and data-type parameters, add-column specifies the value of new column data.

The include-column value of the rule-action parameter includes the columns specified by object-locator.

The change-data-type value for rule-action is only available for column rule targets.

The include-column value of the rule-action parameter changes the mode of the table to drop all columns by default and include the columns specified. Multiple columns are included in the target by invoking the include-column rule multiple times.

rule-target schema, table, column, table-tablespace, index-tablespace The type of object that you're transforming.

The table-tablespace and index-tablespace values are only available for an Oracle target endpoint.

Make sure to specify a value for the parameter that you specify as part of the object-locator: table-tablespace-name or index-tablespace-name 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.
data-type

type – The data type to use if the rule-action is add-column or the replacement data type if therule-action is change-data-type.

Or, the name of the replacement data type when rule-action is change-data-type, the value of column-name is "%", and an additional data-type parameter to identify the existing data type is included in the object-locator.

AWS DMS supports column data type transformations for the following DMS data types: "bytes", "date", "time", "datetime", "int1", "int2", "int4", "int8", "numeric", "real4", "real8", "string", "uint1", "uint2", "uint4", "uint8", "wstring", "blob", "nclob", "clob", "boolean", "set", "list" "map", "tuple"

precision – If the added column or replacement data type has a precision, an integer value to specify the precision.

length – The length of new column data (when used with add-column)

The following is an example of a data-type parameter to specify the existing data type to be replaced. This existing data-type parameter is included in the object-locator when the value of column-name is "%", shown following.

. . . "object-locator": { "schema-name": "dbo", "table-name": "dms", "column-name": "%", "data-type": "int2" }, "data-type": { "type": "int8" }

Here, any column with the int2 data type is replaced with the int8 data type.

The length value for data-type is only available for use with column rule targets of add-column rule actions.

expression An alphanumeric value that follows SQLite syntax.

When used with the rule-action set to rename-schema, the expression parameter specifies a new schema. When used with the rule-action set to rename-table, expression specifies a new table. When used with the rule-action set to rename-column, expression specifies a new column name value.

When used with the rule-action set to add-column, expression specifies data that makes up a new column.

For more information about using expressions for transformation rules, see Using transformation rule expressions to define column content.

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 define 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.
before-image-def An object with the following parameters:
  • column-prefix – A value prepended to a column name. The default value is BI_.

  • column-suffix – A value appended to the column name. The default is empty.

  • column-filter – Requires one of the following values: pk-only (default), non-lob (optional) and all (optional).

This parameter defines a naming convention to identify the before-image columns and specifies a filter to identify which source columns can have before-image columns created for them on the target. You can specify this parameter when the rule-action is set to add-before-image-columns and the rule-target is set to column.

Don't set both column-prefix and column-suffix to empty strings.

For column-filter, select:

  • pk-only – To add only columns that are part of table primary keys.

  • non-lob – To add only columns that are not of LOB type.

  • all – To add any column that has a before-image value.

For more information about before-image support for AWS DMS target endpoints, see:

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