優(yōu)化時，把hive sql當(dāng)作map reduce程序來讀，會成心想不到的欣喜。

理解hadoop的核心能力，是hive優(yōu)化的根本。這是這1年來，項目組所有成員寶貴的經(jīng)驗總結(jié)。

長時間視察hadoop處理數(shù)據(jù)的進程，有幾個顯著的特點:

1.不怕數(shù)據(jù)多，就怕數(shù)據(jù)傾斜。

2．對jobs數(shù)比較多的作業(yè)運行效力相對照較低，比如即便有幾百行的表，如果屢次關(guān)聯(lián)屢次匯總，產(chǎn)生10幾個jobs，沒半小時是跑不完的。map reduce作業(yè)初始化的時間是比較長的。

3.對sum，count來講，不存在數(shù)據(jù)傾斜問題。

4.對count(distinct ),效力較低，數(shù)據(jù)量1多，準(zhǔn)出問題，如果是多count(distinct )效力更低。

優(yōu)化可以從幾個方面著手：

1. 好的模型設(shè)計事半功倍。

2. 解決數(shù)據(jù)傾斜問題。

3. 減少job數(shù)。

4. 設(shè)置公道的map reduce的task數(shù)，能有效提升性能。(比如，10w+級別的計算，用160個reduce，那是相當(dāng)?shù)睦速M，1個足夠)。

5. 自己動手寫sql解決數(shù)據(jù)傾斜問題是個不錯的選擇。set hive.groupby.skewindata=true;這是通用的算法優(yōu)化，但算法優(yōu)化總是漠視業(yè)務(wù)，習(xí)慣性提供通用的解決方法。 Etl開發(fā)人員更了解業(yè)務(wù)，更了解數(shù)據(jù)，所以通過業(yè)務(wù)邏輯解決傾斜的方法常常更精確，更有效。

6. 對count(distinct)采取漠視的方法，特別數(shù)據(jù)大的時候很容易產(chǎn)生傾斜問題，不抱僥幸心理。自己動手，豐衣足食。

7. 對小文件進行合并，是行至有效的提高調(diào)度效力的方法，假設(shè)我們的作業(yè)設(shè)置公道的文件數(shù)，對云梯的整體調(diào)度效力也會產(chǎn)生積極的影響。

8. 優(yōu)化時掌控整體，單個作業(yè)最優(yōu)不如整體最優(yōu)。

遷移和優(yōu)化進程中的案例：

問題1：如日志中，常會有信息丟失的問題，比如全網(wǎng)日志中的user_id，如果取其中的user_id和bmw_users關(guān)聯(lián)，就會碰到數(shù)據(jù)傾斜的問題。

方法：解決數(shù)據(jù)傾斜問題

解決方法1. User_id為空的不參與關(guān)聯(lián)，例如：

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;

總結(jié)：2比1效力更好，不但io少了，而且作業(yè)數(shù)也少了。1方法log讀取兩次，jobs是2。2方法job數(shù)是1 。這個優(yōu)化合適無效id(比如⑼9,’’,null等)產(chǎn)生的傾斜問題。把空值的key變成1個字符串加上隨機數(shù)，就可以把傾斜的數(shù)據(jù)分到不同的reduce上 ,解決數(shù)據(jù)傾斜問題。由于空值不參與關(guān)聯(lián)，即便分到不同的reduce上，也不影響終究的結(jié)果。附上hadoop通用關(guān)聯(lián)的實現(xiàn)方法（關(guān)聯(lián)通過2次排序?qū)崿F(xiàn)的，關(guān)聯(lián)的列為parition key,關(guān)聯(lián)的列c1和表的tag組成排序的group key,根據(jù)parition key分配reduce。同1reduce內(nèi)根據(jù)group key排序）。

問題2：不同數(shù)據(jù)類型id的關(guān)聯(lián)會產(chǎn)生數(shù)據(jù)傾斜問題。

1張表s8的日志，每一個商品1條記錄，要和商品表關(guān)聯(lián)。但關(guān)聯(lián)卻碰到傾斜的問題。s8的日志中有字符串商品id,也有數(shù)字的商品id,類型是string的，但商品中的數(shù)字id是bigint的。猜想問題的緣由是把s8的商品id轉(zhuǎn)成數(shù)字id做hash來分配reduce，所以字符串id的s8日志，都到1個reduce上了，解決的方法驗證了這個猜想。

方法：把數(shù)字類型轉(zhuǎn)換成字符串類型

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的優(yōu)化的特性

hive對union all優(yōu)化只局限于非嵌套查詢。

比如以下的例子：

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;

從業(yè)務(wù)邏輯上說，子查詢內(nèi)的group by 怎樣都看顯很多余（功能上的過剩,除非有count(distinct)），如果不是由于hive bug或性能上的考量(曾出現(xiàn)如果不子查詢group by ，數(shù)據(jù)得不到正確的結(jié)果的hive bug)。所以這個hive按經(jīng)驗轉(zhuǎn)換成

select * from

(select * from t1

Union all

Select * from t2

) t3

   Group by c1,c2,c3;

經(jīng)過測試，并未出現(xiàn)union all的hive bug,數(shù)據(jù)是1致的。mr的作業(yè)數(shù)有3減少到1。

t1相當(dāng)于1個目錄，t2相當(dāng)于1個目錄，那末對map reduce程序來講，t1,t2可以做為map reduce 作業(yè)的mutli inputs。那末，這可以通過1個map reduce 來解決這個問題。Hadoop的計算框架，不怕數(shù)據(jù)多，就怕作業(yè)數(shù)多。

但如果換成是其他計算平臺如oracle，那就不1定了，由于把大的輸入拆成兩個輸入，分別排序匯總后merge(假設(shè)兩個子排序是并行的話)，是有可能性能更優(yōu)的（比如希爾排序比冒泡排序的性能更優(yōu)）。

問題4：比如推行效果表要和商品表關(guān)聯(lián)，效果表中的auction id列既有商品id,也有數(shù)字id,和商品表關(guān)聯(lián)得到商品的信息。那末以下的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。

比分別過濾數(shù)字id,字符串id然后分別和商品表關(guān)聯(lián)性能要好。

這樣寫的好處,1個MR作業(yè),商品表只讀取1次，推行效果表只讀取1次。把這個sql換成MR代碼的話，map的時候，把a表的記錄打上標(biāo)簽a,商品表記錄每讀取1條，打上標(biāo)簽b，變成兩個<key ,value>對，<b,數(shù)字id>，<b,字符串id>。所以商品表的hdfs讀只會是1次。

問題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。假設(shè)先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優(yōu)化上可以做得更智能（把子查詢當(dāng)作臨時表），這樣可以減少開發(fā)人員的負擔(dān)。出現(xiàn)這個問題的緣由應(yīng)當(dāng)是union all目前的優(yōu)化只局限于非嵌套查詢。如果寫MR程序這1點也不是問題，就是multi inputs。

問題6：使用map join解決數(shù)據(jù)傾斜的常景下小表關(guān)聯(lián)大表的問題，但如果小表很大，怎樣解決。這個使用的頻率非常高，但如果小表很大，大到map join會出現(xiàn)bug或異常，這時候就需要特別的處理。云瑞和玉璣提供了非常給力的解決方案。以下例子：

Select * from log a

Left outer join members b

On a.memberid = b.memberid.

Members有600w+的記錄，把members分發(fā)到所有的map上也是個不小的開消，而且map join不支持這么大的小表。如果用普通的join，又會碰到數(shù)據(jù)傾斜的問題。

解決方法：

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。

先根據(jù)log取所有的memberid，然后mapjoin 關(guān)聯(lián)members取今天有日志的members的信息，然后在和log做mapjoin。

假設(shè)，log里memberid有上百萬個，這就又回到原來map join問題。所幸，逐日的會員uv不會太多，有交易的會員不會太多，有點擊的會員不會太多，有傭金的會員不會太多等等。所以這個方法能解決很多場景下的數(shù)據(jù)傾斜問題。

問題7：HIVE下通用的數(shù)據(jù)傾斜解決方法,double被關(guān)聯(lián)的相對較小的表，這個方法在mr的程序里經(jīng)常使用。還是剛才的那個問題：

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表只有1列number，有30行，是1,30的自然數(shù)序列。就是把member表膨脹成30份，然后把log數(shù)據(jù)根據(jù)memberid和pvtime分到不同的reduce里去，這樣可以保證每一個reduce分配到的數(shù)據(jù)可以相對均勻。就目前測試來看，使用mapjoin的方案性能稍好。后面的方案合適在map join沒法解決問題的情況下。

長遠假想，把以下的優(yōu)化方案做成通用的hive優(yōu)化方法

1. 采樣log表，哪些memberid比較傾斜，得到1個結(jié)果表tmp1。由于對計算框架來講，所有的數(shù)據(jù)過來，他都是不知道數(shù)據(jù)散布情況的，所以采樣是其實不可少的。Stage1

2. 數(shù)據(jù)的散布符合社會學(xué)統(tǒng)計規(guī)則，貧富不均。傾斜的key不會太多，就像1個社會的富人不多，奇特的人不多1樣。所以tmp1記錄數(shù)會很少。把tmp1和members做map join生成tmp2,把tmp2讀到distribute file cache。這是1個map進程。Stage2

3.    map讀入members和log，假設(shè)記錄來自log,則檢查memberid是不是在tmp2里，如果是，輸出到本地文件a,否則生成<memberid,value>的key,value對，假設(shè)記錄來自member,生成<memberid,value>的key,value對，進入reduce階段。Stage3.

4. 終究把a文件，把Stage3 reduce階段輸出的文件合并起寫到hdfs。

這個方法在hadoop里應(yīng)當(dāng)是能實現(xiàn)的。Stage2是1個map進程，可以和stage3的map進程可以合并成1個map進程。

這個方案目標(biāo)就是：傾斜的數(shù)據(jù)用mapjoin,不傾斜的數(shù)據(jù)用普通的join，終究合并得到完全的結(jié)果。用hive sql寫的話，sql會變得很多段，而且log表會有屢次讀。傾斜的key始終是很少的，這個在絕大部份的業(yè)務(wù)背景下適用。那是不是可以作為hive針對數(shù)據(jù)傾斜join時候的通用算法呢？

問題8：多粒度(平級的)uv的計算優(yōu)化，比如要計算店鋪的uv。還有要計算頁面的uv,pvip.

方案1:

Select shopid,count(distinct uid)

From log group by shopid;

Select pageid, count(distinct uid),

From log group by pageid;

由于存在數(shù)據(jù)傾斜問題，這個結(jié)果的運行時間是非常長的。

方案2：

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寫，多1次額外的map階段的hdfs寫。使用這個方法，可以順利的產(chǎn)出結(jié)果。

方案3：

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’ ;

終究得到兩個結(jié)果表t3,頁面uv表，t4,店鋪結(jié)果表。從io上來講，log1次讀。但比方案2少次hdfs寫（multi insert有時會增加額外的map階段hdfs寫）。作業(yè)數(shù)減少1個到3，有reduce的作業(yè)數(shù)由4減少到2，第3步是1個小表的map進程，分下表，計算資源消耗少。但方案2每一個都是大范圍的去重匯總計算。

這個優(yōu)化的主要思路是，map reduce作業(yè)初始化話的時間是比較長，既然起來了，讓他多干點活，順便把頁面按uid去重的活也干了，省下log的1次讀和作業(yè)的初始化時間，省下網(wǎng)絡(luò)shuffle的io，但增加了本地磁盤讀寫。效力提升較多。

這個方案合適平級的不需要逐級向上匯總的多粒度uv計算，粒度越多，節(jié)省資源越多，比較通用。

問題9：多粒度，逐層向上匯總的uv結(jié)算。比如4個維度，a,b,c,d，分別計算a,b,c,d,uv；

a,b,c,uv;a,b,uv;a;uv,total uv4個結(jié)果表。這可以用問題8的方案2，這里由于uv場景的特殊性，多粒度，逐層向上匯總，就能夠使用1次排序，所有uv計算受益的計算方法。

案例：目前mm_log日志1天有25億+的pv數(shù)，要從mm日志中計算uv，與ipuv,1總計算

3個粒度的結(jié)果表

（memberid,siteid,adzoneid,province,uv,ipuv）  R_TABLE_4

（memberid,siteid,adzoneid,uv,ipuv） R_TABLE_3

 (memberid,siteid,uv,ipuv) R_TABLE_2

第1步：按memberid,siteid,adzoneid,province,使用group去重,產(chǎn)生臨時表，對cookie,ip

打上標(biāo)簽放1起，1起去重，臨時表叫T_4;

Select memberid,siteid,adzoneid,province,type,user

From(

Select memberid,siteid,adzoneid,province,‘a(chǎn)’ 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 ;

第2步：排名,產(chǎn)生表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，效果相當(dāng)于Group by user去重后做sum操作。

第3步：不同粒度uv統(tǒng)計，先從最細粒度的開始統(tǒng)計，產(chǎn)生結(jié)果表R_TABLE_4,這時候，結(jié)果集只有10w的級別。

如統(tǒng)計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 ,

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機掃描二維碼進行捐贈