hive性能优化模板.docx

hive性能优化模板.docx

《hive性能优化模板.docx》由会员分享，可在线阅读，更多相关《hive性能优化模板.docx（13页珍藏版）》请在冰豆网上搜索。

hive性能优化模板

优化时，把hivesql当做mapreduce程序来读，会有意想不到的惊喜。

理解hadoop的核心能力，是hive优化的根本。

这是这一年来，项目组所有成员宝贵的经验总结。

长期观察hadoop处理数据的过程，有几个显著的特征:

1.不怕数据多，就怕数据倾斜。

2．对jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，没半小时是跑不完的。

mapreduce作业初始化的时间是比较长的。

3.对sum，count来说，不存在数据倾斜问题。

4.对count（distinct）,效率较低，数据量一多，准出问题，如果是多count（distinct）效率更低。

优化可以从几个方面着手：

1. 好的模型设计事半功倍。

2. 解决数据倾斜问题。

3. 减少job数。

4. 设置合理的mapreduce的task数，能有效提升性能。

（比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够）。

5. 自己动手写sql解决数据倾斜问题是个不错的选择。

sethive.groupby.skewindata=true;这是通用的算法优化，但算法优化总是漠视业务，习惯性提供通用的解决方法。

 Etl开发人员更了解业务，更了解数据，所以通过业务逻辑解决倾斜的方法往往更精确，更有效。

6. 对count（distinct）采取漠视的方法，尤其数据大的时候很容易产生倾斜问题，不抱侥幸心理。

自己动手，丰衣足食。

7. 对小文件进行合并，是行至有效的提高调度效率的方法，假如我们的作业设置合理的文件数，对云梯的整体调度效率也会产生积极的影响。

8. 优化时把握整体，单个作业最优不如整体最优。

迁移和优化过程中的案例：

问题1：

如日志中，常会有信息丢失的问题，比如全网日志中的user_id，如果取其中的user_id和bmw_users关联，就会碰到数据倾斜的问题。

方法：

解决数据倾斜问题

解决方法1.User_id为空的不参与关联，例如：

Select*

Fromloga

Join bmw_usersb

Ona.user_idisnotnull

Anda.user_id=b.user_id

Unionall

Select*

fromloga

wherea.user_idisnull.

解决方法2 ：

Select*

fromloga

leftouterjoinbmw_usersb

oncasewhena.user_idisnullthenconcat（‘dp_hive’,rand（））elsea.user_idend=b.user_id;

总结：

2比1效率更好，不但io少了，而且作业数也少了。

1方法log读取两次，jobs是2。

2方法job数是1 。

这个优化适合无效id（比如-99,’’,null等）产生的倾斜问题。

把空值的key变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。

因为空值不参与关联，即使分到不同的reduce上，也不影响最终的结果。

附上hadoop通用关联的实现方法（关联通过二次排序实现的，关联的列为paritionkey,关联的列c1和表的tag组成排序的groupkey,根据paritionkey分配reduce。

同一reduce内根据groupkey排序）。

问题2：

不同数据类型id的关联会产生数据倾斜问题。

一张表s8的日志，每个商品一条记录，要和商品表关联。

但关联却碰到倾斜的问题。

s8的日志中有字符串商品id,也有数字的商品id,类型是string的，但商品中的数字id是bigint的。

猜测问题的原因是把s8的商品id转成数字id做hash来分配reduce，所以字符串id的s8日志，都到一个reduce上了，解决的方法验证了这个猜测。

方法：

把数字类型转换成字符串类型

Select*froms8_loga

Leftouterjoinr_auction_auctionsb

Ona.auction_id=cast（b.auction_idasstring）;

问题3：

利用hive 对UNIONALL的优化的特性

hive对unionall优化只局限于非嵌套查询。

比如以下的例子：

select*from

（select*fromt1

 Groupbyc1,c2,c3

Unionall

Select*fromt2

Groupbyc1,c2,c3）t3

   Groupbyc1,c2,c3;

从业务逻辑上说，子查询内的groupby 怎么都看显得多余（功能上的多余,除非有count（distinct）），如果不是因为hivebug或者性能上的考量（曾经出现如果不子查询groupby ，数据得不到正确的结果的hivebug）。

所以这个hive按经验转换成

select*from

（select*fromt1

Unionall

Select*fromt2

）t3

   Groupbyc1,c2,c3;

经过测试，并未出现unionall的hivebug,数据是一致的。

mr的作业数有3减少到1。

t1相当于一个目录，t2相当于一个目录，那么对mapreduce程序来说，t1,t2可以做为mapreduce 作业的mutliinputs。

那么，这可以通过一个mapreduce 来解决这个问题。

Hadoop的计算框架，不怕数据多，就怕作业数多。

但如果换成是其他计算平台如oracle，那就不一定了，因为把大的输入拆成两个输入，分别排序汇总后merge（假如两个子排序是并行的话），是有可能性能更优的（比如希尔排序比冒泡排序的性能更优）。

问题4：

比如推广效果表要和商品表关联，效果表中的auctionid列既有商品id,也有数字id,和商品表关联得到商品的信息。

那么以下的hivesql性能会比较好

Select*fromeffecta

Join（selectauction_idasauction_idfromauctions

Unionall

Selectauction_string_idasauction_idfromauctions

）b

Ona.auction_id=b.auction_id。

比分别过滤数字id,字符串id然后分别和商品表关联性能要好。

这样写的好处,1个MR作业,商品表只读取一次，推广效果表只读取一次。

把这个sql换成MR代码的话，map的时候，把a表的记录打上标签a,商品表记录每读取一条，打上标签b，变成两个对，，。

所以商品表的hdfs读只会是一次。

问题5：

先join生成临时表，在unionall还是写嵌套查询，这是个问题。

比如以下例子：

Select*

From（select*

    Fromt1

    Uionall

    select*

    Fromt4

    Unionall

    Select*

    Fromt2

    Joint3

    Ont2.id=t3.id

    ）x

Groupbyc1,c2;

这个会有4个jobs。

假如先join生成临时表的话t5,然后unionall，会变成2个jobs。

Insertoverwritetablet5

Select*

    Fromt2

    Joint3

    Ont2.id=t3.id

;

Select*from（t1unionallt4unionallt5）;

hive在unionall优化上可以做得更智能（把子查询当做临时表），这样可以减少开发人员的负担。

出现这个问题的原因应该是unionall目前的优化只局限于非嵌套查询。

如果写MR程序这一点也不是问题，就是multiinputs。

问题6：

使用mapjoin解决数据倾斜的常景下小表关联大表的问题，但如果小表很大，怎么解决。

这个使用的频率非常高，但如果小表很大，大到mapjoin会出现bug或异常，这时就需要特别的处理。

云瑞和玉玑提供了非常给力的解决方案。

以下例子：

Select*fromloga

Leftouterjoinmembersb

Ona.memberid=b.memberid.

Members有600w+的记录，把members分发到所有的map上也是个不小的开销，而且mapjoin不支持这么大的小表。

如果用普通的join，又会碰到数据倾斜的问题。

解决方法：

Select/*+mapjoin（x）*/*fromloga

Leftouterjoin（select /*+mapjoin（c）*/d.*

From（select distinctmemberidfromlog）c

Joinmembersd

Onc.memberid=d.memberid

）x

Ona.memberid=b.memberid。

先根据log取所有的memberid，然后mapjoin 关联members取今天有日志的members的信息，然后在和log做mapjoin。

假如，log里memberid有上百万个，这就又回到原来mapjoin问题。

所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。

所以这个方法能解决很多场景下的数据倾斜问题。

问题7：

HIVE下通用的数据倾斜解决方法,double被关联的相对较小的表，这个方法在mr的程序里常用。

还是刚才的那个问题：

Select *fromloga

Leftouterjoin（select /*+mapjoin（e）*/

memberid,number

             Frommembersd

            Joinnume

            ）b

Ona.memberid= b.memberid

Andmod（a.pvtime,30）+1=b.number。

Num表只有一列number，有30行，是1,30的自然数序列。

就是把member表膨胀成30份，然后把log数据根据memberid和pvtime分到不同的reduce里去，这样可以保证每个reduce分配到的数据可以相对均匀。

就目前测试来看，使用mapjoin的方案性能稍好。

后面的方案适合在mapjoin无法解决问题的情况下。

长远设想，把如下的优化方案做成通用的hive优化方法

1. 采样log表，哪些memberid比较倾斜，得到一个结果表tmp1。

由于对计算框架来说，所有的数据过来，他都是不知道数据分布情况的，所以采样是并不可少的。

Stage1

2. 数据的分布符合社会学统计规则，贫富不均。

倾斜的key不会太多，就像一个社会的富人不多，奇特的人不多一样。

所以tmp1记录数会很少。

把tmp1和members做mapjoin生成tmp2,把tmp2读到distributefilecache。

这是一个map过程。

Stage2

3.   map读入members和log，假如记录来自log,则检查memberid是否在tmp2里，如果是，输出到本地文件a,否则生成的key,value对，假如记录来自member,生成的key,value对，进入reduce阶段。

Stage3.

4. 最终把a文件，把Stage3reduce阶段输出的文件合并起写到hdfs。

这个方法在hadoop里应该是能实现的。

Stage2是一个map过程，可以和stage3的map过程可以合并成一个map过程。

这个方案目标就是：

倾斜的数据用mapjoin,不倾斜的数据用普通的join，最终合并得到完整的结果。

用hivesql写的话，sql会变得很多段，而且log表会有多次读。

倾斜的key始终是很少的，这个在绝大部分的业务背景下适用。

那是否可以作为hive针对数据倾斜join时候的通用算法呢？

问题8：

多粒度（平级的）uv的计算优化，比如要计算店铺的uv。

还有要计算页面的uv,pvip.

方案1:

Selectshopid,count（distinctuid）

Fromloggroupbyshopid;

Selectpageid,count（distinctuid）,

Fromloggroupbypageid;

由于存在数据倾斜问题，这个结果的运行时间是非常长的。

方案二：

Fromlog

Insertoverwritetablet1（type=’1’）

Selectshopid

Groupbyshopid,acookie

Insertoverwritetablet1（type=’2’）

Groupbypageid,acookie;

店铺uv:

Selectshopid,sum

（1）

Fromt1

Wheretype=’1’

Groupbyshopid;

页面uv:

Selectpageid,sum

（1）

Fromt1

Wheretype=’1’

Groupbypageid;

这里使用了multiinsert的方法，有效减少了hdfs读，但multiinsert会增加hdfs写，多一次额外的map阶段的hdfs写。

使用这个方法，可以顺利的产出结果。

方案三：

Insertintot1

Selecttype,type_name,’’asuid

From（

Select ‘page’astype,

       Pageidastype_name,

       Uid

Fromlog

Unionall

Select ‘shop’astype,

      Shopidastype_name,

      Uid

Fromlog）y

Groupbytype,type_name,uid;

Insertintot2

Selecttype,type_name,sum

（1）

Fromt1

Groupbytype,type_name;

Fromt2

Insertintot3

Selecttype,type_name,uv

Wheretype=’page’

Selecttype,type_name,uv

Wheretype=’shop’;

最终得到两个结果表t3,页面uv表，t4,店铺结果表。

从io上来说，log一次读。

但比方案2少次hdfs写（multiinsert有时会增加额外的map阶段hdfs写）。

作业数减少1个到3，有reduce的作业数由4减少到2，第三步是一个小表的map过程，分下表，计算资源消耗少。

但方案2每个都是大规模的去重汇总计算。

这个优化的主要思路是，mapreduce作业初始化话的时间是比较长，既然起来了，让他多干点活，顺便把页面按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,totaluv4个结果表。

这可以用问题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;

Selectmemberid,siteid,adzoneid,province,type,user

From（

Selectmemberid,siteid,adzoneid,province,‘a’type,cookieasuserfrommm_logwhereds=20101205

Unionall

Selectmemberid,siteid,adzoneid,province,‘i’type,ipasuserfrommm_logwhereds=20101205

）xgroupbymemberid,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）asadzone_num,

row_number（type,user,memberid,siteid）assite_num,

row_number（type,user,memberid）asmember_num,

row_number（type,user）astotal_num

from（select *fromT_4distributebytype,usersortbytype,user,memberid,siteid,adzoneid）x;

这样就可以得到不同层次粒度上user的排名，相同的userid在不同的粒度层次上，排名等于1的记录只有1条。

取排名等于1的做sum，效果相当于Groupbyuser去重后做sum操作。

第三步：

不同粒度uv统计，先从最细粒度的开始统计，产生结果表R_TABLE_4,这时，结果集只有10w的级别。

如统计memberid,siteid,adzoneid,provinceid粒度的uv使用的方法就是

Selectmemberid,siteid,adzoneid,provinceid,

sum（casewhen type=’a’thencast

（1）asbigintend）asprovince_uv,

sum（casewhen type=’i’thencast

（1）asbigintend）asprovince_ip,

sum（casewhenadzone_num=1andtype=’a’thencast

（1）asbigintend）asadzone_uv,

sum（casewhenadzone_num=1andtype=’i’thencast

（1）asbigintend）asadzone_ip,

sum（casewhensite_num=1andtype=’a’thencast

（1）asbigintend）assite_uv,

sum（casewhensite_num=1andtype=’i’thencast

（1）asbigintend）assite_ip,

sum（casewhenmember_num=1andtype=’a’thencast

（1）asbigintend）asmember_uv,

sum（casewhenmember_num=1andtype=’i’thencast

（1）asbigintend）asmember_ip,

sum（casewhentotal_num=1andtype=’a’thencast

（1）asbigintend）astotal_uv,

sum（casewhentotal_num=1andtype=’i’thencast

（1）asbigintend）astotal_ip,

fromT_4_NUM

groupbymemberid,siteid,adzoneid,provinceid;

广告位粒度的uv的话，从R_TABLE_4统计，这是源表做10w级别的统计

Selectmemberid,siteid,adzoneid,sum（adzone_uv）,sum（adzone_ip）

FromR_TABLE_4

Groupbymemberid,siteid,adzoneid；

memberid,siteid的uv计算 ，

memberid的uv计算,

totaluv 的计算也都从R_TABLE_4汇总。