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

• 拥有ByteHouse帐户，并创建数据库用于数据导入。可参考快速入门创建。
• 已安装ByteHouse CLI。 可以参考此处进行安装和配置。
• 本文中使用的SSB数据集工具是为x86或amd64架构构建的，如果使用其它架构可能需要自行编译此工具。

自助生成SSB数据集并且上传到TOS对象存储中，或者联系火山ByteHouse团队提供SSB数据集。

生成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

上传SSB数据集至TOS

1. 创建TOS桶：benchmarkdata-bytehouse 桶。
2. 上传测试集到TOS桶中。
   1. 下载tosutil工具。
   2. 利用tosutil上传到tos桶中。

提前配置 tosutil config 文件：配置文件说明--对象存储-火山引擎
AK/SK 获取地址：账号登录-火山引擎
桶region/endpoint获取地址：存储桶概览--对象存储-火山引擎

./tosutil cp ./customer.tbl tos://benchmarkdata-bytehouse/benchmarkdata/dataset/ssb_csv_100/customer.tbl

./tosutil cp ./lineorder.tbl tos://benchmarkdata-bytehouse/benchmarkdata/dataset/ssb_csv_100/lineorder.tbl

./tosutil cp ./part.tbl tos://benchmarkdata-bytehouse/benchmarkdata/dataset/ssb_csv_100/part.tbl

./tosutil cp ./supplier.tbl tos://benchmarkdata-bytehouse/benchmarkdata/dataset/ssb_csv_100/supplier.tbl

通过账号密码登录并访问ByteHouse控制台。

连接到 ByteHouse

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

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

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

设置计算组

在计算组页面创建一个大小为“M”、名称为"ssb_test”的计算组（或修改默认计算组位“M”型号）。

创建数据库与数据表

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

DROP DATABASE IF EXISTS ssb;
CREATE DATABASE ssb;
USE ssb;
 
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       FixedString(15),
    C_MKTSEGMENT  LowCardinality(String)
) ENGINE=CnchMergeTree() ORDER BY (C_CUSTKEY);

CREATE TABLE lineorder
(
    LO_ORDERKEY             UInt64,
    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(toString(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=CnchMergeTree() 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=CnchMergeTree() ORDER BY (S_SUPPKEY);

参考下面的命令，通过ByteHouse页面 将 TOS 中的数据集导入表中。

其中 s3_ak_id ='', s3_ak_secret = '', s3_endpoint = '' 的配置 请替换成您的AK/SK/endpoint信息。
AK/SK信息可以前往 账号登录-火山引擎获取。

USE ssb;

SET receive_timeout = 10800000, send_timeout = 10800000;  

INSERT INTO customer FORMAT CSV INFILE 's3://benchmarkdata-bytehouse/benchmarkdata/dataset/ssb_csv_100/customer.tbl' settings s3_ak_id ='', s3_ak_secret = '', s3_endpoint = '', s3_use_virtual_hosted_style=1;

INSERT INTO lineorder FORMAT CSV INFILE 's3://benchmarkdata-bytehouse/benchmarkdata/dataset/ssb_csv_100/lineorder.tbl' settings s3_ak_id ='', s3_ak_secret = '', s3_endpoint = '', s3_use_virtual_hosted_style=1;

INSERT INTO part FORMAT CSV INFILE 's3://benchmarkdata-bytehouse/benchmarkdata/dataset/ssb_csv_100/part.tbl' settings s3_ak_id ='', s3_ak_secret = '', s3_endpoint = '', s3_use_virtual_hosted_style=1;

INSERT INTO supplier FORMAT CSV INFILE 's3://benchmarkdata-bytehouse/benchmarkdata/dataset/ssb_csv_100/supplier.tbl' settings s3_ak_id ='', s3_ak_secret = '', s3_endpoint = '', s3_use_virtual_hosted_style=1;

在以上五张表数据导入完成后，可以通过以下命令将多张表转化为一张宽表。

SET receive_timeout = 10800000, send_timeout = 10800000;

CREATE TABLE lineorder_flat
ENGINE = CnchMergeTree
PARTITION BY toYear(LO_ORDERDATE)
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;

查询参数设置

在数据查询时，可以添加以下参数以提升性能。使用方式：即在SQL尾部加上该配置；其中{xxx} 表示需要根据使用的计算组情况进行替换的变量

推荐配置

SETTINGS enable_optimizer=1,enable_plan_cache=1,max_threads={CPU_CORES},
merge_tree_max_rows_to_use_cache=1000000000000,
merge_tree_max_bytes_to_use_cache=201326592000000
enable_zero_copy_read=1
enable_two_stages_prewhere=1,
disk_cache_mode=USE_DISK_CACHE,
use_uncompressed_cache=1,

点查配置

SETTINGS log_queries_min_type='QUERY_FINISH',
max_threads=1,
exchange_source_pipeline_threads=1,
enable_plan_cache=1,
optimize_skip_unused_shards=1,
use_uncompressed_cache=0,
query_worker_fault_tolerance=0,
enable_prune_source_plan_segment=1 ,
enable_table_scan_build_pipeline_optimization=1,
send_cacheable_table_definitions=1

查询 SQL

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

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) = 1994AND 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 BYyear,
    P_BRAND
ORDER BYyear,
    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 BYyear,
    P_BRAND
ORDER BYyear,
    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 BYyear,
    P_BRAND
ORDER BYyear,
    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 <= 1997GROUP BY
    C_NATION,
    S_NATION,yearORDER BYyear 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 <= 1997GROUP BY
    C_CITY,
    S_CITY,yearORDER BYyear 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 <= 1997GROUP BY
    C_CITY,
    S_CITY,yearORDER BYyear 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) = 199712GROUP BY
    C_CITY,
    S_CITY,yearORDER BYyear 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 BYyear,
    C_NATION
ORDER BYyear 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 BYyear,
    S_NATION,
    P_CATEGORY
ORDER BYyear 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 BYyear,
    S_CITY,
    P_BRAND
ORDER BYyear ASC,
    S_CITY ASC,
    P_BRAND ASC;