Hive調優的幾個入手點： Hive是基於Hadoop框架的，Hadoop框架又是運行在JVM中的，而JVM最終是要運行在操作系統之上的，所以，Hive的調優可以通過如下幾個方面入手： 操作系統調優 - Hadoop主要的操作系統是Linux，Linux系統調優包括文件系統的選擇、cpu的調度、記憶體 ...
Hive調優的幾個入手點：
Hive是基於Hadoop框架的，Hadoop框架又是運行在JVM中的，而JVM最終是要運行在操作系統之上的，所以，Hive的調優可以通過如下幾個方面入手：
- 操作系統調優
- Hadoop主要的操作系統是Linux，Linux系統調優包括文件系統的選擇、cpu的調度、記憶體構架和虛擬記憶體的管理、IO調度和網路子系統的選擇等等。
- JVM的調優
- JVM調優主要包括堆棧的大小、回收器的選擇等等。
- Hadoop參數調優
- Hive查詢sql性能調優。
Hive總體調優：
- join連接時的優化
- 當多個表進行查詢時，從左到右表的大小順序應該是從小到大（hive在對每行記錄操作時會把其他表先緩存起來，直到掃描最後的表進行計算）。
- 當可以使用left semi join 語法時不要使用inner join，前者效率更高（對於左表中指定的一條記錄，一旦在右表中找到立即停止掃描）。
- 在where子句中增加分區過濾器。
- 使用記憶體表（mapjoin）
- 如果所有表中有一張表足夠小，則可置於記憶體中，這樣在和其他表進行連接的時候就能完成匹配，省略掉reduce過程。
- 記憶體連接查詢 mapjoin：
在map端完成join操作，不需要用reduce，基於記憶體做join，屬於優化操作。
在map端把小表載入到記憶體中，然後讀取大表，和記憶體中的小表完成連接操作。其中使用了分散式緩存技術。
不消耗集群的reduce資源（適用於reduce相對緊缺）,減少了reduce操作，加快程式執行，降低網路負載。
占用部分記憶體，所以載入到記憶體中的表不能過大，因為每個計算節點都會載入一次。
- 基礎語法
select /*+mapjoin(載入入記憶體的表別名)*/ 表別名1.列1,表別名1.列2,表別名2.列3...
from (select 列1,列2,列3... from 表1) 表別名1
join (select 列1,列2,列3... from 表2) 表別名2
on 表別名1.列1=表別名2.列1
- 同一種數據的多種處理
- 從一個數據源產生的多個數據聚合，無需每次聚合都需要重新掃描一次。
例如，從employee中取出數據分別插入student和person兩張表。
低效的寫法： insert overwrite table student select * from employee; insert overwrite table person select * from employee;
高效的寫法： from employee insert overwrite table student select * insert overwrite table person select *
- 使用limit子句
- limit子句用於限制返回數據的結果集大小。
- limit子句通常位於所有查詢的結尾處。
- limit子句示例：
select t1.*,t2.score,t3.score from Student t1
inner join SC t2 on t1.Sid = t2.Sid and t2.Cid = '01'
inner join SC t3 on t1.Sid = t3.Sid and t3.Cid = '02'
where t2.score > t3.score limit 1;
- 設置多個reduce並開啟併發執行
- 某個job任務中可能包含眾多的階段，其中某些階段沒有依賴關係可以併發執行，開啟併發執行後job任務可以更快的完成。
- 開啟併發執行：set hive.exec.parallel=true
- hive的使用禁忌：
- 當表為分區表時，where字句後沒有分區欄位和限制時，不允許執行。
- 能使用sort by排序的，不要使用order by，當使用order by語句時，請使用limit欄位，因為order by只會產生一個reduce任務。
- 限制笛卡爾積的查詢。
Hive排序調優
- 假設我們有一張數據量很大的表，表結構如下
我們希望對裡面多個欄位分組排序，sql如下：
select t1.ip,t1.logtime,t1.logmessage,t1.logstatus from
(select ip,logtime,logmessage,logstatus,logsize from logfile
order by ip,logtime,logmessage,logstatus,logsize asc) t1
group by t1.ip,t1.logtime,t1.logmessage,t1.logstatus limit 100;
很明顯，這條sql的reduce階段只有一個reduce， 這是因為ORDER BY是全局排序，hive只能通過一個reduce進行排序；
優化方案：我們可以使用distribute by和sort by配合使用，來完成排序，這樣可以充分利用hadoop資源， 在多個reduce中局部排序，修改後的sql:
select t1.ip,t1.logtime,t1.logmessage,t1.logstatus from
(select ip,logtime,logmessage,logstatus,logsize from logfile
distribute by ip,logtime,logmessage,logstatus
sort by logsize asc) t1
group by t1.ip,t1.logtime,t1.logmessage,t1.logstatus;
Map數量調優
- 通常情況下，作業會通過input的目錄（數據塊的分佈）產生一個或者多個map任務。
- 主要的決定因素有： input的文件總個數，input的文件大小，集群設置的文件塊大小。
- map分佈實例
- 假設input目錄下有1個文件a,大小為780M,那麼hadoop會將該文件a分隔成7個塊（6個128m的塊和1個12m的塊），從而產生7個map數。
- 假設input目錄下有3個文件a,b,c,大小分別為10m，20m，130m，那麼hadoop會分隔成4個塊（10m,20m,128m,2m）,從而產生4個map數。
- 是不是map數越多越好？
- 如果一個任務有很多小文件（遠遠小於塊大小128m）,則每個小文件也會被當做一個塊，用一個map任務來完成。
- 一個map任務啟動和初始化的時間遠遠大於邏輯處理的時間，就會造成很大的資源浪費。而且，同時可執行的map數是受限的。 所以map不是越多越好，而是分塊大小越接近128越好。 這種情況可以合併小文件，降低map數量。
- 是不是所有分塊大小越接近128越好？
- 比如有一個127m的文件，正常會用一個map去完成，但這個文件只有一個或者兩個小欄位，卻有幾千萬的記錄，如果map處理的邏輯比較複雜，用一個map任務去做，肯定也比較耗時。 這種情況可以拆分文件，添加map數量。
- 所以，map數量的多少，要根據業務邏輯具體調整，並通過文件大小調節map數量。
-
hive合併小文件，減少map數量的設置參數（根據實際情況調整）
- set mapred.max.split.size;
- set mapred.min.split.size.per.node;
- set mapred.min.split.size.per.rack;
- set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
- hive拆分大文件，增加map數量
- set mapred.reduce.tasks
Reduce數量調優
- 是不是reduce數越多越好？
- 同map一樣，啟動和初始化reduce也會消耗時間和資源。 有多少個reduce,就會有多少個輸出文件，如果生成了很多個小文件，那麼如果這些小文件作為下一個任務的輸入，則也會出現小文件過多的問題。
- 同樣，在設置reduce個數的時候也需要考慮這兩個原則：
- 使大數據量利用合適的reduce數；
- 使單個reduce任務處理合適的數據量。
- 預設reduce數量
- hive.exec.reducers.bytes.per.reducer（每個reduce任務處理的數據量，預設為1000^3=1G）
- 計算reducer數的公式：總輸入數據量/上述參數，如果reduce的輸入（map的輸出）總大小不超過1G,那麼只會有一個reduce任務
- 調整reduce數量的方法
- set hive.exec.reducers.bytes.per.reducer=500000000（動態計算）
- set mapred.reduce.tasks = 15（可直接設置數量）
- 很多時候我們會發現任務中不管數據量多大，不管有沒有設置調整reduce個數的參數，任務中一直都只有一個reduce任務，出現這種情況的原因：
- 沒有group by的彙總或用了Order by（常見）
- 有笛卡爾積
Sql具體優化示例
- 關於子查詢
- 過濾子查詢中的數據，減少子查詢中的數據量。
- 對於分區表要加分區。
- 子查詢只選擇需要使用到的欄位。
- 低效寫法：
select a.user_id from dwd.dwd_d_res_mb_five_imei a
inner join dwd.dwd_d_prd_cm_user_info b on a.user_id=b.user_id
where a.service_type='4G' and b.service_type='4G'and
concat(a.month_id,a.day_id)='20160626‘ and b.day_id='26';
- 高效寫法：
select a.user_id from
(select user_id from dwd.dwd_d_res_mb_five_imei a
where concat(a.month_id,a.day_id)='20160626' and a.service_type='4G') a
inner join
(select user_id from dwd.dwd_d_prd_cm_user_info b
where b.day_id='26' and b.service_type='4G') b on a.user_id=b.user_id;
- 合理使用union all
- 子查詢中union all部分個數大於2，或者每個union all部分數據量很大，應該拆分多段insert。這樣執行時間能提升50%。
- 低效寫法：
insert overwite table tablename partition (day_id= ....)
select ..... from (
select ... from A union all
select ... from B union all
select ... from C) R
where ...;
- 高效寫法:
insert into table tablename partition (day_id= ....)
select .... from A
WHERE ...;
insert into table tablename partition (day_id= ....)
select .... from B
WHERE ...;
insert into table tablename partition (day_id= ....)
select .... from C
WHERE ...;
- 不要使用count(distinct)，避免數據傾斜
- count(distinct)操作會造成數據傾斜，效率較低，數據量一多，極容易出問題。
- 低效寫法：
select a, count(distinct b) as c from tbl group by a;
- 高效寫法：
select a, count(1) as c from (select a, b from tbl group by a, b) t group by a;
- hive中沒有in/exists (not)，使用LEFT OUTER JOIN或LEFT SEMI JOIN
- LEFT OUTER JOIN寫法：
SELECT a.key, a.value FROM a LEFT OUTER JOIN b ON (a.key = b.key) WHERE b.key is not NULL and b.key<>’’;
- LEFT SEMI JOIN更為高效，
LEFT SEMI JOIN 的限制是，JOIN 子句中右邊的表只能在 ON 子句中設置過濾條件，在 WHERE 子句、SELECT 子句或其他地方過濾都不行。
- 減少job數
- 在開發過程中，會生成多餘Job不夠高效比如查詢某網站日誌中訪問過頁面a和頁面b的用戶數量
- 低效的寫法是面向明細的，先取出看過頁面a的用戶，再取出看過頁面b的用戶，然後取交集，sql如下：
select count(1) from
(select distinct user_id from logs where page_name = 'a') a
inner join
(select distinct user_id from logs where page_name = 'b') b
on a.user_id = b.user_id;
- 這個sql會產生2個求子查詢的Job，一個用於關聯的Job，還有一個計數的Job，一共有4個Job。
- 高效思路是用group by替代join，更加符合M/R的模式，而且生成了一個完全不帶子查詢的sql，只需要用一個Job就能跑完：
select count(1) from logs
group by user_id
having (count(case when page_name = 'a' then 1 end) > 0
and count(case when page_name = 'b' then 1 end) > 0)
其它優化註意事項
- 查詢sql中避免複雜邏輯，原子化操作，查詢sql包含複雜邏輯的，可以拆分成中間表。
- join連接key為空時，空的key都hash到一個reduce上去了。高效做法是把空的key和非空的key做區分，空的key不做join操作。
