本文为您介绍如何迁移自建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 |
| |
索引 |
|
说明 自动建有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 |
向量检索 | 不支持 | 支持 | |
空间数据 | 不支持 | 支持 | |
安全管理 | 自定义权限 | 兼容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使用如下命令语句查看源ClikHouse实例的数据表列表。说明 查询到的表中,如果有以.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。
您也可以使用如下命令直接查看system.tables元数据表。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';
字段 说明 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;
- ClickHouse实例的建表DDL如下所示。
- 将名称为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;
- ClickHouse实例的建表DDL如下所示。
- 将名称为lineitem表在ClickHouse实例中的DDL转换为Hologres的建表DDL。
- 在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的数据写入至Hologres。
- 通过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中查询导入数据,验证数据是否导入成功。
数据查询语句迁移
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) WITH GROUP(ORDER BY expr) quantileExact(level) (expr) percentile_cont (level) WITH 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实例上的数据查询语句。
- 示例二。
- 在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实例上的数据查询语句。
- 示例三。
- 在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;
- 在ClickHouse实例上的数据查询语句。
函数兼容
Hologres与Clickhouse存在大量语法一致的基础函数。针对其他Clickhouse函数,Hologres支持部分函数或其同语义函数。具体函数兼容情况请参见Clickhouse兼容函数。