[[423380]]

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

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

1、開啟FetchTask

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

設(shè)置的方式：

Hive.fetch.task.conversion 默認為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>  
  
  
或者當前session修改 
hive> set hive.fetch.task.conversion=more; 
執(zhí)行SELECT id, money FROM m limit 10; 不走mr 

2、合并中間表

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

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

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

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

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

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

4、jvm重用

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

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

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

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

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

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

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

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

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

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

reduce個數(shù)

參數(shù)1：

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

參數(shù)2：

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

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

set mapred.reduce.tasks =N：

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

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

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

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

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

hive.exec.parallel.thread.number    8//job并行執(zhí)行的數(shù)目，一個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ù)庫不用考慮會自動優(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ù)一個表的字段來約束另為一個表，盡量用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 階段，適用于大表連接大表(默認的方式)

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

set hive.auto.convert.join=true; 

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