Data Transmission Service:Synchronize data from an RDS MySQL instance to an ApsaraDB for ClickHouse cluster

Source database limitations

Destination and other limitations

Usage notes

• DTS executes the CREATE DATABASE IF NOT EXISTS 'test' statement in the source database on a schedule to advance the binary log file position.

• DTS calculates synchronization latency based on the timestamp of the latest synchronized data at the destination and the current timestamp at the source. If no DML operations are performed on the source for a long time, latency readings may be inaccurate. Run a DML operation on the source to refresh the latency. If you synchronize an entire database, create a heartbeat table that is updated every second.

• If the source database is a self-managed MySQL database and you perform a primary/secondary switchover on the source while the data synchronization task is running, the task fails.

Step 1: Open the Data Synchronization page

Use one of the following methods:

Step 2: Configure source and destination databases

1. Click Create Task.

2. Configure the parameters described in the following table.

Task name

Source database

Destination database

1. Click Test Connectivity and Proceed.

Make sure that the CIDR blocks of DTS servers are allowed by the security settings of the source and destination databases. DTS adds these CIDR blocks automatically for Alibaba Cloud instances. For self-managed databases, add them manually. See Add the CIDR blocks of DTS servers.

Step 3: Select synchronization objects

1. In the Configure Objects step, configure the following settings.

If you use object name mapping, other objects that depend on mapped objects may fail to synchronize.

1. Click Next: Advanced Settings and configure the following parameters.

1. Click Next: Configure Database and Table Fields to configure ClickHouse table settings. For each table to synchronize, set the Type, Primary Key Column, Sort Key, Distribution Key, and Partition Key:

   • DTS provides default configurations. Set Definition Status to All to review and modify them.

   • Primary Key Column and Sort Key support composite keys — select multiple fields from the drop-down lists.

   • Select one or more columns from Primary Key Column as the Partition Key. Only one field can be selected as the Distribution Key.

   • The Partition Key is optional but cannot be a nullable field. Only BIGINT, INT, TIMESTAMP, DATETIME, and DATE types are supported as partition keys. See Calculation logic for partition keys.

   • For details on primary key columns, sort keys, and partition keys, see CREATE TABLE.

Step 4: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

DTS runs a precheck before the task can start.

• If the precheck fails, click View Details next to each failed item, resolve the issues, and rerun the precheck.

• If an alert is triggered:

  • For alerts that cannot be ignored: click View Details, fix the issue, and rerun the precheck.

  • For alerts that can be ignored: click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring alerts may cause data inconsistency.

Step 5: Purchase the instance

1. Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

2. On the buy page, configure the following parameters.

1. Read and select Data Transmission Service (Pay-as-you-go) Service Terms.

2. Click Buy and Start, then click OK in the confirmation dialog.

The task appears in the task list. Monitor its progress from there.

Time information

Table and field information

SELECT * FROM table_name FINAL WHERE _sign > 0;

Calculation logic for partition keys