如何使用 Hive 進行 OLAP 分析

寫在前面

在線分析處理 (OLAP,Online Analytical Processing) 是通過帶層次的維度和跨維度進行多維分析的，簡單理解爲一種多維數據分析的方式，通過 OLAP 可以展示數據倉庫中數據的多維邏輯視圖。在多維分析中，數據是按照維度 (觀察數據的角度) 來表示的，比如商品、城市、客戶。而維通常按層次 (層次維度) 組織的，如城市、省、國家，再比如時間也是有層次的，如天、周、月、季度和年。不同的管理者可以從不同的維度 (視角) 去觀察這些數據，這些在多個不同維度上對數據進行綜合考察的手段就是通常所說的數據倉庫多維查詢，最常見的就如上卷 (roll-up) 和下鑽 (drill-down) 了, 所謂上卷，指的是選定特定的數據範圍之後，對其進行彙總統計以獲取更高層次的信息。所謂下鑽，指的是選定特定的數據範圍之後，需要進一步查看細節的數據。從另一種意義上說，鑽取就是針對多維展現的數據，進一步探究其內部的組成和來源。值得注意的是，上卷和下鑽要求維度具有層級結構，即數倉中所說的層次維度。

如何實現數據的多維分析

Hive 提供了多維數據分析的函數，如 GROUPING SETS,GROUPING_ID,CUBE,ROLLUP, 通過這些分析函數，可以輕而易舉的實現多維數據分析。下面將會通過一個案例來了解這些函數的具體含義以及該如何使用這些函數。注意：在 hive 中使用這些函數之前，要確保開啓了 map 端聚合，即 set hive.map.aggr=true，否則會報如下錯誤：

簡單介紹

在一個 group by 查詢中，通過該子句可以對不同維度或同一維度的不同層次進行聚合，簡單理解爲一條 sql 可以實現多種不同的分組規則，用戶可以在該函數中傳入自己定義的多種分組字段，本質上等價於多個 group by 語句進行 UNION，對於 GROUPING SETS 子句中的空白集'（）'表示對總體進行聚集。

示例模板

-- 使用GROUPING SETS查詢
SELECT a,
       b,
       SUM(c)
FROM tab1
GROUP BY a,b
GROUPING SETS ((a,b), a,b, ());
-- 與GROUP BY等價關係
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b
UNION
SELECT null, null, SUM( c ) FROM tab1;

當使用聚合時，有時候會出現數據本身爲 null 值，很難區分究竟是數據列本身爲 null 值還是聚合數據行爲 null，即無法區分查詢結果中的 null 值是屬於列本身的還是聚合的結果行，因此需要一種方法識別出列中的 null 值。grouping_id 函數就是此場景下的解決方案。注意該函數是有兩個下劃線。這個函數爲每種聚合數據行生成唯一的組 id。它的返回值看起來像整型數值，其實是字符串類型，這個值使用了位圖策略（bitvector，位向量），即它的二進制形式中的每一位表示對應列是否參與分組，如果某一列參與了分組，對應位就被置爲 1，否則爲 0。通過這種方式可以區分出數據本身中的 null 值。看到這是不是還是一頭霧水，沒關係，來看下面的示例：

-- 創建表
create table test_grouping__id(id int,amount decimal(10,2));
-- 插入數據
insert into table test_grouping__id values(1,null),(1,1),(2,2),(3,3),(3,null),(4,5);
--執行查詢
SELECT id,
       amount,
       grouping__id,
       count(*) cnt
FROM test_grouping__id
GROUP BY id,
         amount
GROUPING sets(id,(id,amount),())
ORDER BY grouping__id

查詢結果分析

查詢結果如下圖所示：綠色框表示未進行分組，即進行全局聚合，grouping_id 等於 0，表示沒有字段參與分組。藍色框表示按照 id 進行分組，對應的 grouping_id 爲 1，表示只有一個字段參與了分組。橘色的框表示按照 id 和 amount 兩個字段進行分組，grouping_id 爲 3，即有兩個字段參與了分組，轉成十進制爲 2^0 + 2^1  = 3。

以上面爲例，分組字段爲 id、amount，轉成二進制表示形式爲：

通用的語法爲 WITH ROLLUP, 需要與 group by 一起用於在維的層次結構級別上計算聚合。功能爲可以按照 group by 的分組字段進行組合，計算出不同分組的結果。注意對於分組字段的組合會與最左邊的字段爲主。使用 ROLLUP 的 GROUP BY a，b，c 假定層次結構是 “a” 向下鑽取到 “ b”，“ b” 向下鑽取到“ c”。則可以通過 GROUP BY a，b，c，WITH ROLLUP 進行實現，該語句等價於 GROUP BY a，b，c GROUPING SETS（（a，b，c），（a，b），（a），（））。即使用 WITH ROLLUP，首先會對全局聚合(不分組)，然後會按 GROUP BY 字段組合，進行聚合，但是最左側的分組字段必須參與分組，比如 a 字段是最左側的字段，則 a 必定參與分組組合。

示例模板

-- 使用WITH ROLLUP查詢
SELECT a,
       b,
       c
       SUM(d)
FROM tab1
GROUP BY a,b,c
WITH ROLLUP
-- 等價於下面的方式
SELECT a,
       b,
       c,
       SUM(d)
FROM tab1
GROUP BY a,b,c
GROUPING SETS ((a,b,c), (a,b), (a),());

CUBE 表示一個立方體，apache 的 kylin 使用就是這種預計算方式。即會對給定的維度 (分組字段) 進行多種組合之後，形成不同分組規則的數據結果。一旦我們在一組維度上計算出 CUBE，就可以得到這些維度上所有可能的聚合聚合結果。比如：GROUP BY a，b，c WITH CUBE，等價於 GROUP BY a，b，c GROUPING SETS（（a，b，c），（a，b），（b，c）， （a，c），（a），（b），（c），（））。

其實，可以將上面的情況抽象成排列組合的問題，即從分組字段集合 (假設有 n 個字段) 中隨意取出 0~n 個字段，那麼會有多少中組合方式，如下面公式所示：

結合上面的例子，GROUP BY a，b，c WITH CUBE，那麼所有的組合方式有：（a，b，c），（a，b），（b，c）， （a，c），（a），（b），（c），（）, 一共有 8 種組合，即 2^3 = 8。

示例模板

-- 使用WITH CUBE查詢
SELECT a,
       b,
       c
       SUM(d)
FROM tab1
GROUP BY a,b,c
WITH CUBE
-- 等價於下面的方式
SELECT a,
       b,
       c,
       SUM(d)
FROM tab1
GROUP BY a,b,c
GROUPING SETS ((a,b,c),(a,b),(b,c), (a,c),(a),(b),(c),());

使用案例

數據準備

有一份用戶行爲數據集，包括用戶的所有行爲（包括 pv 點擊、buy 購買、cart 加購、fav 收藏），具體如下表所示：

-- 創建表
CREATE TABLE user_behavior
             (
                user_id int ,
                item_id int,
                category_id int,
                behavior string,
               access_time string
               )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 裝載數據
1,101,1,pv,1511658000
2,102,1,pv,1511658000
3,103,1,pv,1511658000
4,104,2,cart,1511659329
5,105,2,buy,1511659326
6,106,3,fav,1511659323
7,101,1,pv,1511658010
8,102,1,buy,1511658200
9,103,1,cart,1511658030
10,107,3,fav,1511659332

GROUPING SETS 使用

-- 查詢每種商品品類、每種用戶行爲的訪問次數
-- 查詢每種用戶行爲的訪問次數
SELECT 
item_id,
category_id,
behavior,
COUNT(*) AS cnt,
GROUPING__ID 
FROM user_behavior 
GROUP BY item_id,category_id,behavior 
GROUPING SETS ((category_id,behavior),behavior)
ORDER BY GROUPING__ID;

結果如下：

ROLLUP 使用

-- 查詢每種商品品類的訪問次數
-- 查詢每種商品品類、每種用戶行爲的次數
-- 查詢用戶的總訪問次數
SELECT 
category_id,
behavior,
COUNT(*) AS cnt,
GROUPING__ID 
FROM user_behavior 
GROUP BY category_id,behavior 
WITH ROLLUP
ORDER BY GROUPING__ID;

結果如下：

CUBE 使用

-- 查詢每種商品品類的訪問次數
-- 查詢每種用戶行爲的次數
-- 查詢每種商品品類、每種用戶行爲的次數
-- 查詢用戶的總訪問次數
SELECT 
category_id,
behavior,
COUNT(*) AS cnt,
GROUPING__ID 
FROM user_behavior 
GROUP BY category_id,behavior 
WITH CUBE
ORDER BY GROUPING__ID;

結果如下：

總結

本文首先介紹了什麼是 OLAP，接着介紹 Hive 中提供的幾種 OLAP 分析的函數，並對每一種函數進行了詳細說明，並給出了相關的圖示解釋，最後以一個案例說明了這幾種函數的使用方式，可以進一步加深理解。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/Yjt8ySfJdwviiTIi9ffmFw

猜你喜歡