什麼是hive的高級分組聚合，它的用法和註意事項以及性能分析

来源:https://www.cnblogs.com/lubians/archive/2023/06/29/17514776.html
-Advertisement-
Play Games

hive的高級分組聚合是指在聚合時使用GROUPING SETS、CUBE和ROLLUP的分組聚合。 高級分組聚合在很多資料庫類SQL中都有出現，並非hive獨有，這裡只說明hive中的情況。 使用高級分組聚合不僅可以簡化SQL語句，而且通常情況下會提升SQL語句的性能。 ## 1.Grouping ...


hive的高級分組聚合是指在聚合時使用GROUPING SETS、CUBE和ROLLUP的分組聚合。

高級分組聚合在很多資料庫類SQL中都有出現，並非hive獨有，這裡只說明hive中的情況。

使用高級分組聚合不僅可以簡化SQL語句，而且通常情況下會提升SQL語句的性能。

1.Grouping sets 的使用

示例：

-- 使用方式
select a,b,sum(c) from tbl group by a,b grouping sets(a,b)

Grouping sets的子句允許在一個group by 語句中，指定多個分組聚合列。所有含有Grouping sets 的子句都可以用union連接的多個group by 查詢邏輯來表示。

如下一些常見的等價替換示例：

-- 語句1
select a, b sum(c) from tbl group by a,b grouping sets((a,b))
-- 相當於 
select a,b,sum(c) from tbl group by a,b

-- 語句2
select a,b,sum(c) from tbl group by a,b grouping sets((a,b),a)
-- 相當於
select a,b,sum(c) from tbl group by a,b
union
select a,null ,sum(c) from tbl group by a

-- 語句3
select a,b,sum(c) from tbl group by a,b grouping sets(a,b)
-- 相當於
select a,null,sum(c) from tbl group by a
union
select null ,b,sum(c) from tbl group by b

-- 語句4
select a,b,sum(c) from tbl group by a,b grouping sets((a,b),a,b,())
-- 相當於
select a,b,sum(c) from tbl group by a,b
union
select a,null,sum(c) from tbl group by a
union
select null,b,sum(c) from tbl group by b
union
select null,null,sum(c) from tbl

可以看到通過等價替換的改寫之後，語句會變得簡潔，性能我們之後分析。

2.cube 和rollup的使用

示例：

-- cube使用示例
select a,b,c,count(1) from tbl group by a,b,c with cube
-- rollup使用示例
select a,b,c,count(1) from tbl group by a,b,c with rollup

用法說明：

以上兩個高級分組函數都可以在一個group by 語句中完成多個分組聚合，它們都可以用grouping sets來等價替換。

  • cube 會計算所有group by 列的所有組合
-- cube語句
select a,b,c,count(1) from tbl group by a,b,c with cube
-- 相當於
select a,b,c count(1) from tbl group by a,b,c
grouping sets((a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),())
  • rollup 會按照group by 指定的列從左到右進行分組聚合
-- rollup語句 滾動式聚合
select a,b,c,count(1) from tbl group by a,b,c with rollup
-- 相當於
select a,b,c,count(1) from tbl group by a,b,c s
grouping sets((a,b,c),(a,b),(a),())

3.使用高級分組聚合函數的性能分析

我們可以通過執行計劃的執行來分析高級分組聚合SQL語句的執行過程，比對其優化的節點。

例1 含grouping sets關鍵詞的SQL執行案例。

set hive.map.aggr=true;
explain
-- 小於30歲人群的不同性別平均年齡
select gender,avg(age) as avg_age from temp.user_info_all where ymd = '20230505'
and age < 30 
group by gender;

-- 將以上語句改為grouping sets關鍵詞執行語句
set hive.map.aggr=true;
explain
select gender,avg(age) as num from temp.user_info_all 
where ymd = '20230505'
and age < 30 
group by gender grouping sets((gender));

查看其執行計劃：

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: user_info_all
            Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (age < 30) (type: boolean)
              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: avg(age)
                keys: gender (type: int), 0 (type: int)
                mode: hash
                outputColumnNames: _col0, _col1, _col2
                Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col0 (type: int), _col1 (type: int)
                  sort order: ++
                  Map-reduce partition columns: _col0 (type: int), _col1 (type: int)
                  Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col2 (type: struct<count:bigint,sum:double,input:bigint>)
      Reduce Operator Tree:
        Group By Operator
          aggregations: avg(VALUE._col0)
          keys: KEY._col0 (type: int), KEY._col1 (type: int)
          mode: mergepartial
          outputColumnNames: _col0, _col2
          Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE
          pruneGroupingSetId: true
          Select Operator
            expressions: _col0 (type: int), _col2 (type: double)
            outputColumnNames: _col0, _col1
            Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: true
              Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

對以上內容進行關鍵字解讀：

map階段：

  • Group By Operator ：Map端開啟聚合操作
  • aggregations：分組聚合的演算法，該案例採取avg(age)
  • keys: 這裡是分組列+ 一個固定列 0
  • mode:Hash
  • outputColumnNames:最終輸出三列。_col0, _col1, _col2
  • Reduce Output Operator：該階段為map階段聚合後的操作
  • key expressions：map端最終輸出的key，該例為gender和0兩列。
  • sort order：輸出兩列都正序排序
  • Map-reduce partition columns：表示Map階段數據輸出的分區列，該案例為gender和0兩列進行分區。
  • value expressions:map端最終輸出value，為一個結構體。

Reduce階段：

  • Group By Operator：reduce階段的分組聚合操作。
  • aggregations: 分組聚合演算法，avg(VALUE._col0)表示對map階段輸出的 value expressions的 _col0取平均值。
  • keys:指定分組聚合的key，有兩列。為map階段輸出的key。
  • mode: mergepartial
  • outputColumnNames: 表示最終輸出的列，該例為gender和num。
  • pruneGroupingSetId: 表示是否對最終輸出的grouping id進行修剪，如果為true，則表示將keys最後一列拋棄。案例中為0列。
  • Select Operator：進行列投影操作。
  • expressions:輸出的列。gender和num。

通過查看以上的執行計劃，可以看出在使用含有grouping sets語句的SQL中，hive執行計劃並沒有給出具體的實現細節。

再執行具有多個聚合列的實例來看看：

例2 聚合年齡和聚合性別多列合併測試。

set hive.map.aggr=true;
explain
select gender,age,count(0) as num from temp.user_info_all 
where ymd = '20230505'
and age < 30 
group by gender,age grouping sets(gender,age);

註：grouping sets後進行分組的列一定要在之前的group by中進行申明。

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: user_info_all
            Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (age < 30) (type: boolean)
              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: count(0)
                keys: gender (type: int), age (type: bigint), 0 (type: int)
                mode: hash
                outputColumnNames: _col0, _col1, _col2, _col3
                Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
                  sort order: +++
                  Map-reduce partition columns: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
                  Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col3 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: int), KEY._col1 (type: bigint), KEY._col2 (type: int)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col3
          Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
          pruneGroupingSetId: true
          Select Operator
            expressions: _col0 (type: int), _col1 (type: bigint), _col3 (type: bigint)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: true
              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

通過以上兩個例子可以看出hive執行計劃中沒有具體的高級分組聚合如何實現分組方案。兩者執行方式基本上差不多。

在數據掃描和查詢上的確減少了多次數據掃描和數據io操作。在一定程度上節省了計算資源。

例3 使用cube替代grouping sets 。

set hive.map.aggr=true;
explain
select gender,age,count(0) as num from temp.user_info_all 
where ymd = '20230505'
and age < 30 
group by gender,age with cube;

-- 等價語句
select gender,age,count(0) as num from temp.user_info_all 
where ymd = '20230505'
and age < 30 
group by gender,age grouping sets((gender,age),(gender),(age),());

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: user_info_all
            Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (age < 30) (type: boolean)
              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: count(0)
                keys: gender (type: int), age (type: bigint), 0 (type: int)
                mode: hash
                outputColumnNames: _col0, _col1, _col2, _col3
                Statistics: Num rows: 43512392 Data size: 1044297408 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
                  sort order: +++
                  Map-reduce partition columns: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
                  Statistics: Num rows: 43512392 Data size: 1044297408 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col3 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: int), KEY._col1 (type: bigint), KEY._col2 (type: int)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col3
          Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
          pruneGroupingSetId: true
          Select Operator
            expressions: _col0 (type: int), _col1 (type: bigint), _col3 (type: bigint)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: true
              Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

以上例3 cube語句和例2語句輸出數據完全是不一樣的。但其輸出執行計劃內容基本和例2一致。可以看出hive的執行計劃對高級分組聚合拆分執行計劃的支持還不是很好。

使用高級分組聚合，要註意開啟map端聚合模式。

使用高級分組聚合，如上案例，僅使用一個作業就能夠實現union寫法需要多個作業才能實現的邏輯。

從這點上來看能夠減少多個作業在磁碟和網路I/O時的負擔，是一種優化。

但是同時也要註意因過度使用高級分組聚合語句而導致的數據急速膨脹問題。

  • 通常使用簡單的group by 語句，一份數據只有一種聚合結果，一個分組聚合通常只有一個記錄；

  • 使用高級分組聚合，例如cube，在一個作業中一份數據會存在多種聚合情況，最終輸出是，每種聚合情況各自對應一條數據。

註意事項：

如果使用高級分組聚合的語句處理的底表，在數據量很大的情況下容易導致Map或者Reduce任務因硬體資源不足而崩潰。

hive中使用hive.new.job.grouping.set.cardinality 配置項來應對以上情況。

如果SQL語句中處理分組聚合情況超過該配置項指定的值，預設值為（30），則會創建一個新的作業。

下一期：hive視窗分析函數解讀以及帶視窗分析函數的SQL性能分析

按例，歡迎點擊此處關註我的個人公眾號，交流更多知識。

後臺回覆關鍵字 hive，隨機贈送一本魯邊備註版珍藏大數據書籍。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 創建Avalonia 模板項目-基礎
    # 創建Avalonia 模板項目-基礎 Avalonia是一個跨平臺的.NET框架，用於構建漂亮、現代的圖形用戶界面（GUI）。使用Avalonia，您可以從單個代碼庫創建適用於Windows、macOS、 Linux、iOS、Android和Web Assembly的原生應用程式。 本文部分內容 ...
  • Taurus .Net Core 微服務開源框架：Admin 插件【3】 - 指標統計管理
    繼上篇：Taurus .Net Core 微服務開源框架：Admin 插件【2】 - 系統環境信息管理，本篇繼續介紹下一個內容：系統指標節點... ...
  • 安裝Firefox問題
    在使用Quark-n開發板時，想打開瀏覽器網上衝浪，但是出現了下麵的情況。 說明我沒有這個軟體，那就安裝一手。 1、先執行軟體更新，在終端中敲入指令 sudo apt-get update 2、更新Firefox sudo apt install firefox 然後蹦出來這個畫面： apt使用出錯 ...
  • CentOS 7 下/etc/ssh/sshd_config 文件解釋
    CentOS 7 下/etc/ssh/sshd_config 文件詳解 SSH由客戶端和服務端的軟體組成，在客戶端可以使用的軟體有SecureCRT、putty、Xshell等，而在伺服器端運行的是一個sshd的服務，通過使用SSH，可以把所有傳輸的數據進行加密，而且也能夠防止dns和IP欺騙，此外 ...
  • 使用FinallShell遠程登錄Linux伺服器
    # 痞子衡嵌入式半月刊： 第 78 期 ![](http://henjay724.com/image/cnblogs/pzh_mcu_bi_weekly.PNG) 這裡分享嵌入式領域有用有趣的項目/工具以及一些熱點新聞，農曆年分二十四節氣，希望在每個交節之日準時發佈一期。 本期刊是開源項目（GitH ...
  • Spark使用Python開發和RDD
    # 使用PySpark ## 配置python環境 在所有節點上按照python3，版本必須是python3.6及以上版本 ```Shell yum install -y python3 ``` 修改所有節點的環境變數 ```Shell export JAVA_HOME=/usr/local/jdk ...
  • MySQL學習2--數據查詢
    一、基礎數據查詢 select語句用於從表中選取數據，結果被存儲在一個結果表中（稱為結果集）。 語法：select * from 表名稱 #查詢指定表中的所有數據 *為模糊匹配所有列 例： mysql> select * from person; + + + + + + + | id | name ...
  • 拈花雲科基於 Apache DolphinScheduler 在文旅業態下的實踐
    ![file](https://img2023.cnblogs.com/other/2685289/202306/2685289-20230629181452343-550852462.png) 作者｜雲科NearFar X Lab團隊 左益、周志銀、洪守偉、陳超、武超 ## 一、導讀 無錫拈花雲科 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類