Index: common/src/java/org/apache/hadoop/hive/conf/HiveConf.java =================================================================== --- common/src/java/org/apache/hadoop/hive/conf/HiveConf.java (revision 744613) +++ common/src/java/org/apache/hadoop/hive/conf/HiveConf.java (working copy) @@ -106,6 +106,7 @@ HIVEPARTITIONPRUNER("hive.partition.pruning", "nonstrict"), HIVEALIAS("hive.alias", ""), HIVEMAPSIDEAGGREGATE("hive.map.aggr", "false"), + HIVEGROUPBYSKEW("hive.groupby.skewindata", "true"), HIVEJOINEMITINTERVAL("hive.join.emit.interval", 1000), HIVEMAPAGGRHASHMEMORY("hive.map.aggr.hash.percentmemory", (float)0.5), Index: ql/src/test/results/clientpositive/groupby2_noskew.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby2_noskew.q.out (revision 0) +++ ql/src/test/results/clientpositive/groupby2_noskew.q.out (revision 0) @@ -0,0 +1,77 @@ +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF src)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB dest_g2)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION substr (TOK_COLREF src key) 1 1)) (TOK_SELEXPR (TOK_FUNCTIONDI count (TOK_FUNCTION substr (TOK_COLREF src value) 5))) (TOK_SELEXPR (TOK_FUNCTION concat (TOK_FUNCTION substr (TOK_COLREF src key) 1 1) (TOK_FUNCTION sum (TOK_FUNCTION substr (TOK_COLREF src value) 5))))) (TOK_GROUPBY (TOK_FUNCTION substr (TOK_COLREF src key) 1 1)))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + Reduce Output Operator + key expressions: + expr: substr(key, 1, 1) + type: string + expr: substr(value, 5) + type: string + sort order: ++ + Map-reduce partition columns: + expr: substr(key, 1, 1) + type: string + tag: -1 + Reduce Operator Tree: + Group By Operator + aggregations: + expr: sum(UDFToDouble(KEY.1)) + expr: count(DISTINCT KEY.1) + keys: + expr: KEY.0 + type: string + mode: complete + Select Operator + expressions: + expr: 0 + type: string + expr: 2 + type: bigint + expr: concat(0, UDFToString(1)) + type: string + Select Operator + expressions: + expr: 0 + type: string + expr: UDFToInteger(1) + type: int + expr: 2 + type: string + File Output Operator + compressed: false + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest_g2 + + Stage: Stage-0 + Move Operator + tables: + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest_g2 + + +0 1 00.0 +1 71 116414.0 +2 69 225571.0 +3 62 332004.0 +4 74 452763.0 +5 6 5397.0 +6 5 6398.0 +7 6 7735.0 +8 8 8762.0 +9 7 91047.0 Index: ql/src/test/results/clientpositive/groupby7_noskew.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby7_noskew.q.out (revision 0) +++ ql/src/test/results/clientpositive/groupby7_noskew.q.out (revision 0) @@ -0,0 +1,739 @@ +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF SRC)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB DEST1)) (TOK_SELECT (TOK_SELEXPR (TOK_COLREF SRC key)) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION SUBSTR (TOK_COLREF SRC value) 5)))) (TOK_GROUPBY (TOK_COLREF SRC key))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB DEST2)) (TOK_SELECT (TOK_SELEXPR (TOK_COLREF SRC key)) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION SUBSTR (TOK_COLREF SRC value) 5)))) (TOK_GROUPBY (TOK_COLREF SRC key)))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1, Stage-2 + Stage-2 depends on stages: Stage-1 + Stage-0 depends on stages: Stage-1, Stage-2 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + Reduce Output Operator + key expressions: + expr: key + type: string + sort order: + + Map-reduce partition columns: + expr: key + type: string + tag: -1 + value expressions: + expr: substr(value, 5) + type: string + File Output Operator + compressed: true + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.mapred.SequenceFileOutputFormat + name: binary_table + Reduce Operator Tree: + Group By Operator + aggregations: + expr: sum(UDFToDouble(VALUE.0)) + keys: + expr: KEY.0 + type: string + mode: complete + Select Operator + expressions: + expr: 0 + type: string + expr: 1 + type: double + Select Operator + expressions: + expr: UDFToInteger(0) + type: int + expr: UDFToString(1) + type: string + File Output Operator + compressed: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest1 + + Stage: Stage-0 + Move Operator + tables: + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest1 + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest2 + + Stage: Stage-2 + Map Reduce + Alias -> Map Operator Tree: + /data/users/njain/hive4/hive4/build/ql/tmp/563879386/576370888.10002 + Reduce Output Operator + key expressions: + expr: key + type: string + sort order: + + Map-reduce partition columns: + expr: key + type: string + tag: -1 + value expressions: + expr: substr(value, 5) + type: string + Reduce Operator Tree: + Group By Operator + aggregations: + expr: sum(UDFToDouble(VALUE.0)) + keys: + expr: KEY.0 + type: string + mode: complete + Select Operator + expressions: + expr: 0 + type: string + expr: 1 + type: double + Select Operator + expressions: + expr: UDFToInteger(0) + type: int + expr: UDFToString(1) + type: string + File Output Operator + compressed: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest2 + + +0 0.0 +10 10.0 +100 200.0 +103 206.0 +104 208.0 +105 105.0 +11 11.0 +111 111.0 +113 226.0 +114 114.0 +116 116.0 +118 236.0 +119 357.0 +12 24.0 +120 240.0 +125 250.0 +126 126.0 +128 384.0 +129 258.0 +131 131.0 +133 133.0 +134 268.0 +136 136.0 +137 274.0 +138 552.0 +143 143.0 +145 145.0 +146 292.0 +149 298.0 +15 30.0 +150 150.0 +152 304.0 +153 153.0 +155 155.0 +156 156.0 +157 157.0 +158 158.0 +160 160.0 +162 162.0 +163 163.0 +164 328.0 +165 330.0 +166 166.0 +167 501.0 +168 168.0 +169 676.0 +17 17.0 +170 170.0 +172 344.0 +174 348.0 +175 350.0 +176 352.0 +177 177.0 +178 178.0 +179 358.0 +18 36.0 +180 180.0 +181 181.0 +183 183.0 +186 186.0 +187 561.0 +189 189.0 +19 19.0 +190 190.0 +191 382.0 +192 192.0 +193 579.0 +194 194.0 +195 390.0 +196 196.0 +197 394.0 +199 597.0 +2 2.0 +20 20.0 +200 400.0 +201 201.0 +202 202.0 +203 406.0 +205 410.0 +207 414.0 +208 624.0 +209 418.0 +213 426.0 +214 214.0 +216 432.0 +217 434.0 +218 218.0 +219 438.0 +221 442.0 +222 222.0 +223 446.0 +224 448.0 +226 226.0 +228 228.0 +229 458.0 +230 1150.0 +233 466.0 +235 235.0 +237 474.0 +238 476.0 +239 478.0 +24 48.0 +241 241.0 +242 484.0 +244 244.0 +247 247.0 +248 248.0 +249 249.0 +252 252.0 +255 510.0 +256 512.0 +257 257.0 +258 258.0 +26 52.0 +260 260.0 +262 262.0 +263 263.0 +265 530.0 +266 266.0 +27 27.0 +272 544.0 +273 819.0 +274 274.0 +275 275.0 +277 1108.0 +278 556.0 +28 28.0 +280 560.0 +281 562.0 +282 564.0 +283 283.0 +284 284.0 +285 285.0 +286 286.0 +287 287.0 +288 576.0 +289 289.0 +291 291.0 +292 292.0 +296 296.0 +298 894.0 +30 30.0 +302 302.0 +305 305.0 +306 306.0 +307 614.0 +308 308.0 +309 618.0 +310 310.0 +311 933.0 +315 315.0 +316 948.0 +317 634.0 +318 954.0 +321 642.0 +322 644.0 +323 323.0 +325 650.0 +327 981.0 +33 33.0 +331 662.0 +332 332.0 +333 666.0 +335 335.0 +336 336.0 +338 338.0 +339 339.0 +34 34.0 +341 341.0 +342 684.0 +344 688.0 +345 345.0 +348 1740.0 +35 105.0 +351 351.0 +353 706.0 +356 356.0 +360 360.0 +362 362.0 +364 364.0 +365 365.0 +366 366.0 +367 734.0 +368 368.0 +369 1107.0 +37 74.0 +373 373.0 +374 374.0 +375 375.0 +377 377.0 +378 378.0 +379 379.0 +382 764.0 +384 1152.0 +386 386.0 +389 389.0 +392 392.0 +393 393.0 +394 394.0 +395 790.0 +396 1188.0 +397 794.0 +399 798.0 +4 4.0 +400 400.0 +401 2005.0 +402 402.0 +403 1209.0 +404 808.0 +406 1624.0 +407 407.0 +409 1227.0 +41 41.0 +411 411.0 +413 826.0 +414 828.0 +417 1251.0 +418 418.0 +419 419.0 +42 84.0 +421 421.0 +424 848.0 +427 427.0 +429 858.0 +43 43.0 +430 1290.0 +431 1293.0 +432 432.0 +435 435.0 +436 436.0 +437 437.0 +438 1314.0 +439 878.0 +44 44.0 +443 443.0 +444 444.0 +446 446.0 +448 448.0 +449 449.0 +452 452.0 +453 453.0 +454 1362.0 +455 455.0 +457 457.0 +458 916.0 +459 918.0 +460 460.0 +462 924.0 +463 926.0 +466 1398.0 +467 467.0 +468 1872.0 +469 2345.0 +47 47.0 +470 470.0 +472 472.0 +475 475.0 +477 477.0 +478 956.0 +479 479.0 +480 1440.0 +481 481.0 +482 482.0 +483 483.0 +484 484.0 +485 485.0 +487 487.0 +489 1956.0 +490 490.0 +491 491.0 +492 984.0 +493 493.0 +494 494.0 +495 495.0 +496 496.0 +497 497.0 +498 1494.0 +5 15.0 +51 102.0 +53 53.0 +54 54.0 +57 57.0 +58 116.0 +64 64.0 +65 65.0 +66 66.0 +67 134.0 +69 69.0 +70 210.0 +72 144.0 +74 74.0 +76 152.0 +77 77.0 +78 78.0 +8 8.0 +80 80.0 +82 82.0 +83 166.0 +84 168.0 +85 85.0 +86 86.0 +87 87.0 +9 9.0 +90 270.0 +92 92.0 +95 190.0 +96 96.0 +97 194.0 +98 196.0 +0 0.0 +10 10.0 +100 200.0 +103 206.0 +104 208.0 +105 105.0 +11 11.0 +111 111.0 +113 226.0 +114 114.0 +116 116.0 +118 236.0 +119 357.0 +12 24.0 +120 240.0 +125 250.0 +126 126.0 +128 384.0 +129 258.0 +131 131.0 +133 133.0 +134 268.0 +136 136.0 +137 274.0 +138 552.0 +143 143.0 +145 145.0 +146 292.0 +149 298.0 +15 30.0 +150 150.0 +152 304.0 +153 153.0 +155 155.0 +156 156.0 +157 157.0 +158 158.0 +160 160.0 +162 162.0 +163 163.0 +164 328.0 +165 330.0 +166 166.0 +167 501.0 +168 168.0 +169 676.0 +17 17.0 +170 170.0 +172 344.0 +174 348.0 +175 350.0 +176 352.0 +177 177.0 +178 178.0 +179 358.0 +18 36.0 +180 180.0 +181 181.0 +183 183.0 +186 186.0 +187 561.0 +189 189.0 +19 19.0 +190 190.0 +191 382.0 +192 192.0 +193 579.0 +194 194.0 +195 390.0 +196 196.0 +197 394.0 +199 597.0 +2 2.0 +20 20.0 +200 400.0 +201 201.0 +202 202.0 +203 406.0 +205 410.0 +207 414.0 +208 624.0 +209 418.0 +213 426.0 +214 214.0 +216 432.0 +217 434.0 +218 218.0 +219 438.0 +221 442.0 +222 222.0 +223 446.0 +224 448.0 +226 226.0 +228 228.0 +229 458.0 +230 1150.0 +233 466.0 +235 235.0 +237 474.0 +238 476.0 +239 478.0 +24 48.0 +241 241.0 +242 484.0 +244 244.0 +247 247.0 +248 248.0 +249 249.0 +252 252.0 +255 510.0 +256 512.0 +257 257.0 +258 258.0 +26 52.0 +260 260.0 +262 262.0 +263 263.0 +265 530.0 +266 266.0 +27 27.0 +272 544.0 +273 819.0 +274 274.0 +275 275.0 +277 1108.0 +278 556.0 +28 28.0 +280 560.0 +281 562.0 +282 564.0 +283 283.0 +284 284.0 +285 285.0 +286 286.0 +287 287.0 +288 576.0 +289 289.0 +291 291.0 +292 292.0 +296 296.0 +298 894.0 +30 30.0 +302 302.0 +305 305.0 +306 306.0 +307 614.0 +308 308.0 +309 618.0 +310 310.0 +311 933.0 +315 315.0 +316 948.0 +317 634.0 +318 954.0 +321 642.0 +322 644.0 +323 323.0 +325 650.0 +327 981.0 +33 33.0 +331 662.0 +332 332.0 +333 666.0 +335 335.0 +336 336.0 +338 338.0 +339 339.0 +34 34.0 +341 341.0 +342 684.0 +344 688.0 +345 345.0 +348 1740.0 +35 105.0 +351 351.0 +353 706.0 +356 356.0 +360 360.0 +362 362.0 +364 364.0 +365 365.0 +366 366.0 +367 734.0 +368 368.0 +369 1107.0 +37 74.0 +373 373.0 +374 374.0 +375 375.0 +377 377.0 +378 378.0 +379 379.0 +382 764.0 +384 1152.0 +386 386.0 +389 389.0 +392 392.0 +393 393.0 +394 394.0 +395 790.0 +396 1188.0 +397 794.0 +399 798.0 +4 4.0 +400 400.0 +401 2005.0 +402 402.0 +403 1209.0 +404 808.0 +406 1624.0 +407 407.0 +409 1227.0 +41 41.0 +411 411.0 +413 826.0 +414 828.0 +417 1251.0 +418 418.0 +419 419.0 +42 84.0 +421 421.0 +424 848.0 +427 427.0 +429 858.0 +43 43.0 +430 1290.0 +431 1293.0 +432 432.0 +435 435.0 +436 436.0 +437 437.0 +438 1314.0 +439 878.0 +44 44.0 +443 443.0 +444 444.0 +446 446.0 +448 448.0 +449 449.0 +452 452.0 +453 453.0 +454 1362.0 +455 455.0 +457 457.0 +458 916.0 +459 918.0 +460 460.0 +462 924.0 +463 926.0 +466 1398.0 +467 467.0 +468 1872.0 +469 2345.0 +47 47.0 +470 470.0 +472 472.0 +475 475.0 +477 477.0 +478 956.0 +479 479.0 +480 1440.0 +481 481.0 +482 482.0 +483 483.0 +484 484.0 +485 485.0 +487 487.0 +489 1956.0 +490 490.0 +491 491.0 +492 984.0 +493 493.0 +494 494.0 +495 495.0 +496 496.0 +497 497.0 +498 1494.0 +5 15.0 +51 102.0 +53 53.0 +54 54.0 +57 57.0 +58 116.0 +64 64.0 +65 65.0 +66 66.0 +67 134.0 +69 69.0 +70 210.0 +72 144.0 +74 74.0 +76 152.0 +77 77.0 +78 78.0 +8 8.0 +80 80.0 +82 82.0 +83 166.0 +84 168.0 +85 85.0 +86 86.0 +87 87.0 +9 9.0 +90 270.0 +92 92.0 +95 190.0 +96 96.0 +97 194.0 +98 196.0 Index: ql/src/test/results/clientpositive/groupby2_map.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby2_map.q.out (revision 744613) +++ ql/src/test/results/clientpositive/groupby2_map.q.out (working copy) @@ -3,8 +3,7 @@ STAGE DEPENDENCIES: Stage-1 is a root stage - Stage-2 depends on stages: Stage-1 - Stage-0 depends on stages: Stage-2 + Stage-0 depends on stages: Stage-1 STAGE PLANS: Stage: Stage-1 @@ -43,40 +42,6 @@ Group By Operator aggregations: expr: sum(VALUE.0) - expr: count(DISTINCT KEY.1) - keys: - expr: KEY.0 - type: string - mode: partial2 - File Output Operator - compressed: false - table: - input format: org.apache.hadoop.mapred.SequenceFileInputFormat - output format: org.apache.hadoop.mapred.SequenceFileOutputFormat - name: binary_table - - Stage: Stage-2 - Map Reduce - Alias -> Map Operator Tree: - /data/users/athusoo/commits/hive_trunk_ws3/build/ql/tmp/435212851/489689211.10001 - Reduce Output Operator - key expressions: - expr: 0 - type: string - sort order: + - Map-reduce partition columns: - expr: 0 - type: string - tag: -1 - value expressions: - expr: 1 - type: double - expr: 2 - type: bigint - Reduce Operator Tree: - Group By Operator - aggregations: - expr: sum(VALUE.0) expr: count(VALUE.1) keys: expr: KEY.0 Index: ql/src/test/results/clientpositive/groupby4_noskew.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby4_noskew.q.out (revision 0) +++ ql/src/test/results/clientpositive/groupby4_noskew.q.out (revision 0) @@ -0,0 +1,64 @@ +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF src)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB dest1)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION substr (TOK_COLREF src key) 1 1))) (TOK_GROUPBY (TOK_FUNCTION substr (TOK_COLREF src key) 1 1)))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + Select Operator + expressions: + expr: key + type: string + Reduce Output Operator + key expressions: + expr: substr(0, 1, 1) + type: string + sort order: + + Map-reduce partition columns: + expr: substr(0, 1, 1) + type: string + tag: -1 + Reduce Operator Tree: + Group By Operator + keys: + expr: KEY.0 + type: string + mode: complete + Select Operator + expressions: + expr: 0 + type: string + File Output Operator + compressed: false + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.MetadataTypedColumnsetSerDe + name: dest1 + + Stage: Stage-0 + Move Operator + tables: + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.MetadataTypedColumnsetSerDe + name: dest1 + + +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 Index: ql/src/test/results/clientpositive/groupby1_noskew.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby1_noskew.q.out (revision 0) +++ ql/src/test/results/clientpositive/groupby1_noskew.q.out (revision 0) @@ -0,0 +1,372 @@ +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF src)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB dest_g1)) (TOK_SELECT (TOK_SELEXPR (TOK_COLREF src key)) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION substr (TOK_COLREF src value) 5)))) (TOK_GROUPBY (TOK_COLREF src key)))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + Reduce Output Operator + key expressions: + expr: key + type: string + sort order: + + Map-reduce partition columns: + expr: key + type: string + tag: -1 + value expressions: + expr: substr(value, 5) + type: string + Reduce Operator Tree: + Group By Operator + aggregations: + expr: sum(UDFToDouble(VALUE.0)) + keys: + expr: KEY.0 + type: string + mode: complete + Select Operator + expressions: + expr: 0 + type: string + expr: 1 + type: double + Select Operator + expressions: + expr: UDFToInteger(0) + type: int + expr: 1 + type: double + File Output Operator + compressed: false + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest_g1 + + Stage: Stage-0 + Move Operator + tables: + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest_g1 + + +0 0.0 +10 10.0 +100 200.0 +103 206.0 +104 208.0 +105 105.0 +11 11.0 +111 111.0 +113 226.0 +114 114.0 +116 116.0 +118 236.0 +119 357.0 +12 24.0 +120 240.0 +125 250.0 +126 126.0 +128 384.0 +129 258.0 +131 131.0 +133 133.0 +134 268.0 +136 136.0 +137 274.0 +138 552.0 +143 143.0 +145 145.0 +146 292.0 +149 298.0 +15 30.0 +150 150.0 +152 304.0 +153 153.0 +155 155.0 +156 156.0 +157 157.0 +158 158.0 +160 160.0 +162 162.0 +163 163.0 +164 328.0 +165 330.0 +166 166.0 +167 501.0 +168 168.0 +169 676.0 +17 17.0 +170 170.0 +172 344.0 +174 348.0 +175 350.0 +176 352.0 +177 177.0 +178 178.0 +179 358.0 +18 36.0 +180 180.0 +181 181.0 +183 183.0 +186 186.0 +187 561.0 +189 189.0 +19 19.0 +190 190.0 +191 382.0 +192 192.0 +193 579.0 +194 194.0 +195 390.0 +196 196.0 +197 394.0 +199 597.0 +2 2.0 +20 20.0 +200 400.0 +201 201.0 +202 202.0 +203 406.0 +205 410.0 +207 414.0 +208 624.0 +209 418.0 +213 426.0 +214 214.0 +216 432.0 +217 434.0 +218 218.0 +219 438.0 +221 442.0 +222 222.0 +223 446.0 +224 448.0 +226 226.0 +228 228.0 +229 458.0 +230 1150.0 +233 466.0 +235 235.0 +237 474.0 +238 476.0 +239 478.0 +24 48.0 +241 241.0 +242 484.0 +244 244.0 +247 247.0 +248 248.0 +249 249.0 +252 252.0 +255 510.0 +256 512.0 +257 257.0 +258 258.0 +26 52.0 +260 260.0 +262 262.0 +263 263.0 +265 530.0 +266 266.0 +27 27.0 +272 544.0 +273 819.0 +274 274.0 +275 275.0 +277 1108.0 +278 556.0 +28 28.0 +280 560.0 +281 562.0 +282 564.0 +283 283.0 +284 284.0 +285 285.0 +286 286.0 +287 287.0 +288 576.0 +289 289.0 +291 291.0 +292 292.0 +296 296.0 +298 894.0 +30 30.0 +302 302.0 +305 305.0 +306 306.0 +307 614.0 +308 308.0 +309 618.0 +310 310.0 +311 933.0 +315 315.0 +316 948.0 +317 634.0 +318 954.0 +321 642.0 +322 644.0 +323 323.0 +325 650.0 +327 981.0 +33 33.0 +331 662.0 +332 332.0 +333 666.0 +335 335.0 +336 336.0 +338 338.0 +339 339.0 +34 34.0 +341 341.0 +342 684.0 +344 688.0 +345 345.0 +348 1740.0 +35 105.0 +351 351.0 +353 706.0 +356 356.0 +360 360.0 +362 362.0 +364 364.0 +365 365.0 +366 366.0 +367 734.0 +368 368.0 +369 1107.0 +37 74.0 +373 373.0 +374 374.0 +375 375.0 +377 377.0 +378 378.0 +379 379.0 +382 764.0 +384 1152.0 +386 386.0 +389 389.0 +392 392.0 +393 393.0 +394 394.0 +395 790.0 +396 1188.0 +397 794.0 +399 798.0 +4 4.0 +400 400.0 +401 2005.0 +402 402.0 +403 1209.0 +404 808.0 +406 1624.0 +407 407.0 +409 1227.0 +41 41.0 +411 411.0 +413 826.0 +414 828.0 +417 1251.0 +418 418.0 +419 419.0 +42 84.0 +421 421.0 +424 848.0 +427 427.0 +429 858.0 +43 43.0 +430 1290.0 +431 1293.0 +432 432.0 +435 435.0 +436 436.0 +437 437.0 +438 1314.0 +439 878.0 +44 44.0 +443 443.0 +444 444.0 +446 446.0 +448 448.0 +449 449.0 +452 452.0 +453 453.0 +454 1362.0 +455 455.0 +457 457.0 +458 916.0 +459 918.0 +460 460.0 +462 924.0 +463 926.0 +466 1398.0 +467 467.0 +468 1872.0 +469 2345.0 +47 47.0 +470 470.0 +472 472.0 +475 475.0 +477 477.0 +478 956.0 +479 479.0 +480 1440.0 +481 481.0 +482 482.0 +483 483.0 +484 484.0 +485 485.0 +487 487.0 +489 1956.0 +490 490.0 +491 491.0 +492 984.0 +493 493.0 +494 494.0 +495 495.0 +496 496.0 +497 497.0 +498 1494.0 +5 15.0 +51 102.0 +53 53.0 +54 54.0 +57 57.0 +58 116.0 +64 64.0 +65 65.0 +66 66.0 +67 134.0 +69 69.0 +70 210.0 +72 144.0 +74 74.0 +76 152.0 +77 77.0 +78 78.0 +8 8.0 +80 80.0 +82 82.0 +83 166.0 +84 168.0 +85 85.0 +86 86.0 +87 87.0 +9 9.0 +90 270.0 +92 92.0 +95 190.0 +96 96.0 +97 194.0 +98 196.0 Index: ql/src/test/results/clientpositive/groupby3_map.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby3_map.q.out (revision 744613) +++ ql/src/test/results/clientpositive/groupby3_map.q.out (working copy) @@ -3,8 +3,7 @@ STAGE DEPENDENCIES: Stage-1 is a root stage - Stage-2 depends on stages: Stage-1 - Stage-0 depends on stages: Stage-2 + Stage-0 depends on stages: Stage-1 STAGE PLANS: Stage: Stage-1 @@ -51,40 +50,6 @@ aggregations: expr: sum(VALUE.0) expr: max(VALUE.1) - expr: avg(DISTINCT UDFToDouble(KEY.0)) - expr: min(VALUE.3) - expr: avg(VALUE.4) - mode: partial2 - File Output Operator - compressed: false - table: - input format: org.apache.hadoop.mapred.SequenceFileInputFormat - output format: org.apache.hadoop.mapred.SequenceFileOutputFormat - name: binary_table - - Stage: Stage-2 - Map Reduce - Alias -> Map Operator Tree: - /data/users/athusoo/commits/hive_trunk_ws2/build/ql/tmp/1134254033/650040203.10001 - Reduce Output Operator - sort order: - tag: -1 - value expressions: - expr: 0 - type: double - expr: 1 - type: string - expr: 2 - type: string - expr: 3 - type: string - expr: 4 - type: string - Reduce Operator Tree: - Group By Operator - aggregations: - expr: sum(VALUE.0) - expr: max(VALUE.1) expr: avg(VALUE.2) expr: min(VALUE.3) expr: avg(VALUE.4) Index: ql/src/test/results/clientpositive/groupby6_noskew.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby6_noskew.q.out (revision 0) +++ ql/src/test/results/clientpositive/groupby6_noskew.q.out (revision 0) @@ -0,0 +1,64 @@ +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF src)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB dest1)) (TOK_SELECTDI (TOK_SELEXPR (TOK_FUNCTION substr (TOK_COLREF src value) 5 1))))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + Select Operator + expressions: + expr: value + type: string + Reduce Output Operator + key expressions: + expr: substr(0, 5, 1) + type: string + sort order: + + Map-reduce partition columns: + expr: substr(0, 5, 1) + type: string + tag: -1 + Reduce Operator Tree: + Group By Operator + keys: + expr: KEY.0 + type: string + mode: complete + Select Operator + expressions: + expr: 0 + type: string + File Output Operator + compressed: false + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.MetadataTypedColumnsetSerDe + name: dest1 + + Stage: Stage-0 + Move Operator + tables: + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.MetadataTypedColumnsetSerDe + name: dest1 + + +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 Index: ql/src/test/results/clientpositive/groupby3_noskew.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby3_noskew.q.out (revision 0) +++ ql/src/test/results/clientpositive/groupby3_noskew.q.out (revision 0) @@ -0,0 +1,75 @@ +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF src)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB dest1)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION substr (TOK_COLREF src value) 5))) (TOK_SELEXPR (TOK_FUNCTION avg (TOK_FUNCTION substr (TOK_COLREF src value) 5))) (TOK_SELEXPR (TOK_FUNCTIONDI avg (TOK_FUNCTION substr (TOK_COLREF src value) 5))) (TOK_SELEXPR (TOK_FUNCTION max (TOK_FUNCTION substr (TOK_COLREF src value) 5))) (TOK_SELEXPR (TOK_FUNCTION min (TOK_FUNCTION substr (TOK_COLREF src value) 5)))))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + Select Operator + expressions: + expr: value + type: string + Reduce Output Operator + key expressions: + expr: substr(0, 5) + type: string + sort order: + + tag: -1 + Reduce Operator Tree: + Group By Operator + aggregations: + expr: sum(UDFToDouble(KEY.0)) + expr: max(KEY.0) + expr: avg(DISTINCT UDFToDouble(KEY.0)) + expr: min(KEY.0) + expr: avg(KEY.0) + mode: complete + Select Operator + expressions: + expr: 0 + type: double + expr: 4 + type: double + expr: 2 + type: double + expr: 1 + type: string + expr: 3 + type: string + Select Operator + expressions: + expr: 0 + type: double + expr: 1 + type: double + expr: 2 + type: double + expr: UDFToDouble(3) + type: double + expr: UDFToDouble(4) + type: double + File Output Operator + compressed: false + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest1 + + Stage: Stage-0 + Move Operator + tables: + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest1 + + +130091.0 260.182 256.10355987055016 98.0 0.0 Index: ql/src/test/results/clientpositive/groupby8_noskew.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby8_noskew.q.out (revision 0) +++ ql/src/test/results/clientpositive/groupby8_noskew.q.out (revision 0) @@ -0,0 +1,119 @@ +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF SRC)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB DEST1)) (TOK_SELECT (TOK_SELEXPR (TOK_COLREF SRC key)) (TOK_SELEXPR (TOK_FUNCTIONDI COUNT (TOK_FUNCTION SUBSTR (TOK_COLREF SRC value) 5)))) (TOK_GROUPBY (TOK_COLREF SRC key))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB DEST2)) (TOK_SELECT (TOK_SELEXPR (TOK_COLREF SRC key)) (TOK_SELEXPR (TOK_FUNCTIONDI COUNT (TOK_FUNCTION SUBSTR (TOK_COLREF SRC value) 5)))) (TOK_GROUPBY (TOK_COLREF SRC key)))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1, Stage-2 + Stage-2 depends on stages: Stage-1 + Stage-0 depends on stages: Stage-1, Stage-2 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + Reduce Output Operator + key expressions: + expr: key + type: string + expr: substr(value, 5) + type: string + sort order: ++ + Map-reduce partition columns: + expr: key + type: string + tag: -1 + File Output Operator + compressed: false + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.mapred.SequenceFileOutputFormat + name: binary_table + Reduce Operator Tree: + Group By Operator + aggregations: + expr: count(DISTINCT KEY.1) + keys: + expr: KEY.0 + type: string + mode: complete + Select Operator + expressions: + expr: 0 + type: string + expr: 1 + type: bigint + Select Operator + expressions: + expr: UDFToInteger(0) + type: int + expr: UDFToString(1) + type: string + File Output Operator + compressed: false + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest1 + + Stage: Stage-0 + Move Operator + tables: + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest1 + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest2 + + Stage: Stage-2 + Map Reduce + Alias -> Map Operator Tree: + /data/users/njain/hive4/hive4/build/ql/tmp/433864016/42871131.10002 + Reduce Output Operator + key expressions: + expr: key + type: string + expr: substr(value, 5) + type: string + sort order: ++ + Map-reduce partition columns: + expr: key + type: string + tag: -1 + Reduce Operator Tree: + Group By Operator + aggregations: + expr: count(DISTINCT KEY.1) + keys: + expr: KEY.0 + type: string + mode: complete + Select Operator + expressions: + expr: 0 + type: string + expr: 1 + type: bigint + Select Operator + expressions: + expr: UDFToInteger(0) + type: int + expr: UDFToString(1) + type: string + File Output Operator + compressed: false + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest2 + + Index: ql/src/test/results/clientpositive/groupby5_noskew.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby5_noskew.q.out (revision 0) +++ ql/src/test/results/clientpositive/groupby5_noskew.q.out (revision 0) @@ -0,0 +1,372 @@ +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF src)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB dest1)) (TOK_SELECT (TOK_SELEXPR (TOK_COLREF src key)) (TOK_SELEXPR (TOK_FUNCTION sum (TOK_FUNCTION substr (TOK_COLREF src value) 5)))) (TOK_GROUPBY (TOK_COLREF src key)))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + Reduce Output Operator + key expressions: + expr: key + type: string + sort order: + + Map-reduce partition columns: + expr: key + type: string + tag: -1 + value expressions: + expr: substr(value, 5) + type: string + Reduce Operator Tree: + Group By Operator + aggregations: + expr: sum(UDFToDouble(VALUE.0)) + keys: + expr: KEY.0 + type: string + mode: complete + Select Operator + expressions: + expr: 0 + type: string + expr: 1 + type: double + Select Operator + expressions: + expr: UDFToInteger(0) + type: int + expr: UDFToString(1) + type: string + File Output Operator + compressed: false + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest1 + + Stage: Stage-0 + Move Operator + tables: + replace: true + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat + serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe + name: dest1 + + +0 0.0 +10 10.0 +100 200.0 +103 206.0 +104 208.0 +105 105.0 +11 11.0 +111 111.0 +113 226.0 +114 114.0 +116 116.0 +118 236.0 +119 357.0 +12 24.0 +120 240.0 +125 250.0 +126 126.0 +128 384.0 +129 258.0 +131 131.0 +133 133.0 +134 268.0 +136 136.0 +137 274.0 +138 552.0 +143 143.0 +145 145.0 +146 292.0 +149 298.0 +15 30.0 +150 150.0 +152 304.0 +153 153.0 +155 155.0 +156 156.0 +157 157.0 +158 158.0 +160 160.0 +162 162.0 +163 163.0 +164 328.0 +165 330.0 +166 166.0 +167 501.0 +168 168.0 +169 676.0 +17 17.0 +170 170.0 +172 344.0 +174 348.0 +175 350.0 +176 352.0 +177 177.0 +178 178.0 +179 358.0 +18 36.0 +180 180.0 +181 181.0 +183 183.0 +186 186.0 +187 561.0 +189 189.0 +19 19.0 +190 190.0 +191 382.0 +192 192.0 +193 579.0 +194 194.0 +195 390.0 +196 196.0 +197 394.0 +199 597.0 +2 2.0 +20 20.0 +200 400.0 +201 201.0 +202 202.0 +203 406.0 +205 410.0 +207 414.0 +208 624.0 +209 418.0 +213 426.0 +214 214.0 +216 432.0 +217 434.0 +218 218.0 +219 438.0 +221 442.0 +222 222.0 +223 446.0 +224 448.0 +226 226.0 +228 228.0 +229 458.0 +230 1150.0 +233 466.0 +235 235.0 +237 474.0 +238 476.0 +239 478.0 +24 48.0 +241 241.0 +242 484.0 +244 244.0 +247 247.0 +248 248.0 +249 249.0 +252 252.0 +255 510.0 +256 512.0 +257 257.0 +258 258.0 +26 52.0 +260 260.0 +262 262.0 +263 263.0 +265 530.0 +266 266.0 +27 27.0 +272 544.0 +273 819.0 +274 274.0 +275 275.0 +277 1108.0 +278 556.0 +28 28.0 +280 560.0 +281 562.0 +282 564.0 +283 283.0 +284 284.0 +285 285.0 +286 286.0 +287 287.0 +288 576.0 +289 289.0 +291 291.0 +292 292.0 +296 296.0 +298 894.0 +30 30.0 +302 302.0 +305 305.0 +306 306.0 +307 614.0 +308 308.0 +309 618.0 +310 310.0 +311 933.0 +315 315.0 +316 948.0 +317 634.0 +318 954.0 +321 642.0 +322 644.0 +323 323.0 +325 650.0 +327 981.0 +33 33.0 +331 662.0 +332 332.0 +333 666.0 +335 335.0 +336 336.0 +338 338.0 +339 339.0 +34 34.0 +341 341.0 +342 684.0 +344 688.0 +345 345.0 +348 1740.0 +35 105.0 +351 351.0 +353 706.0 +356 356.0 +360 360.0 +362 362.0 +364 364.0 +365 365.0 +366 366.0 +367 734.0 +368 368.0 +369 1107.0 +37 74.0 +373 373.0 +374 374.0 +375 375.0 +377 377.0 +378 378.0 +379 379.0 +382 764.0 +384 1152.0 +386 386.0 +389 389.0 +392 392.0 +393 393.0 +394 394.0 +395 790.0 +396 1188.0 +397 794.0 +399 798.0 +4 4.0 +400 400.0 +401 2005.0 +402 402.0 +403 1209.0 +404 808.0 +406 1624.0 +407 407.0 +409 1227.0 +41 41.0 +411 411.0 +413 826.0 +414 828.0 +417 1251.0 +418 418.0 +419 419.0 +42 84.0 +421 421.0 +424 848.0 +427 427.0 +429 858.0 +43 43.0 +430 1290.0 +431 1293.0 +432 432.0 +435 435.0 +436 436.0 +437 437.0 +438 1314.0 +439 878.0 +44 44.0 +443 443.0 +444 444.0 +446 446.0 +448 448.0 +449 449.0 +452 452.0 +453 453.0 +454 1362.0 +455 455.0 +457 457.0 +458 916.0 +459 918.0 +460 460.0 +462 924.0 +463 926.0 +466 1398.0 +467 467.0 +468 1872.0 +469 2345.0 +47 47.0 +470 470.0 +472 472.0 +475 475.0 +477 477.0 +478 956.0 +479 479.0 +480 1440.0 +481 481.0 +482 482.0 +483 483.0 +484 484.0 +485 485.0 +487 487.0 +489 1956.0 +490 490.0 +491 491.0 +492 984.0 +493 493.0 +494 494.0 +495 495.0 +496 496.0 +497 497.0 +498 1494.0 +5 15.0 +51 102.0 +53 53.0 +54 54.0 +57 57.0 +58 116.0 +64 64.0 +65 65.0 +66 66.0 +67 134.0 +69 69.0 +70 210.0 +72 144.0 +74 74.0 +76 152.0 +77 77.0 +78 78.0 +8 8.0 +80 80.0 +82 82.0 +83 166.0 +84 168.0 +85 85.0 +86 86.0 +87 87.0 +9 9.0 +90 270.0 +92 92.0 +95 190.0 +96 96.0 +97 194.0 +98 196.0 Index: ql/src/test/results/clientpositive/groupby1_map.q.out =================================================================== --- ql/src/test/results/clientpositive/groupby1_map.q.out (revision 744613) +++ ql/src/test/results/clientpositive/groupby1_map.q.out (working copy) @@ -3,8 +3,7 @@ STAGE DEPENDENCIES: Stage-1 is a root stage - Stage-2 depends on stages: Stage-1 - Stage-0 depends on stages: Stage-2 + Stage-0 depends on stages: Stage-1 STAGE PLANS: Stage: Stage-1 @@ -37,37 +36,6 @@ keys: expr: KEY.0 type: string - mode: partial2 - File Output Operator - compressed: false - table: - input format: org.apache.hadoop.mapred.SequenceFileInputFormat - output format: org.apache.hadoop.mapred.SequenceFileOutputFormat - name: binary_table - - Stage: Stage-2 - Map Reduce - Alias -> Map Operator Tree: - /tmp/hive-zshao/632752964/335784834.10001 - Reduce Output Operator - key expressions: - expr: 0 - type: string - sort order: + - Map-reduce partition columns: - expr: 0 - type: string - tag: -1 - value expressions: - expr: 1 - type: double - Reduce Operator Tree: - Group By Operator - aggregations: - expr: sum(VALUE.0) - keys: - expr: KEY.0 - type: string mode: final Select Operator expressions: Index: ql/src/test/queries/clientpositive/groupby7_noskew.q =================================================================== --- ql/src/test/queries/clientpositive/groupby7_noskew.q (revision 0) +++ ql/src/test/queries/clientpositive/groupby7_noskew.q (revision 0) @@ -0,0 +1,19 @@ +set hive.groupby.skewindata=false; + +CREATE TABLE DEST1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST2(key INT, value STRING) STORED AS TEXTFILE; + +SET hive.exec.compress.intermediate=true; +SET hive.exec.compress.output=true; + +EXPLAIN +FROM SRC +INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, sum(SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, sum(SUBSTR(SRC.value,5)) GROUP BY SRC.key; + +FROM SRC +INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, sum(SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, sum(SUBSTR(SRC.value,5)) GROUP BY SRC.key; + +SELECT DEST1.* FROM DEST1; +SELECT DEST2.* FROM DEST2; Index: ql/src/test/queries/clientpositive/groupby8_noskew.q =================================================================== --- ql/src/test/queries/clientpositive/groupby8_noskew.q (revision 0) +++ ql/src/test/queries/clientpositive/groupby8_noskew.q (revision 0) @@ -0,0 +1,13 @@ +set hive.groupby.skewindata=false; + +CREATE TABLE DEST1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST2(key INT, value STRING) STORED AS TEXTFILE; + +EXPLAIN +FROM SRC +INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; + +SELECT DEST1.* FROM DEST1; +SELECT DEST2.* FROM DEST2; + Index: ql/src/test/queries/clientpositive/groupby1_noskew.q =================================================================== --- ql/src/test/queries/clientpositive/groupby1_noskew.q (revision 0) +++ ql/src/test/queries/clientpositive/groupby1_noskew.q (revision 0) @@ -0,0 +1,10 @@ +set hive.groupby.skewindata=false; + +CREATE TABLE dest_g1(key INT, value DOUBLE) STORED AS TEXTFILE; + +EXPLAIN +FROM src INSERT OVERWRITE TABLE dest_g1 SELECT src.key, sum(substr(src.value,5)) GROUP BY src.key; + +FROM src INSERT OVERWRITE TABLE dest_g1 SELECT src.key, sum(substr(src.value,5)) GROUP BY src.key; + +SELECT dest_g1.* FROM dest_g1; Property changes on: ql/src/test/queries/clientpositive/groupby1_noskew.q ___________________________________________________________________ Added: svn:executable + * Index: ql/src/test/queries/clientpositive/groupby2_noskew.q =================================================================== --- ql/src/test/queries/clientpositive/groupby2_noskew.q (revision 0) +++ ql/src/test/queries/clientpositive/groupby2_noskew.q (revision 0) @@ -0,0 +1,12 @@ +set hive.groupby.skewindata=false; + +CREATE TABLE dest_g2(key STRING, c1 INT, c2 STRING) STORED AS TEXTFILE; + +EXPLAIN +FROM src +INSERT OVERWRITE TABLE dest_g2 SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))) GROUP BY substr(src.key,1,1); + +FROM src +INSERT OVERWRITE TABLE dest_g2 SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))) GROUP BY substr(src.key,1,1); + +SELECT dest_g2.* FROM dest_g2; Property changes on: ql/src/test/queries/clientpositive/groupby2_noskew.q ___________________________________________________________________ Added: svn:executable + * Index: ql/src/test/queries/clientpositive/groupby3_noskew.q =================================================================== --- ql/src/test/queries/clientpositive/groupby3_noskew.q (revision 0) +++ ql/src/test/queries/clientpositive/groupby3_noskew.q (revision 0) @@ -0,0 +1,12 @@ +set hive.groupby.skewindata=false; + +CREATE TABLE dest1(c1 DOUBLE, c2 DOUBLE, c3 DOUBLE, c4 DOUBLE, c5 DOUBLE) STORED AS TEXTFILE; + +EXPLAIN +FROM src +INSERT OVERWRITE TABLE dest1 SELECT sum(substr(src.value,5)), avg(substr(src.value,5)), avg(DISTINCT substr(src.value,5)), max(substr(src.value,5)), min(substr(src.value,5)); + +FROM src +INSERT OVERWRITE TABLE dest1 SELECT sum(substr(src.value,5)), avg(substr(src.value,5)), avg(DISTINCT substr(src.value,5)), max(substr(src.value,5)), min(substr(src.value,5)); + +SELECT dest1.* FROM dest1; Property changes on: ql/src/test/queries/clientpositive/groupby3_noskew.q ___________________________________________________________________ Added: svn:executable + * Index: ql/src/test/queries/clientpositive/groupby4_noskew.q =================================================================== --- ql/src/test/queries/clientpositive/groupby4_noskew.q (revision 0) +++ ql/src/test/queries/clientpositive/groupby4_noskew.q (revision 0) @@ -0,0 +1,13 @@ +set hive.groupby.skewindata=false; + +CREATE TABLE dest1(c1 STRING) STORED AS TEXTFILE; + +EXPLAIN +FROM src +INSERT OVERWRITE TABLE dest1 SELECT substr(src.key,1,1) GROUP BY substr(src.key,1,1); + +FROM src +INSERT OVERWRITE TABLE dest1 SELECT substr(src.key,1,1) GROUP BY substr(src.key,1,1); + +SELECT dest1.* FROM dest1; + Property changes on: ql/src/test/queries/clientpositive/groupby4_noskew.q ___________________________________________________________________ Added: svn:executable + * Index: ql/src/test/queries/clientpositive/groupby5_noskew.q =================================================================== --- ql/src/test/queries/clientpositive/groupby5_noskew.q (revision 0) +++ ql/src/test/queries/clientpositive/groupby5_noskew.q (revision 0) @@ -0,0 +1,17 @@ +set hive.groupby.skewindata=false; + +CREATE TABLE dest1(key INT, value STRING) STORED AS TEXTFILE; + +EXPLAIN +INSERT OVERWRITE TABLE dest1 +SELECT src.key, sum(substr(src.value,5)) +FROM src +GROUP BY src.key; + +INSERT OVERWRITE TABLE dest1 +SELECT src.key, sum(substr(src.value,5)) +FROM src +GROUP BY src.key; + +SELECT dest1.* FROM dest1; + Property changes on: ql/src/test/queries/clientpositive/groupby5_noskew.q ___________________________________________________________________ Added: svn:executable + * Index: ql/src/test/queries/clientpositive/groupby6_noskew.q =================================================================== --- ql/src/test/queries/clientpositive/groupby6_noskew.q (revision 0) +++ ql/src/test/queries/clientpositive/groupby6_noskew.q (revision 0) @@ -0,0 +1,14 @@ +set hive.groupby.skewindata=false; + +CREATE TABLE dest1(c1 STRING) STORED AS TEXTFILE; + +EXPLAIN +FROM src +INSERT OVERWRITE TABLE dest1 SELECT DISTINCT substr(src.value,5,1); + +FROM src +INSERT OVERWRITE TABLE dest1 SELECT DISTINCT substr(src.value,5,1); + +SELECT dest1.* FROM dest1; + + Property changes on: ql/src/test/queries/clientpositive/groupby6_noskew.q ___________________________________________________________________ Added: svn:executable + * Index: ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java =================================================================== --- ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java (revision 744613) +++ ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java (working copy) @@ -1951,7 +1951,7 @@ * Evaluate partial aggregates first, followed by actual aggregates. */ @SuppressWarnings("nls") - private Operator genGroupByPlan4MR(String dest, QB qb, + private Operator genGroupByPlanMapAggr1MR(String dest, QB qb, Operator inputOperatorInfo) throws SemanticException { QBParseInfo parseInfo = qb.getParseInfo(); @@ -1960,6 +1960,37 @@ Operator groupByOperatorInfo = genGroupByPlanMapGroupByOperator(qb, dest, inputOperatorInfo, groupByDesc.Mode.HASH); + int numReducers = -1; + + // Optimize the scenario when there are no grouping keys and no distinct - only 1 reducer is needed + // For eg: select count(1) from T where t.ds = .... + if (!optimizeMapAggrGroupBy(dest, qb)) + numReducers = 1; + + // ////// Generate ReduceSink Operator + Operator reduceSinkOperatorInfo = + genGroupByPlanReduceSinkOperator(parseInfo, dest, groupByOperatorInfo, numReducers); + + return genGroupByPlanGroupByOperator2MR(parseInfo, dest, reduceSinkOperatorInfo, groupByDesc.Mode.FINAL); + } + + /** + * Generate a Group-By plan using a 2 map-reduce jobs. First perform a map + * side partial aggregation (to reduce the amount of data). Then spray by + * the distinct key (or a random number) in hope of getting a uniform + * distribution, and compute partial aggregates grouped by that distinct key. + * Evaluate partial aggregates first, followed by actual aggregates. + */ + @SuppressWarnings("nls") + private Operator genGroupByPlanMapAggr2MR(String dest, QB qb, + Operator inputOperatorInfo) throws SemanticException { + + QBParseInfo parseInfo = qb.getParseInfo(); + + // ////// Generate GroupbyOperator for a map-side partial aggregation + Operator groupByOperatorInfo = genGroupByPlanMapGroupByOperator(qb, + dest, inputOperatorInfo, groupByDesc.Mode.HASH); + // Optimize the scenario when there are no grouping keys and no distinct - 2 map-reduce jobs are not needed // For eg: select count(1) from T where t.ds = .... if (!optimizeMapAggrGroupBy(dest, qb)) { @@ -2739,9 +2770,11 @@ || getGroupByForClause(qbp, dest).size() > 0) { if (conf.getVar(HiveConf.ConfVars.HIVEMAPSIDEAGGREGATE).equalsIgnoreCase("true")) - curr = genGroupByPlan4MR(dest, qb, curr); + curr = genGroupByPlanMapAggr1MR(dest, qb, curr); + else if (conf.getVar(HiveConf.ConfVars.HIVEGROUPBYSKEW).equalsIgnoreCase("true")) + curr = genGroupByPlan2MR(dest, qb, curr); else - curr = genGroupByPlan2MR(dest, qb, curr); + curr = genGroupByPlan1MR(dest, qb, curr); } curr = genSelectPlan(dest, qb, curr); Index: ql/src/java/org/apache/hadoop/hive/ql/udf/UDAFAvg.java =================================================================== --- ql/src/java/org/apache/hadoop/hive/ql/udf/UDAFAvg.java (revision 744613) +++ ql/src/java/org/apache/hadoop/hive/ql/udf/UDAFAvg.java (working copy) @@ -54,9 +54,15 @@ public boolean merge(String o) { if (o != null && !o.isEmpty()) { int pos = o.indexOf('/'); - assert(pos != -1); - mSum += Double.parseDouble(o.substring(0, pos)); - mCount += Long.parseLong(o.substring(pos+1)); + // In case merge is called without an iterate, the + if (pos == -1) { + mSum += Double.parseDouble(o); + mCount ++; + } + else { + mSum += Double.parseDouble(o.substring(0, pos)); + mCount += Long.parseLong(o.substring(pos+1)); + } } return true; }