Hive SQL调优方法总结

朱雀 2024-04-20 08:11 65阅读 0赞

**使用分区剪裁、列剪裁**

*  在 select 中，尽量只拿需要的列，尽量使用分区过滤，少使用 select \*
 *  另外在分区裁剪中，当使用外关联时，副表的过滤条件如果使用 where 就会导致先全表关联在过滤，应该改用 on，或者之间使用子查询

## 少用 count（distinct） ##

*  count（distinct）是由一个 reduce task 来完成的，这一个 reduce 需要处理的数据量太大，就会导致整个 job 很难完成。
 *  count（distinct）可以使用**先 group by 再 count** 的方式来替换

## 多对多的关联 ##

*  如果存在多对多关联，起码要保证有一个表或者结果集的关联键不重复
 *  如果某一个关联键的记录数非常多，那么分配到该 reduce task 的数据量将非常大，会导致整个 job 很难完成
 *  避免笛卡尔集

## 合理使用 MapJoin ##

*  首先这里先介绍一下 hive 里的两种 join

1.  **Hive Common Join**：如果不主动指定 MapJoin 或者不符合 MapJoin 的条件，Hive 解析器默认的 Join 操作就是 Common Join，即在 Reduce 阶段完成 Join。过程如下
    
     *  Map 阶段 ：以关联键的组合为 key，value 为 join 之后关心的列，按照 key 排序，value 中会包含表的 tag 信息，用于标明此 value 属于哪个表
     *  Shuffle 阶段：根据 key 的值进行 hash，并将 key/value 按照 hash 值推送至不同的 reduce 中
     *  reduce 阶段：根据 key 的值完成 join 操作，期间通过 tag 来识别不同表中的数据
2.  **Hive Map Join**：MapJoin 通常用于一个很小的表和一个大表进行 join 的场景。过程如下：
    
     *  首先是一个 Local task，我们暂称为 taskA，它负责扫描小表 b 的数据，将其转化为一个 hashtable 的结构，并写入本地文件，之后将该文件加载到 DistributeCache 中，该 HashTable 的数据结构可以抽象为下面的表。
     *  接下来是一个没有 reduce 的 MR，我们暂称之为 taskB, 它启动 MapTasks 扫描大表 a，在 Map 阶段根据 a 的每一条记录去和 DistributeCache 中 b 表对应的 HashTable 关联，并直接输出结果。
     *  由于 MapJoin 没有 Reduce，所以 Map 直接输出结果文件，有多少 map，就有多少结果文件

<table> 
 <thead> 
  <tr> 
   <th>key</th> 
   <th>value</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>26</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>34</td> 
  </tr> 
 </tbody> 
</table>

## 合理使用 Union ALL ##

*  对同一张表的 union all 要比多重 insert 快得多。
 *  原因是 hive 本身对这种 union all 做过优化，即只扫描依次源表，多重 insert 虽然也只扫描一次，但因为要 insert 到多个分区，所以做了很多其他的事情，导致消耗的时间非常长

## 并行执行 job ##

*  并行执行可以加快任务的执行速率，但不会减少其占用的资源
 *  在系统资源比较空闲的时候才有优势，否则并行不起来

## 使用本地 MR ##

*  如果 Hive 中运行的 sql 本身数据量很小，那么使用本地 MR 的效率要比提交到 Hadoop 集群中运行快很多

## 合理使用动态分区 ##

*  动态分区可以优化诸如需要往指定分区插入数据的这种操作。
 *  配置参数
    
     *  hive.exec.dynamic.partition：是否开启动态分区，默认为 false，设置成 true
     *  hive.exec.dynamic.partition.mode：默认值表示必须指定至少一个静态分区，默认为 strict，设置成 nonstrict
     *  hive.exec.max.dynamic.partitions.pernode：在每个执行 MR 的节点上，最大可以创建多少个动态分区，默认 100，按实际情况来定
     *  hive.exec.max.created.files：整个 MR Job 中，最大可以创建多少个 HDFS 文件，默认值：100000，一般默认值足够了，除非你的数据量非常大，需要创建的文件数大于 100000，可根据实际情况加以调整。
     *  hive.error.on.empty.partition：当有空分区生成时，是否抛出异常，默认值：false，一般不需要设置。

## 避免数据倾斜 ##

*  数据倾斜的症状：
    
     *  任务进度长时间维持在 99%（或 100%）;
     *  查看任务监控页面，发现只有少量（1 个或几个）reduce 子任务未完成。
     *  本地读写数据量很大。
 *  导致数据倾斜的操作：GROUP BY, COUNTDISTINCT, join
 *  数据倾斜产生的原因：key 分布不均匀，业务数据本身特点
 *  常用的数据倾斜解决办法：
    
     *  使用 COUNT DISTINCT 和 GROUP BY 造成的数据倾斜：存在大量空值或 NULL，或者某一个值的记录特别多，可以先把该值过滤掉，在最后单独处理:
     *  多重 COUNT DISTINCT：通常使用 UNION ALL +ROW\_NUMBER() + SUM + GROUP BY 来变通实现。
     *  使用 JOIN 引起的数据倾斜：关联键存在大量空值或者某一特殊值，如”NULL”，可以空值单独处理，不参与关联，或者空值或特殊值加随机数作为关联键；不同数据类型的字段关联，则可以转换为同一数据类型之后再做关联

## 控制 Map 数和 Reduce 数 ##

*  首先是控制 Map 数：根据实际情况来进行控制 map 的数量，主要是要遵循两个原则：1. 使大数据量利用合适的 map 的数；2. 使单个 map 任务处理合适的数据量。
    
     *  是不是 map 越多越好：答案是否定的。如果一个任务有很多小文件（远远小于块大小 128m）, 则每个小文件也会被当做一个块，用一个 map 任务来完成，而一个 map 任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的 map 数是受限的
     *  是不是保证每个 map 处理接近 128m 的文件块，就高枕无忧了？：答案也是不一定。比如有一个 127m 的文件，正常会用一个 map 去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果 map 处理的逻辑比较复杂，用一个 map 任务去做，肯定也比较耗时。
 *  控制 Hive 任务的 reduce 数：同样的，在设置 reduce 个数的时候也需要考虑这两个原则：使大数据量利用合适的 reduce 数；使单个 reduce 任务处理合适的数据量；
    
     *  Hive 自己如何确定 reduce 数：reduce 个数的设定极大影响任务执行效率，不指定 reduce 个数的情况下，Hive 会猜测确定一个 reduce 个数，基于以下两个设定：
        
         *  hive.exec.reducers.bytes.per.reducer（每个 reduce 任务处理的数据量，默认为 1000^3=1G）
         *  hive.exec.reducers.max（每个任务最大的 reduce 数，默认为 999）
     *  调整 reduce 个数方法就在于调整合理的参数
     *  reduce 个数并不是越多越好：同 map 一样，启动和初始化 reduce 也会消耗时间和资源；另外，有多少个 reduce, 就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
     *  什么情况下只有一个 reduce：没有 group by 的汇总、用了 Order by、有笛卡尔积

## 中间结果压缩 ##

*  中间结果的压缩：建议采用 lzo，因为它速度比较快，不像其他压缩方式比较耗 CPU

## 其他 ##

*  了解 HQL ->MapReduce 的过程；
 *  HQL 优化其实也是 MapReduce 的优化，作为分布式计算模型，其最核心的地方就是要确保每个节点上分布的数据均匀，才能最大程度发挥它的威力，否则，某一个不均匀的节点就会拖后腿。