join29.q.out | searchcode

/tags/release-0.0.0-rc0/hive/external/ql/src/test/results/clientpositive/join29.q.out

#
text | 297 lines | 285 code | 12 blank | 0 comment | 0 complexity | 30d2440f53c332cf5f7af0be6ebb9541 MD5 | raw file
Possible License(s): Apache-2.0, BSD-3-Clause, JSON, CPL-1.0

PREHOOK: query: CREATE TABLE dest_j1(key STRING, cnt1 INT, cnt2 INT)
PREHOOK: type: CREATETABLE
POSTHOOK: query: CREATE TABLE dest_j1(key STRING, cnt1 INT, cnt2 INT)
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@dest_j1
PREHOOK: query: EXPLAIN 
INSERT OVERWRITE TABLE dest_j1 
SELECT /*+ MAPJOIN(subq1) */ subq1.key, subq1.cnt, subq2.cnt
FROM (select x.key, count(1) as cnt from src1 x group by x.key) subq1 JOIN 
     (select y.key, count(1) as cnt from src y group by y.key) subq2 ON (subq1.key = subq2.key)
PREHOOK: type: QUERY
POSTHOOK: query: EXPLAIN 
INSERT OVERWRITE TABLE dest_j1 
SELECT /*+ MAPJOIN(subq1) */ subq1.key, subq1.cnt, subq2.cnt
FROM (select x.key, count(1) as cnt from src1 x group by x.key) subq1 JOIN 
     (select y.key, count(1) as cnt from src y group by y.key) subq2 ON (subq1.key = subq2.key)
POSTHOOK: type: QUERY
ABSTRACT SYNTAX TREE:
  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src1) x)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) key)) (TOK_SELEXPR (TOK_FUNCTION count 1) cnt)) (TOK_GROUPBY (. (TOK_TABLE_OR_COL x) key)))) subq1) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) y)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL y) key)) (TOK_SELEXPR (TOK_FUNCTION count 1) cnt)) (TOK_GROUPBY (. (TOK_TABLE_OR_COL y) key)))) subq2) (= (. (TOK_TABLE_OR_COL subq1) key) (. (TOK_TABLE_OR_COL subq2) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest_j1))) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST subq1))) (TOK_SELEXPR (. (TOK_TABLE_OR_COL subq1) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL subq1) cnt)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL subq2) cnt)))))

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-9 depends on stages: Stage-1, Stage-7
  Stage-2 depends on stages: Stage-9
  Stage-6 depends on stages: Stage-2 , consists of Stage-5, Stage-4
  Stage-5
  Stage-0 depends on stages: Stage-5, Stage-4
  Stage-3 depends on stages: Stage-0
  Stage-4
  Stage-7 is a root stage

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
        subq2:y 
          TableScan
            alias: y
            Select Operator
              expressions:
                    expr: key
                    type: string
              outputColumnNames: key
              Group By Operator
                aggregations:
                      expr: count(1)
                bucketGroup: false
                keys:
                      expr: key
                      type: string
                mode: hash
                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: bigint
      Reduce Operator Tree:
        Group By Operator
          aggregations:
                expr: count(VALUE._col0)
          bucketGroup: false
          keys:
                expr: KEY._col0
                type: string
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Select Operator
            expressions:
                  expr: _col0
                  type: string
                  expr: _col1
                  type: bigint
            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-9
    Map Reduce Local Work
      Alias -> Map Local Tables:
        file:/tmp/sdong/hive_2011-02-10_16-04-29_506_6626668492500795/-mr-10004 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        file:/tmp/sdong/hive_2011-02-10_16-04-29_506_6626668492500795/-mr-10004 
            HashTable Sink Operator
              condition expressions:
                0 {_col0} {_col1}
                1 {_col1}
              handleSkewJoin: false
              keys:
                0 [Column[_col0]]
                1 [Column[_col0]]
              Position of Big Table: 1

  Stage: Stage-2
    Map Reduce
      Alias -> Map Operator Tree:
        file:/tmp/sdong/hive_2011-02-10_16-04-29_506_6626668492500795/-mr-10002 
            Map Join Operator
              condition map:
                   Inner Join 0 to 1
              condition expressions:
                0 {_col0} {_col1}
                1 {_col1}
              handleSkewJoin: false
              keys:
                0 [Column[_col0]]
                1 [Column[_col0]]
              outputColumnNames: _col0, _col1, _col3
              Position of Big Table: 1
              Select Operator
                expressions:
                      expr: _col0
                      type: string
                      expr: _col1
                      type: bigint
                      expr: _col3
                      type: bigint
                outputColumnNames: _col0, _col1, _col3
                Select Operator
                  expressions:
                        expr: _col0
                        type: string
                        expr: _col1
                        type: bigint
                        expr: _col3
                        type: bigint
                  outputColumnNames: _col0, _col1, _col2
                  Select Operator
                    expressions:
                          expr: _col0
                          type: string
                          expr: UDFToInteger(_col1)
                          type: int
                          expr: UDFToInteger(_col2)
                          type: int
                    outputColumnNames: _col0, _col1, _col2
                    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: default.dest_j1
      Local Work:
        Map Reduce Local Work

  Stage: Stage-6
    Conditional Operator

  Stage: Stage-5
    Move Operator
      files:
          hdfs directory: true
          destination: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-04-29_506_6626668492500795/-ext-10000

  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: default.dest_j1

  Stage: Stage-3
    Stats-Aggr Operator

  Stage: Stage-4
    Map Reduce
      Alias -> Map Operator Tree:
        pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-04-29_506_6626668492500795/-ext-10003 
            File Output Operator
              compressed: false
              GlobalTableId: 0
              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: default.dest_j1

  Stage: Stage-7
    Map Reduce
      Alias -> Map Operator Tree:
        subq1:x 
          TableScan
            alias: x
            Select Operator
              expressions:
                    expr: key
                    type: string
              outputColumnNames: key
              Group By Operator
                aggregations:
                      expr: count(1)
                bucketGroup: false
                keys:
                      expr: key
                      type: string
                mode: hash
                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: bigint
      Reduce Operator Tree:
        Group By Operator
          aggregations:
                expr: count(VALUE._col0)
          bucketGroup: false
          keys:
                expr: KEY._col0
                type: string
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Select Operator
            expressions:
                  expr: _col0
                  type: string
                  expr: _col1
                  type: bigint
            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


PREHOOK: query: INSERT OVERWRITE TABLE dest_j1 
SELECT /*+ MAPJOIN(subq1) */ subq1.key, subq1.cnt, subq2.cnt
FROM (select x.key, count(1) as cnt from src1 x group by x.key) subq1 JOIN 
     (select y.key, count(1) as cnt from src y group by y.key) subq2 ON (subq1.key = subq2.key)
PREHOOK: type: QUERY
PREHOOK: Input: default@src
PREHOOK: Input: default@src1
PREHOOK: Output: default@dest_j1
POSTHOOK: query: INSERT OVERWRITE TABLE dest_j1 
SELECT /*+ MAPJOIN(subq1) */ subq1.key, subq1.cnt, subq2.cnt
FROM (select x.key, count(1) as cnt from src1 x group by x.key) subq1 JOIN 
     (select y.key, count(1) as cnt from src y group by y.key) subq2 ON (subq1.key = subq2.key)
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
POSTHOOK: Input: default@src1
POSTHOOK: Output: default@dest_j1
POSTHOOK: Lineage: dest_j1.cnt1 EXPRESSION [(src1)x.null, ]
POSTHOOK: Lineage: dest_j1.cnt2 EXPRESSION [(src)y.null, ]
POSTHOOK: Lineage: dest_j1.key SIMPLE [(src1)x.FieldSchema(name:key, type:string, comment:default), ]
PREHOOK: query: select * from dest_j1 x order by x.key
PREHOOK: type: QUERY
PREHOOK: Input: default@dest_j1
PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-04-43_398_9188586114177273945/-mr-10000
POSTHOOK: query: select * from dest_j1 x order by x.key
POSTHOOK: type: QUERY
POSTHOOK: Input: default@dest_j1
POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-04-43_398_9188586114177273945/-mr-10000
POSTHOOK: Lineage: dest_j1.cnt1 EXPRESSION [(src1)x.null, ]
POSTHOOK: Lineage: dest_j1.cnt2 EXPRESSION [(src)y.null, ]
POSTHOOK: Lineage: dest_j1.key SIMPLE [(src1)x.FieldSchema(name:key, type:string, comment:default), ]
128	1	3
146	1	2
150	1	1
213	1	2
224	1	2
238	1	2
255	1	2
273	1	3
278	1	2
311	1	3
369	1	3
401	1	5
406	1	4
66	1	1
98	1	2