Synchronize ApsaraDB RDS for MySQL to an ApsaraDB for ClickHouse cluster-ApsaraDB for ClickHouse-阿里云帮助中心

ApsaraDB for ClickHouse provides the Zero-ETL data integration feature to synchronize data from RDS MySQL to ApsaraDB for ClickHouse. This feature eliminates the need to build or maintain data synchronization pipelines, is free of charge, and reduces your data transfer and O&M costs.

Overview

Businesses often manage operational data distributed across various systems.

ETL is the process of extracting data from upstream business systems, transforming and cleaning it, and then loading it into a data warehouse. The goal is to consolidate this data into a destination data warehouse for analysis.

Traditional ETL workflows often present the following challenges:

High resource costs: Different data sources often require different ETL tools, and building these pipelines incurs additional costs.
High system complexity: Maintaining ETL tools increases operational complexity and distracts from application development.
Data latency: Periodic batch updates common in ETL can delay analysis in near-real-time scenarios.

To address these issues, Alibaba Cloud provides a zero-ETL feature. This feature allows you to quickly build data pipelines between online transactional processing (OLTP) systems and online analytical processing (OLAP) data warehouses. The feature automatically extracts, transforms, and loads data from OLTP systems to OLAP data warehouses. This provides a one-stop solution for data synchronization and management, integrating transaction processing with data analytics. This allows you to focus on data analysis.

Benefits

Easy to use: You do not need to create and maintain complex data pipelines for extract, transform, load (ETL) operations. You only need to select the source data and the destination instance. The system automatically creates a real-time data pipeline. This reduces the challenges of building and managing data pipelines and allows you to focus on application development.
Zero cost: The zero-ETL pipeline does not incur additional fees. You can analyze upstream data in the data warehouse for free.
Multi-source aggregation: You can use zero-ETL pipelines to synchronize data from multiple instances to a single ApsaraDB for ClickHouse instance in real time. This allows you to create a comprehensive analytical view.
Note
If you synchronize data from multiple instances to a single ApsaraDB for ClickHouse instance, the synchronization objects in different tasks cannot overlap.

Supported pipeline

ApsaraDB RDS for MySQL to ApsaraDB for ClickHouse

Billing

The zero-ETL synchronization pipeline is free of charge.

Prerequisites

The ApsaraDB for ClickHouse cluster and the RDS for MySQL instance are located in the same region.
Create database accounts for the source ApsaraDB RDS for MySQL instance and the destination database. For more information, see Create an account for an ApsaraDB RDS for MySQL instance and Create an account for an ApsaraDB for ClickHouse cluster.

Limitations

Type	Description
Limitations on ApsaraDB RDS for MySQL	You cannot synchronize tables without a primary key. `RENAME TABLE` operations are not synchronized. If you select specific tables as synchronization objects and need to edit mappings such as column names, a single synchronization task supports a maximum of 1,000 tables. If you exceed this limit, an error is reported when you submit the task. In this case, we recommend that you split the tables into multiple tasks or configure a synchronization task for the entire database. Binary logs: ApsaraDB RDS for MySQL enables binary logging by default. Ensure that the binlog_row_image parameter is set to full. Otherwise, the precheck fails and the synchronization task cannot start. For instructions, see Configure instance parameters. Important If your source instance is a self-managed MySQL database, enable binary logging and set binlog_format to row and binlog_row_image to full. If your self-managed MySQL database is a dual-primary cluster (where both nodes act as primary and secondary), enable the log_slave_updates parameter so DTS can capture all binary log events. For instructions, see Create an account and configure binary logging for a self-managed MySQL database. The local binary logs for an ApsaraDB RDS for MySQL instance must be retained for at least three days (seven days is recommended). For a self-managed MySQL database, retain local binary logs for at least seven days. Otherwise, DTS may fail to retrieve binary logs, causing the task to fail. In extreme cases, this may cause data inconsistency or data loss. Issues caused by binary log retention periods shorter than DTS requires are not covered under the DTS SLA. Note To configure the retention period for local binary logs on an ApsaraDB RDS for MySQL instance, see Automatically delete local logs. Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails. Note During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database. Data generated by changes that do not write to binary logs—such as data restored from physical backups or created by cascade operations—is not synchronized to the destination database. Note If this occurs, remove the affected database or table from the synchronization objects. Then add it back. You can do this only if your business allows it. For more information, see Modify synchronization objects. If your source database is MySQL 8.0.23 or later and contains invisible hidden columns, DTS may not read those columns. This may cause data loss. Note Run the `ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;` command to make the hidden column visible. For more information, see Invisible Columns. If your ApsaraDB RDS for MySQL instance has Always-Encrypted enabled, full data synchronization is not supported. Note ApsaraDB RDS for MySQL instances with Transparent Data Encryption (TDE) enabled support schema synchronization, full data synchronization, and incremental data synchronization. Read-only ApsaraDB RDS for MySQL instances that do not record transaction logs, such as read-only ApsaraDB RDS for MySQL 5.6 instances, are not supported as source databases. The zero-ETL feature periodically runs the CREATE DATABASE IF NOT EXISTS `test` command on the source database to advance the binary log offset.
Other limitations	ApsaraDB for ClickHouse has a limited range for time-related data types. If the time data in ApsaraDB RDS for MySQL is outside this range, the time values synchronized to ApsaraDB for ClickHouse may be incorrect. For more information about the time range, see Time information.

Usage notes

Notes on creating zero-ETL pipelines
If the number of Zero-ETL links for an ApsaraDB for ClickHouse cluster exceeds the limit, you cannot create new Zero-ETL links. You can use DTS to create a new synchronization link or delete unused Zero-ETL links to create new ones. The limits on the number of Zero-ETL links are as follows:
- The maximum number of links that you can create in an Enterprise Edition cluster is calculated by using the formula [Lower limit of cluster CCU / 8] and rounding up the result. For example, if a cluster has a lower CCU limit of 22 and an upper CCU limit of 36, the calculation uses the lower limit of 22. The result is 22 / 8 = 2.75, which is rounded up to 3. This means that the maximum number of Zero-ETL links that you can create is 3.
- Number of creatable links for a Community Edition cluster: [Total number of cluster cores / 8]. The result is rounded up. For example, for a cluster with two 8-core 32 GB nodes, the total number of CPU cores is 8*2=16. The number of creatable links is then calculated as 16/8=2. This means that you can create a maximum of 2 Zero-ETL links.
Notes on synchronization tasks
- If a DDL statement on the source ApsaraDB RDS for MySQL instance does not comply with standard MySQL syntax, the synchronization task may fail or data may be lost.
- The number of databases to be synchronized must not exceed the ApsaraDB for ClickHouse limit of 256.
- The names of the databases, tables, and columns to be synchronized must comply with the ApsaraDB for ClickHouse naming conventions. For more information, see Limits on object naming conventions.
- If you synchronize one or more tables instead of an entire database, do not use tools like pt-online-schema-change to perform online DDL changes on the source objects during data synchronization. Otherwise, the synchronization fails.
- Before you synchronize data, evaluate the performance of the source and destination databases. We recommend performing data synchronization during off-peak hours. Full data synchronization consumes read and write resources on both the source and destination databases, which may increase the database load.
- During the schema synchronization phase, the zero-ETL feature adds the _sign, _is_deleted, and _version fields to the destination tables.
- If the destination is an ApsaraDB for ClickHouse Community-Compatible Edition cluster, seamless integration creates a local table and a distributed table for the destination.
  - The name of the distributed table is the same as the name of the source table.
  - The name of a local table is <distributed_table_name> + _local.

Data type mappings

MySQL and ApsaraDB for ClickHouse support different data types, so one-to-one type mapping is not always possible. During schema synchronization, DTS maps source types to the closest supported types in ClickHouse. For the full mapping table, see Data type mappings for initial schema synchronization.

Preparations

Create a service-linked role and grant the required management permissions to a RAM user.

Create the AliyunServiceRoleForClickHouseZeroETL service-linked role.
Note
The system automatically creates the AliyunServiceRoleForClickHouseZeroETL service-linked role. If a dialog box prompts you to create this role manually during configuration, you can ignore it.
Grant permissions to a RAM user.
A RAM user must have the following three permissions to create a zero-ETL pipeline. For information about how to grant permissions to a RAM user, see Manage RAM user permissions.
- Permission for the source ApsaraDB RDS for MySQL instance: AliyunRDSFullAccess
- Permission for the destination ApsaraDB for ClickHouse cluster: AliyunClickHouseFullAccess
- Permission for DTS: The following code provides the custom policy script for DTS. For information about how to create a custom policy, 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"
                }
            }
        }
    ]
}
```

Synchronize data

Step 1: Go to the Zero-ETL page

Log on to the ApsaraDB for ClickHouse console. In the top-left corner, select the region where your destination cluster is located.
On the Clusters page, select Clusters of Community-compatible Edition, and click the target cluster ID.
In the left navigation bar, click Zero-ETL.

Step 2: Create and start the zero-ETL task

Click Create Zero-ETL Task to open the Create Zero-ETL Task page.

Enter the Task Name and complete the following configurations.

Configure the source and destination databases.

Configure the source and destination databases with the following parameters, and then click Test Connectivity and Proceed.

Source database

Parameter	Description
Database Type	Only ApsaraDB RDS for MySQL is supported.
Access Method	Only Alibaba Cloud instance is supported.
Instance Region	Select the region of the source instance.
RDS Instance ID	The ID of the ApsaraDB RDS for MySQL instance.
Database Account	The database account of the ApsaraDB RDS for MySQL instance.
Database Account	The password for the database account of the ApsaraDB RDS for MySQL instance.
Encryption	Select Non-encrypted or SSL-encrypted based on your requirements. If you select SSL-encrypted, you must first enable SSL encryption for the RDS MySQL instance. For more information, see Use a cloud certificate to quickly enable SSL encryption.

Destination database

Parameter	Description
Database Type	ClickHouse
Access Method	Only Alibaba Cloud instance is supported.
Instance Region	The region of the destination cluster.
Cluster ID	The ID of the destination cluster.
Cluster Type	The type of the cluster. Valid values: Community-Compatible Edition and Enterprise Edition.
Database Account	The database account of the destination cluster.
Database Password	The password for the database account of the destination cluster.

Configure zero-ETL.
In the Source Objects box, select the objects to synchronize, and then click to move them to the Selected Objects box. Click Next: Configure Database and Table Fields.
Configure database and table fields.
On the Configure Zero-ETL page, configure the Type, Primary Key Column, Sort Key, Distribution Key, and Partition Key of the table to be synchronized in the destination database.
Note
- By default, the page displays information about undefined tables. You can make modifications after setting the Definition Status to All.
- The Primary Key Column and Sort Key can be composite keys. This means you can select multiple fields from the corresponding drop-down lists for the Primary Key Column or Sort Key. You must select one or more columns from the Primary Key Column to use as the Partition Key. You can select only one field for the Distribution Key. For more information about the primary key column, sort key, and partition key, see CREATE TABLE.
- The Partition Key is optional, but it cannot be a nullable field. Otherwise, the synchronization task will fail.
Save the task.
After you configure the database table fields, click Next: Save Task Settings and Precheck.
Note
After this operation, the task is saved, regardless of whether the precheck passes.
Precheck and start the task.
When the Success Rate is 100%, click Start to start the Zero-ETL task.
On the Zero-ETL page, you can view the ID/Name, Source/Destination, and Status of the target Zero-ETL task.
If the precheck fails, adjust the source and destination databases based on the failure information. Then, find the task on the Zero-ETL page, modify it, and run the precheck again. After the precheck is successful, you can start the task.

Monitor zero-ETL tasks

You can use the following methods to monitor zero-ETL tasks. We recommend configuring alerts or event subscriptions to receive timely updates on task status. If a task becomes abnormal, you can use active monitoring to troubleshoot the issue.

Monitoring method	Benefit	Limitation	Actions
Active monitoring	Provides a comprehensive view of the task status, including synchronization performance, details, and logs.	Does not actively notify you when a zero-ETL task becomes abnormal.	Monitor a task in the ApsaraDB for ClickHouse console
Alert monitoring	CloudMonitor automatically sends alert notifications based on alert rules, helping you to promptly identify and handle abnormal monitoring data.	Monitors only the synchronization latency (in milliseconds) of zero-ETL tasks.	Monitor synchronization latency by using CloudMonitor alerts
Event subscription	When a system event for a zero-ETL task meets the alert conditions, CloudMonitor automatically sends an alert notification. This allows you to stay informed about task failures and recoveries and take prompt action.	Monitors only the failure and recovery of zero-ETL tasks.	Subscribe to zero-ETL task events in CloudMonitor

Monitor in the console

Log on to the ApsaraDB for ClickHouse console. In the top-left corner, select the region where your destination cluster is located.
On the Clusters page, select Clusters of Community-compatible Edition, and click the target cluster ID.
In the left navigation bar, click Zero-ETL.
Click Task Details in the Actions column of the target task.
On the task details page, you can view comprehensive information and monitor the task.

CloudMonitor latency alerts

You can create alert rules in CloudMonitor to monitor zero-ETL latency. When a metric meets the alert conditions, CloudMonitor automatically sends an alert notification, helping you to promptly identify and handle abnormal monitoring data.

Step 1: Create a zero-ETL latency alert

For information about how to create a zero-ETL latency alert, see Use the CloudMonitor console. When you create the alert, make sure to set the following parameters.

Parameter	Description
Product	Select Clickhouse - ZeroETL Latency.
Metric	Select Synchronization Latency.

Step 2: View cluster latency

Log on to CloudMonitor.
In the Clickhouse - ZeroETL Latency list, click Monitoring Charts in the Actions column of the target cluster to view the cluster synchronization latency.

CloudMonitor event subscription

If you need to monitor the recovery and failure of zero-ETL tasks and receive timely notifications, you can subscribe to the relevant events.

For information about how to subscribe to Zero-ETL events, see Manage event subscriptions. When you create a subscription policy, make sure to set the following parameters.

Event	Parameter	Description
Zero-ETL task failure	Subscription type	Select System Event.
	Product	Select ApsaraDB for ClickHouse.
	Event type	Select Abnormal.
	Event name	Select ZeroETL task abnormal.
Zero-ETL task recovery	Subscription type	Select System Event.
	Product	Select ApsaraDB for ClickHouse.
	Event type	Select Restore.
	Event name	Select ZeroETLTaskRestore.

FAQ

Q: After using Zero-ETL to synchronize data to ApsaraDB for ClickHouse, why is the data volume in the destination database larger than that in the source database?

Cause: When you perform an UPDATE or DELETE operation on the source, ApsaraDB for ClickHouse writes a new row and uses the _sign, _is_deleted, and _version fields to mark these operations. As a result, the destination database has more rows than the source database.

Solution: When querying, use _sign or _is_deleted conditions to filter out deleted data based on the version. Also, add FINAL after the table name to deduplicate records. For more information about field identifiers, see Field information.

Q: After using Zero-ETL to synchronize data to ApsaraDB for ClickHouse, why does a local table appear in the destination database?

If the destination is an ApsaraDB for ClickHouse Community-Compatible Edition cluster, seamless integration creates a local table and a distributed table for the destination.

The name of the distributed table is the same as the name of the source table.
The name of a local table is <distributed_table_name> + _local.