When configuring data synchronization or migration task objects, DTS supports setting the names of task objects (databases, tables, columns) in the destination database instance through the name mapping feature. You can use this feature to synchronize or migrate data to specified objects in the destination database instance, or create objects in the destination database instance with the same structure but different names as the task objects.
Scenarios
The following scenarios also apply when the source and destination databases are the same database instance, but data isolation is required. For example, differentiation by database name or table name.
Scenario | Description |
Create an object in the destination database to receive data. |
|
Specify the objects in the destination database to receive data. For example, you want to synchronize or migrate data of multiple databases or tables to a single large database or table. | You must create an object in the destination database in advance to receive data and ensure data compatibility.
|
Create an object in the destination database. | This scenario is supported only for data migration tasks. You need to select only Schema Migration for the Migration Types parameter. |
Prerequisites
The task is in the Configure Objects stage, and database, table, and column mapping operations need to be performed. For methods to enter the Configure Objects stage, see the relevant configuration documents in Synchronization solution overview and Migration solution overview.
We recommend selecting all options for Migration Types or Synchronization Types. Otherwise, the synchronization or migration results may not meet expectations.
The old version configuration page needs to be in the Configure Objects and Advanced Settings stage.
Considerations
Once the task is started, do not perform database, table, or column mapping operations again, as this may cause data loss or task failure.
Tair or Redis instances only support database name mapping, specifying the database (DB 0~DB 255) to receive data.
When the destination database type is DataHub, database name mapping is not supported.
When the destination database type is Kafka, if you use the mapping feature to modify the Table Name, data will be written to the Topic you specified. You must ensure that the Topic name you entered in the Table Name field actually exists in the destination Kafka instance. Otherwise, the task will fail.
If the granularity of the task object selection is the entire database, only database name mapping is supported.
If you need to use table name or column name mapping: when the source database type is MongoDB, the task object selection granularity needs to be collection; when the source database type is Tair/Redis, the task object selection granularity needs to be Redis DB; for other types of source databases, the task object selection granularity needs to be table.
If you use the database, table, and column name mapping feature, it may cause synchronization or migration failures for other objects that depend on this object.
Individual database, table, and column name mapping
Move task objects from Source Objects to the Selected Objects area.
Open the database or table mapping interface.
Right-click the target object in the Selected Objects area.
Place the mouse pointer on the target object, then click the Edit button that appears after the target object.
In the dialog box that appears, modify the name of the object in the destination database instance.
NoteWhen performing database or table name mapping, some instances (please refer to the actual console interface) also support adding additional columns and filtering SQL statements (DML and DDL) for incremental tasks.
If different statements are selected for database name mapping and table name mapping, the statements selected during table name mapping take precedence.
You can also configure condition filtering for task data in the Edit Table dialog box. For more information, see Set filter conditions.
Database name mapping
In the Edit Schema dialog box that appears, set the name of the database in the destination database instance.
NoteWhen both source and destination database types are SQL Server, the Edit Database Name dialog box appears.
Table name mapping
In the Edit Table dialog box that appears, set the name of the data table in the destination database instance.
Column name mapping
In the Edit Table dialog box that appears, in the Columns section, set the name of the corresponding column in the destination instance (Destination Column Name).
Click OK.
Configure other parameters that are required for the DTS task.
Batch database, table, and column name mapping
Move task objects from Source Objects to the Selected Objects area.
Click Batch Edit in the upper-right corner of the Selected Objects area.
In the Batch Edit dialog box, batch map database or table names according to your requirements.
Source database type is Tair/Redis
In the Select Objects box, select the objects you want to edit.
NoteThe batch edit type is set to Include Only Selected Databases by default.
Optional: In the Select Editing Type area, click the Object Name tab.
Select the effective scope according to your requirements.
Edit Renamed Object Name: Indicates that the batch-modified database names are modified based on the previously renamed names.
Edit Original Object Name: Indicates that the batch-modified database names are modified based on the original names.
Select the rule for changing database and table names, and set the changed database or table names.
Add prefix or suffix to names: Select Add Prefix and Suffix for Select Rule, and enter the prefix and suffix.
Uniformly update names: Select Change All Names for Select Rule, and enter the changed name.
Uniformly reduce a part of names: Select Shorten All Names for Select Rule, and enter the keyword to be reduced.
Click Apply to add the configured batch mapping rule.
Click OK.
Source database type is SQL Server
Select the batch edit type according to your requirements.
In this example, Select All Databases and Tables is selected.
In the Select Objects box, select the objects you want to edit.
Optional: In the Select Editing Type area, click the Object Name tab.
Optional: Select the object name type.
NoteYou can configure Select Object Name Type only when Schema Mapping Mode of Source and Destination Databases on the Configure Objects page is set to Use <Source schema name>.<Source table name> as the destination table name, or when the Configure Objects page does not have the Schema Mapping Mode of Source and Destination Databases configuration item.
Library table name: Only map the names of databases and tables in the selected objects.
Schema name: Only map the Schema names in the selected objects.
Select the effective scope according to your requirements.
Edit Renamed Object Name: Indicates that the batch-modified database names are modified based on the previously renamed names.
Edit Original Object Name: Indicates that the batch-modified database names are modified based on the original names.
Select the rule for changing database and table names, and set the changed database or table names.
Add prefix or suffix to names: Select Add Prefix and Suffix for Select Rule, and enter the prefix and suffix.
Uniformly update names: Select Change All Names for Select Rule, and enter the changed name.
Uniformly reduce a part of names: Select Shorten All Names for Select Rule, and enter the keyword to be reduced.
Click Apply to add the configured batch mapping rule.
Click OK.
Source database is of other types
Select the batch edit type according to your requirements.
In this example, Select All Databases and Tables is selected.
In the Select Objects box, select the objects you want to edit.
Optional: In the Select Editing Type area, click the Object Name tab.
NoteWhen performing batch mapping, some instances (please refer to the actual console interface) also support adding additional columns and filtering SQL statements (DML and DDL) for incremental tasks.
Select the effective scope according to your requirements.
Edit Renamed Object Name: Indicates that the batch-modified database, table, and column names are modified based on the previously renamed names.
Edit Original Object Name: Indicates that the batch-modified database, table, and column names are modified based on the original names.
Select the rule for changing database and table names, and set the changed database or table names.
Add prefix or suffix to names: Select Add Prefix and Suffix for Select Rule, and enter the prefix and suffix.
Uniformly update names: Select Change All Names for Select Rule, and enter the changed name.
Uniformly reduce a part of names: Select Shorten All Names for Select Rule, and enter the keyword to be reduced.
Click Apply to add the configured batch mapping rule.
Click OK.
Configure other parameters that are required for the DTS task.
Synchronize or migrate partial columns
Move the table to be synchronized or migrated from Source Objects to the Selected Objects area.
Right-click the target object in the Selected Objects area to open the table mapping interface.
In the Edit Table dialog box that appears, in the Columns section, clear the Synchronize All Tables check box.
Clear the check boxes for columns that do not need to be synchronized or migrated.
Click OK.
Configure other parameters that are required for the DTS task.
FAQ
How do I filter columns that do not need to be synchronized or migrated?
Use column name mapping, clear the Synchronize All Tables check box, and then clear the check boxes for the corresponding columns.
Does column name mapping support modifying column types?
No, it does not.
Can batch edit rules be modified?
No, they cannot. You can click the
after the target rule to delete the mapping rule, and then add a new rule.
Why did the batch edit rule disappear?
You may have forgotten to click Apply after setting up the batch mapping rule, or you may have accidentally deleted the rule.