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

-- SORT_QUERY_RESULTS

CREATE TABLE dest1(key STRING, val1 INT, val2 INT) partitioned by (ds string);
CREATE TABLE dest2(key STRING, val1 INT, val2 INT) partitioned by (ds string);

EXPLAIN
FROM src
INSERT OVERWRITE TABLE dest1 partition(ds='111')
  SELECT src.value, count(src.key), count(distinct src.key) GROUP BY src.value
INSERT OVERWRITE TABLE dest2  partition(ds='111')
  SELECT substr(src.value, 5), count(src.key), count(distinct src.key) GROUP BY substr(src.value, 5);

FROM src
INSERT OVERWRITE TABLE dest1 partition(ds='111')
  SELECT src.value, count(src.key), count(distinct src.key) GROUP BY src.value
INSERT OVERWRITE TABLE dest2  partition(ds='111')
  SELECT substr(src.value, 5), count(src.key), count(distinct src.key) GROUP BY substr(src.value, 5);

SELECT * from dest1;
SELECT * from dest2;