侧边栏壁纸
博主头像
王一川博主等级

努力成为一个不会前端的全栈工程师

  • 累计撰写 70 篇文章
  • 累计创建 20 个标签
  • 累计收到 39 条评论
标签搜索

目 录CONTENT

文章目录
大数据

Hive 执行计划那些事

王一川
王一川
2021-11-17 / 0 评论 / 0 点赞 / 1,807 阅读 / 13,852 字
温馨提示：
本文最后更新于 2022-06-02，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

在实际生产中，往往试图去优化一些线上的 SQL，但是伴随着大量数据的生产环境不可能做一个小小的优化有去线上执行，甚至多次执行去观察到底优化有没有成功；这种做法往往是不可取的。因此 Hive 提供了一个执行计划的功能帮助我们查看优化前后的 SQL 到底是怎么执行的来判断本次优化是否成功。

本文将依托 join 操作来介绍如何查看执行计划，同时解读 mapjoin 的执行计划并提供一个 mapjoin 失效的案例

如何查看执行计划

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query-sql

通常 explain sql 的信息就已经够用了，拓展、依赖、权限相关的根据具体情况来看

hive (advance)> explain select * from smalltable;
OK
Explain
-- 阶段的依赖关系
STAGE DEPENDENCIES:
  Stage-0 is a root stage

-- 具体每个阶段的执行计划
STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: smalltable
          Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: id (type: bigint), t (type: bigint), uid (type: string), keyword (type: string), url_rank (type: int), click_num (type: int), click_url (type: string)
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
            Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
            ListSink

Time taken: 0.139 seconds, Fetched: 17 row(s)

可以看出当前执行计划只有一个阶段，该阶段的执行计划是一个 Fetch 抓取操作；limit -1 表示没有限制；之后是执行树是一个 TableScan 表扫描，展示了表名以及关于表的一些统计信息；接下来就是查询操作，查了哪些字段、输出的字段名是什么以及一些统计信息。这就是简单的 select * 操作的执行计划

mapjoin 执行计划

众所周知 mapjoin 是 hive 优化的重要一环，可以有效的解决因 join 操作带来的数据倾斜，同时也可以提高 sql 的执行效率；通常在大表 join 小表的时候将小表缓存到内存，再在 map 端读取大表并将小表广播到大表的每个 mapTask 中进行 join 操作，因此避免了 reduce 也就避免了数据倾斜，那 mapjoin 的执行计划是什么样子的呢？

首先开启 mapjoin(默认就是开启的)，设置小表的阈值(默认 25M)

set hive.auto.convert.join=true;
set hive.mapjoin.smalltable.filesize=25000000;

事先准备两张表，一个大表、一个小表

hive (advance)> explain select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
              > from smalltable s
              > join bigtable b
              > on s.id = b.id;
OK
Explain
STAGE DEPENDENCIES:
  Stage-4 is a root stage , consists of Stage-5, Stage-1
  Stage-5 has a backup stage: Stage-1
  Stage-3 depends on stages: Stage-5
  Stage-1
  Stage-0 depends on stages: Stage-3, Stage-1

STAGE PLANS:
  Stage: Stage-4
    Conditional Operator

  Stage: Stage-5
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $hdt$_0:s 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $hdt$_0:s 
          TableScan
            alias: s
            Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: bigint)
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
                HashTable Sink Operator
                  keys:
                    0 _col0 (type: bigint)
                    1 _col0 (type: bigint)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: bigint), t (type: bigint), uid (type: string), keyword (type: string), url_rank (type: int), click_num (type: int), click_url (type: string)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
                Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
                Map Join Operator
                  condition map:
                       Inner Join 0 to 1
                  keys:
                    0 _col0 (type: bigint)
                    1 _col0 (type: bigint)
                  outputColumnNames: _col1, _col2, _col3, _col4, _col5, _col6, _col7
                  Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
                  Select Operator
                    expressions: _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: string), _col5 (type: int), _col6 (type: int), _col7 (type: string)
                    outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
                    Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 1 Data size: 143165927 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
      Execution mode: vectorized
      Local Work:
        Map Reduce Local Work

  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: s
            Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: bigint)
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 130150840 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: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
          TableScan
            alias: b
            Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: bigint), t (type: bigint), uid (type: string), keyword (type: string), url_rank (type: int), click_num (type: int), click_url (type: string)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
                Statistics: Num rows: 1 Data size: 1291573248 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: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col1 (type: bigint), _col2 (type: string), _col3 (type: string), _col4 (type: int), _col5 (type: int), _col6 (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Inner Join 0 to 1
          keys:
            0 _col0 (type: bigint)
            1 _col0 (type: bigint)
          outputColumnNames: _col1, _col2, _col3, _col4, _col5, _col6, _col7
          Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: string), _col5 (type: int), _col6 (type: int), _col7 (type: string)
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
            Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 1 Data size: 143165927 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.313 seconds, Fetched: 131 row(s)

这个时候发现：哇！好多不想看了，不要着急每一个复杂的 SQL 都是一个个简单的 SQL 组合起来的，因此执行计划也是这样的。

首先看阶段的依赖关系

STAGE DEPENDENCIES:
  Stage-4 is a root stage , consists of Stage-5, Stage-1
  Stage-5 has a backup stage: Stage-1
  Stage-3 depends on stages: Stage-5
  Stage-1
  Stage-0 depends on stages: Stage-3, Stage-1

解释：

阶段 4 是根阶段，包含阶段 5 和阶段 1；

阶段 5 有一个备份阶段是阶段 1，也就是说阶段 1 是阶段 5 的备份

阶段 3 依赖阶段 5，也就是说阶段 5 执行完是阶段 3

阶段 1 是独立的，是阶段 5 的备份

阶段 0 依赖阶段 3 和阶段 1

最终该执行计划其实有两条线，主线是阶段 4 -> 阶段 5 -> 阶段 3 -> 阶段 0，备份线是阶段 4 -> 阶段 1 -> 阶段 0；为什么会有备份线呢？后面说 😉😉

先看主线阶段 4 -> 阶段 5 -> 阶段 3 -> 阶段 0

阶段 4

Stage: Stage-4
    Conditional Operator

就是一个条件运算符，或许是声明一下 join 的连接方式吧

阶段 5

Stage: Stage-5
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $hdt$_0:s 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $hdt$_0:s 
          TableScan
            alias: s
            Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: bigint)
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
                HashTable Sink Operator
                  keys:
                    0 _col0 (type: bigint)
                    1 _col0 (type: bigint)

阶段五是一个本地的 mapreduce 任务，首先是一个抓取操作，扫描的表是 s，即 smalltable 表，以 HashTable Sink 的形式输出，可以理解为把 smalltable 以 HashTable 的形式加载到内存，key 是 _col0 也就是 id，因为 join 操作只用到 smalltable 的 id，输出的都是 bigtable 的字段。

阶段 3

Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: bigint), t (type: bigint), uid (type: string), keyword (type: string), url_rank (type: int), click_num (type: int), click_url (type: string)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
                Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
                Map Join Operator
                  condition map:
                       Inner Join 0 to 1
                  keys:
                    0 _col0 (type: bigint)
                    1 _col0 (type: bigint)
                  outputColumnNames: _col1, _col2, _col3, _col4, _col5, _col6, _col7
                  Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
                  Select Operator
                    expressions: _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: string), _col5 (type: int), _col6 (type: int), _col7 (type: string)
                    outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
                    Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 1 Data size: 143165927 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
      Execution mode: vectorized
      Local Work:
        Map Reduce Local Work

阶段三首先扫描了 b 表即 bigtable；同时 select 了 7 个字段；之后进行了 MapJoin 的操作，并将结果输出成一个临时表，没有压缩并展示了序列化类，输入输出格式；同时大表的执行模式是 vectorized 矢量查询，注意该过程是没有 reduce 阶段的。

image-20211117113626501

阶段 0

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

简单的抓取操作输出

这就是主线的执行计划也是 mapjoin 的一个原理，下面看备用线阶段 4 -> 阶段 1 -> 阶段 0

阶段 1

Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: s
            Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: bigint)
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 130150840 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: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
          TableScan
            alias: b
            Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: bigint), t (type: bigint), uid (type: string), keyword (type: string), url_rank (type: int), click_num (type: int), click_url (type: string)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
                Statistics: Num rows: 1 Data size: 1291573248 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: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col1 (type: bigint), _col2 (type: string), _col3 (type: string), _col4 (type: int), _col5 (type: int), _col6 (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Inner Join 0 to 1
          keys:
            0 _col0 (type: bigint)
            1 _col0 (type: bigint)
          outputColumnNames: _col1, _col2, _col3, _col4, _col5, _col6, _col7
          Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: string), _col5 (type: int), _col6 (type: int), _col7 (type: string)
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
            Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 1 Data size: 143165927 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

阶段一很长，但无非做了两件事，首先该阶段是一个 mapreduce 任务且有 reduce 阶段；map 阶段分别扫描了两张表 s，b 即：smalltable 和 bigtable；reduce 阶段对两张表做了 join 操作，后面的操作和上面一样；这其实就是普通的 join 操作，map 端读取数据，reduce 端处理。

看完就好理解 mapjoin 的执行计划了，备用线路防止 mapjoin 失效，即：如果 mapjoin 的条件不成立就进行普通的 join 操作。

mapjoin 失效

上面的执行计划看出，mapjoin 执行过程中会有一个普通的 join 来兜底，如果 mapjoin 失效走普通 join 保证任务不失败，那 mapjoin 何时失效呢 ？当然不是我把 mapjoin 关了，比如这样

-- 不是这样做的
set hive.auto.convert.join=false;

我们依然保持 mapjoin 的开启，即和上面操作的配置不变，仅改动一丢丢的 SQL

explain select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from smalltable s
left join bigtable b
on s.id = b.id;

将内连接改为左连接，同样满足传说中的那句话，小表在前、大表在后，查看执行计划

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: s
            Statistics: Num rows: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: bigint)
              outputColumnNames: _col0
              Statistics: Num rows: 1 Data size: 130150840 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: 1 Data size: 130150840 Basic stats: COMPLETE Column stats: NONE
          TableScan
            alias: b
            Statistics: Num rows: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: bigint), t (type: bigint), uid (type: string), keyword (type: string), url_rank (type: int), click_num (type: int), click_url (type: string)
              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
              Statistics: Num rows: 1 Data size: 1291573248 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: 1 Data size: 1291573248 Basic stats: COMPLETE Column stats: NONE
                value expressions: _col1 (type: bigint), _col2 (type: string), _col3 (type: string), _col4 (type: int), _col5 (type: int), _col6 (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join 0 to 1
          keys:
            0 _col0 (type: bigint)
            1 _col0 (type: bigint)
          outputColumnNames: _col1, _col2, _col3, _col4, _col5, _col6, _col7
          Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: string), _col5 (type: int), _col6 (type: int), _col7 (type: string)
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
            Statistics: Num rows: 1 Data size: 143165927 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 1 Data size: 143165927 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: 1.145 seconds, Fetched: 60 row(s)

啊这，发现做的是普通的join，这时候 mapjoin 就失效了，这是为什么呢？看下面图解

image-20211117122808826

按照 mapjoin 的思路，小表广播到大表的每一个 maptask 中进行左连接，这时候问题就出现了，因为小表是主表，如果小表中的数据没有关联到大表，那这个字段该如何处理？补 null，不行，因为不能保证这个字段在另一个 maptask 中不出现；剔除也不可取因为这是左连接。因此左连接时 mapjoin 会失效。

0
hive
版权归属： 王一川
本文链接： https://kpretty.tech/archives/hiveexplain
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区