本文为您介绍如何迁移自建ClickHouse的数据库表和数据至交互式分析Hologres上进行数据开发。

前提条件

背景信息

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
向量检索 不支持 支持
空间数据 不支持 支持
安全管理 自定义权限 兼容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的迁移。

  1. 在ClickHouse-Client使用如下命令语句查看源ClikHouse实例的数据库列表。
    说明 查询到的表中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源实例账号的密码。
  2. 在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';
  3. 在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 存储策略,可忽略。
  4. 将源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;
  5. 在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实例,操作步骤如下。

  1. 在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源实例迁移的表名称。
  2. 在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实例的表名称。
  3. 在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实例上的数据查询语句。
      -- 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;