Hive SQL優化思路

2021 年 12 月 28 日
筆記
hive, SQL, 大數據

Hive的優化主要分為：配置優化、SQL語句優化、任務優化等方案。其中在開發過程中主要涉及到的可能是SQL優化這塊。

優化的核心思想是：

減少數據量（例如分區、列剪裁）
避免數據傾斜（例如加參數、Key打散）
避免全表掃描（例如on添加加上分區等）
減少job數（例如相同的on條件的join放在一起作為一個任務）

HQL語句優化

1. 使用分區剪裁、列剪裁

在分區剪裁中，當使用外關聯時，如果將副表的過濾條件寫在Where後面，那麼就會先全表關聯，之後再過濾。

select a.*  
from a  
left join b on  a.uid = b.uid  
where a.ds='2020-08-10'  
and b.ds='2020-08-10'

上面這個SQL主要犯了兩個錯誤：

副表(上方b表)的where條件寫在join後面，會導致先全表關聯在過濾分區。

註：雖然a表的where條件也寫在join後面，但是a表會進行謂詞下推，也就是先執行where條件，再執行join，但是b表不會進行謂詞下推！

on的條件沒有過濾null值的情況，如果兩個數據表存在大批量null值的情況，會造成數據傾斜。

正確寫法：

select a.*  
from a  
left join b on (d.uid is not null and a.uid = b.uid and b.ds='2020-08-10') 
where a.ds='2020-08-10'

如果null值也是需要的，那麼需要在條件上轉換，或者單獨拿出來


select a.*  
from a  
left join b on (a.uid is not null and a.uid = b.uid and b.ds='2020-08-10')  
where a.ds='2020-08-10'  
union all  
select a.* from a where a.uid is null

或者：

select a.*  
from a  
left join b on   
case when a.uid is null then concat("test",RAND()) else a.uid end = b.uid and b.ds='2020-08-10'  
where a.ds='2020-08-10'

或者（子查詢）：

select a.*  
from a  
left join   
(select uid from where ds = '2020-08-10' and uid is not null) b on a.uid = b.uid 
where a.uid is not null  
and a.ds='2020-08-10'

2. 盡量不要用COUNT DISTINCT

因為COUNT DISTINCT操作需要用一個Reduce Task來完成，這一個Reduce需要處理的數據量太大，就會導致整個Job很難完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替換，雖然會多用一個Job來完成，但在數據量大的情況下，這個絕對是值得的。

select count(distinct uid)  
from test  
where ds='2020-08-10' and uid is not null

轉換為：

select count(a.uid)  
from   
(select uid 
 from test 
 where uid is not null and ds = '2020-08-10' 
 group by uid
) a

3. 使用with as

拖慢Hive查詢效率除了join產生的shuffle以外，還有一個就是子查詢，在SQL語句裏面盡量減少子查詢。with as是將語句中用到的子查詢事先提取出來（類似臨時表），使整個查詢當中的所有模塊都可以調用該查詢結果。使用with as可以避免Hive對不同部分的相同子查詢進行重複計算。

select a.*  
from  a  
left join b on  a.uid = b.uid  
where a.ds='2020-08-10'  
and b.ds='2020-08-10'

可以轉化為：

with test1 as 
(
select uid  
from b  
where ds = '2020-08-10' and uid is not null  
)  
select a.*  
from a  
left join test1 on a.uid = test1.uid  
where a.ds='2020-08-10' and a.uid is not null

4. 大小表的join

寫有Join操作的查詢語句時有一條原則：應該將條目少的表/子查詢放在Join操作符的左邊。原因是在Join操作的Reduce階段，位於Join操作符左邊的表的內容會被加載進內存，將條目少的表放在左邊，可以有效減少發生OOM錯誤的幾率。但新版的hive已經對小表JOIN大表和大表JOIN小表進行了優化。小表放在左邊和右邊已經沒有明顯區別。不過在做join的過程中通過小表在前可以適當的減少數據量，提高效率。

5. 數據傾斜

數據傾斜的原理都知道，就是某一個或幾個key佔據了整個數據的90%，這樣整個任務的效率都會被這個key的處理拖慢，同時也可能會因為相同的key會聚合到一起造成內存溢出。

數據傾斜只會發生在shuffle過程中。這裡給大家羅列一些常用的並且可能會觸發shuffle操作的算子：distinct、 groupByKey、reduceByKey、aggregateByKey、join、cogroup、repartition等。出現數據傾斜時，可能就是你的代碼中使用了這些算子中的某一個所導致的。

hive的數據傾斜一般的處理方案：

常見的做法，通過參數調優：

set hive.map.aggr=true;  
set hive.groupby.skewindata = ture;

當選項設定為true時，生成的查詢計劃有兩個MapReduce任務。

在第一個MapReduce中，map的輸出結果集合會隨機分佈到reduce中，每個reduce做部分聚合操作，並輸出結果。

這樣處理的結果是，相同的Group By Key有可能分發到不同的reduce中，從而達到負載均衡的目的；

第二個MapReduce任務再根據預處理的數據結果按照Group By Key分佈到reduce中（這個過程可以保證相同的Group By Key分佈到同一個reduce中），最後完成最終的聚合操作。

但是這個處理方案對於我們來說是個黑盒，無法把控。

一般處理方案是將對應的key值打散即可。

例如：

select a.*  
from a  
left join b on  a.uid = b.uid  
where a.ds='2020-08-10'  
and b.ds='2020-08-10'

如果有90%的key都是null，這樣不可避免的出現數據傾斜。

select a.uid  
from test1 as a  
join(  
   select case when uid is null then cast(rand(1000000) as int)  
   else uid  
   from test2 where ds='2020-08-10') b   
on a.uid = b.uid  
where a.ds='2020-08-10'

當然這種只是理論上的處理方案。

正常的方案是null進行過濾，但是日常情況下不是這種特殊的key。

那麼在日常需求的情況下如何處理這種數據傾斜的情況呢：

sample採樣，獲取哪些集中的key；
將集中的key按照一定規則添加隨機數；
進行join，由於打散了，所以數據傾斜避免了；
在處理結果中對之前的添加的隨機數進行切分，變成原始的數據。

當然這些優化都是針對SQL本身的優化，還有一些是通過參數設置去調整的，這裏面就不再詳細描述了。

但是優化的核心思想都差不多：

減少數據量
避免數據傾斜
減少JOB數
虛核心點：根據業務邏輯對業務實現的整體進行優化；
虛解決方案：採用presto、impala等專門的查詢引擎，採用spark計算引擎替換MR/TEZ

Tags: hive SQL 大數據