hive-join的执行计划观察

 

        看下join的执行过程，还是看explain计划。

sql代码如下：

explain
select  t1.prov_id
        ,t2.deep
from    (
            select  prov_id
                    ,deep
            from    dim.dim_city
            where   prov_id = 110000
        ) t1
join    (
            select  prov_id
                    ,deep
            from    dim.dim_city
            where   deep = 1
        ) t2
on      t1.prov_id = t2.prov_id
;

执行计划

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

STAGE PLANS:
//对应第一段子查询
Stage: Stage-4
Map Reduce Local Work
Alias -> Map Local Tables:
t1:dim_city
Fetch Operator
limit: -1
Alias -> Map Local Operator Tree:
t1:dim_city
TableScan
alias: dim_city
filterExpr: (prov_id = 110000) (type: boolean)
Statistics: Num rows: 3775 Data size: 522191 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: (prov_id = 110000) (type: boolean)
Statistics: Num rows: 1887 Data size: 261026 Basic stats: COMPLETE Column stats: NONE
Select Operator
Statistics: Num rows: 1887 Data size: 261026 Basic stats: COMPLETE Column stats: NONE
HashTable Sink Operator
keys:
0 110000 (type: int)
1 _col0 (type: int)

//对应第二段子查询
Stage: Stage-3
Map Reduce
Map Operator Tree:
TableScan
alias: dim_city
filterExpr: ((deep = 1) and prov_id is not null) (type: boolean)
Statistics: Num rows: 3775 Data size: 522191 Basic stats: COMPLETE Column stats: NONE
Filter Operator
//这里有个细节，join时null值不能关联，在取数据时直接就过滤掉了
predicate: ((deep = 1) and prov_id is not null) (type: boolean)
Statistics: Num rows: 1887 Data size: 261026 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: prov_id (type: int)
outputColumnNames: _col0
Statistics: Num rows: 1887 Data size: 261026 Basic stats: COMPLETE Column stats: NONE


Map Join Operator
condition map:
//声明连接算法是内连接
Inner Join 0 to 1
//指定两表连接的条件
keys:
//第一个表输出的数据集
0 110000 (type: int)，110000是关联的条件
//第二个表输出的数据集，_col0是关联的条件
1 _col0 (type: int)
Statistics: Num rows: 2075 Data size: 287128 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: 110000 (type: int), 1 (type: int)
outputColumnNames: _col0, _col1
Statistics: Num rows: 2075 Data size: 287128 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 2075 Data size: 287128 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
Local Work:
Map Reduce Local Work

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