ApsaraDB for ClickHouse:Migrate self-managed ClickHouse to ApsaraDB for ClickHouse Enterprise Edition

Considerations

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

For an incremental migration, you must update the config.xml file on your self-managed cluster to configure system.part_log and system.query_log.

<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 target cluster compatibility

To ensure that the behavior of the target cluster is as compatible as possible with the self-managed cluster, connect to the target cluster and modify the compatibility parameter to make the version numbers of the target cluster and the self-managed cluster consistent.

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 configuration value.
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 then click the ID of the target cluster.

2. In the left-side navigation pane, click Data Migration and Synchronization > Migration from ClickHouse.

3. Click Create Migration Task.

4. Select Source and Target Instances.

   Parameter	Description	Example
   Task Name	The name of the migration task. It can contain only uppercase letters, lowercase letters, and digits. The name must be unique and is case-insensitive.	MigrationTask1229
   Source Cluster Name	Obtain the cluster name of the self-managed instance by running SELECT * FROM system.clusters; .	default
   VPC IP Address	The IP and PORT addresses of each shard in the cluster are separated by commas. Format: IP:PORT,IP:PORT,.... You can use the following SQL statement to get the IP address and port 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 self-managed cluster. replica_num=1 selects the first replica. You can also select other replica sets or choose one replica from each shard. Important You cannot use the VPC domain name or SLB address of ClickHouse. If you use Network Address Translation (NAT), provide the public-facing IP address and port of the shard.	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 for the database account of the self-managed cluster.	test******
   Source Instance Kernel Version	Click Get Version.	22.8.5.29

5. Based on the retrieved source instance version, perform one of the following operations:

   • 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 then click Next.

   • If version retrieval fails: Retrieval may fail due to incorrect source instance information or network connectivity issues. Resolve the issue based on the prompt, and then click Get Version again.

   Note

   Due to parameter incompatibility between earlier Community versions and the Enterprise Edition, if the source instance version is earlier than 22.10, you must synchronize data by pushing data from the source to the target. This requires making the target cluster's IP address routable from the self-managed network. If the self-managed network and the Enterprise Edition instance are in the same VPC, or if they are connected through a VPC peering connection, you can use the original IP address directly.

6. Check connectivity and configuration

   1. Click Start Check.

      Click to view check items.

      • Connectivity Verification: Ensure there is full network connectivity between the self-managed instance and the target instance, allowing mutual access between all nodes.

      • Account Permission Verification: Verifies that the source account and password are correct and can be used to connect to the source instance.

      • Check source instance system tables: A self-managed instance must have the system.query_log, system.parts, and system.part_log system tables.

      • Check the configuration: The self-managed instance and the Target Instance have the same Time Zone, and the Target Instance's compatibility parameter is the same as the source version.

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

   3. After the check is complete, proceed with the subsequent operations based on the results.

      You can select a Result Level and check item, then click the button to view the corresponding check results. The results are described as follows.

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

      • Warning: Indicates a non-blocking issue. You must determine if the warning affects your business or the migration. You can either ignore the warning or resolve the issue based on the warning message and then click Start Check again.

      • Error: This indicates a blocking issue. You must resolve the error based on the error message and then click Start Check again.

        For error messages and solutions, see FAQ.

7. Check database and table schema

   1. Click Start Check.

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

   3. After the check is complete, proceed with the subsequent operations based on the results.

      For a description of the check results, see step 6.

8. Database and table schema migration

   1. Click Start Migration.

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

   3. After the check is complete, proceed with the subsequent operations based on the results.

      For a description of the check results, see step 6.

9. (Optional) Check SQL compatibility

   The SQL compatibility check verifies syntax compatibility between different kernel versions by replaying SQL statements from the self-managed instance on the target instance. You can decide whether to perform this step based on your needs.

   • To skip this step, click Skip.

   • To perform this step, select a Request Replay Time and then click Start Check. If the check passes, click Next. If the check fails, see step 6 for how to handle it.

     Important

     • This check only verifies syntax compatibility and does not require data in the instance's databases and tables. If you need data, you can first proceed to the next step to migrate some data.

     • False positives may occur if the client version used for replaying SQL does not match the target instance. If you encounter an exception, run the SQL statement manually to verify the error.

10. Start synchronization

    1. Click Start Sync.

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

       During the synchronization process, you can use the Stop, Restart, and Cancel Migration operations to control the migration flow. Click to view a description and the impact of each operation.

       Actions	Functionality	Impact	Use Case
       Stop	Immediately stops data migration and migrates the remaining database and table schemas.	Data may not be fully migrated. Before you restart the migration, you must clear the migrated data from the target cluster to avoid data duplication.	To proactively stop the migration task after all data has been migrated. To test the migration after a portion of the data has been migrated, without stopping writes to the self-managed cluster.
       Restart	If an exception occurs while checking and cleaning other databases/tables, migrating data, or migrating remaining schemas, you can resolve the issue based on the error message. This action will retry the current step and then proceed with the subsequent steps.	None	To resume the migration from a breakpoint after resolving an exception that occurred during the process.
       Cancel Migration	Forcibly cancels the task and skips all subsequent steps. Important After you cancel the migration, the migration task is locked, preventing you from modifying the migration flow. You can use the Previous, Next, or Refresh buttons to view the execution results of the migration steps.	The migration task is forcibly terminated. The target instance's database and table schemas and configurations may be incomplete and cannot be used for business operations. Before you restart the migration, you must clear the migrated data from the target cluster to avoid data duplication.	To quickly end the migration and re-enable writes if the migration task is affecting the self-managed cluster.

    3. When the Migrate Data step is running, switch to the Migrate Data tab and click the button to view the Migration Progress and Estimated Time Remaining.

       Click to see how to evaluate if the migration can be completed.

       Whether the migration can succeed depends on the relationship between the migration speed and the write speed of the self-managed cluster.

       • The following table shows migration speed data from our tests:

         Average part size	Source Instance Type	Source Disk Type	Target Instance Type	Target Storage Medium	Number of Cluster Nodes	Single-Node Migration Speed	Overall Migration Speed
         402.54 MB	8C32G	PL1	16CCU	OSS	16	47 MB/s	752.34 MB/s
         402.54 MB	80C384G	PL3	48CCU	ESSD_L2	8	197.74 MB/s	1581.95 MB/s

       • Determine the relationship between the write speeds of the target and self-managed clusters:

         Data migration speed depends on factors such as part size (in our tests, migration speeds were higher when the average part size was between 100 MB and 10 GB), instance type, disk type, and business data characteristics. Therefore, the test data is for reference only. To determine the actual write speed of the target cluster, you need to check its disk throughput. For instructions on how to check disk throughput, see View cluster monitoring information.

         • If the write speed of the target cluster is slower than the write speed of the self-managed cluster, the migration is likely to fail. We recommend that you cancel the migration task and use manual migration instead.

         • If the write speed of the target cluster is faster than the write speed of the self-managed cluster, you can continue with the subsequent steps. To improve the likelihood of a successful migration, we recommend that the time required for the migration, calculated as Data Volume / (Migration Speed - Self-managed Instance Write Speed), be 5 days or less.

       Important

       • You must closely monitor the Migration Progress of the target task. Based on the Estimated Time Remaining, stop writes to the self-managed cluster and handle tables with the Kafka and RabbitMQ engines.

       • The current maximum migration time threshold is set to 5 days. If the migration continues for 5 days, the system will automatically cancel the migration task. If your migration task requires more time, submit a ticket to contact technical support to adjust the threshold.

       Click to see how to estimate when to stop writing to the self-managed cluster.

       Before switching over your business traffic, you must ensure that no new data is written to the self-managed instance to guarantee data integrity after migration. To do this, stop business writes and delete the Kafka and RabbitMQ tables. The procedure is as follows:

       1. Log on to the self-managed cluster and run the following statement to query the tables that need to be handled:

          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 target cluster and run the CREATE TABLE statement you obtained in the previous step.

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

          Important

          When you delete a Kafka table, you must also delete any materialized views that reference it. Otherwise, the materialized views cannot be migrated, which will cause the entire migration to fail.

    4. When the Migration Progress reaches 100% and you have confirmed that the source instance has stopped receiving writes, click the Stop button to end the migration process and proceed to the next steps.

       

    5. After the synchronization is complete, click Completed.

       Important

       After the "Start Synchronization" step is complete, the migration task is locked, which means you are not allowed to modify the migration flow. You can use the Previous, Next, or Refresh buttons to view the execution results of the migration steps.

Step 4: Migrate data from non-MergeTree tables

For non-MergeTree tables, the migration task migrates only the table schema (for example, MySQL tables) or does not support them at all (for example, Log tables). Therefore, after the migration task is complete, the target cluster may contain tables that have a schema but no business data. You must migrate this business data manually by following these steps:

1. Log on to the self-managed cluster and view the non-MergeTree tables whose data needs to be migrated.

   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 target cluster and perform data migration using the remote function.