Enable multi-table merging - Data Transmission Service - Alibaba Cloud Documentation Center

When you use Data Transmission Service (DTS) for data synchronization or migration, DTS provides a multi-table merging feature if the target database is AnalyticDB for MySQL 3.0. This feature allows you to consolidate multiple tables that have identical structures in the source database and merge them into a single data table in the target database, which enables more efficient and convenient data migration and integration.

Prerequisites

You have created a migration or synchronization task with a target of AnalyticDB for MySQL 3.0, and have proceeded to the Configure Objects and Advanced Settings step. You can find the relevant configuration documents in the Synchronization Solution Overview or Migration Solution Overview.

Usage notes

You cannot enable multi-table merging for tasks that have data verification enabled.
Incremental tasks with multi-table merging enabled do not support DDL operations. If you perform a DDL operation on the source database, it may cause data loss or task failure.

Procedure

This section shows how to enable multi-table merging for a DTS data synchronization instance.

Navigate to the Configure Objects and Advanced Settings step and configure all parameters preceding the Merge Tables section.
In the Merge Tables section, select Yes.
Optional: For Set the value of __dts_data_source, select No.

Note
Customizing the tag column name and values is supported only for subscription instances.
In the Source Objects pane, select the tables that you want to synchronize, and then click the icon to move them to the Selected Objects pane.
In the Selected Objects pane, right-click a table to synchronize. In the Edit Table dialog box, modify the Table Name.

Note
The Table Name is the table's name in the destination database.

The process is similar to changing the Table Name. In the Edit Table dialog box, you can change the table name to a target name (such as person_new), set a filter condition (which supports standard SQL WHERE clauses and applies to both full and incremental synchronization), select the DML operations to synchronize (insert, update, or delete), and select the columns to synchronize (such as p_name and p_id). When you are finished, click OK.
Repeat the preceding step to modify the Table Name for all selected tables.

Important
You must specify the same Table Name for all tables that will be merged.
Follow the on-screen instructions and reference documents to complete the remaining task configuration.

If you selected Yes for Set the value of __dts_data_source in step 3, you must also set the Value of __dts_data_source in the Configure Database and Table Fields step.

Note
The value in the Value of __dts_data_source column must be unique for each table.

On the Configure Database and Table Fields page, locate the Value of __dts_data_source column and enter a unique value for each source table. Other columns on this page include Schema Name, Table Name, Type (for example, partitioned table), Primary Key Column, and Distribution Key.

FAQ

How do I query the __dts_data_source column?

After you enable multi-table merging, DTS automatically adds a __dts_data_source column (of the text type) to the destination table to record the data source. Use the following syntax to query the column:

SELECT __dts_data_source FROM <destination_table_name>;

The returned value uses the format DTS instance ID:database name:schema name:table name, for example, dts:dtstestdata:testschema:customer1.

The query fails with the error message "Column cannot be resolved"

If you encounter this error, check the following items:

Whether multi-table merging is enabled for the DTS task. If multi-table merging is not enabled, the destination table does not contain the __dts_data_source column.
Whether you are querying the destination database instead of the source database.
Whether the column name uses a double underscore prefix (__dts_data_source), not a single underscore.