表的优化
- 小表大表Join(MapJOIN)
- 案例实操
- 大表 Join 大表
- 空 KEY 过滤
- 空 key 转换
小表大表Join(MapJOIN)
将 key 相对分散，并且数据量小的表放在 join 的左边，可以使用 map join 让小的维度表先进内存。在 map端完成 join。
实际测试发现：新版的 hive 已经对小表 JOIN 大表和大表 JOIN 小表进行了优化。小表放在左边和右边已经没有区别。
案例实操
1）需求介绍
测试大表 JOIN 小表和小表 JOIN 大表的效率
2）开启 MapJoin 参数设置
（1）设置自动选择 Mapjoin
set hive.auto.convert.join = true; 默认为 true（2）大表小表的阈值设置（默认 25M 以下认为是小表）：
set hive.mapjoin.smalltable.filesize = 25000000;3）MapJoin 工作机制
4）建大表、小表和 JOIN 后表的语句
// 创建大表
create table bigtable(id bigint, t bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
// 创建小表
create table smalltable(id bigint, t bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
// 创建 join 后表的语句
create table jointable(id bigint, t bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';5）分别向大表和小表中导入数据
hive (default)> load data local inpath '/opt/module/data/bigtable' into table bigtable;
hive (default)>load data local inpath '/opt/module/data/smalltable' into table smalltable;6）小表 JOIN 大表语句
insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from smalltable s
join bigtable b on b.id = s.id;7）大表 JOIN 小表语句
insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from bigtable b
join smalltable s on s.id = b.id;Mapjoin:MapJoin顾名思义，就是在Map阶段进行表之间的连接。而不需要进入到Reduce阶段才进行连接。这样就节省了在Shuffle阶段时要进行的大量数据传输。从而起到了优化作业的作用。
总结:小表join大表的时候,可以通过开启mapjoin,在map端进行join,节省在Shuffle阶段时要进行的大量数据传输。
大表 Join 大表
空 KEY 过滤
有时 join 超时是因为某些 key 对应的数据太多，而相同 key 对应的数据都会发送到相同 的 reducer 上，从而导致内存不够。此时我们应该仔细分析这些异常的 key，很多情况下， 这些 key 对应的数据是异常数据，我们需要在 SQL 语句中进行过滤。例如 key 对应的字段为 空，操作如下：
案例实操
（1）配置历史服务器配置 mapred-site.xml
<property>
<name>mapreduce.jobhistory.address</name>
<value>hadoop102:10020</value>
</property>
<property>
<name>mapreduce.jobhistory.webapp.address</name>
<value>hadoop102:19888</value>
</property>启动历史服务器
sbin/mr-jobhistory-daemon.sh start historyserver查看 jobhistory
http://hadoop102:19888/jobhistory（2）创建原始数据空 id 表
// 创建空 id 表
create table nullidtable(id bigint, t bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';（3）分别加载原始数据和空 id 数据到对应表中
hive (default)> load data local inpath '/opt/module/data/nullid' into table nullidtable;（4）测试不过滤空 id
hive (default)> insert overwrite table jointable select n.* from nullidtable n left join bigtable o on n.id = o.id;（5）测试过滤空 id
hive (default)> insert overwrite table jointable select n.* from (select
* from nullidtable where id is not null) n left join bigtable o on n.id = o.id;总结:大表join小表的话,可以判断大表里面的key为null的数据是否是有用的数据,如果是没用的数据,那么就先从大表里面去选取id不为null的数据,把id为null的数据都过滤掉,然后再去做join操作,就能够省了在map端对大量key为null的数据的处理。
空 key 转换
有时虽然某个 key 为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在 join 的结果中，此时我们可以表 a 中 key 为空的字段赋一个随机的值，使得数据随机均匀地 分不到不同的 reducer 上。例如：
（1）设置 5 个 reduce 个数
set mapreduce.job.reduces = 5;（2）JOIN 两张表
insert overwrite table jointable
select n.* from nullidtable n left join bigtable b on n.id = b.id;结果：如下图所示，可以看出来，出现了数据倾斜，某些 reducer 的资源消耗远大于其 他 reducer。
随机分布空 null 值
（1）设置 5 个 reduce 个数
set mapreduce.job.reduces = 5;（2）JOIN 两张表
insert overwrite table jointable
select n.* from nullidtable n full join bigtable o on nvl(n.id,rand()) = o.id;总结:一张表里面的数据,key为null的数据过多,那么具有相同key的数据都会发送到同一个reducer上面,从而导致内存不够。
大表join小表的时候:
- 这些key为null的数据是不需要的:
如果这些key所对应的value值是我们不需要的话,我们就要将他们过滤掉,通过where条件去筛选id is not null的数据,然后再去做join的操作,将数据insert overwrite 写进表里面
- 这些key为null的数据是需要的:
这些数据如果是是需要的话,为了防止出现数据倾斜,而刚好一张表里面有很多的id为null,那么我们可以通过nvl(id,rand())函数去判断,如果id为null的话,就赋值一个随机值,如果id不为null的话,正常计算,放到所对应的reducer里面。
