You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by na...@apache.org on 2013/01/30 05:20:41 UTC
svn commit: r1440271 [2/7] - in /hive/trunk: ./
common/src/java/org/apache/hadoop/hive/conf/ conf/
ql/src/java/org/apache/hadoop/hive/ql/exec/
ql/src/java/org/apache/hadoop/hive/ql/lib/
ql/src/java/org/apache/hadoop/hive/ql/metadata/ ql/src/java/org/ap...
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,158 @@
+set hive.exec.infer.bucket.sort=true;
+
+-- This tests inferring how data is bucketed/sorted from the operators in the reducer
+-- and populating that information in partitions' metadata
+
+CREATE TABLE test_table (key STRING, value STRING) PARTITIONED BY (part STRING);
+
+-- Test group by, should be bucketed and sorted by group by key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, count(*) FROM src GROUP BY key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by where a key isn't selected, should not be bucketed or sorted
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, count(*) FROM src GROUP BY key, value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join, should be bucketed and sorted by join key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, a.value FROM src a JOIN src b ON a.key = b.key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join with two keys, should be bucketed and sorted by join keys
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, a.value FROM src a JOIN src b ON a.key = b.key AND a.value = b.value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join with two keys and only one selected, should not be bucketed or sorted
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, '1' FROM src a JOIN src b ON a.key = b.key AND a.value = b.value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join on three tables on same key, should be bucketed and sorted by join key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, c.value FROM src a JOIN src b ON (a.key = b.key) JOIN src c ON (b.key = c.key);
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join on three tables on different keys, should be bucketed and sorted by latter key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, c.value FROM src a JOIN src b ON (a.key = b.key) JOIN src c ON (b.value = c.value);
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test distribute by, should only be bucketed by key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM src DISTRIBUTE BY key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test sort by, should be sorted by key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM src SORT BY key ASC;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test sort by desc, should be sorted by key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM src SORT BY key DESC;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test cluster by, should be bucketed and sorted by key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM src CLUSTER BY key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test distribute by and sort by different keys, should be bucketed by one key sorted by the other
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM src DISTRIBUTE BY key SORT BY value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join in simple subquery, should be bucketed and sorted on key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value from (SELECT a.key, b.value FROM src a JOIN src b ON (a.key = b.key)) subq;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join in simple subquery renaming key column, should be bucketed and sorted on key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT k, value FROM (SELECT a.key as k, b.value FROM src a JOIN src b ON (a.key = b.key)) subq;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in simple subquery, should be bucketed and sorted on key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, cnt from (SELECT key, count(*) as cnt FROM src GROUP BY key) subq;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in simple subquery renaming key column, should be bucketed and sorted on key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT k, cnt FROM (SELECT key as k, count(*) as cnt FROM src GROUP BY key) subq;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in subquery with where outside, should still be bucketed and sorted on key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM (SELECT key, count(1) AS value FROM src group by key) a where key < 10;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in subquery with expression on value, should still be bucketed and sorted on key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value + 1 FROM (SELECT key, count(1) AS value FROM src group by key) a where key < 10;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in subquery with lateral view outside, should still be bucketed and sorted
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM (SELECT key FROM src group by key) a lateral view explode(array(1, 2)) value as value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in subquery with another group by outside, should be bucketed and sorted by the
+-- key of the outer group by
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT count(1), value FROM (SELECT key, count(1) as value FROM src group by key) a group by value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in subquery with select on outside reordering the columns, should be bucketed and
+-- sorted by the column the group by key ends up in
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT value, key FROM (SELECT key, count(1) as value FROM src group by key) a;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in subquery followed by distribute by, should only be bucketed by the distribute key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM (SELECT key, count(1) as value FROM src group by key) a distribute by key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in subquery followed by sort by, should only be sorted by the sort key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM (SELECT key, count(1) as value FROM src group by key) a sort by key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in subquery followed by transform script, should not be bucketed or sorted
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT TRANSFORM (a.key, a.value) USING 'cat' AS (key, value) FROM (SELECT key, count(1) AS value FROM src GROUP BY KEY) a;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by on function, should be bucketed and sorted by key and value because the function is applied in the mapper
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, value FROM (SELECT concat(key, "a") AS key, value, count(*) FROM src GROUP BY concat(key, "a"), value) a;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_bucketed_table.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_bucketed_table.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_bucketed_table.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_bucketed_table.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,26 @@
+set hive.enforce.bucketing=true;
+set hive.enforce.sorting=true;
+set hive.exec.infer.bucket.sort=true;
+
+-- Test writing to a bucketed table, the output should be bucketed by the bucketing key into the
+-- a number of files equal to the number of buckets
+CREATE TABLE test_table_bucketed (key STRING, value STRING) PARTITIONED BY (part STRING)
+CLUSTERED BY (value) SORTED BY (value) INTO 3 BUCKETS;
+
+-- Despite the fact that normally inferring would say this table is bucketed and sorted on key,
+-- this should be bucketed and sorted by value into 3 buckets
+INSERT OVERWRITE TABLE test_table_bucketed PARTITION (part = '1')
+SELECT key, count(1) FROM src GROUP BY KEY;
+
+DESCRIBE FORMATTED test_table_bucketed PARTITION (part = '1');
+
+-- If the count(*) from sampling the buckets matches the count(*) from each file, the table is
+-- bucketed
+SELECT COUNT(*) FROM test_table_bucketed TABLESAMPLE (BUCKET 1 OUT OF 3) WHERE part = '1';
+
+SELECT COUNT(*) FROM test_table_bucketed TABLESAMPLE (BUCKET 2 OUT OF 3) WHERE part = '1';
+
+SELECT COUNT(*) FROM test_table_bucketed TABLESAMPLE (BUCKET 3 OUT OF 3) WHERE part = '1';
+
+SELECT cnt FROM (SELECT INPUT__FILE__NAME, COUNT(*) cnt FROM test_table_bucketed WHERE part = '1'
+GROUP BY INPUT__FILE__NAME ORDER BY INPUT__FILE__NAME ASC LIMIT 3) a;
\ No newline at end of file
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_convert_join.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_convert_join.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_convert_join.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_convert_join.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,25 @@
+set hive.exec.infer.bucket.sort=true;
+set hive.exec.infer.bucket.sort.num.buckets.power.two=true;
+set hive.auto.convert.join=true;
+
+-- This tests inferring how data is bucketed/sorted from the operators in the reducer
+-- and populating that information in partitions' metadata. In particular, those cases
+-- where joins may be auto converted to map joins.
+
+CREATE TABLE test_table (key STRING, value STRING) PARTITIONED BY (part STRING);
+
+-- Tests a join which is converted to a map join, the output should be neither bucketed nor sorted
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, b.value FROM src a JOIN src b ON a.key = b.key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+set hive.mapjoin.check.memory.rows=1;
+set hive.mapjoin.localtask.max.memory.usage = 0.0001;
+
+-- Tests a join which is not converted to a map join, the output should be bucketed and sorted
+
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, b.value FROM src a JOIN src b ON a.key = b.key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_dyn_part.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_dyn_part.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_dyn_part.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_dyn_part.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,87 @@
+set hive.exec.infer.bucket.sort=true;
+set hive.exec.infer.bucket.sort.num.buckets.power.two=true;
+set hive.exec.dynamic.partition.mode=nonstrict;
+set hive.merge.mapfiles=false;
+set hive.merge.mapredfiles=false;
+
+-- This tests inferring how data is bucketed/sorted from the operators in the reducer
+-- and populating that information in partitions' metadata. In particular, those cases
+-- where dynamic partitioning is used.
+
+CREATE TABLE test_table LIKE srcpart;
+ALTER TABLE test_table SET FILEFORMAT RCFILE;
+
+-- Simple case, this should not be bucketed or sorted
+
+INSERT OVERWRITE TABLE test_table PARTITION (ds, hr)
+SELECT key, value, ds, hr FROM srcpart
+WHERE ds = '2008-04-08';
+
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='11');
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='12');
+
+-- This should not be bucketed or sorted since the partition keys are in the set of bucketed
+-- and sorted columns for the output
+
+INSERT OVERWRITE TABLE test_table PARTITION (ds, hr)
+SELECT key, COUNT(*), ds, hr FROM srcpart
+WHERE ds = '2008-04-08'
+GROUP BY key, ds, hr;
+
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='11');
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='12');
+
+-- Both partitions should be bucketed and sorted by key
+
+INSERT OVERWRITE TABLE test_table PARTITION (ds, hr)
+SELECT key, value, '2008-04-08', IF (key % 2 == 0, '11', '12') FROM
+(SELECT key, COUNT(*) AS value FROM srcpart
+WHERE ds = '2008-04-08'
+GROUP BY key) a;
+
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='11');
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='12');
+
+CREATE TABLE srcpart_merge_dp LIKE srcpart;
+
+CREATE TABLE srcpart_merge_dp_rc LIKE srcpart;
+ALTER TABLE srcpart_merge_dp_rc SET FILEFORMAT RCFILE;
+
+LOAD DATA LOCAL INPATH '../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp PARTITION(ds='2008-04-08', hr=11);
+LOAD DATA LOCAL INPATH '../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp PARTITION(ds='2008-04-08', hr=11);
+LOAD DATA LOCAL INPATH '../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp PARTITION(ds='2008-04-08', hr=11);
+LOAD DATA LOCAL INPATH '../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp PARTITION(ds='2008-04-08', hr=11);
+
+LOAD DATA LOCAL INPATH '../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp PARTITION(ds='2008-04-08', hr=12);
+
+INSERT OVERWRITE TABLE srcpart_merge_dp_rc PARTITION (ds = '2008-04-08', hr)
+SELECT key, value, hr FROM srcpart_merge_dp WHERE ds = '2008-04-08';
+
+set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
+set hive.merge.mapfiles=true;
+set hive.merge.mapredfiles=true;
+set hive.merge.smallfiles.avgsize=200;
+set hive.exec.compress.output=false;
+set hive.exec.dynamic.partition=true;
+set mapred.reduce.tasks=2;
+
+-- Tests dynamic partitions where bucketing/sorting can be inferred, but some partitions are
+-- merged and some are moved. Currently neither should be bucketed or sorted, in the future,
+-- (ds='2008-04-08', hr='12') may be bucketed and sorted, (ds='2008-04-08', hr='11') should
+-- definitely not be.
+
+EXPLAIN
+INSERT OVERWRITE TABLE test_table PARTITION (ds = '2008-04-08', hr)
+SELECT key, value, IF (key % 100 == 0, '11', '12') FROM
+(SELECT key, COUNT(*) AS value FROM srcpart
+WHERE ds = '2008-04-08'
+GROUP BY key) a;
+
+INSERT OVERWRITE TABLE test_table PARTITION (ds = '2008-04-08', hr)
+SELECT key, value, IF (key % 100 == 0, '11', '12') FROM
+(SELECT key, COUNT(*) AS value FROM srcpart
+WHERE ds = '2008-04-08'
+GROUP BY key) a;
+
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='11');
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='12');
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_grouping_operators.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_grouping_operators.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_grouping_operators.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_grouping_operators.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,57 @@
+set hive.exec.infer.bucket.sort=true;
+
+-- This tests inferring how data is bucketed/sorted from the operators in the reducer
+-- and populating that information in partitions' metadata, in particular, this tests
+-- the grouping operators rollup/cube/grouping sets
+
+CREATE TABLE test_table_out (key STRING, value STRING, agg STRING) PARTITIONED BY (part STRING);
+
+CREATE TABLE test_table_out_2 (key STRING, value STRING, grouping_key STRING, agg STRING) PARTITIONED BY (part STRING);
+
+-- Test rollup, should not be bucketed or sorted because its missing the grouping ID
+EXPLAIN INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT key, value, count(1) FROM src GROUP BY key, value WITH ROLLUP;
+
+INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT key, value, count(1) FROM src GROUP BY key, value WITH ROLLUP;
+
+DESCRIBE FORMATTED test_table_out PARTITION (part = '1');
+
+-- Test rollup, should be bucketed and sorted on key, value, grouping_key
+
+INSERT OVERWRITE TABLE test_table_out_2 PARTITION (part = '1')
+SELECT key, value, GROUPING__ID, count(1) FROM src GROUP BY key, value WITH ROLLUP;
+
+DESCRIBE FORMATTED test_table_out_2 PARTITION (part = '1');
+
+-- Test cube, should not be bucketed or sorted because its missing the grouping ID
+EXPLAIN INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT key, value, count(1) FROM src GROUP BY key, value WITH CUBE;
+
+INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT key, value, count(1) FROM src GROUP BY key, value WITH CUBE;
+
+DESCRIBE FORMATTED test_table_out PARTITION (part = '1');
+
+-- Test cube, should be bucketed and sorted on key, value, grouping_key
+
+INSERT OVERWRITE TABLE test_table_out_2 PARTITION (part = '1')
+SELECT key, value, GROUPING__ID, count(1) FROM src GROUP BY key, value WITH CUBE;
+
+DESCRIBE FORMATTED test_table_out_2 PARTITION (part = '1');
+
+-- Test grouping sets, should not be bucketed or sorted because its missing the grouping ID
+EXPLAIN INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT key, value, count(1) FROM src GROUP BY key, value GROUPING SETS (key, value);
+
+INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT key, value, count(1) FROM src GROUP BY key, value GROUPING SETS (key, value);
+
+DESCRIBE FORMATTED test_table_out PARTITION (part = '1');
+
+-- Test grouping sets, should be bucketed and sorted on key, value, grouping_key
+
+INSERT OVERWRITE TABLE test_table_out_2 PARTITION (part = '1')
+SELECT key, value, GROUPING__ID, count(1) FROM src GROUP BY key, value GROUPING SETS (key, value);
+
+DESCRIBE FORMATTED test_table_out_2 PARTITION (part = '1');
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_list_bucket.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_list_bucket.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_list_bucket.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_list_bucket.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,33 @@
+set hive.mapred.supports.subdirectories=true;
+set hive.merge.mapfiles=false;
+set hive.merge.mapredfiles=false;
+set mapred.input.dir.recursive=true;
+
+-- This tests that bucketing/sorting metadata is not inferred for tables with list bucketing
+
+-- INCLUDE_HADOOP_MAJOR_VERSIONS(0.23)
+
+-- create a skewed table
+CREATE TABLE list_bucketing_table (key STRING, value STRING)
+PARTITIONED BY (part STRING)
+SKEWED BY (key) ON ("484")
+STORED AS DIRECTORIES;
+
+-- Tests group by, the output should neither be bucketed nor sorted
+
+INSERT OVERWRITE TABLE list_bucketing_table PARTITION (part = '1')
+SELECT key, count(*) FROM src GROUP BY key;
+
+DESC FORMATTED list_bucketing_table PARTITION (part = '1');
+
+-- create a table skewed on a key which doesnt exist in the data
+CREATE TABLE list_bucketing_table2 (key STRING, value STRING)
+PARTITIONED BY (part STRING)
+SKEWED BY (key) ON ("abc")
+STORED AS DIRECTORIES;
+
+-- should not be bucketed or sorted
+INSERT OVERWRITE TABLE list_bucketing_table2 PARTITION (part = '1')
+SELECT key, count(*) FROM src GROUP BY key;
+
+DESC FORMATTED list_bucketing_table2 PARTITION (part = '1');
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_map_operators.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_map_operators.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_map_operators.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_map_operators.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,74 @@
+set hive.exec.infer.bucket.sort=true;
+set hive.enforce.bucketing = true;
+set hive.enforce.sorting = true;
+
+-- This tests inferring how data is bucketed/sorted from the operators in the reducer
+-- and populating that information in partitions' metadata, in particular, this tests
+-- that operators in the mapper have no effect
+
+CREATE TABLE test_table1 (key STRING, value STRING)
+CLUSTERED BY (key) SORTED BY (key DESC) INTO 2 BUCKETS;
+
+CREATE TABLE test_table2 (key STRING, value STRING)
+CLUSTERED BY (key) SORTED BY (key DESC) INTO 2 BUCKETS;
+
+INSERT OVERWRITE TABLE test_table1 SELECT key, value FROM src;
+
+INSERT OVERWRITE TABLE test_table2 SELECT key, value FROM src;
+
+CREATE TABLE test_table_out (key STRING, value STRING) PARTITIONED BY (part STRING);
+
+set hive.map.groupby.sorted=true;
+
+-- Test map group by doesn't affect inference, should not be bucketed or sorted
+EXPLAIN INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT key, count(*) FROM test_table1 GROUP BY key;
+
+INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT key, count(*) FROM test_table1 GROUP BY key;
+
+DESCRIBE FORMATTED test_table_out PARTITION (part = '1');
+
+-- Test map group by doesn't affect inference, should be bucketed and sorted by value
+EXPLAIN INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT a.key, a.value FROM (
+ SELECT key, count(*) AS value FROM test_table1 GROUP BY key
+) a JOIN (
+ SELECT key, value FROM src
+) b
+ON (a.value = b.value);
+
+INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT a.key, a.value FROM (
+ SELECT key, cast(count(*) AS STRING) AS value FROM test_table1 GROUP BY key
+) a JOIN (
+ SELECT key, value FROM src
+) b
+ON (a.value = b.value);
+
+DESCRIBE FORMATTED test_table_out PARTITION (part = '1');
+
+set hive.map.groupby.sorted=false;
+set hive.optimize.bucketmapjoin = true;
+set hive.optimize.bucketmapjoin.sortedmerge = true;
+
+-- Test SMB join doesn't affect inference, should not be bucketed or sorted
+EXPLAIN INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT /*+ MAPJOIN(a) */ a.key, b.value FROM test_table1 a JOIN test_table2 b ON a.key = b.key;
+
+INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT /*+ MAPJOIN(a) */ a.key, b.value FROM test_table1 a JOIN test_table2 b ON a.key = b.key;
+
+DESCRIBE FORMATTED test_table_out PARTITION (part = '1');
+
+-- Test SMB join doesn't affect inference, should be bucketed and sorted by key
+EXPLAIN INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT /*+ MAPJOIN(a) */ b.value, count(*) FROM test_table1 a JOIN test_table2 b ON a.key = b.key
+GROUP BY b.value;
+
+INSERT OVERWRITE TABLE test_table_out PARTITION (part = '1')
+SELECT /*+ MAPJOIN(a) */ b.value, count(*) FROM test_table1 a JOIN test_table2 b ON a.key = b.key
+GROUP BY b.value;
+
+DESCRIBE FORMATTED test_table_out PARTITION (part = '1');
+
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_merge.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_merge.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_merge.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_merge.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,25 @@
+set hive.exec.infer.bucket.sort=true;
+set hive.exec.infer.bucket.sort.num.buckets.power.two=true;
+set hive.merge.mapredfiles=true;
+set mapred.reduce.tasks=2;
+
+-- This tests inferring how data is bucketed/sorted from the operators in the reducer
+-- and populating that information in partitions' metadata. In particular, those cases
+-- where where merging may or may not be used.
+
+CREATE TABLE test_table (key STRING, value STRING) PARTITIONED BY (part STRING);
+
+-- Tests a reduce task followed by a merge. The output should be neither bucketed nor sorted.
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, b.value FROM src a JOIN src b ON a.key = b.key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+set hive.merge.smallfiles.avgsize=2;
+set hive.exec.compress.output=false;
+
+-- Tests a reduce task followed by a move. The output should be bucketed and sorted.
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, b.value FROM src a JOIN src b ON a.key = b.key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_multi_insert.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_multi_insert.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_multi_insert.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_multi_insert.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,46 @@
+set hive.exec.infer.bucket.sort=true;
+set hive.exec.infer.bucket.sort.num.buckets.power.two=true;
+
+-- This tests inferring how data is bucketed/sorted from the operators in the reducer
+-- and populating that information in partitions' metadata. In particular, those cases
+-- where multi insert is used.
+
+CREATE TABLE test_table (key STRING, value STRING) PARTITIONED BY (part STRING);
+
+-- Simple case, neither partition should be bucketed or sorted
+
+FROM src
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1') SELECT key, value
+INSERT OVERWRITE TABLE test_table PARTITION (part = '2') SELECT value, key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+DESCRIBE FORMATTED test_table PARTITION (part = '2');
+
+-- The partitions should be bucketed and sorted by different keys
+
+FROM src
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1') SELECT key, COUNT(*) GROUP BY key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '2') SELECT COUNT(*), value GROUP BY value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+DESCRIBE FORMATTED test_table PARTITION (part = '2');
+
+-- The first partition should be bucketed and sorted, the second should not
+
+FROM src
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1') SELECT key, COUNT(*) GROUP BY key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '2') SELECT key, value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+DESCRIBE FORMATTED test_table PARTITION (part = '2');
+
+set hive.multigroupby.singlereducer=true;
+
+-- Test the multi group by single reducer optimization
+-- Both partitions should be bucketed by key
+FROM src
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1') SELECT key, COUNT(*) GROUP BY key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '2') SELECT key, SUM(SUBSTR(value, 5)) GROUP BY key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+DESCRIBE FORMATTED test_table PARTITION (part = '2');
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_num_buckets.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_num_buckets.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_num_buckets.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_num_buckets.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,37 @@
+set hive.exec.infer.bucket.sort=true;
+set hive.merge.mapfiles=false;
+set hive.merge.mapredfiles=false;
+set mapred.reduce.tasks=2;
+
+CREATE TABLE test_table (key INT, value STRING) PARTITIONED BY (ds STRING, hr STRING);
+
+-- Tests dynamic partitions where bucketing/sorting can be inferred, but not all reducers write
+-- all partitions. The subquery produces rows as follows
+-- key = 0:
+-- 0, <value>, 0
+-- key = 1:
+-- 0, <value>, 1
+-- key = 2:
+-- 1, <value>, 0
+-- This means that by distributing by the first column into two reducers, and using the third
+-- columns as a dynamic partition, the dynamic partition for 0 will get written in both reducers
+-- and the partition for 1 will get written in one reducer. So hr=0 should be bucketed by key
+-- and hr=1 should not.
+
+EXPLAIN
+INSERT OVERWRITE TABLE test_table PARTITION (ds = '2008-04-08', hr)
+SELECT key2, value, cast(hr as int) FROM
+(SELECT if ((key % 3) < 2, 0, 1) as key2, value, (key % 2) as hr
+FROM srcpart
+WHERE ds = '2008-04-08') a
+DISTRIBUTE BY key2;
+
+INSERT OVERWRITE TABLE test_table PARTITION (ds = '2008-04-08', hr)
+SELECT key2, value, cast(hr as int) FROM
+(SELECT if ((key % 3) < 2, 0, 1) as key2, value, (key % 3 % 2) as hr
+FROM srcpart
+WHERE ds = '2008-04-08') a
+DISTRIBUTE BY key2;
+
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='0');
+DESCRIBE FORMATTED test_table PARTITION (ds='2008-04-08', hr='1');
Added: hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_reducers_power_two.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_reducers_power_two.q?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_reducers_power_two.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/infer_bucket_sort_reducers_power_two.q Wed Jan 30 04:20:39 2013
@@ -0,0 +1,46 @@
+set hive.exec.infer.bucket.sort=true;
+set hive.exec.infer.bucket.sort.num.buckets.power.two=true;
+set hive.exec.reducers.bytes.per.reducer=2500;
+
+-- This tests inferring how data is bucketed/sorted from the operators in the reducer
+-- and populating that information in partitions' metadata, it also verifies that the
+-- number of reducers chosen will be a power of two
+
+CREATE TABLE test_table (key STRING, value STRING) PARTITIONED BY (part STRING);
+
+-- Test group by, should be bucketed and sorted by group by key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT key, count(*) FROM src GROUP BY key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join, should be bucketed and sorted by join key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, a.value FROM src a JOIN src b ON a.key = b.key;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join with two keys, should be bucketed and sorted by join keys
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, a.value FROM src a JOIN src b ON a.key = b.key AND a.value = b.value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join on three tables on same key, should be bucketed and sorted by join key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, c.value FROM src a JOIN src b ON (a.key = b.key) JOIN src c ON (b.key = c.key);
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test join on three tables on different keys, should be bucketed and sorted by latter key
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT a.key, c.value FROM src a JOIN src b ON (a.key = b.key) JOIN src c ON (b.value = c.value);
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
+
+-- Test group by in subquery with another group by outside, should be bucketed and sorted by the
+-- key of the outer group by
+INSERT OVERWRITE TABLE test_table PARTITION (part = '1')
+SELECT count(1), value FROM (SELECT key, count(1) as value FROM src group by key) a group by value;
+
+DESCRIBE FORMATTED test_table PARTITION (part = '1');
Modified: hive/trunk/ql/src/test/queries/clientpositive/reduce_deduplicate_exclude_gby.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/reduce_deduplicate_exclude_gby.q?rev=1440271&r1=1440270&r2=1440271&view=diff
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/reduce_deduplicate_exclude_gby.q (original)
+++ hive/trunk/ql/src/test/queries/clientpositive/reduce_deduplicate_exclude_gby.q Wed Jan 30 04:20:39 2013
@@ -1,5 +1,7 @@
create table t1( key_int1 int, key_int2 int, key_string1 string, key_string2 string);
+set hive.optimize.reducededuplication=false;
+
set hive.map.aggr=false;
select Q1.key_int1, sum(Q1.key_int1) from (select * from t1 cluster by key_int1) Q1 group by Q1.key_int1;
Added: hive/trunk/ql/src/test/results/clientnegative/merge_negative_3.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientnegative/merge_negative_3.q.out?rev=1440271&view=auto
==============================================================================
--- hive/trunk/ql/src/test/results/clientnegative/merge_negative_3.q.out (added)
+++ hive/trunk/ql/src/test/results/clientnegative/merge_negative_3.q.out Wed Jan 30 04:20:39 2013
@@ -0,0 +1,16 @@
+PREHOOK: query: create table srcpart2 (key int, value string) partitioned by (ds string) clustered by (key) sorted by (key) into 2 buckets stored as RCFILE
+PREHOOK: type: CREATETABLE
+POSTHOOK: query: create table srcpart2 (key int, value string) partitioned by (ds string) clustered by (key) sorted by (key) into 2 buckets stored as RCFILE
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: default@srcpart2
+PREHOOK: query: insert overwrite table srcpart2 partition (ds='2011') select * from src
+PREHOOK: type: QUERY
+PREHOOK: Input: default@src
+PREHOOK: Output: default@srcpart2@ds=2011
+POSTHOOK: query: insert overwrite table srcpart2 partition (ds='2011') select * from src
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@src
+POSTHOOK: Output: default@srcpart2@ds=2011
+POSTHOOK: Lineage: srcpart2 PARTITION(ds=2011).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
+POSTHOOK: Lineage: srcpart2 PARTITION(ds=2011).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
+FAILED: SemanticException org.apache.hadoop.hive.ql.parse.SemanticException: Merge can not perform on bucketized partition/table.
Modified: hive/trunk/ql/src/test/results/clientpositive/ctas.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/ctas.q.out?rev=1440271&r1=1440270&r2=1440271&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/ctas.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/ctas.q.out Wed Jan 30 04:20:39 2013
@@ -827,6 +827,8 @@ STAGE PLANS:
TotalFiles: 1
GatherStats: false
MultiFileSpray: false
+ Path -> Sorted Columns:
+#### A masked pattern was here ####
Truncated Path -> Alias:
/src [src]
@@ -891,6 +893,8 @@ STAGE PLANS:
TotalFiles: 1
GatherStats: true
MultiFileSpray: false
+ Path -> Sorted Columns:
+#### A masked pattern was here ####
Truncated Path -> Alias:
#### A masked pattern was here ####