[ 
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)

Reply via email to