ApsaraDB for 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;