1. 項(xiàng)目概述從“多維度報(bào)表”的痛點(diǎn)說(shuō)起做數(shù)據(jù)開(kāi)發(fā)或者數(shù)據(jù)分析的朋友對(duì)“多維分析”這個(gè)詞一定不陌生。簡(jiǎn)單來(lái)說(shuō)就是你需要從不同維度組合去觀察同一份數(shù)據(jù)。舉個(gè)最經(jīng)典的例子一份銷售數(shù)據(jù)老板可能想看全國(guó)的總銷售額一個(gè)維度也可能想看每個(gè)省份的總銷售額另一個(gè)維度還想看每個(gè)省份下每個(gè)城市的總銷售額兩個(gè)維度的組合甚至想看所有維度的總計(jì)。如果維度多了比如加上產(chǎn)品類別、銷售渠道、時(shí)間年/月這個(gè)組合數(shù)會(huì)呈指數(shù)級(jí)增長(zhǎng)。傳統(tǒng)的做法是什么寫(xiě)多個(gè)GROUP BY語(yǔ)句然后用UNION ALL拼起來(lái)。我敢說(shuō)但凡寫(xiě)過(guò)這種SQL的人都經(jīng)歷過(guò)代碼冗長(zhǎng)、維護(hù)困難、執(zhí)行效率低下的折磨。GROUPING SETS就是Hive以及標(biāo)準(zhǔn)SQL中為了解決這個(gè)“多維聚合”痛點(diǎn)而生的利器。它允許你在一個(gè)GROUP BY子句中指定多個(gè)不同的分組集合Hive會(huì)一次性計(jì)算出所有指定分組的結(jié)果。而GROUPING_ID函數(shù)則是這個(gè)過(guò)程中的“導(dǎo)航員”和“驗(yàn)票員”它生成一個(gè)標(biāo)識(shí)位告訴你當(dāng)前結(jié)果行是由哪個(gè)分組集合產(chǎn)生的這對(duì)于區(qū)分和解析聚合結(jié)果至關(guān)重要。理解并熟練運(yùn)用這對(duì)組合能讓你從繁瑣的UNION ALL中解放出來(lái)寫(xiě)出更簡(jiǎn)潔、更高效、更易維護(hù)的聚合查詢尤其是在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)的匯總層或直接生成多維報(bào)表時(shí)效率提升立竿見(jiàn)影。2. GROUPING SETS 核心原理與語(yǔ)法拆解2.1 它到底解決了什么問(wèn)題在深入語(yǔ)法之前我們先用一個(gè)場(chǎng)景把問(wèn)題具象化。假設(shè)有一張銷售表sales字段包括region地區(qū)、city城市、product產(chǎn)品、amount銷售額。現(xiàn)在需要出三個(gè)報(bào)表按region匯總銷售額。按region, city匯總銷售額。所有數(shù)據(jù)的總銷售額。用傳統(tǒng)方法SQL會(huì)寫(xiě)成這樣SELECT region, NULL as city, SUM(amount) as total_amount FROM sales GROUP BY region UNION ALL SELECT region, city, SUM(amount) as total_amount FROM sales GROUP BY region, city UNION ALL SELECT NULL as region, NULL as city, SUM(amount) as total_amount FROM sales;這還只是三個(gè)簡(jiǎn)單的組合。如果維度增加到4個(gè)需要ROLLUP或CUBE后面會(huì)提到效果代碼量會(huì)爆炸。更糟糕的是表sales會(huì)被掃描多次如果數(shù)據(jù)量巨大性能開(kāi)銷非常可觀。GROUPING SETS的核心思想是“一次掃描多組聚合”。它告訴Hive“請(qǐng)你掃描一次數(shù)據(jù)然后按照我給的這幾套分組規(guī)則分別計(jì)算聚合結(jié)果最后把結(jié)果拼在一起返回給我。”2.2 基礎(chǔ)語(yǔ)法與執(zhí)行邏輯GROUPING SETS的語(yǔ)法是作為GROUP BY子句的擴(kuò)展出現(xiàn)的。SELECT column1, column2, ..., aggregate_function(column) FROM table_name GROUP BY column1, column2, ... GROUPING SETS ( (column1, column2, ...), -- 分組集合1 (column1), -- 分組集合2 (column2), -- 分組集合3 () -- 分組集合4空集表示全局匯總 );執(zhí)行邏輯分解解析階段Hive解析SQL識(shí)別出GROUP BY子句中包含了GROUPING SETS以及其中定義的具體分組集合列表。任務(wù)規(guī)劃Hive會(huì)生成一個(gè)MapReduce或Tez作業(yè)。雖然邏輯上是“一次掃描”但在物理執(zhí)行計(jì)劃中它可能會(huì)為不同的分組集安排不同的Reducer任務(wù)但關(guān)鍵的優(yōu)化在于Map階段通?？梢怨灿眉粗蛔x取一次源數(shù)據(jù)然后為不同的分組鍵組合分發(fā)數(shù)據(jù)。數(shù)據(jù)分發(fā)與聚合在Shuffle階段數(shù)據(jù)會(huì)根據(jù)GROUPING SETS中所有涉及的分組鍵的組合進(jìn)行分區(qū)和排序發(fā)送到相應(yīng)的Reducer。每個(gè)Reducer負(fù)責(zé)計(jì)算一個(gè)或多個(gè)分組集合的結(jié)果。結(jié)果合并所有分組集合的計(jì)算結(jié)果會(huì)被合并成一個(gè)結(jié)果集返回。對(duì)于某些未參與當(dāng)前行分組計(jì)算的列其值會(huì)顯示為NULL。拿上面的銷售例子用GROUPING SETS重寫(xiě)SELECT region, city, SUM(amount) as total_amount FROM sales GROUP BY region, city GROUPING SETS ( (region, city), -- 按地區(qū)和城市分組 (region), -- 僅按地區(qū)分組 () -- 全局總計(jì) );這個(gè)查詢會(huì)返回三部分結(jié)果(region, city)的明細(xì)聚合、(region)的匯總、以及最后的()總計(jì)。城市city在僅按region分組和全局總計(jì)的行中值為NULL。注意GROUPING SETS中指定的分組集合必須是GROUP BY后面列的子集。例如GROUP BY a, b, c那么GROUPING SETS里可以是(a,b), (a), (c)但不能出現(xiàn)(a,b,d)因?yàn)閐不在GROUP BY的列中。2.3 特殊形式ROLLUP 和 CUBEGROUPING SETS有兩個(gè)常用的簡(jiǎn)寫(xiě)形式它們代表了兩種經(jīng)典的多維分析模式。1. ROLLUP層級(jí)上卷聚合ROLLUP假設(shè)維度之間有層級(jí)關(guān)系如年月日國(guó)家省市它生成從最細(xì)粒度到最粗粒度的一系列分組。語(yǔ)法是GROUP BY ROLLUP(a, b, c)。它等價(jià)于GROUPING SETS ( (a, b, c), -- 最細(xì)粒度 (a, b), -- 上卷一層 (a), -- 再上卷一層 () -- 全局總計(jì) )執(zhí)行順序是從右向左“上卷”。ROLLUP(a,b,c)會(huì)先按(a,b,c)分組然后“卷起”c按(a,b)分組再“卷起”b按(a)分組最后全卷起來(lái)做總計(jì)。這在做財(cái)務(wù)或管理報(bào)表時(shí)非常常用。2. CUBE全維度組合聚合CUBE比ROLLUP更徹底它生成指定維度所有可能的組合。語(yǔ)法是GROUP BY CUBE(a, b, c)。它等價(jià)于GROUPING SETS ( (a, b, c), -- 三維組合 (a, b), (a, c), (b, c), -- 所有兩維組合 (a), (b), (c), -- 所有單維組合 () -- 全局總計(jì) )如果維度是n個(gè)CUBE會(huì)產(chǎn)生2^n個(gè)分組集合。CUBE適合用于探索性數(shù)據(jù)分析你不知道哪些維度組合是關(guān)鍵那就把所有組合都算出來(lái)看看。但代價(jià)是計(jì)算量和結(jié)果集大小會(huì)急劇膨脹使用時(shí)需謹(jǐn)慎評(píng)估。實(shí)操心得在資源允許的情況下用CUBE做一次性的全維度探查非常高效。但對(duì)于定期跑的報(bào)表任務(wù)通常更推薦用ROLLUP或明確的GROUPING SETS因?yàn)樗鼈兏蠘I(yè)務(wù)邏輯的層級(jí)且計(jì)算量更可控。永遠(yuǎn)不要為了炫技而濫用CUBE。3. GROUPING_ID 函數(shù)聚合結(jié)果的“身份證”當(dāng)使用GROUPING SETS、ROLLUP或CUBE時(shí)結(jié)果集中會(huì)混入來(lái)自不同分組集合的行。由于未參與分組的列會(huì)顯示為NULL這就帶來(lái)一個(gè)問(wèn)題這個(gè)NULL值到底是數(shù)據(jù)本身是NULL還是因?yàn)榫酆袭a(chǎn)生的NULL我們又如何快速區(qū)分某一行是屬于哪個(gè)分組集合的結(jié)果這就是GROUPING_ID函數(shù)大顯身手的地方。3.1 GROUPING_ID 的計(jì)算原理GROUPING_ID函數(shù)接受一列或多列作為參數(shù)返回一個(gè)整數(shù)。這個(gè)整數(shù)的二進(jìn)制表示精確地刻畫(huà)了當(dāng)前結(jié)果行中哪些列是參與聚合的對(duì)應(yīng)位為0哪些列是因?yàn)榫酆隙恢脼镹ULL的對(duì)應(yīng)位為1。計(jì)算步驟確定列的順序順序與GROUP BY子句中列的出現(xiàn)順序一致或者與GROUPING_ID函數(shù)參數(shù)中列的順序一致通常兩者一致。假設(shè)GROUP BY a, b, c那么順序就是a(最高位)、b、c(最低位)。逐列判斷對(duì)于結(jié)果集中的每一行依次檢查每個(gè)列。如果該列在生成當(dāng)前行的分組集合中被使用了即參與了分組則對(duì)應(yīng)二進(jìn)制位為0。如果該列在生成當(dāng)前行的分組集合中未被使用因此在結(jié)果中為NULL則對(duì)應(yīng)二進(jìn)制位為1。生成整數(shù)將這個(gè)二進(jìn)制串轉(zhuǎn)換為十進(jìn)制整數(shù)即為GROUPING_ID的值。舉例說(shuō)明 沿用sales表GROUP BY region, city。對(duì)于按(region, city)分組的結(jié)果行region和city都參與了分組所以二進(jìn)制位是region0, city0二進(jìn)制00十進(jìn)制0。對(duì)于按(region)分組的結(jié)果行region參與分組0city未參與1二進(jìn)制01十進(jìn)制1。對(duì)于全局總計(jì)()region和city都未參與二進(jìn)制11十進(jìn)制3。SELECT region, city, SUM(amount) as total_amount, GROUPING_ID(region, city) as grouping_id FROM sales GROUP BY region, city GROUPING SETS ( (region, city), (region), () );結(jié)果會(huì)多出一列g(shù)rouping_id值分別為0, 1, 3。3.2 如何利用 GROUPING_ID 進(jìn)行結(jié)果過(guò)濾與標(biāo)識(shí)知道grouping_id的值后我們可以做很多有用的事情1. 精準(zhǔn)篩選特定聚合層級(jí)的結(jié)果假設(shè)我只想要按region匯總的結(jié)果grouping_id1SELECT ... FROM ... GROUP BY ... GROUPING SETS (...) HAVING GROUPING_ID(region, city) 1; -- 或者在外層包裝子查詢后用WHERE過(guò)濾這在將不同粒度的結(jié)果輸出到不同目的地時(shí)非常有用。2. 清晰標(biāo)識(shí)聚合行的含義我們可以在查詢中使用CASE WHEN根據(jù)grouping_id為聚合行生成更易讀的標(biāo)簽。SELECT CASE WHEN GROUPING_ID(region, city) 3 THEN 總計(jì) WHEN GROUPING_ID(region, city) 1 THEN region || 地區(qū)匯總 ELSE region END as region_label, CASE WHEN GROUPING_ID(region, city) IN (1,3) THEN N/A ELSE city END as city_label, SUM(amount) as total_amount FROM sales GROUP BY region, city GROUPING SETS ((region, city), (region), ());這樣最終報(bào)表的閱讀者就能一眼看出每一行數(shù)據(jù)的含義。3. 區(qū)分真實(shí)NULL與聚合NULL這是GROUPING_ID另一個(gè)關(guān)鍵用途。如果原始數(shù)據(jù)中city字段本身就有NULL值那么按(region, city)分組時(shí)city為NULL的行也會(huì)被單獨(dú)分組。此時(shí)GROUPING_ID可以幫助我們區(qū)分grouping_id0且city IS NULL這是數(shù)據(jù)中真實(shí)的NULL城市形成的分組。grouping_id1這是按region匯總行city列的NULL是聚合產(chǎn)生的。注意事項(xiàng)GROUPING_ID函數(shù)在Hive的不同版本中其參數(shù)順序的敏感性可能略有差異。最穩(wěn)妥的做法是確保傳入GROUPING_ID的列順序與GROUP BY子句中列的順序完全一致。雖然通常只傳入GROUP BY的所有列但你也可以傳入一個(gè)子集此時(shí)返回的ID是基于這個(gè)子集列計(jì)算的這在復(fù)雜場(chǎng)景下可能有用但容易混淆建議初學(xué)者保持順序和列數(shù)一致。4. 高級(jí)用法與性能優(yōu)化實(shí)戰(zhàn)掌握了基礎(chǔ)我們來(lái)看看如何在復(fù)雜場(chǎng)景和性能敏感的環(huán)境中使用它們。4.1 復(fù)雜維度組合與自定義GROUPING SETSGROUPING SETS的強(qiáng)大之處在于它的靈活性。你不僅可以做標(biāo)準(zhǔn)的ROLLUP和CUBE還可以定義任何你需要的分組組合。場(chǎng)景除了常規(guī)的地區(qū)、城市匯總老板還想額外看幾個(gè)重點(diǎn)城市如‘北京’ ‘上?！?‘廣州’各自的總銷售額以及所有重點(diǎn)城市加起來(lái)的總和。SELECT region, city, SUM(amount) as total_amount, GROUPING_ID(region, city) as gid FROM sales GROUP BY region, city GROUPING SETS ( (region, city), -- 標(biāo)準(zhǔn)明細(xì) (region), -- 地區(qū)匯總 (), -- 全局總計(jì) (city) -- 額外按城市匯總跨地區(qū) ) HAVING (GROUPING_ID(region, city) ! 0 AND GROUPING_ID(region, city) ! 2) -- 排除按city單獨(dú)分組中region為真實(shí)NULL的行 OR city IN (北京, 上海, 廣州); -- 保留我們關(guān)心的重點(diǎn)城市明細(xì)這個(gè)查詢通過(guò)自定義GROUPING SETS增加了(city)這個(gè)分組然后通過(guò)HAVING子句進(jìn)行復(fù)雜過(guò)濾實(shí)現(xiàn)了混合粒度的查詢需求。4.2 與其它高級(jí)分組函數(shù)配合使用GROUPING SETS常與GROUPING_ID配合也可以和其它窗口函數(shù)、分析函數(shù)結(jié)合實(shí)現(xiàn)更復(fù)雜的邏輯。場(chǎng)景計(jì)算每個(gè)地區(qū)銷售額占比同時(shí)也要顯示各級(jí)匯總行的占比。SELECT region, city, total_amount, gid, -- 使用窗口函數(shù)根據(jù)不同的grouping_id選擇不同的分區(qū)基準(zhǔn)計(jì)算占比 CASE WHEN gid 0 THEN total_amount / SUM(total_amount) OVER(PARTITION BY region) WHEN gid 1 THEN total_amount / SUM(total_amount) OVER() ELSE NULL END as ratio FROM ( SELECT region, city, SUM(amount) as total_amount, GROUPING_ID(region, city) as gid FROM sales GROUP BY region, city GROUPING SETS ((region, city), (region)) ) t;這個(gè)例子在子查詢中先進(jìn)行多維度聚合然后在外層利用grouping_idgid作為條件使用窗口函數(shù)SUM() OVER()針對(duì)不同的聚合層級(jí)計(jì)算占比。4.3 性能考量與調(diào)優(yōu)技巧雖然GROUPING SETS減少了查詢語(yǔ)句的復(fù)雜度但并沒(méi)有減少計(jì)算量。它仍然需要計(jì)算所有指定分組集合的聚合。以下是一些性能優(yōu)化的關(guān)鍵點(diǎn)減少不必要的維度在CUBE或大的GROUPING SETS中仔細(xì)評(píng)估每個(gè)維度組合的業(yè)務(wù)價(jià)值。去掉那些明顯無(wú)用或過(guò)于細(xì)分的組合。能用ROLLUP就不用CUBE。利用中間聚合層預(yù)聚合如果源表數(shù)據(jù)量極大數(shù)十億行直接在其上進(jìn)行多維度GROUPING SETS計(jì)算可能非常慢。一個(gè)常見(jiàn)的優(yōu)化模式是第一層在ETL過(guò)程中先按最細(xì)粒度例如(region, city, product, day)進(jìn)行聚合將結(jié)果存入一張中間匯總表。這個(gè)聚合可以每天或每小時(shí)進(jìn)行一次。第二層業(yè)務(wù)查詢或報(bào)表直接從這張中間匯總表上使用GROUPING SETS進(jìn)行上卷聚合。因?yàn)閿?shù)據(jù)已經(jīng)過(guò)預(yù)聚合行數(shù)大大減少查詢速度會(huì)得到質(zhì)的提升。關(guān)注數(shù)據(jù)傾斜GROUPING SETS可能會(huì)改變數(shù)據(jù)在Reduce階段的分發(fā)方式。如果某個(gè)維度的值非常集中例如90%的數(shù)據(jù)city都是‘未知’那么在計(jì)算GROUPING SETS中包含該維度的組合時(shí)可能導(dǎo)致嚴(yán)重的Reduce端數(shù)據(jù)傾斜。需要監(jiān)控作業(yè)運(yùn)行情況考慮使用set hive.groupby.skewindatatrue;Hive舊版本或優(yōu)化分組鍵。合理設(shè)置Reduce數(shù)量GROUPING SETS可能會(huì)生成比普通GROUP BY更多的Reduce任務(wù)。需要根據(jù)分組集合的數(shù)量和數(shù)據(jù)的分布情況合理設(shè)置mapreduce.job.reduces或tez.grouping.max-size等參數(shù)避免Reduce任務(wù)過(guò)多或過(guò)少。實(shí)操心得對(duì)于超大型表的GROUPING SETS查詢我個(gè)人的經(jīng)驗(yàn)是預(yù)聚合是性價(jià)比最高的優(yōu)化手段。犧牲一部分存儲(chǔ)空間換取查詢響應(yīng)時(shí)間的指數(shù)級(jí)下降在數(shù)據(jù)倉(cāng)庫(kù)建設(shè)中是非常劃算的。在設(shè)計(jì)中間匯總表時(shí)要仔細(xì)選擇聚合的粒度它應(yīng)該能滿足絕大多數(shù)上卷查詢的需求同時(shí)又不至于讓表本身過(guò)大。5. 常見(jiàn)問(wèn)題排查與避坑指南在實(shí)際使用中你肯定會(huì)遇到一些意想不到的情況。這里我總結(jié)了一些典型問(wèn)題和解決方法。5.1 結(jié)果中NULL值的混淆問(wèn)題這是新手最常踩的坑。GROUPING SETS產(chǎn)生的NULL和數(shù)據(jù)的NULL混在一起。問(wèn)題現(xiàn)象你按(a, b)分組結(jié)果里有一行(NULL, ‘value’)。這到底是a列本身為NULL的數(shù)據(jù)行還是按b列單獨(dú)分組產(chǎn)生的結(jié)果行解決方案使用GROUPING函數(shù)Hive提供了GROUPING(col)函數(shù)它針對(duì)單列如果該列的NULL是由聚合產(chǎn)生則返回1否則返回0。你可以用CASE WHEN GROUPING(a) 1 THEN ‘Aggregate_NULL’ ELSE a END來(lái)區(qū)分。使用GROUPING_ID如前所述GROUPING_ID是更全面的解決方案。通過(guò)計(jì)算出的ID值你可以明確知道當(dāng)前行的分組構(gòu)成。數(shù)據(jù)預(yù)處理在聚合前將數(shù)據(jù)中的NULL值替換為一個(gè)業(yè)務(wù)中不可能出現(xiàn)的特殊值如‘N/A’ ‘UNKNOWN’。這樣結(jié)果中所有的NULL就都是聚合產(chǎn)生的了。聚合完成后如果需要再將這些特殊值轉(zhuǎn)換回NULL。這種方法邏輯清晰但增加了ETL步驟。5.2 GROUPING_ID計(jì)算結(jié)果與預(yù)期不符可能原因及排查列順序不一致確保GROUPING_ID函數(shù)參數(shù)的列順序與GROUP BY子句中列的書(shū)寫(xiě)順序完全一致。GROUP BY a, b, c與GROUPING_ID(c, a, b)計(jì)算出的ID天差地別。使用了不在GROUP BY中的列GROUPING_ID的參數(shù)列必須是GROUP BY子句中列的子集。如果傳入未在GROUP BY中出現(xiàn)的列行為是未定義的通常會(huì)導(dǎo)致錯(cuò)誤或意外結(jié)果。Hive版本差異極少數(shù)情況下不同Hive版本對(duì)GROUPING SETS和GROUPING_ID的實(shí)現(xiàn)可能有細(xì)微差別。如果遷移環(huán)境后出現(xiàn)問(wèn)題檢查版本發(fā)行說(shuō)明。5.3 性能突然變慢排查思路檢查輸入數(shù)據(jù)量是否源表數(shù)據(jù)量暴增是否分區(qū)過(guò)濾條件失效導(dǎo)致全表掃描檢查分組集合數(shù)量是否無(wú)意中使用了CUBE且維度很多2^n的增長(zhǎng)是非?？植赖摹；仡櫂I(yè)務(wù)需求是否真的需要所有組合。檢查數(shù)據(jù)傾斜查看作業(yè)日志是否某個(gè)Reduce任務(wù)運(yùn)行時(shí)間遠(yuǎn)長(zhǎng)于其他任務(wù)?？梢允褂肧ELECT col, COUNT(*) FROM table GROUP BY col ORDER BY COUNT(*) DESC LIMIT 10;來(lái)檢查分組鍵的分布是否均勻。檢查資源配置是否與其他重任務(wù)擠占了集群資源Reduce數(shù)量設(shè)置是否合理5.4 與Hive其他特性結(jié)合時(shí)的注意事項(xiàng)與DISTRIBUTE BY / SORT BY 結(jié)合在Hive中你可以在GROUP BY后使用DISTRIBUTE BY和SORT BY來(lái)控制數(shù)據(jù)分發(fā)和排序。但和GROUPING SETS結(jié)合時(shí)需小心這可能會(huì)干擾Hive為多分組集優(yōu)化的數(shù)據(jù)分發(fā)邏輯通常不建議混用。與動(dòng)態(tài)分區(qū)插入結(jié)合如果你想將GROUPING SETS的結(jié)果寫(xiě)入不同的Hive分區(qū)邏輯上可行但操作復(fù)雜。通常的做法是先插入到一張臨時(shí)表然后再根據(jù)grouping_id或其他條件使用多條INSERT OVERWRITE語(yǔ)句將數(shù)據(jù)分發(fā)到不同的目標(biāo)分區(qū)。在視圖或子查詢中GROUPING SETS可以用于創(chuàng)建視圖或子查詢。但要確保外層查詢能正確處理由聚合產(chǎn)生的NULL值。在視圖定義中清晰說(shuō)明各列含義是個(gè)好習(xí)慣。避坑技巧在開(kāi)發(fā)復(fù)雜GROUPING SETS查詢時(shí)我習(xí)慣遵循“先簡(jiǎn)后繁”的原則。先在一個(gè)小的測(cè)試數(shù)據(jù)集上用最簡(jiǎn)單的GROUPING SETS比如一兩個(gè)維度驗(yàn)證邏輯和GROUPING_ID的計(jì)算是否正確。然后逐步增加維度、增加分組集合、添加過(guò)濾條件。每一步都確認(rèn)結(jié)果符合預(yù)期。這樣能快速定位問(wèn)題是在哪個(gè)環(huán)節(jié)引入的。另外為這類查詢的產(chǎn)出表增加一個(gè)grouping_id列是極其有用的它就像數(shù)據(jù)的元信息為后續(xù)的數(shù)據(jù)核對(duì)、異常排查和下游消費(fèi)提供了清晰的依據(jù)。