clickhouse性能测试并数据集下载（Star Schema Benchmark）附clickhouse官网

女爷i 2022-11-18 04:51 578阅读 0赞

**目录**

目标：clickhouse性能测试，使用官网数据集。

1.下载SSBM工具

2.生成数据

3.建表

4.导入数据

5.执行测试，查看结果。

6.clickhouse数据集官网

--------------------

# 目标：clickhouse性能测试，使用官网数据集。 #

## 1.下载SSBM工具 ##

[root@node01]# git clone https://github.com/vadimtk/ssb-dbgen.git
    [root@node01]# cd ssb-dbgen
    [root@node01]# make

## 2.生成数据 ##

[root@node01]# ./dbgen -s 100 -T c
    [root@node01]# ./dbgen -s 100 -T p
    [root@node01]# ./dbgen -s 100 -T s
    [root@node01]# ./dbgen -s 100 -T l
    [root@node01]# ./dbgen -s 100 -T d

查看下数据

ll -Sh

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTExMTAzMDE_size_16_color_FFFFFF_t_70][]

ls -l *tbl

![20210412171629750.png][]

## 3.建表 ##

CREATE TABLE default.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 default.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 default.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 default.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;

## 4.导入数据 ##

[root@node01]# clickhouse-client --query "INSERT INTO customer FORMAT CSV" < customer.tbl
 [root@node01]# clickhouse-client --query "INSERT INTO part FORMAT CSV" < part.tbl
 [root@node01]# clickhouse-client --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl
 [root@node01]# clickhouse-client --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl

注意：如果此处报错，检查clickhouse的配置（端口是否占用，是否设置用户和密码）

比如：

clickhouse-client -h 10.30.202.21 --port 8000 -u root --password 123456 --database="default" --query "INSERT INTO default.customer FORMAT CSV" < customer.tbl

创建第五张表

客户端执行

SET max_memory_usage = 20000000000, allow_experimental_multiple_joins_emulation = 1;

建表

CREATE TABLE default.lineorder_flat
    ENGINE = MergeTree
    PARTITION BY toYear(l.LO_ORDERDATE)
    ORDER BY (l.LO_ORDERDATE, l.LO_ORDERKEY) AS
    SELECT l.*, c.*, s.*, p.*
    FROM lineorder l
     ANY INNER JOIN customer c ON (c.C_CUSTKEY = l.LO_CUSTKEY)
     ANY INNER JOIN supplier s ON (s.S_SUPPKEY = l.LO_SUPPKEY)
     ANY INNER JOIN part p ON  (p.P_PARTKEY = l.LO_PARTKEY);

注意：官网语句会有报错，请适当修改！

删除三列

ALTER TABLE lineorder_flat DROP COLUMN c.C_CUSTKEY, DROP COLUMN s.S_SUPPKEY, DROP COLUMN p.P_PARTKEY;

注意：官网语句会有报错，请适当修改！

最终表信息如下：

![20210413093600969.png][]

## 5.执行测试，查看结果。 ##

<table> 
 <tbody> 
 <tr> 
 <td style="text-align:center;vertical-align:top;width:48pt;">编号</td> 
 <td style="text-align:center;vertical-align:top;width:250pt;">查询语句SQL</td> 
 <td style="text-align:center;vertical-align:top;width:33pt;">耗时(sec)</td> 
 <td style="text-align:center;vertical-align:top;width:85pt;">扫描行数(thousand rows)</td> 
 <td style="text-align:center;vertical-align:top;width:48pt;">返回行数(rows)</td> 
 <td style="text-align:center;vertical-align:top;width:87pt;">每秒处理行数（million rows）</td> 
 <td style="text-align:center;vertical-align:top;width:72pt;">每秒处理大小（MB）</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q1.1</td> 
 <td style="vertical-align:top;width:250pt;">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 &lt; 25;</td> 
 <td style="text-align:right;vertical-align:middle;">0.004</td> 
 <td style="text-align:right;vertical-align:middle;">12.03</td> 
 <td style="text-align:right;vertical-align:middle;">1</td> 
 <td style="text-align:right;vertical-align:middle;">2.74</td> 
 <td style="text-align:right;vertical-align:middle;">21.94</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q1.2</td> 
 <td style="vertical-align:top;width:250pt;">SELECT SUM(l.LO_EXTENDEDPRICE * l.LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYYYYMM(l.LO_ORDERDATE) = 199401 AND l.LO_DISCOUNT BETWEEN 4 AND 6 AND l.LO_QUANTITY BETWEEN 26 AND 35;</td> 
 <td style="text-align:right;vertical-align:middle;">0.005</td> 
 <td style="text-align:right;vertical-align:middle;">30.05</td> 
 <td style="text-align:right;vertical-align:middle;">1</td> 
 <td style="text-align:right;vertical-align:middle;">6.1</td> 
 <td style="text-align:right;vertical-align:middle;">48.79</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q1.3</td> 
 <td style="vertical-align:top;width:250pt;">SELECT SUM(l.LO_EXTENDEDPRICE * l.LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toISOWeek(l.LO_ORDERDATE) = 6 AND toYear(l.LO_ORDERDATE) = 1994 AND l.LO_DISCOUNT BETWEEN 5 AND 7 AND l.LO_QUANTITY BETWEEN 26 AND 35;</td> 
 <td style="text-align:right;vertical-align:middle;">0.008</td> 
 <td style="text-align:right;vertical-align:middle;">13.67</td> 
 <td style="text-align:right;vertical-align:middle;">1</td> 
 <td style="text-align:right;vertical-align:middle;">1.64</td> 
 <td style="text-align:right;vertical-align:middle;">7.21</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q2.1</td> 
 <td style="vertical-align:top;width:250pt;">SELECT SUM(l.LO_REVENUE), toYear(l.LO_ORDERDATE) AS year, p.P_BRAND FROM lineorder_flat WHERE p.P_CATEGORY = 'MFGR#12' AND s.S_REGION = 'AMERICA' GROUP BY year, p.P_BRAND ORDER BY year, p.P_BRAND;</td> 
 <td style="text-align:right;vertical-align:middle;">0.008</td> 
 <td style="text-align:right;vertical-align:middle;">181.04</td> 
 <td style="text-align:right;vertical-align:middle;">237</td> 
 <td style="text-align:right;vertical-align:middle;">21.41</td> 
 <td style="text-align:right;vertical-align:middle;">227.07</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q2.2</td> 
 <td style="vertical-align:top;width:250pt;">SELECT SUM(l.LO_REVENUE), toYear(l.LO_ORDERDATE) AS year, p.P_BRAND FROM lineorder_flat WHERE p.P_BRAND BETWEEN 'MFGR#2221' AND 'MFGR#2228' AND s.S_REGION = 'ASIA' GROUP BY year, p.P_BRAND ORDER BY year, p.P_BRAND;</td> 
 <td style="text-align:right;vertical-align:middle;">0.007</td> 
 <td style="text-align:right;vertical-align:middle;">181.04</td> 
 <td style="text-align:right;vertical-align:middle;">48</td> 
 <td style="text-align:right;vertical-align:middle;">26.55</td> 
 <td style="text-align:right;vertical-align:middle;">254.67</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q2.3</td> 
 <td style="vertical-align:top;width:250pt;">SELECT SUM(l.LO_REVENUE), toYear(l.LO_ORDERDATE) AS year, p.P_BRAND FROM lineorder_flat WHERE p.P_BRAND = 'MFGR#2239' AND s.S_REGION = 'EUROPE' GROUP BY year, p.P_BRAND ORDER BY year, p.P_BRAND;</td> 
 <td style="text-align:right;vertical-align:middle;">0.008</td> 
 <td style="text-align:right;vertical-align:middle;">181.04</td> 
 <td style="text-align:right;vertical-align:middle;">6</td> 
 <td style="text-align:right;vertical-align:middle;">23.66</td> 
 <td style="text-align:right;vertical-align:middle;">227</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q3.1</td> 
 <td style="vertical-align:top;width:250pt;">SELECT c.C_NATION, s.S_NATION, toYear(l.LO_ORDERDATE) AS year , SUM(l.LO_REVENUE) AS revenue FROM lineorder_flat WHERE c.C_REGION = 'ASIA' AND s.S_REGION = 'ASIA' AND year &gt;= 1992 AND year &lt;= 1997 GROUP BY c.C_NATION, s.S_NATION, year ORDER BY year ASC, revenue DESC;</td> 
 <td style="text-align:right;vertical-align:middle;">0.012</td> 
 <td style="text-align:right;vertical-align:middle;">181.04</td> 
 <td style="text-align:right;vertical-align:middle;">150</td> 
 <td style="text-align:right;vertical-align:middle;">15.57</td> 
 <td style="text-align:right;vertical-align:middle;">156.24</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q3.2</td> 
 <td style="vertical-align:top;width:250pt;">SELECT c.C_CITY, s.S_CITY, toYear(l.LO_ORDERDATE) AS year , SUM(l.LO_REVENUE) AS revenue FROM lineorder_flat WHERE c.C_NATION = 'UNITED STATES' AND s.S_NATION = 'UNITED STATES' AND year &gt;= 1992 AND year &lt;= 1997 GROUP BY c.C_CITY, s.S_CITY, year ORDER BY year ASC, revenue DESC;</td> 
 <td style="text-align:right;vertical-align:middle;">0.011</td> 
 <td style="text-align:right;vertical-align:middle;">181.04</td> 
 <td style="text-align:right;vertical-align:middle;">222</td> 
 <td style="text-align:right;vertical-align:middle;">16.13</td> 
 <td style="text-align:right;vertical-align:middle;">166.84</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q3.3</td> 
 <td style="vertical-align:top;width:250pt;">SELECT c.C_CITY, s.S_CITY, toYear(l.LO_ORDERDATE) AS year , SUM(l.LO_REVENUE) AS revenue FROM lineorder_flat WHERE (c.C_CITY = 'UNITED KI1' OR c.C_CITY = 'UNITED KI5') AND (s.S_CITY = 'UNITED KI1' OR s.S_CITY = 'UNITED KI5') AND year &gt;= 1992 AND year &lt;= 1997 GROUP BY c.C_CITY, s.S_CITY, year ORDER BY year ASC, revenue DESC;</td> 
 <td style="text-align:right;vertical-align:middle;">0.009</td> 
 <td style="text-align:right;vertical-align:middle;">181.04</td> 
 <td style="text-align:right;vertical-align:middle;">9</td> 
 <td style="text-align:right;vertical-align:middle;">21.04</td> 
 <td style="text-align:right;vertical-align:middle;">174.94</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q3.4</td> 
 <td style="vertical-align:top;width:250pt;">SELECT c.C_CITY, s.S_CITY, toYear(l.LO_ORDERDATE) AS year , SUM(l.LO_REVENUE) AS revenue FROM lineorder_flat WHERE (c.C_CITY = 'UNITED KI1' OR c.C_CITY = 'UNITED KI5') AND (s.S_CITY = 'UNITED KI1' OR s.S_CITY = 'UNITED KI5') AND toYYYYMM(l.LO_ORDERDATE) = '199712' GROUP BY c.C_CITY, s.S_CITY, year ORDER BY year ASC, revenue DESC;</td> 
 <td style="text-align:right;vertical-align:middle;">0.004</td> 
 <td style="text-align:right;vertical-align:middle;">&nbsp;</td> 
 <td style="text-align:right;vertical-align:middle;">0</td> 
 <td style="text-align:right;vertical-align:middle;">&nbsp;</td> 
 <td style="text-align:right;vertical-align:middle;">&nbsp;</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q4.1</td> 
 <td style="vertical-align:top;width:250pt;">SELECT toYear(l.LO_ORDERDATE) AS year, c.C_NATION , SUM(l.LO_REVENUE - l.LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE c.C_REGION = 'AMERICA' AND s.S_REGION = 'AMERICA' AND (p.P_MFGR = 'MFGR#1' OR p.P_MFGR = 'MFGR#2') GROUP BY year, c.C_NATION ORDER BY year, c.C_NATION;</td> 
 <td style="text-align:right;vertical-align:middle;">0.009</td> 
 <td style="text-align:right;vertical-align:middle;">181.04</td> 
 <td style="text-align:right;vertical-align:middle;">&nbsp;</td> 
 <td style="text-align:right;vertical-align:middle;">20.8</td> 
 <td style="text-align:right;vertical-align:middle;">291.63</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q4.2</td> 
 <td style="vertical-align:top;width:250pt;">SELECT toYear(l.LO_ORDERDATE) AS year, s.S_NATION, p.P_CATEGORY , SUM(l.LO_REVENUE - l.LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE c.C_REGION = 'AMERICA' AND s.S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (p.P_MFGR = 'MFGR#1' OR p.P_MFGR = 'MFGR#2') GROUP BY year, s.S_NATION, p.P_CATEGORY ORDER BY year, s.S_NATION, p.P_CATEGORY;</td> 
 <td style="text-align:right;vertical-align:middle;">0.008</td> 
 <td style="text-align:right;vertical-align:middle;">21.09</td> 
 <td style="text-align:right;vertical-align:middle;">50</td> 
 <td style="text-align:right;vertical-align:middle;">2.63</td> 
 <td style="text-align:right;vertical-align:middle;">39.6</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;">Q4.3</td> 
 <td style="vertical-align:top;width:250pt;">SELECT toYear(l.LO_ORDERDATE) AS year, s.S_CITY, p.P_BRAND , SUM(l.LO_REVENUE - l.LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE s.S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND p.P_CATEGORY = 'MFGR#14' GROUP BY year, s.S_CITY, p.P_BRAND ORDER BY year, s.S_CITY, p.P_BRAND;</td> 
 <td style="text-align:right;vertical-align:middle;">0.007</td> 
 <td style="text-align:right;vertical-align:middle;">21.09</td> 
 <td style="text-align:right;vertical-align:middle;">32</td> 
 <td style="text-align:right;vertical-align:middle;">3.14</td> 
 <td style="text-align:right;vertical-align:middle;">50.61</td> 
 </tr> 
 </tbody> 
</table>

## 6.clickhouse数据集官网 ##

[https://clickhouse.tech/docs/zh/getting-started/example-datasets/star-schema/\#star-schema-benchmark][https_clickhouse.tech_docs_zh_getting-started_example-datasets_star-schema_star-schema-benchmark]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTExMTAzMDE_size_16_color_FFFFFF_t_70]: /images/20221022/33cf00e909ea475a99d869e011fa6d22.png
[20210412171629750.png]: /images/20221022/fd39159a24a247feb9debf942670974c.png
[20210413093600969.png]: /images/20221022/b8a5c6588a894001a8d32f6380bb08d0.png
[https_clickhouse.tech_docs_zh_getting-started_example-datasets_star-schema_star-schema-benchmark]: https://clickhouse.tech/docs/zh/getting-started/example-datasets/star-schema/#star-schema-benchmark