如何遷移自建ClickHouse的資料庫表和資料至Hologres - Hologres

本文為您介紹如何遷移自建ClickHouse的資料庫表和資料至即時數倉Hologres上進行資料開發。

前提條件

開通Hologres，詳情請參見購買Hologres。
已有ClickHouse執行個體，並且安裝ClickHouse-Client工具，如需安裝請單擊ClickHouse-Client，安裝和使用詳情請參見Getting Started。
使用PSQL用戶端串連Hologres執行個體，詳情請參見PSQL用戶端。

背景資訊

ClickHouse是一個用於聯機分析（OLAP）的列式資料庫管理系統。Hologres是阿里巴巴自主研發的一款互動式分析產品，支援亞秒級響應與高QPS，您可以從ClickHouse遷移表和資料至Hologres擷取更好的資料開發體驗。

Hologres與ClickHouse產品特性對比如下。

分類	對比項	Clickhouse	Hologres
產品	定位	流量分析	通用即時數倉：資料分析和線上服務。
寫入	儲存	列存	列存和行存。
	寫入可見度	秒級（需要用戶端攢資料進行批處理，分布式表寫入依賴Shard資料複製完成）	毫秒級（寫入自適應批處理，寫入即可查）
	寫入效能	高	非常高
	明細儲存	支援	支援
	主鍵（Primary Key）	非資料庫主鍵（不支援唯一性限制式，僅用於索引+彙總）	標準資料庫主鍵，支援唯一性限制式。
	可更新	不完備，能力弱（不支援基於主鍵的高QPS更新）。	完整支援（支援基於主鍵的高QPS更新）。
	即時寫入	Append	Append insert or ignore insert or replace update
	索引	primary key minmax ngram token bloom filter	bitmap dictionary segment primary clustering 說明自動建有minmax、bloom filter、ngram等索引，對使用者透明。
查詢	最佳化器	RBO（Rule-Based Optimizer）	CBO（Cost-Based Optimizer）
	聯邦查詢	支援（Engine支援HDFS、Kafka）	支援（FDW直讀MaxCompute、Hive）
	預彙總	支援（通過MergeTree）	支援（預存程序+定期調度）
	高QPS點查	不支援	支援，QPS可達千萬以上。
	單表複雜查詢	效能好	效能好
	多表JOIN	效能差	效能好
	SQL文法	自訂文法	相容PostgreSQL，功能更豐富。
	WINDOW FUNCTION	不支援	支援
事務	事務ACID	無（不保證寫入即可查，最終一致性）	有限支援（支援DDL事務、單行事務、基於snapshot的可見度）
複製	容災和備份	通過Replication實現（遠程ZK+CK）	通過Binlog複製實現邏輯複製，通過底層機制實現物理複製。
進階功能	Binlog	無	提供Binlog
	向量檢索	ClickHouse 22.8及以上版本支援	支援
	空間資料	不支援	支援
	安全管理	自訂許可權	相容PG許可權模型、豐富的許可權控制、IP白名單、資料脫敏。
	儲存計算分離	不分離，單機容量限制	分離，儲存容量近乎無限。
	可用性	使用者手工處理Failover	Failover自動回復
	營運	複雜（手工維護Shard分布）	免營運
生態	資料接入	Kafka、Flink、Spark、...	Flink、Spark、JDBC、DataX、…
生態	BI工具	支援對接少量BI工具（Tableau、Superset、...）	相容PostgreSQL生態，支援對接100+主流BI工具。

資料類型映射

ClickHouse與Hologres的資料類型映射如下表所示。

類別	ClickHouse	Hologres
日期	Date	Date
	DateTime	TIMESTAMPTZ
	DateTime(timezone)	TIMESTAMPTZ
	DateTime64	TIMESTAMPTZ
數值	Int8	不支援單位元組INT，可選SMALLINT。
	Int16	SMALLINT
	Int32	INT
	Int64	BIGINT
	UInt8	INT
	UInt16	INT
	UInt32	BIGINT
	UInt64	BIGINT
	Float32	FLOAT
	Float64	DOUBLE PRECISION
	Decimal(P, S)	DECIMAL
	Decimal32(S)	DECIMAL
	Decimal64(S)	DECIMAL
	Decimal128(S)	DECIMAL
布爾	無，使用UInt8代替。	BOOLEAN
字元	String	TEXT
	FixString(N)	無，使用TEXT代替。
	LowCardinality	無，自動智能設定或使用`call set_table_properties('x', 'dictionary_encoding_columns', 'col');` 命令進行設定。
二進位	無，使用String或FixString(N)。	BIT(n)、VARBIT(n)、BYTEA、CHAR(n) 等資料類型。
其他	UUID	UUID
	Enum	不支援，使用TEXT代替。
	Nested、 Tuple、Array	數組

中繼資料遷移

中繼資料的遷移，主要指進行建表DDL的遷移。

在ClickHouse-Client使用如下命令語句查看源ClickHouse執行個體的資料庫列表。

說明

查詢到的表中system是系統資料庫，不需要遷移，可以直接過濾掉。

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

參數說明如下。

參數	說明
host	ClickHouse源執行個體的地址。
port	ClickHouse源執行個體的連接埠。
username	登入ClickHouse源執行個體的帳號，擁有DML讀寫和設定許可權，允許DDL許可權。
password	登入ClickHouse源執行個體帳號的密碼。

在ClickHouse-Client使用如下命令語句查看源ClickHouse執行個體的資料表列表。

說明

查詢到的表中，如果有以.inner.開頭的表，此類表是物化視圖的內部表，不需要遷移，可以直接過濾掉。

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

參數說明如下。

參數	說明
host	源ClickHouse執行個體的地址。
port	源ClickHouse執行個體的連接埠。
username	登入源ClickHouse執行個體的帳號，擁有DML讀寫和設定許可權，允許DDL許可權。
password	登入源ClickHouse執行個體帳號的密碼。
database_name	源ClickHouse執行個體遷移表所在的資料庫名稱。

您也可以通過以下命令語句查詢源ClickHouse執行個體所有的資料庫和表名稱。

select distinct database, name from system.tables where database != 'system';

在ClickHouse-Client使用如下命令語句匯出ClickHouse源執行個體的建表DDL。

clickhouse-client --host="<host>" --port="<port>" --user="<username>" --password="<password>" --query="SHOW CREATE TABLE <database_name>.<table_name>"  > table.sql;

您也可以使用如下命令直接查看system.tables中繼資料表。

SELECT * FROM system.tables
where database = '<database_name>' and engine != 'Distributed';

system.tables中欄位的轉換說明如下。

欄位	說明
database	ClickHouse的database映射到Hologres（PostgreSQL文法）的Schema概念，即ClickHouse的`create database "<database_name>";` 命令映射為Hologres的`create schema "<schema_name>";`命令。
name	表名稱，無需改動。
engine	Hologres沒有Distributed表概念，沒有Local和Distributed之分，就是一個單表，分布式儲存和查詢，所以需要過濾掉`engine='Distributed'`的表。
is_temporary	Temporary表邏輯上無須遷移，同時Hologres暫不支援Temporary表。
data_paths metadata_path metadata_modification_time	可忽略。
dependencies_database dependencies_table	常見於View、Materialized View。具有dependencies的View，在Hologres中，需要先於base表建立。Hologres的Materialized View還未支援。
create_table_query	ClickHouse源執行個體表的DDL，需轉換成Hologres DDL（PostgreSQL文法）。
engine_full	Engine詳細資料，可忽略。
partition_key	對應Hologres的分區列，ClickHouse的源執行個體partition_key如果為col1，則Hologres建表語句後添加`partition by list (col1);`語句。
sorting_key	對應Hologres的Segment Key和Clustering Key索引。
primary_key	主鍵，對應Hologres DDL文法的Primary Key。
sampling_key	Hologres DDL不支援採樣。
storage_policy	儲存策略，可忽略。

將源ClickHouse執行個體的建表DDL轉換為Hologres的文法（相容PostgreSQL SQL標準）。

根據system.tables中欄位的轉換說明和資料類型映射進行DDL轉換，樣本如下。

將名稱為lineitem表在ClickHouse執行個體中的DDL轉換為Hologres的建表DDL。

ClickHouse執行個體的建表DDL如下所示。

-- lineitem on ClickHouse
CREATE TABLE lineitem_local ON CLUSTER default(
  l_orderkey            UInt64,
  l_partkey             UInt32,
  l_suppkey             UInt32,
  l_linenumber          UInt32,
  l_quantity            decimal(15,2),
  l_extendedprice       decimal(15,2),
  l_discount            decimal(15,2),
  l_tax                 decimal(15,2),
  l_returnflag          LowCardinality(String),
  l_linestatus          LowCardinality(String),
  l_shipdate            Date,
  l_commitdate          Date,
  l_receiptdate         Date,
  l_shipinstruct        LowCardinality(String),
  l_shipmode            LowCardinality(String),
  l_comment             LowCardinality(String)
) ENGINE = MergeTree
PARTITION BY toYear(l_shipdate)
ORDER BY (l_orderkey, l_linenumber);

CREATE TABLE lineitem on cluster default as lineitem_local ENGINE = Distributed(default, default, lineitem_local, l_orderkey);

轉換後Hologres執行個體的建表DDL如下所示。

-- lineitem on Hologres
-- create a table group with 32 shards
CALL hg_create_table_group ('lineitem_tg', 32);
BEGIN;
CREATE TABLE LINEITEM
(
    L_ORDERKEY      BIGINT         NOT NULL,
    L_PARTKEY       INT         NOT NULL,
    L_SUPPKEY       INT         NOT NULL,
    L_LINENUMBER    INT         NOT NULL,
    L_QUANTITY      DECIMAL(15,2) NOT NULL,
    L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
    L_DISCOUNT      DECIMAL(15,2) NOT NULL,
    L_TAX           DECIMAL(15,2) NOT NULL,
    L_RETURNFLAG    TEXT        NOT NULL,
    L_LINESTATUS    TEXT        NOT NULL,
    L_SHIPDATE      TIMESTAMPTZ NOT NULL,
    L_COMMITDATE    TIMESTAMPTZ NOT NULL,
    L_RECEIPTDATE   TIMESTAMPTZ NOT NULL,
    L_SHIPINSTRUCT  TEXT        NOT NULL,
    L_SHIPMODE      TEXT        NOT NULL,
    L_COMMENT       TEXT        NOT NULL,
    PRIMARY KEY (L_ORDERKEY,L_LINENUMBER)
);
CALL set_table_property('LINEITEM', 'clustering_key', 'L_SHIPDATE,L_ORDERKEY');
CALL set_table_property('LINEITEM', 'segment_key', 'L_SHIPDATE');
CALL set_table_property('LINEITEM', 'table_group', 'lineitem_tg');
CALL set_table_property('LINEITEM', 'distribution_key', 'L_ORDERKEY');
-- columns with LowCardinality
CALL set_table_property('LINEITEM', 'bitmap_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT');
-- columns with LowCardinality
CALL set_table_property('LINEITEM', 'dictionary_encoding_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT');
CALL set_table_property('LINEITEM', 'time_to_live_in_seconds', '31536000');
COMMIT;

將名稱為customer表在ClickHouse執行個體中的DDL轉換為Hologres的建表DDL。

ClickHouse執行個體的建表DDL如下所示。

-- customer on ClickHouse
CREATE TABLE customer_local ON CLUSTER default(
  c_custkey             UInt32,
  c_name                String,
  c_address             String,
  c_nationkey           UInt32,
  c_phone               LowCardinality(String),
  c_acctbal             decimal(15,2),
  c_mktsegment          LowCardinality(String),
  c_comment             LowCardinality(String)
) ENGINE = MergeTree
ORDER BY (c_custkey);

CREATE TABLE customer on cluster default as customer_local
ENGINE = Distributed(default, default, customer_local, c_custkey);

轉換後Hologres執行個體的建表DDL如下所示。

-- customer on Hologres
BEGIN;
CREATE TABLE CUSTOMER (
    C_CUSTKEY    INT    NOT NULL PRIMARY KEY,
    C_NAME       TEXT   NOT NULL,
    C_ADDRESS    TEXT   NOT NULL,
    C_NATIONKEY  INT    NOT NULL,
    C_PHONE      TEXT   NOT NULL,
    C_ACCTBAL    DECIMAL(15,2) NOT NULL,
    C_MKTSEGMENT TEXT   NOT NULL,
    C_COMMENT    TEXT   NOT NULL
);
CALL set_table_property('CUSTOMER', 'distribution_key', 'C_CUSTKEY');
CALL set_table_property('CUSTOMER', 'table_group', 'lineitem_tg');
CALL set_table_property('CUSTOMER', 'bitmap_columns', 'C_CUSTKEY,C_NATIONKEY,C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT');
CALL set_table_property('CUSTOMER', 'dictionary_encoding_columns', 'C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT');
CALL set_table_property('CUSTOMER', 'time_to_live_in_seconds', '31536000');
COMMIT;

在PSQL用戶端使用如下命令語句將轉換後的建表DDL匯入到目標Hologres執行個體中。

PGUSER="<username>" PGPASSWORD="<password>" psql -h "<host>" -p "<port>" -d "<database_name>" -f table.sql;

資料移轉

從源ClickHouse遷移資料至Hologres有如下三種方法。

（推薦）在源執行個體將資料匯出為檔案，然後通過COPY語句命令語句（JDBC/PSQL）將檔案匯入到Hologres目標執行個體。
通過編寫Flink、Spark job將源執行個體資料讀出，然後寫入目標Hologres執行個體，請參見使用Spark匯入。
通過DataWorksData Integration或DataX，讀取源執行個體資料，後寫入目標Hologres執行個體，請參見Data Integration概述。

在源執行個體將資料匯出為檔案，再匯入目標Hologres執行個體，操作步驟如下。

在ClickHouse-Client使用如下命令語句匯出源執行個體資料至本地CSV檔案。

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

參數說明如下。

參數	說明
host	ClickHouse源執行個體的地址。
port	ClickHouse源執行個體的連接埠。
username	登入ClickHouse源執行個體的帳號，擁有DML讀寫和設定許可權，允許DDL許可權。
password	登入ClickHouse源執行個體帳號的密碼。
database_name	ClickHouse源執行個體遷移表所在的資料庫名稱。
table_name	ClickHouse源執行個體遷移的表名稱。

在PSQL用戶端使用如下命令語句將本地CSV檔案匯入到Hologres執行個體。

PGUSER="<username>" PGPASSWORD="<password>" psql -h "<host>" -p "<port>" -d "<database_name>" -c "COPY <schema_name>.<table_name> FROM STDIN (FORMAT 'csv')" < table.csv;

參數說明如下。

參數	說明
username	登入Hologres目標執行個體的帳號，擁有DML讀寫和設定許可權，允許DDL許可權。通常是阿里雲帳號的AccessKey ID，您可以單擊AccessKey 管理，擷取AccessKey ID。
password	登入Hologres目標執行個體帳號的密碼，通常是阿里雲帳號的AccessKey Secret，您可以單擊AccessKey 管理，擷取AccessKey Secret。
host	Hologres執行個體的伺服器位址。您可以登入管理主控台，進入執行個體詳情頁，從網路資訊擷取。
port	Hologres執行個體的連接埠。您可以登入管理主控台，進入執行個體詳情頁，從網路資訊擷取。
database_name	遷移到Hologres執行個體的資料庫名稱。
schema_name	遷移到Hologres執行個體的Schema名稱，不填預設為public。
table_name	遷移到Hologres執行個體的表名稱。

在Hologres中查詢匯入資料，驗證資料是否匯入成功。

ClickHouse離線整庫同步

可以通過DataWorksData Integration同步解決方案將ClickHouse整個資料庫的資料離線同步至Hologres，詳情請參見ClickHouse整庫資料離線同步至Hologres。

資料查詢語句遷移

Hologres的資料查詢語句採用PostgreSQL文法，ClickHouse為自創文法，部分相容SQL ANSI，兩者文法基本類似，但有細節上的差異。所以需要對資料查詢語句進行遷移，常見的是SQL中使用函數名的遷移，例如Scalar函數、Window函數等。

ClickHouse和Hologres的SQL有如下差別。

ClickHouse中用''包圍的列名，在Hologres中需要替換成""包圍。
ClickHouse中使用SELECT X FROM <database_name>.<table_name>命令，在Hologres中使用SELECT X FROM <schema_name>.<table_name>命令。

運算式差異，主要表現在函數上，函數映射表（僅列出有差異的函數，未列出則無差異）如下所示。

ClickHouse	Hologres
toYear(expr)	to_char(expr, 'YYYY')
toInt32(expr)	CAST(expr as INTEGER)
uniq() uniqCombined() uniqCombined64() uniqHLL12()	approx_count_distinct()
uniqExact()	count(distinct x)
quantile(level) (expr)	approx_percentile(level) WITHIN GROUP(ORDER BY expr)
quantileExact(level) (expr)	percentile_cont (level) WITHIN GROUP(ORDER BY expr)

資料查詢語句遷移有如下方法。

正則替換
採用正則替換的方式，將ClickHouse的一些固定模式的文法（函數名、標誌符、運算式等）轉換成Hologres的文法，例如將''轉換為""。
ClickHouse Extension
Hologres中具備ClickHouse Extension，相容部分ClickHouse函數，無需轉換，例如toUInt32()函數。

下面以部分TPC-H Query為例，將ClickHouse源執行個體的資料查詢語句遷移至Hologres的樣本如下所示。

樣本一。

在ClickHouse執行個體上的資料查詢語句。

-- Q1 on ClickHouse
select
  l_returnflag,
  l_linestatus,
  sum(l_quantity) as sum_qty,
  sum(l_extendedprice) as sum_base_price,
  sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
  sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
  avg(l_quantity) as avg_qty,
  avg(l_extendedprice) as avg_price,
  avg(l_discount) as avg_disc,
  count(*) as count_order
from
  lineitem
where
  l_shipdate <= date '1998-12-01' - interval '90' day
group by
  l_returnflag,
  l_linestatus
order by
  l_returnflag,
  l_linestatus;

轉換後Hologres執行個體的資料查詢語句。

-- Q1 on Hologres
select
  l_returnflag,
  l_linestatus,
  sum(l_quantity) as sum_qty,
  sum(l_extendedprice) as sum_base_price,
  sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
  sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
  avg(l_quantity) as avg_qty,
  avg(l_extendedprice) as avg_price,
  avg(l_discount) as avg_disc,
  count(*) as count_order
from
  lineitem
where
  l_shipdate <= date '1998-12-01' - interval '90' day
group by
  l_returnflag,
  l_linestatus
order by
  l_returnflag,
  l_linestatus;

樣本二。

在ClickHouse執行個體上的資料查詢語句。

-- Q4 on ClickHouse
select
  o_orderpriority,
  count(*) as order_count
from
  orders
where
  o_orderdate >= date '1993-07-01'
  and o_orderdate < date '1993-07-01' + interval '3' month
  and o_orderdate in (
    select
      o_orderdate
    from
      lineitem,
      orders
    where
      l_orderkey = o_orderkey
      and l_commitdate < l_receiptdate
  )
group by
  o_orderpriority
order by
  o_orderpriority;

轉換後Hologres執行個體的資料查詢語句。

-- Q4 on Hologres
select
  o_orderpriority,
  count(*) as order_count
from
  orders
where
  o_orderdate >= date '1993-07-01'
  and o_orderdate < date '1993-07-01' + interval '3' month
  and exists (
    select
      *
    from
      lineitem
    where
      l_orderkey = o_orderkey
      and l_commitdate < l_receiptdate
  )
group by
  o_orderpriority
order by
  o_orderpriority;

樣本三。

在ClickHouse執行個體上的資料查詢語句。

-- Q11 on ClickHouse
select
  ps_partkey,
  sum(ps_supplycost * ps_availqty) as value
from
  partsupp,
  supplier,
  nation
where
  ps_suppkey = s_suppkey
  and s_nationkey = n_nationkey
  and n_name = 'GERMANY'
group by
  ps_partkey having
    sum(ps_supplycost * ps_availqty) > (
      select
        sum(ps_supplycost * ps_availqty) * toDecimal32(0.0000010000,9)
      from
        partsupp,
        supplier,
        nation
      where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'GERMANY'
    )
order by
  value desc
  limit 100;

轉換後Hologres執行個體的資料查詢語句。

-- Q11 on Hologres
select
  ps_partkey,
  sum(ps_supplycost * ps_availqty) as value
from
  partsupp,
  supplier,
  nation
where
  ps_suppkey = s_suppkey
  and s_nationkey = n_nationkey
  and n_name = 'GERMANY'
group by
  ps_partkey having
    sum(ps_supplycost * ps_availqty) > (
      select
        sum(ps_supplycost * ps_availqty) * 0.0000010000
      from
        partsupp,
        supplier,
        nation
      where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'GERMANY'
    )
order by
  value desc
  limit 100;

函數相容

Hologres與Clickhouse存在大量文法一致的基礎函數。針對其他Clickhouse函數，Hologres支援部分函數或其同語義函數。具體函數相容情況請參見Clickhouse相容函數。