Hive优化实践2-大表join小表优化

4、大表join小表优化
　　　　　　和join相关的优化主要分为mapjoin可以解决的优化（即大表join小表）和mapjoin无法解决的优化（即大表join大表），前者相对容易解决，后者较难，比较麻烦。

首先介绍大表join小表优化。以销售明细表为例来说明大表join小表的场景。

假如供应商进行评级，比如（五星、四星、三星、二星、一星），此时因为人员希望能够分析各供应商星级的每天销售情况及其占比。

开发人员一般会写出如下SQL：

select seller_star, count(order_id) as order_cnt

from

(select order_id,seller_id from sales_detail_table where partition_value='20181010' ) a

left outer join

( select seller_id, seller_start from dim_seller where partition_value =''20181010' ) b

on a.seller_id = b.seller_id

group by b.seller_star;

现实世界的二八准则将导致订单集中在部分供应商上，而好的供应商的评级通常会更高，此时更加加剧了数据倾斜的程度，如果不加以优化，上面SQL将会耗费很长时间，，甚至运行不出结果。

通常来说，供应商是有限的，比如上千家，上万家，数量不会很大，而销售明细表比较大，这就是典型的大表join小表的问题，可以通过mapjoin的方式来优化，只需要添加mapjoin hint即可，

优化后的SQL如下：

select /+mapjoin(b)/

seller_star, count(order_id) as order_cnt

from

(select order_id,seller_id from sales_detail_table where partition_value='20181010' ) a

left outer join

( select seller_id, seller_start from dim_seller where partition_value =''20181010' ) b

on a.seller_id = b.seller_id

group by b.seller_star;

/+mapjoin(b)/ 即是mapjoin hint,如果需要多个mapjoin多个表，则格式为：/+mapjoin(b,c,d)/.。 Hive对于mapjoin是默认开启的，设置参数为：

Set hive.auto.convert.join = true;

mapjoin优化是在Map阶段进行join，而不是通常那样在Reduce阶段按照join列进行分发后在每个Reduce节点上进行join，不需要分发也就没有倾斜的问题，相反，Hive会将小表

全量复制到每个Map任务节点（对于本例是dim_seller表，当然进全量复制b表 sql指定的列），然后每个Map任务节点执行lookup小表即可。

从上面的分析可以看出，小表不能太大，否则全量复制分发得不偿失，实际上Hive根据参数hive.mapjoin.smalltable.size(0.11.0版本后是hive.auto.convert.join.nonconditionaltask.size) 来确定小表的

大小是否满足条件（默认25MB），实际中此参数允许的最大值可以修改，但是一般最大不能超过1GB（太大的话Map任务所在的节点内存会撑爆，Hive会报错。另外需要注意的是，HDFS显示的文件

大小是压缩后的大小，当实际加载到内存的时候，容量会增大很多，很多场景下会膨胀10倍）。