ApsaraDB for ClickHouse:Synchronize data from RDS for MySQL to ApsaraDB for ClickHouse using zero-ETL

Source database (RDS for MySQL)

If you synchronize data from multiple instances to a single ApsaraDB for ClickHouse cluster, make sure that the synchronization objects in different tasks do not overlap.

General limits

Maximum number of zero-ETL tasks per cluster

The maximum number of zero-ETL tasks you can create for a cluster depends on the edition:

• Enterprise Edition: ceil(lower CCU limit / 8). For a cluster with a lower ClickHouse Compute Unit (CCU) limit of 22, the calculation is ceil(22 / 8) = 3.

• Community-Compatible Edition: ceil(total CPU cores / 8). For a two-node cluster where each node has 8 cores, the calculation is ceil(16 / 8) = 2.

If a cluster reaches its limit, delete unused tasks or create additional synchronization tasks directly in the DTS console.

Table structure in the destination

During schema synchronization, the zero-ETL feature adds _sign, _is_deleted, and _version fields to each destination table.

For Community-Compatible Edition clusters, the task creates both a local table and a distributed table for each source table:

• Distributed table: Same name as the source table. Use this table for queries.

• Local table: Named <distributed_table_name>_local. Used internally by ClickHouse for shard-level storage.

Scheduling and performance

Run the task during off-peak hours. During full data synchronization, DTS uses read and write resources on both the source and destination, which may increase server load.

Step 1: Create the service-linked role

Create the AliyunServiceRoleForClickHouseZeroETL service-linked role.

When you select a database instance ID during task configuration, the console prompts you to create this role automatically. Manual creation is not required in that case.

Step 2: Grant RAM user permissions

To allow a RAM user to create zero-ETL tasks, grant the following permissions. See Manage RAM user permissions.

• Source RDS for MySQL: AliyunRDSFullAccess

• Destination ClickHouse cluster: AliyunClickHouseFullAccess

• DTS: Create a custom policy using the following script. See Create a custom permission policy.

{
    "Version": "1",
    "Statement": [
        {
            "Action": "dts:*",
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": "ram:PassRole",
            "Resource": "*",
            "Effect": "Allow",
            "Condition": {
                "StringEquals": {
                    "acs:Service": "dts.aliyuncs.com"
                }
            }
        }
    ]
}

Step 1: Go to the zero-ETL page

1. Log on to the ApsaraDB for ClickHouse console.

2. In the upper-left corner, select the region of your cluster.

3. On the Clusters page, click the Clusters of Community-compatible Edition tab, then click the target cluster ID.

4. In the left navigation pane, click Zero-ETL.

Step 2: Configure source and destination databases

1. Click Create Zero-ETL Task.

2. Enter a Task Name.

3. Configure the source and destination databases.

   Configure the source and destination databases based on the following parameters. After you complete the configurations, click Test Connectivity and Proceed.

   Source database

   Parameter	Description
   Database Type	RDS for MySQL (only supported option)
   Access Method	Alibaba Cloud Instance (only supported option)
   Instance Region	Region of the source RDS for MySQL instance
   RDS Instance ID	ID of the source RDS for MySQL instance
   Database Account	Database account for the RDS for MySQL instance
   Database Account	Password for the database account of the RDS for MySQL instance
   Encryption	Select Non-encrypted or SSL-encrypted. If you select SSL-encrypted, enable SSL encryption for the RDS for MySQL instance first. See Use a cloud certificate to quickly enable SSL link encryption.

   Destination database

   Parameter	Description
   Database Type	ClickHouse
   Access Method	Alibaba Cloud instance (only supported option)
   Instance Region	Region of the destination cluster
   Cluster ID	ID of the destination cluster
   Cluster Type	Community Edition or Enterprise Edition
   Database Account	Database account for the destination cluster
   Database Password	Password for the database account of the destination cluster

4. Click Test Connectivity and Proceed.

Step 3: Select objects to synchronize

In the Source Objects section, select the databases or tables to synchronize. Click to move them to the Selected Objects section.

Click Next: Configure Database and Table Fields.

Step 4: Configure table fields

On the Configure Zero-ETL page, set the following for each table:

By default, only tables with undefined configurations are shown. To view all tables, set Definition Status to All. The partition key must be selected from the primary key column. For details on these fields, see CREATE TABLE.

Click Next: Save Task Settings and Precheck. The task is saved regardless of whether the precheck passes.

Step 5: Run the precheck and start the task

When Success Rate reaches 100%, click Start.

If the precheck fails, fix the reported issues in the source or destination, then modify and rerun the precheck from the zero-ETL page.

After the task starts, the zero-ETL page shows the task ID/name, source/destination, and status.