[[423380]]

大家好，我是一哥，今天分享一下Hive如何提升查詢效率。Hive作為最常用的數(shù)倉計(jì)算引擎，是我們必備的技能，但是很多人只是會寫Hql，并不會優(yōu)化，也不知道如何提升查詢效率，今天分享8條軍規(guī)：

我是一哥，現(xiàn)任某500強(qiáng)大數(shù)據(jù)分析負(fù)責(zé)人，在這里和大家分享大數(shù)據(jù)實(shí)踐的那些事，個(gè)人專注數(shù)據(jù)倉庫架構(gòu)，數(shù)據(jù)分析，MPP數(shù)據(jù)庫研究、流處理計(jì)算等領(lǐng)域~

1、開啟FetchTask

一個(gè)簡單的查詢語句，是指一個(gè)沒有函數(shù)、排序等功能的語句，當(dāng)開啟一個(gè)Fetch Task功能，就執(zhí)行一個(gè)簡單的查詢語句不會生成MapRreduce作業(yè)，而是直接使用FetchTask，從hdfs文件系統(tǒng)中進(jìn)行查詢輸出數(shù)據(jù)，從而提高效率。

設(shè)置的方式：

Hive.fetch.task.conversion 默認(rèn)為minimal 
  
修改配置文件hive-site.xml 
<property> 
  <name>hive.fetch.task.conversion</name> 
  <value>more</value> 
  <description> 
    Some select queries can be converted to single FETCH task  
    minimizing latency.Currently the query should be single  
    sourced not having any subquery and should not have 
    any aggregations or distincts (which incurrs RS),  
    lateral views and joins. 
    1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only 
    2. more    : SELECT, FILTER, LIMIT only (+TABLESAMPLE, virtual columns) 
  </description> 
</property>  
  
  
或者當(dāng)前session修改 
hive> set hive.fetch.task.conversion=more; 
執(zhí)行SELECT id, money FROM m limit 10; 不走mr 

2、合并中間表

一個(gè)日志文件中，每一行記錄，會有很多很多字段，四五十個(gè)字段很正常。實(shí)際分析中，常常使用少數(shù)幾個(gè)字段將原始的表中數(shù)據(jù)，依據(jù)業(yè)務(wù)需求提取出要分析的字段，數(shù)據(jù)放入到對應(yīng)的業(yè)務(wù)表(子表)中，實(shí)際的業(yè)務(wù)針對業(yè)務(wù)表進(jìn)行分析。

在實(shí)際中，我們會發(fā)現(xiàn)，有些業(yè)務(wù)處理，會有共同數(shù)據(jù)集用戶表、訂單表、商品表，三個(gè)表需要進(jìn)行join的操作，join 會產(chǎn)生一個(gè)結(jié)果集，會有很多的業(yè)務(wù)是針對此jion結(jié)果集進(jìn)行分析。

優(yōu)化：將眾多的業(yè)務(wù)中相同的中間結(jié)果集，抽取到一個(gè)Hive中的表中去。

3、合理使用分區(qū)表

外部表、分區(qū)表，結(jié)合使用，采用多級分區(qū)。數(shù)據(jù)采用存儲格式(textfile、orcfile、parquet)或者數(shù)據(jù)壓縮(snappy)。

明細(xì)數(shù)據(jù)我們一般采用按天分區(qū)，對于特別大的表，可以采用子分區(qū)，每個(gè)分區(qū)其實(shí)對應(yīng)到HDFS上就是一個(gè)目錄。數(shù)據(jù)存儲方式我們可以采用parquet列式存儲，同時(shí)具有很好的壓縮性能;同時(shí)可以減少大量的表掃描和反序列化的時(shí)間。在OLAP查詢場景下，我們選擇需要的列信息進(jìn)行查詢，而不是直接select * 查詢所有字段。

4、jvm重用

JVM重用是hadoop調(diào)優(yōu)參數(shù)的內(nèi)容，對hive的性能具有非常大的影響，特別是對于很難避免小文件的場景或者task特別多的場景，這類場景大多數(shù)執(zhí)行時(shí)間都很短。hadoop默認(rèn)配置是使用派生JVM來執(zhí)行map和reduce任務(wù)的，這是jvm的啟動(dòng)過程可能會造成相當(dāng)大的開銷，尤其是執(zhí)行的job包含有成千上萬個(gè)task任務(wù)的情況。JVM重用可以使得JVM實(shí)例在同一個(gè)JOB中重新使用N次，N的值可以在Hadoop的mapre-site.xml文件中進(jìn)行設(shè)置

mapred.job.reuse.jvm.num.tasks 1 

也可在hive的執(zhí)行設(shè)置：

set mapred.job.reuse.jvm.num.tasks = 10; 

JVM的一個(gè)缺點(diǎn)是，開啟JVM重用將會一直占用使用到的task插槽，以便進(jìn)行重用，直到任務(wù)完成后才能釋放。如果某個(gè)“不平衡“的job中有幾個(gè)reduce task 執(zhí)行的時(shí)間要比其他reduce task消耗的時(shí)間多得多的話，那么保留的插槽就會一直空閑著卻無法被其他的job使用，直到所有的task都結(jié)束了才會釋放。

5、speculative execution(推測執(zhí)行)

所謂的推測執(zhí)行，就是當(dāng)所有task都開始運(yùn)行之后，Job Tracker會統(tǒng)計(jì)所有任務(wù)的平均進(jìn)度，如果某個(gè)task所在的task node機(jī)器配置比較低或者CPU load很高(原因很多)，導(dǎo)致任務(wù)執(zhí)行比總體任務(wù)的平均執(zhí)行要慢，此時(shí)Job Tracker會啟動(dòng)一個(gè)新的任務(wù)(duplicate task)，原有任務(wù)和新任務(wù)哪個(gè)先執(zhí)行完就把另外一個(gè)kill掉。

推測執(zhí)行需要設(shè)置Job的兩個(gè)參數(shù)：

mapred.map.tasks.speculative.execution=true 
mapred.reduce.tasks.speculative.execution=true 

6、合理設(shè)置reduce個(gè)數(shù)

reduce個(gè)數(shù)

參數(shù)1：

hive.exec.reducers.bytes.per.reducer=256000000 //每個(gè)reduce任務(wù)處理的數(shù)據(jù)量 

參數(shù)2：

hive.exec.reducers.max=1009 //每個(gè)任務(wù)最大的reduce數(shù)目 

計(jì)算公式：reducer個(gè)數(shù)=min(參數(shù)2,總輸入數(shù)據(jù)量/參數(shù)1)

set mapred.reduce.tasks =N：

每個(gè)任務(wù)默認(rèn)的reduce數(shù)目。典型為0.99* reduce槽數(shù)，hive默認(rèn)為-1，即自動(dòng)確定reduce數(shù)目。

reduce個(gè)數(shù)并不是越多越好

同map一樣，啟動(dòng)和初始化reduce也會消耗時(shí)間和資源;另外，有多少個(gè)reduce，就會有多少個(gè)輸出文件，如果生成了很多個(gè)小文件，那么如果這些小文件作為下一個(gè)任務(wù)的輸入，則也會出現(xiàn)小文件過多的問題。小文件過多會非常影響查詢效率，文件越多造成的IO就越多，同時(shí)還會增加元數(shù)據(jù)(namenode)的壓力。在生產(chǎn)環(huán)境中，一定要避免小文件問題，如果核查發(fā)現(xiàn)，及時(shí)合并文件!!

7、開啟并行執(zhí)行

并行執(zhí)行，意思是同步執(zhí)行hive的多個(gè)階段，hive在執(zhí)行過程，將一個(gè)查詢轉(zhuǎn)化成一個(gè)或者多個(gè)階段。某個(gè)特定的job可能包含眾多的階段，而這些階段可能并非完全相互依賴的，也就是說可以并行執(zhí)行的，這樣可能使得整個(gè)job的執(zhí)行時(shí)間縮短

hive.exec.parallel.thread.number    8//job并行執(zhí)行的數(shù)目，一個(gè)SQL語句可能有很多mapreduce任務(wù)，限制 
hive.exec.parallel  false 

hive執(zhí)行開啟：

set hive.exec.parallel=true 

8、優(yōu)化sql

• where條件優(yōu)化

優(yōu)化前(關(guān)系數(shù)據(jù)庫不用考慮會自動(dòng)優(yōu)化)：

select m.cid,u.id from order m join customer u on( m.cid =u.id )where m.dt='20180808'; 

優(yōu)化后(where條件在map端執(zhí)行而不是在reduce端執(zhí)行)：

select m.cid,u.id from (select * from order where dt='20180818') m join customer u on( m.cid =u.id); 

• union優(yōu)化

盡量不要使用union (union 去掉重復(fù)的記錄)而是使用 union all 然后在用group by 去重

• count distinct優(yōu)化

不要使用count (distinct cloumn) ,使用子查詢。

select count(1) from (select id from tablename group by id) tmp; 

• 用in 來代替join

如果需要根據(jù)一個(gè)表的字段來約束另為一個(gè)表，盡量用in來代替join 。

select id,name from tb1 a join tb2 b on(a.id = b.id); 
 
select id,name from tb1 where id in(select id from tb2); 

in 要比join 快

• 消滅子查詢內(nèi)的 group by 、 COUNT(DISTINCT)，MAX，MIN?？梢詼p少job的數(shù)量。
• join 優(yōu)化：

Common/shuffle/Reduce JOIN：連接發(fā)生的階段，發(fā)生在reduce 階段，適用于大表連接大表(默認(rèn)的方式)

Map join ：連接發(fā)生在map階段，適用于小表連接大表 大表的數(shù)據(jù)從文件中讀取;小表的數(shù)據(jù)存放在內(nèi)存中(hive中已經(jīng)自動(dòng)進(jìn)行了優(yōu)化，自動(dòng)判斷小表，然后進(jìn)行緩存)。

set hive.auto.convert.join=true; 

SMB join：Sort -Merge -Bucket Join 對大表連接大表的優(yōu)化，用桶表的概念來進(jìn)行優(yōu)化。在一個(gè)桶內(nèi)發(fā)送生笛卡爾積連接(需要是兩個(gè)桶表進(jìn)行join)

set hive.auto.convert.sortmerge.join=true; 
 
set hive.optimize.bucketmapjoin = true; 
 
set hive.optimize.bucketmapjoin.sortedmerge = true; 
 
set hive.auto.convert.sortmerge.join.noconditionaltask=true;