Hive优化之SQL的优化(三)

二龙2年前技术文章504

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

案例一 过滤前置:

案例背景

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

问题报错分析

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

图片一.png

图片二.png

任务sql:

图片三.png

解决优化

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

图片四.png

图片五.png

案例二 谨防过度优化:

案例背景

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

运行缓慢分析

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

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

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

图片六.png

图片七.png

    原因有以下几点:

    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的调优都是要基于业务出发,适合的才是最好的。


相关文章

Hive压测之开源Hive基准测试工具(hive-testbench-hive14)

Hive压测之开源Hive基准测试工具(hive-testbench-hive14)

此文章禁止转载概述Hive基准测试工具工具,可用来造数测试Hive基本性能。TPC-DS:提供一个公平和诚实的业务和数据模型,99个案例TPC-H:面向商品零售业的决策支持系统测试基准,定义了8张表,...

helm安装部署trino对接hive(一)

helm安装部署trino对接hive(一)

前提:本文前提是基于hive组件已经提前安装的情况下,安装部署好trino容器之后进行对hive组件的对接。helm trino地址:https://artifacthub.io/packages/h...

Hive优化之配置参数的优化(一)

Hive优化之配置参数的优化(一)

 Hive是大数据领域常用的组件之一,主要是大数据离线数仓的运算,关于Hive的性能调优在日常工作和面试中是经常涉及的一个点,因此掌握一些Hive调优是必不可少的一项技能。影响Hive效率的...

Hive架构图及Hive SQL的执行流程

Hive架构图及Hive SQL的执行流程

1、Hive产生背景MapReduce编程的不便性HDFS上的文件缺少Schema(表名,名称,ID等,为数据库对象的集合)2、Hive是什么Hive的使用场景是什么?基于Hadoop做一些数据清洗啊...

Hive优化之监控(四)

Hive优化之监控(四)

    Hive是大数据领域常用的组件之一,主要是大数据离线数仓的运算,关于Hive的性能调优在日常工作和面试中是经常涉及的一个点,因此掌握一些Hive调优是必...

ranger对接metastore

ranger对接metastore

前提:本文前提是基于集群中已经安装部署了ranger组件、hive组件的情况下,增加ranger metastore插件的对接。安装部署1、ranger metastore插件编译插件下载 https...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。