PageRenderTime 26ms CodeModel.GetById 12ms app.highlight 6ms RepoModel.GetById 2ms app.codeStats 0ms

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

#
text | 295 lines | 289 code | 6 blank | 0 comment | 0 complexity | aeac7dd675771e412d943243f974c088 MD5 | raw file
  1PREHOOK: query: CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING, c5 INT, c6 STRING) STORED AS TEXTFILE
  2PREHOOK: type: CREATETABLE
  3POSTHOOK: query: CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING, c5 INT, c6 STRING) STORED AS TEXTFILE
  4POSTHOOK: type: CREATETABLE
  5POSTHOOK: Output: default@dest1
  6PREHOOK: query: explain
  7FROM (
  8 FROM 
  9  (
 10  FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
 11  ) a
 12 FULL OUTER JOIN 
 13 (
 14  FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
 15 ) b 
 16 ON (a.c1 = b.c3)
 17 LEFT OUTER JOIN 
 18 (
 19  FROM src src3 SELECT src3.key AS c5, src3.value AS c6 WHERE src3.key > 20 and src3.key < 25
 20 ) c
 21 ON (a.c1 = c.c5)
 22 SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6
 23) c
 24INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6
 25PREHOOK: type: QUERY
 26POSTHOOK: query: explain
 27FROM (
 28 FROM 
 29  (
 30  FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
 31  ) a
 32 FULL OUTER JOIN 
 33 (
 34  FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
 35 ) b 
 36 ON (a.c1 = b.c3)
 37 LEFT OUTER JOIN 
 38 (
 39  FROM src src3 SELECT src3.key AS c5, src3.value AS c6 WHERE src3.key > 20 and src3.key < 25
 40 ) c
 41 ON (a.c1 = c.c5)
 42 SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6
 43) c
 44INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6
 45POSTHOOK: type: QUERY
 46ABSTRACT SYNTAX TREE:
 47  (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_FULLOUTERJOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) src1)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src1) key) c1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src1) value) c2)) (TOK_WHERE (and (> (. (TOK_TABLE_OR_COL src1) key) 10) (< (. (TOK_TABLE_OR_COL src1) key) 20))))) a) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) src2)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src2) key) c3) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src2) value) c4)) (TOK_WHERE (and (> (. (TOK_TABLE_OR_COL src2) key) 15) (< (. (TOK_TABLE_OR_COL src2) key) 25))))) b) (= (. (TOK_TABLE_OR_COL a) c1) (. (TOK_TABLE_OR_COL b) c3))) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) src3)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src3) key) c5) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src3) value) c6)) (TOK_WHERE (and (> (. (TOK_TABLE_OR_COL src3) key) 20) (< (. (TOK_TABLE_OR_COL src3) key) 25))))) c) (= (. (TOK_TABLE_OR_COL a) c1) (. (TOK_TABLE_OR_COL c) c5)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) c1) c1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) c2) c2) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) c3) c3) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) c4) c4) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c5) c5) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c6) c6)))) c)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest1))) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c1)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c2)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c3)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c4)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c5)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c6)))))
 48
 49STAGE DEPENDENCIES:
 50  Stage-1 is a root stage
 51  Stage-0 depends on stages: Stage-1
 52  Stage-2 depends on stages: Stage-0
 53
 54STAGE PLANS:
 55  Stage: Stage-1
 56    Map Reduce
 57      Alias -> Map Operator Tree:
 58        c:a:src1 
 59          TableScan
 60            alias: src1
 61            Filter Operator
 62              predicate:
 63                  expr: ((key > 10) and (key < 20))
 64                  type: boolean
 65              Filter Operator
 66                predicate:
 67                    expr: ((key > 10) and (key < 20))
 68                    type: boolean
 69                Select Operator
 70                  expressions:
 71                        expr: key
 72                        type: string
 73                        expr: value
 74                        type: string
 75                  outputColumnNames: _col0, _col1
 76                  Reduce Output Operator
 77                    key expressions:
 78                          expr: _col0
 79                          type: string
 80                    sort order: +
 81                    Map-reduce partition columns:
 82                          expr: _col0
 83                          type: string
 84                    tag: 0
 85                    value expressions:
 86                          expr: _col0
 87                          type: string
 88                          expr: _col1
 89                          type: string
 90        c:b:src2 
 91          TableScan
 92            alias: src2
 93            Filter Operator
 94              predicate:
 95                  expr: ((key > 15) and (key < 25))
 96                  type: boolean
 97              Filter Operator
 98                predicate:
 99                    expr: ((key > 15) and (key < 25))
100                    type: boolean
101                Select Operator
102                  expressions:
103                        expr: key
104                        type: string
105                        expr: value
106                        type: string
107                  outputColumnNames: _col0, _col1
108                  Reduce Output Operator
109                    key expressions:
110                          expr: _col0
111                          type: string
112                    sort order: +
113                    Map-reduce partition columns:
114                          expr: _col0
115                          type: string
116                    tag: 1
117                    value expressions:
118                          expr: _col0
119                          type: string
120                          expr: _col1
121                          type: string
122        c:c:src3 
123          TableScan
124            alias: src3
125            Filter Operator
126              predicate:
127                  expr: ((key > 20) and (key < 25))
128                  type: boolean
129              Filter Operator
130                predicate:
131                    expr: ((key > 20) and (key < 25))
132                    type: boolean
133                Select Operator
134                  expressions:
135                        expr: key
136                        type: string
137                        expr: value
138                        type: string
139                  outputColumnNames: _col0, _col1
140                  Reduce Output Operator
141                    key expressions:
142                          expr: _col0
143                          type: string
144                    sort order: +
145                    Map-reduce partition columns:
146                          expr: _col0
147                          type: string
148                    tag: 2
149                    value expressions:
150                          expr: _col0
151                          type: string
152                          expr: _col1
153                          type: string
154      Reduce Operator Tree:
155        Join Operator
156          condition map:
157               Outer Join 0 to 1
158               Left Outer Join0 to 2
159          condition expressions:
160            0 {VALUE._col0} {VALUE._col1}
161            1 {VALUE._col0} {VALUE._col1}
162            2 {VALUE._col0} {VALUE._col1}
163          handleSkewJoin: false
164          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
165          Select Operator
166            expressions:
167                  expr: _col0
168                  type: string
169                  expr: _col1
170                  type: string
171                  expr: _col2
172                  type: string
173                  expr: _col3
174                  type: string
175                  expr: _col4
176                  type: string
177                  expr: _col5
178                  type: string
179            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
180            Select Operator
181              expressions:
182                    expr: _col0
183                    type: string
184                    expr: _col1
185                    type: string
186                    expr: _col2
187                    type: string
188                    expr: _col3
189                    type: string
190                    expr: _col4
191                    type: string
192                    expr: _col5
193                    type: string
194              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
195              Select Operator
196                expressions:
197                      expr: UDFToInteger(_col0)
198                      type: int
199                      expr: _col1
200                      type: string
201                      expr: UDFToInteger(_col2)
202                      type: int
203                      expr: _col3
204                      type: string
205                      expr: UDFToInteger(_col4)
206                      type: int
207                      expr: _col5
208                      type: string
209                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
210                File Output Operator
211                  compressed: false
212                  GlobalTableId: 1
213                  table:
214                      input format: org.apache.hadoop.mapred.TextInputFormat
215                      output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
216                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
217                      name: default.dest1
218
219  Stage: Stage-0
220    Move Operator
221      tables:
222          replace: true
223          table:
224              input format: org.apache.hadoop.mapred.TextInputFormat
225              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
226              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
227              name: default.dest1
228
229  Stage: Stage-2
230    Stats-Aggr Operator
231
232
233PREHOOK: query: FROM (
234 FROM 
235  (
236  FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
237  ) a
238 FULL OUTER JOIN 
239 (
240  FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
241 ) b 
242 ON (a.c1 = b.c3)
243 LEFT OUTER JOIN 
244 (
245  FROM src src3 SELECT src3.key AS c5, src3.value AS c6 WHERE src3.key > 20 and src3.key < 25
246 ) c
247 ON (a.c1 = c.c5)
248 SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6
249) c
250INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6
251PREHOOK: type: QUERY
252PREHOOK: Input: default@src
253PREHOOK: Output: default@dest1
254POSTHOOK: query: FROM (
255 FROM 
256  (
257  FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
258  ) a
259 FULL OUTER JOIN 
260 (
261  FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
262 ) b 
263 ON (a.c1 = b.c3)
264 LEFT OUTER JOIN 
265 (
266  FROM src src3 SELECT src3.key AS c5, src3.value AS c6 WHERE src3.key > 20 and src3.key < 25
267 ) c
268 ON (a.c1 = c.c5)
269 SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6
270) c
271INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6
272POSTHOOK: type: QUERY
273POSTHOOK: Input: default@src
274POSTHOOK: Output: default@dest1
275POSTHOOK: Lineage: dest1.c1 EXPRESSION [(src)src1.FieldSchema(name:key, type:string, comment:default), ]
276POSTHOOK: Lineage: dest1.c2 SIMPLE [(src)src1.FieldSchema(name:value, type:string, comment:default), ]
277POSTHOOK: Lineage: dest1.c3 EXPRESSION [(src)src2.FieldSchema(name:key, type:string, comment:default), ]
278POSTHOOK: Lineage: dest1.c4 SIMPLE [(src)src2.FieldSchema(name:value, type:string, comment:default), ]
279POSTHOOK: Lineage: dest1.c5 EXPRESSION [(src)src3.FieldSchema(name:key, type:string, comment:default), ]
280POSTHOOK: Lineage: dest1.c6 SIMPLE [(src)src3.FieldSchema(name:value, type:string, comment:default), ]
281PREHOOK: query: SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4,dest1.c5,dest1.c6)) FROM dest1
282PREHOOK: type: QUERY
283PREHOOK: Input: default@dest1
284PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_01-18-42_908_2010709528917086908/-mr-10000
285POSTHOOK: query: SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4,dest1.c5,dest1.c6)) FROM dest1
286POSTHOOK: type: QUERY
287POSTHOOK: Input: default@dest1
288POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_01-18-42_908_2010709528917086908/-mr-10000
289POSTHOOK: Lineage: dest1.c1 EXPRESSION [(src)src1.FieldSchema(name:key, type:string, comment:default), ]
290POSTHOOK: Lineage: dest1.c2 SIMPLE [(src)src1.FieldSchema(name:value, type:string, comment:default), ]
291POSTHOOK: Lineage: dest1.c3 EXPRESSION [(src)src2.FieldSchema(name:key, type:string, comment:default), ]
292POSTHOOK: Lineage: dest1.c4 SIMPLE [(src)src2.FieldSchema(name:value, type:string, comment:default), ]
293POSTHOOK: Lineage: dest1.c5 EXPRESSION [(src)src3.FieldSchema(name:key, type:string, comment:default), ]
294POSTHOOK: Lineage: dest1.c6 SIMPLE [(src)src3.FieldSchema(name:value, type:string, comment:default), ]
295-5178357269