PageRenderTime 30ms CodeModel.GetById 16ms app.highlight 7ms RepoModel.GetById 1ms app.codeStats 1ms

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

#
text | 442 lines | 430 code | 12 blank | 0 comment | 0 complexity | 77d6cc9d22d05465ff0ff8e1e7411fbc MD5 | raw file
  1PREHOOK: query: CREATE TABLE dest_j1(key STRING, value STRING, val2 STRING) STORED AS TEXTFILE
  2PREHOOK: type: CREATETABLE
  3POSTHOOK: query: CREATE TABLE dest_j1(key STRING, value STRING, val2 STRING) STORED AS TEXTFILE
  4POSTHOOK: type: CREATETABLE
  5POSTHOOK: Output: default@dest_j1
  6PREHOOK: query: EXPLAIN EXTENDED
  7INSERT OVERWRITE TABLE dest_j1
  8SELECT /*+ MAPJOIN(x) */ x.key, x.value, subq1.value
  9FROM 
 10( SELECT x.key as key, x.value as value from src x where x.key < 20
 11     UNION ALL
 12  SELECT x1.key as key, x1.value as value from src x1 where x1.key > 100
 13) subq1
 14JOIN src1 x ON (x.key = subq1.key)
 15PREHOOK: type: QUERY
 16POSTHOOK: query: EXPLAIN EXTENDED
 17INSERT OVERWRITE TABLE dest_j1
 18SELECT /*+ MAPJOIN(x) */ x.key, x.value, subq1.value
 19FROM 
 20( SELECT x.key as key, x.value as value from src x where x.key < 20
 21     UNION ALL
 22  SELECT x1.key as key, x1.value as value from src x1 where x1.key > 100
 23) subq1
 24JOIN src1 x ON (x.key = subq1.key)
 25POSTHOOK: type: QUERY
 26ABSTRACT SYNTAX TREE:
 27  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_UNION (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) x)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) key) key) (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) value) value)) (TOK_WHERE (< (. (TOK_TABLE_OR_COL x) key) 20)))) (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) x1)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL x1) key) key) (TOK_SELEXPR (. (TOK_TABLE_OR_COL x1) value) value)) (TOK_WHERE (> (. (TOK_TABLE_OR_COL x1) key) 100))))) subq1) (TOK_TABREF (TOK_TABNAME src1) x) (= (. (TOK_TABLE_OR_COL x) key) (. (TOK_TABLE_OR_COL subq1) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest_j1))) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST x))) (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) value)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL subq1) value)))))
 28
 29STAGE DEPENDENCIES:
 30  Stage-8 is a root stage
 31  Stage-1 depends on stages: Stage-8
 32  Stage-5 depends on stages: Stage-1 , consists of Stage-4, Stage-3
 33  Stage-4
 34  Stage-0 depends on stages: Stage-4, Stage-3
 35  Stage-2 depends on stages: Stage-0
 36  Stage-3
 37
 38STAGE PLANS:
 39  Stage: Stage-8
 40    Map Reduce Local Work
 41      Alias -> Map Local Tables:
 42        x 
 43          Fetch Operator
 44            limit: -1
 45      Alias -> Map Local Operator Tree:
 46        x 
 47          TableScan
 48            alias: x
 49            GatherStats: false
 50            HashTable Sink Operator
 51              condition expressions:
 52                0 {_col1}
 53                1 {key} {value}
 54              handleSkewJoin: false
 55              keys:
 56                0 [Column[_col0]]
 57                1 [Column[key]]
 58              Position of Big Table: 0
 59
 60  Stage: Stage-1
 61    Map Reduce
 62      Alias -> Map Operator Tree:
 63        null-subquery1:subq1-subquery1:x 
 64          TableScan
 65            alias: x
 66            GatherStats: false
 67            Filter Operator
 68              isSamplingPred: false
 69              predicate:
 70                  expr: (key < 20)
 71                  type: boolean
 72              Filter Operator
 73                isSamplingPred: false
 74                predicate:
 75                    expr: (key < 20)
 76                    type: boolean
 77                Select Operator
 78                  expressions:
 79                        expr: key
 80                        type: string
 81                        expr: value
 82                        type: string
 83                  outputColumnNames: _col0, _col1
 84                  Union
 85                    Map Join Operator
 86                      condition map:
 87                           Inner Join 0 to 1
 88                      condition expressions:
 89                        0 {_col1}
 90                        1 {key} {value}
 91                      handleSkewJoin: false
 92                      keys:
 93                        0 [Column[_col0]]
 94                        1 [Column[key]]
 95                      outputColumnNames: _col1, _col2, _col3
 96                      Position of Big Table: 0
 97                      Select Operator
 98                        expressions:
 99                              expr: _col1
100                              type: string
101                              expr: _col2
102                              type: string
103                              expr: _col3
104                              type: string
105                        outputColumnNames: _col1, _col2, _col3
106                        Select Operator
107                          expressions:
108                                expr: _col2
109                                type: string
110                                expr: _col3
111                                type: string
112                                expr: _col1
113                                type: string
114                          outputColumnNames: _col0, _col1, _col2
115                          File Output Operator
116                            compressed: false
117                            GlobalTableId: 1
118                            directory: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10002
119                            NumFilesPerFileSink: 1
120                            Stats Publishing Key Prefix: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10000/
121                            table:
122                                input format: org.apache.hadoop.mapred.TextInputFormat
123                                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
124                                properties:
125                                  bucket_count -1
126                                  columns key,value,val2
127                                  columns.types string:string:string
128                                  file.inputformat org.apache.hadoop.mapred.TextInputFormat
129                                  file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
130                                  location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/dest_j1
131                                  name default.dest_j1
132                                  serialization.ddl struct dest_j1 { string key, string value, string val2}
133                                  serialization.format 1
134                                  serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
135                                  transient_lastDdlTime 1297382757
136                                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
137                                name: default.dest_j1
138                            TotalFiles: 1
139                            GatherStats: true
140                            MultiFileSpray: false
141        null-subquery2:subq1-subquery2:x1 
142          TableScan
143            alias: x1
144            GatherStats: false
145            Filter Operator
146              isSamplingPred: false
147              predicate:
148                  expr: (key > 100)
149                  type: boolean
150              Filter Operator
151                isSamplingPred: false
152                predicate:
153                    expr: (key > 100)
154                    type: boolean
155                Select Operator
156                  expressions:
157                        expr: key
158                        type: string
159                        expr: value
160                        type: string
161                  outputColumnNames: _col0, _col1
162                  Union
163                    Map Join Operator
164                      condition map:
165                           Inner Join 0 to 1
166                      condition expressions:
167                        0 {_col1}
168                        1 {key} {value}
169                      handleSkewJoin: false
170                      keys:
171                        0 [Column[_col0]]
172                        1 [Column[key]]
173                      outputColumnNames: _col1, _col2, _col3
174                      Position of Big Table: 0
175                      Select Operator
176                        expressions:
177                              expr: _col1
178                              type: string
179                              expr: _col2
180                              type: string
181                              expr: _col3
182                              type: string
183                        outputColumnNames: _col1, _col2, _col3
184                        Select Operator
185                          expressions:
186                                expr: _col2
187                                type: string
188                                expr: _col3
189                                type: string
190                                expr: _col1
191                                type: string
192                          outputColumnNames: _col0, _col1, _col2
193                          File Output Operator
194                            compressed: false
195                            GlobalTableId: 1
196                            directory: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10002
197                            NumFilesPerFileSink: 1
198                            Stats Publishing Key Prefix: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10000/
199                            table:
200                                input format: org.apache.hadoop.mapred.TextInputFormat
201                                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
202                                properties:
203                                  bucket_count -1
204                                  columns key,value,val2
205                                  columns.types string:string:string
206                                  file.inputformat org.apache.hadoop.mapred.TextInputFormat
207                                  file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
208                                  location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/dest_j1
209                                  name default.dest_j1
210                                  serialization.ddl struct dest_j1 { string key, string value, string val2}
211                                  serialization.format 1
212                                  serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
213                                  transient_lastDdlTime 1297382757
214                                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
215                                name: default.dest_j1
216                            TotalFiles: 1
217                            GatherStats: true
218                            MultiFileSpray: false
219      Local Work:
220        Map Reduce Local Work
221      Needs Tagging: false
222      Path -> Alias:
223        pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/src [null-subquery1:subq1-subquery1:x, null-subquery2:subq1-subquery2:x1]
224      Path -> Partition:
225        pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/src 
226          Partition
227            base file name: src
228            input format: org.apache.hadoop.mapred.TextInputFormat
229            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
230            properties:
231              bucket_count -1
232              columns key,value
233              columns.types string:string
234              file.inputformat org.apache.hadoop.mapred.TextInputFormat
235              file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
236              location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/src
237              name default.src
238              serialization.ddl struct src { string key, string value}
239              serialization.format 1
240              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
241              transient_lastDdlTime 1297378968
242            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
243          
244              input format: org.apache.hadoop.mapred.TextInputFormat
245              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
246              properties:
247                bucket_count -1
248                columns key,value
249                columns.types string:string
250                file.inputformat org.apache.hadoop.mapred.TextInputFormat
251                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
252                location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/src
253                name default.src
254                serialization.ddl struct src { string key, string value}
255                serialization.format 1
256                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
257                transient_lastDdlTime 1297378968
258              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
259              name: default.src
260            name: default.src
261
262  Stage: Stage-5
263    Conditional Operator
264
265  Stage: Stage-4
266    Move Operator
267      files:
268          hdfs directory: true
269          source: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10002
270          destination: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10000
271
272  Stage: Stage-0
273    Move Operator
274      tables:
275          replace: true
276          source: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10000
277          table:
278              input format: org.apache.hadoop.mapred.TextInputFormat
279              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
280              properties:
281                bucket_count -1
282                columns key,value,val2
283                columns.types string:string:string
284                file.inputformat org.apache.hadoop.mapred.TextInputFormat
285                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
286                location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/dest_j1
287                name default.dest_j1
288                serialization.ddl struct dest_j1 { string key, string value, string val2}
289                serialization.format 1
290                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
291                transient_lastDdlTime 1297382757
292              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
293              name: default.dest_j1
294          tmp directory: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10001
295
296  Stage: Stage-2
297    Stats-Aggr Operator
298      Stats Aggregation Key Prefix: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10000/
299
300  Stage: Stage-3
301    Map Reduce
302      Alias -> Map Operator Tree:
303        pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10002 
304            File Output Operator
305              compressed: false
306              GlobalTableId: 0
307              directory: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10000
308              NumFilesPerFileSink: 1
309              table:
310                  input format: org.apache.hadoop.mapred.TextInputFormat
311                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
312                  properties:
313                    bucket_count -1
314                    columns key,value,val2
315                    columns.types string:string:string
316                    file.inputformat org.apache.hadoop.mapred.TextInputFormat
317                    file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
318                    location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/dest_j1
319                    name default.dest_j1
320                    serialization.ddl struct dest_j1 { string key, string value, string val2}
321                    serialization.format 1
322                    serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
323                    transient_lastDdlTime 1297382757
324                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
325                  name: default.dest_j1
326              TotalFiles: 1
327              GatherStats: false
328              MultiFileSpray: false
329      Needs Tagging: false
330      Path -> Alias:
331        pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10002 [pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10002]
332      Path -> Partition:
333        pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_16-05-57_676_6075966104051319240/-ext-10002 
334          Partition
335            base file name: -ext-10002
336            input format: org.apache.hadoop.mapred.TextInputFormat
337            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
338            properties:
339              bucket_count -1
340              columns key,value,val2
341              columns.types string:string:string
342              file.inputformat org.apache.hadoop.mapred.TextInputFormat
343              file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
344              location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/dest_j1
345              name default.dest_j1
346              serialization.ddl struct dest_j1 { string key, string value, string val2}
347              serialization.format 1
348              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
349              transient_lastDdlTime 1297382757
350            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
351          
352              input format: org.apache.hadoop.mapred.TextInputFormat
353              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
354              properties:
355                bucket_count -1
356                columns key,value,val2
357                columns.types string:string:string
358                file.inputformat org.apache.hadoop.mapred.TextInputFormat
359                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
360                location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/dest_j1
361                name default.dest_j1
362                serialization.ddl struct dest_j1 { string key, string value, string val2}
363                serialization.format 1
364                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
365                transient_lastDdlTime 1297382757
366              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
367              name: default.dest_j1
368            name: default.dest_j1
369
370
371PREHOOK: query: INSERT OVERWRITE TABLE dest_j1
372SELECT /*+ MAPJOIN(x) */ x.key, x.value, subq1.value
373FROM 
374( SELECT x.key as key, x.value as value from src x where x.key < 20
375     UNION ALL
376  SELECT x1.key as key, x1.value as value from src x1 where x1.key > 100
377) subq1
378JOIN src1 x ON (x.key = subq1.key)
379PREHOOK: type: QUERY
380PREHOOK: Input: default@src
381PREHOOK: Input: default@src1
382PREHOOK: Output: default@dest_j1
383POSTHOOK: query: INSERT OVERWRITE TABLE dest_j1
384SELECT /*+ MAPJOIN(x) */ x.key, x.value, subq1.value
385FROM 
386( SELECT x.key as key, x.value as value from src x where x.key < 20
387     UNION ALL
388  SELECT x1.key as key, x1.value as value from src x1 where x1.key > 100
389) subq1
390JOIN src1 x ON (x.key = subq1.key)
391POSTHOOK: type: QUERY
392POSTHOOK: Input: default@src
393POSTHOOK: Input: default@src1
394POSTHOOK: Output: default@dest_j1
395POSTHOOK: Lineage: dest_j1.key SIMPLE [(src1)x.FieldSchema(name:key, type:string, comment:default), ]
396POSTHOOK: Lineage: dest_j1.val2 EXPRESSION [(src)x.FieldSchema(name:value, type:string, comment:default), (src)x1.FieldSchema(name:value, type:string, comment:default), ]
397POSTHOOK: Lineage: dest_j1.value SIMPLE [(src1)x.FieldSchema(name:value, type:string, comment:default), ]
398PREHOOK: query: select * from dest_j1 x order by x.key
399PREHOOK: type: QUERY
400PREHOOK: Input: default@dest_j1
401PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-06-04_585_5324136836737662149/-mr-10000
402POSTHOOK: query: select * from dest_j1 x order by x.key
403POSTHOOK: type: QUERY
404POSTHOOK: Input: default@dest_j1
405POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-06-04_585_5324136836737662149/-mr-10000
406POSTHOOK: Lineage: dest_j1.key SIMPLE [(src1)x.FieldSchema(name:key, type:string, comment:default), ]
407POSTHOOK: Lineage: dest_j1.val2 EXPRESSION [(src)x.FieldSchema(name:value, type:string, comment:default), (src)x1.FieldSchema(name:value, type:string, comment:default), ]
408POSTHOOK: Lineage: dest_j1.value SIMPLE [(src1)x.FieldSchema(name:value, type:string, comment:default), ]
409128		val_128
410128		val_128
411128		val_128
412146	val_146	val_146
413146	val_146	val_146
414150	val_150	val_150
415213	val_213	val_213
416213	val_213	val_213
417224		val_224
418224		val_224
419238	val_238	val_238
420238	val_238	val_238
421255	val_255	val_255
422255	val_255	val_255
423273	val_273	val_273
424273	val_273	val_273
425273	val_273	val_273
426278	val_278	val_278
427278	val_278	val_278
428311	val_311	val_311
429311	val_311	val_311
430311	val_311	val_311
431369		val_369
432369		val_369
433369		val_369
434401	val_401	val_401
435401	val_401	val_401
436401	val_401	val_401
437401	val_401	val_401
438401	val_401	val_401
439406	val_406	val_406
440406	val_406	val_406
441406	val_406	val_406
442406	val_406	val_406