[
https://issues.apache.org/jira/browse/HIVE-17486?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16160943#comment-16160943
]
liyunzhang_intel commented on HIVE-17486:
-----------------------------------------
[~stakiar]: thanks for interesting on it. I guess this optimization have
following effect.
{code}
set hive.strict.checks.cartesian.product=false;
set hive.join.emit.interval=2;
set hive.auto.convert.join=false;
explain SELECT *
FROM (
SELECT test1.key AS key1, test1.value AS value1, test1.col_1 AS col_1,
test2.key AS key2, test2.value AS value2, test2.col_2 AS col_2
FROM test1 RIGHT OUTER JOIN test2
ON (test1.value=test2.value
AND (test1.key between 100 and 102
OR test2.key between 100 and 102))
) sq1
FULL OUTER JOIN (
SELECT test1.key AS key3, test1.value AS value3, test1.col_1 AS col_3,
test2.key AS key4, test2.value AS value4, test2.col_2 AS col_4
FROM test1 LEFT OUTER JOIN test2
ON (test1.value=test2.value
AND (test1.key between 100 and 102
OR test2.key between 100 and 102))
) sq2
ON (sq1.value1 is null or sq2.value4 is null and sq2.value3 != sq1.value2);
{code}
the spark explain
{code}
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Spark
Edges:
Reducer 2 <- Map 1 (PARTITION-LEVEL SORT, 12), Map 4 (PARTITION-LEVEL
SORT, 12)
Reducer 3 <- Reducer 2 (PARTITION-LEVEL SORT, 1), Reducer 6
(PARTITION-LEVEL SORT, 1)
Reducer 6 <- Map 5 (PARTITION-LEVEL SORT, 12), Map 7 (PARTITION-LEVEL
SORT, 12)
DagName: root_20170911043433_e314705a-beca-41a0-b28a-c85c5f811a67:1
Vertices:
Map 1
Map Operator Tree:
TableScan
alias: test1
Statistics: Num rows: 6 Data size: 56 Basic stats: COMPLETE
Column stats: NONE
Select Operator
expressions: key (type: int), value (type: int), col_1
(type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 6 Data size: 56 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
key expressions: _col1 (type: int)
sort order: +
Map-reduce partition columns: _col1 (type: int)
Statistics: Num rows: 6 Data size: 56 Basic stats:
COMPLETE Column stats: NONE
value expressions: _col0 (type: int), _col2 (type: string)
Map 4
Map Operator Tree:
TableScan
alias: test2
Statistics: Num rows: 4 Data size: 38 Basic stats: COMPLETE
Column stats: NONE
Select Operator
expressions: key (type: int), value (type: int), col_2
(type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 4 Data size: 38 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
key expressions: _col1 (type: int)
sort order: +
Map-reduce partition columns: _col1 (type: int)
Statistics: Num rows: 4 Data size: 38 Basic stats:
COMPLETE Column stats: NONE
value expressions: _col0 (type: int), _col2 (type: string)
Map 5
Map Operator Tree:
TableScan
alias: test1
Statistics: Num rows: 6 Data size: 56 Basic stats: COMPLETE
Column stats: NONE
Select Operator
expressions: key (type: int), value (type: int), col_1
(type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 6 Data size: 56 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
key expressions: _col1 (type: int)
sort order: +
Map-reduce partition columns: _col1 (type: int)
Statistics: Num rows: 6 Data size: 56 Basic stats:
COMPLETE Column stats: NONE
value expressions: _col0 (type: int), _col2 (type: string)
Map 7
Map Operator Tree:
TableScan
alias: test2
Statistics: Num rows: 4 Data size: 38 Basic stats: COMPLETE
Column stats: NONE
Select Operator
expressions: key (type: int), value (type: int), col_2
(type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 4 Data size: 38 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
key expressions: _col1 (type: int)
sort order: +
Map-reduce partition columns: _col1 (type: int)
Statistics: Num rows: 4 Data size: 38 Basic stats:
COMPLETE Column stats: NONE
value expressions: _col0 (type: int), _col2 (type: string)
Reducer 2
Reduce Operator Tree:
Join Operator
condition map:
Right Outer Join 0 to 1
keys:
0 _col1 (type: int)
1 _col1 (type: int)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
residual filter predicates: {(_col0 BETWEEN 100 AND 102 or
_col3 BETWEEN 100 AND 102)}
Statistics: Num rows: 6 Data size: 61 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
sort order:
Statistics: Num rows: 6 Data size: 61 Basic stats: COMPLETE
Column stats: NONE
value expressions: _col0 (type: int), _col1 (type: int),
_col2 (type: string), _col3 (type: int), _col4 (type: int), _col5 (type: string)
Reducer 3
Reduce Operator Tree:
Join Operator
condition map:
Outer Join 0 to 1
keys:
0
1
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5,
_col6, _col7, _col8, _col9, _col10, _col11
residual filter predicates: {(_col1 is null or (_col10 is null
and (_col7 <> _col4)))}
Statistics: Num rows: 36 Data size: 768 Basic stats: COMPLETE
Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 36 Data size: 768 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
Reducer 6
Reduce Operator Tree:
Join Operator
condition map:
Left Outer Join 0 to 1
keys:
0 _col1 (type: int)
1 _col1 (type: int)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
residual filter predicates: {(_col0 BETWEEN 100 AND 102 or
_col3 BETWEEN 100 AND 102)}
Statistics: Num rows: 6 Data size: 61 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
sort order:
Statistics: Num rows: 6 Data size: 61 Basic stats: COMPLETE
Column stats: NONE
value expressions: _col0 (type: int), _col1 (type: int),
_col2 (type: string), _col3 (type: int), _col4 (type: int), _col5 (type: string)
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
{code}
the explain tez:
{code}
STAGE PLANS:
Stage: Stage-1
Tez
#### A masked pattern was here ####
Edges:
Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 5 (SIMPLE_EDGE)
Reducer 3 <- Reducer 2 (CUSTOM_SIMPLE_EDGE), Reducer 4
(CUSTOM_SIMPLE_EDGE)
Reducer 4 <- Map 1 (SIMPLE_EDGE), Map 5 (SIMPLE_EDGE)
#### A masked pattern was here ####
Vertices:
Map 1
Map Operator Tree:
TableScan
alias: test1
Statistics: Num rows: 6 Data size: 56 Basic stats: COMPLETE
Column stats: NONE
Select Operator
expressions: key (type: int), value (type: int), col_1
(type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 6 Data size: 56 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
key expressions: _col1 (type: int)
sort order: +
Map-reduce partition columns: _col1 (type: int)
Statistics: Num rows: 6 Data size: 56 Basic stats:
COMPLETE Column stats: NONE
value expressions: _col0 (type: int), _col2 (type: string)
Select Operator
expressions: key (type: int), value (type: int), col_1
(type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 6 Data size: 56 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
key expressions: _col1 (type: int)
sort order: +
Map-reduce partition columns: _col1 (type: int)
Statistics: Num rows: 6 Data size: 56 Basic stats:
COMPLETE Column stats: NONE
value expressions: _col0 (type: int), _col2 (type: string)
Execution mode: llap
LLAP IO: no inputs
Map 5
Map Operator Tree:
TableScan
alias: test2
Statistics: Num rows: 4 Data size: 38 Basic stats: COMPLETE
Column stats: NONE
Select Operator
expressions: key (type: int), value (type: int), col_2
(type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 4 Data size: 38 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
key expressions: _col1 (type: int)
sort order: +
Map-reduce partition columns: _col1 (type: int)
Statistics: Num rows: 4 Data size: 38 Basic stats:
COMPLETE Column stats: NONE
value expressions: _col0 (type: int), _col2 (type: string)
Select Operator
expressions: key (type: int), value (type: int), col_2
(type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 4 Data size: 38 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
key expressions: _col1 (type: int)
sort order: +
Map-reduce partition columns: _col1 (type: int)
Statistics: Num rows: 4 Data size: 38 Basic stats:
COMPLETE Column stats: NONE
value expressions: _col0 (type: int), _col2 (type: string)
Execution mode: llap
LLAP IO: no inputs
Reducer 2
Execution mode: llap
Reduce Operator Tree:
Merge Join Operator
condition map:
Right Outer Join0 to 1
keys:
0 _col1 (type: int)
1 _col1 (type: int)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
residual filter predicates: {(_col0 BETWEEN 100 AND 102 or
_col3 BETWEEN 100 AND 102)}
Statistics: Num rows: 6 Data size: 61 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
sort order:
Statistics: Num rows: 6 Data size: 61 Basic stats: COMPLETE
Column stats: NONE
value expressions: _col0 (type: int), _col1 (type: int),
_col2 (type: string), _col3 (type: int), _col4 (type: int), _col5 (type: string)
Reducer 3
Execution mode: llap
Reduce Operator Tree:
Merge Join Operator
condition map:
Outer Join 0 to 1
keys:
0
1
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5,
_col6, _col7, _col8, _col9, _col10, _col11
residual filter predicates: {(_col1 is null or (_col10 is null
and (_col7 <> _col4)))}
Statistics: Num rows: 36 Data size: 768 Basic stats: COMPLETE
Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 36 Data size: 768 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
Reducer 4
Execution mode: llap
Reduce Operator Tree:
Merge Join Operator
condition map:
Left Outer Join0 to 1
keys:
0 _col1 (type: int)
1 _col1 (type: int)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
residual filter predicates: {(_col0 BETWEEN 100 AND 102 or
_col3 BETWEEN 100 AND 102)}
Statistics: Num rows: 6 Data size: 61 Basic stats: COMPLETE
Column stats: NONE
Reduce Output Operator
sort order:
Statistics: Num rows: 6 Data size: 61 Basic stats: COMPLETE
Column stats: NONE
value expressions: _col0 (type: int), _col1 (type: int),
_col2 (type: string), _col3 (type: int), _col4 (type: int), _col5 (type: string)
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
{code}
>From the explain, hive on spark loads test1, test2 twice while hive on tez
>only loads once. If my understanding is wrong, tell me,thanks!
> Enable SharedWorkOptimizer in tez on HOS
> ----------------------------------------
>
> Key: HIVE-17486
> URL: https://issues.apache.org/jira/browse/HIVE-17486
> Project: Hive
> Issue Type: Bug
> Reporter: liyunzhang_intel
> Assignee: liyunzhang_intel
>
> in HIVE-16602, Implement shared scans with Tez.
> Given a query plan, the goal is to identify scans on input tables that can be
> merged so the data is read only once. Optimization will be carried out at the
> physical level.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)