Hive SQL優(yōu)化思路
后臺回復(fù)【加群】，申請加入優(yōu)質(zhì)大數(shù)據(jù)學(xué)習(xí)社群
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放在一起作為一個任務(wù)）
HQL語句優(yōu)化
1. 使用分區(qū)剪裁、列剪裁
在分區(qū)剪裁中，當(dāng)使用外關(guān)聯(lián)時，如果將副表的過濾條件寫在Where后面，那么就會先全表關(guān)聯(lián)，之后再過濾。
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后面，會導(dǎo)致先全表關(guān)聯(lián)在過濾分區(qū)。
注：雖然a表的where條件也寫在join后面，但是a表會進(jìn)行謂詞下推，也就是先執(zhí)行where條件，再執(zhí)行join，但是b表不會進(jìn)行謂詞下推！
on的條件沒有過濾null值的情況，如果兩個數(shù)據(jù)表存在大批量null值的情況，會造成數(shù)據(jù)傾斜。
正確寫法：
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值也是需要的，那么需要在條件上轉(zhuǎn)換，或者單獨拿出來
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需要處理的數(shù)據(jù)量太大，就會導(dǎo)致整個Job很難完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替換，雖然會多用一個Job來完成，但在數(shù)據(jù)量大的情況下，這個絕對是值得的。
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
拖慢Hive查詢效率除了join產(chǎn)生的shuffle以外，還有一個就是子查詢，在SQL語句里面盡量減少子查詢。with as是將語句中用到的子查詢事先提取出來（類似臨時表），使整個查詢當(dāng)中的所有模塊都可以調(diào)用該查詢結(jié)果。使用with as可以避免Hive對不同部分的相同子查詢進(jìn)行重復(fù)計算。
select?a.*??
from??a??
left?join?b?on??a.uid?=?b.uid??
where?a.ds='2020-08-10'??
and?b.ds='2020-08-10'??
可以轉(zhuǎn)化為：
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操作的查詢語句時有一條原則：應(yīng)該將條目少的表/子查詢放在Join操作符的左邊。原因是在Join操作的Reduce階段，位于Join操作符左邊的表的內(nèi)容會被加載進(jìn)內(nèi)存，將條目少的表放在左邊，可以有效減少發(fā)生OOM錯誤的幾率。但新版的hive已經(jīng)對小表JOIN大表和大表JOIN小表進(jìn)行了優(yōu)化。小表放在左邊和右邊已經(jīng)沒有明顯區(qū)別。不過在做join的過程中通過小表在前可以適當(dāng)?shù)臏p少數(shù)據(jù)量，提高效率。
5. 數(shù)據(jù)傾斜
數(shù)據(jù)傾斜的原理都知道，就是某一個或幾個key占據(jù)了整個數(shù)據(jù)的90%，這樣整個任務(wù)的效率都會被這個key的處理拖慢，同時也可能會因為相同的key會聚合到一起造成內(nèi)存溢出。
數(shù)據(jù)傾斜只會發(fā)生在shuffle過程中。這里給大家羅列一些常用的并且可能會觸發(fā)shuffle操作的算子：distinct、 groupByKey、reduceByKey、aggregateByKey、join、cogroup、repartition等。出現(xiàn)數(shù)據(jù)傾斜時，可能就是你的代碼中使用了這些算子中的某一個所導(dǎo)致的。
hive的數(shù)據(jù)傾斜一般的處理方案：
常見的做法，通過參數(shù)調(diào)優(yōu)：
set?hive.map.aggr=true;??
set?hive.groupby.skewindata?=?ture;
當(dāng)選項設(shè)定為true時，生成的查詢計劃有兩個MapReduce任務(wù)。
在第一個MapReduce中，map的輸出結(jié)果集合會隨機(jī)分布到reduce中，每個reduce做部分聚合操作，并輸出結(jié)果。
這樣處理的結(jié)果是，相同的Group By Key有可能分發(fā)到不同的reduce中，從而達(dá)到負(fù)載均衡的目的；
第二個MapReduce任務(wù)再根據(jù)預(yù)處理的數(shù)據(jù)結(jié)果按照Group By Key分布到reduce中（這個過程可以保證相同的Group By Key分布到同一個reduce中），最后完成最終的聚合操作。
但是這個處理方案對于我們來說是個黑盒，無法把控。
一般處理方案是將對應(yīng)的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，這樣不可避免的出現(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ù)傾斜的情況呢：
sample采樣，獲取哪些集中的key； 將集中的key按照一定規(guī)則添加隨機(jī)數(shù)； 進(jìn)行join，由于打散了，所以數(shù)據(jù)傾斜避免了； 在處理結(jié)果中對之前的添加的隨機(jī)數(shù)進(jìn)行切分，變成原始的數(shù)據(jù)。
當(dāng)然這些優(yōu)化都是針對SQL本身的優(yōu)化，還有一些是通過參數(shù)設(shè)置去調(diào)整的，這里面就不再詳細(xì)描述了。
但是優(yōu)化的核心思想都差不多：
減少數(shù)據(jù)量； 避免數(shù)據(jù)傾斜； 減少JOB數(shù)； 核心點：根據(jù)業(yè)務(wù)邏輯對業(yè)務(wù)實現(xiàn)的整體進(jìn)行優(yōu)化； 解決方案：采用presto、impala等專門的查詢引擎，采用spark計算引擎替換MR/TEZ。
交流群
加我微信：ddxygq，回復(fù)“加群”，我拉你進(jìn)群。
猜你喜歡
數(shù)倉建模—指標(biāo)體系
數(shù)倉建模—寬表的設(shè)計
Spark SQL知識點與實戰(zhàn)
Hive計算最大連續(xù)登陸天數(shù)
Flink計算pv和uv的通用方法