ApsaraDB for ClickHouse:Migrate data from a self-managed ClickHouse cluster to an ApsaraDB for ClickHouse cluster

Overview

The data migration policy involves two main steps: First, migrate metadata to ensure that all table schemas are correctly migrated to the ApsaraDB for ClickHouse cluster. Then, use the remote function of ApsaraDB for ClickHouse to directly migrate data, or export the data and import it to the ApsaraDB for ClickHouse cluster to complete the migration.

Step 1: Migrate metadata

To migrate metadata from the self-managed ClickHouse cluster, import the DDL statements that are executed to create tables in the self-managed ClickHouse cluster to the destination ApsaraDB for ClickHouse cluster.

If you need to install clickhouse-client, make sure that the version of clickhouse-client is consistent with the version of the destination ApsaraDB for ClickHouse cluster. For more information about the download link for clickhouse-client, see clickhouse-client.

1. Query the databases in the self-managed ClickHouse cluster.

   clickhouse-client --host="<old host>" --port="<old port>" --user="<old user name>" --password="<old password>" --query="SHOW databases"  > database.list

   The following table describes the parameters in the preceding syntax.

   Parameter	Description
   old host	The endpoint of the self-managed ClickHouse cluster.
   old port	The port that is used by the self-managed ClickHouse cluster.
   old user name	The username that is used to log on to the self-managed ClickHouse cluster. The user has DML permissions, including read, write, and set permissions. The user can also be granted DDL permissions.
   old password	The password of the user that is used to connect to the self-managed ClickHouse cluster.

   Note

   system stands for the system database. You do not need to migrate this database.

2. Query the tables in the self-managed ClickHouse cluster.

   clickhouse-client --host="<old host>" --port="<old port>" --user="<old user name>" --password="<old password>" --query="SHOW tables from <database_name>"  > table.list

   The following table describes a parameter in the preceding syntax.

   Parameter	Description
   database_name	The database name.

   You can also use system tables to query the names of all databases and tables in the cluster.

   SELECT DISTINCT database, name FROM system.tables WHERE database != 'system';

   Note

   Table names that start with .inner. are the internal representations of materialized views. You can skip these tables during migration.

3. Export the DDL statements that are executed to create tables in the specified database of the self-managed ClickHouse cluster.

   clickhouse-client --host="<old host>" --port="<old port>" --user="<old user name>" --password="<old password>" --query="SELECT concat(create_table_query, ';') FROM system.tables WHERE database='<database_name>' FORMAT TabSeparatedRaw" > tables.sql

4. Import the DDL statements that are executed to create tables in the self-managed ClickHouse cluster to the destination ApsaraDB for ClickHouse cluster.

   Note

   Before you import the DDL statements that are executed to create tables, you must create a database to which the tables will be imported in the ApsaraDB for ClickHouse cluster.

   clickhouse-client --host="<new host>" --port="<new port>" --user="<new user name>" --password="<new password>"  -d '<database_name>'  --multiquery < tables.sql

   The following table describes the parameters in the preceding syntax.

   Parameter	Description
   new host	The endpoint of the destination ApsaraDB for ClickHouse cluster.
   new port	The port that is used by the destination ApsaraDB for ClickHouse cluster.
   new user name	The username that is used to log on to the destination ApsaraDB for ClickHouse cluster. The user has DML permissions, including read, write, and set permissions. The user can also be granted DDL permissions.
   new password	The password of the user that is used to log on to the destination ApsaraDB for ClickHouse cluster.

Step 2: 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;

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;

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

FAQ

• Q: What do I do if the error message "Too many partitions for single INSERT block (more than 100)" appears?

  A: The number of partitions in a single inserted block specified in a single INSERT operation exceeds the value of max_partitions_per_insert_block. The default value of max_partitions_per_insert_block is 100. ApsaraDB for ClickHouse generates a data part for each write operation. A partition may contain one or more data parts. If a single INSERT operation inserts an excessively large number of partitions, a large number of data parts are generated in ApsaraDB for ClickHouse, which can impose a heavy burden on merge and query operations. To prevent a large number of data parts from being generated, ApsaraDB for ClickHouse imposes certain restrictions.

  Solution: Perform the following operations to adjust the number of partitions or the value of max_partitions_per_insert_block:

  • Adjust the table schema and the partitioning mode or make sure that the number of partitions inserted at a time does not exceed the upper limit.

  • To prevent the number of partitions inserted at a time from exceeding the upper limit, execute the following statement to change the value of max_partitions_per_insert_block based on the data volume to increase the upper limit on the number of partitions that can be inserted at a time:

    SET GLOBAL ON cluster DEFAULT max_partitions_per_insert_block = XXX;

    Note

    In ClickHouse, the recommended default value of max_partitions_per_insert_block is 100. Do not set max_partitions_per_insert_block to an excessively large value. Otherwise, the performance may be affected. You can change the value of max_partitions_per_insert_block to the default value after you import data in batches.

• Q: Why does a destination ApsaraDB for ClickHouse cluster fail to connect to a self-managed ClickHouse cluster?

  A: The self-managed ClickHouse cluster may have been configured with a firewall or a whitelist. You can view the VPC to which the destination ApsaraDB for ClickHouse cluster belongs in the ApsaraDB for ClickHouse console and add the entire CIDR block of the VPC to the whitelist of the self-managed ClickHouse cluster. If you want to control the CIDR block range in the whitelist to avoid potential security risks, execute the following SQL statement to query the IP addresses of the backend servers of the destination ApsaraDB for ClickHouse cluster and add only these IP addresses to the whitelist of the self-managed ClickHouse cluster.

  SELECT * FROM system.clusters;