测试:一个4000万和一个5000多万的表Join,关联键数据倾斜,并且笛卡尔积,效果明显。
#建立小表
create table lxw_test1(id int,name string,date_time string) clustered by(id) sorted by(id) into 10 buckets;
#建立大表
create table lxw_test2(id int,name string,date_time string) clustered by(id) sorted by(id) into 5 buckets;
注:两个表关联键为id,需要按id分桶并且做排序,小表的分桶数是大表分桶数的倍数。
#启用桶表
set hive.enforce.bucketing = true;
#往小表中插入4000万条记录
insert overwrite table lxw_test1 select id,name,null from woa_all_user_info_his where pt = '2012-05-28' limit 40000000;
#往大表中插5000多万条记录(woa_all_user_info_his中有5000多万条记录)
insert overwrite table lxw_test2 select id,name,date_time from woa_all_user_info_his where pt = '2012-05-28';
#设置Sort Merge Bucket Map Join的参数
set hive.optimize.bucketmapjoin = true; set hive.optimize.bucketmapjoin.sortedmerge = true; set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
注:此时的状况是Bucket columns==Join Columns==Sort Columns,完全具备具备使用Sort Merge Bucket Map Join的条件。
#查询
select /*+ mapjoin(b) */ count(1) from lxw_test1 a join lxw_test2 b on a.id = b.id
测试结果:
包括insert数据,采用Sort Merge Bucket Map Join的方式耗时10分钟左右。
如果这两个表做普通的join,耗时1个多小时,还跑不完,最后只得Kill掉了!