Transform data with AWS DMS version 3.1.3

AWS now supports new data transformation capabilities in the latest AWS Database Migration Service (AWS DMS) version 3.1.3. You can change schema, table, and column names; specify individual tablespace names for Oracle targets; and update a table’s primary and unique key on any target. DMS version 3.1.3 supports the following new data transformation capabilities:

Explicit table mapping
Transformation rules for tablespaces for Oracle source and target
Transformation rules for index tablespaces for Oracle source and target
Defininition of the primary key or unique key index
Data type modification of the target column

Explicit table mapping

In previous DMS versions, you used the AWS Management Console to perform table mapping, specify table selections, or transform rule-actions on schemas and tables.

With 3.1.3, AWS DMS allows you to perform explicit table selection. Explicit table mapping rules allow you to select a specific source table for migration to supported DMS targets. It also excludes subsets of tables from the source for better granularity. When using explicit selection rules, you can’t use wildcards (%) for schema and table names in the table mapping.

In the following example, the source has seven tables. Using the explicit transformation rule, you can choose to migrate the DEPT table only, while excluding the remaining tables from migration.

SQL> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG LIKE %DEPT%'

TABLE_NAME
------------------------------
HRDEPT
DEVDEPT
SUPPORTDEPT
PMDEPT
SECURITYDEPT
ITDEPT
DEPT

The corresponding transformation rule in the DMS console looks like the following:

{  
   "rules":[  
      {  
         "rule-type":"selection",
         "rule-id":"1",
         "rule-name":"1",
         "object-locator":{  
            "schema-name":"MYSCHEMA",
            "table-name":%"DEPT%"
         },
         "rule-action":"exclude"
      },
      {  
         "rule-type":"selection",
         "rule-id":"1",
         "rule-name":"1",
         "object-locator":{  
            "schema-name":"MYSCHEMA",
            "table-name":"DEPT"
         },
         "rule-action":"explicit"
      }
   ]
}

You can query for the table ‘DEPT’ on the target database schema using the following query:

SQL> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG LIKE %DEPT%'

TABLE_NAME
------------------------------
DEPT

Transformation rule for tablespace for Oracle source and target

Transformation rules for tablespace allow you to migrate tables to a specific tablespace on an Oracle target. The following code example lets you put the table MYTABLENAME into a separate tablespace.

{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-action": "rename",
"rule-target": "table-tablespace",
"schema-name": "MYSCHEMA",
"table-name": "MYTABLENAME",
"table-tablespace-name": "%"
},
"value": "TARGETTABLESPACE"
}

Transformation rules for index tablespaces for Oracle source and target

Transformation rules for index tablespaces allow you to migrate table indexes to a tablespace on your choice on an Oracle target. The following code example allows you to put the index for table MYTABLENAME into a separate tablespace.

{
"rule-type": "transformation",
"rule-id": "3",
"rule-name": "3",
"rule-action": "rename",
"rule-target": "index-tablespace",
"schema-name": "MYSCHEMANAME",
"table-name": "MYTABLENAME",
"index-tablespace-name": "%"
},
"value": " TARGETTABLESPACE "
}

Definition of the primary key or unique index

Managing and creating primary keys on table columns presents a constant challenge. New DMS functionality allows you to update the primary key on a target.

The following example shows you how to create a table primary key on the required columns. The column order defined in the transformation rule indicates the column order in the primary key created on the target. You can specify the origin field to create the key on the target as either a primary key or a unique index. DMS creates it as a primary key by default.

SQL> Create table MyTable 
(
    ID int Primary KEY NOT NULL, 
    SNO INT,
    NAME VARCHAR(20),
    SALE_AMOUNT int,
    SALE_DATE DATE,
    REGION VARCHAR(10)
)

In this example, ID is the only primary key column on the source. You can use this sample table mapping rule to migrate SNO as the primary key as well:

{
	"rules": [
		{
			"rule-type": "selection",
			"rule-id": "1",
			"rule-name": "1",
			"object-locator": {
				"schema-name": "dbo",
				"table-name": " MyTable"
			},
			"rule-action": "include"
		},
		{
			"rule-type": "transformation",
			"rule-id": "2",
			"rule-name": "2",
			"object-locator": {
				"schema-name": "dbo",
				"table-name": " MyTable"
			},
			"rule-action": "define-primary-key",
			"rule-target": "table",
			"primary-key-def": {
				"name": "newPk",
				"origin": "primary-key",
				"columns": [
					"ID",
					"sno"
				]
			}
		}
	]
}

On the target end, this code example creates table DMS with both ID and SNO as primary keys. The syntax for the primary key constraint looks like the following:

ALTER TABLE [dbo].[dms] ADD  CONSTRAINT [ID_1553262410892936_PK] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[SNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Data type change for the target column

You can transform the type of a column on the fly using the change-data-type transformation during the migration. DMS now supports data type transformations for the following data types:

BYTES("bytes"), DATE("date"), TIME("time"), DATETIME("datetime"), INT1 ("int1"), INT2 ("int2"), INT4 ("int4"), INT8 ("int8"), NUMERIC ("numeric"), REAL4 ("real4"), REAL8 ("real8"), STRING("string"), UINT1("uint1"), UINT2 ("unit2"), UINT4 ("uint4"), UINT8 ("uint8"), WSTRING("wstring"), BLOB ("blob"), NCLOB ("nclob"), CLOB ("clob"), BOOLEAN ("boolean"), SET ("set"), LIST ("list"), MAP ("map"), TUPLE ("tuple");

In the following example, I use SQL Server as the source and target to demonstrate how to change the column data types with the target transformation rule.

Step 1: Create a source table

Use the following code example to create a table on the source:

CREATE TABLE [dbo].[dms]
(
	[ID] [int] NOT NULL,
	[SNO] [int] NOT NULL,
	[NAME] [varchar](20) NULL,
	[SALE_AMOUNT] [int] NULL,
	[SALE_DATE] [date] NULL,
	[REGION] [varchar](10) NULL
)

Step 2: Create a task

Use the following task-mapping JSON to create a task using the following source and target endpoint:

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

Step 3: Modify the table.

The transformation rules should modify your table to the following:

CREATE TABLE [dbo].[dms]
(
	[ID] [int] NOT NULL,
	[SNO] [int] NOT NULL,
	[NAME] [varchar](20) NULL,
	[SALE_AMOUNT] [bigint] NULL,
	[SALE_DATE] [date] NULL,
	[REGION] [varchar](10)
)

Conclusion

AWS DMS replication engine version 3.1.3 makes the features in this post available to you. You can use explicit mapping rules (all engines), different transformation rules for Oracle source and target, and define primary key and unique key indexes on the target columns for all engines.

If you have questions or suggestions, add your comments below. Good luck and happy migrating!

About the Author

Harish Bannai is a cloud support engineer at AWS. He works with customers to provide guidance and technical assistance on database projects, helping them to improve the value of their solutions when using AWS..