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

Considerations

Step 1: Check cluster and enable system tables

Before you begin the data migration, configure the config.xml file on your self-managed cluster to enable incremental migration. The configuration depends on whether the system.part_log and system.query_log system tables are already enabled.

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

To ensure the target cluster is as compatible as possible with the self-managed cluster, connect to the target cluster and modify the compatibility parameter to match the version of your self-managed cluster.

For example:

SELECT currentProfiles(); // Get the current profile name.
SELECT
    profile_name,
    setting_name,
    value
FROM system.settings_profile_elements
WHERE (setting_name = 'compatibility') AND (profile_name = 'xxxx'); // Check the value of the compatibility setting.
ALTER PROFILE XXXX SETTINGS compatibility = '23.8'; // Set the compatibility value.

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 navigation pane, choose Data Migration and Synchronization > Migration from ClickHouse.

3. Click Create Migration Task.

4. Select the source and target instances.

   Parameter	Description	Example
   Task Name	A unique, case-insensitive name for the migration task. The name can contain only letters and digits.	MigrationTask1229
   Source Cluster Name	Run SELECT * FROM system.clusters; to obtain the cluster name of your self-managed cluster.	default
   VPC IP Address	The IP addresses and ports for each shard in the cluster, separated by commas. Format: IP:PORT,IP:PORT,.... You can use the following SQL statement to get the IP addresses and ports of your self-managed cluster: SELECT shard_num, replica_num, host_address as ip, port FROM system.clusters WHERE cluster = '<cluster_name>' and replica_num = 1; Parameter description: cluster_name: The name of your self-managed cluster. replica_num=1 selects the first replica set. You can also select another replica set or manually choose one replica from each shard. Important You cannot use the VPC domain name or SLB address of the ClickHouse cluster. If you use NAT to map IP addresses and ports to Alibaba Cloud, you must configure the mapped IP addresses and ports according to your network setup.	192.168.0.5:9000,192.168.0.6:9000
   Database Account	The database account for 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 source instance version, proceed as follows:

   • 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: This can happen if the source instance information is incorrect or the network is disconnected. Follow the prompt to resolve the issue, and then click Get Version again.

   Note

   Due to parameter incompatibilities between earlier community editions and the Enterprise Edition, if the source instance version is earlier than 22.10, you must synchronize data by pushing it from the source to the target. In this scenario, you must map the target instance's IP address to 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 addresses for the connection.

6. Check connectivity and configurations.

   1. Click Start Check.

      The check verifies the following items:

      • Connectivity verification: Verifies that the self-managed cluster and the target instance have full network connectivity and that all nodes can access each other.

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

      • Source instance system table check: Verifies that the self-managed instance has the system.query_log, system.parts, and system.part_log system tables.

      • Configuration check: Verifies that the self-managed instance and target instance use the same time zone, and the compatibility parameter of the target instance matches the source instance version.

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

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

      You can select a Result level and a check item, and then click the icon to view the corresponding results. Result levels are described below.

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

      • Warning: These are non-blocking items. You must manually confirm if the warning affects your workload or the migration task. You can either ignore the warning or resolve the issue and click Start Check again.

      • Error: These are blocking items. You must resolve the error using the provided information and then click Start Check again.

        For information about error messages and solutions, see FAQ.

7. Check Database and Table Structures.

   After the connectivity and configuration check passes, proceed to check the database and table structures. This step consists of three sub-steps: Select databases to migrate, Select tables to migrate, and Check database and table structures.

   Important

   The Select databases to migrate and Select tables to migrate sub-steps are optional. If you want to migrate all databases and tables from the source instance, you can skip these two sub-steps and proceed directly to sub-step 3 (Check database and table structures).

   Sub-step 1: Select databases to migrate (Optional)

   1. Click Query Source. The system automatically queries all databases on the source instance.

   2. During the query, you can click Query Results to view the real-time results.

   3. After the query is complete, select the databases to migrate based on your business requirements. After the selection is complete, click Confirm.

   Sub-step 2: Select tables to migrate (Optional)

   1. After you select the databases, the interface for selecting tables is displayed. Click Query Source. The system automatically queries all tables in the selected databases.

   2. During the query, you can click Query Results to view the real-time results.

   3. After the query is complete, select the tables to migrate based on your business requirements. After the selection is complete, click Confirm.

   Note

   By default, all tables are selected. If you only need to migrate specific tables, deselect the select-all checkbox for the corresponding database, expand the dropdown list, and select the tables you need.

   Sub-step 3: Check database and table structures

   1. Click Start Check. The system checks the database structures, table structures, and UDFs to identify any incompatibilities between the source and target instances.

   2. During the check, you can filter by Result Level and Check Item, and click the Refresh icon to view real-time results.

   3. Check results are classified into three levels: success, warning, and error.

      • Check succeeds: Click Next to proceed with the migration.

      • Warnings occur during the check

        Set the result level to Warning and find the corresponding check items to review the warning details.

        Important

        Warning-level check items are non-blocking. You must confirm whether the warning affects your workload or the migration task. After your confirmation, you have two options:

        • Ignore the warning and click Next to continue the migration.

        • Resolve the warning based on the provided details, and then click Start Check to recheck the database and table structures. For information about warning messages and solutions, see the FAQ section of this topic.

      • Check fails

        Set the result level to Error and find the corresponding check items to review the error details.

        Important

        Error-level check items are blocking. You must resolve the errors based on the provided details, and then click Start Check to recheck the database and table structures. For information about error messages and solutions, see the FAQ section of this topic.

8. Migrate Database and Table Structures.

   1. Click Start Migration.

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

   3. After the migration is complete, proceed based on the results.

      For information about the results, see Step 5.

9. (Optional) Check SQL compatibility.

   The SQL compatibility check replays SQL statements from your self-managed instance on the target instance to verify syntax compatibility between different kernel versions.

   • To skip this step, click skip.

   • To perform this check, select a Request replay time and click Start Check. If the check passes, click Next. If the check fails, see Step 5 for solutions.

     Important

     • The instance databases and tables contain no data, so this check only validates syntax compatibility. To test with data, you can migrate some data in the next step.

     • Mismatches between the client version used for SQL replay and the target instance can cause false positives. If errors occur, run the SQL statements manually to verify the results.

10. Record and clean up Kafka/RabbitMQ engine tables.

    Before starting synchronization, record the definitions of Kafka/RabbitMQ engine tables and their downstream materialized views on the self-managed cluster, handle implicit tables, and then drop these tables to avoid migration exceptions.

    1. Log on to the self-managed cluster and query all Kafka and RabbitMQ engine tables along with their downstream dependencies.

       /*
       create_table_query: table definition
       dependencies_database: database of the downstream dependent table
       dependencies_table: name of the downstream dependent table
       From dependencies_database and dependencies_table, you can identify the materialized views that depend on the Kafka/RabbitMQ tables.
       */
       SELECT * FROM system.tables WHERE engine IN ('RabbitMQ', 'Kafka');

    2. View the materialized view definitions and check whether their target tables are implicit tables.

       /*
       View the materialized view definition.
       If the target table of a materialized view is an implicit table, note the following:
       Dropping the materialized view also drops the implicit table, which causes data loss.
       Example: In CREATE MATERIALIZED VIEW [db.]table_name [TO[db.]name], if TO is not specified,
       the system automatically creates an implicit table, which may be in the format '.inner_id.<TABLE_UUID>' or '.inner.<TABLE>'.
       */
       SELECT * FROM system.tables WHERE database='<DATABASE>' AND name = '<MATERIALIZED_VIEW_NAME>';

    3. If a materialized view's target table is an implicit table, rename it to a new name to prevent data loss when the materialized view is dropped later.

       -- Rename the implicit target table to preserve data
       RENAME TABLE <DATABASE>.`.inner_id.<TABLE_UUID>` TO <DATABASE>.<new_target_table_name>;

    4. Drop the Kafka/RabbitMQ engine tables and their downstream materialized views.

       -- Drop the materialized views first
       DROP TABLE <DATABASE>.<MATERIALIZED_VIEW_NAME>;
       -- Then drop the Kafka/RabbitMQ engine tables
       DROP TABLE <DATABASE>.<KAFKA_OR_RABBITMQ_TABLE_NAME>;

    Important

    Make sure to save all recorded DDL statements. You will need them to recreate these tables on both the self-managed cluster and the target cluster later. If you performed the RENAME operation, use the TO clause to point to the renamed target table when recreating the materialized views.

11. Start Synchronization.

    1. Click Start Sync.

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

       During synchronization, use the Stop, Restart, and Cancel Migration operations to control the migration process. Click to view details about these operations.

       Actions	Description	Impact	Use case
       Stop	Immediately stops data migration and proceeds to migrate the remaining database and table structures.	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.	Manually stopping the migration task after all data has been migrated. Testing with a partial data migration without having to stop writes on the self-managed cluster.
       Restart	If an error occurs during a check or migration step, this operation retries the failed step after you resolve the issue.	None	Resume the task from the point of failure after resolving a migration error.
       Cancel Migration	Forcibly cancels the task and skips all subsequent steps. Important After cancellation, the migration task is locked, preventing you from modifying the migration process. You can use the Previous, Next, or Refresh buttons to view the results of completed steps.	The migration task is forcibly terminated. The database and table structures and configurations of the target instance may be incomplete and cannot be used for production workloads. Before you restart the migration, you must clear the migrated data from the target cluster to avoid data duplication.	The migration task is negatively affecting the self-managed cluster, and you need to end it quickly to resume writes.

    3. On the self-managed cluster, use the previously saved DDL statements to recreate the Kafka/RabbitMQ engine tables and their downstream materialized views. After recreation, incremental data resumes flowing in and is automatically synchronized to the target cluster.

       Important

       If you performed the RENAME operation on the implicit target table earlier, use the TO clause to point to the renamed target table when recreating the materialized views. For more information, see CREATE MATERIALIZED VIEW.

       CREATE MATERIALIZED VIEW [db.]table_name [TO[db.]name]

       .

       -- Recreate the Kafka/RabbitMQ engine table on the self-managed cluster
       CREATE TABLE <DATABASE>.<KAFKA_OR_RABBITMQ_TABLE_NAME> (...)
       ENGINE = Kafka/RabbitMQ
       SETTINGS ...;
       
       -- Recreate the materialized view (pointing to the renamed target table)
       CREATE MATERIALIZED VIEW <DATABASE>.<MATERIALIZED_VIEW_NAME> TO <DATABASE>.<new_target_table_name>
       AS SELECT ... FROM <DATABASE>.<KAFKA_OR_RABBITMQ_TABLE_NAME>;

    4. When the process reaches the Migrate data stage, switch to the Migrate data tab and click the icon to view the Migration Progress and Estimated remaining time.

       Assess whether the migration can be completed

       The migration's success depends on the migration speed relative to the write speed of the self-managed cluster.

       • The following table provides migration speed test data:

         Average part size	Source instance type	Source disk type	Target instance type	Target storage medium	Cluster nodes	Single-node speed	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 the write speeds of the target cluster and the self-managed cluster:

         Data migration speed depends on factors such as part size (in our tests, we observed fast migration speeds for an average part size between 100 MB and 10 GB), instance type, disk type, and workload characteristics. Therefore, the test data is for reference only. To determine the actual write speed of the target cluster, check its disk throughput. For more information about how to view disk throughput, see View cluster monitoring information.

         • If the write speed of the target cluster is slower than that of the self-managed cluster: The migration is likely to fail. We recommend canceling the task and performing a manual migration.

         • If the write speed of the target cluster is faster than that of the self-managed cluster: To improve the success rate, we recommend ensuring that the migration time, calculated as Data Volume / (Migration Speed - Self-managed Cluster Write Speed), is no more than 5 days.

       Important

       • You must closely monitor the Migration Progress. Based on the Estimated remaining time, you need to proactively stop writes to the self-managed cluster and handle tables that use the Kafka and RabbitMQ engines.

       • The background process automatically cancels any task that runs longer than 5 days. If your migration task requires more time, submit a ticket to request a threshold adjustment.

       Estimate when to stop writes to the self-managed cluster and perform cutover

       When the estimated migration time is less than 10 minutes or the migration progress reaches 99%, perform the following operations to complete the cutover:

       1. Stop business writes. On the self-managed cluster, drop the previously recreated Kafka/RabbitMQ engine tables and their downstream materialized views.

          -- Drop the materialized views first
          DROP TABLE <DATABASE>.<MATERIALIZED_VIEW_NAME>;
          -- Then drop the Kafka/RabbitMQ engine tables
          DROP TABLE <DATABASE>.<KAFKA_OR_RABBITMQ_TABLE_NAME>;

       2. Wait for the migration progress to reach 100% and the migration to fully complete.

       3. Connect to the target cluster and use the previously saved DDL statements to recreate the Kafka/RabbitMQ engine tables and their downstream materialized views.

          Important

          If you performed the RENAME operation on the implicit target table earlier, use the TO clause to point to the renamed target table when recreating the materialized views. For more information, see CREATE MATERIALIZED VIEW.

          -- Recreate the Kafka/RabbitMQ engine table on the target cluster
          CREATE TABLE <DATABASE>.<KAFKA_OR_RABBITMQ_TABLE_NAME> (...)
          ENGINE = Kafka/RabbitMQ
          SETTINGS ...;
          
          -- Recreate the materialized view (pointing to the renamed target table)
          CREATE MATERIALIZED VIEW <DATABASE>.<MATERIALIZED_VIEW_NAME> TO <DATABASE>.<new_target_table_name>
          AS SELECT ... FROM <DATABASE>.<KAFKA_OR_RABBITMQ_TABLE_NAME>;

       4. Verify that the data pipeline on the target cluster is working properly and that data is flowing in as expected.

    5. When the Migration Progress reaches 100% and you confirm that writes to the source instance have stopped, click Stop to end the data migration and proceed to the next steps.

       

    6. After the synchronization is complete, click Completed.

       Important

       After the Start Synchronization step is complete, the migration task is locked, and you cannot modify the migration process. You can still use the Previous, Next, or Refresh buttons to view the results of completed steps.

Step 4: Migrate data for non-MergeTree tables

During a migration task, non-MergeTree tables either support only table structure migration (for example, MySQL tables) or do not support migration at all (for example, Log tables). Therefore, after the migration task is complete, the target cluster may contain tables with a structure but no business data. You must manually migrate the business data as follows:

1. Log on to the self-built cluster and identify the 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 target cluster and use the remote function to migrate the data.