ApsaraDB for ClickHouse:Migrate from self-managed to Community-Compatible ClickHouse

Step 1: Enable system tables in the self-managed cluster

Console migration requires system.part_log and system.query_log to be enabled and correctly configured in the self-managed cluster's config.xml.

<part_log>
    <database>system</database>
    <table>part_log</table>
    <partition_by>event_date</partition_by>
    <order_by>event_time</order_by>
    <ttl>event_date + INTERVAL 15 DAY DELETE</ttl>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</part_log>

<query_log>
    <database>system</database>
    <table>query_log</table>
    <partition_by>event_date</partition_by>
    <order_by>event_time</order_by>
    <ttl>event_date + INTERVAL 15 DAY DELETE</ttl>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>

DROP TABLE system.part_log;
DROP TABLE system.query_log;

Step 2: Set compatibility on the destination cluster

Configure the destination cluster to use the same compatibility level as the self-managed cluster. This minimizes changes required after migration.

1. Check the version of both clusters:

   SELECT version();

2. If the versions differ, set the compatibility parameter on the destination cluster to match the self-managed cluster version. For example:

   SET GLOBAL compatibility = '22.8';

Step 3 (Optional): Enable the MaterializedMySQL engine

If the self-managed cluster has tables using the MaterializedMySQL engine, run the following on the destination cluster:

SET GLOBAL allow_experimental_database_materialized_mysql = 1;

The ClickHouse community no longer maintains the MaterializedMySQL engine. After migration, use Data Transmission Service (DTS) to synchronize MySQL data with ReplacingMergeTree tables instead. See Use seamless integration to synchronize data from ApsaraDB RDS for MySQL to an ApsaraDB for ClickHouse cluster and Synchronize data from ApsaraDB RDS for MySQL to an ApsaraDB for ClickHouse cluster.

Step 4: Create a migration task

1. Log in to the ApsaraDB for ClickHouse console.

2. On the Clusters page, select Clusters of Community-compatible Edition and click the destination cluster ID.

3. In the left navigation pane, click Data Migration and Synchronization > Migrate from ClickHouse.

4. Click Create Migration Task.

5. Configure the source cluster connection settings: To get IP addresses and ports for the source cluster: replica_num = 1 selects the first replica set. You can select other replica sets as needed.

   Cross-account or cross-region migration

   Cross-account or cross-region Alibaba Cloud ClickHouse instance: ``sql SELECT shard_num, replica_num, host_address AS ip, port FROM system.clusters WHERE cluster = 'default' AND replica_num = 1; ``

   Non-Alibaba Cloud instance migration

   Non-Alibaba Cloud ClickHouse instance: ``sql SELECT shard_num, replica_num, host_address AS ip, port FROM system.clusters WHERE cluster = '<cluster_name>' AND replica_num = 1; ``

   Source cluster parameters

   Source cluster settings:

   Field	Description	Example
   Source Access Method	Select Express Connect, VPN Gateway, Smart Access Gateway, or Self-managed ClickHouse Clusters on an ECS Instance.	—
   Cluster Name	A name for the source cluster. Digits and lowercase letters only.	source
   Source Cluster Name	The internal cluster name from SELECT * FROM system.clusters;.	default
   VPC IP Address	TCP IP:PORT pairs for each shard, comma-separated. Do not use VPC domain names or SLB addresses. Format: IP:PORT,IP:PORT,...	192.168.0.5:9000,192.168.0.6:9000
   Database Account	Account for the source cluster.	test
   Database Password	Password for the source cluster account.	—

   Destination cluster parameters

   Destination cluster settings:

   Field	Description
   Database Account	Account for the destination cluster.
   Database Password	Password for the destination cluster account.

   

6. Click Test Connection and Proceed. If the test fails, fix the connection settings and try again.

7. Review the migration content and click Next: Pre-detect and Start Synchronization.

8. The system runs three pre-checks. If all checks pass: If a check fails, follow the error guidance in Pre-check error messages and solutions.

   1. Read the impact prompts carefully.

   2. Click Completed to create and start the task.

   Check item	Requirement
   Instance Status Detection	No running management tasks (scale-out, upgrade, or downgrade) on either cluster.
   Storage Space Detection	Destination cluster storage ≥ 1.2× the used space of the self-managed cluster.
   Local Table and Distributed Table Detection	Each local table in the self-managed cluster must have exactly one corresponding distributed table.

   

Step 5: Assess whether the migration can complete

Skip this step if the source cluster write speed is less than 20 MB/s.

If the source cluster write speed exceeds 20 MB/s, check that the destination cluster can keep up:

1. View the Disk throughput metric on the destination cluster. See View cluster monitoring information.

2. Compare write speeds:

   • Destination write speed > source write speed: migration is likely to succeed. Continue to Step 6.

   • Destination write speed < source write speed: migration is likely to fail. Cancel the task and use Manual migration instead.

Step 6: Monitor the migration task

1. Log in to the ApsaraDB for ClickHouse console.

2. On the Clusters page, select Clusters of Community-compatible Edition and click the destination cluster ID.

3. In the left navigation pane, click Data Migration and Synchronization > Migrate from ClickHouse.

4. Monitor the task status and Running Information column.

To view task details, click View Details in the Actions column. The details page shows migrated table schemas, database structures, and any error messages.

After the task ends (status: Completed or Canceled), the View Details page is cleared. To view migrated table schemas afterward, run:

SELECT `database`, `name`, `engine_full`
FROM `system`.`tables`
WHERE `database` NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema');

Task statuses:

Step 7: Stop writes and handle Kafka and RabbitMQ tables

Before switching business traffic to the destination cluster, stop all writes to the self-managed cluster and manually create Kafka and RabbitMQ tables on the destination.

1. On the self-managed cluster, identify the tables to process:

   SELECT * FROM system.tables WHERE engine IN ('RabbitMQ', 'Kafka');

2. Get the DDL for each table:

   SHOW CREATE TABLE <target_table_name>;

3. On the destination cluster, run the DDL statements to create the tables. See Connect to a ClickHouse cluster using DMS for connection instructions.

4. On the self-managed cluster, delete the Kafka and RabbitMQ tables.

Step 8: Complete the migration

After writes to the self-managed cluster are stopped, finalize the migration. This migrates remaining data, runs a data volume check, and migrates any outstanding database and table schemas.

1. Log in to the ApsaraDB for ClickHouse console.

2. On the Clusters page, select Clusters of Community-compatible Edition and click the destination cluster ID.

3. In the left navigation pane, click Data Migration and Synchronization > Migrate from ClickHouse.

4. In the Actions column for the task, click Complete Migration.

5. In the dialog box, click OK.

Step 9: Migrate data for non-MergeTree tables

The migration task copies table schemas for non-MergeTree tables (external tables, Log tables) but not their data. Migrate the data manually.

1. On the self-managed cluster, identify tables that need manual data migration:

   SELECT
       `database` AS database_name,
       `name` AS table_name,
       `engine`
   FROM `system`.`tables`
   WHERE (`engine` NOT LIKE '%MergeTree%')
     AND (`engine` != 'Distributed')
     AND (`engine` != 'MaterializedView')
     AND (`engine` NOT IN ('Kafka', 'RabbitMQ'))
     AND (`database` NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema'))
     AND (`database` NOT IN (
         SELECT `name`
         FROM `system`.`databases`
         WHERE `engine` IN ('MySQL', 'MaterializedMySQL', 'MaterializeMySQL', 'Lazy', 'PostgreSQL', 'MaterializedPostgreSQL', 'SQLite')
     ))

2. On the destination cluster, migrate the data using the remote function. See Migrate data using the remote function in the Manual migration section.

Step 1: Migrate table schemas (DDL)

Install clickhouse-client at the same version as the destination ApsaraDB for ClickHouse cluster. See clickhouse-client for download instructions.

1. List databases in the self-managed cluster:

   clickhouse-client \
     --host="<old_host>" --port="<old_port>" \
     --user="<old_username>" --password="<old_password>" \
     --query="SHOW databases" > database.list

   The system database does not need to be migrated.

2. List tables in the self-managed cluster. Use either approach:

   Tables with names starting with .inner. are internal representations of materialized views and do not need to be migrated.

   clickhouse-client \
     --host="<old_host>" --port="<old_port>" \
     --user="<old_username>" --password="<old_password>" \
     --query="SHOW tables from <database_name>" > table.list

   Or query all databases and tables at once:

   SELECT DISTINCT database, name FROM system.tables WHERE database != 'system';

3. Export the DDL for all tables in a database:

   clickhouse-client \
     --host="<old_host>" --port="<old_port>" \
     --user="<old_username>" --password="<old_password>" \
     --query="SELECT concat(create_table_query, ';') FROM system.tables WHERE database='<database_name>' FORMAT TabSeparatedRaw" > tables.sql

4. Create the target database on the destination cluster, then import the DDL:

   Parameter	Description
   	Address of the self-managed cluster.
   	Port of the self-managed cluster.
   	Account with DML read/write, settings, and DDL permissions.
   	Password for the account.
   <new_host>	Address of the destination ApsaraDB for ClickHouse cluster.
   <new_port>	Port of the destination ApsaraDB for ClickHouse cluster.
   <new_username>	Account with DML read/write, settings, and DDL permissions.
   <new_password>	Password for the account.

   clickhouse-client \
     --host="<new_host>" --port="<new_port>" \
     --user="<new_username>" --password="<new_password>" \
     -d '<database_name>' --multiquery < tables.sql

Step 2: Migrate data

Three options are available. Use the remote function (Option A) when possible, as it supports partition-level migration and reduces resource usage.

clickhouse-client \
  --host="<old_host>" --port="<old_port>" \
  --user="<old_username>" --password="<old_password>" \
  --query="SELECT * FROM <database_name>.<table_name> FORMAT CSV" > table.csv

clickhouse-client \
  --host="<new_host>" --port="<new_port>" \
  --user="<new_username>" --password="<new_password>" \
  --query="INSERT INTO <database_name>.<table_name> FORMAT CSV" < table.csv

clickhouse-client \
  --host="<old_host>" --port="<old_port>" \
  --user="<username>" --password="<password>" \
  --query="SELECT * FROM <database_name>.<table_name> FORMAT CSV" | \
clickhouse-client \
  --host="<new_host>" --port="<new_port>" \
  --user="<username>" --password="<password>" \
  --query="INSERT INTO <database_name>.<table_name> FORMAT CSV"