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

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

#
text | 313 lines | 305 code | 8 blank | 0 comment | 0 complexity | efaa452beddc46c6b9c00b998b976dd6 MD5 | raw file
  1PREHOOK: query: CREATE TABLE dest1(key STRING, c1 INT, c2 STRING, C3 INT, c4 INT) STORED AS TEXTFILE
  2PREHOOK: type: CREATETABLE
  3POSTHOOK: query: CREATE TABLE dest1(key STRING, c1 INT, c2 STRING, C3 INT, c4 INT) STORED AS TEXTFILE
  4POSTHOOK: type: CREATETABLE
  5POSTHOOK: Output: default@dest1
  6PREHOOK: query: EXPLAIN EXTENDED
  7FROM srcpart src
  8INSERT OVERWRITE TABLE dest1 
  9SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))), sum(DISTINCT substr(src.value, 5)), count(DISTINCT src.value) 
 10WHERE src.ds = '2008-04-08'
 11GROUP BY substr(src.key,1,1)
 12PREHOOK: type: QUERY
 13POSTHOOK: query: EXPLAIN EXTENDED
 14FROM srcpart src
 15INSERT OVERWRITE TABLE dest1 
 16SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))), sum(DISTINCT substr(src.value, 5)), count(DISTINCT src.value) 
 17WHERE src.ds = '2008-04-08'
 18GROUP BY substr(src.key,1,1)
 19POSTHOOK: type: QUERY
 20ABSTRACT SYNTAX TREE:
 21  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME srcpart) src)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest1))) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) key) 1 1)) (TOK_SELEXPR (TOK_FUNCTIONDI count (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) value) 5))) (TOK_SELEXPR (TOK_FUNCTION concat (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) key) 1 1) (TOK_FUNCTION sum (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) value) 5)))) (TOK_SELEXPR (TOK_FUNCTIONDI sum (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) value) 5))) (TOK_SELEXPR (TOK_FUNCTIONDI count (. (TOK_TABLE_OR_COL src) value)))) (TOK_WHERE (= (. (TOK_TABLE_OR_COL src) ds) '2008-04-08')) (TOK_GROUPBY (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) key) 1 1))))
 22
 23STAGE DEPENDENCIES:
 24  Stage-1 is a root stage
 25  Stage-0 depends on stages: Stage-1
 26  Stage-2 depends on stages: Stage-0
 27
 28STAGE PLANS:
 29  Stage: Stage-1
 30    Map Reduce
 31      Alias -> Map Operator Tree:
 32        src 
 33          TableScan
 34            alias: src
 35            GatherStats: false
 36            Filter Operator
 37              isSamplingPred: false
 38              predicate:
 39                  expr: (ds = '2008-04-08')
 40                  type: boolean
 41              Select Operator
 42                expressions:
 43                      expr: key
 44                      type: string
 45                      expr: value
 46                      type: string
 47                outputColumnNames: key, value
 48                Group By Operator
 49                  aggregations:
 50                        expr: count(DISTINCT substr(value, 5))
 51                        expr: sum(substr(value, 5))
 52                        expr: sum(DISTINCT substr(value, 5))
 53                        expr: count(DISTINCT value)
 54                  bucketGroup: false
 55                  keys:
 56                        expr: substr(key, 1, 1)
 57                        type: string
 58                        expr: substr(value, 5)
 59                        type: string
 60                        expr: value
 61                        type: string
 62                  mode: hash
 63                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
 64                  Reduce Output Operator
 65                    key expressions:
 66                          expr: _col0
 67                          type: string
 68                          expr: _col1
 69                          type: string
 70                          expr: _col2
 71                          type: string
 72                    sort order: +++
 73                    Map-reduce partition columns:
 74                          expr: _col0
 75                          type: string
 76                    tag: -1
 77                    value expressions:
 78                          expr: _col3
 79                          type: bigint
 80                          expr: _col4
 81                          type: double
 82                          expr: _col5
 83                          type: double
 84                          expr: _col6
 85                          type: bigint
 86      Needs Tagging: false
 87      Path -> Alias:
 88        pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/srcpart/ds=2008-04-08/hr=11 [src]
 89        pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/srcpart/ds=2008-04-08/hr=12 [src]
 90      Path -> Partition:
 91        pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/srcpart/ds=2008-04-08/hr=11 
 92          Partition
 93            base file name: hr=11
 94            input format: org.apache.hadoop.mapred.TextInputFormat
 95            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
 96            partition values:
 97              ds 2008-04-08
 98              hr 11
 99            properties:
100              bucket_count -1
101              columns key,value
102              columns.types string:string
103              file.inputformat org.apache.hadoop.mapred.TextInputFormat
104              file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
105              location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/srcpart/ds=2008-04-08/hr=11
106              name default.srcpart
107              partition_columns ds/hr
108              serialization.ddl struct srcpart { string key, string value}
109              serialization.format 1
110              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
111              transient_lastDdlTime 1297328959
112            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
113          
114              input format: org.apache.hadoop.mapred.TextInputFormat
115              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
116              properties:
117                bucket_count -1
118                columns key,value
119                columns.types string:string
120                file.inputformat org.apache.hadoop.mapred.TextInputFormat
121                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
122                location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/srcpart
123                name default.srcpart
124                partition_columns ds/hr
125                serialization.ddl struct srcpart { string key, string value}
126                serialization.format 1
127                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
128                transient_lastDdlTime 1297328959
129              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
130              name: default.srcpart
131            name: default.srcpart
132        pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/srcpart/ds=2008-04-08/hr=12 
133          Partition
134            base file name: hr=12
135            input format: org.apache.hadoop.mapred.TextInputFormat
136            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
137            partition values:
138              ds 2008-04-08
139              hr 12
140            properties:
141              bucket_count -1
142              columns key,value
143              columns.types string:string
144              file.inputformat org.apache.hadoop.mapred.TextInputFormat
145              file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
146              location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/srcpart/ds=2008-04-08/hr=12
147              name default.srcpart
148              partition_columns ds/hr
149              serialization.ddl struct srcpart { string key, string value}
150              serialization.format 1
151              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
152              transient_lastDdlTime 1297328959
153            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
154          
155              input format: org.apache.hadoop.mapred.TextInputFormat
156              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
157              properties:
158                bucket_count -1
159                columns key,value
160                columns.types string:string
161                file.inputformat org.apache.hadoop.mapred.TextInputFormat
162                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
163                location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/srcpart
164                name default.srcpart
165                partition_columns ds/hr
166                serialization.ddl struct srcpart { string key, string value}
167                serialization.format 1
168                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
169                transient_lastDdlTime 1297328959
170              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
171              name: default.srcpart
172            name: default.srcpart
173      Reduce Operator Tree:
174        Group By Operator
175          aggregations:
176                expr: count(DISTINCT KEY._col1:0._col0)
177                expr: sum(VALUE._col1)
178                expr: sum(DISTINCT KEY._col1:1._col0)
179                expr: count(DISTINCT KEY._col1:2._col0)
180          bucketGroup: false
181          keys:
182                expr: KEY._col0
183                type: string
184          mode: mergepartial
185          outputColumnNames: _col0, _col1, _col2, _col3, _col4
186          Select Operator
187            expressions:
188                  expr: _col0
189                  type: string
190                  expr: _col1
191                  type: bigint
192                  expr: concat(_col0, _col2)
193                  type: string
194                  expr: _col3
195                  type: double
196                  expr: _col4
197                  type: bigint
198            outputColumnNames: _col0, _col1, _col2, _col3, _col4
199            Select Operator
200              expressions:
201                    expr: _col0
202                    type: string
203                    expr: UDFToInteger(_col1)
204                    type: int
205                    expr: _col2
206                    type: string
207                    expr: UDFToInteger(_col3)
208                    type: int
209                    expr: UDFToInteger(_col4)
210                    type: int
211              outputColumnNames: _col0, _col1, _col2, _col3, _col4
212              File Output Operator
213                compressed: false
214                GlobalTableId: 1
215                directory: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_01-54-56_939_392063944204159853/-ext-10000
216                NumFilesPerFileSink: 1
217                Stats Publishing Key Prefix: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_01-54-56_939_392063944204159853/-ext-10000/
218                table:
219                    input format: org.apache.hadoop.mapred.TextInputFormat
220                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
221                    properties:
222                      bucket_count -1
223                      columns key,c1,c2,c3,c4
224                      columns.types string:int:string:int:int
225                      file.inputformat org.apache.hadoop.mapred.TextInputFormat
226                      file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
227                      location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/dest1
228                      name default.dest1
229                      serialization.ddl struct dest1 { string key, i32 c1, string c2, i32 c3, i32 c4}
230                      serialization.format 1
231                      serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
232                      transient_lastDdlTime 1297331696
233                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
234                    name: default.dest1
235                TotalFiles: 1
236                GatherStats: true
237                MultiFileSpray: false
238
239  Stage: Stage-0
240    Move Operator
241      tables:
242          replace: true
243          source: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_01-54-56_939_392063944204159853/-ext-10000
244          table:
245              input format: org.apache.hadoop.mapred.TextInputFormat
246              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
247              properties:
248                bucket_count -1
249                columns key,c1,c2,c3,c4
250                columns.types string:int:string:int:int
251                file.inputformat org.apache.hadoop.mapred.TextInputFormat
252                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
253                location pfile:/data/users/sdong/www/open-source-hive1/build/ql/test/data/warehouse/dest1
254                name default.dest1
255                serialization.ddl struct dest1 { string key, i32 c1, string c2, i32 c3, i32 c4}
256                serialization.format 1
257                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
258                transient_lastDdlTime 1297331696
259              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
260              name: default.dest1
261          tmp directory: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_01-54-56_939_392063944204159853/-ext-10001
262
263  Stage: Stage-2
264    Stats-Aggr Operator
265      Stats Aggregation Key Prefix: pfile:/data/users/sdong/www/open-source-hive1/build/ql/scratchdir/hive_2011-02-10_01-54-56_939_392063944204159853/-ext-10000/
266
267
268PREHOOK: query: FROM srcpart src
269INSERT OVERWRITE TABLE dest1 
270SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))), sum(DISTINCT substr(src.value, 5)), count(DISTINCT src.value) 
271WHERE src.ds = '2008-04-08'
272GROUP BY substr(src.key,1,1)
273PREHOOK: type: QUERY
274PREHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
275PREHOOK: Input: default@srcpart@ds=2008-04-08/hr=12
276PREHOOK: Output: default@dest1
277POSTHOOK: query: FROM srcpart src
278INSERT OVERWRITE TABLE dest1 
279SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))), sum(DISTINCT substr(src.value, 5)), count(DISTINCT src.value) 
280WHERE src.ds = '2008-04-08'
281GROUP BY substr(src.key,1,1)
282POSTHOOK: type: QUERY
283POSTHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
284POSTHOOK: Input: default@srcpart@ds=2008-04-08/hr=12
285POSTHOOK: Output: default@dest1
286POSTHOOK: Lineage: dest1.c1 EXPRESSION [(srcpart)src.FieldSchema(name:value, type:string, comment:default), ]
287POSTHOOK: Lineage: dest1.c2 EXPRESSION [(srcpart)src.FieldSchema(name:key, type:string, comment:default), (srcpart)src.FieldSchema(name:value, type:string, comment:default), ]
288POSTHOOK: Lineage: dest1.c3 EXPRESSION [(srcpart)src.FieldSchema(name:value, type:string, comment:default), ]
289POSTHOOK: Lineage: dest1.c4 EXPRESSION [(srcpart)src.FieldSchema(name:value, type:string, comment:default), ]
290POSTHOOK: Lineage: dest1.key EXPRESSION [(srcpart)src.FieldSchema(name:key, type:string, comment:default), ]
291PREHOOK: query: SELECT dest1.* FROM dest1
292PREHOOK: type: QUERY
293PREHOOK: Input: default@dest1
294PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_01-55-03_112_7822131255872092269/-mr-10000
295POSTHOOK: query: SELECT dest1.* FROM dest1
296POSTHOOK: type: QUERY
297POSTHOOK: Input: default@dest1
298POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_01-55-03_112_7822131255872092269/-mr-10000
299POSTHOOK: Lineage: dest1.c1 EXPRESSION [(srcpart)src.FieldSchema(name:value, type:string, comment:default), ]
300POSTHOOK: Lineage: dest1.c2 EXPRESSION [(srcpart)src.FieldSchema(name:key, type:string, comment:default), (srcpart)src.FieldSchema(name:value, type:string, comment:default), ]
301POSTHOOK: Lineage: dest1.c3 EXPRESSION [(srcpart)src.FieldSchema(name:value, type:string, comment:default), ]
302POSTHOOK: Lineage: dest1.c4 EXPRESSION [(srcpart)src.FieldSchema(name:value, type:string, comment:default), ]
303POSTHOOK: Lineage: dest1.key EXPRESSION [(srcpart)src.FieldSchema(name:key, type:string, comment:default), ]
3040	1	00.0	0	1
3051	71	132828.0	10044	71
3062	69	251142.0	15780	69
3073	62	364008.0	20119	62
3084	74	4105526.0	30965	74
3095	6	5794.0	278	6
3106	5	6796.0	331	5
3117	6	71470.0	447	6
3128	8	81524.0	595	8
3139	7	92094.0	577	7