Hive調優實戰
轉載地址:http://sznmail.iteye.com/blog/1499789
最佳化時,把hive sql當做map reduce程式來讀,會有意想不到的驚喜。
理解hadoop的核心能力,是hive最佳化的根本。這是這一年來,專案組所有成員寶貴的經驗總結。
長期觀察hadoop處理資料的過程,有幾個顯著的特徵:
1.不怕資料多,就怕資料傾斜。
2.對jobs數比較多的作業執行效率相對比較低,比如即使有幾百行的表,如果多次關聯多次彙總,產生十幾個jobs,沒半小時是跑不完的。map reduce作業初始化的時間是比較長的。
3.對sum,count來說,不存在資料傾斜問題。
4.對count(distinct ),效率較低,資料量一多,準出問題,如果是多count(distinct )效率更低。
最佳化可以從幾個方面著手:
1. 好的模型設計事半功倍。
2. 解決資料傾斜問題。
3. 減少job數。
4. 設定合理的map reduce的task數,能有效提升效能。(比如,10w+級別的計算,用160個reduce,那是相當的浪費,1個足夠)。
5. 自己動手寫sql解決資料傾斜問題是個不錯的選擇。set hive.groupby.skewindata=true;這是通用的演算法最佳化,但演算法最佳化總是漠視業務,習慣性提供通用的解決方法。 Etl開發人員更瞭解業務,更瞭解資料,所以透過業務邏輯解決傾斜的方法往往更精確,更有效。
6. 對count(distinct)採取漠視的方法,尤其資料大的時候很容易產生傾斜問題,不抱僥倖心理。自己動手,豐衣足食。
7. 對小檔案進行合併,是行至有效的提高排程效率的方法,假如我們的作業設定合理的檔案數,對雲梯的整體排程效率也會產生積極的影響。
8. 最佳化時把握整體,單個作業最優不如整體最優。
遷移和最佳化過程中的案例:
問題1:如日誌中,常會有資訊丟失的問題,比如全網日誌中的user_id,如果取其中的user_id和bmw_users關聯,就會碰到資料傾斜的問題。
方法:解決資料傾斜問題
解決方法1. User_id為空的不參與關聯,例如:
Select *
From log a
Join bmw_users b
On a.user_id is not null
And a.user_id = b.user_id
Union all
Select *
from log a
where a.user_id is null.
解決方法2 :
Select *
from log a
left outer join bmw_users b
on case when a.user_id is null then concat(‘dp_hive’,rand() ) else a.user_id end = b.user_id;
總結:2比1效率更好,不但io少了,而且作業數也少了。1方法log讀取兩次,jobs是2。2方法job數是1 。這個最佳化適合無效id(比如-99,’’,null等)產生的傾斜問題。把空值的key變成一個字串加上隨機數,就能把傾斜的資料分到不同的reduce上 ,解決資料傾斜問題。因為空值不參與關聯,即使分到不同的reduce上,也不影響最終的結果。附上hadoop通用關聯的實現方法(關聯透過二次排序實現的,關聯的列為parition key,關聯的列c1和表的tag組成排序的group key,根據parition key分配reduce。同一reduce內根據group key排序)。
問題2:不同資料型別id的關聯會產生資料傾斜問題。
一張表s8的日誌,每個商品一條記錄,要和商品表關聯。但關聯卻碰到傾斜的問題。s8的日誌中有字串商品id,也有數字的商品id,型別是string的,但商品中的數字id是bigint的。猜測問題的原因是把s8的商品id轉成數字id做hash來分配reduce,所以字串id的s8日誌,都到一個reduce上了,解決的方法驗證了這個猜測。
方法:把數字型別轉換成字串型別
Select * from s8_log a
Left outer join r_auction_auctions b
On a.auction_id = cast(b.auction_id as string);
問題3:利用hive 對UNION ALL的最佳化的特性
hive對union all最佳化只侷限於非巢狀查詢。
比如以下的例子:
select * from
(select * from t1
Group by c1,c2,c3
Union all
Select * from t2
Group by c1,c2,c3) t3
Group by c1,c2,c3;
從業務邏輯上說,子查詢內的group by 怎麼都看顯得多餘(功能上的多餘,除非有count(distinct)),如果不是因為hive bug或者效能上的考量(曾經出現如果不子查詢group by ,資料得不到正確的結果的hive bug)。所以這個hive按經驗轉換成
select * from
(select * from t1
Union all
Select * from t2
) t3
Group by c1,c2,c3;
經過測試,並未出現union all的hive bug,資料是一致的。mr的作業數有3減少到1。
t1相當於一個目錄,t2相當於一個目錄,那麼對map reduce程式來說,t1,t2可以做為map reduce 作業的mutli inputs。那麼,這可以透過一個map reduce 來解決這個問題。Hadoop的計算框架,不怕資料多,就怕作業數多。
但如果換成是其他計算平臺如oracle,那就不一定了,因為把大的輸入拆成兩個輸入,分別排序彙總後merge(假如兩個子排序是並行的話),是有可能效能更優的(比如希爾排序比氣泡排序的效能更優)。
問題4:比如推廣效果表要和商品表關聯,效果表中的auction id列既有商品id,也有數字id,和商品表關聯得到商品的資訊。那麼以下的hive sql效能會比較好
Select * from effect a
Join (select auction_id as auction_id from auctions
Union all
Select auction_string_id as auction_id from auctions
) b
On a.auction_id = b.auction_id。
比分別過濾數字id,字串id然後分別和商品表關聯效能要好。
這樣寫的好處,1個MR作業,商品表只讀取一次,推廣效果表只讀取一次。把這個sql換成MR程式碼的話,map的時候,把a表的記錄打上標籤a,商品表記錄每讀取一條,打上標籤b,變成兩個
問題5:先join生成臨時表,在union all還是寫巢狀查詢,這是個問題。比如以下例子:
Select *
From (select *
From t1
Uion all
select *
From t4
Union all
Select *
From t2
Join t3
On t2.id = t3.id
) x
Group by c1,c2;
這個會有4個jobs。假如先join生成臨時表的話t5,然後union all,會變成2個jobs。
Insert overwrite table t5
Select *
From t2
Join t3
On t2.id = t3.id
;
Select * from (t1 union all t4 union all t5) ;
hive在union all最佳化上可以做得更智慧(把子查詢當做臨時表),這樣可以減少開發人員的負擔。出現這個問題的原因應該是union all目前的最佳化只侷限於非巢狀查詢。如果寫MR程式這一點也不是問題,就是multi inputs。
問題6:使用map join解決資料傾斜的常景下小表關聯大表的問題,但如果小表很大,怎麼解決。這個使用的頻率非常高,但如果小表很大,大到map join會出現bug或異常,這時就需要特別的處理。雲瑞和玉璣提供了非常給力的解決方案。以下例子:
Select * from log a
Left outer join members b
On a.memberid = b.memberid.
Members有600w+的記錄,把members分發到所有的map上也是個不小的開銷,而且map join不支援這麼大的小表。如果用普通的join,又會碰到資料傾斜的問題。
解決方法:
Select /*+mapjoin(x)*/* from log a
Left outer join (select /*+mapjoin(c)*/d.*
From (select distinct memberid from log ) c
Join members d
On c.memberid = d.memberid
)x
On a.memberid = b.memberid。
先根據log取所有的memberid,然後mapjoin 關聯members取今天有日誌的members的資訊,然後在和log做mapjoin。
假如,log裡memberid有上百萬個,這就又回到原來map join問題。所幸,每日的會員uv不會太多,有交易的會員不會太多,有點選的會員不會太多,有佣金的會員不會太多等等。所以這個方法能解決很多場景下的資料傾斜問題。
問題7:HIVE下通用的資料傾斜解決方法,double被關聯的相對較小的表,這個方法在mr的程式裡常用。還是剛才的那個問題:
Select * from log a
Left outer join (select /*+mapjoin(e)*/
memberid, number
From members d
Join num e
) b
On a.memberid= b.memberid
And mod(a.pvtime,30)+1=b.number。
Num表只有一列number,有30行,是1,30的自然數序列。就是把member表膨脹成30份,然後把log資料根據memberid和pvtime分到不同的reduce裡去,這樣可以保證每個reduce分配到的資料可以相對均勻。就目前測試來看,使用mapjoin的方案效能稍好。後面的方案適合在map join無法解決問題的情況下。
長遠設想,把如下的最佳化方案做成通用的hive最佳化方法
1. 取樣log表,哪些memberid比較傾斜,得到一個結果表tmp1。由於對計算框架來說,所有的資料過來,他都是不知道資料分佈情況的,所以取樣是並不可少的。Stage1
2. 資料的分佈符合社會學統計規則,貧富不均。傾斜的key不會太多,就像一個社會的富人不多,奇特的人不多一樣。所以tmp1記錄數會很少。把tmp1和members做map join生成tmp2,把tmp2讀到distribute file cache。這是一個map過程。Stage2
3. map讀入members和log,假如記錄來自log,則檢查memberid是否在tmp2裡,如果是,輸出到本地檔案a,否則生成
4. 最終把a檔案,把Stage3 reduce階段輸出的檔案合併起寫到hdfs。
這個方法在hadoop裡應該是能實現的。Stage2是一個map過程,可以和stage3的map過程可以合併成一個map過程。
這個方案目標就是:傾斜的資料用mapjoin,不傾斜的資料用普通的join,最終合併得到完整的結果。用hive sql寫的話,sql會變得很多段,而且log表會有多次讀。傾斜的key始終是很少的,這個在絕大部分的業務背景下適用。那是否可以作為hive針對資料傾斜join時候的通用演算法呢?
問題8:多粒度(平級的)uv的計算最佳化,比如要計算店鋪的uv。還有要計算頁面的uv,pvip.
方案1:
Select shopid,count(distinct uid)
From log group by shopid;
Select pageid, count(distinct uid),
From log group by pageid;
由於存在資料傾斜問題,這個結果的執行時間是非常長的。
方案二:
From log
Insert overwrite table t1 (type=’1’)
Select shopid
Group by shopid ,acookie
Insert overwrite table t1 (type=’2’)
Group by pageid,acookie;
店鋪uv:
Select shopid,sum(1)
From t1
Where type =’1’
Group by shopid ;
頁面uv:
Select pageid,sum(1)
From t1
Where type =’1’
Group by pageid ;
這裡使用了multi insert的方法,有效減少了hdfs讀,但multi insert會增加hdfs寫,多一次額外的map階段的hdfs寫。使用這個方法,可以順利的產出結果。
方案三:
Insert into t1
Select type,type_name,’’ as uid
From (
Select ‘page’ as type,
Pageid as type_name,
Uid
From log
Union all
Select ‘shop’ as type,
Shopid as type_name,
Uid
From log ) y
Group by type,type_name,uid;
Insert into t2
Select type,type_name,sum(1)
From t1
Group by type,type_name;
From t2
Insert into t3
Select type,type_name,uv
Where type=’page’
Select type,type_name,uv
Where type=’shop’ ;
最終得到兩個結果表t3,頁面uv表,t4,店鋪結果表。從io上來說,log一次讀。但比方案2少次hdfs寫(multi insert有時會增加額外的map階段hdfs寫)。作業數減少1個到3,有reduce的作業數由4減少到2,第三步是一個小表的map過程,分下表,計算資源消耗少。但方案2每個都是大規模的去重彙總計算。
這個最佳化的主要思路是,map reduce作業初始化話的時間是比較長,既然起來了,讓他多幹點活,順便把頁面按uid去重的活也幹了,省下log的一次讀和作業的初始化時間,省下網路shuffle的io,但增加了本地磁碟讀寫。效率提升較多。
這個方案適合平級的不需要逐級向上彙總的多粒度uv計算,粒度越多,節省資源越多,比較通用。
問題9:多粒度,逐層向上彙總的uv結算。比如4個維度,a,b,c,d,分別計算a,b,c,d,uv;
a,b,c,uv;a,b,uv;a;uv,total uv4個結果表。這可以用問題8的方案二,這裡由於uv場景的特殊性,多粒度,逐層向上彙總,就可以使用一次排序,所有uv計算受益的計算方法。
案例:目前mm_log日誌一天有25億+的pv數,要從mm日誌中計算uv,與ipuv,一共計算
三個粒度的結果表
(memberid,siteid,adzoneid,province,uv,ipuv) R_TABLE_4
(memberid,siteid,adzoneid,uv,ipuv) R_TABLE_3
(memberid,siteid,uv,ipuv) R_TABLE_2
第一步:按memberid,siteid,adzoneid,province,使用group去重,產生臨時表,對cookie,ip
打上標籤放一起,一起去重,臨時表叫T_4;
Select memberid,siteid,adzoneid,province,type,user
From(
Select memberid,siteid,adzoneid,province,‘a’ type ,cookie as user from mm_log where ds=20101205
Union all
Select memberid,siteid,adzoneid,province,‘i’ type ,ip as user from mm_log where ds=20101205
) x group by memberid,siteid,adzoneid,province,type,user ;
第二步:排名,產生表T_4_NUM.Hadoop最強大和核心能力就是parition 和 sort.按type,acookie分組,
Type,acookie,memberid,siteid,adzoneid,province排名。
Select * ,
row_number(type,user,memberid,siteid,adzoneid ) as adzone_num ,
row_number(type,user,memberid,siteid ) as site_num,
row_number(type,user,memberid ) as member_num,
row_number(type,user ) as total_num
from (select * from T_4 distribute by type,user sort by type,user, memberid,siteid,adzoneid ) x;
這樣就可以得到不同層次粒度上user的排名,相同的user id在不同的粒度層次上,排名等於1的記錄只有1條。取排名等於1的做sum,效果相當於Group by user去重後做sum操作。
第三步:不同粒度uv統計,先從最細粒度的開始統計,產生結果表R_TABLE_4,這時,結果集只有10w的級別。
如統計memberid,siteid,adzoneid,provinceid粒度的uv使用的方法就是
Select memberid,siteid,adzoneid, provinceid,
sum(case when type =’a’ then cast(1) as bigint end ) as province_uv ,
sum(case when type =’i’ then cast(1) as bigint end ) as province_ip ,
sum(case when adzone_num =1 and type =’a’ then cast(1) as bigint end ) as adzone_uv ,
sum(case when adzone_num =1 and type =’i’ then cast(1) as bigint end ) as adzone_ip ,
sum(case when site_num =1 and type =’a’ then cast(1) as bigint end ) as site_uv ,
sum(case when site_num =1 and type =’i’ then cast(1) as bigint end ) as site_ip ,
sum(case when member_num =1 and type =’a’ then cast(1) as bigint end ) as member_uv ,
sum(case when member_num =1 and type =’i’ then cast(1) as bigint end ) as member_ip ,
sum(case when total_num =1 and type =’a’ then cast(1) as bigint end ) as total_uv ,
sum(case when total_num =1 and type =’i’ then cast(1) as bigint end ) as total_ip ,
from T_4_NUM
group by memberid,siteid,adzoneid, provinceid ;
廣告位粒度的uv的話,從R_TABLE_4統計,這是源表做10w級別的統計
Select memberid,siteid,adzoneid,sum(adzone_uv),sum(adzone_ip)
From R_TABLE_4
Group by memberid,siteid,adzoneid;
memberid,siteid的uv計算 ,
memberid的uv計算,
total uv 的計算也都從R_TABLE_4彙總。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26613085/viewspace-1130941/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Hive調優實用Hive
- Hive效能調優實踐 - VidhyaHive
- 實戰 nginx 調優Nginx
- 效能調優實戰
- MySQL調優篇 | SQL調優實戰(5)MySql
- 數倉調優實戰:GUC引數調優
- Hive使用Calcite CBO優化流程及SQL優化實戰Hive優化SQL
- Java垃圾回收調優實戰Java
- 記憶體調優實戰記憶體
- JVM效能調優與實戰篇JVM
- hive查詢注意事項和調優Hive
- PHP 效能分析(三): 效能調優實戰PHP
- flink實戰--讀寫Hive(Flink on Hive)Hive
- MindSpore模型精度調優實戰:常用的定位精度除錯調優思路模型除錯
- 開發中hive常見的調優策略Hive
- Spring Boot Serverless 實戰系列 | 效能調優Spring BootServer
- Spark Streaming調優引數及最佳實踐深入剖析-Spark商業調優實戰Spark
- Hadoop實戰:Hive操作使用HadoopHive
- [Hive]Hive排序優化Hive排序優化
- Hive --------- hive 的優化Hive優化
- Hive-常見調優方式 && 兩個面試sqlHive面試SQL
- Golang pprof 效能調優實戰,效能提升 3 倍!Golang
- JVM效能調優與實戰進階篇-上JVM
- Spark應用程式開發引數調優深入剖析-Spark商業調優實戰Spark
- Hive篇---Hive使用優化Hive優化
- Webpack 實戰:入門、進階與調優(中卷)Web
- PHP 效能分析第三篇: 效能調優實戰PHP
- Hive優化Hive優化
- hive學習之三:專案中的hive優化實踐Hive優化
- JVM實戰調優(空格引發的服務異常)JVM
- MindSpore模型精度調優實戰:如何更快定位精度問題模型
- JVM效能調優與實戰基礎理論篇-下JVM
- SQL Server調優實戰 亂建聚集索引的後果SQLServer索引
- Elasticsearch調優實踐Elasticsearch
- hive的優化Hive優化
- hive、spark優化HiveSpark優化
- Hive效能優化Hive優化
- 高效能 Java 計算服務的效能調優實戰Java