PageRenderTime 16ms CodeModel.GetById 8ms app.highlight 6ms RepoModel.GetById 1ms app.codeStats 0ms

/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
  1PREHOOK: query: CREATE TABLE dest_j1(key STRING, cnt1 INT, cnt2 INT)
  2PREHOOK: type: CREATETABLE
  3POSTHOOK: query: CREATE TABLE dest_j1(key STRING, cnt1 INT, cnt2 INT)
  4POSTHOOK: type: CREATETABLE
  5POSTHOOK: Output: default@dest_j1
  6PREHOOK: query: EXPLAIN 
  7INSERT OVERWRITE TABLE dest_j1 
  8SELECT /*+ MAPJOIN(subq1) */ subq1.key, subq1.cnt, subq2.cnt
  9FROM (select x.key, count(1) as cnt from src1 x group by x.key) subq1 JOIN 
 10     (select y.key, count(1) as cnt from src y group by y.key) subq2 ON (subq1.key = subq2.key)
 11PREHOOK: type: QUERY
 12POSTHOOK: query: EXPLAIN 
 13INSERT OVERWRITE TABLE dest_j1 
 14SELECT /*+ MAPJOIN(subq1) */ subq1.key, subq1.cnt, subq2.cnt
 15FROM (select x.key, count(1) as cnt from src1 x group by x.key) subq1 JOIN 
 16     (select y.key, count(1) as cnt from src y group by y.key) subq2 ON (subq1.key = subq2.key)
 17POSTHOOK: type: QUERY
 18ABSTRACT SYNTAX TREE:
 19  (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)))))
 20
 21STAGE DEPENDENCIES:
 22  Stage-1 is a root stage
 23  Stage-9 depends on stages: Stage-1, Stage-7
 24  Stage-2 depends on stages: Stage-9
 25  Stage-6 depends on stages: Stage-2 , consists of Stage-5, Stage-4
 26  Stage-5
 27  Stage-0 depends on stages: Stage-5, Stage-4
 28  Stage-3 depends on stages: Stage-0
 29  Stage-4
 30  Stage-7 is a root stage
 31
 32STAGE PLANS:
 33  Stage: Stage-1
 34    Map Reduce
 35      Alias -> Map Operator Tree:
 36        subq2:y 
 37          TableScan
 38            alias: y
 39            Select Operator
 40              expressions:
 41                    expr: key
 42                    type: string
 43              outputColumnNames: key
 44              Group By Operator
 45                aggregations:
 46                      expr: count(1)
 47                bucketGroup: false
 48                keys:
 49                      expr: key
 50                      type: string
 51                mode: hash
 52                outputColumnNames: _col0, _col1
 53                Reduce Output Operator
 54                  key expressions:
 55                        expr: _col0
 56                        type: string
 57                  sort order: +
 58                  Map-reduce partition columns:
 59                        expr: _col0
 60                        type: string
 61                  tag: -1
 62                  value expressions:
 63                        expr: _col1
 64                        type: bigint
 65      Reduce Operator Tree:
 66        Group By Operator
 67          aggregations:
 68                expr: count(VALUE._col0)
 69          bucketGroup: false
 70          keys:
 71                expr: KEY._col0
 72                type: string
 73          mode: mergepartial
 74          outputColumnNames: _col0, _col1
 75          Select Operator
 76            expressions:
 77                  expr: _col0
 78                  type: string
 79                  expr: _col1
 80                  type: bigint
 81            outputColumnNames: _col0, _col1
 82            File Output Operator
 83              compressed: false
 84              GlobalTableId: 0
 85              table:
 86                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
 87                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
 88
 89  Stage: Stage-9
 90    Map Reduce Local Work
 91      Alias -> Map Local Tables:
 92        file:/tmp/sdong/hive_2011-02-10_16-04-29_506_6626668492500795/-mr-10004 
 93          Fetch Operator
 94            limit: -1
 95      Alias -> Map Local Operator Tree:
 96        file:/tmp/sdong/hive_2011-02-10_16-04-29_506_6626668492500795/-mr-10004 
 97            HashTable Sink Operator
 98              condition expressions:
 99                0 {_col0} {_col1}
100                1 {_col1}
101              handleSkewJoin: false
102              keys:
103                0 [Column[_col0]]
104                1 [Column[_col0]]
105              Position of Big Table: 1
106
107  Stage: Stage-2
108    Map Reduce
109      Alias -> Map Operator Tree:
110        file:/tmp/sdong/hive_2011-02-10_16-04-29_506_6626668492500795/-mr-10002 
111            Map Join Operator
112              condition map:
113                   Inner Join 0 to 1
114              condition expressions:
115                0 {_col0} {_col1}
116                1 {_col1}
117              handleSkewJoin: false
118              keys:
119                0 [Column[_col0]]
120                1 [Column[_col0]]
121              outputColumnNames: _col0, _col1, _col3
122              Position of Big Table: 1
123              Select Operator
124                expressions:
125                      expr: _col0
126                      type: string
127                      expr: _col1
128                      type: bigint
129                      expr: _col3
130                      type: bigint
131                outputColumnNames: _col0, _col1, _col3
132                Select Operator
133                  expressions:
134                        expr: _col0
135                        type: string
136                        expr: _col1
137                        type: bigint
138                        expr: _col3
139                        type: bigint
140                  outputColumnNames: _col0, _col1, _col2
141                  Select Operator
142                    expressions:
143                          expr: _col0
144                          type: string
145                          expr: UDFToInteger(_col1)
146                          type: int
147                          expr: UDFToInteger(_col2)
148                          type: int
149                    outputColumnNames: _col0, _col1, _col2
150                    File Output Operator
151                      compressed: false
152                      GlobalTableId: 1
153                      table:
154                          input format: org.apache.hadoop.mapred.TextInputFormat
155                          output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
156                          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
157                          name: default.dest_j1
158      Local Work:
159        Map Reduce Local Work
160
161  Stage: Stage-6
162    Conditional Operator
163
164  Stage: Stage-5
165    Move Operator
166      files:
167          hdfs directory: true
168          destination: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-04-29_506_6626668492500795/-ext-10000
169
170  Stage: Stage-0
171    Move Operator
172      tables:
173          replace: true
174          table:
175              input format: org.apache.hadoop.mapred.TextInputFormat
176              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
177              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
178              name: default.dest_j1
179
180  Stage: Stage-3
181    Stats-Aggr Operator
182
183  Stage: Stage-4
184    Map Reduce
185      Alias -> Map Operator Tree:
186        pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-04-29_506_6626668492500795/-ext-10003 
187            File Output Operator
188              compressed: false
189              GlobalTableId: 0
190              table:
191                  input format: org.apache.hadoop.mapred.TextInputFormat
192                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
193                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
194                  name: default.dest_j1
195
196  Stage: Stage-7
197    Map Reduce
198      Alias -> Map Operator Tree:
199        subq1:x 
200          TableScan
201            alias: x
202            Select Operator
203              expressions:
204                    expr: key
205                    type: string
206              outputColumnNames: key
207              Group By Operator
208                aggregations:
209                      expr: count(1)
210                bucketGroup: false
211                keys:
212                      expr: key
213                      type: string
214                mode: hash
215                outputColumnNames: _col0, _col1
216                Reduce Output Operator
217                  key expressions:
218                        expr: _col0
219                        type: string
220                  sort order: +
221                  Map-reduce partition columns:
222                        expr: _col0
223                        type: string
224                  tag: -1
225                  value expressions:
226                        expr: _col1
227                        type: bigint
228      Reduce Operator Tree:
229        Group By Operator
230          aggregations:
231                expr: count(VALUE._col0)
232          bucketGroup: false
233          keys:
234                expr: KEY._col0
235                type: string
236          mode: mergepartial
237          outputColumnNames: _col0, _col1
238          Select Operator
239            expressions:
240                  expr: _col0
241                  type: string
242                  expr: _col1
243                  type: bigint
244            outputColumnNames: _col0, _col1
245            File Output Operator
246              compressed: false
247              GlobalTableId: 0
248              table:
249                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
250                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
251
252
253PREHOOK: query: INSERT OVERWRITE TABLE dest_j1 
254SELECT /*+ MAPJOIN(subq1) */ subq1.key, subq1.cnt, subq2.cnt
255FROM (select x.key, count(1) as cnt from src1 x group by x.key) subq1 JOIN 
256     (select y.key, count(1) as cnt from src y group by y.key) subq2 ON (subq1.key = subq2.key)
257PREHOOK: type: QUERY
258PREHOOK: Input: default@src
259PREHOOK: Input: default@src1
260PREHOOK: Output: default@dest_j1
261POSTHOOK: query: INSERT OVERWRITE TABLE dest_j1 
262SELECT /*+ MAPJOIN(subq1) */ subq1.key, subq1.cnt, subq2.cnt
263FROM (select x.key, count(1) as cnt from src1 x group by x.key) subq1 JOIN 
264     (select y.key, count(1) as cnt from src y group by y.key) subq2 ON (subq1.key = subq2.key)
265POSTHOOK: type: QUERY
266POSTHOOK: Input: default@src
267POSTHOOK: Input: default@src1
268POSTHOOK: Output: default@dest_j1
269POSTHOOK: Lineage: dest_j1.cnt1 EXPRESSION [(src1)x.null, ]
270POSTHOOK: Lineage: dest_j1.cnt2 EXPRESSION [(src)y.null, ]
271POSTHOOK: Lineage: dest_j1.key SIMPLE [(src1)x.FieldSchema(name:key, type:string, comment:default), ]
272PREHOOK: query: select * from dest_j1 x order by x.key
273PREHOOK: type: QUERY
274PREHOOK: Input: default@dest_j1
275PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-04-43_398_9188586114177273945/-mr-10000
276POSTHOOK: query: select * from dest_j1 x order by x.key
277POSTHOOK: type: QUERY
278POSTHOOK: Input: default@dest_j1
279POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-04-43_398_9188586114177273945/-mr-10000
280POSTHOOK: Lineage: dest_j1.cnt1 EXPRESSION [(src1)x.null, ]
281POSTHOOK: Lineage: dest_j1.cnt2 EXPRESSION [(src)y.null, ]
282POSTHOOK: Lineage: dest_j1.key SIMPLE [(src1)x.FieldSchema(name:key, type:string, comment:default), ]
283128	1	3
284146	1	2
285150	1	1
286213	1	2
287224	1	2
288238	1	2
289255	1	2
290273	1	3
291278	1	2
292311	1	3
293369	1	3
294401	1	5
295406	1	4
29666	1	1
29798	1	2