Saving all output to "!!{outputDirectory}!!/skewjoin.q.raw". Enter "record" with no arguments to stop it.
>>>  !run !!{qFileDirectory}!!/skewjoin.q
>>>  set hive.optimize.skewjoin = true;
No rows affected 
>>>  set hive.skewjoin.key = 2;
No rows affected 
>>>  
>>>  
>>>  
>>>  
>>>  
>>>  
>>>  
>>>  CREATE TABLE T1(key STRING, val STRING) STORED AS TEXTFILE;
No rows affected 
>>>  CREATE TABLE T2(key STRING, val STRING) STORED AS TEXTFILE;
No rows affected 
>>>  CREATE TABLE T3(key STRING, val STRING) STORED AS TEXTFILE;
No rows affected 
>>>  CREATE TABLE T4(key STRING, val STRING) STORED AS TEXTFILE;
No rows affected 
>>>  CREATE TABLE dest_j1(key INT, value STRING) STORED AS TEXTFILE;
No rows affected 
>>>  
>>>  LOAD DATA LOCAL INPATH '../data/files/T1.txt' INTO TABLE T1;
No rows affected 
>>>  LOAD DATA LOCAL INPATH '../data/files/T2.txt' INTO TABLE T2;
No rows affected 
>>>  LOAD DATA LOCAL INPATH '../data/files/T3.txt' INTO TABLE T3;
No rows affected 
>>>  LOAD DATA LOCAL INPATH '../data/files/T1.txt' INTO TABLE T4;
No rows affected 
>>>  
>>>  
>>>  EXPLAIN 
FROM src src1 JOIN src src2 ON (src1.key = src2.key) 
INSERT OVERWRITE TABLE dest_j1 SELECT src1.key, src2.value;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME src) src1) (TOK_TABREF (TOK_TABNAME src) src2) (= (. (TOK_TABLE_OR_COL src1) key) (. (TOK_TABLE_OR_COL src2) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest_j1))) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src1) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src2) value)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-5 depends on stages: Stage-1 , consists of Stage-6'
'  Stage-6'
'  Stage-4 depends on stages: Stage-6'
'  Stage-0 depends on stages: Stage-1, Stage-4'
'  Stage-2 depends on stages: Stage-0'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        src1 '
'          TableScan'
'            alias: src1'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 0'
'              value expressions:'
'                    expr: key'
'                    type: string'
'        src2 '
'          TableScan'
'            alias: src2'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 1'
'              value expressions:'
'                    expr: value'
'                    type: string'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Inner Join 0 to 1'
'          condition expressions:'
'            0 {VALUE._col0}'
'            1 {VALUE._col1}'
'          handleSkewJoin: true'
'          outputColumnNames: _col0, _col5'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col5'
'                  type: string'
'            outputColumnNames: _col0, _col1'
'            Select Operator'
'              expressions:'
'                    expr: UDFToInteger(_col0)'
'                    type: int'
'                    expr: _col1'
'                    type: string'
'              outputColumnNames: _col0, _col1'
'              File Output Operator'
'                compressed: false'
'                GlobalTableId: 1'
'                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'
'                    name: skewjoin.dest_j1'
''
'  Stage: Stage-5'
'    Conditional Operator'
''
'  Stage: Stage-6'
'    Map Reduce Local Work'
'      Alias -> Map Local Tables:'
'        1 '
'          Fetch Operator'
'            limit: -1'
'      Alias -> Map Local Operator Tree:'
'        1 '
'            HashTable Sink Operator'
'              condition expressions:'
'                0 {0_VALUE_0}'
'                1 {1_VALUE_0}'
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'              Position of Big Table: 0'
''
'  Stage: Stage-4'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        0 '
'            Map Join Operator'
'              condition map:'
'                   Inner Join 0 to 1'
'              condition expressions:'
'                0 {0_VALUE_0}'
'                1 {1_VALUE_0}'
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'              outputColumnNames: _col0, _col5'
'              Position of Big Table: 0'
'              Select Operator'
'                expressions:'
'                      expr: _col0'
'                      type: string'
'                      expr: _col5'
'                      type: string'
'                outputColumnNames: _col0, _col1'
'                Select Operator'
'                  expressions:'
'                        expr: UDFToInteger(_col0)'
'                        type: int'
'                        expr: _col1'
'                        type: string'
'                  outputColumnNames: _col0, _col1'
'                  File Output Operator'
'                    compressed: false'
'                    GlobalTableId: 1'
'                    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'
'                        name: skewjoin.dest_j1'
'      Local Work:'
'        Map Reduce Local Work'
''
'  Stage: Stage-0'
'    Move Operator'
'      tables:'
'          replace: true'
'          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'
'              name: skewjoin.dest_j1'
''
'  Stage: Stage-2'
'    Stats-Aggr Operator'
''
''
153 rows selected 
>>>  
>>>  FROM src src1 JOIN src src2 ON (src1.key = src2.key) 
INSERT OVERWRITE TABLE dest_j1 SELECT src1.key, src2.value;
'_col0','_col1'
No rows selected 
>>>  
>>>  SELECT sum(hash(key)), sum(hash(value)) FROM dest_j1;
'_c0','_c1'
'278697','101852390308'
1 row selected 
>>>  
>>>  
>>>  EXPLAIN 
SELECT /*+ STREAMTABLE(a) */ * 
FROM T1 a JOIN T2 b ON a.key = b.key 
JOIN T3 c ON b.key = c.key 
JOIN T4 d ON c.key = d.key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_JOIN (TOK_JOIN (TOK_TABREF (TOK_TABNAME T1) a) (TOK_TABREF (TOK_TABNAME T2) b) (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key))) (TOK_TABREF (TOK_TABNAME T3) c) (= (. (TOK_TABLE_OR_COL b) key) (. (TOK_TABLE_OR_COL c) key))) (TOK_TABREF (TOK_TABNAME T4) d) (= (. (TOK_TABLE_OR_COL c) key) (. (TOK_TABLE_OR_COL d) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_STREAMTABLE (TOK_HINTARGLIST a))) (TOK_SELEXPR TOK_ALLCOLREF))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 3'
'              value expressions:'
'                    expr: key'
'                    type: string'
'                    expr: val'
'                    type: string'
'        b '
'          TableScan'
'            alias: b'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 1'
'              value expressions:'
'                    expr: key'
'                    type: string'
'                    expr: val'
'                    type: string'
'        c '
'          TableScan'
'            alias: c'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 2'
'              value expressions:'
'                    expr: key'
'                    type: string'
'                    expr: val'
'                    type: string'
'        d '
'          TableScan'
'            alias: d'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 0'
'              value expressions:'
'                    expr: key'
'                    type: string'
'                    expr: val'
'                    type: string'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Inner Join 0 to 1'
'               Inner Join 1 to 2'
'               Inner Join 2 to 3'
'          condition expressions:'
'            0 {VALUE._col0} {VALUE._col1}'
'            1 {VALUE._col0} {VALUE._col1}'
'            2 {VALUE._col0} {VALUE._col1}'
'            3 {VALUE._col0} {VALUE._col1}'
'          handleSkewJoin: false'
'          outputColumnNames: _col0, _col1, _col4, _col5, _col8, _col9, _col12, _col13'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col1'
'                  type: string'
'                  expr: _col4'
'                  type: string'
'                  expr: _col5'
'                  type: string'
'                  expr: _col8'
'                  type: string'
'                  expr: _col9'
'                  type: string'
'                  expr: _col12'
'                  type: string'
'                  expr: _col13'
'                  type: string'
'            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
123 rows selected 
>>>  
>>>  SELECT /*+ STREAMTABLE(a) */ * 
FROM T1 a JOIN T2 b ON a.key = b.key 
JOIN T3 c ON b.key = c.key 
JOIN T4 d ON c.key = d.key;
'key','val','key','val','key','val','key','val'
'2','12','2','22','2','12','2','12'
1 row selected 
>>>  
>>>  EXPLAIN 
SELECT /*+ STREAMTABLE(a,c) */ * 
FROM T1 a JOIN T2 b ON a.key = b.key 
JOIN T3 c ON b.key = c.key 
JOIN T4 d ON c.key = d.key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_JOIN (TOK_JOIN (TOK_TABREF (TOK_TABNAME T1) a) (TOK_TABREF (TOK_TABNAME T2) b) (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key))) (TOK_TABREF (TOK_TABNAME T3) c) (= (. (TOK_TABLE_OR_COL b) key) (. (TOK_TABLE_OR_COL c) key))) (TOK_TABREF (TOK_TABNAME T4) d) (= (. (TOK_TABLE_OR_COL c) key) (. (TOK_TABLE_OR_COL d) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_STREAMTABLE (TOK_HINTARGLIST a c))) (TOK_SELEXPR TOK_ALLCOLREF))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 3'
'              value expressions:'
'                    expr: key'
'                    type: string'
'                    expr: val'
'                    type: string'
'        b '
'          TableScan'
'            alias: b'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 1'
'              value expressions:'
'                    expr: key'
'                    type: string'
'                    expr: val'
'                    type: string'
'        c '
'          TableScan'
'            alias: c'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 2'
'              value expressions:'
'                    expr: key'
'                    type: string'
'                    expr: val'
'                    type: string'
'        d '
'          TableScan'
'            alias: d'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: string'
'              tag: 0'
'              value expressions:'
'                    expr: key'
'                    type: string'
'                    expr: val'
'                    type: string'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Inner Join 0 to 1'
'               Inner Join 1 to 2'
'               Inner Join 2 to 3'
'          condition expressions:'
'            0 {VALUE._col0} {VALUE._col1}'
'            1 {VALUE._col0} {VALUE._col1}'
'            2 {VALUE._col0} {VALUE._col1}'
'            3 {VALUE._col0} {VALUE._col1}'
'          handleSkewJoin: false'
'          outputColumnNames: _col0, _col1, _col4, _col5, _col8, _col9, _col12, _col13'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col1'
'                  type: string'
'                  expr: _col4'
'                  type: string'
'                  expr: _col5'
'                  type: string'
'                  expr: _col8'
'                  type: string'
'                  expr: _col9'
'                  type: string'
'                  expr: _col12'
'                  type: string'
'                  expr: _col13'
'                  type: string'
'            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
123 rows selected 
>>>  
>>>  SELECT /*+ STREAMTABLE(a,c) */ * 
FROM T1 a JOIN T2 b ON a.key = b.key 
JOIN T3 c ON b.key = c.key 
JOIN T4 d ON c.key = d.key;
'key','val','key','val','key','val','key','val'
'2','12','2','22','2','12','2','12'
1 row selected 
>>>  
>>>  
>>>  EXPLAIN FROM T1 a JOIN src c ON c.key+1=a.key SELECT /*+ STREAMTABLE(a) */ sum(hash(a.key)), sum(hash(a.val)), sum(hash(c.key));
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME T1) a) (TOK_TABREF (TOK_TABNAME src) c) (= (+ (. (TOK_TABLE_OR_COL c) key) 1) (. (TOK_TABLE_OR_COL a) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_STREAMTABLE (TOK_HINTARGLIST a))) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL a) key)))) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL a) val)))) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL c) key)))))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-2 depends on stages: Stage-1'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: UDFToDouble(key)'
'                    type: double'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: UDFToDouble(key)'
'                    type: double'
'              tag: 1'
'              value expressions:'
'                    expr: key'
'                    type: string'
'                    expr: val'
'                    type: string'
'        c '
'          TableScan'
'            alias: c'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: (key + 1)'
'                    type: double'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: (key + 1)'
'                    type: double'
'              tag: 0'
'              value expressions:'
'                    expr: key'
'                    type: string'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Inner Join 0 to 1'
'          condition expressions:'
'            0 {VALUE._col0} {VALUE._col1}'
'            1 {VALUE._col0}'
'          handleSkewJoin: false'
'          outputColumnNames: _col0, _col1, _col4'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col1'
'                  type: string'
'                  expr: _col4'
'                  type: string'
'            outputColumnNames: _col0, _col1, _col4'
'            Group By Operator'
'              aggregations:'
'                    expr: sum(hash(_col0))'
'                    expr: sum(hash(_col1))'
'                    expr: sum(hash(_col4))'
'              bucketGroup: false'
'              mode: hash'
'              outputColumnNames: _col0, _col1, _col2'
'              File Output Operator'
'                compressed: false'
'                GlobalTableId: 0'
'                table:'
'                    input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                    output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'            Reduce Output Operator'
'              sort order: '
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: bigint'
'                    expr: _col1'
'                    type: bigint'
'                    expr: _col2'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'                expr: sum(VALUE._col1)'
'                expr: sum(VALUE._col2)'
'          bucketGroup: false'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1, _col2'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: bigint'
'                  expr: _col1'
'                  type: bigint'
'                  expr: _col2'
'                  type: bigint'
'            outputColumnNames: _col0, _col1, _col2'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
121 rows selected 
>>>  FROM T1 a JOIN src c ON c.key+1=a.key SELECT /*+ STREAMTABLE(a) */ sum(hash(a.key)), sum(hash(a.val)), sum(hash(c.key));
'_c1','_c2','_c3'
'198','6274','194'
1 row selected 
>>>  
>>>  EXPLAIN FROM 
(SELECT src.* FROM src) x 
JOIN 
(SELECT src.* FROM src) Y 
ON (x.key = Y.key) 
SELECT sum(hash(Y.key)), sum(hash(Y.value));
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_ALLCOLREF (TOK_TABNAME src)))))) x) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_ALLCOLREF (TOK_TABNAME src)))))) Y) (= (. (TOK_TABLE_OR_COL x) key) (. (TOK_TABLE_OR_COL Y) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL Y) key)))) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL Y) value)))))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-5 depends on stages: Stage-1 , consists of Stage-6'
'  Stage-6'
'  Stage-4 depends on stages: Stage-6'
'  Stage-2 depends on stages: Stage-1, Stage-4'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        x:src '
'          TableScan'
'            alias: src'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: string'
'              outputColumnNames: _col0'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: _col0'
'                      type: string'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: _col0'
'                      type: string'
'                tag: 0'
'        y:src '
'          TableScan'
'            alias: src'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: string'
'                    expr: value'
'                    type: string'
'              outputColumnNames: _col0, _col1'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: _col0'
'                      type: string'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: _col0'
'                      type: string'
'                tag: 1'
'                value expressions:'
'                      expr: _col0'
'                      type: string'
'                      expr: _col1'
'                      type: string'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Inner Join 0 to 1'
'          condition expressions:'
'            0 '
'            1 {VALUE._col0} {VALUE._col1}'
'          handleSkewJoin: true'
'          outputColumnNames: _col2, _col3'
'          Select Operator'
'            expressions:'
'                  expr: _col2'
'                  type: string'
'                  expr: _col3'
'                  type: string'
'            outputColumnNames: _col2, _col3'
'            Group By Operator'
'              aggregations:'
'                    expr: sum(hash(_col2))'
'                    expr: sum(hash(_col3))'
'              bucketGroup: false'
'              mode: hash'
'              outputColumnNames: _col0, _col1'
'              File Output Operator'
'                compressed: false'
'                GlobalTableId: 0'
'                table:'
'                    input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                    output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-5'
'    Conditional Operator'
''
'  Stage: Stage-6'
'    Map Reduce Local Work'
'      Alias -> Map Local Tables:'
'        1 '
'          Fetch Operator'
'            limit: -1'
'      Alias -> Map Local Operator Tree:'
'        1 '
'            HashTable Sink Operator'
'              condition expressions:'
'                0 '
'                1 {1_VALUE_0} {1_VALUE_1}'
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'              Position of Big Table: 0'
''
'  Stage: Stage-4'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        0 '
'            Map Join Operator'
'              condition map:'
'                   Inner Join 0 to 1'
'              condition expressions:'
'                0 '
'                1 {1_VALUE_0} {1_VALUE_1}'
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'              outputColumnNames: _col2, _col3'
'              Position of Big Table: 0'
'              Select Operator'
'                expressions:'
'                      expr: _col2'
'                      type: string'
'                      expr: _col3'
'                      type: string'
'                outputColumnNames: _col2, _col3'
'                Group By Operator'
'                  aggregations:'
'                        expr: sum(hash(_col2))'
'                        expr: sum(hash(_col3))'
'                  bucketGroup: false'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  File Output Operator'
'                    compressed: false'
'                    GlobalTableId: 0'
'                    table:'
'                        input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                        output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
'      Local Work:'
'        Map Reduce Local Work'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'            Reduce Output Operator'
'              sort order: '
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: bigint'
'                    expr: _col1'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'                expr: sum(VALUE._col1)'
'          bucketGroup: false'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: bigint'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
185 rows selected 
>>>  
>>>  FROM 
(SELECT src.* FROM src) x 
JOIN 
(SELECT src.* FROM src) Y 
ON (x.key = Y.key) 
SELECT sum(hash(Y.key)), sum(hash(Y.value));
'_c0','_c1'
'44481300','101852390308'
1 row selected 
>>>  
>>>  
>>>  EXPLAIN FROM 
(SELECT src.* FROM src) x 
JOIN 
(SELECT src.* FROM src) Y 
ON (x.key = Y.key and substring(x.value, 5)=substring(y.value, 5)+1) 
SELECT sum(hash(Y.key)), sum(hash(Y.value));
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_ALLCOLREF (TOK_TABNAME src)))))) x) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_ALLCOLREF (TOK_TABNAME src)))))) Y) (and (= (. (TOK_TABLE_OR_COL x) key) (. (TOK_TABLE_OR_COL Y) key)) (= (TOK_FUNCTION substring (. (TOK_TABLE_OR_COL x) value) 5) (+ (TOK_FUNCTION substring (. (TOK_TABLE_OR_COL y) value) 5) 1))))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL Y) key)))) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL Y) value)))))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-5 depends on stages: Stage-1 , consists of Stage-6'
'  Stage-6'
'  Stage-4 depends on stages: Stage-6'
'  Stage-2 depends on stages: Stage-1, Stage-4'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        x:src '
'          TableScan'
'            alias: src'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: string'
'                    expr: value'
'                    type: string'
'              outputColumnNames: _col0, _col1'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: _col0'
'                      type: string'
'                      expr: UDFToDouble(substring(_col1, 5))'
'                      type: double'
'                sort order: ++'
'                Map-reduce partition columns:'
'                      expr: _col0'
'                      type: string'
'                      expr: UDFToDouble(substring(_col1, 5))'
'                      type: double'
'                tag: 0'
'        y:src '
'          TableScan'
'            alias: src'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: string'
'                    expr: value'
'                    type: string'
'              outputColumnNames: _col0, _col1'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: _col0'
'                      type: string'
'                      expr: (substring(_col1, 5) + 1)'
'                      type: double'
'                sort order: ++'
'                Map-reduce partition columns:'
'                      expr: _col0'
'                      type: string'
'                      expr: (substring(_col1, 5) + 1)'
'                      type: double'
'                tag: 1'
'                value expressions:'
'                      expr: _col0'
'                      type: string'
'                      expr: _col1'
'                      type: string'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Inner Join 0 to 1'
'          condition expressions:'
'            0 '
'            1 {VALUE._col0} {VALUE._col1}'
'          handleSkewJoin: true'
'          outputColumnNames: _col2, _col3'
'          Select Operator'
'            expressions:'
'                  expr: _col2'
'                  type: string'
'                  expr: _col3'
'                  type: string'
'            outputColumnNames: _col2, _col3'
'            Group By Operator'
'              aggregations:'
'                    expr: sum(hash(_col2))'
'                    expr: sum(hash(_col3))'
'              bucketGroup: false'
'              mode: hash'
'              outputColumnNames: _col0, _col1'
'              File Output Operator'
'                compressed: false'
'                GlobalTableId: 0'
'                table:'
'                    input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                    output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-5'
'    Conditional Operator'
''
'  Stage: Stage-6'
'    Map Reduce Local Work'
'      Alias -> Map Local Tables:'
'        1 '
'          Fetch Operator'
'            limit: -1'
'      Alias -> Map Local Operator Tree:'
'        1 '
'            HashTable Sink Operator'
'              condition expressions:'
'                0 '
'                1 {1_VALUE_0} {1_VALUE_1}'
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0], Column[joinkey1]]'
'                1 [Column[joinkey0], Column[joinkey1]]'
'              Position of Big Table: 0'
''
'  Stage: Stage-4'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        0 '
'            Map Join Operator'
'              condition map:'
'                   Inner Join 0 to 1'
'              condition expressions:'
'                0 '
'                1 {1_VALUE_0} {1_VALUE_1}'
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0], Column[joinkey1]]'
'                1 [Column[joinkey0], Column[joinkey1]]'
'              outputColumnNames: _col2, _col3'
'              Position of Big Table: 0'
'              Select Operator'
'                expressions:'
'                      expr: _col2'
'                      type: string'
'                      expr: _col3'
'                      type: string'
'                outputColumnNames: _col2, _col3'
'                Group By Operator'
'                  aggregations:'
'                        expr: sum(hash(_col2))'
'                        expr: sum(hash(_col3))'
'                  bucketGroup: false'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  File Output Operator'
'                    compressed: false'
'                    GlobalTableId: 0'
'                    table:'
'                        input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                        output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
'      Local Work:'
'        Map Reduce Local Work'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'            Reduce Output Operator'
'              sort order: '
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: bigint'
'                    expr: _col1'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'                expr: sum(VALUE._col1)'
'          bucketGroup: false'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: bigint'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
195 rows selected 
>>>  
>>>  FROM 
(SELECT src.* FROM src) x 
JOIN 
(SELECT src.* FROM src) Y 
ON (x.key = Y.key and substring(x.value, 5)=substring(y.value, 5)+1) 
SELECT sum(hash(Y.key)), sum(hash(Y.value));
'_c0','_c1'
'',''
1 row selected 
>>>  
>>>  
>>>  EXPLAIN 
SELECT sum(hash(src1.c1)), sum(hash(src2.c4)) 
FROM 
(SELECT src.key as c1, src.value as c2 from src) src1 
JOIN 
(SELECT src.key as c3, src.value as c4 from src) src2 
ON src1.c1 = src2.c3 AND src1.c1 < 100 
JOIN 
(SELECT src.key as c5, src.value as c6 from src) src3 
ON src1.c1 = src3.c5 AND src3.c5 < 80;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) key) c1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) value) c2)))) src1) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) key) c3) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) value) c4)))) src2) (AND (= (. (TOK_TABLE_OR_COL src1) c1) (. (TOK_TABLE_OR_COL src2) c3)) (< (. (TOK_TABLE_OR_COL src1) c1) 100))) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) key) c5) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) value) c6)))) src3) (AND (= (. (TOK_TABLE_OR_COL src1) c1) (. (TOK_TABLE_OR_COL src3) c5)) (< (. (TOK_TABLE_OR_COL src3) c5) 80)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL src1) c1)))) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL src2) c4)))))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-7 depends on stages: Stage-1 , consists of Stage-8, Stage-9'
'  Stage-8'
'  Stage-5 depends on stages: Stage-8'
'  Stage-2 depends on stages: Stage-1, Stage-5, Stage-6'
'  Stage-9'
'  Stage-6 depends on stages: Stage-9'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        src1:src '
'          TableScan'
'            alias: src'
'            Filter Operator'
'              predicate:'
'                  expr: ((key < 100.0) and (key < 80.0))'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: string'
'                outputColumnNames: _col0'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: string'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: string'
'                  tag: 0'
'                  value expressions:'
'                        expr: _col0'
'                        type: string'
'        src2:src '
'          TableScan'
'            alias: src'
'            Filter Operator'
'              predicate:'
'                  expr: ((key < 100.0) and (key < 80.0))'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: string'
'                      expr: value'
'                      type: string'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: string'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: string'
'                  tag: 1'
'                  value expressions:'
'                        expr: _col1'
'                        type: string'
'        src3:src '
'          TableScan'
'            alias: src'
'            Filter Operator'
'              predicate:'
'                  expr: ((key < 80.0) and (key < 100.0))'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: string'
'                outputColumnNames: _col0'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: string'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: string'
'                  tag: 2'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Inner Join 0 to 1'
'               Inner Join 0 to 2'
'          condition expressions:'
'            0 {VALUE._col0}'
'            1 {VALUE._col1}'
'            2 '
'          handleSkewJoin: true'
'          outputColumnNames: _col0, _col3'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col3'
'                  type: string'
'            outputColumnNames: _col0, _col3'
'            Group By Operator'
'              aggregations:'
'                    expr: sum(hash(_col0))'
'                    expr: sum(hash(_col3))'
'              bucketGroup: false'
'              mode: hash'
'              outputColumnNames: _col0, _col1'
'              File Output Operator'
'                compressed: false'
'                GlobalTableId: 0'
'                table:'
'                    input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                    output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-7'
'    Conditional Operator'
''
'  Stage: Stage-8'
'    Map Reduce Local Work'
'      Alias -> Map Local Tables:'
'        1 '
'          Fetch Operator'
'            limit: -1'
'        2 '
'          Fetch Operator'
'            limit: -1'
'      Alias -> Map Local Operator Tree:'
'        1 '
'            HashTable Sink Operator'
'              condition expressions:'
'                0 {0_VALUE_0}'
'                1 {1_VALUE_0}'
'                2 '
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'                2 [Column[joinkey0]]'
'              Position of Big Table: 0'
'        2 '
'            HashTable Sink Operator'
'              condition expressions:'
'                0 {0_VALUE_0}'
'                1 {1_VALUE_0}'
'                2 '
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'                2 [Column[joinkey0]]'
'              Position of Big Table: 0'
''
'  Stage: Stage-5'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        0 '
'            Map Join Operator'
'              condition map:'
'                   Inner Join 0 to 1'
'                   Inner Join 0 to 2'
'              condition expressions:'
'                0 {0_VALUE_0}'
'                1 {1_VALUE_0}'
'                2 '
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'                2 [Column[joinkey0]]'
'              outputColumnNames: _col0, _col3'
'              Position of Big Table: 0'
'              Select Operator'
'                expressions:'
'                      expr: _col0'
'                      type: string'
'                      expr: _col3'
'                      type: string'
'                outputColumnNames: _col0, _col3'
'                Group By Operator'
'                  aggregations:'
'                        expr: sum(hash(_col0))'
'                        expr: sum(hash(_col3))'
'                  bucketGroup: false'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  File Output Operator'
'                    compressed: false'
'                    GlobalTableId: 0'
'                    table:'
'                        input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                        output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
'      Local Work:'
'        Map Reduce Local Work'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'            Reduce Output Operator'
'              sort order: '
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: bigint'
'                    expr: _col1'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'                expr: sum(VALUE._col1)'
'          bucketGroup: false'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: bigint'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-9'
'    Map Reduce Local Work'
'      Alias -> Map Local Tables:'
'        0 '
'          Fetch Operator'
'            limit: -1'
'        2 '
'          Fetch Operator'
'            limit: -1'
'      Alias -> Map Local Operator Tree:'
'        0 '
'            HashTable Sink Operator'
'              condition expressions:'
'                0 {0_VALUE_0}'
'                1 {1_VALUE_0}'
'                2 '
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'                2 [Column[joinkey0]]'
'              Position of Big Table: 1'
'        2 '
'            HashTable Sink Operator'
'              condition expressions:'
'                0 {0_VALUE_0}'
'                1 {1_VALUE_0}'
'                2 '
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'                2 [Column[joinkey0]]'
'              Position of Big Table: 1'
''
'  Stage: Stage-6'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        1 '
'            Map Join Operator'
'              condition map:'
'                   Inner Join 0 to 1'
'                   Inner Join 0 to 2'
'              condition expressions:'
'                0 {0_VALUE_0}'
'                1 {1_VALUE_0}'
'                2 '
'              handleSkewJoin: false'
'              keys:'
'                0 [Column[joinkey0]]'
'                1 [Column[joinkey0]]'
'                2 [Column[joinkey0]]'
'              outputColumnNames: _col0, _col3'
'              Position of Big Table: 1'
'              Select Operator'
'                expressions:'
'                      expr: _col0'
'                      type: string'
'                      expr: _col3'
'                      type: string'
'                outputColumnNames: _col0, _col3'
'                Group By Operator'
'                  aggregations:'
'                        expr: sum(hash(_col0))'
'                        expr: sum(hash(_col3))'
'                  bucketGroup: false'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  File Output Operator'
'                    compressed: false'
'                    GlobalTableId: 0'
'                    table:'
'                        input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                        output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
'      Local Work:'
'        Map Reduce Local Work'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
316 rows selected 
>>>  
>>>  SELECT sum(hash(src1.c1)), sum(hash(src2.c4)) 
FROM 
(SELECT src.key as c1, src.value as c2 from src) src1 
JOIN 
(SELECT src.key as c3, src.value as c4 from src) src2 
ON src1.c1 = src2.c3 AND src1.c1 < 100 
JOIN 
(SELECT src.key as c5, src.value as c6 from src) src3 
ON src1.c1 = src3.c5 AND src3.c5 < 80;
'_c0','_c1'
'293143','-136853010385'
1 row selected 
>>>  
>>>  EXPLAIN 
SELECT /*+ mapjoin(v)*/ sum(hash(k.key)), sum(hash(v.val)) FROM T1 k LEFT OUTER JOIN T1 v ON k.key+1=v.key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_TABREF (TOK_TABNAME T1) k) (TOK_TABREF (TOK_TABNAME T1) v) (= (+ (. (TOK_TABLE_OR_COL k) key) 1) (. (TOK_TABLE_OR_COL v) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST v))) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL k) key)))) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION hash (. (TOK_TABLE_OR_COL v) val)))))))'
''
'STAGE DEPENDENCIES:'
'  Stage-4 is a root stage'
'  Stage-1 depends on stages: Stage-4'
'  Stage-2 depends on stages: Stage-1'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-4'
'    Map Reduce Local Work'
'      Alias -> Map Local Tables:'
'        v '
'          Fetch Operator'
'            limit: -1'
'      Alias -> Map Local Operator Tree:'
'        v '
'          TableScan'
'            alias: v'
'            HashTable Sink Operator'
'              condition expressions:'
'                0 {key}'
'                1 {val}'
'              handleSkewJoin: false'
'              keys:'
'                0 [class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[key], Const int 1()]'
'                1 [class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[key]()]'
'              Position of Big Table: 0'
''
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        k '
'          TableScan'
'            alias: k'
'            Map Join Operator'
'              condition map:'
'                   Left Outer Join0 to 1'
'              condition expressions:'
'                0 {key}'
'                1 {val}'
'              handleSkewJoin: false'
'              keys:'
'                0 [class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[key], Const int 1()]'
'                1 [class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[key]()]'
'              outputColumnNames: _col0, _col5'
'              Position of Big Table: 0'
'              File Output Operator'
'                compressed: false'
'                GlobalTableId: 0'
'                table:'
'                    input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                    output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
'      Local Work:'
'        Map Reduce Local Work'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col5'
'                  type: string'
'            outputColumnNames: _col0, _col5'
'            Select Operator'
'              expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col5'
'                    type: string'
'              outputColumnNames: _col0, _col5'
'              Group By Operator'
'                aggregations:'
'                      expr: sum(hash(_col0))'
'                      expr: sum(hash(_col5))'
'                bucketGroup: false'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  sort order: '
'                  tag: -1'
'                  value expressions:'
'                        expr: _col0'
'                        type: bigint'
'                        expr: _col1'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'                expr: sum(VALUE._col1)'
'          bucketGroup: false'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: bigint'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
117 rows selected 
>>>  SELECT /*+ mapjoin(v)*/ sum(hash(k.key)), sum(hash(v.val)) FROM T1 k LEFT OUTER JOIN T1 v ON k.key+1=v.key;
'_c1','_c2'
'372','6320'
1 row selected 
>>>  
>>>  select /*+ mapjoin(k)*/ sum(hash(k.key)), sum(hash(v.val)) from T1 k join T1 v on k.key=v.val;
'_c1','_c2'
'',''
1 row selected 
>>>  
>>>  select /*+ mapjoin(k)*/ sum(hash(k.key)), sum(hash(v.val)) from T1 k join T1 v on k.key=v.key;
'_c1','_c2'
'429','12643'
1 row selected 
>>>  
>>>  select sum(hash(k.key)), sum(hash(v.val)) from T1 k join T1 v on k.key=v.key;
'_c0','_c1'
'429','12643'
1 row selected 
>>>  
>>>  select count(1) from  T1 a join T1 b on a.key = b.key;
'_c0'
'8'
1 row selected 
>>>  
>>>  FROM T1 a LEFT OUTER JOIN T2 c ON c.key+1=a.key SELECT sum(hash(a.key)), sum(hash(a.val)), sum(hash(c.key));
'_c0','_c1','_c2'
'317','9462','50'
1 row selected 
>>>  
>>>  FROM T1 a RIGHT OUTER JOIN T2 c ON c.key+1=a.key SELECT /*+ STREAMTABLE(a) */ sum(hash(a.key)), sum(hash(a.val)), sum(hash(c.key));
'_c1','_c2','_c3'
'51','1570','318'
1 row selected 
>>>  
>>>  FROM T1 a FULL OUTER JOIN T2 c ON c.key+1=a.key SELECT /*+ STREAMTABLE(a) */ sum(hash(a.key)), sum(hash(a.val)), sum(hash(c.key));
'_c1','_c2','_c3'
'317','9462','318'
1 row selected 
>>>  
>>>  SELECT sum(hash(src1.key)), sum(hash(src1.val)), sum(hash(src2.key)) FROM T1 src1 LEFT OUTER JOIN T2 src2 ON src1.key+1 = src2.key RIGHT OUTER JOIN T2 src3 ON src2.key = src3.key;
'_c0','_c1','_c2'
'370','11003','377'
1 row selected 
>>>  
>>>  SELECT sum(hash(src1.key)), sum(hash(src1.val)), sum(hash(src2.key)) FROM T1 src1 JOIN T2 src2 ON src1.key+1 = src2.key JOIN T2 src3 ON src2.key = src3.key;
'_c0','_c1','_c2'
'370','11003','377'
1 row selected 
>>>  
>>>  select /*+ mapjoin(v)*/ sum(hash(k.key)), sum(hash(v.val)) from T1 k left outer join T1 v on k.key+1=v.key;
'_c1','_c2'
'372','6320'
1 row selected 
>>>  
>>>  
>>>  
>>>  
>>>  
>>>  
>>>  !record