join
按照key进行分发,key的合并在map阶段,而在join左边的表,也就是主表,会首先读入内存,当然它不是全部读入内存,而是部分读入内存,如果左边的表的key相对分散(或少,分散的意思就是相同key的数据量小),读入内存的数据会比较小,join任务执行会比较快。而如果左边的表的key比较集中,而这张表的数据量很大,那么数据倾斜会比较严重。
map阶段同一key数据分发给一个reduce
join原则:
小表join大表,原因是在join操作的reduce阶段(不是map阶段),位于join左边的表的内容会被部分加载进内存,如果数据量比较少,就是全部加载到内存。将条目少的表放在左边,可以有效减少发生内存溢出的几率。
多个表关联时,最多分拆成小段,避免大sql(无法控制中间job)
多表join on条件相同时合并为一个map-reduce,做outer join的时候也是一样,查看执行计划explain
比如查询三个表关联:
select pt.page_id,count(t.url) PV
from rpt_page_type pt
join
(select url_page_id,url from trackinfo where ds='2013-10-11') t
on pt.page_id=t.url_page_id
join
(select page_id from rpt_page_kpi_new where ds='2013-10-11') r
on t.url_page_id=r.page_id
group by pt.page_id;
where条件最好不要放在最后,弄个中间子查询放进去,减少数据分发。
关联三个表,通常就是生成三个mapreduce,一个表一个。
hive的执行计划和oracle不太一样,oracle会生成一个cost,通过这个cost可以知道脚本的执行性能,但hive里面没有这个东西。
优化的话就是mapreduce的数量越少越好
比较两个表关联:
select pt.page_id,count(t.url) PV
from rpt_page_type pt
join
(select url_page_id,url from trackinfo where ds='2013-10-11') t
on pt.page_id=t.url_page_id
group by pt.page_id
上面两个select有很多地方相同,利用这个特性,可以把相同join on条件的放在一个job处理
当然很多情况下要看怎么做,但是要有这个意识,很多表关联时,把相同的条件抽出来
在小表关联大表时,如果join的条件不相同,如:
insert overwrite table page_pv
select pt.page_id,count(t.url) PV
from rpt_page_type pt
join
(select url_page_id,url,province_id from trackinfo where ds='2013-10-11') t
on pt.page_id=t.url_page_id
join
(select page_id,province_id from rpt_page_kpi_new where ds='2013-10-11') r
on t.province_id=r.province_id
group by pt.page_id
大表join小表
访户未登录时,日志中userid是空,在用user_id进行hash分桶时,会将日志中userid为空的数据分到一起,导致了过大空key造成倾斜。
解决办法:
由于相同的key只会分发到一个reduce去处理,所以就可以把空值的key变成一个字符串加上一个随机数,把倾斜的数据分到不同的reduce商,由于null值关联不上,处理后并不影响最终结果。
案例:
End_user 5000万,纬度表
Trackinfo 每天两个亿,按天增量表
原写法:
select u.id,url,t.track_time
from end_user u
join
(select end_user_id,url,track_time from trackinfo where ds='2013-12-01')t
on u.id=t.end_user_id limit 2;
其中end_user_id是很多为空的
调整为:
select u.id,url,track_time
from end_user u
join
{select case when end_user_id='null' or end_user_id is null
then cast (concat('00000000',floor(rand()*1000000)) as bigint)
else end_user_id end end_user_id,
url,track_time
from trackinfo where ds='2013-12-01') t
on u.id=t.end_user_id limit 2;
实验发现时间是原时间的一半。也就是说性能提高了一倍。
MapJoin:
join操作在map阶段完成,如果需要的数据在map的过程中可以访问则不再需要reduce
小表关联一个超大表时,容易发生数据倾斜,可以用MapJoin把小表全部加载到内存,在map端进行join,避免reduce处理
如:
insert overwrite table page_pv
select /*+ MAPJOIN(pt)*/
pt.page_id,count(t.url) PV
from rpt_page_type pt
join
(select url_page_id,url from trackinfo where ds='2013-10-11') t
on pt.page_id=t.url_page_id;
这个小表如果是25MB,25000行,放到内存中是比较合适的。
如果是小表,能否自动选择mapjoin?
set hive.auto.convert.join = true;默认为false
该参数为true时,hive自动对左边的表统计量,如果是小表则加入内存中,也就是对小表进行map join
大表小表的阈值:
set hive.mapjoin.smalltable.filesize;
hive.mapjoin.smalltable.filesize=25000000
默认是25MB
hive.mapjoin.cache.numrows
说明:mapjoin存放在内存里的数据量,也就是行数
默认是25000
hive.mapjoin.followby.gby.localtask.max.memory.usage
说明:map join做group by 操作时,可以使用多大的内存来存储数据,如果数据量太大,则不会保存在内存里
默认值:0.53
hive.mapjoin.localtask.max.memory.usage
说明:本地任务可以使用内存的百分比
默认值为0.90
group by:
map端部分聚合:
并不是所有的聚合操作都需要在reduce端完成,很多聚合操作都可以现在map端进行聚合,最后在reduce端得出最终结果。
基于hash
参数包括:
hive.map.aggr = true就是说也会在map端进行部分聚合,默认为true
hive.groupby.mapaggr.checkinterval = 100000在map端进行聚合操作的条目数量,也就是行数
默认情况下,map端同一个key数据分发给一个reduce,当一个key数量过大时就会倾斜了。数据倾斜大多数情况就是map阶段很快,reduce阶段很慢。
有数据倾斜的时候进行负载均衡
hive.groupby.skewindata = false
当选项设定为true,生成的查询计划会有两个MR job,第一个MR job中,Map的输出结果会随机分布到Reduce中,每个Reduce做部分聚合操作,并输出结果,这样处理的结果是相同的group by key有可能被分发到不同的reduce中,从而达到负载均衡的目的,第二个MR job再根据预处理的数据结果按照Group by key分不到reduce中,(这个过程可以保证相同的group by key被分布到同一个reduce中,最后完成最终的绝活操作。
Count(distinct)也是容易数据倾斜,当该字段存在大量值为null或空的记录
原因就是执行计划也是按照key去进行分发
解决思路:
count distinct时,将值为空的数据在where里过滤掉,在最后结果加1
如:
count(distinct end_user_id) as user_num
修正为:
cast(count(distinct end_user_id)+1 as bigint) as user_num
where end_user_id is not null and query<>