Hologres:Migrate tables and data from ClickHouse to Hologres

Data type mappings

Migrate metadata

The migration of metadata involves the migration of DDL statements that are used to create tables. To migrate metadata, perform the following steps:

1. Execute the following statement on the ClickHouse client to query the databases in your ClickHouse cluster.

   Note

   The database named system in the query result is the system database. This database does not need to be migrated and can be skipped.

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

   The following table describes the parameters.

   Parameter	Description
   host	The endpoint of the ClickHouse cluster.
   port	The port number of the ClickHouse cluster.
   username	The username of the account that you use to log on to the ClickHouse cluster. The user must have permissions to execute specific DML statements, including read, write, and setting permissions. The user must also have permissions to execute DDL statements.
   password	The password of the account that you use to log on to the ClickHouse cluster.

2. Execute the following statement on the ClickHouse client to query the tables in your ClickHouse cluster.

   Note

   You can skip the returned tables whose names start with .inner. because these tables are internal representations of materialized views.

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

   The following table describes the parameters.

   Parameter	Description
   host	The endpoint of the ClickHouse cluster.
   port	The port number of the ClickHouse cluster.
   username	The username of the account that you use to log on to the ClickHouse cluster. The user must have permissions to execute specific DML statements, including read, write, and setting permissions. The user must also have permissions to execute DDL statements.
   password	The password of the account that you use to log on to the ClickHouse cluster.
   database_name	The name of the database in which the tables to be migrated reside.

   You can also execute the following statement to query the names of all databases and tables in the ClickHouse cluster:

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

3. Execute the following statement on the ClickHouse client to export the DDL statements that are used to create tables in the ClickHouse cluster:

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

   You can also execute the following statement to query system tables:

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

   The following table describes the conversion of the system table fields in ClickHouse when metadata is migrated from ClickHouse to Hologres.

   Parameter	Description
   database	The database in ClickHouse, which corresponds to a schema in Hologres that uses the PostgreSQL syntax. The create database "<database_name>"; statement in ClickHouse corresponds to the create schema "<schema_name>"; statement in Hologres.
   name	The name of the ClickHouse table. No conversion is required.
   engine	Hologres does not have distributed tables. Tables in Hologres are not classified into distributed tables and local tables. Tables in Hologres are individual tables for distributed storage and query. When you query system tables in the ClickHouse database, you must skip the tables that meet the engine='Distributed' condition.
   is_temporary	Specifies whether the table is a temporary table in ClickHouse. Temporary tables do not need to be migrated. In addition, Hologres does not support temporary tables.
   data_paths metadata_path metadata_modification_time	These fields can be ignored.
   dependencies_database dependencies_table	These fields are required when views and materialized views are used. In Hologres, a view with dependencies must be created before a base table is created. Hologres does not support materialized views.
   create_table_query	The DDL statement that is used to create the source ClickHouse table. To create the destination Hologres table, the statement must be converted into the DDL statement that uses the PostgreSQL syntax.
   engine_full	The details of the engine. This field can be ignored.
   partition_key	The partition key column of the ClickHouse table, which corresponds to the partition key column of the Hologres table. For example, if the partition_key parameter of the ClickHouse table is set to col1, you must append the partition by list (col1); clause to the statements that are used to create the Hologres table.
   sorting_key	The sort key of the ClickHouse table. The sort key corresponds to a segment key or a clustering key of the Hologres table.
   primary_key	The primary key of the ClickHouse table. The primary key corresponds to the primary key in the DDL statements that are used to create the Hologres table.
   sampling_key	The key used for sampling. Hologres DDL statements do not support sampling.
   storage_policy	The storage policy of the ClickHouse database. This field can be ignored.

4. Convert the DDL statements that are used to create the source ClickHouse table into the statements that are used to create the destination Hologres table. Hologres is compatible with the PostgreSQL syntax.

   You must convert DDL statements based on the field conversion description in the preceding table and the Data type mappings section in this topic. Examples:

   • Convert the DDL statements that are used to create the lineitem table in the ClickHouse cluster into the DDL statements that are used to create a destination Hologres table.

     • The following sample code shows the DDL statements that are used to create the lineitem table in the ClickHouse cluster:

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

     • The following sample code shows the converted DDL statements that are used to create the destination Hologres table:

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

   • Convert the DDL statements that are used to create the customer table in the ClickHouse cluster into the DDL statements that are used to create a destination Hologres table.

     • The following sample code shows the DDL statements that are used to create the customer table in the ClickHouse cluster:

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

     • The following sample code shows the converted DDL statements that are used to create the destination Hologres table:

       -- 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. Execute the following statement on the PostgreSQL client to import the converted DDL statements to the destination Hologres instance:

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

Migrate data

The following example shows how to export the data that you want to migrate from the ClickHouse cluster as a file and import the file to the Hologres instance. To use this method, perform the following steps:

1. Execute the following statement on the ClickHouse client to export the data from the ClickHouse cluster 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;

   The following table describes the parameters.

   Parameter	Description
   host	The endpoint of the ClickHouse cluster.
   port	The port number of the ClickHouse cluster.
   username	The username of the account that you use to log on to the ClickHouse cluster. The user must have permissions to execute specific DML statements, including read, write, and setting permissions. The user must also have permissions to execute DDL statements.
   password	The password of the account that you use to log on to the ClickHouse cluster.
   database_name	The name of the database in which the table to be migrated resides.
   table_name	The name of the table that you want to migrate from the ClickHouse cluster.

2. Execute the following statement on the PostgreSQL client to import the local CSV file to the Hologres instance:

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

   The following table describes the parameters.

   Parameter	Description
   username	The username of the account that you use to log on to the Hologres instance. The user must have permissions to execute specific DML statements, including read, write, and setting permissions. The user must also have permissions to execute DDL statements. The username is usually the AccessKey ID of your Alibaba Cloud account. You can move the pointer over the profile picture in the upper-right corner of the Hologres console and select AccessKey Management to obtain the AccessKey ID.
   password	The password of the account that you use to log on to the Hologres instance. The password is usually the AccessKey secret of your Alibaba Cloud account. You can move the pointer over the profile picture in the upper-right corner of the Hologres console and select AccessKey Management to obtain the AccessKey secret.
   host	The endpoint of the Hologres instance. You can obtain this information from the Network Information section of the instance details page in the Hologres console.
   port	The port number of the Hologres instance. You can obtain this information from the Network Information section of the instance details page in the Hologres console.
   database_name	The name of the Hologres database to which the local CSV file is to be imported.
   schema_name	The name of the Hologres schema to which the local CSV file is to be imported. Default value: public.
   table_name	The name of the Hologres table to which the local CSV file is to be imported.

3. Query the imported data on the Hologres instance to verify whether the import is successful.

Synchronize all data from a ClickHouse database to Hologres in offline mode

You can use the synchronization solution provided by DataWorks Data Integration to synchronize all data from a ClickHouse database to Hologres in offline mode. For more information, see Synchronize data from multiple tables in an ApsaraDB for ClickHouse database to Hologres in offline mode.

Convert data query statements

The data query statements in Hologres use the PostgreSQL syntax, whereas those in ClickHouse use custom syntax. The SQL syntax of ClickHouse is partially compatible with the American National Standards Institute (ANSI) SQL syntax. The SQL syntax of Hologres and that of ClickHouse are similar on the whole but has differences in details. Therefore, you need to convert data query statements, especially the functions such as scalar functions and window functions in the data query statements.

Function compatibility

The syntax of many basic functions in Hologres is the same as that of basic functions in ClickHouse. Hologres supports some ClickHouse functions or the ClickHouse functions that have the same semantics as Hologres functions. For more information about the function compatibility between Hologres and ClickHouse, see Supported ClickHouse functions.