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