hive企業級調優

1 Fetch抓取
Fetch抓取是指，Hive中對某些情況的查詢可以不必使用MapReduce計算。例如：SELECT * FROM employees;在這種情況下，Hive可以簡單地讀取employee對應的儲存目錄下的檔案，然後輸出查詢結果到控制檯。
在hive-default.xml.template檔案中hive.fetch.task.conversion預設是more，老版本hive預設是minimal，該屬性修改為more以後，在全域性查詢、欄位查詢、limit查詢等都不走mapreduce。

hive.fetch.task.conversion
more

Expects one of [none, minimal, more].
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 incurs RS), lateral views and joins.
0. none : disable hive.fetch.task.conversion
1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
2. more : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)


案例實操：
1）把hive.fetch.task.conversion設定成none，然後執行查詢語句，都會執行mapreduce程式。
hive (default)> set hive.fetch.task.conversion=none;
hive (default)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;
2）把hive.fetch.task.conversion設定成more，然後執行查詢語句，如下查詢方式都不會執行mapreduce程式。
hive (default)> set hive.fetch.task.conversion=more;
hive (default)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;
9.2 本地模式
大多數的Hadoop Job是需要Hadoop提供的完整的可擴充套件性來處理大資料集的。不過，有時Hive的輸入資料量是非常小的。在這種情況下，為查詢觸發執行任務消耗的時間可能會比實際job的執行時間要多的多。對於大多數這種情況，Hive可以通過本地模式在單臺機器上處理所有的任務。對於小資料集，執行時間可以明顯被縮短。
使用者可以通過設定hive.exec.mode.local.auto的值為true，來讓Hive在適當的時候自動啟動這個優化。
set hive.exec.mode.local.auto=true; //開啟本地mr
//設定local mr的最大輸入資料量，當輸入資料量小於這個值時採用local mr的方式，預設為134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//設定local mr的最大輸入檔案個數，當輸入檔案個數小於這個值時採用local mr的方式，預設為4
set hive.exec.mode.local.auto.input.files.max=10;
案例實操：
1）開啟本地模式，並執行查詢語句
hive (default)> set hive.exec.mode.local.auto=true;
hive (default)> select * from emp cluster by deptno;
Time taken: 1.328 seconds, Fetched: 14 row(s)
2）關閉本地模式，並執行查詢語句
hive (default)> set hive.exec.mode.local.auto=false;
hive (default)> select * from emp cluster by deptno;
Time taken: 20.09 seconds, Fetched: 14 row(s)
9.3 表的優化
9.3.1 小表、大表Join
儘量的將小表放在join的前面
將key相對分散，並且資料量小的表放在join的左邊，這樣可以有效減少記憶體溢位錯誤發生的機率；再進一步，可以使用map join讓小的維度表（1000條以下的記錄條數）先進記憶體。在map端完成reduce。
實際測試發現：新版的hive已經對小表JOIN大表和大表JOIN小表進行了優化。小表放在左邊和右邊已經沒有明顯區別。
案例實操
1．需求
測試大表JOIN小表和小表JOIN大表的效率
2．建大表、小表和JOIN後表的語句
// 建立大表
create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by ‘\t’;
// 建立小表
create table smalltable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by ‘\t’;
// 建立join後表的語句
create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by ‘\t’;
3．分別向大表和小表中匯入資料
hive (default)> load data local inpath ‘/opt/module/datas/bigtable’ into table bigtable;
hive (default)>load data local inpath ‘/opt/module/datas/smalltable’ into table smalltable;
4．關閉mapjoin功能（預設是開啟的）
set hive.auto.convert.join = false;
5．執行小表JOIN大表語句
insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from smalltable s
left join bigtable b
on b.id = s.id;
Time taken: 35.921 seconds
No rows affected (44.456 seconds)
6．執行大表JOIN小表語句
insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable b
left join smalltable s
on s.id = b.id;
Time taken: 34.196 seconds
No rows affected (26.287 seconds)
9.3.2 大表Join大表
1．空KEY過濾
有時join超時是因為某些key對應的資料太多，而相同key對應的資料都會傳送到相同的reducer上，從而導致記憶體不夠。此時我們應該仔細分析這些異常的key，很多情況下，這些key對應的資料是異常資料，我們需要在SQL語句中進行過濾。例如key對應的欄位為空，操作如下：
案例實操
（1）配置歷史伺服器
配置mapred-site.xml

mapreduce.jobhistory.address
hadoop102:10020


mapreduce.jobhistory.webapp.address
hadoop102:19888

啟動歷史伺服器
sbin/mr-jobhistory-daemon.sh start historyserver
檢視jobhistory
http://doit01:19888/jobhistory
（2）建立原始資料表、空id表、合併後資料表
// 建立原始表
create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by ‘\t’;
// 建立空id表
create table nullidtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by ‘\t’;
// 建立join後表的語句
create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by ‘\t’;
（3）分別載入原始資料和空id資料到對應表中
hive (default)> load data local inpath ‘/opt/module/datas/ori’ into table ori;
hive (default)> load data local inpath ‘/opt/module/datas/nullid’ into table nullidtable;
（4）測試不過濾空id
hive (default)> insert overwrite table jointable
select n.* from nullidtable n left join ori o on n.id = o.id;
Time taken: 42.038 seconds
Time taken: 37.284 seconds
（5）測試過濾空id
hive (default)> insert overwrite table jointable
select n.* from (select * from nullidtable where id is not null ) n left join ori o on n.id = o.id;
Time taken: 31.725 seconds
Time taken: 28.876 seconds
2．空key轉換
有時雖然某個key為空對應的資料很多，但是相應的資料不是異常資料，必須要包含在join的結果中，此時我們可以表a中key為空的欄位賦一個隨機的值，使得資料隨機均勻地分不到不同的reducer上。例如：
案例實操：
不隨機分佈空null值：
（1）設定5個reduce個數
set mapreduce.job.reduces = 5;
（2）JOIN兩張表
insert overwrite table jointable
select n.* from nullidtable n left join ori b on n.id = b.id;
結果：如圖6-13所示，可以看出來，出現了資料傾斜，某些reducer的資源消耗遠大於其他reducer。

圖6-13 空key轉換
隨機分佈空null值
（1）設定5個reduce個數
set mapreduce.job.reduces = 5;
（2）JOIN兩張表
insert overwrite table jointable
select n.* from nullidtable n full join ori o on
case when n.id is null then concat(‘hive’, rand()) else n.id end = o.id;
結果：如圖6-14所示，可以看出來，消除了資料傾斜，負載均衡reducer的資源消耗

圖6-14 隨機分佈空值
9.3.3 MapJoin
如果不指定MapJoin或者不符合MapJoin的條件，那麼Hive解析器會將Join操作轉換成Common Join，即：在Reduce階段完成join。容易發生資料傾斜。可以用MapJoin把小表全部載入到記憶體在map端進行join，避免reducer處理。
1．開啟MapJoin引數設定
（1）設定自動選擇Mapjoin
set hive.auto.convert.join = true; 預設為true
（2）大表小表的閾值設定（預設25M一下認為是小表）：
set hive.mapjoin.smalltable.filesize=25000000;
2．MapJoin工作機制，如圖6-15所示
圖6-15 MapJoin工作機制
案例實操：
（1）開啟Mapjoin功能
set hive.auto.convert.join = true; 預設為true
（2）執行小表JOIN大表語句
insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from smalltable s
join bigtable b
on s.id = b.id;
Time taken: 24.594 seconds
（3）執行大表JOIN小表語句
insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable b
join smalltable s
on s.id = b.id;
Time taken: 24.315 seconds
9.3.4 Group By
預設情況下，Map階段同一Key資料分發給一個reduce，當一個key資料過大時就傾斜了。
並不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端進行部分聚合，最後在Reduce端得出最終結果。
1．開啟Map端聚合引數設定
（1）是否在Map端進行聚合，預設為True
hive.map.aggr = true
（2）在Map端進行聚合操作的條目數目
hive.groupby.mapaggr.checkinterval = 100000
（3）有資料傾斜的時候進行負載均衡（預設是false）
hive.groupby.skewindata = true
當選項設定為 true，生成的查詢計劃會有兩個MR Job。第一個MR Job中，Map的輸出結果會隨機分佈到Reduce中，每個Reduce做部分聚合操作，並輸出結果，這樣處理的結果是相同的Group By Key有可能被分發到不同的Reduce中，從而達到負載均衡的目的；第二個MR Job再根據預處理的資料結果按照Group By Key分佈到Reduce中（這個過程可以保證相同的Group By Key被分佈到同一個Reduce中），最後完成最終的聚合操作。
9.3.5 Count(Distinct) 去重統計
資料量小的時候無所謂，資料量大的情況下，由於COUNT DISTINCT操作需要用一個Reduce Task來完成，這一個Reduce需要處理的資料量太大，就會導致整個Job很難完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替換：
案例實操
1． 建立一張大表
hive (default)> create table bigtable(id bigint, time bigint, uid string, keyword
string, url_rank int, click_num int, click_url string) row format delimited
fields terminated by ‘\t’;
2．載入資料
hive (default)> load data local inpath ‘/opt/module/datas/bigtable’ into table
bigtable;
3．設定5個reduce個數
set mapreduce.job.reduces = 5;
4．執行去重id查詢
hive (default)> select count(distinct id) from bigtable;
Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 7.12 sec HDFS Read: 120741990 HDFS Write: 7 SUCCESS
Total MapReduce CPU Time Spent: 7 seconds 120 msec
OK
c0
100001
Time taken: 23.607 seconds, Fetched: 1 row(s)
5．採用GROUP by去重id
hive (default)> select count(id) from (select id from bigtable group by id) a;
Stage-Stage-1: Map: 1 Reduce: 5 Cumulative CPU: 17.53 sec HDFS Read: 120752703 HDFS Write: 580 SUCCESS
Stage-Stage-2: Map: 1 Reduce: 1 Cumulative CPU: 4.29 sec HDFS Read: 9409 HDFS Write: 7 SUCCESS
Total MapReduce CPU Time Spent: 21 seconds 820 msec
OK
_c0
100001
Time taken: 50.795 seconds, Fetched: 1 row(s)
雖然會多用一個Job來完成，但在資料量大的情況下，這個絕對是值得的。
9.3.6 笛卡爾積
儘量避免笛卡爾積，join的時候不加on條件，或者無效的on條件，Hive只能使用1個reducer來完成笛卡爾積。
9.3.7 行列過濾
列處理：在SELECT中，只拿需要的列，如果有，儘量使用分割槽過濾，少用SELECT *。
行處理：在分割槽剪裁中，當使用外關聯時，如果將副表的過濾條件寫在Where後面，那麼就會先全表關聯，之後再過濾，比如：
案例實操：
1．測試先關聯兩張表，再用where條件過濾
hive (default)> select o.id from bigtable b
join ori o on o.id = b.id
where o.id <= 10;
Time taken: 34.406 seconds, Fetched: 100 row(s)
2．通過子查詢後，再關聯表
hive (default)> select b.id from bigtable b
join (select id from ori where id <= 10 ) o on b.id = o.id;
Time taken: 30.058 seconds, Fetched: 100 row(s)
9.3.8 動態分割槽調整
分割槽 分桶
關係型資料庫中，對分割槽表Insert資料時候，資料庫自動會根據分割槽欄位的值，將資料插入到相應的分割槽中，Hive中也提供了類似的機制，即動態分割槽(Dynamic Partition)，只不過，使用Hive的動態分割槽，需要進行相應的配置。
1．開啟動態分割槽引數設定
（1）開啟動態分割槽功能（預設true，開啟）
hive.exec.dynamic.partition=true
（2）設定為非嚴格模式（動態分割槽的模式，預設strict，表示必須指定至少一個分割槽為靜態分割槽，nonstrict模式表示允許所有的分割槽欄位都可以使用動態分割槽。）
hive.exec.dynamic.partition.mode=nonstrict
（3）在所有執行MR的節點上，最大一共可以建立多少個動態分割槽。
hive.exec.max.dynamic.partitions=1000
（4）在每個執行MR的節點上，最大可以建立多少個動態分割槽。該引數需要根據實際的資料來設定。比如：源資料中包含了一年的資料，即day欄位有365個值，那麼該引數就需要設定成大於365，如果使用預設值100，則會報錯。
hive.exec.max.dynamic.partitions.pernode=100
（5）整個MR Job中，最大可以建立多少個HDFS檔案。
hive.exec.max.created.files=100000
（6）當有空分割槽生成時，是否丟擲異常。一般不需要設定。
hive.error.on.empty.partition=false
2．案例實操
需求：將ori中的資料按照時間(如：20111230000008)，插入到目標表ori_partitioned_target的相應分割槽中。
（1）建立分割槽表
create table ori_partitioned(id bigint, time bigint, uid string, keyword string,
url_rank int, click_num int, click_url string)
partitioned by (p_time bigint)
row format delimited fields terminated by ‘\t’;
（2）載入資料到分割槽表中
hive (default)> load data local inpath ‘/home/doit/ds1’ into table
ori_partitioned partition(p_time=‘20111230000010’) ;
hive (default)> load data local inpath ‘/home/doit/ds2’ into table ori_partitioned partition(p_time=‘20111230000011’) ;
（3）建立目標分割槽表
create table ori_partitioned_target(id bigint, time bigint, uid string,
keyword string, url_rank int, click_num int, click_url string) PARTITIONED BY (p_time STRING) row format delimited fields terminated by ‘\t’;
（4）設定動態分割槽
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.exec.max.dynamic.partitions = 1000;
set hive.exec.max.dynamic.partitions.pernode = 100;
set hive.exec.max.created.files = 100000;
set hive.error.on.empty.partition = false;

hive (default)> insert overwrite table ori_partitioned_target partition (p_time)
select id, time, uid, keyword, url_rank, click_num, click_url, p_time from ori_partitioned;
（5）檢視目標分割槽表的分割槽情況
hive (default)> show partitions ori_partitioned_target;
9.3.9 分桶
詳見6.6章。
9.3.10 分割槽
詳見4.6章。
9.4 資料傾斜
9.4.1 合理設定Map數
1）通常情況下，作業會通過input的目錄產生一個或者多個map任務。
主要的決定因素有：input的檔案總個數，input的檔案大小，叢集設定的檔案塊大小。
2）是不是map數越多越好？
答案是否定的。如果一個任務有很多小檔案（遠遠小於塊大小128m），則每個小檔案也會被當做一個塊，用一個map任務來完成，而一個map任務啟動和初始化的時間遠遠大於邏輯處理的時間，就會造成很大的資源浪費。而且，同時可執行的map數是受限的。
3）是不是保證每個map處理接近128m的檔案塊，就高枕無憂了？
答案也是不一定。比如有一個127m的檔案，正常會用一個map去完成，但這個檔案只有一個或者兩個小欄位，卻有幾千萬的記錄，如果map處理的邏輯比較複雜，用一個map任務去做，肯定也比較耗時。
針對上面的問題2和3，我們需要採取兩種方式來解決：即減少map數和增加map數；
9.4.2 小檔案進行合併
在map執行前合併小檔案，減少map數：CombineHiveInputFormat具有對小檔案進行合併的功能（系統預設的格式）。HiveInputFormat沒有對小檔案合併功能。
set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
9.4.3 複雜檔案增加Map數 128M
當input的檔案都很大，任務邏輯複雜，map執行非常慢的時候，可以考慮增加Map數，來使得每個map處理的資料量減少，從而提高任務的執行效率。
增加map的方法為：根據computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，調整maxSize最大值。讓maxSize最大值低於blocksize就可以增加map的個數。
案例實操：
1．執行查詢
hive (default)> select count() from emp;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2．設定最大切片值為100個位元組
hive (default)> set mapreduce.input.fileinputformat.split.maxsize=100;
hive (default)> select count() from emp;
Hadoop job information for Stage-1: number of mappers: 6; number of reducers: 1
9.4.4 合理設定Reduce數
1．調整reduce個數方法一
（1）每個Reduce處理的資料量預設是256MB
hive.exec.reducers.bytes.per.reducer=256000000
（2）每個任務最大的reduce數，預設為1009
hive.exec.reducers.max=1009
（3）計算reducer數的公式
N=min(引數2，總輸入資料量/引數1)
2．調整reduce個數方法二
在hadoop的mapred-default.xml檔案中修改
設定每個job的Reduce個數
set mapreduce.job.reduces = 15;
3．reduce個數並不是越多越好
1）過多的啟動和初始化reduce也會消耗時間和資源；
2）另外，有多少個reduce，就會有多少個輸出檔案，如果生成了很多個小檔案，那麼如果這些小檔案作為下一個任務的輸入，則也會出現小檔案過多的問題；
在設定reduce個數的時候也需要考慮這兩個原則：處理大資料量利用合適的reduce數；使單個reduce任務處理資料量大小要合適；
9.5 並行執行
Hive會將一個查詢轉化成一個或者多個階段。這樣的階段可以是MapReduce階段、抽樣階段、合併階段、limit階段。或者Hive執行過程中可能需要的其他階段。預設情況下，Hive一次只會執行一個階段。不過，某個特定的job可能包含眾多的階段，而這些階段可能並非完全互相依賴的，也就是說有些階段是可以並行執行的，這樣可能使得整個job的執行時間縮短。不過，如果有更多的階段可以並行執行，那麼job可能就越快完成。
通過設定引數hive.exec.parallel值為true，就可以開啟併發執行。不過，在共享叢集中，需要注意下，如果job中並行階段增多，那麼叢集利用率就會增加。
set hive.exec.parallel=true; //開啟任務並行執行
set hive.exec.parallel.thread.number=16; //同一個sql允許最大並行度，預設為8。
當然，得是在系統資源比較空閒的時候才有優勢，否則，沒資源，並行也起不來。
9.6 嚴格模式
Hive提供了一個嚴格模式，可以防止使用者執行那些可能意想不到的不好的影響的查詢。
通過設定屬性hive.mapred.mode值為預設是非嚴格模式nonstrict 。開啟嚴格模式需要修改hive.mapred.mode值為strict，開啟嚴格模式可以禁止3種型別的查詢。

hive.mapred.mode
strict

The mode in which the Hive operations are being performed.
In strict mode, some risky queries are not allowed to run. They include:
Cartesian Product.
No partition being picked up for a query.
Comparing bigints and strings.
Comparing bigints and doubles.
Orderby without limit.

1. 對於分割槽表，除非where語句中含有分割槽欄位過濾條件來限制範圍，否則不允許執行。換句話說，就是使用者不允許掃描所有分割槽。進行這個限制的原因是，通常分割槽表都擁有非常大的資料集，而且資料增加迅速。沒有進行分割槽限制的查詢可能會消耗令人不可接受的巨大資源來處理這個表。
2. 對於使用了order by語句的查詢，要求必須使用limit語句。因為order by為了執行排序過程會將所有的結果資料分發到同一個Reducer中進行處理，強制要求使用者增加這個LIMIT語句可以防止Reducer額外執行很長一段時間。
3. 限制笛卡爾積的查詢。對關係型資料庫非常瞭解的使用者可能期望在執行JOIN查詢的時候不使用ON語句而是使用where語句，這樣關聯式資料庫的執行優化器就可以高效地將WHERE語句轉化成那個ON語句。不幸的是，Hive並不會執行這種優化，因此，如果表足夠大，那麼這個查詢就會出現不可控的情況。
   9.7 JVM重用
   JVM重用是Hadoop調優引數的內容，其對Hive的效能具有非常大的影響，特別是對於很難避免小檔案的場景或task特別多的場景，這類場景大多數執行時間都很短。
   Hadoop的預設配置通常是使用派生JVM來執行map和Reduce任務的。這時JVM的啟動過程可能會造成相當大的開銷，尤其是執行的job包含有成百上千task任務的情況。JVM重用可以使得JVM例項在同一個job中重新使用N次。N的值可以在Hadoop的mapred-site.xml檔案中進行配置。通常在10-20之間，具體多少需要根據具體業務場景測試得出。
   
   mapreduce.job.jvm.numtasks
   10
   How many tasks to run per jvm. If set to -1, there is
   no limit.
   
   
   這個功能的缺點是，開啟JVM重用將一直佔用使用到的task插槽，以便進行重用，直到任務完成後才能釋放。如果某個“不平衡的”job中有某幾個reduce task執行的時間要比其他Reduce task消耗的時間多的多的話，那麼保留的插槽就會一直空閒著卻無法被其他的job使用，直到所有的task都結束了才會釋放。
   9.8 推測執行
   在分散式叢集環境下，因為程式Bug（包括Hadoop本身的bug），負載不均衡或者資源分佈不均等原因，會造成同一個作業的多個任務之間執行速度不一致，有些任務的執行速度可能明顯慢於其他任務（比如一個作業的某個任務進度只有50%，而其他所有任務已經執行完畢），則這些任務會拖慢作業的整體執行進度。為了避免這種情況發生，Hadoop採用了推測執行（Speculative Execution）機制，它根據一定的法則推測出“拖後腿”的任務，併為這樣的任務啟動一個備份任務，讓該任務與原始任務同時處理同一份資料，並最終選用最先成功執行完成任務的計算結果作為最終結果。
   設定開啟推測執行引數：Hadoop的mapred-site.xml檔案中進行配置
   
   mapreduce.map.speculative
   true
   If true, then multiple instances of some map tasks
   may be executed in parallel.
   

mapreduce.reduce.speculative true If true, then multiple instances of some reduce tasks may be executed in parallel. 不過hive本身也提供了配置項來控制reduce-side的推測執行： hive.mapred.reduce.tasks.speculative.execution true Whether speculative execution for reducers should be turned on. 關於調優這些推測執行變數，還很難給一個具體的建議。如果使用者對於執行時的偏差非常敏感的話，那麼可以將這些功能關閉掉。如果使用者因為輸入資料量很大而需要執行長時間的map或者Reduce task的話，那麼啟動推測執行造成的浪費是非常巨大大。 9.9 壓縮 詳見第8章。 9.10 執行計劃（Explain） (搜尋) 1．基本語法 EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query 2．案例實操 （1）檢視下面這條語句的執行計劃 hive (default)> explain select * from emp; hive (default)> explain select deptno, avg(sal) avg_sal from emp group by deptno; （2）檢視詳細執行計劃 hive (default)> explain extended select * from emp; hive (default)> explain extended select deptno, avg(sal) avg_sal from emp group by deptno;

10. JDBC
    jdbc是jdk開發的運算元據庫的一套標準API(介面方法)