Hive優化

要點：優化時，把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，變成兩個<key ,value>對，<b,數字id>，<b,字串id>。所以商品表的hdfs讀只會是一次。

 

問題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,否則生成<memberid,value>的key,value對，假如記錄來自member,生成<memberid,value>的key,value對，進入reduce階段。Stage3.

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彙總。

 

 

一．join優化

Join查詢操作的基本原則：應該將條目少的表/子查詢放在 Join 操作符的左邊。原因是在 Join 操作的 Reduce 階段，位於 Join 操作符左邊的表的內容會被載入進記憶體，將條目少的表放在左邊，可以有效減少發生記憶體溢位錯誤的機率。

Join查詢操作中如果存在多個join，且所有參與join的表中其參與join的key都相同，則會將所有的join合併到一個mapred程式中。

案例：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)  在一個mapre程式中執行join

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)   在兩個mapred程式中執行join

Map join的關鍵在於join操作中的某個表的資料量很小，案例：

SELECT /*+ MAPJOIN(b) */ a.key, a.value

  FROM a join b on a.key = b.key 

Mapjoin 的限制是無法執行a FULL/RIGHT OUTER JOIN b，和map join相關的hive引數：hive.join.emit.interval  hive.mapjoin.size.key  hive.mapjoin.cache.numrows

由於join操作是在where操作之前執行，所以當你在執行join時，where條件並不能起到減少join資料的作用；案例：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

  WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

最好修改為：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

  ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

在join操作的每一個mapred程式中，hive都會把出現在join語句中相對靠後的表的資料stream化，相對靠前的變的資料快取在記憶體中。當然，也可以手動指定stream化的表：SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

二、group by 優化

Map端聚合，首先在map端進行初步聚合，最後在reduce端得出最終結果，相關引數：

· hive.map.aggr = true是否在 Map 端進行聚合，預設為 True

· hive.groupby.mapaggr.checkinterval = 100000在 Map 端進行聚合操作的條目數目

資料傾斜聚合優化，設定引數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 中），最後完成最終的聚合操作。

三、合併小檔案

檔案數目過多，會給 HDFS 帶來壓力，並且會影響處理效率，可以通過合併 Map 和 Reduce 的結果檔案來消除這樣的影響：

· hive.merge.mapfiles = true是否和並 Map 輸出檔案，預設為 True

· hive.merge.mapredfiles = false是否合併 Reduce 輸出檔案，預設為 False

· hive.merge.size.per.task = 256*1000*1000合併檔案的大小

四、Hive實現(not) in

通過left outer join進行查詢,（假設B表中包含另外的一個欄位 key1 

select a.key from a left outer join b on a.key=b.key where b.key1 is null

通過left semi join 實現 in

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

Left semi join 的限制：join條件中右邊的表只能出現在join條件中。

五、排序優化

Order by 實現全域性排序，一個reduce實現，效率低

Sort by 實現部分有序，單個reduce輸出的結果是有序的，效率高，通常和DISTRIBUTE BY關鍵字一起使用（DISTRIBUTE BY關鍵字 可以指定map 到 reduce端的分發key）

CLUSTER BY col1 等價於DISTRIBUTE BY col1 SORT BY col1

六、使用分割槽

Hive中的每個分割槽都對應hdfs上的一個目錄，分割槽列也不是表中的一個實際的欄位，而是一個或者多個偽列，在表的資料檔案中實際上並不儲存分割槽列的資訊與資料。Partition關鍵字中排在前面的為主分割槽（只有一個），後面的為副分割槽

靜態分割槽：靜態分割槽在載入資料和使用時都需要在sql語句中指定

          案例：(stat_date='20120625',province='hunan')

動態分割槽：使用動態分割槽需要設定hive.exec.dynamic.partition引數值為true，預設值為false，在預設情況下，hive會假設主分割槽時靜態分割槽，副分割槽使用動態分割槽；如果想都使用動態分割槽，需要設定set hive.exec.dynamic.partition.mode=nostrick，預設為strick

          案例：(stat_date='20120625',province)

七、Distinct使用

Hive支援在group by時對同一列進行多次distinct操作，卻不支援在同一個語句中對多個列進行distinct操作。

八、Hql使用自定義的mapred指令碼

注意事項：在使用自定義的mapred指令碼時，關鍵字MAP REDUCE 是語句SELECT TRANSFORM ( ... )的語法轉換，並不意味著使用MAP關鍵字時會強制產生一個新的map過程，使用REDUCE關鍵字時會產生一個red過程。

自定義的mapred指令碼可以是hql語句完成更為複雜的功能，但是效能比hql語句差了一些，應該儘量避免使用，如有可能，使用UDTF函式來替換自定義的mapred指令碼

九、UDTF

UDTF將單一輸入行轉化為多個輸出行，並且在使用UDTF時，select語句中不能包含其他的列，UDTF不支援巢狀，也不支援group by 、sort by等語句。如果想避免上述限制，需要使用lateral view語法，案例：

select a.timestamp, get_json_object(a.appevents, '$.eventid'), get_json_object(a.appenvets, '$.eventname') from log a;

select a.timestamp, b.*

from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;

其中，get_json_object為UDF函式，json_tuple為UDTF函式。

UDTF函式在某些應用場景下可以大大提高hql語句的效能，如需要多次解析json或者xml資料的應用場景。