前言

本文旨在介紹如何利用 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 在某些情況下僅使用一半的節點進行計算，可能導致性能數據不占優勢。

通過以上步驟和注意事項，您可以有效地進行性能測試并獲得優化的結果。