Hive SQL 企業級優化技巧

• • Hive SQL 企業級優化技巧
  • • 技巧 1：活用 group by
    • 技巧 2：巧用 cube 函數
    • 技巧 3：利用 lateral view 對字段采用行轉列處理
    • 技巧 4：表連接配接優化與中間表的應用
    • 技巧 5：如何解決資料傾斜

Hive SQL 企業級優化技巧

本文是筆者在日常完成 Hive SQL 業務需求中，總結出一些 HQL 優化技巧，希望能給各位提供一些幫助與啟發，文章如有運用不妥之處，敬請諒解。

關于資料傾斜的優化技巧，因為篇幅有限，隻會先簡要提供解決思路，後邊也會更新輔以實際案例的關于資料傾斜原因與具體解決方案。

技巧 1：活用 group by

結合實際的代碼塊，各位先看以下兩段代碼

第一段：

第二段：

上邊代碼中，session_id 是作為使用者通路一次的會話字段，對比第一段 SQL 代碼，明顯第二段的計算方式更為簡潔，通路次數不用考慮 session_id ，直接對每個使用者 group by 後 count(*) 得出每個使用者的通路次數，最後在最外層 sum(pv) 得到總的通路次數即可。

同時在進行簡單的查詢操作時 group by 也能替代 distinct 進行去重，碰到資料量級較大時，可以用這個技巧減少代碼運作的時間。

技巧 2：巧用 cube 函數

cube：根據group by 次元的所有組合進行聚合。

具體應用見以下代碼：

假如想要統計全部類目的商家數量，可以使用 cube 函數得出。

技巧 3：利用 lateral view 對字段采用行轉列處理

在電商業務中，有時候會碰到一個商家經營商品的類目屬性是涵蓋了多個類目的，但你需要統計出不同類目的商家數多少？這時候就可以運用對類目字段行轉列，再聚合統計。

代碼如下：

技巧 4：表連接配接優化與中間表的應用

1、join 的時候，小表在前，大表在後

Hive 查詢的時候，無論是否使用 mapjoin ，小表都需要放入前面，原因是reduce階段會将第一個表的 key 全部放入記憶體，當第二個表到來的時候再開始輸出。假設最後的一個表是大表，即它會把其他的表緩存起來，縮短計算時長。

2、在對多個表進行 join 連接配接的時候，on 的部分優先使用相同的連接配接條件。如果不是同一個連接配接條件，可能會導緻出現多個 MapReduce job，不利于減少計算量。

3、在使用資料表的時候，要養成提前篩選過濾掉不必要的資料，即能減少計算量，又能避免不必要的失誤，當然這是建立在充分了解業務邏輯的情況。

4、在梳理業務邏輯，邏輯過于複雜的時候，應适當引入中間表。比如做月表可以先做一張日表，先統計出每日的名額資料，再由拉取一個月的資料作為月表資料。累計名額可以先建立累計表。

技巧 5：如何解決資料傾斜

什麼是資料傾斜？

答：一個進行中的任務進度一直維持在99%（或100%），檢視日志發現有一個 reduce 運作時長非常久，其他的 reduce 都能夠在1min之内完成，由此可推斷出必然發生了資料傾斜。資料傾斜基本都發生在group by、join等需要資料shuffle的操作中。

解決思路：

1、連接配接條件沒有過濾空值，如果檢視資料發現兩個表的連接配接條件存在大量空值，應先在連接配接前篩掉空值。

2、連接配接的 key 大量重複切非必要，則過濾掉這部分 連接配接key。

3、大小表連接配接的話，直接用 /+mapjoin/，将小表放記憶體裡，在 map 端做 join。

4、兩個表連接配接條件的字段資料類型不一緻，把連接配接 key 轉換下資料類型