【資料圖】

優(yōu)化時(shí)，把hive sql當(dāng)做map reduce程序來讀，會(huì)有意想不到的驚喜。 理解hadoop的核心能力，是hive優(yōu)化的根本。這是這一年來，項(xiàng)目組所有成員寶貴的經(jīng)驗(yàn)總結(jié)。   長期觀察hadoop處理數(shù)據(jù)的過程，有幾個(gè)顯著的特征: 1.不怕數(shù)據(jù)多，就怕數(shù)據(jù)傾斜。 2．對jobs數(shù)比較多的作業(yè)運(yùn)行效率相對比較低，比如即使有幾百行的表，如果多次關(guān)聯(lián)多次匯總，產(chǎn)生十幾個(gè)jobs，沒半小時(shí)是跑不完的。map reduce作業(yè)初始化的時(shí)間是比較長的。 3.對sum，count來說，不存在數(shù)據(jù)傾斜問題。 4.對count(distinct ),效率較低，數(shù)據(jù)量一多，準(zhǔn)出問題，如果是多count(distinct )效率更低。   優(yōu)化可以從幾個(gè)方面著手： 1. 好的模型設(shè)計(jì)事半功倍。 2. 解決數(shù)據(jù)傾斜問題。 3. 減少job數(shù)。 4. 設(shè)置合理的map reduce的task數(shù)，能有效提升性能。(比如，10w+級別的計(jì)算，用160 個(gè)reduce，那是相當(dāng)?shù)睦速M(fèi)，1個(gè)足夠)。 5. 自己動(dòng)手寫sql解決數(shù)據(jù)傾斜問題是個(gè)不錯(cuò)的選擇。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ù)大的時(shí)候很容易產(chǎn)生傾斜問題，不抱僥幸心 理。自己動(dòng)手，豐衣足食。 7. 對小文件進(jìn)行合并，是行至有效的提高調(diào)度效率的方法，假如我們的作業(yè)設(shè)置合理的文 件數(shù)，對云梯的整體調(diào)度效率也會(huì)產(chǎn)生積極的影響。 8. 優(yōu)化時(shí)把握整體，單個(gè)作業(yè)最優(yōu)不如整體最優(yōu)。  遷移和優(yōu)化過程中的案例：  問題1：如日志中，常會(huì)有信息丟失的問題，比如全網(wǎng)日志中的user_id，如果取其中的user_id和bmw_users關(guān)聯(lián)，就會(huì)碰到數(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 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 。這個(gè)優(yōu)化適合無效id(比如-99,’’,null等)產(chǎn)生的傾斜問題。把空值的key變成一個(gè)字符串加上隨機(jī)數(shù)，就能把傾斜的數(shù)據(jù)分到不同的reduce上 ,解決數(shù)據(jù)傾斜問題。因?yàn)榭罩挡粎⑴c關(guān)聯(lián)，即使分到不同的reduce上，也不影響最終的結(jié)果。附上hadoop通用關(guān)聯(lián)的實(shí)現(xiàn)方法（關(guān)聯(lián)通過二次排序?qū)崿F(xiàn)的，關(guān)聯(lián)的列為parition key,關(guān)聯(lián)的列c1和表的tag組成排序的group key,根據(jù)parition key分配reduce。同一reduce內(nèi)根據(jù)group key排序）。  問題2：不同數(shù)據(jù)類型id的關(guān)聯(lián)會(huì)產(chǎn)生數(shù)據(jù)傾斜問題。 一張表s8的日志，每個(gè)商品一條記錄，要和商品表關(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日志，都到一個(gè)reduce上了，解決的方法驗(yàn)證了這個(gè)猜測。  方法：把數(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)），如果不是因?yàn)閔ive bug或者性能上的考量(曾經(jīng)出現(xiàn)如果不子查詢group by ，數(shù)據(jù)得不到正確的結(jié)果的hive bug)。所以這個(gè)hive按經(jīng)驗(yàn)轉(zhuǎn)換成 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; 經(jīng)過測試，并未出現(xiàn)union all的hive bug,數(shù)據(jù)是一致的。mr的作業(yè)數(shù)有3減少到1。 t1相當(dāng)于一個(gè)目錄，t2相當(dāng)于一個(gè)目錄，那么對map reduce程序來說，t1,t2可以做為map reduce 作業(yè)的mutli inputs。那么，這可以通過一個(gè)map reduce 來解決這個(gè)問題。Hadoop的計(jì)算框架，不怕數(shù)據(jù)多，就怕作業(yè)數(shù)多。 但如果換成是其他計(jì)算平臺(tái)如oracle，那就不一定了，因?yàn)榘汛蟮妮斎氩鸪蓛蓚€(gè)輸入，分別排序匯總后merge(假如兩個(gè)子排序是并行的話)，是有可能性能更優(yōu)的（比如希爾排序比冒泡排序的性能更優(yōu)）。  問題4：比如推廣效果表要和商品表關(guān)聯(lián)，效果表中的auction id列既有商品id,也有數(shù)字id,和商品表關(guān)聯(lián)得到商品的信息。那么以下的hive sql性能會(huì)比較好 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個(gè)MR作業(yè),商品表只讀取一次，推廣效果表只讀取一次。把這個(gè)sql換成MR代碼的話，map的時(shí)候，把a(bǔ)表的記錄打上標(biāo)簽a,商品表記錄每讀取一條，打上標(biāo)簽b，變成兩個(gè)對，，。所以商品表的hdfs讀只會(huì)是一次。  問題5：先join生成臨時(shí)表，在union all還是寫嵌套查詢，這是個(gè)問題。比如以下例子： Select *  From (select *      From t1      Uion all      select *      From t4      Select *      From t2       Join t3      On t2.id = t3.id      ) Group by c1,c2; 這個(gè)會(huì)有4個(gè)jobs。假如先join生成臨時(shí)表的話t5,然后union all，會(huì)變成2個(gè)jobs。 Insert overwrite table t5 Select *      From t2       Join t3      On t2.id = t3.id ; Select * from (t1 union t4 union all t5)  hive在union all優(yōu)化上可以做得更智能（把子查詢當(dāng)做臨時(shí)表），這樣可以減少開發(fā)人員的負(fù)擔(dān)。出現(xiàn)這個(gè)問題的原因應(yīng)該是union all目前的優(yōu)化只局限于非嵌套查詢。如果寫MR程序這一點(diǎn)也不是問題，就是muti inputs。  問題6：使用map join解決數(shù)據(jù)傾斜的常景下小表關(guān)聯(lián)大表的問題，但如果小表很大，怎么解決。這個(gè)使用的頻率非常高，但如果小表很大，大到map join會(huì)出現(xiàn)bug或異常，這時(shí) 就需要特別的處理。云瑞和玉璣提供了非常給力的解決方案。以下例子： Select * from log a Left outer join members b On a.memberid = b.memberid. Members有600w+的記錄，把members分發(fā)到所有的map上也是個(gè)不小的開銷，而且map join不支持這么大的小表。如果用普通的join，又會(huì)碰到數(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。 假如，log里memberid有上百萬個(gè)，這就又回到原來map join問題。所幸，每日的會(huì)員uv不會(huì)太多，有交易的會(huì)員不會(huì)太多，有點(diǎn)擊的會(huì)員不會(huì)太多，有傭金的會(huì)員不會(huì)太多等等。所以這個(gè)方法能解決很多場景下的數(shù)據(jù)傾斜問題。  問題7：HIVE下通用的數(shù)據(jù)傾斜解決方法,double被關(guān)聯(lián)的相對較小的表，這個(gè)方法在mr的程序里常用。還是剛才的那個(gè)問題： 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的自然數(shù)序列。就是把member表膨脹成30份，然后把log數(shù)據(jù)根據(jù)memberid和pvtime分到不同的reduce里去，這樣可以保證每個(gè)reduce分配到的數(shù)據(jù)可以相對均勻。就目前測試來看，使用mapjoin的方案性能稍好。后面的方案適合在map join無法解決問題的情況下。  長遠(yuǎn)設(shè)想，把如下的優(yōu)化方案做成通用的hive優(yōu)化方法 1. 采樣log表，哪些memberid比較傾斜，得到一個(gè)結(jié)果表tmp1。由于對計(jì)算框架來說， 所有的數(shù)據(jù)過來，他都是不知道數(shù)據(jù)分布情況的，所以采樣是并不可少的。Stage1 2. 數(shù)據(jù)的分布符合社會(huì)學(xué)統(tǒng)計(jì)規(guī)則，貧富不均。傾斜的key不會(huì)太多，就像一個(gè)社會(huì)的富 人不多，奇特的人不多一樣。所以tmp1記錄數(shù)會(huì)很少。把tmp1和members做map join生成tmp2,把tmp2讀到distribute file cache。這是一個(gè)map過程。Stage2 3. map讀入members和log，假如記錄來自log,則檢查memberid是否在tmp2里，如果 是，輸出到本地文件a,否則生成的key,value對，假如記錄來自member, 生成的key,value對，進(jìn)入reduce階段。Stage3. 4. 最終把a(bǔ)文件，把Stage3 reduce階段輸出的文件合并起寫到hdfs。 這個(gè)方法在hadoop里應(yīng)該是能實(shí)現(xiàn)的。Stage2是一個(gè)map過程，可以和stage3的map過程可以合并成一個(gè)map過程。 這個(gè)方案目標(biāo)就是：傾斜的數(shù)據(jù)用mapjoin,不傾斜的數(shù)據(jù)用普通的join，最終合并得到完整的結(jié)果。用hive sql寫的話，sql會(huì)變得很多段，而且log表會(huì)有多次讀。傾斜的key始終是很少的，這個(gè)在絕大部分的業(yè)務(wù)背景下適用。那是否可以作為hive針對數(shù)據(jù)傾斜join時(shí)候的通用算法呢？   問題8：多粒度(平級的)uv的計(jì)算優(yōu)化，比如要計(jì)算店鋪的uv。還有要計(jì)算頁面的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ù)傾斜問題，這個(gè)結(jié)果的運(yùn)行時(shí)間是非常長的。 方案二： 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會(huì)增加hdfs寫，多一次額外的map階段的hdfs寫。使用這個(gè)方法，可以順利的產(chǎn)出結(jié)果。 方案三： 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’  最終得到兩個(gè)結(jié)果表t3,頁面uv表，t4,店鋪結(jié)果表。從io上來說，log一次讀。但比方案2少次hdfs寫（multi insert有時(shí)會(huì)增加額外的map階段hdfs寫）。作業(yè)數(shù)減少1個(gè)到3，有reduce的作業(yè)數(shù)由4減少到2，第三步是一個(gè)小表的map過程，分下表，計(jì)算資源消耗少。但方案2每個(gè)都是大規(guī)模的去重匯總計(jì)算。 這個(gè)優(yōu)化的主要思路是，map reduce作業(yè)初始化話的時(shí)間是比較長，既然起來了，讓他多干點(diǎn)活，順便把頁面按uid去重的活也干了，省下log的一次讀和作業(yè)的初始化時(shí)間，省下網(wǎng)絡(luò)shuffle的io，但增加了本地磁盤讀寫。效率提升較多。 這個(gè)方案適合平級的不需要逐級向上匯總的多粒度uv計(jì)算，粒度越多，節(jié)省資源越多，比較通用。  問題9：多粒度，逐層向上匯總的uv結(jié)算。比如4個(gè)維度，a,b,c,d，分別計(jì)算a,b,c,d,uv； a,b,c,uv;a,b,uv;a;uv,total uv4個(gè)結(jié)果表。這可以用問題8的方案二，這里由于uv場景的特殊性，多粒度，逐層向上匯總，就可以使用一次排序，所有uv計(jì)算受益的計(jì)算方法。 案例：目前mm_log日志一天有25億+的pv數(shù)，要從mm日志中計(jì)算uv，與ipuv,一共計(jì)算 三個(gè)粒度的結(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 第一步：按memberid,siteid,adzoneid,province,使用group去重,產(chǎn)生臨時(shí)表，對cookie,ip 打上標(biāo)簽放一起，一起去重，臨時(shí)表叫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  第二步：排名,產(chǎn)生表T_4_NUM.Hadoop最強(qiáng)大和核心能力就是parition 和 sort.按type，acookie分組， ype，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操作。 第三步：不同粒度uv統(tǒng)計(jì)，先從最細(xì)粒度的開始統(tǒng)計(jì)，產(chǎn)生結(jié)果表R_TABLE_4,這時(shí)，結(jié)果集只有10w的級別。 如統(tǒng)計(jì)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統(tǒng)計(jì)，這是源表做10w級別的統(tǒng)計(jì) Select memberid,siteid,adzoneid,sum(adzone_uv),sum(adzone_ip) From R_TABLE_4  Group by memberid,siteid,adzoneid； memberid,siteid的uv計(jì)算 ， memberid的uv計(jì)算, total uv 的計(jì)算也都從R_TABLE_4匯總。