如何迁移自建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导入。
通过DataWorks数据集成或DataX，读取源实例数据，后写入目标Hologres实例，请参见数据集成概述。

在源实例将数据导出为文件，再导入目标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离线整库同步

可以通过DataWorks数据集成同步解决方案将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兼容函数。