一、檢視執行計劃
explain extended hql;可以看到掃描資料的hdfs路徑
二、hive表優化
分區(不同檔案夾):
動态分區開啟:
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
預設值:strict
描述:strict是避免全分區字段是動态的,必須有至少一個分區字段是指定有值的
避免産生大量分區
分桶(不同檔案):
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;開啟強制排序,插資料到表中會進行強制排序,預設false;
三、Hive SQL優化
groupby資料傾斜優化
hive.groupby.skewindata=true;(多起一個job)1.join優化
(1)資料傾斜
hive.optimize.skewjoin=true;
如果是join過程出現傾斜,應該設定為true
set hive.skewjoin.key=100000;
這個是join的鍵對應的記錄條數超過這個值則會進行優化
簡單說就是一個job變為兩個job執行HQL
(2)mapjoin(map端執行join)
啟動方式一:(自動判斷)
set.hive.auto.convert.join=true;
hive.mapjoin.smalltable.filesize 預設值是25mb
小表小于25mb自動啟動mapjoin
啟動方式二:(手動)
select /*+mapjoin(A)*/ f.a,f.b from A t join B f on (f.a=t.a)
mapjoin支援不等值條件
reducejoin不支援在ON條件中不等值判斷
(3)bucketjoin(資料通路可以精确到桶級别)
使用條件:1.兩個表以相同方式劃分桶
2.兩個表的桶個數是倍數關系
例子:
create table order(cid int,price float) clustered by(cid) into 32 buckets;
create table customer(id int,first string) clustered by(id) into 32/64 buckets;
select price from order t join customer s on t.cid=s.id;
(4)where條件優化
優化前(關系資料庫不用考慮會自動優化):
select m.cid,u.id from order m join customer u on m.cid =u.id where m.dt='2013-12-12';
優化後(where條件在map端執行而不是在reduce端執行):
select m.cid,u.id from (select * from order where dt='2013-12-12') m join customer u on m.cid =u.id;
(5)group by 優化
hive.groupby.skewindata=true;
如果group by過程出現傾斜應該設定為true
set hive.groupby.mapaggr.checkinterval=100000;
這個是group的鍵對應的記錄條數超過這個值則會進行優化
也是一個job變為兩個job
(6)count distinct優化
優化前(隻有一個reduce,先去重再count負擔比較大):
select count(distinct id) from tablename;
優化後(啟動兩個job,一個job負責子查詢(可以有多個reduce),另一個job負責count(1)):
select count(1) from (select distinct id from tablename) tmp;
select count(1) from (select id from tablename group by id) tmp;
set mapred.reduce.tasks=3;
(7)
優化前:
select a,sum(b),count(distinct c),count(distinct d) from test group by a;
優化後:
select a,sum(b) as b,count(c) as c,count(d) as d from
(
select a, 0 as b,c,null as d from test group by a,c
union all
select a,0 as b, null as c,d from test group by a,d
select a, b,null as c ,null as d from test) tmp group by a;
四、Hive job優化
1.并行化執行
hive預設job是順序進行的,一個HQL拆分成多個job,job之間無依賴關系也沒有互相影響可以并行執行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
就是控制對于同一個sql來說同時可以運作的job的最大值,該參數預設為8.此時最大可以同時運作8個job
2.本地化執行(在存放資料的節點上執行)
set hive.exec.mode.local.auto=true;
本地化執行必須滿足條件:
(1)job的輸入資料大小必須小于參數
hive.exec.mode.local.auto.inputbytes.max(預設128MB)
(2)job的map數必須小于參數:
hive.exec.mode.local.auto.tasks.max(預設為4)太多沒有足夠的slots
(3)job的reduce數必須為0或1
3.job合并輸入小檔案
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
多個split合成一個,合并split數由mapred.max.split.size限制的大小決定
4.job合并輸出小檔案(為後續job優化做準備)
set hive.merge.smallfiles.avgsize=256000000;當輸出檔案平均大小小于該值,啟動新job合并檔案
set hive.merge.size.per.task=64000000;合并之後的每個檔案大小
5.JVM重利用
set mapred.job.reuse.jvm.num.tasks=20;
每個jvm運作多少個task;
JVM重利用可以使job長時間保留slot,直到作業結束。
6.壓縮資料(多個job)
(1)中間壓縮處理hive查詢的多個job之間的資料,對于中間壓縮,最好選擇一個節省cpu耗時的壓縮方式
set hive.exec.compress.intermediate=true;
set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set hive.intermediate.compression.type=BLOCK;按塊壓縮,而不是記錄
(2)最終輸出壓縮(選擇壓縮效果好的,減少儲存空間)
set hive.exec.compress.output=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set mapred.output.compression.type=BLOCK;按塊壓縮,而不是記錄
五、Hive Map優化
1.set mapred.map.tasks=10 無效
(1)預設map個數
default_num=total_size/block_size;
(2)期望大小(手動設定的個數)
goal_num =mapred.map.tasks;
(3)設定處理的檔案大小(根據檔案分片大小計算的map個數)
split_size=max(block_size,mapred.min.split.size);
split_num=total_size/split_size;
(4)最終計算的map個數(實際map個數)
compute_map_num=min(split_num,max(default_num,goal_num))
總結:
(1)如果想增加map個數,則設定mapred.map.tasks為一個較大的值;
(2)如果想減小map個數,則設定mapred.min.split.size為一個較大的值。
2.map端聚合
set hive.map.aggr=true;相當于map端執行combiner
3.推測執行(預設為true)
mapred.map.tasks.speculative.execution
六、Hive Shuffle優化
Map 端
io.sort.mb
io.sort.spill.percent
min.num.spill.for.combine
io.sort.factor
io.sort.record.percent
reduce端
mapred.reduce.parallel.copies
mapred.reduce.copy.backoff
mapred.job.shuffle.input.buffer.percent
七、HIve Reduce優化
1.推測執行(預設為true)
mapred.reduce.tasks.speculative.execution(hadoop裡面的)
hive.mapred.reduce.tasks.speculative.execution(hive裡面相同的參數,效果和hadoop裡面的一樣)
兩個随便哪個都行
2.Reduce優化(reduce個數設定)
set mapred.reduce.tasks=10;直接設定
最大值
hive.exec.reducers.max 預設:999
每個reducer計算的檔案量大小
hive.exec.reducers.bytes.per.reducer 預設:1G
計算公式:雖然設了這麼多,但不一定用到這麼多
numRTasks =min[maxReducers,input.size/perReducer]
maxReducers=hive.exec.reducers.max
perReducer=hive.exec.reducers.bytes.per.reducer