mapjoin,bucket mapjoin,SMB join测试

1、造一个1千万条数据的表备用

先借用sparksql界面运行，不要用hive运行。因为sparksql支持sequence函数，hive不支持。

use test_db;
set spark.sql.shuffle.partitions=100;

drop table if exists test_1kw;
create table test_1kw as
--造1千万条数
select
       id,
       ceil(1000*rand()) x
 from
(select explode(sequence(id+1,id+10000)) id from
(select  explode(sequence(0,9990000,10000)) id));

2、后面全部切换到hive，不需用SparkSQL了。造1千万的大分桶表。大约100M

drop table if exists test_big;
create table test_big(id int, name string)
clustered by(id) sorted by (id asc) into 100 buckets
row format delimited fields terminated by 't';

insert overwrite table test_big
select * from test_1kw;

3、造10万条数据的小表,约1M

create table test_small as
select * from test_1kw where id <=100000;

4、对比一个不用mapjoin，一个用mapjoin，去8088分别看耗时

--取消mapjoin，耗时4分17秒,8088页面看到有2个map，1个map处理大表，1个map处理小表，每个map顺序加载表下的所有文件块。
set hive.auto.convert.join=false;
drop table if exists test_nomapjoin;
create table test_nomapjoin as
select a.id,a.name,b.id id2,b.name name2 from test_big a join test_small b on a.id = b.id;

select count(1) from test_nomapjoin;

--启用mapjoin，耗时1分2秒,8088页面看到只有1个map，只处理test_big大表，顺序加载大表下的所有文件块。没看到map处理test_small小表。。没看到reduce。
set hive.auto.convert.join=true;
--预定义50M算小表
set hive.auto.convert.join.noconditionaltask.size=51200000;
--需要分析表的大小，然后通过desc formatted 的totalsize显示是不是小表。
ANALYZE TABLE test_big compute statistics noscan ;
ANALYZE TABLE test_small compute statistics noscan ;
desc formatted test_big;
desc formatted test_small;

drop table if exists test_mapjoin;
create table test_mapjoin as
select a.id,a.name,b.id id2,b.name name2 from test_big a join test_small b on a.id = b.id;

如果要测Spark的

那么将set hive.auto.convert.join.noconditionaltask.size=51200000;

换成spark.sql.autoBroadcastJoinThreshold=51200000;

其他语句复制到Spark引擎跑即可

1、造一个1千万条数据的表备用

先借用sparksql界面运行，不要用hive运行。因为sparksql支持sequence函数，hive不支持。

use test_db;
set spark.sql.shuffle.partitions=100;

drop table if exists test_1kw;
create table test_1kw as
--造1千万条数
select
       id,
       ceil(1000*rand()) x
 from
(select explode(sequence(id+1,id+10000)) id from
(select  explode(sequence(0,9990000,10000)) id));

2、后面全部切换到hive，不需用SparkSQL了。造1千万的大分桶表。大约100M

drop table if exists test_big;
create table test_big(id int, name string)
clustered by(id) sorted by (id asc) into 100 buckets
row format delimited fields terminated by 't';

insert overwrite table test_big
select * from test_1kw;

3、造10万条数据的小表,约1M

create table test_small as
select * from test_1kw where id <=100000;

4、对比一个不用mapjoin，一个用mapjoin，去8088分别看耗时

--取消mapjoin，耗时4分17秒,8088页面看到有2个map，1个map处理大表，1个map处理小表，每个map顺序加载表下的所有文件块。
set hive.auto.convert.join=false;
drop table if exists test_nomapjoin;
create table test_nomapjoin as
select a.id,a.name,b.id id2,b.name name2 from test_big a join test_small b on a.id = b.id;

select count(1) from test_nomapjoin;

--启用mapjoin，耗时1分2秒,8088页面看到只有1个map，只处理test_big大表，顺序加载大表下的所有文件块。没看到map处理test_small小表。。没看到reduce。
set hive.auto.convert.join=true;
--预定义50M算小表
set hive.auto.convert.join.noconditionaltask.size=51200000;
--需要分析表的大小，然后通过desc formatted 的totalsize显示是不是小表。
ANALYZE TABLE test_big compute statistics noscan ;
ANALYZE TABLE test_small compute statistics noscan ;
desc formatted test_big;
desc formatted test_small;

drop table if exists test_mapjoin;
create table test_mapjoin as
select a.id,a.name,b.id id2,b.name name2 from test_big a join test_small b on a.id = b.id;

如果要测Spark的，复制到Spark引擎跑即可

--让test_big 和 test_big2 的分桶数都是100，都是大表。
create table test_big2(id int, name string)
clustered by(id) sorted by (id asc) into 100 buckets
row format delimited fields terminated by 't';

insert overwrite table test_big2 select * from test_big;

--4分20秒 开启SMB map join。8088看到100个map，0个reduce
set hive.auto.convert.join=true;
set hive.optimize.bucketmapjoin = true;  -- 开启 bucket map join
set hive.auto.convert.sortmerge.join=true; -- 开启 SBM join支持
set hive.optimize.bucketmapjoin.sortedmerge = true;  -- 自动尝试开启 SMB join
set hive.enforce.sorting=true;  -- 开启强制排序
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.auto.convert.join.noconditionaltask = true;
set hive.auto.convert.join.noconditionaltask.size = 50000000;
set hive.auto.convert.sortmerge.join.bigtable.selection.policy
    = org.apache.hadoop.hive.ql.optimizer.TableSizebasedBigTableSelectorForAutoSMJ;

drop table if exists test_smb;
create table test_smb as
select  e.id,e.name,h.id id2,h.name name2 from test_big e join test_big2 h on e.id=h.id;
--关闭smb,8088看到200个map，(两表各100)。1个reduce。
--关闭SMBjoin，用MR跑，耗时8分
set hive.auto.convert.join=false;
set hive.optimize.bucketmapjoin = false;  -- 关闭 bucket map join
set hive.auto.convert.sortmerge.join=false; -- 关闭 SBM join支持
set hive.optimize.bucketmapjoin.sortedmerge = false;  -- 自动尝试开启 SMB join
set hive.enforce.sorting=false;  -- 开启强制排序
set hive.auto.convert.join.noconditionaltask = false;
drop table if exists test_nosmb;
create table test_nosmb as
select e.id,e.name,h.id id2,h.name name2 from test_big e join test_big2 h on e.id=h.id;

如果要测Spark的，复制到Spark引擎跑即可