Creating transformation rules Retrieving transformation rules Editing transformation rules Transformation rule format Transformation rule examples

Transformation rules in DMS Schema Conversion

Transformation rules let you customize how DMS Schema Conversion converts your database objects, overriding default naming and data type mappings. You can apply these rules to databases, schemas, tables, views, columns, function return values, routine parameters, and local variables.

For example, suppose that you have a set of tables in your source schema named test_TABLE_NAME. You can set up a rule that changes the prefix test_ to the prefix demo_ in the target schema.

You can create transformation rules that perform the following tasks:

Add, remove, or replace a prefix
Add, remove, or replace a suffix
Change the data type of a column, function return value, routine parameter, or local variable
Convert the object name to lowercase or uppercase
Rename an object

Creating transformation rules

DMS Schema Conversion stores transformation rules as part of your migration project. To define transformation rules, pass them as a JSON string that contains an object with a rules array when you create or modify a migration project. Each element in the array represents a separate rule object. A migration project supports up to 512 transformation rules (128 KB maximum).

The structure of the array with transformation rules is as follows:


{
  "rules": [
    {
      Rule object 1
    },
    {
      Rule object 2
    },
    ...
    {
      Rule object N
    }
  ]
}

You can add multiple transformation rules in your project. DMS Schema Conversion applies transformation rules during conversion in the same order as you added them.

If multiple rules target the same object, the behavior depends on the action type. Rules with different action types all take effect. For example, an add-prefix rule and an add-suffix rule both apply. Rules with the same action type override each other, and only the last one takes effect.

To create transformation rules, use one of the following methods.

AWS Management Console

To create transformation rules using the console

On the Create migration project page, choose Add transformation rule. For more information, see Creating migration projects.
For Rule target, choose the type of database objects that this rule applies to. The console wizard supports schema, table, and column targets. To use other targets such as database or view, use the JSON editor in the console or the AWS CLI.
For Source schema, choose Enter a schema. Then, specify the source schemas, tables, and columns to which this rule applies. You can use an exact name to select one object, or use a pattern with the percent sign (%) as a wildcard to match any number of characters.
For Action, choose the task to perform.
Depending on the rule action, enter additional values:
- add-prefix or add-suffix – Enter the text to add.
- remove-prefix or remove-suffix – Enter the text to remove.
- replace-prefix or replace-suffix – Enter the existing text and the replacement text.
- rename – Enter the new name for the object.
- convert-uppercase or convert-lowercase – No additional values required.
- change-data-type – Enter the target data type name, and optionally the precision, scale, or length.
Choose Add transformation rule to add another transformation rule.

After you are done adding rules, choose Create migration project.

AWS CLI

To add transformation rules using the AWS CLI, use the --transformation-rules parameter with one of the following commands:

create-migration-project – When creating a new migration project.
modify-migration-project – For an existing migration project.

The following example adds transformation rules to an existing migration project:


aws dms modify-migration-project \
  --migration-project-identifier migration_project_arn \
  --transformation-rules 'json_rules'

To run this command in a specific AWS Region, add the --region parameter, for example --region us-west-2.

To edit, duplicate, or remove existing transformation rules, see Editing transformation rules.

Retrieving transformation rules

You can retrieve transformation rules from an existing DMS Schema Conversion migration project using the AWS Management Console or the AWS CLI.

Editing transformation rules

To edit, add, or remove transformation rules in your migration project, use one of the following methods.

AWS Management Console

To edit transformation rules

Choose Migration projects, and then choose your migration project.
Choose Modify to edit your migration project settings.
For Transformation rules, choose one of the following actions:
- Choose Add transformation rule to add a new transformation rule.
- Choose Duplicate to duplicate an existing transformation rule and add it at the end of the list.
- Choose Remove to remove an existing transformation rule.
- Choose the existing transformation rule to edit it.
After you are done editing rules, choose Save changes.

AWS CLI

To update transformation rules using the AWS CLI, use the modify-migration-project command with the --transformation-rules parameter. Pass the complete updated set of rules as a JSON string.


aws dms modify-migration-project \
  --migration-project-identifier migration_project_arn \
  --transformation-rules 'json_rules'

Transformation rule format

A transformation rule uses JSON object fields to define how to convert a source object into its target equivalent. Each rule type uses a specific set of fields. Include only the fields that apply to your task.

The following JSON object describes a DMS Schema Conversion transformation rule:


{
  "rule-type": "transformation",
  "rule-id": rule_id,
  "rule-name": "rule_name",
  "rule-action": "rule_action",
  "rule-target": "rule_target",
  "object-locator": {
    [ "database-name": "database_name", ]
    [ "schema-name": "schema_name", ]
    [ "table-name": "table_name", ]
    [ "column-name": "column_name", ]
    [ "parent": "parent_name", ]
    [ "function-name": "function_name", ]
    [ "procedure-name": "procedure_name", ]
    [ "parameter-name": "parameter_name", ]
    [ "local-variable-name": "local_variable_name", ]
    [ "type": "source_data_type", ]
    [ "precision": source_precision, ]
    [ "scale": source_scale, ]
    [ "length": source_length ]
  },
  [ "value": "rule_value", ]
  [ "old-value": "rule_old_value", ]
  [ "data-type": {
    "type": "data_type_name",
    [ "precision": data_type_precision, ]
    [ "scale": data_type_scale, ]
    [ "length": data_type_length ]
  } ]
}

The following table describes each parameter.

Parameter	Possible values	Description
`rule-type`	`transformation`	A value that applies the rule to each object specified by the object locator. For all transformation rules, set this to `transformation`. Required parameter.
`rule-id`	A numeric (integer) value.	A unique numeric value to identify the rule. Required parameter.
`rule-name`	An alphanumeric value.	A unique name to identify the rule. Required parameter.
`rule-action`	`add-prefix`, `remove-prefix`, `replace-prefix`, `add-suffix`, `remove-suffix`, `replace-suffix`, `convert-uppercase`, `convert-lowercase`, `rename`, `change-data-type`	The transformation you want to apply to the object. All transformation rule actions are case-sensitive. Prefix operations: `add-prefix` – Add a specified text to the beginning of a name. `remove-prefix` – Remove a specified text from the beginning of a name. `replace-prefix` – Replace an existing prefix with a new one. Suffix operations: `add-suffix` – Add a specified text to the end of a name. `remove-suffix` – Remove a specified text from the end of a name. `replace-suffix` – Replace an existing suffix with a new one. Case conversion: `convert-uppercase` – Convert all characters in a name to uppercase. `convert-lowercase` – Convert all characters in a name to lowercase. Direct modification: `rename` – Change the name to a completely new specified value. `change-data-type` – Modify the data type of a column or local variable to a different data type. Scope: prefix, suffix, rename, and case conversion operations can be applied to schemas, tables, and columns. The `change-data-type` operation applies only to columns, function return values, routine parameters, and local variables. Required parameter.
`rule-target`	`database`, `schema`, `table`, `view`, `column`, `function result`, `routine parameter`, `local variable`	The type of object to which the rule will be applied. `database` – Database. `schema` – Database schema. `table` – Database table. `view` – Database view. `column` – Database table or view column. `function result` – Function return value. `routine parameter` – Stored procedure or function parameter. `local variable` – Local variable within stored procedure or function. Required parameter.
`object-locator`	A JSON object.	An object that identifies which source database objects the rule applies to. You can use the percent sign (`%`) as a wildcard for all or part of the value in each `object-locator` object field. The object contains the following string fields: `database-name` `schema-name` `table-name` `column-name` `parent` `function-name` `procedure-name` `parameter-name` `local-variable-name` `type` `precision` `scale` `length` All object names, such as table or column names, in transformation rules are case-sensitive. The following sections describe each object field. Required parameter.
`object-locator \| database-name`	An alphanumeric value that follows the naming rules for the source database.	The name of the source database. Applicable for database vendors where the database architecture originates from the database as the top-level container of objects. For example: Microsoft SQL Server, SAP ASE. Optional JSON object field.
`object-locator \| schema-name`	An alphanumeric value that follows the naming rules for the source database.	The name of the source database schema. Optional JSON object field.
`object-locator \| table-name`	An alphanumeric value that follows the naming rules for the source database.	The name of the source database table. Optional JSON object field.
`object-locator \| column-name`	An alphanumeric value that follows the naming rules for the source database.	The name of the source database table column. Optional JSON object field.
`object-locator \| parent`	An alphanumeric value that follows the naming rules for the source database.	The name of the source database object that is the parent of the specified object, for example, for the `function-name` or `procedure-name`. In databases of some vendors, the object to which the rule is applied may be subordinate and require specifying its parent. For example, packaged functions in Oracle, where the parent is the package name. Optional JSON object field.
`object-locator \| function-name`	An alphanumeric value that follows the naming rules for the source database.	The name of the source database function. Optional JSON object field.
`object-locator \| procedure-name`	An alphanumeric value that follows the naming rules for the source database.	The name of the source database procedure. Optional JSON object field.
`object-locator \| parameter-name`	An alphanumeric value that follows the naming rules for the source database.	The name of the source database function or procedure parameter. Optional JSON object field.
`object-locator \| local-variable-name`	An alphanumeric value that follows the naming rules for the source database.	The name of a local variable within the source database function or procedure. Optional JSON object field.
`object-locator \| type`	An alphanumeric value.	The source data type name to match. Use this field to apply the rule only to objects with a specific data type. Optional JSON object field.
`object-locator \| precision`	A numeric (integer) value.	The precision of the source data type to match. Optional JSON object field.
`object-locator \| scale`	A numeric (integer) value.	The scale of the source data type to match. Optional JSON object field.
`object-locator \| length`	A numeric (integer) value.	The length of the source data type to match. Optional JSON object field.
`value`	An alphanumeric value that follows the naming rules for the target database.	The text value used by the transformation action. For `add-prefix` and `add-suffix`, specifies the text to add. For `remove-prefix` and `remove-suffix`, specifies the text to remove. For `replace-prefix` and `replace-suffix`, specifies the replacement text. For `rename`, specifies the new name. Required for all actions except `convert-uppercase`, `convert-lowercase`, and `change-data-type`.
`old-value`	An alphanumeric value that follows the naming rules for the source database.	The existing value to find and replace in the object name. Required for actions such as `replace-prefix` and `replace-suffix`. Optional parameter.
`data-type`	A JSON object.	An object describing replacement data type properties when `rule-action` is `change-data-type`. The object contains the following fields: `type` : string `precision` : number `scale` : number `length` : number The following sections describe each object field. Optional parameter.
`data-type \| type`	An alphanumeric value.	The name of the replacement data type for the target database column if the `rule-action` is `change-data-type`. Must be a valid target database data type. Required JSON object field.
`data-type \| precision`	A numeric (integer) value.	The precision of the replacement data type for the target database if the `rule-action` is `change-data-type`. Optional JSON object field.
`data-type \| scale`	A numeric (integer) value.	The scale of the replacement data type for the target database if the `rule-action` is `change-data-type`. Optional JSON object field.
`data-type \| length`	A numeric (integer) value.	The length of the replacement data type for the target database if the `rule-action` is `change-data-type`. Optional JSON object field.

Transformation rule examples

The following examples show the JSON value for the --transformation-rules parameter for different rule types: