Hive学习笔记（四）——优化篇1

HiveQL的调优对于经常使用HQL做数据开发的弟兄来书是很有必要去了解的，学习hive背后的实现细节，如何更加高效的使用hive，我想这也是很有必要了解的。无论是在面试中还是在开发过程中都会起到很大的作用。
此处使用的hive版本为：2.3.0

1 使用EXPLAIN

explain可以打印出hive的执行计划，它可以帮助我们了解hive是如何将查询语句转化为MapReduce任务的。

使用方法：在hql语句前面加上explain

explain select sum(num) from bucket_num;

执行结果：

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: bucket_num
            Statistics: Num rows: 28 Data size: 114 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: num (type: int)
              outputColumnNames: num
              Statistics: Num rows: 28 Data size: 114 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: sum(num)
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  sort order: 
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col0 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: sum(VALUE._col0)
          mode: mergepartial
          outputColumnNames: _col0
          Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.268 seconds, Fetched: 44 row(s)

1. 从执行计划的结果上来看：可以看到表名：bucket_number,字段名：num ，数据类型：int，以及聚合函数sum（）。
2. 一个hive任务会包含一个或者多个stage阶段，不同的stage会存在着相互的依赖关系，越复杂的查询通常会有越多的stage，也就需要更多的时间来完成。一个stage可以是一个MapReduce任务，也可以是一个抽样阶段，或者是一个合并阶段，还可以是一个limit阶段，默认情况下hive会执行一个stag。
3. STAGE PLAN
   Stage-1:包含了job的大部分处理过程
   Stage-0：该语句是在该阶段是一个没有任何操作单的阶段
   Map Operator Tree: map阶段的开始
   Reduce Operator Tree: reduce阶段的开始
   _col0:这是临时结果起得字段名
   4）理解hive对每个查询的解析对分析复杂的查询和执行效率低的一个不错的方式
   5）还有一种更详细的方式，可以对比下：

explain extended select sum(num) from bucket_num;

2 限制调整

限制调整指的是：我们在执行查询语句的时候要查询整个语句，在返回结果，这种情况是很浪费时间的，所以要尽可能的避免这种情况的发生。所以，hive有个属性可以开启，当使用limit语句时，可以对其语句进行抽样：

<prperty>
	<name>hive.limit.optimize.enable</name>
	<value>true</value>
</property>

所以该功能一旦开启，还可以设置row的数量和要抽样文件的数量

<prperty>
	<name>hive.limit.row.max.size</name>
	<value>10000</value>
</property>

<prperty>
	<name>hive.limit.optimit.limit.file</name>
	<value>10</value>
</property>

不过功能虽好，但是也是有缺点的，因为随机抽样就不一定能够处理到所以有用的数据，所以在每次操作其他聚合函数时可能会产生不同的结果。

3 列裁剪和分区裁剪

列裁剪说白了就是执行查询语句的时候只查对应的列
区裁剪同样也是只查询对应的分区

当列很多或者数据量很大时，如果选择*或者不指定分区，全列扫描和全表扫描效率都很低.Hive中与列裁剪优化相关的配置项是hive.optimize.cp，与分区裁剪优化相关的则是hive.optimize.pruner，默认都是真实的。在HiveQL解析阶段对应的则是ColumnPruner逻辑优化器。

4 谓词下推

谓词下推，这个概念是在关系型数据库也是存在的，现在也在HQL语句中也是存在的，意思是说过滤条件查询的谓词语句提前执行，减少查询的数据量。

select a.uid,a.event_type,b.topic_id,b.title
from calendar_record_log a
left outer join (
  select uid,topic_id,title from forum_topic
  where pt_date = 20190224 and length(content) >= 100
) b on a.uid = b.uid
where a.pt_date = 20190224 and status = 0;

对forum_topic做过滤的地方语句写在子查询内部，而不是外部.Hive中有谓词下推优化的配置项hive.optimize.ppd，默认值真，与它对应的逻辑优化器是PredicatePushDown。该优化器就是将OperatorTree中的FilterOperator向上提，见下图。

如图所示：FIL（9）的位置，现在已经提到了上面执行，这样做的好处是可以将原有的数据过滤掉一部分，从而提高查询效率，当然这样的查询对结果也不一定是正确的，谓词狭义也要看情况。
可参考这篇文章，对下移的四种谓词下移情况作了详细介绍：https://blog.csdn.net/pengzonglu7292/article/details/81036712

5 sort by 代替 order by

1）order by 的排序方式，它跟其他SQL方言一样是全局排序的，这样的话，会导致map端的所有数据进入到reduce端,如果数据量大的话，这会增加reducer端的计算量，甚至有可能宕机。
如果非要使用order by，就必须在严格模式下，该模式下，并且和limit一起使用，限制条数，order by就不会全局排序（不建议这么做）
set hive.mapred.mode = strict;

2）sort by 的排序方式为局部排序，会视情况会启动多个reduce来进行排序，并且保证每个reducer内部有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by一同使用。distribute by的作用是：按照一定规则分发到不同的reducer端，map端数据就不会随机分配到reducer。

select uid,upload_time,event_type,record_data
from calendar_record_log
where pt_date >= 20190201 and pt_date <= 20190224
distribute by uid
sort by upload_time desc,event_type desc;

六 group by 配置

先看看group by聚合原理：

group by时，如果先起一个combiner在map端做部分预聚合，可以有效减少shuffle数据量。预聚合的配置项是hive.map.aggr，默认值true，对应的优化器为GroupByOptimizer，简单方便。
通过hive.groupby.mapaggr.checkinterval参数也可以设置map端预聚合的行数阈值，超过该值就会分拆job，默认值100000。

当然，group by 时，如果某些key值数据量过大，就会发生数据倾斜，对此，hive有自己的均衡函数：默认是false
hive.groupby.skewindata=true
其实现方法是在group by时启动两个MR job。第一个job会将map端数据随机输入reducer，每个reducer做部分聚合，相同的key就会分布在不同的reducer中。第二个job再将前面预处理过的数据按key聚合并输出结果，这样就起到了均衡的效果。
但是，配置项毕竟是死的，单纯靠它有时不能根本上解决问题，因此还是建议自行了解数据倾斜的细节，并优化查询语句。