Saving all output to "!!{outputDirectory}!!/groupby_multi_single_reducer2.q.raw". Enter "record" with no arguments to stop it. >>> !run !!{qFileDirectory}!!/groupby_multi_single_reducer2.q >>> set hive.multigroupby.singlereducer=true; No rows affected >>> >>> CREATE TABLE dest_g2(key STRING, c1 INT) STORED AS TEXTFILE; No rows affected >>> CREATE TABLE dest_g3(key STRING, c1 INT, c2 INT) STORED AS TEXTFILE; No rows affected >>> >>> EXPLAIN FROM src INSERT OVERWRITE TABLE dest_g2 SELECT substr(src.key,1,1), count(DISTINCT src.key) WHERE substr(src.key,1,1) >= 5 GROUP BY substr(src.key,1,1) INSERT OVERWRITE TABLE dest_g3 SELECT substr(src.key,1,1), count(DISTINCT src.key), count(src.value) WHERE substr(src.key,1,1) < 5 GROUP BY substr(src.key,1,1); 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest_g2))) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) key) 1 1)) (TOK_SELEXPR (TOK_FUNCTIONDI count (. (TOK_TABLE_OR_COL src) key)))) (TOK_WHERE (>= (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) key) 1 1) 5)) (TOK_GROUPBY (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) key) 1 1))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest_g3))) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) key) 1 1)) (TOK_SELEXPR (TOK_FUNCTIONDI count (. (TOK_TABLE_OR_COL src) key))) (TOK_SELEXPR (TOK_FUNCTION count (. (TOK_TABLE_OR_COL src) value)))) (TOK_WHERE (< (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) key) 1 1) 5)) (TOK_GROUPBY (TOK_FUNCTION substr (. (TOK_TABLE_OR_COL src) key) 1 1))))' '' 'STAGE DEPENDENCIES:' ' Stage-2 is a root stage' ' Stage-0 depends on stages: Stage-2' ' Stage-3 depends on stages: Stage-0' ' Stage-1 depends on stages: Stage-2' ' Stage-4 depends on stages: Stage-1' '' 'STAGE PLANS:' ' Stage: Stage-2' ' Map Reduce' ' Alias -> Map Operator Tree:' ' src ' ' TableScan' ' alias: src' ' Filter Operator' ' predicate:' ' expr: ((substr(key, 1, 1) >= 5) or (substr(key, 1, 1) < 5))' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' outputColumnNames: key, value' ' Reduce Output Operator' ' key expressions:' ' expr: substr(key, 1, 1)' ' type: string' ' expr: key' ' type: string' ' sort order: ++' ' Map-reduce partition columns:' ' expr: substr(key, 1, 1)' ' type: string' ' tag: -1' ' value expressions:' ' expr: value' ' type: string' ' Reduce Operator Tree:' ' Forward' ' Group By Operator' ' aggregations:' ' expr: count(DISTINCT KEY._col1:0._col0)' ' bucketGroup: false' ' keys:' ' expr: KEY._col0' ' type: string' ' mode: complete' ' outputColumnNames: _col0, _col1' ' Select Operator' ' expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: bigint' ' outputColumnNames: _col0, _col1' ' Select Operator' ' expressions:' ' expr: _col0' ' type: string' ' expr: UDFToInteger(_col1)' ' type: int' ' outputColumnNames: _col0, _col1' ' File Output Operator' ' compressed: false' ' GlobalTableId: 1' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' name: groupby_multi_single_reducer2.dest_g2' ' Group By Operator' ' aggregations:' ' expr: count(DISTINCT KEY._col1:0._col0)' ' expr: count(VALUE._col0)' ' bucketGroup: false' ' keys:' ' expr: KEY._col0' ' type: string' ' mode: complete' ' outputColumnNames: _col0, _col1, _col2' ' Select Operator' ' expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: bigint' ' expr: _col2' ' type: bigint' ' outputColumnNames: _col0, _col1, _col2' ' Select Operator' ' expressions:' ' expr: _col0' ' type: string' ' expr: UDFToInteger(_col1)' ' type: int' ' expr: UDFToInteger(_col2)' ' type: int' ' outputColumnNames: _col0, _col1, _col2' ' File Output Operator' ' compressed: false' ' GlobalTableId: 2' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' name: groupby_multi_single_reducer2.dest_g3' '' ' Stage: Stage-0' ' Move Operator' ' tables:' ' replace: true' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' name: groupby_multi_single_reducer2.dest_g2' '' ' Stage: Stage-3' ' Stats-Aggr Operator' '' ' Stage: Stage-1' ' Move Operator' ' tables:' ' replace: true' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' name: groupby_multi_single_reducer2.dest_g3' '' ' Stage: Stage-4' ' Stats-Aggr Operator' '' '' 139 rows selected >>> >>> FROM src INSERT OVERWRITE TABLE dest_g2 SELECT substr(src.key,1,1), count(DISTINCT src.key) WHERE substr(src.key,1,1) >= 5 GROUP BY substr(src.key,1,1) INSERT OVERWRITE TABLE dest_g3 SELECT substr(src.key,1,1), count(DISTINCT src.key), count(src.value) WHERE substr(src.key,1,1) < 5 GROUP BY substr(src.key,1,1); '_col0','_col1','_col2' No rows selected >>> >>> SELECT * FROM dest_g2; 'key','c1' '0','1' '1','71' '2','69' '3','62' '4','74' '5','6' '6','5' '7','6' '8','8' '9','7' 10 rows selected >>> SELECT * FROM dest_g3; 'key','c1','c2' '0','1','3' '1','71','115' '2','69','111' '3','62','99' '4','74','124' '5','6','10' '6','5','6' '7','6','10' '8','8','10' '9','7','12' 10 rows selected >>> >>> DROP TABLE dest_g2; No rows affected >>> DROP TABLE dest_g3; No rows affected >>> !record