前言

本文旨在介绍如何利用 ClickHouse官网 Star Schema数据集对天翼云数据仓库 ClickHouse进行性能测试，并提供数据导入及性能测试的参考方案。

准备工作

购买实例

请先购买天翼云数据仓库 ClickHouse 实例。您可以选择计算增强型或内存优化型。

准备测试机器

准备一台能够访问天翼云数据仓库 ClickHouse 服务的 Linux 机器，并在该机器上安装 ClickHouse 客户端工具。测试机器至少需要 1.5TB 的存储空间，并确保能够顺利访问天翼云数据仓库 ClickHouse 服务。有关 ClickHouse 客户端工具的安装，请参考相应的安装文档。

在购买实例后，您需要在控制台中调整以下参数：

具体参数的调整请参考相关配置文档。注意：调整完成后，请重启集群。

测试步骤

确认软件版本

使用 ClickHouse 客户端访问天翼云数据仓库 ClickHouse 服务，以查看软件版本：

clickhouse client --host $HOST --port $PORT -q "select version()"

请确保软件版本高于 22.8。

准备数据生成工具

git clone git@github.com:vadimtk/ssb-dbgen.git
cd ssb-dbgen
make

生成测试数据

使用 ssb-dbgen 工具生成测试数据。可以选择两种规模的数据，参数 -s 100 生成约 6 亿行数据，-s 1000 生成约 60 亿行数据。建议使用：

# 生成约60亿行数据
./dbgen -s 1000 -T c  # 生成客户表数据
./dbgen -s 1000 -T l  # 生成订单行数据
./dbgen -s 1000 -T p  # 生成产品表数据
./dbgen -s 1000 -T s  # 生成供应商表数据

创建数据库表

在天翼云数据仓库 ClickHouse 控制台上获取服务入口信息，记录访问 IP 和服务端口为 HOST 和 PORT。使用 ClickHouse 客户端工具连接天翼云数据仓库 ClickHouse 服务，执行如下 SQL 创建所需的表：

CREATE TABLE customer
(
        C_CUSTKEY       UInt32,
        C_NAME          String,
        C_ADDRESS       String,
        C_CITY          LowCardinality(String),
        C_NATION        LowCardinality(String),
        C_REGION        LowCardinality(String),
        C_PHONE         String,
        C_MKTSEGMENT    LowCardinality(String)
)
ENGINE = MergeTree ORDER BY (C_CUSTKEY);

CREATE TABLE lineorder
(
    LO_ORDERKEY             UInt32,
    LO_LINENUMBER           UInt8,
    LO_CUSTKEY              UInt32,
    LO_PARTKEY              UInt32,
    LO_SUPPKEY              UInt32,
    LO_ORDERDATE            Date,
    LO_ORDERPRIORITY        LowCardinality(String),
    LO_SHIPPRIORITY         UInt8,
    LO_QUANTITY             UInt8,
    LO_EXTENDEDPRICE        UInt32,
    LO_ORDTOTALPRICE        UInt32,
    LO_DISCOUNT             UInt8,
    LO_REVENUE              UInt32,
    LO_SUPPLYCOST           UInt32,
    LO_TAX                  UInt8,
    LO_COMMITDATE           Date,
    LO_SHIPMODE             LowCardinality(String)
)
ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);

CREATE TABLE part
(
        P_PARTKEY       UInt32,
        P_NAME          String,
        P_MFGR          LowCardinality(String),
        P_CATEGORY      LowCardinality(String),
        P_BRAND         LowCardinality(String),
        P_COLOR         LowCardinality(String),
        P_TYPE          LowCardinality(String),
        P_SIZE          UInt8,
        P_CONTAINER     LowCardinality(String)
)
ENGINE = MergeTree ORDER BY P_PARTKEY;

CREATE TABLE supplier
(
        S_SUPPKEY       UInt32,
        S_NAME          String,
        S_ADDRESS       String,
        S_CITY          LowCardinality(String),
        S_NATION        LowCardinality(String),
        S_REGION        LowCardinality(String),
        S_PHONE         String
)
ENGINE = MergeTree ORDER BY S_SUPPKEY;

导入测试数据

进行数据导入，首先导入基础表数据：

clickhouse client --host $HOST --port $PORT --query "INSERT INTO customer FORMAT CSV" < customer.tbl
clickhouse client --host $HOST --port $PORT --query "INSERT INTO part FORMAT CSV" < part.tbl
clickhouse client --host $HOST --port $PORT --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl
clickhouse client --host $HOST --port $PORT --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl

然后根据基础表数据生成宽表数据。注意您已调整了 max_memory_usage 和 max_insert_threads 参数。

CREATE TABLE lineorder_flat
ENGINE = MergeTree ORDER BY (LO_ORDERDATE, LO_ORDERKEY)
AS SELECT
    l.LO_ORDERKEY AS LO_ORDERKEY,
    l.LO_LINENUMBER AS LO_LINENUMBER,
    l.LO_CUSTKEY AS LO_CUSTKEY,
    l.LO_PARTKEY AS LO_PARTKEY,
    l.LO_SUPPKEY AS LO_SUPPKEY,
    l.LO_ORDERDATE AS LO_ORDERDATE,
    l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
    l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
    l.LO_QUANTITY AS LO_QUANTITY,
    l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
    l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
    l.LO_DISCOUNT AS LO_DISCOUNT,
    l.LO_REVENUE AS LO_REVENUE,
    l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
    l.LO_TAX AS LO_TAX,
    l.LO_COMMITDATE AS LO_COMMITDATE,
    l.LO_SHIPMODE AS LO_SHIPMODE,
    c.C_NAME AS C_NAME,
    c.C_ADDRESS AS C_ADDRESS,
    c.C_CITY AS C_CITY,
    c.C_NATION AS C_NATION,
    c.C_REGION AS C_REGION,
    c.C_PHONE AS C_PHONE,
    c.C_MKTSEGMENT AS C_MKTSEGMENT,
    s.S_NAME AS S_NAME,
    s.S_ADDRESS AS S_ADDRESS,
    s.S_CITY AS S_CITY,
    s.S_NATION AS S_NATION,
    s.S_REGION AS S_REGION,
    s.S_PHONE AS S_PHONE,
    p.P_NAME AS P_NAME,
    p.P_MFGR AS P_MFGR,
    p.P_CATEGORY AS P_CATEGORY,
    p.P_BRAND AS P_BRAND,
    p.P_COLOR AS P_COLOR,
    p.P_TYPE AS P_TYPE,
    p.P_SIZE AS P_SIZE,
    p.P_CONTAINER AS P_CONTAINER
FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

优化查询（可选）

天翼云数据仓库 ClickHouse 提供预计算能力以加快执行速度。可以通过 PROJECTION 来加速查询。执行以下 SQL 以添加不同的投影：

ALTER TABLE lineorder_flat ADD PROJECTION p1 (
    SELECT 
        toYear(LO_ORDERDATE) AS year,
        sum(LO_REVENUE)
    GROUP BY 
        year,
        P_BRAND,
        P_CATEGORY,
        S_REGION
);

-- 继续添加其他投影...

执行完投影后，需要对现有数据进行处理，使投影在存量数据上生效：

ALTER TABLE lineorder_flat MATERIALIZE PROJECTION p1;
-- 继续对其他投影进行物化...

注意： 该步骤是可选的，使用优化后，性能提升非常明显。

执行测试 SQL 并统计执行时间

在测试阶段，您可以执行以下查询，并记录执行时间：

• Q1.1

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;

• Q2.1

SELECT
    sum(LO_REVENUE),
    toYear(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;

• Q3.1

SELECT
    C_NATION,
    S_NATION,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
GROUP BY
    C_NATION,
    S_NATION,
    year
ORDER BY
    year ASC,
    revenue DESC;

总结

性能测试是天翼云数据仓库 ClickHouse 业务接入前的重要步骤，对于性能和资源的评估具有重要意义。进行性能对比测试时，请注意以下几点：

1. 调整天翼云数据仓库 ClickHouse 的关键参数，以最大限度发挥性能。
2. 确保资源的一致性，例如，天翼云数据仓库 ClickHouse 在某些情况下仅使用一半的节点进行计算，可能导致性能数据不占优势。

通过以上步骤和注意事项，您可以有效地进行性能测试并获得优化的结果。