Hive SQL優(yōu)化思路

Hive的優(yōu)化主要分為：配置優(yōu)化、SQL語句優(yōu)化、任務(wù)優(yōu)化等方案。

其中在開發(fā)過程中主要涉及到的可能是SQL優(yōu)化這塊。

優(yōu)化的核心思想是：

• 減少數(shù)據(jù)量（例如分區(qū)、列剪裁）

• 避免數(shù)據(jù)傾斜（例如加參數(shù)、Key打散）

• 避免全表掃描（例如on添加加上分區(qū)等）

• 減少job數(shù)（例如相同的on條件的join放在一起作為一個(gè)任務(wù)）
  
  

HQL語句優(yōu)化

1、使用分區(qū)剪裁、列剪裁

在分區(qū)剪裁中，當(dāng)使用外關(guān)聯(lián)時(shí)，如果將副表的過濾條件寫在Where后面，那么就會(huì)先全表關(guān)聯(lián)，之后再過濾。

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

上面這個(gè)SQL主要是犯了兩個(gè)錯(cuò)誤：

1. 副表的過濾條件寫在where后面，會(huì)導(dǎo)致先全表關(guān)聯(lián)在過濾分區(qū)

2. on的條件沒有過濾null值的情況，如果兩個(gè)數(shù)據(jù)表存在大批量null值的情況，會(huì)造成數(shù)據(jù)傾斜。

select a.*
from test1 a
left join test2 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值也是需要的，那么需要在條件上轉(zhuǎn)換，或者單獨(dú)拿出來

select a.*
from test1 a
left join test2 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 test1 a where a.uid is null

或者
select a.*
from test1 a
left join test2 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 test1 a
left join 
(select uid from test2 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，因?yàn)镃OUNT DISTINCT操作需要用一個(gè)Reduce Task來完成，這一個(gè)Reduce需要處理的數(shù)據(jù)量太大，就會(huì)導(dǎo)致整個(gè)Job很難完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替換，雖然會(huì)多用一個(gè)Job來完成，但在數(shù)據(jù)量大的情況下，這個(gè)絕對是值得的。

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

轉(zhuǎn)換為

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，因?yàn)橥下齢ive查詢效率出了join產(chǎn)生的shuffle以外，還有一個(gè)就是子查詢，在SQL語句里面盡量減少子查詢。with as是將語句中用到的子查詢事先提取出來（類似臨時(shí)表），使整個(gè)查詢當(dāng)中的所有模塊都可以調(diào)用該查詢結(jié)果。使用with as可以避免Hive對不同部分的相同子查詢進(jìn)行重復(fù)計(jì)算。

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

可以轉(zhuǎn)化為

with b 
as 
select uid
from test2
where ds = '2020-08-10' and uid is not null

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

4、大小表的join，寫有Join操作的查詢語句時(shí)有一條原則：應(yīng)該將條目少的表/子查詢放在Join操作符的左邊。原因是在Join操作的Reduce階段，位于Join操作符左邊的表的內(nèi)容會(huì)被加載進(jìn)內(nèi)存，將條目少的表放在左邊，可以有效減少發(fā)生OOM錯(cuò)誤的幾率。

但新版的hive已經(jīng)對小表JOIN大表和大表JOIN小表進(jìn)行了優(yōu)化。小表放在左邊和右邊已經(jīng)沒有明顯區(qū)別。

不過在做join的過程中通過小表在前可以適當(dāng)?shù)臏p少數(shù)據(jù)量，提高效率。

5、數(shù)據(jù)傾斜，數(shù)據(jù)傾斜的原理都知道，就是某一個(gè)或幾個(gè)key占據(jù)了整個(gè)數(shù)據(jù)的90%，這樣整個(gè)任務(wù)的效率都會(huì)被這個(gè)key的處理拖慢，同時(shí)也可能會(huì)因?yàn)橄嗤膋ey會(huì)聚合到一起造成內(nèi)存溢出。

數(shù)據(jù)傾斜只會(huì)發(fā)生在shuffle過程中。這里給大家羅列一些常用的并且可能會(huì)觸發(fā)shuffle操作的算子：distinct、 groupByKey、reduceByKey、aggregateByKey、join、cogroup、repartition等。出現(xiàn)數(shù)據(jù)傾斜時(shí)， 可能就是你的代碼中使用了這些算子中的某一個(gè)所導(dǎo)致的。

hive的數(shù)據(jù)傾斜一般的處理方案：

常見的做法，通過參數(shù)調(diào)優(yōu)：
set hive.map.aggr=true;
set hive.groupby.skewindata = ture;
當(dāng)選項(xiàng)設(shè)定為true時(shí)，生成的查詢計(jì)劃有兩個(gè)MapReduce任務(wù)。
在第一個(gè)MapReduce中，map的輸出結(jié)果集合會(huì)隨機(jī)分布到reduce中，每個(gè)reduce做部分聚合操作，并輸出結(jié)果。
這樣處理的結(jié)果是，相同的Group By Key有可能分發(fā)到不同的reduce中，從而達(dá)到負(fù)載均衡的目的；
第二個(gè)MapReduce任務(wù)再根據(jù)預(yù)處理的數(shù)據(jù)結(jié)果按照Group By Key分布到reduce中（這個(gè)過程可以保證相同的Group By Key分布到同一個(gè)reduce中），最后完成最終的聚合操作。

但是這個(gè)處理方案對于我們來說是個(gè)黑盒，無法把控。

一般處理方案是將對應(yīng)的key值打散即可。

例如：
select a.*
from test1 a
left join test2 b on  a.uid = b.uid
where a.ds='2020-08-10'
and b.ds='2020-08-10'

如果有90%的key都是null，這樣不可避免的出現(xiàn)數(shù)據(jù)傾斜。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'

當(dāng)然這種只是理論上的處理方案。
正常的方案是null進(jìn)行過濾，但是日常情況下不是這中特殊的key。
那么在日常需求的情況下如何處理這種數(shù)據(jù)傾斜的情況呢：
1. sample采樣，獲取哪些集中的key
2. 將集中的key按照一定規(guī)則添加隨機(jī)數(shù)
3. 進(jìn)行join，由于打散了，所以數(shù)據(jù)傾斜避免了
4. 在處理結(jié)果中對之前的添加的隨機(jī)數(shù)進(jìn)行切分，變成原始的數(shù)據(jù)；

當(dāng)然這些優(yōu)化都是針對SQL本身的優(yōu)化，還有一些是通過參數(shù)設(shè)置去調(diào)整的，這里面就不再詳細(xì)描述了。

但是優(yōu)化的核心思想都差不多：

1. 減少數(shù)據(jù)量
2. 避免數(shù)據(jù)傾斜
3. 減少JOB數(shù)
4. 虛核心點(diǎn)：根據(jù)業(yè)務(wù)邏輯對業(yè)務(wù)實(shí)現(xiàn)的整體進(jìn)行優(yōu)化；

5. 虛解決方案：采用presto、impala等專門的查詢引擎，采用spark計(jì)算引擎替換MR/TEZ
   

來源：https://zhuanlan.zhihu.com/p/180353740