MigrationObject-Data Transmission Service(DTS)-阿里云帮助中心

Related API operations

Definition of the MigrationObject parameter

The MigrationObject parameter is an object that supports certain regular expressions. It is defined as follows.

[
    {
        "DBName":"The name of the database that you want to migrate",
        "NewDBName":"The database name that you want to use in the destination instance",
        "SchemaName":"The name of the schema that you want to migrate",
        "NewSchemaName":"The schema name that you want to use in the destination instance",
        "AllTable": false,
        "TableIncludes":[
            {
                "TableName":"The name of the table that you want to migrate",
                "NewTableName":"The table name that you want to use in the destination instance",
                "FilterCondition":"where condition",
                "PrimaryKey":"The primary key columns of the table that you want to migrate. Separate multiple values with commas (,).",
                "PartKey":"The distribution columns of the table that you want to migrate. Separate multiple values with commas (,).",
                "ColumnIncludes":[
                    {
                        "ColumnName":"The name of the column that you want to migrate from the source table",
                        "NewColumnName":"The column name that you want to use in the destination instance"
                    }
                ],
                "ColumnExcludes":[{
                    "ColumnName":"The name of the column that you do not want to migrate from the source table"
                }]
            }
        ],
        "TableExcludes":[{
            "TableName":"The name of the table that you do not want to migrate"
        }]
    }
]

Table 1. Additional information
Parameter	Description
SchemaName	This parameter is available and required only when the source database is SQL Server or PostgreSQL.
NewSchemaName	This parameter is available only when the destination database is SQL Server or PostgreSQL.
ALLTABLE	Specifies whether to configure all tables in the schema to be migrated. The default value is false. You can also set this parameter to true. If the value is false, you must specify the TableIncludes and TableExcludes parameters. If the value is true, you must specify the TableIncludes parameter.
PrimaryKey	This parameter is available and required only when the destination database is AnalyticDB for MySQL or AnalyticDB for PostgreSQL.
PartKey	This parameter is available and required only when the destination database is AnalyticDB for MySQL or AnalyticDB for PostgreSQL.
ColumnName, ColNewColumnNameumnName	Make sure that the column names of the source table correspond to the column names of the destination table in the same order. Otherwise, DTS may fail to find the corresponding columns.
FilterCondition	The filter condition. Only data that meets the filter condition is migrated to the destination database. Note Standard SQL WHERE statements are supported as filter conditions. If you use quotation marks in a filter condition, use single quotation marks ('). For example, `address in('hangzhou','shanghai')`.

Configuration examples

Example 1: Migrate all tables in the dtstestdata database.

[{
    "DBName": "dtstestdata"
}]

Example 2: Migrate all tables from the dtstestdata database to the mysqltest database in the destination instance.

[{
    "DBName": "dtstestdata",
    "NewDBName": "mysqltest"
}]

Example 3: Migrate all tables in the dtstestdata database and the mysqltest database.

[{
    "DBName": "dtstestdata"
},{
    "DBName": "mysqltest"
}]

Example 4: Migrate all tables in the dtstestdata database except the tables whose names are prefixed with "order."

[{
    "DBName": "dtstestdata",
    "AllTable": false,    
    "TableExcludes": [{
        "TableName": "order.*"
    }]
}]

Example 5: Migrate the customer table in the dtstestdata database and migrate only data with values greater than 100 in the ID column.

[{
    "SchemaName": "dtstestdata",
    "TableIncludes": [{
        "TableName": "customer",
        "FilterCondition": "id > 100"
    }]
}]

Example 7: Migrate only the ID and address columns from the customer table in the dtstestdata database.

[{
    "SchemaName": "dtstestdata",
    "TableIncludes": [{
        "TableName": "customer",
        "ColumnIncludes": [{
                "ColumnName": "id"
            },
            {
                "ColumnName": "address"
            }
        ]
    }]
}]

Example 8: Migrate the customer table from the dtstestdata database to AnalyticDB for MySQL or AnalyticDB for PostgreSQL and set the primary key and distribution key of the destination table to CREATE_TIME.

[{
    "SchemaName": "dtstestdata",
    "TableIncludes": [{
        "TableName": "customer",
        "PrimaryKey":"CREATE_TIME",
        "PartKey":"CREATE_TIME",
    }]
}]

Supported regular expressions

Symbol	Rule description
Period (.)	Matches any single character except for '\r\n'.
Asterisk (*)	Matches the preceding subexpression zero or more times. For example, `h.*llo` matches strings such as hllo and heeeello.
Question mark (?)	Matches the preceding subexpression zero or one time. For example, `h.?llo` matches hllo and hello, but not haello.
[characters] Character set	Matches any single character in the brackets. For example, `h[ae]llo` matches hallo and hello.
[^characters] Negative character set	Matches any single character not in the brackets. For example, `h[^ae]llo` matches hcllo or hdllo, but not hallo or hello.
[character1-character2] Character range	Matches any character within the range from character1 to character2. For example, [0-9] and [a-z].