set hive.map.aggr=true;
set hive.groupby.skewindata=false;

-- SORT_QUERY_RESULTS

CREATE TABLE T1(key STRING, val STRING) STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1;

EXPLAIN
SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube;

SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube;

EXPLAIN
SELECT key, val, GROUPING__ID, count(1) FROM T1 GROUP BY key, val with cube;

SELECT key, val, GROUPING__ID, count(1) FROM T1 GROUP BY key, val with cube;

EXPLAIN
SELECT key, count(distinct val) FROM T1 GROUP BY key with cube;

SELECT key, count(distinct val) FROM T1 GROUP BY key with cube;

set hive.groupby.skewindata=true;

EXPLAIN
SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube;

SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube;

EXPLAIN
SELECT key, count(distinct val) FROM T1 GROUP BY key with cube;

SELECT key, count(distinct val) FROM T1 GROUP BY key with cube;


set hive.multigroupby.singlereducer=true;

CREATE TABLE T2(key1 STRING, key2 STRING, val INT) STORED AS TEXTFILE;
CREATE TABLE T3(key1 STRING, key2 STRING, val INT) STORED AS TEXTFILE;

EXPLAIN
FROM T1
INSERT OVERWRITE TABLE T2 SELECT key, val, count(1) group by key, val with cube
INSERT OVERWRITE TABLE T3 SELECT key, val, sum(1) group by key, val with cube;


FROM T1
INSERT OVERWRITE TABLE T2 SELECT key, val, count(1) group by key, val with cube
INSERT OVERWRITE TABLE T3 SELECT key, val, sum(1) group by key, val with cube;