This topic describes how to migrate data from a self-managed ClickHouse database to Hologres for data development and analytics.

Prerequisites

Background information

ClickHouse is a column-oriented database management system used for online analytical processing (OLAP). Hologres is an interactive analytics service developed by Alibaba Cloud. Hologres can respond to queries within subseconds and supports a large amount of queries per second (QPS). You can migrate tables and data from a ClickHouse cluster to Hologres to enhance data development and analytics.

The following table compares the features of Hologres and ClickHouse.
Category Feature ClickHouse Hologres
Service Orientation OLAP-based service Universal real-time data warehouse for data analytics and online data processing
Data write Storage mode Column-oriented Column-oriented and row-oriented
Visibility Data can be viewed within seconds after it is written. The ClickHouse client can accumulate data for batch processing. Data is written to a distributed table based on data replication in shards. Data can be viewed within milliseconds after it is written. Data is written in a way that adapts to batch processing.
Performance Good Excellent
Detailed records Supported Supported
Primary key ClickHouse does not require a unique primary key. Primary keys cannot be used as unique constraints and are used only for indexing and merge operations. Hologres requires standard database primary keys. A primary key can be used as a unique constraint.
Update performance Incomplete and weak support. ClickHouse does not support frequent updates based on primary keys. Complete. Hologres supports frequent updates based on primary keys.
Statement for real-time data writing APPEND
  • APPEND
  • INSERT OR IGNORE
  • INSERT OR REPLACE
  • UPDATE
Indexing
  • primary key
  • minmax
  • ngram
  • token
  • bloom filter
  • bitmap
  • dictionary
  • segment
  • primary
  • clustering
Note Indexes such as minmax, Bloom filter, and N-gram are automatically created and transparent to users.
Query Optimizer Rule-based optimizer (RBO) Cost-based optimizer (CBO)
Federated query Supported. ClickHouse allows you to perform federated queries based on the Hadoop Distributed File System (HDFS) and Kafka engines. Supported. Hologres allows you to use a foreign data wrapper (FDW) to directly read MaxCompute and Hive data.
Pre-aggregation Supported. Pre-aggregation is performed based on MergeTree. Supported. Pre-aggregation is performed based on stored procedures and periodic scheduling.
Frequent point query Not supported Supported. More than 10 million point queries can be performed per second.
Performance of single-table complex query Good Good
Performance of joining multiple tables Poor Good
SQL syntax Custom syntax Hologres is compatible with the standard PostgreSQL syntax and provides more features.
Window function Not supported Supported
Transaction Atomicity, consistency, isolation, durability (ACID) of transactions ClickHouse does not ensure the ACID of transactions. For example, ClickHouse does not ensure that data can be viewed instantly after it is written. In addition, ClickHouse does not ensure data consistency. Hologres ensures the ACID of transactions to a specific extent. For example, Hologres ensures the visibility of data for DDL transactions, single-row transactions, and snapshot-based transactions.
Replication Disaster recovery and backup ClickHouse uses data replicas for disaster recovery and backup on remote Zookeeper and ClickHouse servers. Hologres uses the binary logging feature to logically replicate data and uses the underlying mechanism to physically replicate data.
Advanced feature Binary logging Not supported Supported
Vector search Not supported Supported
Spatial data Not supported Supported
Security management ClickHouse allows you to customize permissions for security management. Hologres is compatible with the Postgres permission model and allows you to perform various operations to manage permissions, configure IP address whitelists, and mask sensitive data for security management.
Separation between storage and computing Storage and computing are not separated. The storage capacity on a single server is limited. Storage and computing are separated. The storage capacity on a single server is almost unlimited.
Availability Failovers must be manually handled. Failovers can be automatically handled.
O&M O&M is complex and the shard distribution must be manually maintained. No O&M operations are required.
Ecosystem Data connection A variety of ecosystem partners such as Apache Kafka, Apache Flink, and Apache Spark are supported. A variety of ecosystem partners such as Apache Flink, Apache Spark, Java Database Connectivity (JDBC), and DataX are supported.
Business intelligence (BI) tool A small number of BI tools such as Tableau and Superset are supported. Hologres is compatible with the PostgreSQL ecosystem and supports more than 100 mainstream BI tools.

Data type mappings

The following table describes the mappings between data types in ClickHouse and Hologres.
Category ClickHouse Hologres
Date Date Date
DateTime TIMESTAMPTZ
DateTime(timezone) TIMESTAMPTZ
DateTime64 TIMESTAMPTZ
Numeric value Int8 Hologres does not support the single-byte INT data type, but uses SMALLINT instead.
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
Boolean value ClickHouse does not support the BOOLEAN data type, but uses UInt8 instead. BOOLEAN
String String TEXT
FixString(N) Hologres does not support the FixString(N) data type, but uses TEXT instead.
LowCardinality Hologres does not support the LowCardinality data type. Hologres automatically and intelligently enables dictionary encoding and allows you to call the set_table_properties('x', 'dictionary_encoding_columns', 'col') function to build dictionary mappings for specific columns.
Binary value ClickHouse does not support the BINARY data type, but uses String or FixString(N) instead. Hologres supports data types such as BIT(n), VARBIT(n), BYTEA, and CHAR(n).
Others UUID UUID
Enum Hologres does not support the Enum data type, but uses TEXT instead.
Nested, Tuple, and Array ARRAY

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. Run the following command on the ClickHouse client to query the databases in your ClickHouse cluster.
    Note The database named system in the command output 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 in the command.
    Parameter Description
    host The endpoint of the ClickHouse cluster.
    port The port number of the ClickHouse cluster.
    username The username that is used to log on to the ClickHouse cluster. The specified 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 that is used to log on to the ClickHouse cluster.
  2. Run the following command on the ClickHouse client to query the tables in the ClickHouse cluster.
    Note You can skip 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 in the command.
    Parameter Description
    host The endpoint of the ClickHouse cluster.
    port The port number of the ClickHouse cluster.
    username The username of the account used to log on to the ClickHouse cluster. The specified 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 used 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 run the following command to query the names of all databases and tables in the ClickHouse cluster:
    select distinct database, name from system.tables where database != 'system';
  3. Run the following command on the ClickHouse client to export the DDL statements that are used to create a table 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.
    Field 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 The mode used by the ClickHouse database. 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 use the SELECT * FROM statement to query system tables in the ClickHouse database, you must specify engine='Distributed' in the statement.
    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 statements used to create the source ClickHouse table. To create the destination Hologres table, the statements must be converted into DDL statements that use the PostgreSQL syntax.
    engine_full The details of the engine, which can be ignored.
    partition_key The partition key column of the ClickHouse database, which corresponds to the partition key column of the Hologres table. For example, if the partition_key parameter of the ClickHouse database is set to col1, you must append the partition by list (col1); statement to the statements that are used to create the Hologres table.
    sorting_key The sort key of the ClickHouse database, which corresponds to a segment key or a clustering key of the Hologres table.
    primary_key The primary key of the ClickHouse database, which 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, which can be ignored.
  4. Convert the DDL statements that are used to create the source ClickHouse table into the corresponding 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 of this topic. Examples:
    • Convert the DDL statements that are used to create the lineitem table in the ClickHouse cluster to the DDL statements that are used to create a corresponding Hologres table.
      • The following sample code shows the DDL statements 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 lineitem 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 corresponding Hologres table.
      • The following sample code shows the DDL statements 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 customer 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. Run the following command 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

You can use one of the following methods to migrate data from the ClickHouse cluster to the Hologres instance:
  • Export the data to be migrated from the ClickHouse cluster as a file. Then, use the COPY command for JDBC or PostgreSQL to import the file to the Hologres instance. This method is recommended.
  • Use Flink or Spark jobs to read data from the ClickHouse cluster and write the data to the Hologres instance. For more information, see Write data from Apache Spark to Hologres.
  • Use the Data Integration service of DataWorks or DataX to read data from the ClickHouse cluster and write the data to the Hologres instance. For more information, see Overview.

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

  1. Run the following command 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 in the command.
    Parameter Description
    host The endpoint of the ClickHouse cluster.
    port The port number of the ClickHouse cluster.
    username The username that is used to log on to the ClickHouse cluster. The specified 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 that is used 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 to be migrated from the ClickHouse cluster.
  2. Run the following command 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 in the command.
    Parameter Description
    username The username of the account used to log on to the Hologres instance. To execute DDL statements as well as specific DML statements, the specified user must have permissions, including read, write, and setting permissions. The username is usually the AccessKey ID of your Alibaba Cloud account. You can move the pointer over the profile image in the upper-right corner of the Hologres console and select AccessKey Management to obtain the AccessKey ID.
    password The password of the account used 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 image 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 the endpoint on the Configurations tab of the instance details page. To go to the Configurations tab, log on to the Hologres console. In the left-side navigation pane, click Instances. On the Instances page, click the name of the Hologres instance.

    port

    The port number of the Hologres instance.

    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 in the Hologres instance to verify whether the import is successful.

Convert data query statements

The data query statements in Hologres use the PostgreSQL syntax, whereas those in ClickHouse use the 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 have differences in details. Therefore, you must convert data query statements, especially for functions such as scalar functions and window functions in the data query statements.

The SQL syntax of ClickHouse and that of Hologres have the following differences:
  • The names of columns in the SQL syntax of ClickHouse are enclosed by single quotation marks (''), whereas those in the SQL syntax of Hologres are enclosed by double quotation marks ("").
  • ClickHouse uses the SELECT X FROM <database_name>.<table_name> statement to query data, whereas Hologres uses the SELECT X FROM <schema_name>.<table_name> statement to query data.
  • Expressions in data query statements differ for ClickHouse and Hologres, particularly with respect to functions. The following table describes the mappings between specific functions in ClickHouse and Hologres. Those functions shared by ClickHouse and Hologres are not described.
    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)
You can use one of the following methods to convert data query statements:
  • Use regular expressions

    You can use regular expressions to convert specific ClickHouse SQL syntax with fixed patterns, such as function names, punctuation marks, and expressions, into the corresponding Hologres SQL syntax. For example, you can convert single quotation marks ('') in the ClickHouse SQL syntax to double quotation marks ("") in the Hologres SQL syntax.

  • Use the ClickHouse extension

    Hologres is embedded with the ClickHouse extension and is compatible with specific ClickHouse functions, such as the toUInt32() function. These functions do not need to be converted before they are used in Hologres.

The following examples show how to convert specific TPC-H query statements that use the ClickHouse SQL syntax to those that use the Hologres SQL syntax:
  • Example 1
    • The following sample code shows a TPC-H query statement that is used to query data in a ClickHouse cluster:
      -- 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;
    • The following sample code shows the converted TPC-H query statement that is used to query data in a Hologres instance:
      -- 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;
  • Example 2
    • The following sample code shows a TPC-H query statement that is used to query data in a ClickHouse cluster:
      -- 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;
    • The following sample code shows the converted TPC-H query statement that is used to query data on a Hologres instance:
      -- 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;
  • Example 3
    • The following sample code shows a TPC-H query statement that is used to query data in a ClickHouse cluster:
      -- 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;
    • The following sample code shows the converted TPC-H query statement that is used to query data on a Hologres instance:
      -- 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 extension

Hologres is embedded with the ClickHouse extension and is compatible with specific ClickHouse functions. The following table describes the ClickHouse functions with which Hologres is compatible.
Function Description Return type
toString(anyelement) Converts a type to the TEXT type. TEXT
toInt64(anyelement) Converts a type to the BIGINT type. BIGINT
toInt32(anyelement) Converts a type to the INT type. INT
toDate(text) Converts the TEXT type to the DATE type. DATE
toFloat64(anyelement) Converts a type to the DOUBLE type. INT
  • Use method
    Extension functions are stored in the ClickHouse extension. To use extension functions, you must create an extension first. You can execute the following statements to use an extension function.
    Note Extensions can be created only by a superuser.
    -- Create an extension.
    CREATE  extension clickhouse; 
    -- Use a function.
    SELECT <function> from tablename; 
  • Example
    CREATE EXTENSION clickhouse;-- Create an extension. If an extension is available, skip this step.
    
    CREATE TABLE public.tb1 (
        id          bigint NOT NULL,
        data_date   text
        ,PRIMARY KEY (id)
    );
    
    INSERT INTO public.tb1 VALUES (1234,'20190102');
    
    SELECT toString(id) from public.tb1;
     tostring
    ----------
     1234
    (1 row)
    
    SELECT toDate(data_date) from public.tb1;
       todate
    ------------
     2019-01-02
    (1 row)