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 = 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 = 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,根据partition key分配reduce。同一个reduce内根据group key排序)。


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

一张表s8的日志,每个商品一条记录,要和商品表关联。但关联却碰到倾斜的问题。s8的日志中有字符串商品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对un于ion 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  )

      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

union all

  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程序这一点也不是问题,就是mult 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(x)*/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做map join

假如,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做mapjoin生成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最强大和核心能力就是partion和sort。按type,accokie分组,












 










 



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值