Kylin官方案例詳細剖析及剪枝優化-OLAP商業環境實戰

版權宣告：本套技術專欄是作者（秦凱新）平時工作的總結和昇華，通過從真實商業環境抽取案例進行總結和分享，並給出商業應用的調優建議和叢集環境容量規劃等內容，請持續關注本套部落格。版權宣告：禁止轉載，歡迎學習。

Kylin官方案例是一個非常經典的案例，包含的技術細節通過深挖能呈現出更具價值的資訊，如：資料倉儲理論，星型模型，雪花模型，角色扮演維度，維度剪枝等。相信您會通過我的深入剖析，有一種恍然大悟的感覺。官方案例是一個訂單案例，其中包含了訂單事實表，訂單日期表，商品分類維度，賬號維度（購買者和銷售者）以及區域維度（購買者和銷售者）。

一 Kylin官方案例表關係及欄位詳解

1.1 Kylin官方案例表簡要說明

KYLIN_SALES是事實表，儲存了銷售訂單的明細資訊。各列分別儲存著賣家、商品分類、訂單金額、商品數量等資訊，每一行對應著一筆交易訂單。
KYLIN_CATEGORY_GROUPINGS是維表，儲存了商品分類的詳細介紹，例如商品分類名稱等。
KYLIN_CAL_DT也是維表，儲存了時間的擴充套件資訊。如單個日期所、月始、周始、年份、月份等。
KYLIN_ACCOUNT也是維表，作為角色扮演維度，包含購買者和開發者賬號。
KYLIN_COUNTRY包含區域維度，作為角色扮演維度，包含購買者和開發者所在區域資訊。

1.2 KYLIN_SALES 事實表欄位詳解：

1.3 KYLIN_CATEGORY_GROUPINGS 欄位說明：

1.3.1 KYLIN_CATEGORY_GROUPINGS主要欄位：

LEAF_CATEG_ID：主鍵
SITE_ID：外來鍵

1.3.2 KYLIN_CATEGORY_GROUPINGS 對外連線關係：

1.3.3 KYLIN_CATEGORY_GROUPINGS 主要欄位解釋：

1.4 KYLIN_COUNTRY 主要欄位詳解：

1.5 KYLIN_ACCOUNT 主要欄位詳解：

1.6 雪花模型關係總覽：

二建立模型（Model）

2.1 Kylin 官方模型 model關係圖如下：

2.2 第一步：雪花和星型模型，建立inner join關係，重要的是確定欄位關聯，對於角色維度（KYLIN_ACCOUNT和KYLIN_COUNTRY），需要別名處理。

對 “Add Lookup Table” 頁面的幾點說明：

資料關係不僅僅是事實表與維度表之間（星型模型），維度表和維度表之間（雪花模型）也可以建立聯絡；
表與表之間的連線新增有三種：“Left Join”、“Inner Join”、“Right Join”；
Skip snapshot for this lookup table 選項指的是是否跳過生成 snapshotTable，由於某些 Lookup 表特別大（大於 300M），如果某一個維度的基數比較大，可能會導致記憶體出現 OOM，所以在建立 snapshotTable 的時候會限制原始表的大小不能超過配置的一個上限值（kylin.snapshot.max-mb，預設值300）;
跳過構建 snapshot 的 lookup 表將不能搜尋，同時不支援設定為衍生維度（Derived）；
大部分情況下都是使用 “Left Join”，其他兩種 Join 方式不是很常用。

2.2.1 每一個 Snapshot 是和一個 Hive 維度表對應的，生成的過程是：

從原始的hive維度表中順序得讀取每一行每一列的值;
使用 TrieDictionary 方式對這些所有的值進行編碼（一個值對應一個 Id）；
再次讀取原始表中每一行的值，將每一列的值使用編碼之後的 Id 進行替換，得到了一個只有 Id 的新表；
同時儲存這個新表和 Dictionary 物件（Id 和值的對映關係）就能夠儲存整個維度表；

Kylin 將這個資料儲存到後設資料庫中。

  該處Snapshot Table 總結的很好，所以標明引用地址：https://juejin.im/post/5bcf370d6fb9a05cff3255dd
複製程式碼

2.2.2 雪花模型關係圖

2.3 第二步：資格維度選擇:

在 Dimensions 頁面選擇可能參與計算的維度，這裡被選擇的只是在 Cube 構建的時候擁有被選擇資格的維度，並不是最後參與 Cube 構建的維度，推薦將維度表中的欄位都選擇上。如下展示了Dimensions的選擇：

對於KYLIN_SALES，其中SLR_SEGMENT_CD，PRICE，ITEM_COUNT沒有選擇，所以沒有資格參與cubeId構建

對於KYLIN_CAL_DT，其中僅選擇了部分參與的維度，其他沒有資格參與cubeId構建

對於KYLIN_CATEGORY_GROUPING，其中僅選擇了部分參與的維度，其他沒有資格參與cubeId構建

對於BUYER_ACCOUNT 與 SELLER_ACCOUNT，全部有資格參與cubeId構建

對於BUYER_COUNTRY，只有COUNTRY , NAME 有資格參與cubeId構建
對於SELLER_COUNTRY，只有COUNTRY , NAME 有資格參與cubeId構建

同理如上

2.3.1 資格維度選擇結果總覽：

2.4 第三步： Measures 度量指標選擇：

在 Measures 頁面選擇可能用於計算的度量。一般而言，銷售額、流量、溫溼度等會作為度量。

2.5 第四步：Settings設定

在 Settings 頁面可以設定分割槽以及過濾條件，其中分割槽是為了系統可以進行增量構建而設計的，目前 Kylin 支援基於日期的分割槽，在 “Partition Date Column” 後面選擇事實表或者維度表中的日期欄位，然後選擇日期格式即可；過濾條件設定後，Kylin 在構建的時候會選擇符合過濾條件的資料進行構建。需要注意的幾點：

時間分割槽列可以支援日期或更細粒度的時間分割槽；
時間分割槽列支援的資料型別有 time/date/datetime/integer等；
過濾條件不需要寫 WHERE；
過濾條件不能包含日期維度。

三構建cube模型

3.1 維度選擇知識總結

在選擇維度時，每一個維度列可以作為普通維度（Normal），也可以作為衍生維度（Derived）。相對於普通維度來說，衍生維度並不參與維度的 Cuboid，衍生維度對應的外來鍵（FK）參與維度 Cuboid，從而降低 Cuboid 數。在查詢時，對衍生維度的查詢會首先轉換為對外來鍵所在維度的查詢，因此會犧牲少量效能（大部分情況下可以接受）。

3.1.1 維度剪枝優化

如何進行維度優化，首先請確認你設定的cube維度都是你查詢時會使用到的。

目前Kylin可以使用的維度優化手段有以下幾種：

聚集組
衍生緯度
強制維度
層次維度
聯合維度
Extended Column

在一個多維資料集合中，維度的個數決定著維度之間可能的組合數，而每一個維度中成員集合的大小決定著每一個可能的組合的個數，例如有三個普通的維度A、B、C，他們的不同成員數分別為10/100/1000，那麼一個維度的組合有2的3次方個，分別是{空、A、B、C、AB、BC、AC、ABC}，每一個成員我們稱為cuboid（維度的組合），而這些集合的成員組合個數分別為1、10、100、1000、10*100、100 1000、101000和10 *100 *1000。我們稱每一個dimension中不同成員個數為cardinatily，我們要儘量避免儲存cardinatily比較高的維度的組合。

在上面的例子中我們可以不快取BC和C這兩個cuboid，可以通過計算的方式通過ABC中成員的值計算出BC或者C中某個成員組合的值，這相當於是時間和空間的一個權衡吧。在kylin中存在的四種維度是為了減少cuboid的個數，而不是每一個維度是否快取的，當前kylin是對所有的cuboid中的所有組合都進行計算和儲存的，對於普通的dimension，從上面的例子中可以看出N個維度的cuboid個數為2的N次方，而kylin中設定了一些維度可以減少cuboid個數，當然，這需要使用者對自己需要的維度十分了解，知道自己可能根據什麼進行group by。

3.1.2 Mandatory維度

這種維度意味著每次查詢的group by中都會攜帶的，將某一個dimension設定為mandatory可以將cuboid的個數減少一半，如下圖：

這是因為我們確定每一次group by都會攜帶A，那麼就可以省去所有不包含A這個維度的cuboid了。

3.1.3 hierarchy維度

這種維度是最常見的，尤其是在mondrian中，我們對於多維資料的操作經常會有上卷下鑽之類的操作，這也就需要要求維度之間有層級關係，例如國家、省、城市，年、季度、月等。有層級關係的維度也可以大大減少cuboid的個數。如下圖：

這裡僅僅侷限於A/B/C是一個層級，例如A是年份，B是季度、C是月份，那麼查詢的時候可能的組合只有年、xx年的季度、xx年xx季度的xx月，這就意味著我們不能再單獨的對季度和月份進行聚合了，例如我們查詢的時候不能使用group by month，而必須使用group by year，quart，month。如果需要單獨的對month進行聚合，那麼還需要再使用month列定義一個單獨的普通維度。

3.1.4 derived維度

這類維度的意思是可推導的維度，需要該維度對應的一個或者多個列可以和維度表的主鍵是一對一的，這種維度可以大大減少cuboid個數，如下圖：

例如timeid是時間這個維度表的主鍵，也就是事實表的外來鍵，時間只精確到天，那麼year、month、day三列可以唯一對應著一個time_id，而time_id是事實表的外來鍵，那麼我們可以指定year、month、day為一個derived維度，實際儲存的時候可以只根據timeid的取值決定維度的組合，但這就要求我們在查詢的時候使用的group by必須指定derived維度集合中的所有列。 3.聯合維度（Joint）每一個聯合維度包括兩個或者更多的維度，聯合維度內的維度，要麼不出現，要麼必須一起出現。不同的聯合之間不應當有共同的維度

3.1.5 聯合維度

聯合維度：將幾個維度視為一個維度。適用場景：

1 可以將確定在查詢時一定會同時使用的幾個維度設為一個聯合維度。
2 可以將基數很小的幾個維度設為一個聯合維度。
3 可以將查詢時很少使用的幾個維度設為一個聯合維度。

優化效果：將N個維度設定為聯合維度，則這N個維度組合成的cuboid個數會從2的N次方減少到1。

應用例項:

假設建立一個交易資料的Cube，它具有很多普通的維度，像是交易日期 cal_dt，交易的城市 city，顧客性別 sex_id 和支付型別 pay_type 等。分析師常用的分析方法為通過按照交易時間、交易地點和顧客性別來聚合，獲取不同城市男女顧客間不同的消費偏好，例如同時聚合交易日期 cal_dt、交易的城市 city 和顧客性別 sex_id來分組。聚合組：[cal_dt, city, sex_id，pay_type] 聯合維度： [cal_dt, city, sex_id]

Case 1：

SELECT cal_dt, city, sex_id, count(*) FROM table GROUP BY cal_dt, city, sex_id 
則它將從Cuboid [cal_dt, city, sex_id]中獲取資料
複製程式碼

Case2:

如果有一條不常用的查詢：

 SELECT cal_dt, city, count(*) FROM table GROUP BY cal_dt, city
 則沒有現成的完全匹配的 Cuboid，Apache Kylin 會通過線上計算的方式，從現有的 Cuboid 中計算出最終結果。
複製程式碼

3.1.6 粒度優化

粒度優化對應的是提高Cube的併發度，其設定是在自定義屬性中的一共有三個屬性可以提高併發度。 1.kylin.hbase.region.cut（共使用幾個分割槽） 2.kylin.hbase.region.count.min（最少使用幾個分割槽） 3.kylin.hbase.region.count.max（最多使用幾個分割槽）

根據相對應的情況調高最少使用分割槽，降低最大使用分割槽，能夠有效增加系統的並行度。

3.1.7 RowKey優化

Rowkeys: 是由維度編碼值組成。”Dictionary” （字典）是預設的編碼方式; 字典只能處理中低基數（少於一千萬）的維度；如果維度基數很高（如大於1千萬), 選擇 “false” 然後為維度輸入合適的長度，通常是那列的最大長度值; 如果超過最大值，會被截斷。請注意，如果沒有字典編碼，cube 的大小可能會非常大。你可以拖拽維度列去調整其在 rowkey 中位置; 位於rowkey前面的列，將可以用來大幅縮小查詢的範圍。通常建議將 mandantory 維度放在開頭, 然後是在過濾 ( where 條件)中起到很大作用的維度；如果多個列都會被用於過濾，將高基數的維度（如 user_id）放在低基數的維度（如 age）的前面。

Kylin 以 Key-Value 的方式將 Cube 儲存到 HBase 中，HBase 的 key，也就是 Rowkey，是由各維度的值拼接而成的；為了更高效地儲存這些值，Kylin 會對它們進行編碼和壓縮；每個維度均可以選擇合適的編碼（Encoding）方式，預設採用的是字典（Dictionary）編碼技術；欄位支援的基本編碼型別如下：

dict：適用於大部分欄位，預設推薦使用，但在超高基情況下，可能引起記憶體不足的問題；
boolean：適用於欄位值為true, false, TRUE, FALSE, True, False, t, f, T, F, yes, no, YES, NO, Yes, No, y, n, Y, N, 1, 0；
integer：適用於欄位值為整數字符，支援的整數區間為[ -2^(8N-1), 2^(8N-1)]；
date：適用於欄位值為日期字元，支援的格式包括yyyyMMdd、yyyy-MM-dd、yyyy-MM-dd HH:mm:ss、yyyy-MM-dd HH:mm:ss.SSS，其中如果包含時間戳部分會被截斷；
time：適用於欄位值為時間戳字元，支援範圍為[ 1970-01-01 00:00:00, 2038/01/19 03:14:07]，毫秒部分會被忽略，time編碼適用於 time, datetime, timestamp 等型別；
fix_length：適用於超高基場景，將選取欄位的前 N 個位元組作為編碼值，當 N 小於欄位長度，會造成欄位截斷，當 N 較大時，造成 RowKey 過長，查詢效能下降，只適用於 varchar 或 nvarchar 型別；
fixed_length_hex：適用於欄位值為十六進位制字元，比如 1A2BFF 或者 FF00FF，每兩個字元需要一個位元組，只適用於 varchar 或 nvarchar 型別。
和Hbase 的RowKey優化類似，在查詢的過程中，被用作過濾條件的維度可能放在其他維度的前面，經常出現的維度應該放在前面，基數比較大的維度應該放在前面
```
  (參考連結：https://juejin.im/post/5bd5c59851882565e031f4be)
複製程式碼
```

3.2：官方案例維度選擇

3.2.1 KYLIN_SALES維度選擇遇到的問題

發現連線鍵沒有勾選，如：LEAF_CATEG_ID 和LSTG_SITE_ID是外來鍵，PART_DT 的是衍生外來鍵也沒有選擇，因此，意味著在CUBE構建時，只用關心計算維度，連線鍵雖然不選擇，衍生維度還是生效的：

3.2.2 KYLIN_CAL_DT 維度選擇，發現PART_DT 的是衍生主鍵沒有勾選：

3.2.3 KYLIN_CATEGORY_GROUPINGS維度選擇遇到的問題

維度選擇，發現連線鍵LEAF_CATEG_ID和SITE_ID沒有選擇，那麼USER_DEFINED_FIELD1和USER_DEFINED_FIELD3作為衍生列，那麼衍生列有什麼用呢？？看我和我朋友的聊天記錄：

3.2.3 BUYER_ACCOUNT 維度選擇遇到的問題

發現連線鍵ACCOUNT_ID沒有選擇，另外無關列ACCOUNT_SELLER_LEVEL和 ACCOUNT_CONTACT沒有被選擇，注意ACCOUNT_COUNTRY改名為BUYER_COUNTRY，如下所示：

3.2.4 SELLER_ACCOUNT 維度選擇遇到的問題

發現連線鍵ACCOUNT_ID沒有選擇，另外無關列ACCOUNT_BUYER_LEVEL和 ACCOUNT_CONTACT沒有被選擇，注意ACCOUNT_COUNTRY改名為SELLER_COUNTRY，如下所示：

3.2.5 BUYER_COUNTRY 和 SELLER_COUNTRY 維度選擇遇到的問題

發現連線鍵COUNTRY沒有選擇，另外 NAME改名為BUYER_COUNTRY_NAME和SELLER_COUNTRY_NAME。

3.3 官方案例度量選擇

主要的聚合方式有：COUNT、SUM、MIN、MAX、PERCENTILE，下面將詳細介紹其他幾種聚合方式：

3.3.1 Count Distinct 理論知識：

Apache Kylin提供了兩種Count Distinct計算方式，一種是近似的，一種是精確的，精確的Count Distinct指標在Build時候會消耗更多的資源（記憶體和儲存），Build的過程也比較慢。

近似Count Distinct Apache Kylin使用HyperLogLog演算法實現了近似Count Distinct,提供了錯誤率從9.75%到1.22%幾種精度供選擇；演算法計算後的Count Distinct指標，理論上，結果最大隻有64KB，最低的錯誤率是1.22%；這種實現方式用在需要快速計算、節省儲存空間，並且能接受錯誤率的Count Distinct指標計算。

精準Count Distinct Kylin中實現了基於bitmap的精確Count Distinct計算方式。當資料型別為tiny int(byte)、small int(short)以及int，會直接將資料值對映到bitmap中；當資料型別為long,string或者其他，則需要將資料值以字串形式編碼成dict(字典)，再將字典ID對映到bitmap; 指標計算後的結果，並不是計數後的值，而是包含了序列化值的bitmap.這樣，才能確保在任意維度上的Count Distinct結果是正確的。這種實現方式提供了精確的無錯誤的Count Distinct結果，但是需要更多的儲存資源，如果資料中的不重復值超過百萬，結果所佔的儲存應該會達到幾百MB。

3.3.2 EXTEND_COLUMN 理論知識：

Extended Column 在OLAP分析場景中，經常存在對某個id進行過濾，但查詢結果要展示為name的情況，比如user_id和user_name。這類問題通常有三種解決方式：

a. 將ID和Name都設定為維度，查詢語句類似select name, count(*) from table where id = 1 group by id,name。這種方式的問題是會導致維度增多，導致預計算結果膨脹；
b. 將id和name都設定為維度，並且將兩者設定為聯合。這種方式的好處是保持維度組合數不會增加，但限制了維度的其它優化，比如ID不能再被設定為強制維度或者層次維度；
c. 將ID設定為維度，Name設定為特殊的Measure，型別為Extended Column。這種方式既能保證過濾id且查詢name的需求，同時也不影響id維度的進一步優化。

3.4 Count Distinct和TopN 官方案例講解：

3.4.1 Count Distinct案例，主要用於對訂單去重，錯誤率要求小於一定百分比

3.4.2 TopN 案例，主要用於按照seller_id 進行分組，然後對price進行聚合操作，實現每一筆訂單的銷售總額

3.5 cube中Messures總覽

4 官方案例Refresh Setting 設定

Auto Merge Thresholds ：自動合併閾值，按天增加的segement，每7天合併一次；7天的segment每28天合併一次
Retention Threshold：預設為0，保留歷史所有的segment。
Volatile Range: 預設為 0，‘Auto Merge’ 會自動合併所有可能的 cube segments；設定具體的數值後，‘Auto Merge’ 將不會合並最近 Volatile Range 天的 cube segments；假設 Volatile Range 設定為 7，則最近 7 天內生成的 cube segments 不會被自動合併；
Partition Start Date:分割槽開始時間

4.1 官方案例Refresh Setting 強制維度設定

和朋友進行反覆確認的強制維度，這裡官方案例真的出現了，在進行查詢的時候，PART_DT不出現也是可以的，為什麼呢？請參考下面的解釋

Mandatory 維度指的是那些總是會出現在Where 條件或 Group By 語句裡的維度。當然必須存在不一定是顯式出現在查詢語句中，例如查詢日期是必要欄位，月份、季度、年屬於它的衍生欄位，那麼查詢的時候出現月份、季度、年這些衍生欄位等效於出現查詢日期這個必要欄位。

4.2 官方案例Refresh Setting層次維度的設定

可以看到官方案例按照層次順序，進行cubeId的構建，根據繫結關係，進行了剪枝處理

4.3 官方案例Refresh Setting聯合維度的設定

根據繫結關係，進行了剪枝處理

4.4 Rokeys 的設定

各維度在 Rowkeys 中的順序，對於查詢的效能會產生較明顯的影響；在這裡使用者可以根據查詢的模式和習慣，通過拖曳的方式調整各個維度在Rowkeys上的順序。推薦的順序為：Mandatory 維度、where 過濾條件中出現頻率較多的維度、高基數維度、低基數維度。這樣做的好處是，充分利用過濾條件來縮小在 HBase 中掃描的範圍，從而提高查詢的效率。

我們發現不常用維度放在了後面：

5 官方案例總覽

發現總共使用了20個維度和6個度量，以及6個Lookup Table (包含別名表)

6 官方案例構建CUBE

7 官方案例測試

在進行Cube構建時，我沒有選擇連線鍵和衍生維度(主鍵)，那麼能不能正常使用，讓我們拭目以待，以下是維度剪枝優化的內容，基於此，我們來做測試：

7.1 測試一：Joint Dimension 缺少夥伴是否會報錯？

7.1.1 訂單事實表與訂單類別表的聯合查詢：

7.1.2 結論：聯合維度確實同伴不會報錯

7.2 測試二：衍生維度能否正常分組和條件過濾？

7.2.1 訂單事實表與時間維度表進行衍生欄位分組：

7.2.2 結論：衍生維度分組不會報錯

7.2.2 訂單事實表與時間維度表進行衍生欄位分組+過濾：

7.2.3 結論：衍生維度過濾不會報錯

7.3 測試三：Mandatory Dimensions 缺少是否報錯？

如果強制維度是衍生維度所在表的連線鍵（即KYLIN_SALES 與 KYLIN_CAL_DT的連線鍵CAL_DT）在，那麼查詢時不包含強制維度也不會不錯，參考7.2.1既可以看出來。

7.3.1 結論：KYLIN_SALES.PART_DT缺少不會報錯，雖然被設定成強制維度

7.4 測試四：層次維度亂序會不會報錯呢？

可以看到隨便更改層次維度順序，都不會報錯，原則上已經做過優化：

看我和一個朋友的聊天記錄，感謝我這位朋友技術支援：

7.4.1 結論：不會報錯

應該這樣理解，層次維度ABC，AB , A 包含了所有維度組合情況情況，所以就不用其他的組合了，計算BC實際上查的還是ABC，所以Kylin通過層次維度設定，縮減了cubeId的數量。

8 結語

至此，整個官方案例剖析完畢，而留給我們的思考又該什麼時候結束呢？Kylin作為OLAP技術先驅，真正把HBASE的列式儲存功能發揮到了極致，沒有HBASE和SPARK作為技術支撐，KYLIN又該是什麼呢？如果有機會，我會寫一篇HBASE技術專欄，我們來看看Hbase如何基於LSM(Log-Structured Merge Tree)把索引即資料的思想給落地的。如對spark感興趣請關注我的Spark技術架構剖析專欄。

秦凱新於深圳 2018-10-30