以 100GB SSB 性能測試為例，通過 ByteHouse 云數(shù)倉開啟你的數(shù)據(jù)分析之路

作者：ByteHouse 團(tuán)隊(duì) 2023-04-22 09:40:36

云計(jì)算

云原生 ByteHouse 作為云原生的數(shù)據(jù)平臺(tái)，從架構(gòu)層面入手，通過存儲(chǔ)和計(jì)算分離的云原生架構(gòu)完美適配云上基礎(chǔ)設(shè)施。ByteHouse 作為云原生的數(shù)據(jù)平臺(tái)，從架構(gòu)層面入手，通過存儲(chǔ)和計(jì)算分離的云原生架構(gòu)完美適配云上基礎(chǔ)設(shè)施。

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對(duì)這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名注冊(cè)、雅安服務(wù)器托管、營銷軟件、網(wǎng)站建設(shè)、寶安網(wǎng)站維護(hù)、網(wǎng)站推廣。

I. 傳統(tǒng)數(shù)倉的演進(jìn)：云數(shù)倉

近年來，隨著數(shù)據(jù)“爆炸式”的增長，越來越多的數(shù)據(jù)被產(chǎn)生、收集和存儲(chǔ)。而挖掘海量數(shù)據(jù)中的真實(shí)價(jià)值，從其中提取商機(jī)并洞見未來，則成了現(xiàn)代企業(yè)和組織不可忽視的命題。

隨著數(shù)據(jù)量級(jí)和復(fù)雜度的增大，數(shù)據(jù)分析處理的技術(shù)架構(gòu)也在不斷演進(jìn)。在面對(duì)海量數(shù)據(jù)分析時(shí)，傳統(tǒng) OLAP 技術(shù)架構(gòu)中的痛點(diǎn)變得越來越明顯，如擴(kuò)容縮容耗時(shí)長，導(dǎo)致資源利用率偏低，成本居高不下；以及運(yùn)維配置復(fù)雜，需要專業(yè)的技術(shù)人員介入等。

為了解決這類問題，云數(shù)倉的概念應(yīng)運(yùn)而生。和傳統(tǒng)數(shù)倉架構(gòu)不同的是，云原生數(shù)倉借助于云平臺(tái)的基礎(chǔ)資源，實(shí)現(xiàn)了資源的動(dòng)態(tài)擴(kuò)縮容，并最大化利用資源，從而達(dá)到 Pay as you go 按實(shí)際用量付費(fèi)的模式。

ByteHouse 作為云原生的數(shù)據(jù)平臺(tái)，從架構(gòu)層面入手，通過存儲(chǔ)和計(jì)算分離的云原生架構(gòu)完美適配云上基礎(chǔ)設(shè)施。在字節(jié)跳動(dòng)內(nèi)部，ByteHouse 已經(jīng)支持 80% 的分析應(yīng)用場景，包括用戶增長業(yè)務(wù)、廣告、A/B 測試等。除了極致的分析性能之外，ByteHouse 開箱即用，按實(shí)際使用付費(fèi)的特性也極大地降低了企業(yè)和個(gè)人的上手門檻，能夠在短短數(shù)分鐘內(nèi)體驗(yàn)到數(shù)據(jù)分析的魅力。Talk is cheap, 接下來就讓我們通過一個(gè)實(shí)戰(zhàn)案例來體驗(yàn)下 ByteHouse 云數(shù)倉的強(qiáng)大功能。

II. 快速上手 ByteHouse —— 輕量級(jí)云數(shù)倉

本章節(jié)通過使用 ByteHouse 云數(shù)倉進(jìn)行 SSB 基準(zhǔn)測試，在帶領(lǐng)讀者了解產(chǎn)品性能的同時(shí)，也一并熟悉產(chǎn)品中各個(gè)模塊的功能，開啟你的數(shù)據(jù)分析之路，通過分析海量數(shù)據(jù)，加速數(shù)據(jù)洞察。ByteHouse 的架構(gòu)總覽如下。

SSB 基準(zhǔn)測試

SSB（Star Schema Benchmark）是由麻省州立大學(xué)波士頓校區(qū)的研究員定義的基于現(xiàn)實(shí)商業(yè)應(yīng)用的數(shù)據(jù)模型。SSB 是在 TPC-H 標(biāo)準(zhǔn)的基礎(chǔ)上改進(jìn)而成，主要將 TPC-H 中的雪花模型改成了更為通用的的星型模型，將基準(zhǔn)查詢從復(fù)雜的 Ad-hoc 查詢改成了結(jié)構(gòu)更加固定的 OLAP 查詢，從而主要用于模擬測試 OLAP 引擎和輕量數(shù)倉場景下的查詢性能。由于 SSB 基準(zhǔn)測試較為中立，并貼近現(xiàn)實(shí)的商業(yè)場景，因此在學(xué)界及工業(yè)界有廣泛的應(yīng)用。

SSB 基準(zhǔn)測試中對(duì)應(yīng)的表結(jié)構(gòu)如下所示，可以看到 SSB 主要采用星型模型，其中包含了 1 個(gè)事實(shí)表 lineorder 和 4 個(gè)維度表 customer, part, dwdate 以及 supplier，每張維度表通過 Primary Key 和事實(shí)表進(jìn)行關(guān)聯(lián)。測試通過執(zhí)行 13 條 SQL 進(jìn)行查詢，包含了多表關(guān)聯(lián)，group by，復(fù)雜條件等多種組合。更多詳細(xì)信息請(qǐng)參考 SSB 文獻(xiàn) (??https://www.cs.umb.edu/~poneil/StarSchemaB.pdf??)。

步驟一：官網(wǎng)注冊(cè)并開通 ByteHouse

訪問ByteHouse 云數(shù)倉火山引擎官網(wǎng)，注冊(cè)火山引擎賬戶，完成實(shí)名認(rèn)證后，即可登錄到產(chǎn)品控制臺(tái)。開通產(chǎn)品進(jìn)行測試，目前 ByteHouse 支持包年包月和按量付費(fèi)兩種模式的實(shí)例，便于您根據(jù)業(yè)務(wù)需求進(jìn)行選擇。

ByteHouse 云數(shù)倉火山引擎官網(wǎng)

??https://www.volcengine.com/product/bytehouse-cloud??

產(chǎn)品控制臺(tái)

??https://console.volcengine.com/bytehouse??

步驟二：創(chuàng)建計(jì)算組

登錄到控制臺(tái)后，可以看到數(shù)據(jù)庫表管理、數(shù)據(jù)加載、SQL 工作表、計(jì)算組、查詢歷史和角色管理等幾大模塊。分別具有如下作用：

數(shù)據(jù)庫表管理：用于創(chuàng)建和管理數(shù)據(jù)庫、數(shù)據(jù)表以及視圖等數(shù)據(jù)對(duì)象 數(shù)據(jù)加載：用于從不同的離線和實(shí)時(shí)數(shù)據(jù)源如對(duì)象存儲(chǔ)、Kafka 等地寫入數(shù)據(jù) SQL 工作表：在界面上編輯、管理并運(yùn)行 SQL 查詢 計(jì)算組：創(chuàng)建和管理虛擬的計(jì)算資源，用于執(zhí)行數(shù)據(jù)查詢等操作 查詢歷史：用于查看 SQL 的歷史執(zhí)行記錄、狀態(tài)和查詢?cè)斍榈?/p>

為了方便進(jìn)行后續(xù)的建庫建表和查詢等操作，首先在 ByteHouse 控制臺(tái)創(chuàng)建型號(hào)為 L 的計(jì)算組，如下圖所示

計(jì)算組是 Bytehouse 中的計(jì)算資源集群，可按需進(jìn)行橫向擴(kuò)展。計(jì)算組提供所需的資源如 CPU、內(nèi)存及臨時(shí)存儲(chǔ)等，用于執(zhí)行數(shù)據(jù)查詢 DQL、DML 等操作。ByteHouse 計(jì)算組能夠?qū)崿F(xiàn)彈性擴(kuò)縮容，讀寫分離、存算分離等，并且能對(duì)資源進(jìn)行細(xì)粒度的權(quán)限控制。

步驟三：創(chuàng)建數(shù)據(jù)庫表

在控制臺(tái)頁面中創(chuàng)建名為 ssb_``100 的數(shù)據(jù)庫

創(chuàng)建完畢后，進(jìn)入到 SQL 工作表模塊，通過如下建表語句建立四個(gè)數(shù)據(jù)表（事實(shí)表），并保存對(duì)應(yīng)的 SQL 語句。

CREATE TABLE ssb_100.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),
C_PLACEHOLDER   Nullable(String)
)
ENGINE = CnchMergeTree ORDER BY (C_CUSTKEY);
CREATE TABLE ssb_100.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),
LO_PLACEHOLDER          Nullable(String)
)
ENGINE = CnchMergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);
CREATE TABLE ssb_100.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),
P_PLACEHOLDER   Nullable(String)
)
ENGINE = CnchMergeTree ORDER BY P_PARTKEY;
CREATE TABLE ssb_100.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,
S_PLACEHOLDER   Nullable(String)
)
ENGINE = CnchMergeTree ORDER BY S_SUPPKEY;
CREATE TABLE ssb_100.dwdate
(
D_DATEKEY            UInt32,
D_DATE               String,
D_DAYOFWEEK          String,    -- defined in Section 2.6 as Size 8, but Wednesday is 9 letters
D_MONTH              String,
D_YEAR               UInt32,
D_YEARMONTHNUM       UInt32,
D_YEARMONTH          String,
D_DAYNUMINWEEK       UInt32,
D_DAYNUMINMONTH      UInt32,
D_DAYNUMINYEAR       UInt32,
D_MONTHNUMINYEAR     UInt32,
D_WEEKNUMINYEAR      UInt32,
D_SELLINGSEASON      String,
D_LASTDAYINWEEKFL    UInt32,
D_LASTDAYINMONTHFL   UInt32,
D_HOLIDAYFL          UInt32,
D_WEEKDAYFL          UInt32,
S_PLACEHOLDER        Nullable(String)
)
ENGINE=CnchMergeTree() ORDER BY (D_DATEKEY);

SQL 執(zhí)行完畢后，在控制臺(tái)左側(cè)對(duì)應(yīng)的數(shù)據(jù)對(duì)象頁面會(huì)展示出創(chuàng)建完成的五個(gè)工作表，分別為 customer,dwdate,lineorder以及part 和 supplier

步驟四：從對(duì)象存儲(chǔ)中導(dǎo)入 SSB 數(shù)據(jù)

通過預(yù)先生成 SSB_100 GB 的數(shù)據(jù)集并存儲(chǔ)在對(duì)象存儲(chǔ)（如 AWS S3 或者 火山引擎 TOS），我們可以方便且快速的將數(shù)據(jù)導(dǎo)入到 ByteHouse 中進(jìn)行分析。本次實(shí)踐中通過配置 火山引擎 TOS 的數(shù)據(jù)源對(duì)數(shù)據(jù)進(jìn)行導(dǎo)入。

首先在數(shù)據(jù)加載模塊，新建對(duì)象存儲(chǔ)數(shù)據(jù)源，并配置對(duì)應(yīng)的秘鑰連接火山引擎對(duì)象存儲(chǔ)

連接新的數(shù)據(jù)源后，選擇 bytehouse-shared-dataset 的儲(chǔ)存桶和ssb_100/lineorder.csv 相應(yīng)的路徑

選擇之前建的數(shù)據(jù)庫ssb_100和對(duì)應(yīng)標(biāo)表lineorder，然后按創(chuàng)建。重復(fù)步驟為其他四個(gè)工作表數(shù)據(jù)加載。

數(shù)據(jù)源中存儲(chǔ)的數(shù)據(jù)條數(shù)如下所示。用于導(dǎo)入完成后，對(duì)數(shù)據(jù)表的行數(shù)進(jìn)行統(tǒng)計(jì)，進(jìn)行準(zhǔn)確性校驗(yàn)。

創(chuàng)建導(dǎo)入任務(wù)完成后，點(diǎn)擊“開始”啟動(dòng)導(dǎo)入任務(wù)，任務(wù)啟動(dòng)后會(huì)在幾秒鐘內(nèi)分配資源并初始化導(dǎo)入任務(wù)，并在導(dǎo)入過程中展示預(yù)估的時(shí)間和導(dǎo)入進(jìn)度。在導(dǎo)入任務(wù)的執(zhí)行詳情中，可以查看導(dǎo)入狀態(tài)、導(dǎo)入詳細(xì)日志、配置信息等。

步驟五：數(shù)據(jù)處理及分析

1、原始查詢測試

通過執(zhí)行 SSB 的 13 條查詢語句，對(duì)于多表關(guān)聯(lián)和排序等場景進(jìn)行性能測試。查詢語句如下所示：

-- pre-warm
select * from ssb_100.customer order by C_CUSTKEY desc limit 100;
select * from ssb_100.dwdate order by D_DATEKEY desc limit 100;
select * from ssb_100.lineorder order by LO_ORDERKEY desc limit 100;
select * from ssb_100.part order by P_PARTKEY desc limit 100;
select * from ssb_100.supplier order by S_SUPPKEY desc limit 100;
select * from ssb_100.lineorder_flat order by LO_ORDERKEY desc limit 100;

-- Q1.1
select sum(LO_EXTENDEDPRICE*LO_DISCOUNT) as revenue
from ssb_100.lineorder
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_100.lineorder
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_100.lineorder
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 d_year, P_BRAND
from ssb_100.lineorder, ssb_100.part, ssb_100.supplier
where LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY
and P_CATEGORY = 'MFGR#53' and S_REGION = 'AMERICA'
GROUP BY d_year, P_BRAND;

-- Q2.2
SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY
and 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_100.lineorder, ssb_100.part, ssb_100.supplier
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY 
and 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_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and 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_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and 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_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and (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_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and (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_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and 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_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and 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_100.lineorder, ssb_100.part, ssb_100.supplier
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY
and 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;

2.打平表測試

為了方便對(duì) SSB 數(shù)據(jù)集進(jìn)行測試，我們可以通過改寫 SSB，將星型模型打平轉(zhuǎn)換為大寬表進(jìn)行分析

注：為了確保打平表的執(zhí)行，需要配置參數(shù) SET max_memory_usage = 20000000000; 此外需要在 ByteHouse 控制臺(tái)中配置查詢超時(shí)為 3600s (我的賬戶 > 查詢配置 > 查詢超時(shí))，避免執(zhí)行超時(shí)導(dǎo)致的失敗。

SET max_memory_usage = 20000000000;
SET send_timeout = 3600;
SET receive_timeout = 3600;
CREATE TABLE IF NOT EXISTS ssb_100.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 ssb_100.lineorder as L
inner join ssb_100.customer as C on C.C_CUSTKEY = L.LO_CUSTKEY
inner join ssb_100.supplier as S on S.S_SUPPKEY = L.LO_SUPPKEY
inner join ssb_100.part as P on P.P_PARTKEY = L.LO_PARTKEY;

建表完成后，通過執(zhí)行查詢語句進(jìn)行 SSB 性能測試，如下所示：

-- F1.1
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM ssb_100.lineorder_flat
WHERE toYear(LO_ORDERDATE) = 1993
AND LO_DISCOUNT BETWEEN 1 AND 3
AND LO_QUANTITY < 25;
-- F1.2
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM ssb_100.lineorder_flat
WHERE toYYYYMM(LO_ORDERDATE) = 199401
AND LO_DISCOUNT BETWEEN 4 AND 6
AND LO_QUANTITY BETWEEN 26 AND 35;
-- F1.3
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM ssb_100.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;
-- F2.1
SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM ssb_100.lineorder_flat
WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
GROUP BY year, P_BRAND
ORDER BY year, P_BRAND;
-- F2.2
SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM ssb_100.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;
-- F2.3
SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM ssb_100.lineorder_flat
WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
GROUP BY year, P_BRAND
ORDER BY year, P_BRAND;
-- F3.1
SELECT C_NATION, S_NATION, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.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;
-- F3.2
SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.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;
-- F3.3
SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.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;
-- F3.4
SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.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;
-- F4.1
SELECT toYear(LO_ORDERDATE) AS year, C_NATION, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb_100.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;
-- F4.2
SELECT toYear(LO_ORDERDATE) AS year, S_NATION, P_CATEGORY, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb_100.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;
-- F4.3
SELECT toYear(LO_ORDERDATE) AS year, S_CITY, P_BRAND, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb_100.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;

III. 查詢結(jié)果和成本分析

執(zhí)行完畢后，統(tǒng)計(jì)查詢結(jié)果如下所示：

注：查詢結(jié)果因配置參數(shù)和資源配置的不同，耗時(shí)也有差異，歡迎聯(lián)系 ByteHouse 進(jìn)行查詢優(yōu)化。

查詢完成后，在 ByteHouse 計(jì)算組詳情頁面可以查看工作負(fù)載，包括總查詢條數(shù)和 CPU/Mem 利用率等，從而確認(rèn)計(jì)算資源的使用情況。

根據(jù)本次壓測進(jìn)行預(yù)估，消耗計(jì)算和存儲(chǔ)資源如下表所示，由于 ByteHouse 云數(shù)倉版本按使用量計(jì)費(fèi)的能力，在空閑時(shí)支持自動(dòng)關(guān)閉計(jì)算組并不收取閑置費(fèi)用，從而能夠極大的節(jié)省資源。測試完成后，預(yù)估的總體消耗約為 31.23 元。

文章標(biāo)題：以100GBSSB性能測試為例，通過ByteHouse云數(shù)倉開啟你的數(shù)據(jù)分析之路
網(wǎng)站地址：http://m.5511xx.com/article/dhcedej.html