• 關(guān)于調(diào)優(yōu)，重要的是理解每一個(gè)優(yōu)化手段的思路。理解優(yōu)化需要配置的每個(gè)參數(shù)的實(shí)際作用。

1、 計(jì)算資源配置

計(jì)算環(huán)境為Hive on MR。計(jì)算資源的調(diào)整主要包括Yarn和MR。

1.1 Yarn資源配置

1）Yarn配置說(shuō)明

需要調(diào)整的Yarn參數(shù)均與CPU、內(nèi)存等資源有關(guān)，核心配置參數(shù)如下

（1）yarn.nodemanager.resource.memory-mb

該參數(shù)的含義是，一個(gè)NodeManager節(jié)點(diǎn)分配給Container使用的內(nèi)存。該參數(shù)的配置，取決于NodeManager所在節(jié)點(diǎn)的總內(nèi)存容量和該節(jié)點(diǎn)運(yùn)行的其他服務(wù)的數(shù)量。
考慮上述因素，此處可將該參數(shù)設(shè)置為64G，如下：

<property>
    <name>yarn.nodemanager.resource.memory-mb</name>
    <value>65536</value>
</property>

• 該參數(shù)默認(rèn)使用8G內(nèi)存去跑任務(wù)。
• 需要手動(dòng)調(diào)整，一般給到總內(nèi)存的1/2或者2/3。

（2）yarn.nodemanager.resource.cpu-vcores

該參數(shù)的含義是，一個(gè)NodeManager節(jié)點(diǎn)分配給Container使用的CPU核數(shù)。該參數(shù)的配置，同樣取決于NodeManager所在節(jié)點(diǎn)的總CPU核數(shù)和該節(jié)點(diǎn)運(yùn)行的其他服務(wù)。
考慮上述因素，此處可將該參數(shù)設(shè)置為16。

<property>
    <name>yarn.nodemanager.resource.cpu-vcores</name>
    <value>16</value>
</property>

（3）yarn.scheduler.maximum-allocation-mb

該參數(shù)的含義是，單個(gè)Container能夠使用的最大內(nèi)存。推薦配置如下：

<property>
    <name>yarn.scheduler.maximum-allocation-mb</name>
    <value>16384</value>
</property>

（4）yarn.scheduler.minimum-allocation-mb

該參數(shù)的含義是，單個(gè)Container能夠使用的最小內(nèi)存，推薦配置如下：

<property>
    <name>yarn.scheduler.minimum-allocation-mb</name>
    <value>512</value>
</property>

2）Yarn配置實(shí)操

（1）修改$HADOOP_HOME/etc/hadoop/yarn-site.xml文件

（2）修改如下參數(shù)

<property>
    <name>yarn.nodemanager.resource.memory-mb</name>
    <value>65536</value>
</property>
<property>
    <name>yarn.nodemanager.resource.cpu-vcores</name>
    <value>16</value>
</property>
<property>
    <name>yarn.scheduler.maximum-allocation-mb</name>
    <value>16384</value>
</property>
<property>
    <name>yarn.scheduler.minimum-allocation-mb</name>
    <value>512</value>
</property>

（3）分發(fā)該配置文件

（4）重啟Yarn。

1.2 MapReduce資源配置

MapReduce資源配置主要包括Map Task的內(nèi)存和CPU核數(shù)，以及Reduce Task的內(nèi)存和CPU核數(shù)。核心配置參數(shù)如下：

1）mapreduce.map.memory.mb

該參數(shù)的含義是，單個(gè)Map Task申請(qǐng)的container容器內(nèi)存大小，其默認(rèn)值為1024。該值不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb規(guī)定的范圍。

該參數(shù)需要根據(jù)不同的計(jì)算任務(wù)單獨(dú)進(jìn)行配置，在hive中，可直接使用如下方式為每個(gè)SQL語(yǔ)句單獨(dú)進(jìn)行配置：

set  mapreduce.map.memory.mb=2048;

2）mapreduce.map.cpu.vcores

該參數(shù)的含義是，單個(gè)Map Task申請(qǐng)的container容器cpu核數(shù)，其默認(rèn)值為1。該值一般無(wú)需調(diào)整。

3）mapreduce.reduce.memory.mb

該參數(shù)的含義是，單個(gè)Reduce Task申請(qǐng)的container容器內(nèi)存大小，其默認(rèn)值為1024。該值同樣不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb規(guī)定的范圍。

該參數(shù)需要根據(jù)不同的計(jì)算任務(wù)單獨(dú)進(jìn)行配置，在hive中，可直接使用如下方式為每個(gè)SQL語(yǔ)句單獨(dú)進(jìn)行配置：

set  mapreduce.reduce.memory.mb=2048;

4）mapreduce.reduce.cpu.vcores

該參數(shù)的含義是，單個(gè)Reduce Task申請(qǐng)的container容器cpu核數(shù)，其默認(rèn)值為1。該值一般無(wú)需調(diào)整。

2、 Explain查看執(zhí)行計(jì)劃（重點(diǎn)）

2.1 Explain執(zhí)行計(jì)劃概述

Explain呈現(xiàn)的執(zhí)行計(jì)劃，由一系列Stage組成，這一系列Stage具有依賴關(guān)系，每個(gè)Stage對(duì)應(yīng)一個(gè)MapReduce Job，或者一個(gè)文件系統(tǒng)操作等。

• stage可以對(duì)應(yīng)mr，也可以對(duì)應(yīng)文件系統(tǒng)操作。因?yàn)椴皇撬械膕ql語(yǔ)句的底層都是mr。比如說(shuō)load語(yǔ)句，底層就不是mr而是文件系統(tǒng)操作。
• 有些sql復(fù)雜，需要多個(gè)mr才能計(jì)算，這個(gè)時(shí)候?qū)?yīng)的也就有多個(gè)stage，多個(gè)stage之間也是有依賴關(guān)系的。依賴關(guān)系也就表明了哪個(gè)mr先執(zhí)行，哪個(gè)后面執(zhí)行。

若某個(gè)Stage對(duì)應(yīng)的一個(gè)MapReduce Job，其Map端和Reduce端的計(jì)算邏輯分別由Map Operator Tree和Reduce Operator Tree進(jìn)行描述，Operator Tree由一系列的Operator組成，一個(gè)Operator代表在Map或Reduce階段的一個(gè)單一的邏輯操作，例如TableScan Operator，Select Operator，Join Operator等。

下圖是由一個(gè)執(zhí)行計(jì)劃繪制而成：

常見(jiàn)的Operator及其作用如下：

• TableScan：表掃描操作，通常map端第一個(gè)操作肯定是表掃描操作
• Select Operator：選取操作
• Group By Operator：分組聚合操作
• Reduce Output Operator：輸出到 reduce 操作
• Filter Operator：過(guò)濾操作， 對(duì)應(yīng)sql語(yǔ)句的where或者h(yuǎn)aving
• Join Operator：join 操作
• File Output Operator：文件輸出操作
• Fetch Operator 客戶端獲取數(shù)據(jù)操作，因?yàn)檫M(jìn)行查詢之后，會(huì)把數(shù)據(jù)寫入到hdfs的臨時(shí)表當(dāng)中，通過(guò)fetch可以展示在終端。

2.2 基本語(yǔ)法

• 基本語(yǔ)法其實(shí)就是在sql的最前面加上explain

EXPLAIN [FORMATTED | EXTENDED | DEPENDENCY] query-sql

注：FORMATTED、EXTENDED、DEPENDENCY關(guān)鍵字為可選項(xiàng)，各自作用如下。

• FORMATTED：將執(zhí)行計(jì)劃以格式化的JSON字符串的形式輸出

• EXTENDED：輸出執(zhí)行計(jì)劃中的額外信息，通常是讀寫的文件名等信息
• DEPENDENCY：輸出執(zhí)行計(jì)劃讀取的表及分區(qū)

2.3 案例實(shí)操

hive (default)> 
explain
select
    user_id,
    count(*)
from order_detail
group by user_id;

3、分組聚合優(yōu)化

3.1 優(yōu)化說(shuō)明

Hive中未經(jīng)優(yōu)化的分組聚合，是通過(guò)一個(gè)MapReduce Job實(shí)現(xiàn)的。Map端負(fù)責(zé)讀取數(shù)據(jù)，并按照分組字段分區(qū)，通過(guò)Shuffle，將數(shù)據(jù)發(fā)往Reduce端，各組數(shù)據(jù)在Reduce端完成最終的聚合運(yùn)算。

• 聚合之后數(shù)據(jù)量不可能變大，但是有可能數(shù)據(jù)量不變。

Hive對(duì)分組聚合的優(yōu)化主要圍繞著減少Shuffle數(shù)據(jù)量進(jìn)行，具體做法是map-side聚合。所謂map-side聚合，就是在map端維護(hù)一個(gè)hash table，利用其完成部分的聚合，然后將部分聚合的結(jié)果，按照分組字段分區(qū)，發(fā)送至reduce端，完成最終的聚合。map-side聚合能有效減少shuffle的數(shù)據(jù)量，提高分組聚合運(yùn)算的效率。

（1）map-side 聚合相關(guān)的參數(shù)

1、啟用map-side聚合

set hive.map.aggr=true;

• 該參數(shù)默認(rèn)是開(kāi)啟的。

2、用于檢測(cè)源表數(shù)據(jù)是否適合進(jìn)行map-side聚合。檢測(cè)的方法是：先對(duì)若干條數(shù)據(jù)進(jìn)行map-side聚合，若聚合后的條數(shù)和聚合前的條數(shù)比值小于該值，則認(rèn)為該表適合進(jìn)行map-side聚合；否則，認(rèn)為該表數(shù)據(jù)不適合進(jìn)行map-side聚合，后續(xù)數(shù)據(jù)便不再進(jìn)行map-side聚合。

set hive.map.aggr.hash.min.reduction=0.5;

• 如果hive.map.aggr.hash.min.reduction的值為1，則所有的數(shù)據(jù)不會(huì)判斷，直接全部進(jìn)行map端聚合。

3、用于檢測(cè)源表是否適合map-side聚合的條數(shù)。

set hive.groupby.mapaggr.checkinterval=100000;

4、map-side聚合所用的hash table，占用map task堆內(nèi)存的最大比例，若超出該值，則會(huì)對(duì)hash table進(jìn)行一次flush。

set hive.map.aggr.hash.force.flush.memory.threshold=0.9;

3.2 優(yōu)化案例

select
    product_id,
    count(*)
from order_detail
group by product_id;

1、優(yōu)化前（跑了46s）

set hive.map.aggr=false;

手動(dòng)將該參數(shù)設(shè)置為false

2、優(yōu)化后（跑了46s）

原因：和product_id的分組字段在這張表上的分布有關(guān)，因?yàn)閔ive在進(jìn)行hive.groupby.mapaggr.checkinterval這個(gè)參數(shù)的校驗(yàn)時(shí)不是隨機(jī)的去進(jìn)行校驗(yàn)，只會(huì)對(duì)每個(gè)map的前面一部分?jǐn)?shù)據(jù)進(jìn)行判斷。可能恰好前面的數(shù)據(jù)在進(jìn)行分組聚合的時(shí)候，product_id的值都相同。

• 也就是hive判斷是否適合分組聚合的不是很智能，這個(gè)時(shí)候我們可以讓其強(qiáng)制進(jìn)行分組聚合。

set hive.map.aggr.hash.min.reduction=1;

此時(shí)時(shí)間跑了32秒，比之前快了10秒。

• 按道理product_id只有100萬(wàn)數(shù)據(jù)，為什么這里map端輸出的數(shù)據(jù)會(huì)大于100萬(wàn)？原因是因?yàn)橛|發(fā)了flush，也就是上面的第四個(gè)參數(shù)。例如，在flush之前已經(jīng)有product_id=1的數(shù)據(jù)了，flush之后會(huì)重新用一個(gè)hash table，這樣product_id可能就會(huì)輸出多次了。

那么如果flush的次數(shù)多了，分組聚合的效果也不會(huì)很好，這個(gè)時(shí)候可以怎么辦？
1、調(diào)整參數(shù)閾值。

set hive.map.aggr.hash.force.flush.memory.threshold=0.9;

2、如果調(diào)整之后效果還是不明顯，說(shuō)明hive的總內(nèi)存小，則可以調(diào)整下面這個(gè)參數(shù)：

set  mapreduce.map.memory.mb=2048;

4、join優(yōu)化

4.1 Join算法概述

Hive擁有多種join算法，包括Common Join，Map Join，Bucket Map Join，Sort Merge Buckt Map Join等，下面對(duì)每種join算法做簡(jiǎn)要說(shuō)明：

（1）Common Join

Common Join是Hive中最穩(wěn)定的join算法，其通過(guò)一個(gè)MapReduce Job完成一個(gè)join操作。Map端負(fù)責(zé)讀取join操作所需表的數(shù)據(jù)，并按照關(guān)聯(lián)字段進(jìn)行分區(qū)，通過(guò)Shuffle，將其發(fā)送到Reduce端，相同key的數(shù)據(jù)在Reduce端完成最終的Join操作。如下圖所示：

• 如果是A join B join C ，這種時(shí)候是使用1個(gè)MR還是2個(gè)MR呢？
• 如果join的字段都是相同的，這種時(shí)候沒(méi)有必要使用兩個(gè)MR，一個(gè)MR就夠了，如下圖所示。
  
• 如果join的字段不相同，就不能使用一個(gè)MR，因?yàn)閙ap分區(qū)的字段不同的。這種情況下只能A和B去進(jìn)行common join，之后在對(duì)join之后的中間結(jié)果與C表進(jìn)行另一個(gè)common join。

因此，sql語(yǔ)句中的join操作和執(zhí)行計(jì)劃中的Common Join任務(wù)并非一對(duì)一的關(guān)系，一個(gè)sql語(yǔ)句中的相鄰的且關(guān)聯(lián)字段相同的多個(gè)join操作可以合并為一個(gè)Common Join任務(wù)。

例如：

hive (default)> 
select 
    a.val, 
    b.val, 
    c.val 
from a 
join b on (a.key = b.key1) 
join c on (c.key = b.key1)

上述sql語(yǔ)句中兩個(gè)join操作的關(guān)聯(lián)字段均為b表的key1字段，則該語(yǔ)句中的兩個(gè)join操作可由一個(gè)Common Join任務(wù)實(shí)現(xiàn)，也就是可通過(guò)一個(gè)Map Reduce任務(wù)實(shí)現(xiàn)。

hive (default)> 
select 
    a.val, 
    b.val, 
    c.val 
from a 
join b on (a.key = b.key1) 
join c on (c.key = b.key2)

上述sql語(yǔ)句中的兩個(gè)join操作關(guān)聯(lián)字段各不相同，則該語(yǔ)句的兩個(gè)join操作需要各自通過(guò)一個(gè)Common Join任務(wù)實(shí)現(xiàn)，也就是通過(guò)兩個(gè)Map Reduce任務(wù)實(shí)現(xiàn)。

（2）Map Join

Map Join有兩種觸發(fā)方式，一種是用戶在SQL語(yǔ)句中增加hint提示，另外一種是Hive優(yōu)化器根據(jù)參與join表的數(shù)據(jù)量大小，自動(dòng)觸發(fā)。

Map Join算法可以通過(guò)兩個(gè)只有map階段的Job完成一個(gè)join操作。其適用場(chǎng)景為大表join小表。若某join操作滿足要求，則第一個(gè)job會(huì)讀取小表數(shù)據(jù)，將其制作為hash table，并上傳至Hadoop 分布式緩存（本質(zhì)上是上傳至HDFS）。第二個(gè)job會(huì)先從分布式緩存中讀取小表數(shù)據(jù)，并緩存在Map Task 的內(nèi)存中，然后掃描大表數(shù)據(jù)，這樣在map端即可完成關(guān)聯(lián)操作。如下圖所示:

• 在map階段完成join，比在reduce階段完成join的效率要更高，因?yàn)檫@樣可以省去shuffle的時(shí)間。
• map join核心的點(diǎn)在于：要將小表的數(shù)據(jù)都緩存到mapper的內(nèi)存里面，所以map join有瓶頸：不能適用于大表join大表的情況。
• 但是并不是所有的join都能在map階段完成，適用場(chǎng)景是：大表join小表。

（3）Bucket Map Join

Bucket Map Join是對(duì)Map Join算法的改進(jìn)，其打破了Map Join只適用于大表join小表的限制，可用于大表join大表的場(chǎng)景。

Bucket Map Join的核心思想是：【要滿足下面幾個(gè)條件】

1、參與join的表均為分桶表
2、關(guān)聯(lián)字段為分桶字段
3、其中一張表的分桶數(shù)量是另外一張表分桶數(shù)量的整數(shù)倍

滿足上面三個(gè)條件則能保證參與join的兩張表的分桶之間具有明確的關(guān)聯(lián)關(guān)系，就可以在兩表的分桶間進(jìn)行Map Join操作了。

這樣一來(lái)，第二個(gè)Job的Map端就無(wú)需再緩存小表的全表數(shù)據(jù)了，而只需緩存其所需的分桶即可。其原理如圖所示：

• bucket map join和map join的核心原理是一致的，同樣是分兩個(gè)階段去做，第一個(gè)階段也是要由本地任務(wù)去讀取相對(duì)來(lái)說(shuō)小一點(diǎn)的表的數(shù)據(jù)，這里讀B的數(shù)據(jù)，之后制作hash表。這里hash表是根據(jù)分桶的數(shù)據(jù)操作的。
• 有幾個(gè)桶就會(huì)有幾個(gè)mapper。

（4）Sort Merge Bucket Map Join

Sort Merge Bucket Map Join（簡(jiǎn)稱SMB Map Join）基于Bucket Map Join。SMB Map Join要求，參與join的表均為分桶表，且需保證分桶內(nèi)的數(shù)據(jù)是有序的，且分桶字段、排序字段和關(guān)聯(lián)字段為相同字段，且其中一張表的分桶數(shù)量是另外一張表分桶數(shù)量的整數(shù)倍。

SMB Map Join同Bucket Join一樣，同樣是利用兩表各分桶之間的關(guān)聯(lián)關(guān)系，在分桶之間進(jìn)行join操作，不同的是，分桶之間的join操作的實(shí)現(xiàn)原理。Bucket Map Join，兩個(gè)分桶之間的join實(shí)現(xiàn)原理為Hash Join算法；而SMB Map Join，兩個(gè)分桶之間的join實(shí)現(xiàn)原理為Sort Merge Join算法。

Hash Join和Sort Merge Join均為關(guān)系型數(shù)據(jù)庫(kù)中常見(jiàn)的Join實(shí)現(xiàn)算法。Hash Join的原理相對(duì)簡(jiǎn)單，就是對(duì)參與join的一張表構(gòu)建hash table，然后掃描另外一張表，然后進(jìn)行逐行匹配。Sort Merge Join需要在兩張按照關(guān)聯(lián)字段排好序的表中進(jìn)行，其原理如圖所示：

SMB Map Join與Bucket Map Join相比的優(yōu)勢(shì)是什么？
1、不需要在制作hash表，分桶在匹配的時(shí)候也不需要使用hash表。
2、對(duì)內(nèi)存的要求更低，不需要將桶在放到第二個(gè)join的內(nèi)存當(dāng)中，因?yàn)橥皟?nèi)的數(shù)據(jù)已經(jīng)有序了。

• Hive中的SMB Map Join就是對(duì)兩個(gè)分桶的數(shù)據(jù)按照上述思路進(jìn)行Join操作?？梢钥闯觯琒MB Map Join與Bucket Map Join相比，在進(jìn)行Join操作時(shí)，Map端是無(wú)需對(duì)整個(gè)Bucket構(gòu)建hash table，也無(wú)需在Map端緩存整個(gè)Bucket數(shù)據(jù)的，每個(gè)Mapper只需按順序逐個(gè)key讀取兩個(gè)分桶的數(shù)據(jù)進(jìn)行join即可。

4.2 使用說(shuō)明

（1）map join

Map Join有兩種觸發(fā)方式，一種是用戶在SQL語(yǔ)句中增加hint提示，另外一種是Hive優(yōu)化器根據(jù)參與join表的數(shù)據(jù)量大小，自動(dòng)觸發(fā)。

1）Hint提示

用戶可通過(guò)如下方式，指定通過(guò)map join算法，并且ta將作為map join中的小表。這種方式已經(jīng)過(guò)時(shí)，不推薦使用。

hive (default)> 
select /*+ mapjoin(ta) */
    ta.id,
    tb.id
from table_a ta
join table_b tb
on ta.id=tb.id;

2）自動(dòng)觸發(fā)

Hive在編譯SQL語(yǔ)句階段，起初所有的join操作均采用Common Join算法實(shí)現(xiàn)。

之后在物理優(yōu)化階段，Hive會(huì)根據(jù)每個(gè)Common Join任務(wù)所需表的大小判斷該Common Join任務(wù)是否能夠轉(zhuǎn)換為Map Join任務(wù)，若滿足要求，便將Common Join任務(wù)自動(dòng)轉(zhuǎn)換為Map Join任務(wù)。

但有些Common Join任務(wù)所需的表大小，在SQL的編譯階段是未知的（例如對(duì)子查詢進(jìn)行join操作），所以這種Common Join任務(wù)是否能轉(zhuǎn)換成Map Join任務(wù)在編譯階是無(wú)法確定的。

針對(duì)這種情況，Hive會(huì)在編譯階段生成一個(gè)條件任務(wù)（Conditional Task），其下會(huì)包含一個(gè)計(jì)劃列表，計(jì)劃列表中包含轉(zhuǎn)換后的Map Join任務(wù)以及原有的Common Join任務(wù)。

• 這個(gè)條件任務(wù)會(huì)包含所有可能的map join任務(wù)。
• 原有的Common Join任務(wù)是作為一個(gè)后備任務(wù)的。

最終具體采用哪個(gè)計(jì)劃，是在運(yùn)行時(shí)決定的。大致思路如下圖所示：

• 在表已知大小的情況下，就不需要使用這個(gè)conditional task了。

假設(shè)現(xiàn)在是a表 join b表

尋找大表候選人階段：
1、如果是left join，則大表候選人為a表。
2、如果是inner join，則大表候選人為a表和b表。
3、如果是right join，則大表候選人為b表。
4、如果是full join，則這種情況下無(wú)法進(jìn)行map join。因?yàn)檫@時(shí)候必須保證返回a和b的全部數(shù)據(jù)。但是map join的原理是緩存大表，遍歷小表，因此無(wú)法做到。

圖中涉及到的參數(shù)如下：

1、啟動(dòng)Map Join自動(dòng)轉(zhuǎn)換

set hive.auto.convert.join=true;

2、一個(gè)Common Join operator轉(zhuǎn)為Map Join operator的判斷條件,若該Common Join相關(guān)的表中,存在n-1張表的已知大小總和<=該值,則生成一個(gè)Map Join計(jì)劃,此時(shí)可能存在多種n-1張表的組合均滿足該條件,則hive會(huì)為每種滿足條件的組合均生成一個(gè)Map Join計(jì)劃,同時(shí)還會(huì)保留原有的Common Join計(jì)劃作為后備(back up)計(jì)劃,實(shí)際運(yùn)行時(shí),優(yōu)先執(zhí)行Map Join計(jì)劃，若不能執(zhí)行成功，則啟動(dòng)Common Join后備計(jì)劃。

set hive.mapjoin.smalltable.filesize=250000;

3、開(kāi)啟無(wú)條件轉(zhuǎn)Map Join

set hive.auto.convert.join.noconditionaltask=true;

4、無(wú)條件轉(zhuǎn)Map Join時(shí)的小表之和閾值,若一個(gè)Common Join operator相關(guān)的表中，存在n-1張表的大小總和<=該值,此時(shí)hive便不會(huì)再為每種n-1張表的組合均生成Map Join計(jì)劃,同時(shí)也不會(huì)保留Common Join作為后備計(jì)劃。而是只生成一個(gè)最優(yōu)的Map Join計(jì)劃。

set hive.auto.convert.join.noconditionaltask.size=10000000;

（2）map join案例

（1）首先查看下面的sql語(yǔ)句優(yōu)化前是如何執(zhí)行的。

• 可以看到這是多表join，并且關(guān)聯(lián)的字段是不同的。字段不同，因此是兩個(gè)common join task。

不進(jìn)行優(yōu)化，所以下面這個(gè)參數(shù)需要關(guān)閉，下面這個(gè)參數(shù)是自動(dòng)進(jìn)行map join優(yōu)化的子開(kāi)關(guān)。

set hive.auto.convert.join=false;

使用explain查看執(zhí)行計(jì)劃

• stage1做了什么？

stage1的第一個(gè)tablescan

stage1的第二個(gè)tablescan

stage1的reduce階段

• stage2做了什么？

經(jīng)過(guò)上面的分析發(fā)現(xiàn)：

我們自己寫的sql語(yǔ)句的多表join的順序，和真正執(zhí)行計(jì)劃當(dāng)中表的join順序是不同的。hive會(huì)選取最小代價(jià)的方式進(jìn)行多表join。

（2）優(yōu)化思路

• 進(jìn)行優(yōu)化的時(shí)候，必須考慮表的大小，不能脫離表的大小去考慮優(yōu)化思路。

經(jīng)分析，參與join的三張表，數(shù)據(jù)量如下

注：可使用如下語(yǔ)句獲取表/分區(qū)的大小信息

hive (default)> 
desc formatted table_name partition(partition_col='partition');

通過(guò)partition(partition_col=‘partition’)，這個(gè)參數(shù)，則只會(huì)打印’partition這個(gè)分區(qū)的信息了。

三張表中，product_info和province_info數(shù)據(jù)量較小，可考慮將其作為小表，進(jìn)行Map Join優(yōu)化。

根據(jù)前文Common Join任務(wù)轉(zhuǎn)Map Join任務(wù)的判斷邏輯圖，可得出以下優(yōu)化方案：

方案一：（9min41s）

啟用Map Join自動(dòng)轉(zhuǎn)換。

hive (default)> 
set hive.auto.convert.join=true;

不使用無(wú)條件轉(zhuǎn)Map Join，因此會(huì)產(chǎn)生條件任務(wù)。

hive (default)> 
set hive.auto.convert.join.noconditionaltask=false;

調(diào)整hive.mapjoin.smalltable.filesize參數(shù)，使其大于等于product_info。這樣的話可以保證product_info表和province_info表都放到內(nèi)存里面。

hive (default)> 
set hive.mapjoin.smalltable.filesize=25285707;

這樣可保證將兩個(gè)Common Join operator均可轉(zhuǎn)為Map Join operator，并保留Common Join作為后備計(jì)劃，保證計(jì)算任務(wù)的穩(wěn)定。

調(diào)整優(yōu)化參數(shù)之后再次查看執(zhí)行計(jì)劃：

• stage5和stage6是將第三張表和中間結(jié)果各自當(dāng)成大表，生成的執(zhí)行任務(wù)。

將流程圖放大如下所示：

方案二：（4min52s）

啟用Map Join自動(dòng)轉(zhuǎn)換。

hive (default)> 
set hive.auto.convert.join=true;

使用無(wú)條件轉(zhuǎn)Map Join。也就是不需要條件任務(wù)了。因?yàn)槲覀內(nèi)龔埍淼拇笮《贾懒?，就不需要了?/mark>

hive (default)> 
set hive.auto.convert.join.noconditionaltask=true;

沒(méi)有條件任務(wù)之后，就不用再調(diào)整hive.mapjoin.smalltable.filesize參數(shù)了，而要調(diào)整

調(diào)整hive.auto.convert.join.noconditionaltask.size參數(shù)，使其大于等于product_info和province_info之和。

hive (default)> 
set hive.auto.convert.join.noconditionaltask.size=25286076;

這樣可直接將兩個(gè)Common Join operator轉(zhuǎn)為兩個(gè)Map Join operator，并且由于兩個(gè)Map Join operator的小表大小之和小于等于hive.auto.convert.join.noconditionaltask.size，故兩個(gè)Map Join operator任務(wù)可合并為同一個(gè)。這個(gè)方案計(jì)算效率最高，但需要的內(nèi)存也是最多的。

方案二的執(zhí)行計(jì)劃如下圖所示，相比于方案一要簡(jiǎn)潔很多。

• 分析：為什么方案二比方案一更快
  方案一雖然兩個(gè)都是map join，但是沒(méi)有進(jìn)行合并。方案二不要條件任務(wù)，并且在內(nèi)存充足的情況下，可以將兩個(gè)map join進(jìn)行合并，

方案三：（時(shí)間和方案一差不多）

啟用Map Join自動(dòng)轉(zhuǎn)換。

hive (default)> 
set hive.auto.convert.join=true;

使用無(wú)條件轉(zhuǎn)Map Join。

hive (default)> 
set hive.auto.convert.join.noconditionaltask=true;

調(diào)整hive.auto.convert.join.noconditionaltask.size參數(shù)，使其等于product_info。

hive (default)> 
set hive.auto.convert.join.noconditionaltask.size=25285707;

這樣可直接將兩個(gè)Common Join operator轉(zhuǎn)為Map Join operator，但不會(huì)將兩個(gè)Map Join的任務(wù)合并。該方案計(jì)算效率比方案二低，但需要的內(nèi)存也更少。

• 需要注意的是，文件在磁盤當(dāng)中占用的空間，和加載到內(nèi)存當(dāng)中占用空間的大小是不同的。例如：數(shù)據(jù)從文件當(dāng)中加載到內(nèi)存當(dāng)中需要有一個(gè)解序列化的過(guò)程，解序列化之后數(shù)據(jù)會(huì)變大的，除此之外，數(shù)據(jù)來(lái)到內(nèi)存當(dāng)中，可能會(huì)封裝成對(duì)象，也會(huì)有一些額外的開(kāi)銷。這種情況下文件的大小是遠(yuǎn)小于加載到內(nèi)存當(dāng)中的大小的。大小一般是10倍的差距。也就是如果文件是1G的話，內(nèi)存當(dāng)中會(huì)是10G。

（3）Bucket Map Join

在MR當(dāng)中，Bucket Map Join不支持自動(dòng)轉(zhuǎn)換，發(fā)須通過(guò)用戶在SQL語(yǔ)句中提供如下Hint提示，并配置如下相關(guān)參數(shù)，方可使用。

1）Hint提示

hive (default)> 
select /*+ mapjoin(ta) */
    ta.id,
    tb.id
from table_a ta
join table_b tb on ta.id=tb.id;

2）相關(guān)參數(shù)

1、關(guān)閉cbo優(yōu)化，cbo會(huì)導(dǎo)致hint信息被忽略

set hive.cbo.enable=false;

2、map join hint默認(rèn)會(huì)被忽略(因?yàn)橐呀?jīng)過(guò)時(shí))，需將如下參數(shù)設(shè)置為false

set hive.ignore.mapjoin.hint=false;

3、啟用bucket map join優(yōu)化功能

set hive.optimize.bucketmapjoin = true;

（4）Bucket Map Join案例

1）示例SQL

hive (default)> 
select
    *
from(
    select
        *
    from order_detail
    where dt='2020-06-14'
)od
join(
    select
        *
    from payment_detail
    where dt='2020-06-14'
)pd
on od.id=pd.order_detail_id;

2）優(yōu)化前

上述SQL語(yǔ)句共有兩張表一次join操作，故優(yōu)化前的執(zhí)行計(jì)劃應(yīng)包含一個(gè)Common Join任務(wù)，通過(guò)一個(gè)MapReduce Job實(shí)現(xiàn)。執(zhí)行計(jì)劃如下圖所示：

• 上面的圖使用的是common join

3）優(yōu)化思路

經(jīng)分析，參與join的兩張表，數(shù)據(jù)量如下。

• 這里的大小是在底層文件的大小，而不是在內(nèi)存當(dāng)中的大小。

如果此時(shí)使用map join將payment_detail當(dāng)成小表的話，按照之前的規(guī)律，319M*10大于3G，在內(nèi)存當(dāng)中需要占用3G多才能緩存小表的Hash表。

因此這個(gè)使用考慮使用bucket map join。首先確保這兩張表是分桶表，分桶個(gè)數(shù)成倍數(shù)，且兩張表的分桶字段需要相同。

首先需要依據(jù)源表創(chuàng)建兩個(gè)分桶表，order_detail建議分16個(gè)bucket，payment_detail建議分8個(gè)bucket,注意分桶個(gè)數(shù)的倍數(shù)關(guān)系以及分桶字段。

–訂單表

hive (default)> 
drop table if exists order_detail_bucketed;
create table order_detail_bucketed(
    id           string comment '訂單id',
    user_id      string comment '用戶id',
    product_id   string comment '商品id',
    province_id  string comment '省份id',
    create_time  string comment '下單時(shí)間',
    product_num  int comment '商品件數(shù)',
    total_amount decimal(16, 2) comment '下單金額'
)
clustered by (id) into 16 buckets
row format delimited fields terminated by '\t';

–支付表

hive (default)> 
drop table if exists payment_detail_bucketed;
create table payment_detail_bucketed(
    id              string comment '支付id',
    order_detail_id string comment '訂單明細(xì)id',
    user_id         string comment '用戶id',
    payment_time    string comment '支付時(shí)間',
    total_amount    decimal(16, 2) comment '支付金額'
)
clustered by (order_detail_id) into 8 buckets
row format delimited fields terminated by '\t';

然后向兩個(gè)分桶表導(dǎo)入數(shù)據(jù)。

訂單表：

hive (default)> 
insert overwrite table order_detail_bucketed
select
    id,
    user_id,
    product_id,
    province_id,
    create_time,
    product_num,
    total_amount   
from order_detail
where dt='2020-06-14';

分桶表：

hive (default)> 
insert overwrite table payment_detail_bucketed
select
    id,
    order_detail_id,
    user_id,
    payment_time,
    total_amount
from payment_detail
where dt='2020-06-14';

然后設(shè)置以下參數(shù)：

1、關(guān)閉cbo優(yōu)化，cbo會(huì)導(dǎo)致hint信息被忽略，需將如下參數(shù)修改為false

set hive.cbo.enable=false;

2、map join hint默認(rèn)會(huì)被忽略(因?yàn)橐呀?jīng)過(guò)時(shí))，需將如下參數(shù)修改為false

set hive.ignore.mapjoin.hint=false;

3、啟用bucket map join優(yōu)化功能,默認(rèn)不啟用，需將如下參數(shù)修改為true

set hive.optimize.bucketmapjoin = true;

最后在重寫SQL語(yǔ)句，如下：

hive (default)> 
select /*+ mapjoin(pd) */
    *
from order_detail_bucketed od
join payment_detail_bucketed pd on od.id = pd.order_detail_id;

優(yōu)化后的執(zhí)行計(jì)劃如圖所示：

• 上面的圖使用的是map join

（5） Sort Merge Bucket Map Join

Sort Merge Bucket Map Join有兩種觸發(fā)方式，包括Hint提示和自動(dòng)轉(zhuǎn)換。Hint提示已過(guò)時(shí)，不推薦使用。

下面是自動(dòng)轉(zhuǎn)換的相關(guān)參數(shù)：

1、啟動(dòng)Sort Merge Bucket Map Join優(yōu)化

set hive.optimize.bucketmapjoin.sortedmerge=true;

2、使用自動(dòng)轉(zhuǎn)換SMB Join

set hive.auto.convert.sortmerge.join=true;

使用和上一個(gè)案例相同的數(shù)據(jù)（分桶之后多加了一個(gè)桶內(nèi)有序），得到的結(jié)果如下圖所示：

文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-813990.html

到了這里，關(guān)于【Hive_05】企業(yè)調(diào)優(yōu)1（資源配置、explain、join優(yōu)化）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！