如何使用HiveSQL進(jìn)行OLAP分析

點(diǎn)擊上方“數(shù)據(jù)管道”，選擇“置頂星標(biāo)”公眾號(hào)

干貨福利，第一時(shí)間送達(dá)

在線分析處理(OLAP,Online Analytical Processing)是通過(guò)帶層次的維度和跨維度進(jìn)行多維分析的，簡(jiǎn)單理解為一種多維數(shù)據(jù)分析的方式，通過(guò)OLAP可以展示數(shù)據(jù)倉(cāng)庫(kù)中數(shù)據(jù)的多維邏輯視圖。在多維分析中，數(shù)據(jù)是按照維度(觀察數(shù)據(jù)的角度)來(lái)表示的，比如商品、城市、客戶。而維通常按層次(層次維度)組織的，如城市、省、國(guó)家，再比如時(shí)間也是有層次的，如天、周、月、季度和年。不同的管理者可以從不同的維度(視角)去觀察這些數(shù)據(jù)，這些在多個(gè)不同維度上對(duì)數(shù)據(jù)進(jìn)行綜合考察的手段就是通常所說(shuō)的數(shù)據(jù)倉(cāng)庫(kù)多維查詢，最常見(jiàn)的就如上卷(roll-up)和下鉆(drill-down)了,所謂上卷，指的是選定特定的數(shù)據(jù)范圍之后，對(duì)其進(jìn)行匯總統(tǒng)計(jì)以獲取更高層次的信息。所謂下鉆，指的是選定特定的數(shù)據(jù)范圍之后，需要進(jìn)一步查看細(xì)節(jié)的數(shù)據(jù)。從另一種意義上說(shuō)，鉆取就是針對(duì)多維展現(xiàn)的數(shù)據(jù)，進(jìn)一步探究其內(nèi)部的組成和來(lái)源。值得注意的是，上卷和下鉆要求維度具有層級(jí)結(jié)構(gòu)，即數(shù)倉(cāng)中所說(shuō)的層次維度。

Hive提供了多維數(shù)據(jù)分析的函數(shù)，如GROUPING SETS,GROUPING_ID,CUBE,ROLLUP,通過(guò)這些分析函數(shù)，可以輕而易舉的實(shí)現(xiàn)多維數(shù)據(jù)分析。下面將會(huì)通過(guò)一個(gè)案例來(lái)了解這些函數(shù)的具體含義以及該如何使用這些函數(shù)。注意：在hive中使用這些函數(shù)之前，要確保開(kāi)啟了map端聚合，即set hive.map.aggr=true，否則會(huì)報(bào)如下錯(cuò)誤：

簡(jiǎn)單介紹

• GROUPING SETS

在一個(gè)group by查詢中，通過(guò)該子句可以對(duì)不同維度或同一維度的不同層次進(jìn)行聚合，簡(jiǎn)單理解為一條sql可以實(shí)現(xiàn)多種不同的分組規(guī)則，用戶可以在該函數(shù)中傳入自己定義的多種分組字段，本質(zhì)上等價(jià)于多個(gè)group by語(yǔ)句進(jìn)行UNION，對(duì)于GROUPING SETS子句中的空白集'（）'表示對(duì)總體進(jìn)行聚集。

示例模板

--?使用GROUPING?SETS查詢
SELECT?a,
???????b,
???????SUM(c)
FROM?tab1
GROUP?BY?a,b
GROUPING?SETS?((a,b),?a,b,?());
--?與GROUP?BY等價(jià)關(guān)系
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;

• GROUPING__ID

當(dāng)使用聚合時(shí)，有時(shí)候會(huì)出現(xiàn)數(shù)據(jù)本身為null值，很難區(qū)分究竟是數(shù)據(jù)列本身為null值還是聚合數(shù)據(jù)行為null，即無(wú)法區(qū)分查詢結(jié)果中的null值是屬于列本身的還是聚合的結(jié)果行，因此需要一種方法識(shí)別出列中的null值。grouping_id 函數(shù)就是此場(chǎng)景下的解決方案。注意該函數(shù)是有兩個(gè)下劃線。這個(gè)函數(shù)為每種聚合數(shù)據(jù)行生成唯一的組id。它的返回值看起來(lái)像整型數(shù)值，其實(shí)是字符串類型，這個(gè)值使用了位圖策略（bitvector，位向量），即它的二進(jìn)制形式中的每一位表示對(duì)應(yīng)列是否參與分組，如果某一列參與了分組，對(duì)應(yīng)位就被置為1，否則為0。通過(guò)這種方式可以區(qū)分出數(shù)據(jù)本身中的null值。看到這是不是還是一頭霧水，沒(méi)關(guān)系，來(lái)看下面的示例：

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

查詢結(jié)果分析

查詢結(jié)果如下圖所示：綠色框表示未進(jìn)行分組，即進(jìn)行全局聚合，grouping_id等于0，表示沒(méi)有字段參與分組。藍(lán)色框表示按照id進(jìn)行分組，對(duì)應(yīng)的grouping_id為1，表示只有一個(gè)字段參與了分組。橘色的框表示按照id和amount兩個(gè)字段進(jìn)行分組，grouping_id為3，即有兩個(gè)字段參與了分組，轉(zhuǎn)成十進(jìn)制為2^0 + 2^1 ?= 3。

以上面為例，分組字段為id、amount，轉(zhuǎn)成二進(jìn)制表示形式為：

• ROLLUP

通用的語(yǔ)法為WITH ROLLUP,需要與group by一起用于在維的層次結(jié)構(gòu)級(jí)別上計(jì)算聚合。功能為可以按照group by的分組字段進(jìn)行組合，計(jì)算出不同分組的結(jié)果。注意對(duì)于分組字段的組合會(huì)與最左邊的字段為主。使用ROLLUP的GROUP BY a，b，c假定層次結(jié)構(gòu)是“ a”向下鉆取到“ b”，“ b”向下鉆取到“ c”。則可以通過(guò)GROUP BY a，b，c，WITH ROLLUP進(jìn)行實(shí)現(xiàn)，該語(yǔ)句等價(jià)于GROUP BY a，b，c GROUPING SETS（（a，b，c），（a，b），（a），（））。即使用WITH ROLLUP，首先會(huì)對(duì)全局聚合(不分組)，然后會(huì)按GROUP BY字段組合，進(jìn)行聚合，但是最左側(cè)的分組字段必須參與分組，比如a字段是最左側(cè)的字段，則a必定參與分組組合。

示例模板

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

• CUBE

CUBE表示一個(gè)立方體，apache的kylin使用就是這種預(yù)計(jì)算方式。即會(huì)對(duì)給定的維度(分組字段)進(jìn)行多種組合之后，形成不同分組規(guī)則的數(shù)據(jù)結(jié)果。一旦我們?cè)谝唤M維度上計(jì)算出CUBE，就可以得到這些維度上所有可能的聚合聚合結(jié)果。比如：GROUP BY a，b，c WITH CUBE，等價(jià)于GROUP BY a，b，c GROUPING SETS（（a，b，c），（a，b），（b，c）， （a，c），（a），（b），（c），（））。

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

結(jié)合上面的例子，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
--?等價(jià)于下面的方式
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),());

使用案例

數(shù)據(jù)準(zhǔn)備

有一份用戶行為數(shù)據(jù)集，包括用戶的所有行為（包括pv點(diǎn)擊、buy購(gòu)買、cart加購(gòu)、fav收藏），具體如下表所示：

--?創(chuàng)建表
CREATE?TABLE?user_behavior
?????????????(
????????????????user_id?int?,
????????????????item_id?int,
????????????????category_id?int,
????????????????behavior?string,
???????????????access_time?string
???????????????)
ROW?FORMAT?DELIMITED?FIELDS?TERMINATED?BY?',';
--?裝載數(shù)據(jù)
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使用

--?查詢每種商品品類、每種用戶行為的訪問(wèn)次數(shù)
--?查詢每種用戶行為的訪問(wèn)次數(shù)
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;

結(jié)果如下：

ROLLUP使用

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

結(jié)果如下：

CUBE使用

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

結(jié)果如下：

總結(jié)

本文首先介紹了什么是OLAP，接著介紹Hive中提供的幾種OLAP分析的函數(shù)，并對(duì)每一種函數(shù)進(jìn)行了詳細(xì)說(shuō)明，并給出了相關(guān)的圖示解釋，最后以一個(gè)案例說(shuō)明了這幾種函數(shù)的使用方式，可以進(jìn)一步加深理解.