Hive SQL调优总结

最新推荐文章于 2024-09-07 16:10:52 发布

阳呀么阳阳阳

最新推荐文章于 2024-09-07 16:10:52 发布

阅读量9.6k

点赞数 10

分类专栏：阳阳阳在学大数据 the way to jgs 文章标签： Hive 优化 HQL

阳阳阳在学大数据同时被 2 个专栏收录

14 篇文章 2 订阅

订阅专栏

the way to jgs

6 篇文章 1 订阅

订阅专栏

Hive SQL调优总结

本文参考链接（总结的很棒很全面）

这里只是总结了一下HQL上面的一些优化点，并不考虑Hadoop层面的参数、配置优化

使用分区剪裁、列剪裁

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

少用count（distinct）

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

多对多的关联

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

合理使用MapJoin

首先这里先介绍一下hive里的两种join
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来识别不同表中的数据
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，就有多少结果文件

key	value
1	26
2	34

合理使用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、有笛卡尔积