Hologres:Migrate data from ClickHouse

Numeric types

ClickHouse offers signed and unsigned integer types at multiple precisions. Hologres uses the standard SQL integer hierarchy. Key difference: ClickHouse has no native Boolean type; Hologres supports BOOLEAN natively.

String and character types

ClickHouse stores all string data as raw bytes with a String type. Hologres maps this to TEXT. LowCardinality is a ClickHouse storage optimization with no direct Hologres equivalent — after migration, enable dictionary encoding manually on the corresponding columns.

Date and time types

ClickHouse uses Date and various DateTime variants. Hologres maps all timezone-aware types to TIMESTAMPTZ.

Complex and other types

Nested structures in ClickHouse (such as Nested and Tuple) are flattened to arrays in Hologres. For Binary data, choose the Hologres type based on your use case.

Step 1: List all databases

Run the following command in the ClickHouse client to list databases in the source instance.

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

The system database appears in the results but does not need to be migrated. Filter it out.

Step 2: List all tables

Run the following command in the ClickHouse client to list tables in a specific database.

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

Tables starting with .inner. are internal tables for materialized views and do not need to be migrated. Filter them out.

To list all databases and tables at once, query the system.tables metadata table:

SELECT DISTINCT database, name FROM system.tables WHERE database != 'system';

Step 3: Export table DDL

Export the DDL for each table you want to migrate.

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

Alternatively, query system.tables directly to inspect DDL and metadata for all non-distributed tables:

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

The following table explains how each system.tables field maps to Hologres concepts.

Step 4: Convert ClickHouse DDL to Hologres DDL

Apply the type mappings and field conversion rules to convert each table's DDL. The following examples use TPC-H benchmark tables.

Example: lineitem table

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);

Converted 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)
);
-- sorting_key (l_orderkey, l_linenumber) maps to clustering key and segment key
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');
-- LowCardinality columns: enable bitmap and dictionary encoding
CALL set_table_property('LINEITEM', 'bitmap_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT');
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;

Example: customer table

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);

Converted 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;

Step 5: Import DDL into Hologres

Run the following command in the psql client to execute the converted DDL against the target Hologres instance.

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

Step 1: Export data from ClickHouse to CSV

CSV is recommended for its simplicity and broad tool support. For tables with complex nested types or high-precision decimals, validate the exported values before loading, as CSV does not preserve type metadata.

Run the following command in the ClickHouse client to export table data to a local CSV file.

clickhouse-client --host="<host>" --port="<port>" --user="<username>" --password="<password>" \
  --query="SELECT * FROM <database_name>.<table_name> FORMAT CSV" > table.csv

Step 2: Import the CSV file into Hologres

Run the following command in the psql client to load the CSV file into the target Hologres table.

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

Step 3: Verify the import

Query the imported table in Hologres to confirm the data loaded correctly.

SELECT COUNT(*) FROM <schema_name>.<table_name>;

Syntax differences

Function equivalents

Functions not listed below have identical syntax in both systems.

Migration strategies

Regular expression replacement — use regex to replace fixed patterns such as function names, identifier quoting, and <database>.<table> references with their Hologres equivalents.

ClickHouse Extension — Hologres provides a ClickHouse Extension that natively supports some ClickHouse functions, such as toUInt32(), without conversion. See ClickHouse-compatible functions.

Query migration examples

The following examples use TPC-H benchmark queries. Each example highlights a specific migration pattern.

Example 1: Simple aggregation — no changes required

For straightforward aggregations using standard SQL functions, ClickHouse and Hologres queries are identical.

ClickHouse (Q1):

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) — no changes:

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;

Example 2: Subquery rewrite — IN to EXISTS

ClickHouse supports IN (subquery) with cross-table references in a way that Hologres does not. Rewrite using EXISTS.

ClickHouse (Q4):

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) — IN (subquery) replaced with EXISTS:

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;

Example 3: Function replacement — `toDecimal32()` to a literal

ClickHouse's toDecimal32(value, scale) function has no Hologres equivalent. Replace it with a plain numeric literal.

ClickHouse (Q11):

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) — toDecimal32(0.0000010000, 9) replaced with 0.0000010000:

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;