ApsaraDB for ClickHouse:Migrate data from a self-managed ClickHouse instance to an ApsaraDB for ClickHouse Community-Compatible Edition cluster

Limits

The version of the destination cluster must be 21.8 or later.

Notes

• During migration:

  • The merge process for the databases and tables being migrated in the destination cluster is paused. The merge process in the self-managed cluster is not paused.

    Note

    If the data migration takes too long, an excessive amount of metadata accumulates in the destination cluster. We recommend that a migration task does not run for more than 5 days.

  • The destination cluster must use the default cluster. If your self-managed cluster uses a different name, the cluster definition in the distributed tables is automatically converted to default.

• Migration content:

  • Supported content for migration:

    • Databases, data dictionaries, and materialized views.

    • Table schema: All table schemas except for Kafka and RabbitMQ engine tables.

    • Data: Incremental migration of data from MergeTree family tables.

  • Unsupported content for migration:

    • Tables and data of Kafka and RabbitMQ engine tables.

    • Data from non-MergeTree tables, such as external tables and Log tables.

    Important

    During the migration process, you must manually handle the unsupported content by following the steps provided.

  • Data volume for migration:

    • Cold data: The migration of cold data is relatively slow. We recommend that you clear as much cold data as possible from your self-managed cluster to ensure the total volume does not exceed 1 TB. Otherwise, the long migration time may cause the task to fail.

    • Hot data: If the volume of hot data exceeds 10 TB, the migration task has a high probability of failure. We do not recommend using this solution for migration in this case.

    If your data volume exceeds these limits, use the Manual migration solution instead.

Impact on clusters

• Self-managed cluster:

  • Reading data from the self-managed cluster increases its CPU and memory usage.

  • DDL operations are not allowed.

• Destination cluster:

  • Writing data to the destination cluster increases its CPU and memory usage.

  • DDL operations are not allowed.

  • DDL operations are not allowed on the databases and tables being migrated. This restriction does not apply to other databases and tables.

  • The merge process is stopped for databases and tables that are being migrated. The merge process is not stopped for other databases and tables.

  • The cluster restarts before the migration task begins and restarts again after the task ends.

  • After the migration is complete, the cluster undergoes a period of high-frequency merge operations. This increases I/O usage and the latency of business requests. We recommend that you plan for the potential impact of increased business request latency. You must calculate the specific duration of the merge operations. For more information about this calculation, see Calculate the merge time after migration.

Procedure

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

SET GLOBAL allow_experimental_database_materialized_mysql = 1;

Step 5: Assess whether the migration can be completed

If the write speed of the source cluster is less than 20 MB/s, you can skip this step.

If the write speed of the source cluster is greater than 20 MB/s, the theoretical write speed of a single node in the destination cluster must also be greater than 20 MB/s. To ensure that the write speed of the destination cluster can keep up with the source cluster's write speed and that the migration can succeed, you must check the actual write speed of the destination cluster to assess the feasibility of the migration. Follow these steps:

1. You can view the Disk throughput of the destination cluster to determine its actual write speed. For more information about how to view Disk throughput, see View cluster monitoring information.

2. Determine the relationship between the write speeds of the destination and source clusters.

   1. If the write speed of the destination cluster is greater than the write speed of the source cluster: The migration has a high probability of success. Continue to Step 6.

   2. If the write speed of the destination cluster is less than the write speed of the source cluster: The migration has a high probability of failure. We recommend that you cancel the migration task and use manual migration to migrate the data.

Step 6: Monitor the migration task and estimate when to stop writes to the self-managed cluster

1. Log on to the ApsaraDB for ClickHouse console.

2. On the Community Edition Instances list, click the ID of the destination cluster.

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

4. On the instance migration list page, you can perform the following operations:

   • View the status and running phase information of the migration task.

     Important

     Closely monitor the Running Information of the destination task. Based on the estimated remaining time in the Running Information column, follow Step 7 to stop writes to the self-managed cluster and handle Kafka and RabbitMQ engine tables.

   • Click View Details in the Actions column to open the task details page. The page includes the following details:

     Note

     If the migration task ends (its status is Completed or Canceled), the content on the View Details page in the console is cleared. You can view the list of migrated table schemas in the destination cluster by running the following SQL statement.

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

     • All migrated table schemas and their migration success status.

     • All migrated database structures and their migration success status.

     • All error messages for failed database and table migrations.

   The following table describes the migration task statuses and their corresponding functions.

   Task Status	Function Description
   Running	Preparing the environment and resources for migration.
   Initialization	Initializing the migration task.
   Configuration Migration	Migrating the cluster configuration.
   Schema Migration	Migrating all databases, along with MergeTree family tables and Distributed tables.
   Data Migration	Incrementally migrating data from MergeTree family tables.
   Other Schema Migration	Migrating materialized views and non-MergeTree table schemas.
   Data Check	Checks if the data volume of the completed tables in the destination cluster is consistent with the data volume in the self-managed cluster. If not, the task may not complete. We recommend restarting the migration.
   Post-configuration	System configuration of the destination cluster after migration is complete, such as cleaning up the migration site and enabling writes to the source instance.
   Completed	The migration task is complete.
   Canceled	The migration task has been canceled.

Step 7: Stop writes to the self-managed cluster and handle Kafka and RabbitMQ engine tables

Before you switch your business services to the new cluster, you must ensure that no new data is written to the self-managed instance to guarantee data integrity after the migration. To do this, you must stop business write operations and delete the Kafka and RabbitMQ tables. Follow these steps:

1. Log on to the self-managed cluster and run the following statement to query the tables that you need to process.

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

2. You can view the table creation statement for the target table.

   SHOW CREATE TABLE <target_table_name>;

3. Log on to the destination cluster and execute the table creation statement that you obtained in the previous step. For more information about how to log on to the destination cluster, see Connect to a ClickHouse cluster using DMS.

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 the materialized views that reference it. Otherwise, the materialized view migration cannot be completed, which causes the entire migration task to fail.

Step 8: Complete the migration task

The Complete operation finalizes the migration after you stop writes to the self-managed cluster. This operation completes the remaining data migration, performs a data volume check, and migrates the remaining database and table schemas. You can view the migrated data in the task details.

1. Log on to the ApsaraDB for ClickHouse console.

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

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

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

5. In the Complete Migration dialog box, click OK.

Step 9: Migrate business data from non-MergeTree tables

The migration task supports migrating only the table schemas of non-MergeTree tables, such as external tables and Log tables. After the migration task is complete, these tables in the destination cluster contain only a table schema and no business data. You must migrate the business data manually. Follow these steps:

1. Log on to the self-managed cluster and run the following statement to view the non-MergeTree tables whose data you need to migrate.

   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 migrate the table data using the remote function. For more information, see Migrate data using the remote function.

Other operations

When a migration task completes, its Migration Status changes to Completed. However, the task list does not update immediately. We recommend that you periodically refresh the page to check the task status.

Method 1: Migrate using the BACKUP and RESTORE commands

For more information, see Use the BACKUP and RESTORE commands to back up and restore data.

Method 2: Migrate using the INSERT FROM SELECT statement