实时数仓Hologres：Hologres vs Clickhouse性能对比参考测试

测试环境介绍

为了消除网络带宽的影响，本测试使用同一台ECS向Hologres和Clickhouse发送查询请求且使用VPC连接，其中Hologres测试关闭了result cache。具体环境信息如下。

• Clickhouse测试环境

  配置项	详情配置信息
  机器	1台阿里云ECS主机
  CPU	Intel Xeon(Ice Lake) Platinum 8369B 64 vCore
  内存	256 GiB
  内网带宽	32 Gbps
  磁盘	ESSD高效云盘200 GB PL1 单盘IOPS上限5万
  操作系统	CentOS 8.4 64位
  硬件费用（不含公网带宽）	1370.04 美元/月
  公网IPv4带宽	200 Mbps
  网络费用	2323.85 美元/月
  Clickhouse版本	21.8.3.44

• Hologres测试环境

  配置项	具体配置信息
  计算资源	CPU：64 Core，内存：256 GB
  存储资源	200 GB（逻辑存储）
  公网带宽	大于5 Gbps
  总费用	1653.73 美元/月
  Hologres版本	0.10.33

测试数据

相关SQL命令

• Clickhouse命令

  所使用的DDL与查询SQL与Clickhouse官网测试方式一致，详情请参见Clickhouse官方链接。

• Hologres命令
  • DDL

    DROP TABLE IF EXISTS lineorder_flat;
    
    BEGIN;
    CREATE TABLE IF NOT EXISTS lineorder_flat (
      lo_orderdate     date NOT NULL ,
      lo_orderkey      int NOT NULL ,
      lo_linenumber    int NOT NULL ,
      lo_custkey       int NOT NULL ,
      lo_partkey       int NOT NULL ,
      lo_suppkey       int NOT NULL ,
      lo_orderpriority text NOT NULL ,
      lo_shippriority  int NOT NULL ,
      lo_quantity      int NOT NULL ,
      lo_extendedprice int NOT NULL ,
      lo_ordtotalprice int NOT NULL ,
      lo_discount      int NOT NULL ,
      lo_revenue       int NOT NULL ,
      lo_supplycost    int NOT NULL ,
      lo_tax           int NOT NULL ,
      lo_commitdate    date NOT NULL ,
      lo_shipmode      text NOT NULL ,
      c_name           text NOT NULL ,
      c_address text NOT NULL ,
      c_city text NOT NULL ,
      c_nation text NOT NULL ,
      c_region text NOT NULL ,
      c_phone text NOT NULL ,
      c_mktsegment text NOT NULL ,
      s_region text NOT NULL ,
      s_nation text NOT NULL ,
      s_city text NOT NULL ,
      s_name text NOT NULL ,
      s_address text NOT NULL ,
      s_phone text NOT NULL ,
      p_name text NOT NULL ,
      p_mfgr text NOT NULL ,
      p_category text NOT NULL ,
      p_brand text NOT NULL ,
      p_color text NOT NULL ,
      p_type text NOT NULL ,
      p_size int NOT NULL ,
      p_container text NOT NULL,
      PRIMARY KEY (lo_orderkey,lo_linenumber)
    );
    CALL set_table_property('lineorder_flat', 'distribution_key', 'lo_orderkey');
    CALL set_table_property('lineorder_flat', 'segment_key', 'lo_orderdate');
    CALL set_table_property('lineorder_flat', 'clustering_key', 'lo_orderdate');
    CALL set_table_property('lineorder_flat', 'bitmap_columns', 'p_category,s_region,c_region,c_nation,s_nation,c_city,s_city,p_mfgr,p_brand');
    CALL set_table_property('lineorder_flat', 'time_to_live_in_seconds', '31536000');
    COMMIT;

  • 查询SQL
    • Q1.1

      SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE LO_ORDERDATE >= DATE '1993-01-01' 
      AND LO_ORDERDATE <= DATE '1993-12-31' 
      AND LO_DISCOUNT BETWEEN 1 AND 3 
      AND LO_QUANTITY < 25;

    • Q1.2

      SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
      FROM LINEORDER_FLAT  
      WHERE LO_ORDERDATE >= DATE '1994-01-01' 
      AND LO_ORDERDATE <= DATE '1994-01-31' 
      AND LO_DISCOUNT BETWEEN 4 AND 6 
      AND LO_QUANTITY BETWEEN 26 AND 35;

    • Q1.3

      SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE EXTRACT(WEEK FROM LO_ORDERDATE ) = 6 
      AND LO_ORDERDATE >= DATE '1994-01-01' 
      AND LO_ORDERDATE <= DATE '1994-12-31' 
      AND LO_DISCOUNT BETWEEN 5 AND 7 
      AND LO_QUANTITY BETWEEN 26 AND 35;

    • Q2.1

      SELECT SUM(LO_REVENUE),
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR,
      P_BRAND 
      FROM LINEORDER_FLAT 
      WHERE P_CATEGORY = 'MFGR#12' 
      AND S_REGION = 'AMERICA' 
      GROUP BY YEAR, P_BRAND 
      ORDER BY YEAR, P_BRAND;

    • Q2.2

      SELECT SUM(LO_REVENUE),
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR,
      P_BRAND 
      FROM LINEORDER_FLAT 
      WHERE P_BRAND BETWEEN 'MFGR#2221' AND 'MFGR#2228' 
      AND S_REGION = 'ASIA' 
      GROUP BY YEAR, P_BRAND 
      ORDER BY YEAR, P_BRAND;

    • Q2.3

      SELECT SUM(LO_REVENUE),
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      P_BRAND 
      FROM LINEORDER_FLAT 
      WHERE P_BRAND = 'MFGR#2239' 
      AND S_REGION = 'EUROPE' 
      GROUP BY YEAR, P_BRAND 
      ORDER BY YEAR, P_BRAND;

    • Q3.1

      SELECT C_NATION,
      S_NATION, 
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      SUM(LO_REVENUE) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE C_REGION = 'ASIA' 
      AND S_REGION = 'ASIA' 
      AND LO_ORDERDATE  >= DATE '1992-01-01' 
      AND LO_ORDERDATE  <= DATE '1997-12-31' 
      GROUP BY C_NATION,S_NATION,YEAR 
      ORDER BY  YEAR ASC,REVENUE DESC;

    • Q3.2

      SELECT C_CITY, 
      S_CITY, 
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      SUM(LO_REVENUE) AS REVENUE
      FROM LINEORDER_FLAT 
      WHERE C_NATION = 'UNITED STATES' 
      AND S_NATION = 'UNITED STATES' 
      AND LO_ORDERDATE >= DATE '1992-01-01' 
      AND LO_ORDERDATE <= DATE '1997-12-31' 
      GROUP BY C_CITY, S_CITY, YEAR 
      ORDER BY YEAR ASC, REVENUE DESC;

    • Q3.3

      SELECT C_CITY,
      S_CITY, 
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      SUM(LO_REVENUE) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE C_CITY IN ( 'UNITED KI1' ,'UNITED KI5') 
      AND S_CITY IN ( 'UNITED KI1' ,'UNITED KI5') 
      AND LO_ORDERDATE >= DATE '1992-01-01' 
      AND LO_ORDERDATE <= DATE '1997-12-31' 
      GROUP BY C_CITY, S_CITY, YEAR 
      ORDER BY YEAR ASC, REVENUE DESC;

    • Q3.4

      SELECT C_CITY,
      S_CITY, 
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      SUM(LO_REVENUE) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE C_CITY IN ('UNITED KI1', 'UNITED KI5') 
      AND S_CITY IN ( 'UNITED KI1',  'UNITED KI5') 
      AND LO_ORDERDATE >= DATE '1997-12-01' 
      AND LO_ORDERDATE <= DATE '1997-12-31' 
      GROUP BY C_CITY, S_CITY, YEAR 
      ORDER BY YEAR ASC, REVENUE DESC;

    • Q4.1

      SELECT EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      C_NATION,  
      SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
      FROM LINEORDER_FLAT 
      WHERE C_REGION = 'AMERICA' 
      AND S_REGION = 'AMERICA' 
      AND P_MFGR IN ( 'MFGR#1' , 'MFGR#2') 
      GROUP BY YEAR, C_NATION 
      ORDER BY YEAR ASC, C_NATION ASC;

    • Q4.2

      SELECT EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      S_NATION, 
      P_CATEGORY, 
      SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
      FROM LINEORDER_FLAT 
      WHERE C_REGION = 'AMERICA' 
      AND S_REGION = 'AMERICA' 
      AND LO_ORDERDATE >= DATE '1997-01-01' 
      AND LO_ORDERDATE <= DATE '1998-12-31' 
      AND  P_MFGR IN ( 'MFGR#1' , 'MFGR#2') 
      GROUP BY YEAR, S_NATION, P_CATEGORY 
      ORDER BY  YEAR ASC, S_NATION ASC, P_CATEGORY ASC;

    • Q4.3

      SELECT EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      S_CITY, 
      P_BRAND, 
      SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
      FROM LINEORDER_FLAT 
      WHERE S_NATION = 'UNITED STATES' 
      AND LO_ORDERDATE >= DATE '1997-01-01' 
      AND LO_ORDERDATE <= DATE '1998-12-31' 
      AND P_CATEGORY = 'MFGR#14' 
      GROUP BY  YEAR,  S_CITY, P_BRAND 
      ORDER BY YEAR ASC,  S_CITY ASC,  P_BRAND ASC;

测试结果