hive join 优化和数据倾斜解决

1、join 倾斜优化

(1) 当大表和小表join出现数据倾斜时，可以将小表缓存至内存，在map端进行join操作，设置如下

hive.auto.convert.join ： 是否自动转换为mapjoin(0.7.0增加参数，默认值false，0.11.0及后版本true)
hive.mapjoin.smalltable.filesize : 小表的最大文件大小，默认为25000000，即25M
hive.auto.convert.join.noconditionaltask ： 是否将多个mapjoin合并为一个
hive.auto.convert.join.noconditionaltask.size ： 多个mapjoin转换为1个时，所有小表的文件大小总和的最大值。

例如，一个大表顺序关联3个小表a(10M), b(8M),c(12M)，如果hive.auto.convert.join.noconditionaltask.size的值：
1. 小于18M，则无法合并mapjoin，必须执行3个mapjoin；
2. 大于18M小于30M，则可以合并a和b表的mapjoin，所以只需要执行2个mapjoin；
3. 大于30M，则可以将3个mapjoin都合并为1个。

(2)如果大表和大表进行join操作，则可采用skewjoin

skewjoin原理

对于skewjoin.key，在执行job时，将它们存入临时的HDFS目录。其它数据正常执行

对倾斜数据开启map join操作，对非倾斜值采取普通join操作

将倾斜数据集和非倾斜数据及进行合并操作 下面是两种方式

hive.optimize.skewjoin.compiletime
    如果建表语句元数据中指定了skew key，则使用set hive.optimize.skewjoin.compiletime=true开启skew join。
    可以通过如下建表语句指定SKEWED key:
      CREATE TABLE xxxx_table_name (key STRING, value STRING)
    SKEWED BY (key) ON (1,5,6) [STORED AS DIRECTORIES];


hive.optimize.skewjoin
该参数为在运行时动态指定数据进行skewjoin，一般和hive.skewjoin.key参数一起使用
  set hive.optimize.skewjoin=true;
  set hive.skewjoin.key=100000;   **（处理的总记录数/reduce个数）的2-4倍都可以接受）**hive 在运行的时候没有办法判断哪个key 会产生多大的倾斜，所以使用这个参数控制倾斜的阈值，如果超过这个值，新的值会发送给那些还没有达到的reduce, 一般可以设置成你

（处理的总记录数/reduce个数）的2-4倍都可以接受
以上参数表示当记录条数超过100000时采用skewjoin操作在这里插入代码片

区别

hive.optimize.skewjoin.compiletime和hive.optimize.skewjoin区别为前者为编译时参数，后者为运行时参数。前者在生成执行计划时根据元数据生成skewjoin，此参数要求倾斜值一定；后者为运行过程中根据数据条数进行skewjoin优化。hive.optimize.skewjoin实际上应该重名为为hive.optimize.skewjoin.runtime参数，考虑兼容性没有进行重命名

写法优化
谓词下推实列

  SELECT t1.id AS t1_id ,
       t2.id AS t2_id ,
       t1.name ,
       t1.age ,
       t2.woke ,
       t2.xuli
FROM
  (SELECT id ,
          age ,
          name
   FROM test_tb1
   WHERE name != 'ximei'
     AND age = '13') t1
LEFT JOIN
  (SELECT id ,
          woke ,
          xuli
   FROM test_tb2
   WHERE woke != 'chenxu'
     AND xuli = 'booshi' ) t2
     on t1.id=t2.id

根据谓词下推规则，等价代码如下:

   SELECT t1.id,
       t2.id ,
       t1.name ,
       t1.age ,
       t2.woke ,
       t2.xuli
FROM test_tb1 t1
LEFT JOIN test_tb2 t2 ON t1.id = t2.id
AND t2.woke != 'chenxu'
AND t2.xuli = 'booshi'
WHERE t1.name != 'ximei'
AND t1.age = '13'

（3）join的key值发生倾斜，key值包含很多空值或是异常值
这种情况可以对异常值赋一个随机值来分散key

如：
select userid , name
from user_info a
join (
select case when userid is null then cast ( rand ( 47 )* 100000 as i nt )
else userid end
from user_read_log
)b on a . userid = b . userid

通过rand函数将为null的值分散到不同的值上，在key值比较就能解决数据倾斜的问题

（4）简单的增加 reduce 并行个数
如：set mapred.reduce.tasks=100；

注：对于异常值如果不需要的话，最好是提前过滤掉，这样计算量可以大大减少

参考：https://blog.csdn.net/wzq6578702/article/details/71807709
参考：https://blog.csdn.net/hellojoy/article/details/82931827