Hive mapjoin的执行计划解读

我会带着你远行 2022-02-04 18:07 357阅读 0赞

# 创建两张表，以emp表和dept表为例： #

create table emp(
    empno int, 
    ename string, 
    job string, 
    mgr int, 
    hiredate string, 
    sal double, 
    comm double, 
    deptno int
    )row format delimited fields terminated by '\t';

加载数据：

load data local inpath '/home/hadoop/data/emp.txt' overwrite into table emp;

CREATE TABLE dept(
    deptno int,
    dname string,
    loc string
    )row format delimited fields terminated by '\t';

加载数据：

load data local inpath '/home/bigdata/testdata/dept.txt' overwrite into table dept;

# 执行计划解读： #

## 首先设置set hive.auto.convert.join=false;Hive默认是true ##

explain select  e.empno, e.ename, d.dname from emp e join dept d on e.deptno=d.deptno;

输出信息如下：

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: e
                Statistics: Num rows: 4 Data size: 512 Basic stats: COMPLETE Column stats: NONE
                Filter Operator
                  predicate: deptno is not null (type: boolean)
                  Statistics: Num rows: 2 Data size: 256 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: deptno (type: int)
                    sort order: +
                    Map-reduce partition columns: deptno (type: int)
                    Statistics: Num rows: 2 Data size: 256 Basic stats: COMPLETE Column stats: NONE
                    value expressions: empno (type: int), ename (type: string)
              TableScan
                alias: d
                Statistics: Num rows: 1 Data size: 80 Basic stats: COMPLETE Column stats: NONE
                Filter Operator
                  predicate: deptno is not null (type: boolean)
                  Statistics: Num rows: 1 Data size: 80 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: deptno (type: int)
                    sort order: +
                    Map-reduce partition columns: deptno (type: int)
                    Statistics: Num rows: 1 Data size: 80 Basic stats: COMPLETE Column stats: NONE
                    value expressions: dname (type: string)
          Reduce Operator Tree:
            Join Operator
              condition map:
                   Inner Join 0 to 1
              keys:
                0 deptno (type: int)
                1 deptno (type: int)
              outputColumnNames: _col0, _col1, _col12
              Statistics: Num rows: 2 Data size: 281 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: _col0 (type: int), _col1 (type: string), _col12 (type: string)
                outputColumnNames: _col0, _col1, _col2
                Statistics: Num rows: 2 Data size: 281 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  Statistics: Num rows: 2 Data size: 281 Basic stats: COMPLETE Column stats: NONE
                  table:
                      input format: org.apache.hadoop.mapred.TextInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
    
      Stage: Stage-0
        Fetch Operator
          limit: -1
          Processor Tree:
            ListSink

## 大概解读执行计划： ##

TableScan 表示去读表，分别读取两张表的数据。

Filter Operator 表示过滤操作 即 e.deptno=d.deptno，此处表示是连接条件。

value expressions 表示输出的value字段有哪些  即 e.empno, e.ename, d.dname字段等

# mapjoin执行计划解读： #

## 设置set hive.auto.convert.join=true; ##

select /*+MAPJOIN(d)*/ e.empno, e.ename, d.dname from emp e join dept d on e.deptno=d.deptno;

输出信息如下：

Total jobs = 1
    19/05/08 10:34:04 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
    Execution log at: /var/folders/dg/h9vh6crd5wz5jzmvs9yndxh80000gn/T//mac/mac_20190508103232_be87aea4-5151-4534-8afc-a4e78bfa3902.log
    2019-05-08 10:34:05	Starting to launch local task to process map join;	maximum memory = 477626368
    2019-05-08 10:34:06	Dump the side-table for tag: 1 with group count: 4 into file: file:/var/folders/dg/h9vh6crd5wz5jzmvs9yndxh80000gn/T/mac/4fa4be83-07af-4b2e-90fc-7b27bec1ca01/hive_2019-05-08_10-34-00_672_565050965233091187-1/-local-10003/HashTable-Stage-3/MapJoin-mapfile01--.hashtable
    2019-05-08 10:34:06	Uploaded 1 File to: file:/var/folders/dg/h9vh6crd5wz5jzmvs9yndxh80000gn/T/mac/4fa4be83-07af-4b2e-90fc-7b27bec1ca01/hive_2019-05-08_10-34-00_672_565050965233091187-1/-local-10003/HashTable-Stage-3/MapJoin-mapfile01--.hashtable (373 bytes)
    2019-05-08 10:34:06	End of local task; Time Taken: 1.077 sec.
    Execution completed successfully
    MapredLocal task succeeded
    Launching Job 1 out of 1
    Number of reduce tasks is set to 0 since there's no reduce operator
    Starting Job = job_1557276691053_0008, Tracking URL = http://localhost:8088/proxy/application_1557276691053_0008/
    Kill Command = /wangqingguo/bigdata/hadoop-2.6.0-cdh5.7.0/bin/hadoop job  -kill job_1557276691053_0008
    Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
    2019-05-08 10:34:13,790 Stage-3 map = 0%,  reduce = 0%
    2019-05-08 10:34:19,002 Stage-3 map = 100%,  reduce = 0%
    Ended Job = job_1557276691053_0008
    MapReduce Jobs Launched:
    Stage-Stage-3: Map: 1   HDFS Read: 6466 HDFS Write: 20 SUCCESS
    Total MapReduce CPU Time Spent: 0 msec

执行计划解读：

Starting to launch local task to process map join：启动一个本地的MR task

Dump the side-table for tag:在底层生成hashtable

Uploaded 1 File to:将生成的hashtable上传到分布式缓存中

End of local task：销毁本地的MR task

MapredLocal task succeeded：表示本地的MR task已经执行成功

Launching Job 1 out of 1：又启动一个job

number of mappers: 1; number of reducers: 0：mapper数量为1，没有reduce数据

# 总结一下： #

1. 对于普通的join，会生成2个stage

2. 对于mapjoin，会生成3个stage

如果是小表join，优先选择mapjoin，默认设置是：