Join:
1、小表join大表
将key相对分散,并且数据量小的表放在join的左边,这样可以有效减少内存溢出错误发生的几率,再进一步可以使用group 让小的维表(1000条以下的记录条数)先进内存,在map端完成reduce。
select count(distinct s_id) from score;
select count(s_id) from score group by s_id; --在map端进行聚合,效率更高
2、多个表关联
多个表关联时,最好拆分成小段,避免大sql(无法控制中间Job)
3、大表Join大表
3.1、空key过滤
有时join超时是因为某些key对应的数据太多,而相同key对应的数据都会发送到相同的reducer上,从而导致内存不够。此时我们应该仔细分析这些异常的key,很多情况下,这些key对应的数据是异常数据,我们需要在SQL语句中进行过滤。例如key对应的字段为空,操作如下:
不过滤:
INSERT OVERWRITE TABLE jointable
SELECT a.* FROM nullidtable a JOIN ori b ON a.id = b.id;
结果:
No rows affected (152.135 seconds)
-----------------------------------------------
过滤:
INSERT OVERWRITE TABLE jointable
SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id = b.id;
结果:
No rows affected (141.585 seconds)
3.2、空key转换
有时虽然某个key为空对应的数据很多,但是相应的数据不是异常数据,必须要包含在join的结果中,此时我们可以表a中key为空的字段赋一个随机的值,使得数据随机均匀地分不到不同的reducer上。例如:
3.2.1、不随机分布:
set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;
INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON
CASE WHEN
a.id IS NULL
THEN 'hive'
ELSE a.id
END
= b.id;
--No rows affected (52.477 seconds)
结果:这样的后果就是所有为null值的id全部都变成了相同的字符串“hive”,及其容易造成数据的倾斜(所有的key相同,相同key的数据会到同一个reduce当中去) 为了解决这种情况,我们可以通过hive的rand函数,随记的给每一个为空的id赋上一个随机值,这样就不会造成数据倾斜
3.2.2、随机分布:
set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;
INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON
CASE WHEN
id IS NULL
THEN concat('hive', rand())
ELSE a.id
END
= b.id;
--No rows affected (42.594 seconds)
实际操作:
操作示例:测试大表JOIN小表和小表JOIN大表的效率 (新的版本当中已经没有区别了,旧的版本当中需要使用小表)
1、关闭mapjoin功能(默认是打开的)
set hive.auto.convert.join = false;
2、执行小表JOIN大表语句
INSERT OVERWRITE TABLE jointable2
SELECT b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
FROM smalltable s
left JOIN bigtable b
ON b.id = s.id;
--Time taken: 67.411 seconds
3、执行大表JOIN小表语句
INSERT OVERWRITE TABLE jointable2
SELECT b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
FROM bigtable b
left JOIN smalltable s
ON s.id = b.id;
--Time taken: 69.376seconds
可以看出大表join小表或者小表join大表,就算是关闭map端join的情况下,在新的版本当中基本上没有区别了(hive为了解决数据倾斜的问题,会自动进行过滤)
MapJoin
如果不指定MapJoin或者不符合MapJoin的条件,那么Hive解析器会将Join操作转换成Common Join(在Reduce阶段完成join)。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join,避免reducer处理。
首先是Task A,它是一个Local Task(在客户端本地执行的Task),负责扫描小表b的数据,将其转换成一个HashTable的数据结构,并写入本地的文件中,之后将该文件加载到DistributeCache中。
接下来是Task B,该任务是一个没有Reduce的MR,启动MapTasks扫描大表a,在Map阶段,根据a的每一条记录去和DistributeCache中b表对应的HashTable关联,并直接输出结果。
由于MapJoin没有Reduce,所以由Map直接输出结果文件,有多少个Map Task,就有多少个结果文件。
案例实操:
1、开启Mapjoin功能
set hive.auto.convert.join = true; 默认为true
2、执行小表JOIN大表语句
INSERT OVERWRITE TABLE jointable2
SELECT b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
FROM smalltable s
JOIN bigtable b
ON s.id = b.id;
--Time taken: 31.814 seconds
3、执行大表JOIN小表语句文章来源:https://www.toymoban.com/news/detail-765323.html
INSERT OVERWRITE TABLE jointable2
SELECT b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
FROM bigtable b
JOIN smalltable s
ON s.id = b.id;
--Time taken: 28.46 seconds
参考原文链接:
原文链接:https://blog.csdn.net/weixin_43563705/article/details/103297070文章来源地址https://www.toymoban.com/news/detail-765323.html
到了这里,关于Hive调优之小表Join大表的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!