文档中心

SSB数据集测试

最近更新时间：2023.09.04 11:36:36

首次发布时间：2023.09.04 11:36:36

您可以参考本指南，通过SSB数据集测试 ByteHouse 的性能。
先决条件：

拥有ByteHouse帐户，并创建数据库用于数据导入。可参考快速入门创建。
已安装ByteHouse CLI。可以参考此处进行安装和配置。

编译示例SSB数据集

通过以下命令生成约67 GB的SSB（星形模式基准）数据集，完成这一操作可能需要等待一些时间。

$ git clone https://github.com/ChrisZou/ssb-dbgen
$ cd ssb-dbgen
$ make
$ ./dbgen -s 100 -T c
$ ./dbgen -s 100 -T l
$ ./dbgen -s 100 -T p
$ ./dbgen -s 100 -T s

连接到 ByteHouse

通过 CLI 执行下面的命令，连接到 ByteHouse。
关于 ByteHouse CLI 的详细使用方法您可以参考 ByteHouse CLI 快速入门。

您可以参考获取集群信息页面获取集群的连接地址和端口（host:port）等信息。
您可参考获取 API Token 页面获取 API Key。
建议加入长查询的超时设置

bytehouse-cli --host {HOST} --secure true --port 19000 --send_timeout=3600 --receive_timeout=3600 --token {API KEY}

创建数据库和数据表

在 ByteHouse 中执行下面的命令，创建数据库和表。

CREATE DATABASE ssb;

CREATE TABLE ssb.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 = CnchMergeTree ORDER BY (C_CUSTKEY);

CREATE TABLE ssb.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 = CnchMergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);

CREATE TABLE ssb.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 = CnchMergeTree ORDER BY P_PARTKEY;

CREATE TABLE ssb.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 = CnchMergeTree ORDER BY S_SUPPKEY;

导入数据

参考下面的命令，通过 CLI 将创建的数据集导入四个表中。

cat customer.tbl | sed 's/,*\r*$//' | bytehouse-cli --host {HOST} --port 19000 --secure -w default_vw --database ssb -q  "INSERT INTO customer FORMAT CSV" --send_timeout=3600 --receive_timeout=3600 --token {API KEY}

请确保命令在ssb-dbgen目录下执行，并且已经替换了。替换host, API key以及相应的文件名和表名。

将星型模型转化成宽表

创建一个新表lineorder_flat

CREATE TABLE ssb.lineorder_flat
(
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),
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),
S_NAME String, 
S_ADDRESS String, 
S_CITY LowCardinality(String), 
S_NATION LowCardinality(String), 
S_REGION LowCardinality(String), 
S_PHONE String,
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 = CnchMergeTree 
ORDER BY (LO_ORDERDATE, LO_ORDERKEY)

登录到 ByteHouse，在计算组页面创建一个大小为“M”、名称为“vw_test”的计算组。

通过 CLI 将四个表转换为一个宽表。这可能需要长达40分钟的时间。

bytehouse-cli --host {HOST} --secure true --port 19000 --send_timeout=10000000 --receive_timeout=10000000 --token {API KEY}

在 CLI 上执行下面的SQL语句。

set warehouse ='vw_test';
INSERT INTO `ssb`.`lineorder_flat`
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 ssb.lineorder AS l
INNER JOIN ssb.customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN ssb.supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN ssb.part AS p ON p.P_PARTKEY = l.LO_PARTKEY 
SETTINGS send_timeout = 10000000, receive_timeout = 10000000, max_execution_time=10000000, exchange_timeout = 1000000, enable_optimizer=0;

执行查询

运行以下数据集查询命令。

Q1.1

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

Q1.2

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM ssb.lineorder_flat
WHERE toYYYYMM(LO_ORDERDATE) = 199401 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 ssb.lineorder_flat
WHERE toISOWeek(LO_ORDERDATE) = 6 AND toYear(LO_ORDERDATE) = 1994
AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;

Q2.1

SELECT
    sum(LO_REVENUE),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM ssb.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),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM ssb.lineorder_flat
WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;

Q2.3

SELECT
    sum(LO_REVENUE),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM ssb.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,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM ssb.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;

Q3.2

SELECT
    C_CITY,
    S_CITY,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM ssb.lineorder_flat
WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;

Q3.3

SELECT
    C_CITY,
    S_CITY,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM ssb.lineorder_flat
WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;

Q3.4

SELECT
    C_CITY,
    S_CITY,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM ssb.lineorder_flat
WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;

Q4.1

SELECT
    toYear(LO_ORDERDATE) AS year,
    C_NATION,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb.lineorder_flat
WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY
    year,
    C_NATION
ORDER BY
    year ASC,
    C_NATION ASC;

Q4.2

SELECT
    toYear(LO_ORDERDATE) AS year,
    S_NATION,
    P_CATEGORY,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb.lineorder_flat
WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY
    year,
    S_NATION,
    P_CATEGORY
ORDER BY
    year ASC,
    S_NATION ASC,
    P_CATEGORY ASC;

Q4.3

SELECT
    toYear(LO_ORDERDATE) AS year,
    S_CITY,
    P_BRAND,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb.lineorder_flat
WHERE S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
GROUP BY
    year,
    S_CITY,
    P_BRAND
ORDER BY
    year ASC,
    S_CITY ASC,
    P_BRAND ASC;

编译示例SSB数据集

连接到 ByteHouse

创建数据库和数据表

导入数据

将星型模型转化成宽表

执行查询

Q1.1

Q1.2

Q1.3

Q2.1

Q2.2

Q2.3

Q3.1

Q3.2

Q3.3

Q3.4

Q4.1

Q4.2

Q4.3

ByteHouse云数仓版

SSB数据集测试

编译示例SSB数据集

连接到 ByteHouse

创建数据库和数据表

导入数据

将星型模型转化成宽表

执行查询

Q1.1

Q1.2

Q1.3

Q2.1

Q2.2

Q2.3

Q3.1

Q3.2

Q3.3

Q3.4

Q4.1

Q4.2

Q4.3