Migrate data between ClickHouse community-compatible clusters

• The source and destination clusters must meet the following requirements:

  • Both are community-compatible edition clusters.

    Note

    If you need to migrate from a community-compatible edition cluster to an enterprise edition cluster, or vice versa, see Migrate a ClickHouse community-compatible edition cluster to an enterprise edition cluster.

  • Both clusters are in the Running state.

  • You have a database account and password for each cluster.

  • Both clusters have the same tiered storage policy for hot and cold data.

  • Both clusters use the same VPC, are in the same region, and their IP addresses are in each other's whitelists. If this condition is not met, you must resolve the network connectivity issue first. For more information, see How to resolve network connectivity issues between a destination cluster and a data source.

    Note

    You can view the IP address of an Alibaba Cloud ClickHouse instance by running the SELECT * FROM system.clusters; command. For information about how to configure a whitelist, see Configure a whitelist.

• The destination cluster must also meet the following requirements:

  • The version is later than or equal to the source cluster version. For information about the latest version, see Community-compatible edition.

  • The available disk space (excluding cold storage) is at least 1.2 times the used disk space (excluding cold storage) of the source cluster.

• Each local table in the source cluster must have a unique distributed table.

Step 1: Record and clean up Kafka/RabbitMQ engine tables

Before starting the migration, record the definitions of all Kafka/RabbitMQ engine tables and their downstream materialized views in the source cluster, handle implicit tables, and then delete these tables to avoid migration errors.

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

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

2. View the materialized view definition to check whether its target table is an implicit table.

   /*
   View the materialized view definition.
   If the target table of the materialized view is an implicit table, pay special attention:
   Dropping the materialized view will also drop the implicit table, causing data loss.
   Example: If CREATE MATERIALIZED VIEW [db.]table_name [TO[db.]name] does not specify TO,
   the system automatically creates an implicit table, possibly in the format '.inner_id.<TABLE_UUID>' or '.inner.<TABLE>'
   */
   SELECT * FROM system.tables WHERE database='<DATABASE>' AND name = '<MATERIALIZED_VIEW_NAME>';

3. If the target table of a materialized view is an implicit table, RENAME the target table to a new name to prevent data loss when the materialized view is dropped later.

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

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

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

Step 2: Create a migration task

1. Log on to the Alibaba Cloud ClickHouse console.

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

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

4. Click Create Migration Task.

   1. Configure the source and destination instances.

      Configure the required information and click Test Connectivity and Proceed.

      Note

      If the connection test is successful, proceed to the next step. If the test fails, reconfigure the source and destination instances as prompted.

      

   2. Confirm the migration content.

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

   3. The system runs a pre-check and starts the task.

      The system performs Instance Status Detection, Storage Space Detection, and Local Table and Distributed Table Detection on the source and destination instances.

      • If the pre-check is successful:

        1. Review the impact on the instances during migration.

        2. Set the Time of Stopping Data Writing.

           Note

           • Writes to the source cluster must be stopped for the last 10 minutes of the migration to ensure data consistency.

           • To ensure a high success rate, we recommend that you set the write-stop duration to at least 30 minutes.

           • A migration task must be completed within five days of its creation. Therefore, the end date for the Time of Stopping Data Writing must be no later than current date + 5 days.

           • To minimize the impact on your business, we recommend that you set the write-stop window to your off-peak hours.

        3. Click Completed.

           Note

           After clicking this button, the task is created and started.

      • If the pre-check fails, follow the prompts to resolve the issues and retry the data migration. The following table describes the pre-check items and their requirements.

        Check item	Requirement
        Instance Status Detection	Ensure that no management tasks, such as scaling or configuration changes, are running on the source or destination cluster. You cannot start the migration if a task is in progress.
        Storage Space Detection	The available storage space of the destination cluster must be at least 1.2 times the used storage space of the source cluster.
        Local Table and Distributed Table Detection	The check fails if a local table in the source cluster does not have a corresponding distributed table or if the distributed table is not unique. You must delete any extra distributed tables or create a unique distributed table for each local table.

Step 3: Assess migration completion

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, you must check the actual write speed of the destination cluster to assess if the migration can complete. The destination cluster's write speed must keep up with the source cluster's. To do this, follow these steps:

1. Check the disk throughput of the destination cluster to determine its actual write speed. For more information, see View cluster monitoring information.

2. Compare the write speeds of the destination and source clusters.

   1. If the write speed of the destination cluster is greater than that of the source cluster, the migration is highly likely to succeed. Proceed to Step 4.

   2. If the write speed of the destination cluster is less than that of the source cluster, the migration is highly likely to fail. We recommend that you cancel the migration task and perform a manual migration.

Step 4: View the migration task

1. On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the destination cluster.

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

   On the instance migration list page, view the Migration Status, Running Information, and Data Write-Stop Window for the task.

   Note

   When the estimated remaining time in the Running Information column is 10 minutes or less and the Migration Status is Migrating, a write-stop is triggered on the source cluster to ensure data consistency. The rules are as follows:

   • If the trigger time is within the configured write-stop window, writes to the source cluster are stopped.

   • If the trigger time is outside the configured write-stop window but is on or before the task start (that is, task creation) date + 5 days, you can modify the write-stop window to continue the migration.

   • If the trigger time is outside the configured write-stop window and is after the task start (that is, task creation) date + 5 days, the migration fails. You must cancel the migration task, clear the migrated data from the destination cluster, and create a new migration task.

   Important

   • If the source cluster contains Kafka/RabbitMQ engine tables: when the migration task enters the data migration phase (that is, table schema migration is complete), perform Step 5 to rebuild Kafka/RabbitMQ engine tables on the source cluster so that incremental data resumes flowing and is synchronized to the destination cluster.

   • Shortly before the configured write-stop window, perform Step 6 to delete the Kafka/RabbitMQ engine tables and their downstream materialized views on the source cluster to prevent message backlog during the write-stop period from causing data inconsistency.

Step 5: Rebuild Kafka/RabbitMQ engine tables on the source cluster

After the migration task enters the data migration phase (that is, table schema migration is complete), use the previously saved DDL statements to rebuild the Kafka/RabbitMQ engine tables and their downstream materialized views on the source cluster. Once rebuilt, incremental data resumes flowing and is automatically synchronized to the destination cluster.

-- Rebuild Kafka/RabbitMQ engine tables on the source cluster
CREATE TABLE <database>.<kafka_or_rabbitmq_table_name> (...)
ENGINE = Kafka/RabbitMQ
SETTINGS ...;

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

Step 6: Delete Kafka/RabbitMQ engine tables on the source cluster

Shortly before the configured write-stop window, delete the Kafka/RabbitMQ engine tables and their downstream materialized views on the source cluster to stop incremental data writes and ensure consistency of the final data synchronization.

-- Delete materialized views first
DROP TABLE <database>.<materialized_view_name> ON CLUSTER default;

-- Then delete Kafka/RabbitMQ engine tables
DROP TABLE <database>.<kafka_or_rabbitmq_table_name> ON CLUSTER default;

Step 7: Rebuild Kafka/RabbitMQ engine tables on the destination cluster

After the migration task is complete, use the previously saved DDL statements to rebuild the Kafka/RabbitMQ engine tables and their downstream materialized views on the destination cluster to restore the incremental data consumption pipeline.

-- Rebuild Kafka/RabbitMQ engine tables on the destination cluster
CREATE TABLE <database>.<kafka_or_rabbitmq_table_name> (...)
ENGINE = Kafka/RabbitMQ
SETTINGS ...;

-- Rebuild materialized view
CREATE MATERIALIZED VIEW <database>.<materialized_view_name> TO <database>.<target_table_name>
AS SELECT ... FROM <database>.<kafka_or_rabbitmq_table_name>;

Step 8: (Optional) Cancel the migration task

1. On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the destination cluster.

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

3. In the Actions column for the migration task, click Cancel Migration.

4. In the Cancel Migration dialog box, click OK.

   Note

   • After canceling the migration, the task list is not updated immediately. Refresh the page periodically to check the task status.

   • After the task is canceled, its Migration Status changes to Completed.

   • Before you start a new migration, you must clear the migrated data from the destination cluster to prevent data duplication.

Step 9: (Optional) Modify the write-stop window

1. On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the destination cluster.

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

3. In the Actions column for the migration task, click Modify Data Write-Stop Time Window.

4. In the Modify Data Write-Stop Time Window dialog box, select a new Time of Stopping Data Writing.

   Note

   The rules for setting the Time of Stopping Data Writing are the same as those for creating a migration task.

5. Click OK.