<form id="dlljd"></form>
        <address id="dlljd"><address id="dlljd"><listing id="dlljd"></listing></address></address>

        <em id="dlljd"><form id="dlljd"></form></em>

          <address id="dlljd"></address>
            <noframes id="dlljd">

              聯系我們 - 廣告服務 - 聯系電話:
              您的當前位置: > 關注 > > 正文

              如何把hivesql當做mapreduce程序來讀?hivesql解決數據傾斜的方法

              來源:CSDN 時間:2023-04-11 14:23:13


              【資料圖】

              優化時,把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 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   Group by c1,c2,c3 Union all  Select * from t2 Group by c1,c2,c3) 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,變成兩個對,,。所以商品表的hdfs讀只會是一次。  問題5:先join生成臨時表,在union all還是寫嵌套查詢,這是個問題。比如以下例子: Select *  From (select *      From t1      Uion all      select *      From t4      Select *      From t2       Join t3      On t2.id = t3.id      ) 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 t4 union all t5)  hive在union all優化上可以做得更智能(把子查詢當做臨時表),這樣可以減少開發人員的負擔。出現這個問題的原因應該是union all目前的優化只局限于非嵌套查詢。如果寫MR程序這一點也不是問題,就是muti 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,否則生成的key,value對,假如記錄來自member, 生成的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分組, 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,效果相當于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匯總。

              責任編輯:

              標簽:

              相關推薦:

              精彩放送:

              新聞聚焦
              Top 中文字幕在线观看亚洲日韩