ApsaraDB for ClickHouse:Migrate from self-managed to Cloud ClickHouse

During migration

• During migration, the merge process pauses in the destination cluster but continues in the self-managed cluster.

  Note

  If a migration task runs for a long time, excessive metadata can accumulate in the destination cluster. We recommend that migration tasks do not exceed 5 days in duration. Tasks that exceed 5 days are automatically canceled.

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

Migration scope

• Supported objects

  • Databases, data dictionaries, and materialized views.

    • This service supports migrating data dictionaries created with SQL, but not those created with XML.

      To verify this, run the following statement: SELECT * FROM system.dictionaries WHERE (database = '') OR isNull(database);. If the statement returns any rows, this indicates you have data dictionaries created using XML.

    • When a data dictionary accesses an external service, ensure that the external service is available and that the cluster is added to its allowlist. If the data source for a data dictionary is an internal table in the current ClickHouse cluster and the HOST parameter in the definition is an IP address, access to the dictionary may fail after a migration due to an IP address change. You must reconfirm the HOST of the current ClickHouse cluster and manually create the data dictionary.

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

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

• Unsupported objects and data

  • Kafka and RabbitMQ engine tables, and their data.

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

  Important

  You must manually migrate the unsupported items listed above.

• Data volume limits

  • Cold data: Migrating cold data is slow. To prevent failures caused by long migration times, we recommend clearing cold data from your self-managed cluster to ensure the total volume does not exceed 1 TB.

  • Hot data: If the volume of hot data exceeds 10 TB, the migration task is likely to fail.

  If your data volume exceeds these limits, consider using the manual migration solution instead.

Step 1: Check the cluster and enable system tables

Before migrating data, modify the config.xml file in your self-managed cluster to enable incremental migration. The required modifications depend on whether the system.part_log and system.query_log tables are 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 destination cluster compatibility

Step 3: (Optional) Enable the MaterializedMySQL engine

If your self-managed cluster contains tables that use the MaterializedMySQL engine, run the following statement to enable this engine.

SET GLOBAL allow_experimental_database_materialized_mysql = 1;

Step 4: 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 self-managed cluster, handle implicit tables, and then delete these tables to avoid migration errors.

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 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 5: Create a data migration task

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 destination cluster.

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

4. On the migration task page, click Create Migration Task.

   1. Configure the source and destination instances.

      Configure the following settings and click Test Connectivity and Proceed.

      Note

      If the connection test is successful, proceed to the next step. If the connection test fails, reconfigure the source and destination instances based on the prompts.

      

      Source cluster parameters

      Parameter	Description	Example
      Source Access Method	Select Express Connect, VPN Gateway, Smart Access Gateway, or Self-managed ClickHouse Clusters on an ECS Instance.	Express Connect, VPN Gateway, Smart Access Gateway, or Self-managed ClickHouse Clusters on an ECS Instance
      Cluster Name	The name of the source cluster. The name can contain only digits and lowercase letters.	source
      Source Cluster Name	Run SELECT * FROM system.clusters; to obtain the Source Cluster Name.	default
      VPC IP Address	The IP address and PORT (TCP address) of each shard in the cluster, separated by commas. Important You cannot use the VPC domain name or SLB address of an ApsaraDB for ClickHouse cluster. Format: IP:PORT,IP:PORT,...... The method to obtain the cluster IP address and PORT varies depending on the data migration scenario. Cross-account or cross-region migration You can use the following SQL statement to obtain 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 = 'default' and replica_num = 1; Here, replica_num=1 selects the first replica set. You can also choose other replica sets or select one replica from each shard. Non-Alibaba Cloud ClickHouse migration If the IP address cannot be easily mapped to Alibaba Cloud, you can use the following SQL statement to obtain 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; The parameters are described as follows: cluster_name: the name of the destination cluster. replica_num=1 selects the first replica set. You can also choose other replica sets or select one replica from each shard. If the IP address and port are mapped to Alibaba Cloud through network translation, you must configure the corresponding mapped IP address and port.	192.168.0.5:9000,192.168.0.6:9000
      Database Account	The database account of the source cluster.	test
      Database Password	The password for the database account of the source cluster.	test******

      Destination cluster parameters

      Parameter	Description	Example
      Database Account	The database account of the destination cluster.	test
      Database Password	The password for the database account of the destination cluster.	test******

   2. Confirm the migration content.

      Carefully review the information about the data to be migrated on the page, and then click Next: Pre-detect and Start Synchronization.

   3. The system runs a precheck on the migration link in the background and starts the task.

      The system runs an Instance Status Detection, a Storage Space Detection, and a Local Table and Distributed Table Detection on the source and destination clusters.

      • If the precheck is successful:

        The following figure shows the page that appears after a successful precheck.

        

        1. Carefully review how the data migration process affects the instances.

        2. Click Completed.

           Important

           • After you click Complete, the system creates and starts the task, and its status changes to Running. You can view the task in the task list.

           • After creating the task, you must monitor it. In the final stage of the migration, you must stop write operations to the self-managed cluster and migrate the remaining database and table schemas. For more information, see Monitor the migration task and stop writes to the self-managed cluster.

      • If the precheck fails: Follow the instructions in the error message and run the data migration task again. The following table describes the precheck items and their requirements. For more information about precheck error messages and their solutions, see Migration precheck errors and solutions.

        Check item	Requirement
        Instance Status Detection	The data migration task cannot start if management tasks (such as scale-out, upgrade, or downgrade) are running on the source or destination cluster.
        Storage Space Detection	The available storage space of the destination cluster must be at least 1.2 times the used storage space of the self-managed cluster.
        Local Table and Distributed Table Detection	If a local table in the self-managed cluster does not have a corresponding distributed table or has more than one distributed table, the check fails. To resolve this, delete the redundant distributed tables or create a unique distributed table for the local table.

Step 8: Rebuild Kafka/RabbitMQ engine tables on the self-managed cluster

After the migration task enters the data migration phase (i.e., 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 self-managed cluster. Once rebuilt, incremental data resumes flowing and is automatically synchronized to the destination cluster.

-- Rebuild Kafka/RabbitMQ engine tables on the self-managed 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 9: Stop writes and perform cutover

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

1. Stop business writes. On the self-managed cluster, delete the previously rebuilt 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>;

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

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

   Important

   If you performed a RENAME operation on implicit target tables earlier, use the TO clause pointing to the renamed target table when rebuilding the materialized view.

   -- Rebuild Kafka/RabbitMQ engine tables on the destination 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>;

4. Verify that the data pipeline on the destination cluster is working properly and data is flowing in normally.

Step 10: Complete migration

After stopping writes to the self-managed cluster, you can Complete the task. This step migrates the remaining data, performs a data volume check, and migrates the remaining database and table schemas. You can view the migrated content 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 destination cluster.

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

4. For the target migration task, click Complete Migration in the Actions column.

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

Step 11: Migrate data for non-MergeTree tables

The migration task migrates only the table schema of non-MergeTree tables (such as external tables and Log tables), creating them in the destination cluster without any 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 identify the non-MergeTree tables that require 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 the table data. For detailed instructions, see Migrate data using the remote function.

Other operations

When a migration task completes, its Migration Status changes to Completed. The task list does not update immediately, so refresh the page periodically to view the latest status.

Method 1: Use the BACKUP and RESTORE commands

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

Method 2: Use the INSERT FROM SELECT statement