The SQL execution plan for hive.

Recently I met someone who asked me, in a hql, if there is a join, then there is a group by operation. How many maps are there at this time;

Actually look at the execution plan before. Today is free to study a bit, a look at the knowledge there is really a lot. Here is an example to illustrate:

explain       Select S0.sno,count (Distinct s0.sname)             from       student S0 left     outer joins Student1 S1 on (s0.sno=s1.sno)     GROUP by S0.sno;
Here are two tables, a student table, and a student1 table. Two tables of Sno, do a join operation. The Sno are grouped and then the sname of different bars in the expression after the grouping is counted.

Below we execute this explanation statement, the result of execution is as follows:

STAGE DEPENDENCIES:
Stage-5 is a root stage
Stage-2 depends on stages:stage-5
Stage-0 depends on stages:stage-2

--Here the entire statement is divided into three stages, Stage-5 as the root directory, and then Stage-2 is dependent on Stage-5. Finally, the Stage-0 shows the results.

STAGE plans:
Stage:stage-5
Map Reduce Local Work
Alias---Map Local Tables:
S1-the first map is done here, and then the table name of the second table is loaded in to mark the alias. The fetch operation is performed here.
Fetch Operator
Limit: 1
Alias---Map local Operator Tree:--Here is the native map operation to load the data.
S1
Tablescan
Alias:s1
Statistics:num rows:40 Data size:160 Basic stats:complete Column stats:none
HashTable Sink Operator--the hash operation performed here. The following is a conditional expression.
Condition Expressions:
0 {sno} {sname}
1
Keys
0 Sno (Type:int)
1 Sno (Type:int)

Stage:stage-2--and then into the second phase. At this stage is the real map reduce phase.
Map Reduce
Map Operator Tree:--The map operation performed here.
Tablescan--a table that scans coordinates first.
Alias:s0
Statistics:num rows:3 Data size:320 Basic stats:complete Column stats:none
Map Join Operator--the hive bar used here is the SQL explanation for the map join.
Condition Map:
Left Outer Join0 to 1--here to connect. The left and right tables are connected.
Condition Expressions:
0 {sno} {sname}
1
Keys
0 Sno (Type:int)
1 Sno (Type:int)
Outputcolumnnames: _col0, _col1--Output two columns here. Sno,sname, respectively.
Statistics:num rows:44 Data size:176 Basic stats:complete Column stats:none
Select Operator--a select operation was performed here.
Expressions: _col0 (Type:int), _col1 (type:string)
Outputcolumnnames: _col0, _col1
Statistics:num rows:44 Data size:176 Basic stats:complete Column stats:none
GROUP BY Operator-in hive, in order to optimize SQL, the data is simply grouped before it enters the reduce end. Here will group the SNO and Sname, as a health, output of three columns of data.
Aggregations:count (DISTINCT _col1)
Keys: _col0 (Type:int), _col1 (type:string)
Mode:hash
Outputcolumnnames: _col0, _col1, _col2--here is the output of three columns for this _col2, my understanding is composed of two columns and.
Statistics:num rows:44 Data size:176 Basic stats:complete Column stats:none
Reduce Output Operator--here we can see that there are some things that need to be done before we get to reduce.
Key expressions: _col0 (Type:int), _col1 (type:string)
Sort order: + +-This is where the data is grouped, and is the preparation for the shuffle process. Sorts the data on the same map task side.
Map-reduce Partition columns: _col0 (type:int)--This will group the data in the map, where the data is delivered to different partition by the group by column name.
Statistics:num rows:44 Data size:176 Basic stats:complete Column stats:none
Local work:
Map Reduce Local Work
Reduce Operator Tree:--here our data goes into the reduce phase of processing.
GROUP BY Operator--here is the true grouping.
Aggregations:count (DISTINCT key._col1:0._col0)--then we set the data in each group according to our group and then count the number of different sname in the group.
Keys:key._col0 (Type:int)
Mode:mergepartial
Outputcolumnnames: _col0, _col1--This will be the output of good books for statistics.
Statistics:num rows:22 Data size:88 Basic stats:complete Column stats:none
Select Operator
Expressions: _col0 (Type:int), _col1 (Type:bigint)
Outputcolumnnames: _col0, _col1
Statistics:num rows:22 Data size:88 Basic stats:complete Column stats:none
File Output Operator--This is the data operation of the files and the operation of landing to disk.
Compressed:false
Statistics:num rows:22 Data size:88 Basic stats:complete Column stats:none
Table
Input Format:org.apache.hadoop.mapred.TextInputFormat-This is the operation to read the data into.
Output Format:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat--This is the format for outputting data.
Serde:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

Stage:stage-0-At this stage we show the data we have just processed.
Fetch Operator
Limit: 1
Processor Tree:
Listsink

Actually here we see the operation of the data write disk. The stage after the map is finished. Probably because the amount of data is not large enough.
In addition, the use of the map Jion operation also did not see the time of the join, the emergence of shuffle process. I think it may also be too small data volume. Loaded directly into the distributed cache. Cause the present phenomenon.

If there is a wrong explanation, if someone sees trouble pointing.

The SQL execution plan for hive.