Hive 實踐 | Apache Hive 提高查詢效率的八條軍規(guī)
編輯：數據社，作者：數據一哥
大家好，我是一哥，今天分享一下Hive如何提升查詢效率。Hive作為最常用的數倉計算引擎，是我們必備的技能，但是很多人只是會寫Hql，并不會優(yōu)化，也不知道如何提升查詢效率，今天分享8條軍規(guī)：
1、開啟FetchTask
一個簡單的查詢語句，是指一個沒有函數、排序等功能的語句，當開啟一個Fetch Task功能，就執(zhí)行一個簡單的查詢語句不會生成MapRreduce作業(yè)，而是直接使用FetchTask，從hdfs文件系統(tǒng)中進行查詢輸出數據，從而提高效率。
設置的方式：
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、合并中間表
一個日志文件中，每一行記錄，會有很多很多字段，四五十個字段很正常。實際分析中，常常使用少數幾個字段將原始的表中數據，依據業(yè)務需求提取出要分析的字段，數據放入到對應的業(yè)務表（子表）中，實際的業(yè)務針對業(yè)務表進行分析。
在實際中，我們會發(fā)現，有些業(yè)務處理，會有共同數據集用戶表、訂單表、商品表，三個表需要進行join的操作，join 會產生一個結果集，會有很多的業(yè)務是針對此jion結果集進行分析。
優(yōu)化：將眾多的業(yè)務中相同的中間結果集，抽取到一個Hive中的表中去。
3、合理使用分區(qū)表
外部表、分區(qū)表，結合使用，采用多級分區(qū)。數據采用存儲格式（textfile、orcfile、parquet）或者數據壓縮(snappy)。
明細數據我們一般采用按天分區(qū)，對于特別大的表，可以采用子分區(qū)，每個分區(qū)其實對應到HDFS上就是一個目錄。數據存儲方式我們可以采用parquet列式存儲，同時具有很好的壓縮性能；同時可以減少大量的表掃描和反序列化的時間。在OLAP查詢場景下，我們選擇需要的列信息進行查詢，而不是直接select * 查詢所有字段。
4、jvm重用
JVM重用是hadoop調優(yōu)參數的內容，對hive的性能具有非常大的影響，特別是對于很難避免小文件的場景或者task特別多的場景，這類場景大多數執(zhí)行時間都很短。hadoop默認配置是使用派生JVM來執(zhí)行map和reduce任務的，這是jvm的啟動過程可能會造成相當大的開銷，尤其是執(zhí)行的job包含有成千上萬個task任務的情況。JVM重用可以使得JVM實例在同一個JOB中重新使用N次，N的值可以在Hadoop的mapre-site.xml文件中進行設置
mapred.job.reuse.jvm.num.tasks 1
也可在hive的執(zhí)行設置：
set mapred.job.reuse.jvm.num.tasks = 10;
JVM的一個缺點是，開啟JVM重用將會一直占用使用到的task插槽，以便進行重用，直到任務完成后才能釋放。如果某個“不平衡“的job中有幾個reduce task 執(zhí)行的時間要比其他reduce task消耗的時間多得多的話，那么保留的插槽就會一直空閑著卻無法被其他的job使用，直到所有的task都結束了才會釋放。
5、speculative execution（推測執(zhí)行）
所謂的推測執(zhí)行，就是當所有task都開始運行之后，Job Tracker會統(tǒng)計所有任務的平均進度，如果某個task所在的task node機器配置比較低或者CPU load很高（原因很多），導致任務執(zhí)行比總體任務的平均執(zhí)行要慢，此時Job Tracker會啟動一個新的任務（duplicate task），原有任務和新任務哪個先執(zhí)行完就把另外一個kill掉。
推測執(zhí)行需要設置Job的兩個參數：
mapred.map.tasks.speculative.execution=true
mapred.reduce.tasks.speculative.execution=true
6、合理設置reduce個數
reduce個數
參數1：
hive.exec.reducers.bytes.per.reducer=256000000 //每個reduce任務處理的數據量
參數2：
hive.exec.reducers.max=1009 //每個任務最大的reduce數目
計算公式：reducer個數=min(參數2,總輸入數據量/參數1)
set mapred.reduce.tasks =N：
每個任務默認的reduce數目。典型為0.99* reduce槽數，hive默認為-1，即自動確定reduce數目。
reduce個數并不是越多越好
同map一樣，啟動和初始化reduce也會消耗時間和資源；另外，有多少個reduce，就會有多少個輸出文件，如果生成了很多個小文件，那么如果這些小文件作為下一個任務的輸入，則也會出現小文件過多的問題。小文件過多會非常影響查詢效率，文件越多造成的IO就越多，同時還會增加元數據（namenode）的壓力。在生產環(huán)境中，一定要避免小文件問題，如果核查發(fā)現，及時合并文件??！
7、開啟并行執(zhí)行
并行執(zhí)行，意思是同步執(zhí)行hive的多個階段，hive在執(zhí)行過程，將一個查詢轉化成一個或者多個階段。某個特定的job可能包含眾多的階段，而這些階段可能并非完全相互依賴的，也就是說可以并行執(zhí)行的，這樣可能使得整個job的執(zhí)行時間縮短
hive.exec.parallel.thread.number 8//job并行執(zhí)行的數目，一個SQL語句可能有很多mapreduce任務，限制
hive.exec.parallel false
hive執(zhí)行開啟：
set hive.exec.parallel=true
8、優(yōu)化sql
where條件優(yōu)化
優(yōu)化前（關系數據庫不用考慮會自動優(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 去掉重復的記錄）而是使用 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
如果需要根據一個表的字段來約束另為一個表，盡量用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 快
消滅子查詢內的 group by 、 COUNT(DISTINCT)，MAX，MIN。可以減少job的數量。
join 優(yōu)化：
Common/shuffle/Reduce JOIN：連接發(fā)生的階段，發(fā)生在reduce 階段，適用于大表連接大表(默認的方式)
Map join ：連接發(fā)生在map階段，適用于小表連接大表 大表的數據從文件中讀??；小表的數據存放在內存中（hive中已經自動進行了優(yōu)化，自動判斷小表，然后進行緩存）。
set hive.auto.convert.join=true;
SMB join：Sort -Merge -Bucket Join 對大表連接大表的優(yōu)化，用桶表的概念來進行優(yōu)化。在一個桶內發(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;