When you call the ConfigureMigrationJob operation, you must specify the MigrationObject parameter. This topic provides the definition and examples of the MigrationObject parameter.

Related operations

ConfigureMigrationJob

Definition of the MigrationObject parameter

The value of the MigrationObject parameter is an array of JSON objects. Regular expressions are supported. The following script shows the parameter definition.

[
    {
        "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 description
Parameter Description
SchemaName This parameter is available and must be specified only if the engine type of the source database is SQL Server or PostgreSQL.
NewSchemaName This parameter is available only if the engine type of the destination database is SQL Server or PostgreSQL.
ALLTABLE Specifies whether to migrate all tables under the schema. The default value is false. You can change the value 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 must be specified only if the engine type of the destination database is AnalyticDB for MySQL or AnalyticDB for PostgreSQL.
PartKey This parameter is available and must be specified only if the engine type of the destination database is AnalyticDB for MySQL or AnalyticDB for PostgreSQL.
ColumnName and ColNewColumnNameumnName You must make sure that the column names of the source table correspond to the column names of the destination table one by one. Otherwise, DTS may fail to identify the corresponding columns.
FilterCondition The filtering conditions. Only the data that meets the specified conditions is migrated to the destination database.
Note
  • A filtering condition can be a standard SQL WHERE statement.
  • You can use apostrophes (') in a filtering condition if necessary. For example, you can enter address in('hangzhou','shanghai').

Configuration examples

Example 1: Migrate all tables in the dtstestdata database.

[{
    "DBName": "dtstestdata"
}]
Example 2: Migrate all tables in the dtstestdata database to the mysqltest database of 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 6: Migrate the customer table in the dtstestdata database and migrate only the ID and address columns in the table.

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

Example 7: Migrate the customer table in the dtstestdata database to AnalyticDB for MySQL or AnalyticDB for PostgreSQL, and set the primary key and distribution key to CREATE_TIME in the destination table.

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

Supported regular expressions

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