I. 傳統(tǒng)數(shù)倉的演進：云數(shù)倉
近年來，隨著數(shù)據(jù)“爆炸式”的增長，越來越多的數(shù)據(jù)被產(chǎn)生、收集和存儲。而挖掘海量數(shù)據(jù)中的真實價值，從其中提取商機并洞見未來，則成了現(xiàn)代企業(yè)和組織不可忽視的命題。
隨著數(shù)據(jù)量級和復雜度的增大，數(shù)據(jù)分析處理的技術(shù)架構(gòu)也在不斷演進。在面對海量數(shù)據(jù)分析時，傳統(tǒng) OLAP 技術(shù)架構(gòu)中的痛點變得越來越明顯，如擴容縮容耗時長，導致資源利用率偏低，成本居高不下；以及運維配置復雜，需要專業(yè)的技術(shù)人員介入等。
為了解決這類問題，云數(shù)倉的概念應運而生。和傳統(tǒng)數(shù)倉架構(gòu)不同的是，云原生數(shù)倉借助于云平臺的基礎(chǔ)資源，實現(xiàn)了資源的動態(tài)擴縮容，并最大化利用資源，從而達到 Pay as you go 按實際用量付費的模式。
ByteHouse 作為云原生的數(shù)據(jù)平臺，從架構(gòu)層面入手，通過存儲和計算分離的云原生架構(gòu)完美適配云上基礎(chǔ)設施。在字節(jié)跳動內(nèi)部，ByteHouse 已經(jīng)支持 80% 的分析應用場景，包括用戶增長業(yè)務、廣告、A/B 測試等。除了極致的分析性能之外，ByteHouse 開箱即用，按實際使用付費的特性也極大地降低了企業(yè)和個人的上手門檻，能夠在短短數(shù)分鐘內(nèi)體驗到數(shù)據(jù)分析的魅力。Talk is cheap, 接下來就讓我們通過一個實戰(zhàn)案例來體驗下 ByteHouse 云數(shù)倉的強大功能。
II. 快速上手 ByteHouse —— 輕量級云數(shù)倉
本章節(jié)通過使用 ByteHouse 云數(shù)倉進行 SSB 基準測試，在帶領(lǐng)讀者了解產(chǎn)品性能的同時，也一并熟悉產(chǎn)品中各個模塊的功能，開啟你的數(shù)據(jù)分析之路，通過分析海量數(shù)據(jù)，加速數(shù)據(jù)洞察。ByteHouse 的架構(gòu)總覽如下。
SSB 基準測試
SSB（Star Schema Benchmark）是由麻省州立大學波士頓校區(qū)的研究員定義的基于現(xiàn)實商業(yè)應用的數(shù)據(jù)模型。SSB 是在 TPC-H 標準的基礎(chǔ)上改進而成，主要將 TPC-H 中的雪花模型改成了更為通用的的星型模型，將基準查詢從復雜的 Ad-hoc 查詢改成了結(jié)構(gòu)更加固定的 OLAP 查詢，從而主要用于模擬測試 OLAP 引擎和輕量數(shù)倉場景下的查詢性能。由于 SSB 基準測試較為中立，并貼近現(xiàn)實的商業(yè)場景，因此在學界及工業(yè)界有廣泛的應用。
SSB 基準測試中對應的表結(jié)構(gòu)如下所示，可以看到 SSB 主要采用星型模型，其中包含了 1 個事實表 lineorder 和 4 個維度表 customer, part, dwdate 以及 supplier，每張維度表通過 Primary Key 和事實表進行關(guān)聯(lián)。測試通過執(zhí)行 13 條 SQL 進行查詢，包含了多表關(guān)聯(lián)，group by，復雜條件等多種組合。更多詳細信息請參考 SSB 文獻 (??https://www.cs.umb.edu/~poneil/StarSchemaB.pdf??)。
步驟一：官網(wǎng)注冊并開通 ByteHouse
訪問ByteHouse 云數(shù)倉火山引擎官網(wǎng)，注冊火山引擎賬戶，完成實名認證后，即可登錄到產(chǎn)品控制臺。開通產(chǎn)品進行測試，目前 ByteHouse 支持包年包月和按量付費兩種模式的實例，便于您根據(jù)業(yè)務需求進行選擇。
ByteHouse 云數(shù)倉火山引擎官網(wǎng)
??https://www.volcengine.com/product/bytehouse-cloud??
產(chǎn)品控制臺
??https://console.volcengine.com/bytehouse??
步驟二：創(chuàng)建計算組
登錄到控制臺后，可以看到數(shù)據(jù)庫表管理、數(shù)據(jù)加載、SQL 工作表、計算組、查詢歷史和角色管理等幾大模塊。分別具有如下作用：
數(shù)據(jù)庫表管理：用于創(chuàng)建和管理數(shù)據(jù)庫、數(shù)據(jù)表以及視圖等數(shù)據(jù)對象
數(shù)據(jù)加載：用于從不同的離線和實時數(shù)據(jù)源如對象存儲、Kafka 等地寫入數(shù)據(jù)
SQL 工作表：在界面上編輯、管理并運行 SQL 查詢
計算組：創(chuàng)建和管理虛擬的計算資源，用于執(zhí)行數(shù)據(jù)查詢等操作
查詢歷史：用于查看 SQL 的歷史執(zhí)行記錄、狀態(tài)和查詢詳情等
為了方便進行后續(xù)的建庫建表和查詢等操作，首先在 ByteHouse 控制臺創(chuàng)建型號為 L 的計算組，如下圖所示
計算組是 Bytehouse 中的計算資源集群，可按需進行橫向擴展。計算組提供所需的資源如 CPU、內(nèi)存及臨時存儲等，用于執(zhí)行數(shù)據(jù)查詢 DQL、DML 等操作。ByteHouse 計算組能夠?qū)崿F(xiàn)彈性擴縮容，讀寫分離、存算分離等，并且能對資源進行細粒度的權(quán)限控制。
步驟三：創(chuàng)建數(shù)據(jù)庫表
在控制臺頁面中創(chuàng)建名為 ssb_``100 的數(shù)據(jù)庫
創(chuàng)建完畢后，進入到 SQL 工作表模塊，通過如下建表語句建立四個數(shù)據(jù)表（事實表），并保存對應的 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í)行完畢后，在控制臺左側(cè)對應的數(shù)據(jù)對象頁面會展示出創(chuàng)建完成的五個工作表，分別為 customer,dwdate,lineorder以及part 和 supplier
步驟四：從對象存儲中導入 SSB 數(shù)據(jù)
通過預先生成 SSB_100 GB 的數(shù)據(jù)集并存儲在對象存儲（如 AWS S3 或者 火山引擎 TOS），我們可以方便且快速的將數(shù)據(jù)導入到 ByteHouse 中進行分析。本次實踐中通過配置 火山引擎 TOS 的數(shù)據(jù)源對數(shù)據(jù)進行導入。
首先在數(shù)據(jù)加載模塊，新建對象存儲數(shù)據(jù)源，并配置對應的秘鑰連接火山引擎對象存儲
連接新的數(shù)據(jù)源后，選擇 bytehouse-shared-dataset 的儲存桶和ssb_100/lineorder.csv 相應的路徑
選擇之前建的數(shù)據(jù)庫ssb_100和對應標表lineorder，然后按創(chuàng)建。重復步驟為其他四個工作表數(shù)據(jù)加載。
數(shù)據(jù)源中存儲的數(shù)據(jù)條數(shù)如下所示。用于導入完成后，對數(shù)據(jù)表的行數(shù)進行統(tǒng)計，進行準確性校驗。
創(chuàng)建導入任務完成后，點擊“開始”啟動導入任務，任務啟動后會在幾秒鐘內(nèi)分配資源并初始化導入任務，并在導入過程中展示預估的時間和導入進度。在導入任務的執(zhí)行詳情中，可以查看導入狀態(tài)、導入詳細日志、配置信息等。
步驟五：數(shù)據(jù)處理及分析
1、原始查詢測試
通過執(zhí)行 SSB 的 13 條查詢語句，對于多表關(guān)聯(liá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.打平表測試
為了方便對 SSB 數(shù)據(jù)集進行測試，我們可以通過改寫 SSB，將星型模型打平轉(zhuǎn)換為大寬表進行分析
注：為了確保打平表的執(zhí)行，需要配置參數(shù) SET max_memory_usage = 20000000000; 此外需要在 ByteHouse 控制臺中配置查詢超時為 3600s (我的賬戶 > 查詢配置 > 查詢超時)，避免執(zhí)行超時導致的失敗。
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í)行查詢語句進行 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)計查詢結(jié)果如下所示：
注：查詢結(jié)果因配置參數(shù)和資源配置的不同，耗時也有差異，歡迎聯(lián)系 ByteHouse 進行查詢優(yōu)化。
查詢完成后，在 ByteHouse 計算組詳情頁面可以查看工作負載，包括總查詢條數(shù)和 CPU/Mem 利用率等，從而確認計算資源的使用情況。
根據(jù)本次壓測進行預估，消耗計算和存儲資源如下表所示，由于 ByteHouse 云數(shù)倉版本按使用量計費的能力，在空閑時支持自動關(guān)閉計算組并不收取閑置費用，從而能夠極大的節(jié)省資源。測試完成后，預估的總體消耗約為 31.23 元。
