Hive是大数据领域常用的组件之一，主要是大数据离线数仓的运算，关于Hive的性能调优在日常工作和面试中是经常涉及的一个点，因此掌握一些Hive调优是必不可少的技能。影响Hive效率的主要有数据倾斜、数据冗余、job的IO以及不同底层引擎配置情况和Hive本身参数和HiveSQL的执行等因素。本文主要是从SQL角度对Hive优化抽取两个案例进行描述。

案例一 过滤前置:

案例背景

    巡检时发现有任务报错，此任务之前运行成功过，运行时长1个小时+。

问题报错分析

    根据日志可以看到任务报错主要是内存不足导致任务task被杀死，观察yarn上executor，可看到处理的数据量较大，观察客户任务sql，可以看到存在多次 join表，且是大表join。

任务sql:

解决优化

    开始时尝试调大内存参数，仍是报错。考虑数据处理量太大，且看客户sql是join后再where过滤，建议客户将过滤前置到每个join内部，并且调大spark.sql.shuffle.partitions参数为1000（默认是200），观察任务运行成功，耗时13分钟。

案例二 谨防过度优化:

案例背景

    客户反馈有一任务运行缓慢，占据资源严重，希望我们能针对这个任务提供一下优化。

运行缓慢分析

    查看sql语句，主要是使用的group by针对不同年龄段的用户实现的去重的功能，且该任务数据量较多。

    sql语句有个比较常见的优化手段：利用group by代替distinct实现去重，因为在数据量比较大的情况下，使用group by能有效的避免数据倾斜，执行效率更高。但其实在设置测试数据进行测试中，group by比distinct的效率还低。

    创建表模拟测试场景，将group by和distinct执行效率进行比较：

    原因有以下几点：

    1、进行去重的列是s_age列，他的含义表示年龄，一个人的年龄是有限的，转化为MapReduce来解释的话，在Map阶段，每个Map会对年龄进行去重，由于一个人年龄不可能无限制的大或小，因此每个Map得到的s_age也有限，最终得到的reducce的数量去重过的s_age的个数，数据量上比较小，不需要避免数据倾斜。

    2、在第二个语句中distinct的命令在内存中会构建一个hashtable,查找去重的时间复杂度是O(1),而group by 在不同版本间变动较大，有的版本会用hashtable进行去重，有的版本则是使用排序的方式去重，这种排序方式是达不到O(1)。

    3、最新的hive版本中针对count(distinct) 进行了优化，通过设置参数，即使出现数据倾斜，内部也会进行自动优化，自动改变sql的执行计划。

    从执行计划中也可以证明这个结论

    语句1的执行计划：

STAGE DEPENDENCIES:

  Stage-1 is a root stage

  Stage-2 depends on stages: Stage-1

  Stage-0 depends on stages: Stage-2



STAGE PLANS:

  Stage: Stage-1

    Map Reduce

      Map Operator Tree:

          TableScan

            alias: student_tb_txt

            Statistics: Num rows: 5482188 Data size: 43857508 Basic stats: COMPLETE Column stats: NONE

            Select Operator

              expressions: s_age (type: bigint)

              outputColumnNames: s_age

              Statistics: Num rows: 5482188 Data size: 43857508 Basic stats: COMPLETE Column stats: NONE

              Group By Operator

                keys: s_age (type: bigint)

                mode: hash

                outputColumnNames: _col0

                Statistics: Num rows: 5482188 Data size: 43857508 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: _col0 (type: bigint)

                  sort order: +

                  Map-reduce partition columns: _col0 (type: bigint)

                  Statistics: Num rows: 5482188 Data size: 43857508 Basic stats: COMPLETE Column stats: NONE

      Reduce Operator Tree:

        Group By Operator

          keys: KEY._col0 (type: bigint)

          mode: mergepartial

          outputColumnNames: _col0

          Statistics: Num rows: 2741094 Data size: 21928754 Basic stats: COMPLETE Column stats: NONE

          Select Operator

            Statistics: Num rows: 2741094 Data size: 21928754 Basic stats: COMPLETE Column stats: NONE

            Group By Operator

              aggregations: count(1)

              mode: hash

              outputColumnNames: _col0

              Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE

              File Output Operator

                compressed: false

                table:

                    input format: org.apache.hadoop.mapred.SequenceFileInputFormat

                    output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

                    serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe



  Stage: Stage-2

    Map Reduce

      Map Operator Tree:

          TableScan

            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: count(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

语句2的执行计划：

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: student_tb_txt

            Statistics: Num rows: 5482188 Data size: 43857508 Basic stats: COMPLETE Column stats: NONE

            Select Operator

              expressions: s_age (type: bigint)

              outputColumnNames: s_age

              Statistics: Num rows: 5482188 Data size: 43857508 Basic stats: COMPLETE Column stats: NONE

              Group By Operator

                aggregations: count(DISTINCT s_age)

                keys: s_age (type: bigint)

                mode: hash

                outputColumnNames: _col0, _col1

                Statistics: Num rows: 5482188 Data size: 43857508 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: _col0 (type: bigint)

                  sort order: +

                  Statistics: Num rows: 5482188 Data size: 43857508 Basic stats: COMPLETE Column stats: NONE

      Reduce Operator Tree:

        Group By Operator

          aggregations: count(DISTINCT KEY._col0:0._col0)

          mode: mergepartial

          outputColumnNames: _col0

          Statistics: Num rows: 1 Data size: 16 Basic stats: COMPLETE Column stats: NONE

          File Output Operator

            compressed: false

            Statistics: Num rows: 1 Data size: 16 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

    对比以上两个语句的执行计划，我们可以看出语句1是将去重和计数放到两个MapReduce中进行处理的，首先在第一个MapReduce阶段实现了select s_age from ods.student_tb_txt group by s_age的工作，在后面的MapReduce作业中实现了select count(1) from (..)b的逻辑。语句2是将去重和计数放在一个MapReduce中完成，相比语句1，消耗的磁盘及网络I/O也会更少。

解决优化

    结合业务和执行计划，使用distinct和group by实现去重功能。

总结： 

    调优要讲究适时调优，过早的进行调优可能导致过犹不及的效果，很多调优尤其是针对SQL的调优都是要基于业务出发，适合的才是最好的