ApsaraDB for ClickHouse:Migrate data from a self-managed ClickHouse cluster to an ApsaraDB for ClickHouse cluster that runs Community-compatible Edition

Step 1: Enable the system tables for the self-managed cluster

Before migration, you must modify the config.xml file of the self-managed cluster to enable system tables system.part_log and system.query_log to allow for incremental migration. The following code provides an example on how to modify the file:

<part_log>
    <database>system</database>
    <table>part_log</table>
    <partition_by>event_date</partition_by>
    <ttl>event_date + INTERVAL 15 DAY DELETE</ttl>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</part_log>

<query_log>
    <database>system</database>
    <table>part_log</table>
    <partition_by>event_date</partition_by>
    <ttl>event_date + INTERVAL 15 DAY DELETE</ttl>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>

Step 2: Configure the destination cluster to be compatible with the self-managed cluster version

(Optional) Step 3: Enable the MaterializedMySQL engine for the destination cluster

If the self-managed cluster contains tables that use the MaterializedMySQL engine, execute the following statement to enable the MaterializedMySQL engine for the destination cluster:

SET GLOBAL allow_experimental_database_materialized_mysql = 1;

Step 4: Create a migration task

1. Log on to the ApsaraDB for ClickHouse console.

2. On the Clusters page, click the Clusters of Community-compatible Edition tab and click the ID of the cluster that you want to manage.

3. In the left-side navigation pane, choose Data Migration and Synchronization > Migration from Self-managed ClickHouse or ApsaraDB for ClickHouse.

4. In the upper-left corner of the page that appears, click Create Migration Task.

   1. Configure the source and destination clusters.

      Configure the required parameters in the Source Instance Information and the Destination Instance Information sections and click Test Connectivity and Proceed.

      Note

      • In this example, set the Source Access Method parameter to Express Connect, VPN Gateway, Smart Access Gateway, or Self-managed ClickHouse on an ECS Instance and configure other parameters as prompted.

      • After the connection test succeeds, proceed to the Migration Content step. If the connection test fails, configure the source and destination clusters again as prompted.

      

   2. Confirm the migration content.

      On the page that appears, read the information about the data migration content and click Next: Pre-detect and Start Synchronization.

   3. The system performs prechecks on the migration configuration and then starts the migration task in the backend after the prechecks pass.

      The system performs the following prechecks on the source and destination clusters: Instance Status Detection, Storage Space Detection, and Local Table and Distributed Table Detection.

      • If the prechecks are successful, perform the following operations:

        If the prechecks are successful, the information shown in the following figure is displayed.

        

        1. Read the information about the impacts of data migration on clusters.

        2. Click Completed.

           Important

           • After you complete the configurations, the task is created and enters the Running state. You can view the task in the task list.

           • After the migration task is created, you must monitor the migration task. When the migration is nearing completion, you must stop writing data to the self-managed cluster and migrate the remaining database and table schemas. For more information, see Monitor a migration task and stop writing data to a self-managed cluster.

      • If the prechecks fail, you need to follow the on-screen instructions to resolve the issue and then configure the migration task parameters again. The following table describes the precheck items and requirements. For more information about errors and solutions, see Query and troubleshot the errors that are reported in the migration check.

        Item	Requirement
        Instance Status Detection	Before you migrate data, make sure that no management operations, such as scale-out, upgrade, or downgrade operations, are being performed on the self-managed and destination clusters. If management operations are being performed on the self-managed and destination clusters, the system cannot start a migration task.
        Storage Space Detection	Before a migration task is started, the system checks the storage space of the self-managed and destination clusters. Make sure that the storage capacity of the destination cluster is at least 1.2 times the storage capacity of the self-managed cluster.
        Local Table and Distributed Table Detection	If no distributed table is created for a local table or multiple distributed tables are created for the same local table of the self-managed cluster, the prechecks fail. You must delete redundant distributed tables or create a unique distributed table.

Use the remote function to migrate data

1. (Optional) If you need to consider network traffic consumption when you migrate data to an ApsaraDB for ClickHouse cluster, you can configure the network_compression_method parameter to specify an appropriate compression algorithm to reduce traffic consumption.

   • View or temporarily change the value of the network_compression_method parameter in the destination ApsaraDB for ClickHouse cluster. Sample statement:

   SET network_compression_method = 'ZSTD';

   • View the value of the network_compression_method parameter in the destination ApsaraDB for ClickHouse cluster. Sample statement:

   SELECT * FROM system.settings WHERE name = 'network_compression_method';

2. Execute the following SQL statement in the destination ApsaraDB for ClickHouse cluster to migrate data:

   INSERT INTO <new_database>.<new_table> 
   SELECT * 
   FROM remote('<old_endpoint>', <old_database>.<old_table>, '<username>', '<password>') 
   [WHERE _partition_id = '<partition_id>']
   SETTINGS max_execution_time = 0, max_bytes_to_read = 0, log_query_threads = 0;

   Note

   For ApsaraDB for ClickHouse V20.8, we recommend that you use the remoteRaw function to migrate data. If data fails to be migrated, you can apply for a minor version update.

   INSERT INTO <new_database>.<new_table> 
   SELECT * 
   FROM remoteRaw('<old_endpoint>', <old_database>.<old_table>, '<username>', '<password>')
   [WHERE _partition_id = '<partition_id>']
   SETTINGS max_execution_time = 0, max_bytes_to_read = 0, log_query_threads = 0;

   Parameters

   Important

   The partition_id parameter can be used to filter data to reduce resource usage. We recommend that you use this parameter.

   (Optional) You can execute the following SQL statement to query the value of the partition_id parameter and the number of parts in system.parts:

   SELECT partition_id, count(*) AS part_count from clusterAllReplicas(default, system, parts) WHERE `database` = '<old_database>' AND `table` = '<old_table>' GROUP BY partition_id ;

   Parameter	Description
   new_database	The name of the database in the destination cluster.
   new_table	The name of the table in the destination cluster.
   old_endpoint	The endpoint of the self-managed cluster. Self-managed cluster The endpoint of a self-managed cluster is in the IP address of the node of the self-managed cluster:port format. Important In this case, the port is a TCP port. ApsaraDB for ClickHouse cluster The endpoint of an ApsaraDB for ClickHouse cluster is a VPC endpoint, instead of a public endpoint. Important The port 3306 is the default port of ApsaraDB for ClickHouse Community-compatible Edition clusters and the port 9000 is the default port of ApsaraDB for ClickHouse Enterprise Edition clusters. Community-compatible Edition cluster: The endpoint is in the VPC endpoint:3306 format. Example: cc-2zeqhh5v7y6q*****.clickhouse.ads.aliyuncs.com:3306. Enterprise Edition cluster: The endpoint is in the VPC endpoint:9000 format. Example: cc-bp1anv7jo84ta*****clickhouse.clickhouseserver.rds.aliyuncs.com:9000.
   old_database	The name of the database in the self-managed cluster.
   old_table	The name of the table in the self-managed cluster.
   username	The username of the account of the self-managed cluster.
   password	The password of the account of the self-managed cluster.
   max_execution_time	The maximum query execution time. The value 0 indicates that no limits are imposed.
   max_bytes_to_read	The maximum number of bytes that can be read from source data during the query. The value 0 indicates that no limits are imposed.
   log_query_threads	Specifies whether to record information about query threads. The value 0 indicates that information about query threads is not recorded.
   _partition_id	The ID of the data partition.

Export and import a file to migrate data

You can export a file from the self-managed cluster and import the file to the destination cluster to migrate data.

• Export and import a CSV file

  1. Export data to a CSV file from the self-managed cluster.

     clickhouse-client --host="<old host>" --port="<old port>" --user="<old user name>" --password="<old password>"  --query="select * from <database_name>.<table_name> FORMAT CSV"  > table.csv

  2. Import the CSV file to the destination cluster.

     clickhouse-client --host="<new host>" --port="<new port>" --user="<new user name>" --password="<new password>"  --query="insert into <database_name>.<table_name> FORMAT CSV"  < table.csv

• Use a pipe in Linux to export and import streaming data

  clickhouse-client --host="<old host>" --port="<old port>" --user="<user name>" --password="<password>"  --query="select * from <database_name>.<table_name> FORMAT CSV" | 
  clickhouse-client --host="<new host>" --port="<new port>" --user="<user name>" --password="<password>"   --query="INSERT INTO <database_name>.<table_name> FORMAT CSV"