✨ Synchronizing Data from MySQL to ClickHouse with Alibaba Cloud DTS: A Complete Guide 🔄.

Introduction

In the era of digital transformation, real-time analysis of vast business data has become a necessity. Traditional databases often struggle to meet the performance and real-time analysis demands. As the ClickHouse community grows, developers seek to accelerate analysis by syncing data from MySQL to ClickHouse. 💡

While the MaterializedMySQL engine offers synchronization capabilities, it has several limitations:

• 🚫 Only supports ClickHouse clusters of version 20.8 and later (Community Edition)
• 🕵️ Lacks synchronization process visualization and performance monitoring
• 🔐 Doesn’t support tables without primary keys
• 🌐 Requires the MySQL and ClickHouse clusters to be in the same VPC network
• ⛔ No support for ETL, object name mapping, or appended columns
• 🚫 Doesn’t allow data filtering or selective synchronization
• ⏸️ Synchronization tasks cannot be paused or resumed
• 🚫 Limited support for certain DDL operations

To overcome these challenges, the Data Transmission Service (DTS) team has developed a synchronization link from MySQL to ClickHouse. 🌉

Advantages of Alibaba Cloud DTS for MySQL-to-ClickHouse Synchronization

Alibaba Cloud DTS offers a powerful solution for MySQL-to-ClickHouse synchronization, addressing the limitations of the MaterializedMySQL engine:

• 🌐 High compatibility with ApsaraDB for ClickHouse Community Edition
• 📊 Visualized synchronization process with performance monitoring
• 🔑 Supports migration of tables without primary keys
• 🌐 Flexible selection of VPC networks for source and destination clusters
• ⚙️ Supports features like ETL, object name mapping, and appended columns
• �ulty_man: 🧑‍💻 Allows data filtering and selective synchronization
• ⏯️ Synchronization tasks can be paused and resumed
• 🆕 Supports common DDL operations like table creation, alteration, truncation, and dropping

With these advantages, Alibaba Cloud DTS offers a smooth and reliable experience for syncing data from MySQL to ClickHouse, facilitating efficient data analysis. 📈

Learning to Use Alibaba Cloud DTS for MySQL-to-ClickHouse Synchronization

When using Alibaba Cloud DTS for MySQL-to-ClickHouse synchronization, you may encounter challenges during preparation, task creation, and ongoing maintenance. This guide aims to provide best practices and address common pitfalls, ensuring a worry-free data migration experience and efficient data analysis with ClickHouse. 🚀

1. Best Practices

1.1 Add Permissions to a Database Account

Insufficient permissions for the database account can cause DTS tasks to fail. Before configuring a task, grant the following permissions to the source and destination database accounts:

1.2 Use the Alibaba Cloud DTS Console to Create Tasks

DTS MySQL-to-ClickHouse links have specific requirements for dblist. Using the DTS console will provide a standardized dblist format. If you need to use DTS OpenAPIs, you must specify dblist in the following format:

dbList={ “source_db_name”: { “name”: “target_db_name”, “all”: false, “state”: “open”, “Table”: { “source_table_name”: { “name”: “target_table_name”, “all”: true, “primary_key”: “id”, “message_key”: “id”, “partition_key”: “sipHash64(id)”, “part_key”: “id”, “type”: “partition” } }, “num”: 1 } }

1.3 Use DTS for Schema Migration

The ClickHouse schema greatly impacts data writing performance and stability. When migrating data from MySQL to ClickHouse with DTS, use the provided schema migration feature to avoid errors caused by unsupported schemas.

If you need to create a custom schema, it must meet the following DTS requirements:

• For ClickHouse Community Edition, create a local table and a distributed table. The distributed table name must match the destination table name in dblist. The local table name must be distributed_table_name_local.
• Add two appended columns: _sign and _version:

• DTS supports only ReplacingMergeTree engines:

• For data type mapping, see: https://www.alibabacloud.com/help/en/dts/user-guide/data-type-mappings-for-schema-synchronization

1.4 Select the Appropriate Partition Key

When configuring a MySQL-to-ClickHouse migration task, you can specify a column as the partition key to separate data. ClickHouse creates a new file directory for each key value, logically separating data within a table (e.g., by day). DROP PARTITION allows quick deletion of a data subset.

However, an inappropriate partition key may cause ClickHouse to exceed the limits of the parts_to_throw_insert and max_parts_in_total parameters, leading to the “Too many inactive parts (N). Parts cleaning are processing significantly slower than inserts” error.

To avoid this issue, DTS schema migration includes optimizations:

• If no partition key is specified, no partition by xxx statement is added when creating a table.
• If a partition key is specified:
  • For BIGINT source types, partition by intDiv(XXX, 18014398509481984) is added.
  • For TinyInt, SmallInt, or MediumInt source types, partition by intDiv(XXX, 4194304) is added.
  • For time-based source types (date, datetime, timestamp), partition by toYYYYMM(update_time) is added.
  • For other source types, no partition by xxx statement is added.

If you don’t use DTS to migrate the tables you created, select an appropriate partition key to avoid the “Too many inactive parts” error during data writing to DTS.

1.5 Select an Appropriate Shard Key

During data writing, the distributed table distributes data to the local table of each host node based on the shard key rules. The shard key must return an integer value, which can be a specific integer column field. If the shard key value is not an integer type, an error will occur when creating a table for schema migration (e.g., Sharding expression has type String, but should be one of integer type.).

To avoid this issue, DTS schema migration includes optimizations:

• If no shard key is specified, rand() is used.
• If a shard key is specified:
  • For multiple columns, sipHash64(A, B, C) is used.
  • For a single integer column, the column is used.
  • For a single non-integer column, sipHash64(A) is used.

2. Trials and Errors

2.1 What to Do When Data is Duplicated?

ClickHouse doesn’t require a unique primary key, allowing multiple rows with the same primary key value. DTS may introduce duplicate data in the following scenarios:

• During full migration, DTS divides a table’s data into slices for concurrent pulling and writing. If you pause and restart the task while migrating a slice, DTS will re-migrate data from that slice, causing duplication.
• During incremental migration, DTS uses the same solution as the MaterializeMySQL engine, updating/deleting versioning based on the ReplacingMergeTree engine. When updating data, an identical row with _sign=1 is inserted. When deleting data, an identical row with _sign=-1 is inserted. During partition merging, ReplacingMergeTree deletes duplicate data and retains the row with the largest _version value.

To remove duplicate data, you can:

• Execute the OPTIMIZE TABLE xxx statement to force ClickHouse to merge partitions and remove duplicates.
• Add FINAL when querying data. To filter deleted data, add _sign>0. For example, SELECT * FROM table_name FINAL WHERE _sign>0;

2.2 Why Is the DateTime Data Inconsistent with the Source?

ClickHouse’s time types (Date, Date32, DateTime, and DateTime64) are based on Unix timestamps. If the time in ApsaraDB RDS for MySQL is not within the following ranges, it will be converted to these ranges, potentially causing inconsistency with the source: