云数据库 ClickHouse：将自建ClickHouse数据迁移到云ClickHouse中

方案概览

数据迁移策略分为两个主要步骤：首先是元数据迁移，确保所有的表结构等正确无误的迁移到云数据库ClickHouse；其次是数据迁移，您可以选择使用ClickHouse的remote函数直接迁移数据，或者将数据导出并在云数据库ClickHouse导入完成迁移。

步骤一：元数据（建表的DDL）的迁移

ClickHouse元数据的迁移，主要指进行建表DDL迁移。

如需安装clickhouse-client工具，请安装与目标实例云数据库ClickHouse版本一致的clickhouse-client工具。下载链接，请参见clickhouse-client。

1. 查看自建实例的database列表。

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

   参数说明：

   参数	描述
   old host	自建实例的地址。
   old port	自建实例的端口。
   old user name	登录自建实例的账号，拥有DML读写和设置权限，允许DDL权限。
   old password	上述账号对应的密码。

   说明

   system是系统数据库，不需要迁移，可以直接过滤掉。

2. 查看自建实例的table列表。

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

   参数说明：

   参数	描述
   database_name	数据库名称

   您也可以通过系统表直接查询所有database和table名称。

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

   说明

   查询到的表名中，如果有以.inner.开头的表，则它们是物化视图的内部表示，不需要迁移，可以直接过滤掉。

3. 导出自建实例中指定数据库下所有表的建表DDL。

   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. 将建表DDL导入到目标实例云数据库ClickHouse。

   说明

   您需要在建表DDL导入之前，在云数据库ClickHouse中创建表所在数据库。

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

   参数说明：

   参数	描述
   new host	目标实例云数据库ClickHouse的地址。
   new port	目标实例云数据库ClickHouse的端口。
   new user name	登录目标实例云数据库ClickHouse的账号，拥有DML读写和设置权限，允许DDL权限。
   new password	上述账号对应的密码。

步骤二：数据迁移

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 ;

常见问题

• Q：如何处理报错：“Too many partitions for single INSERT block (more than 100)”？

  A：单个INSERT操作中超过了max_partitions_per_insert_block（最大分区插入块，默认值为100）。ClickHouse每次写入都会生成一个data part（数据部分），一个分区可能包含一个或多个data part，如果单个INSERT操作中插入了太多分区的数据，那会造成ClickHouse内部有大量的data part（会给合并和查询造成很大的负担）。为了防止出现大量的data part，ClickHouse内部做了限制。

  解决方案：请执行以下操作，调整分区数或者max_partitions_per_insert_block参数。

  • 调整表结构，调整分区方式，或避免单次插入的不同分区数超过限制。

  • 避免单次插入的不同分区数超过限制，可根据数据量适当修改max_partitions_per_insert_block参数，放大单个插入的不同分区数限制，修改语法如下：

    SET GLOBAL ON cluster DEFAULT max_partitions_per_insert_block = XXX;

    说明

    ClickHouse社区推荐默认值为100，分区数不要设置得过大，否则可能对性能产生影响。在批量导入数据后可修改值为默认值。

• Q：为什么目标实例云数据库ClickHouse连接自建数据库ClickHouse连接失败？

  A：可能是您的自建数据库ClickHouse设置了防火墙或白名单等操作。可以在云数据库ClickHouse控制台查看云数据库ClickHouse的VPC网络，并且将该VPC的整个网段都加入到自建数据库ClickHouse的白名单中。如果希望控制白名单的网段范围，避免潜在安全问题，那么可以通过如下SQL查询到目标实例云数据库ClickHouse的后台Server IP，只将这几个IP加入自建实例的白名单中。

  SELECT * FROM system.clusters;