ApsaraDB for ClickHouse:Migrate data from a self-managed ClickHouse cluster to ApsaraDB for ClickHouse Enterprise Edition

Notes

Step 1: Check the self-managed cluster and enable system tables

Before data migration, modify the config.xml file based on whether system.part_log and system.query_log are enabled in the self-managed cluster to support incremental migration.

<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>

Step 2: Configure the destination cluster to match the self-managed cluster version

To ensure the destination cluster behaves as similarly as possible to the self-managed cluster, connect to the destination cluster and set the compatibility parameter to match the version of the self-managed cluster.

Example:

SELECT currentProfiles(); // Get the profile used by the user
SELECT
    profile_name,
    setting_name,
    value
FROM system.settings_profile_elements
WHERE (setting_name = 'compatibility') AND (profile_name = 'xxxx'); // Query the compatibility setting
ALTER PROFILE XXXX SETTINGS compatibility = '23.8'; // Modify the profile

Step 3: Create a migration task

1. Log on to the ApsaraDB for ClickHouse console. On the Clusters page, select Enterprise Edition Clusters and click the ID of the destination cluster.

2. In the navigation pane on the left, choose Data Migration and Synchronization > Migration from ClickHouse.

3. Click Create Migration Task.

4. Select source and destination instances.

   Configuration item	Description	Example
   Task Name	The task name can contain only letters and digits. Names are case-insensitive and must be unique.	MigrationTask1229
   Source Cluster Name	Run SELECT * FROM system.clusters; to get the cluster name of the self-managed instance.	default
   VPC IP Address	The IP address and port of each shard in the cluster, separated by commas. Format: IP:PORT,IP:PORT,.... Run the following SQL statement to get the IP addresses and ports of the self-managed cluster: SELECT shard_num, replica_num, host_address as ip, port FROM system.clusters WHERE cluster = '<cluster_name>' and replica_num = 1; Parameters: cluster_name: The name of the destination cluster. replica_num=1 selects the first replica. You can also select other replicas or manually pick one replica per shard. Important Do not use the VPC domain name or SLB address of ClickHouse. If IP and port mapping is applied for Alibaba Cloud, configure the mapped IP and port based on network connectivity.	192.168.0.5:9000,192.168.0.6:9000
   Database Account	The database account of the self-managed cluster.	test
   Database Password	The password of the database account for the self-managed cluster.	test******
   Source Instance Kernel Version	Click Get Version.	22.8.5.29

5. Based on the source instance version, perform the corresponding operation:

   • If the source instance version is 22.10 or later, click Next.

   • If the source instance version is earlier than 22.10, enter the Destination Instance Information as prompted and click Next.

   • If version retrieval fails, check for errors such as incorrect source instance information or network connectivity issues. Fix the issues and click Get Version again.

   Note

   Due to parameter incompatibilities between older community editions and Enterprise Edition, if the source instance version is earlier than 22.10, you must use push-based synchronization from source to destination. In this case, you must map the destination IP to the self-managed network. If the self-managed network and the Enterprise Edition instance are in the same VPC or connected via VPC peering, you can use the original IP addresses directly.

6. Check connectivity and configuration.

   1. Click Start Check.

      Click to view check items.

      • Connectivity verification: Full network connectivity between the self-managed and destination instances, with mutual access between all nodes.

      • Account permission verification: The source account and password are correct and can connect to the source instance.

      • Source instance system table check: The self-managed instance must have the following three system tables: system.query_log, system.parts, and system.part_log.

      • Configuration check: The self-managed and destination instances must have the same time zone, and the compatibility setting of the destination instance must match the source version.

   2. During the check, click the icon in the upper-right corner to view real-time progress.

   3. After the check completes, proceed based on the results.

      Select a result level and check item, then click the button to view details. Results are interpreted as follows:

      • Success: If all checks pass, click Next to continue.

      • Warning: These are non-blocking issues. Confirm whether they affect your business or migration. You can ignore the warning or fix the issue and click Start Check again to recheck.

      • Error: These are blocking issues. Fix the error and click Start Check again to recheck.

        For error messages and solutions, see FAQ.

7. Check database and table schemas.

   1. Click Start Check.

   2. During the check, click the icon in the upper-right corner to view real-time progress.

   3. After the check completes, proceed based on the results.

      For result interpretation, see Step 5.

8. Migrate database and table schemas.

   1. Click Start Migration.

   2. During migration, click the icon in the upper-right corner to view real-time progress.

   3. Once the inspection is complete, you can proceed with the next steps based on the inspection results.

      For result interpretation, see Step 5.

9. (Optional) Check SQL compatibility.

   SQL compatibility checking replays SQL statements from the self-managed instance on the destination instance to verify syntax compatibility across kernel versions. Decide whether to perform this step based on your needs.

   • To skip, click Skip.

   • To perform the check, select a request replay time and click Start Check. After a successful check, click Next. For failed checks, see Step 5 for resolution.

     Important

     • This check validates syntax compatibility only and does not require data. To test with data, migrate partial data in the next step first.

     • False positives may occur due to client version mismatches with the destination instance. Validate SQL statements manually if needed.

10. Start synchronization.

    1. Click Start Sync.

    2. During synchronization, click the icon in the upper-right corner to view real-time progress.

       You can control the migration process using the following operations: Stop, Restart, and Cancel Migration. Click to view operation meanings and impacts.

       Operation	Feature Definition	Impact	Scenario
       Stop	Immediately stops data migration but continues migrating remaining table schemas.	Some data might not be migrated completely. Before restarting migration, clean up migrated data in the destination cluster to avoid duplicates.	Stop the migration task after full data migration. Test after migrating partial data without stopping writes to the self-managed cluster.
       Restart	Retries the current step after resolving errors encountered during schema or data migration.	None	Resume migration from the breakpoint after fixing an error.
       Cancel migration	Forcefully cancels the task and skips subsequent steps. Important After cancellation, the migration task is locked and cannot be modified. Use the Previous, Next, or Refresh buttons to view step results.	The migration is terminated. The destination instance may have incomplete schemas or configurations and cannot be used for business. Before restarting migration, clean up migrated data in the destination cluster to avoid duplicates.	End migration quickly to resume writes to the self-managed cluster.

    3. When the process reaches Migrate Data, switch to the Migrate Data tab and click the button to view Migration Progress and estimated remaining time.

       Click to learn how to evaluate whether migration can complete.

       Migration success depends on the relationship between migration speed and write speed of the self-managed cluster.

       • Test migration speeds are as follows:

         Average part size	Source instance type	Source disk type	Destination instance type	Destination storage medium	Number of cluster nodes	Migration speed per node	Overall migration speed
         402.54MB	8C32G	PL1	16CCU	OSS	16	47MB/s	752.34MB/s
         402.54MB	80C384G	PL3	48CCU	ESSD_L2	8	197.74MB/s	1581.95MB/s

       • Compare write speeds of the destination and self-managed clusters:

         Migration speed depends on factors such as part size (in tests, average part sizes between 100 MB and 10 GB maintained high speeds), instance type, disk type, and data characteristics. Therefore, test data is for reference only. Determine the actual write speed of the destination cluster by monitoring its disk throughput. For more information, see View cluster monitoring information.

         • If the destination cluster write speed is lower than the self-managed cluster write speed, migration has a high failure rate. Cancel the migration task and use manual migration.

         • If the destination cluster write speed is higher than the self-managed cluster write speed, proceed. To improve success rate, ensure the migration duration calculated as data volume / (migration speed - self-managed write speed) is five days or less.

       Important

       Monitor the Migration Progress closely. Based on the estimated remaining time, stop writes to the self-managed cluster and handle Kafka and RabbitMQ engine tables.

       Click to learn how to estimate when to stop writes to the self-managed cluster.

       To ensure data completeness after migration, stop business writes and delete Kafka and RabbitMQ tables before traffic switchover. Perform the following steps:

       1. Log on to the self-managed cluster and run the following statement to query tables to handle:

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

       2. View the CREATE TABLE statement for the target table:

          SHOW CREATE TABLE <aim_table_name>;

       3. Connect to the destination cluster and run the CREATE TABLE statement obtained in the previous step.

       4. Log on to the self-managed cluster and delete migrated Kafka and RabbitMQ engine tables.

          Important

          When deleting Kafka tables, also delete materialized views that reference them. Otherwise, materialized view migration fails, causing the entire migration to fail.

    4. When Migration Progress reaches 100% and you confirm the source instance has stopped writes, click Stop to end the migration and proceed.

       

    5. After synchronization completes, click Completed.

       Important

       After the "Start synchronization" step completes, the migration task is locked and cannot be modified. Use the Previous, Next, or Refresh buttons to view step results.

Step 4: Migrate business data for non-MergeTree tables

During migration, non-MergeTree tables support only schema migration (such as MySQL tables) or no migration at all (such as Log tables). After migration, some tables in the destination cluster may have schemas but no data. Complete data migration manually as follows:

1. Log on to the self-managed cluster and run the following statement to identify non-MergeTree tables requiring 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. Log on to the destination cluster and use the remote function to migrate data.