真正让你明白Hive调优系列3：笛卡尔乘积,小表join大表，Mapjoin等问题

超、凢脫俗 2023-02-22 12:30 190阅读 0赞

## 0.Hive中的优化分类 ##

**   真正想要掌握Hive的优化，要熟悉相关的MapReduce，Yarn，hdfs底层源码，明晰Hive的底层执行流程。真正让你明白Hive调优系列，会征对下面分类逐一分析演示。**

### 大类1：参数优化 ###

1.  **文件输入前看是否需要map前合并小文件**
2.  **控制map个数，根据实际需求确认每个map的数据处理量，split的参数等**
3.  **Map输出是否需要启动压缩，减少网络传输，OOM处理等**
4.  **控制redcue个数，控制每个reduce的吞吐量，OOM处理等**
5.  **是否将common-join转换成map-join处理策略**
6.  **文件输出是否需要启动小文件合并策略**
7.  **其他相关参数的配置：如严格模式，JVM重用，列剪切等**

### 大类2：开发中优化 ###

1.  **数据倾斜，这个是Hive优化的重头戏。出现的原因是因为出现了数据的重新分发和分布，启动了redcue。Hive中数据倾斜分类：group by ，count(distinct)以及join产生的数据倾斜（当然一些窗口函数中用了partition by一会造成数据倾斜）**
2.  j**oin相关的优化**：分类大表join大表，小表join大表的优化
3.  **代码细节优化分类**：**比如去重用group by替代distinct ；**多表关联，先进行子查询后再进行关联**；表关联时一定要在子查询里过滤掉NULL值，避免数据倾斜；**不要对一个表进行重复处理，多使用临时表，尽量做到一次处理多次使用等等，

### 1.笛卡尔乘积与小表join大表 ###

Hive 设定为严格模式（hive.mapred.mode=strict）时，不允许在 HQL 语句中出现笛卡尔积， 这实际说明了 Hive 对笛卡尔积支持较弱。因为找不到 Join key，Hive 只能使用 1 个 reducer 来完成笛卡尔积。

**需求1**：**一个小表join大表**，且两个表特殊的是笛卡尔乘积（on true/on 1=1）。小表的数据量2Mb，大表的数据是4Gb左右。实际开发中该段代码跑了3个小时左右

drop table if exists FDM_TMP.TMP_FSA_MULTI_PATH_FUNL_ANALYSE_RSLT_D_21_${hivevar:statis_date};
    CREATE TABLE IF NOT EXISTS FDM_TMP.TMP_FSA_MULTI_PATH_FUNL_ANALYSE_RSLT_D_21_${hivevar:statis_date}
    stored as rcfile as
    SELECT T1.ACCT_NO                                              
           ,T1.PAGE_ID                                                 
           ,T1.PAGE_NAME                                               
           ,T1.PAGE_URL                 
           ,T1.TRMNL_TYPE                                              
           ,T1.DEV_ID 
           ,T0.PATH_ID                                               
           ,T0.UBA_HRCHY        
           ,T0.UBA_HRCHY_LO     
           ,T0.TRANS_CYCLE
           ,T0.TRANS_RATE_CALC
           ,T0.CUS_GROUP_NO
           ,T1.SYS_TYPE
      from fdm_tmp.tmp_fsa_multi_path_funl_analyse_rslt_d_01_${hivevar:statis_date} t0   ---小表大概2Mb左右
     inner join FDM_DPA.FSA_MULTI_PATH_FUNL_VISIT_URL_HIS_D t1           ----大表大概3.4G
        on 1=1                                             ----------笛卡尔乘积
       and t0.comp_cond_type='10010201'  --等于
       and t0.path_cond_type = '60020204'         --页面名称
       and t0.UBA_HRCHY= '1' --第一层
     where t1.stat_date<='${statisdate}'
       and t1.stat_date>=t0.trans_cycle  --已将转换周期转换成对应的起始日期
       and (t0.Page_Name = t1.page_name  or t1.page_id =t0.page_name)
     group by t1.acct_no           
              ,t1.Page_ID          
              ,t1.Page_Name        
              ,t1.page_url         
              ,t1.TRMNL_TYPE           
              ,t1.Dev_ID 
              ,t0.path_id              
              ,t0.UBA_HRCHY      
              ,t0.UBA_HRCHY_LO 
              ,t0.trans_cycle
              ,t0.trans_rate_calc
              ,T0.CUS_GROUP_NO
              ,t1.SYS_TYPE
    ;

**优化使用：配置如下参数，使用mapjoin替代common join.当然这里因为group by的原因还是会启动reduce进行去重。但是整体从4个小时优化到1.5小时。一般来说小表join大表一般配置下面四个参数就差不多，当然官方还提供了其他的参数共配置。[Hive官网参数配置][Hive]**

1.  **set hive.auto.convert.join = true ; **  \-- hive是否自动根据文件量大小，选择将common join转成map join 。hive 0.10 版本后的默认值 true。
2.  **set  hive.mapjoin.smalltable.filesize =25000000 ；**大表小表判断的阈值，如果表的大小小于该值25Mb，则会被判定为小表。则会被加载到内存中运行，将commonjoin转化成mapjoin。一般这个值也就最多几百兆的样子。
3.  **set  hive.auto.convert.join.noconditionaltask = true; ** 翻译官网的解释：是否启用基于输入文件的大小，将普通连接转化为Map连接的优化机制。假设参与连接的表(或分区)有N个，如果打开这个参数，并且有N-1个表(或分区)的大小总和小于hive.auto.convert.join.noconditionaltask.size参数指定的值，那么会直接将连接转为Map连接。**（说人话：默认值：true，当将普通的join转化为普通的mapjoin时，是否将多个mapjoin转化为一个mapjoin，主要针对多个小表join大表的情形）**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NDQyNTUz_size_16_color_FFFFFF_t_70][]
4.  **set  hive.auto.convert.join.noconditionaltask.size =10000000;** 翻译官网：如果hive.auto.convert.join.noconditionaltask是关闭的，则本参数不起作用。否则，如果参与连接的N个表(或分区)中的N-1个 的总大小小于这个参数的值，则直接将连接转为Map连接。默认值为10MB。**（说人话：将多个mapjoin转化为一个mapjoin时，其小表总和的最大值，所以这个条件比单独启动一个mapjon的参数****set  hive.mapjoin.smalltable.filesize更加严格。****）**

### **尖叫提示：**  ###

**1.一般遇到小表join大表，不管是多少个小表，把小表写在前面，开启mapjon，同时适当地调大上面的参数,Mapjoin几乎是解决小表join大表（包括笛卡尔乘积）的最好方式。尤其对于笛卡尔乘积的小表join大表来说，性能差别天壤之别。**

**2.所谓的mapjoin优化就是在Map阶段完成join工作，而不是像通常的common join在Reduce阶段按照join的列值进行分发数据到每个Reduce上进行join工作。前面我们知道，没有数据分发分布也就不会有数据倾斜的存在。实际上所谓的mapjoin并不是像有些人说的那样只是将小表加载到内存然后跟大表join那么简单，如果那样照样会有reduce的产生，也不会快那么多。而是会将所有的小表全量复制到每个map任务节点，然后再将小表缓存在每个map节点的内存里与大表进行join工作。所以这解释了为啥小表的大小的不能太大的原因，否则复制分发太多反而得不偿失。一般这个值也就几百兆吧。像我们公司每个map的分配的内存才2G，堆内存才1.5G，你要是搞个1个G的小表,直接很容易OOM报错了。**

**3.****在0.7.0版本之前：需要在sql中使用 /\*+ MAPJOIN(smallTable) \*/ 来开启mapjoin，而后则Hive会自动通过配置的参数来判断是否开启mapjoin。**

**4.对于小表join大表的笛卡尔乘积，还可以通过规避的方法避免：具体比如给 Join的两个表都增加一列Join key**，**原理很简单：将小表扩充一列join key，并将小表的总数复制数倍，join ****key 各不相同，比如第一次为1，复制一次joinkey为2，依次类推；将大表扩充一列join key 为随机数，这个随机数为小表里的joinkey的随机值，如1-5的随机值。这样就实现了将一个大表拆分几分同时处理，而且这样小表扩充了几倍，大表就被对应地分成几份处理。这种方式也可以提高笛卡尔乘积小表join大表的性能。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NDQyNTUz_size_16_color_FFFFFF_t_70 1][]

## 2.笛卡尔乘积：大表join大表 ##

**       大表join大表一般调优有四种方式具体参考其他博客，但是对于笛卡尔乘积来说，如果是小join大，开启mapjoin性能还不算太差，但要是大join大的笛卡尔乘积那是真可怕。**

**1.首先要尽量避免笛卡尔乘积，比如HQL无法支持循环，遍历等缺陷，这种情况遇到笛卡尔乘积的可以考虑用spark来替代，或者用UDF来解决，这是首选方案，其他几乎没有更好的处理方案了。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NDQyNTUz_size_16_color_FFFFFF_t_70 2][]

[Hive]: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NDQyNTUz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200704193816737.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NDQyNTUz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NDQyNTUz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200704201112406.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NDQyNTUz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NDQyNTUz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200704203809453.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NDQyNTUz,size_16,color_FFFFFF,t_70