http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/druid_timestamptz.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/druid_timestamptz.q b/ql/src/test/queries/clientpositive/druid_timestamptz.q index 63c6e4e..4830044 100644 --- a/ql/src/test/queries/clientpositive/druid_timestamptz.q +++ b/ql/src/test/queries/clientpositive/druid_timestamptz.q @@ -1,51 +1,51 @@ set hive.fetch.task.conversion=more; -drop table tstz1; +drop table tstz1_n0; -create table tstz1(`__time` timestamp with local time zone, n string, v integer) +create table tstz1_n0(`__time` timestamp with local time zone, n string, v integer) STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' TBLPROPERTIES ("druid.segment.granularity" = "HOUR"); -insert into table tstz1 +insert into table tstz1_n0 values(cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone), 'Bill', 10); -EXPLAIN select `__time` from tstz1; -select `__time` from tstz1; +EXPLAIN select `__time` from tstz1_n0; +select `__time` from tstz1_n0; -EXPLAIN select cast(`__time` as timestamp) from tstz1; -select cast(`__time` as timestamp) from tstz1; +EXPLAIN select cast(`__time` as timestamp) from tstz1_n0; +select cast(`__time` as timestamp) from tstz1_n0; -EXPLAIN select cast(`__time` as timestamp) from tstz1 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); -select cast(`__time` as timestamp) from tstz1 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); +EXPLAIN select cast(`__time` as timestamp) from tstz1_n0 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); +select cast(`__time` as timestamp) from tstz1_n0 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); -EXPLAIN SELECT EXTRACT(HOUR FROM CAST(`__time` AS timestamp)) FROM tstz1; -SELECT EXTRACT(HOUR FROM CAST(`__time` AS timestamp)) FROM tstz1; +EXPLAIN SELECT EXTRACT(HOUR FROM CAST(`__time` AS timestamp)) FROM tstz1_n0; +SELECT EXTRACT(HOUR FROM CAST(`__time` AS timestamp)) FROM tstz1_n0; -EXPLAIN SELECT FLOOR(CAST(`__time` AS timestamp) to HOUR) FROM tstz1; -SELECT FLOOR(CAST(`__time` AS timestamp) to HOUR) FROM tstz1; +EXPLAIN SELECT FLOOR(CAST(`__time` AS timestamp) to HOUR) FROM tstz1_n0; +SELECT FLOOR(CAST(`__time` AS timestamp) to HOUR) FROM tstz1_n0; set time zone UTC; -EXPLAIN select `__time` from tstz1; -select `__time` from tstz1; -EXPLAIN select cast(`__time` as timestamp) from tstz1; -select cast(`__time` as timestamp) from tstz1; -EXPLAIN select cast(`__time` as timestamp) from tstz1 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); -select cast(`__time` as timestamp) from tstz1 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); +EXPLAIN select `__time` from tstz1_n0; +select `__time` from tstz1_n0; +EXPLAIN select cast(`__time` as timestamp) from tstz1_n0; +select cast(`__time` as timestamp) from tstz1_n0; +EXPLAIN select cast(`__time` as timestamp) from tstz1_n0 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); +select cast(`__time` as timestamp) from tstz1_n0 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); -- THIS is failing explore why ---EXPLAIN select cast(`__time` as timestamp) from tstz1 where `__time` = cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); ---select cast(`__time` as timestamp) from tstz1 where `__time` = cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); +--EXPLAIN select cast(`__time` as timestamp) from tstz1_n0 where `__time` = cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); +--select cast(`__time` as timestamp) from tstz1_n0 where `__time` = cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); -EXPLAIN select cast(`__time` as timestamp) from tstz1 where `__time` >= cast('2016-01-03 20:26:34' as timestamp); -select cast(`__time` as timestamp) from tstz1 where `__time` >= cast('2016-01-03 20:26:34' as timestamp); +EXPLAIN select cast(`__time` as timestamp) from tstz1_n0 where `__time` >= cast('2016-01-03 20:26:34' as timestamp); +select cast(`__time` as timestamp) from tstz1_n0 where `__time` >= cast('2016-01-03 20:26:34' as timestamp); -EXPLAIN select cast(`__time` as timestamp) from tstz1 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone) AND `__time` <= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); -select cast(`__time` as timestamp) from tstz1 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone) AND `__time` <= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); +EXPLAIN select cast(`__time` as timestamp) from tstz1_n0 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone) AND `__time` <= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); +select cast(`__time` as timestamp) from tstz1_n0 where `__time` >= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone) AND `__time` <= cast('2016-01-03 12:26:34 America/Los_Angeles' as timestamp with local time zone); -EXPLAIN SELECT EXTRACT(HOUR FROM CAST(`__time` AS timestamp)) FROM tstz1; -SELECT EXTRACT(HOUR FROM CAST(`__time` AS timestamp)) FROM tstz1; +EXPLAIN SELECT EXTRACT(HOUR FROM CAST(`__time` AS timestamp)) FROM tstz1_n0; +SELECT EXTRACT(HOUR FROM CAST(`__time` AS timestamp)) FROM tstz1_n0; -EXPLAIN SELECT FLOOR(CAST(`__time` AS timestamp) to HOUR) FROM tstz1; -SELECT FLOOR(CAST(`__time` AS timestamp) to HOUR) FROM tstz1; +EXPLAIN SELECT FLOOR(CAST(`__time` AS timestamp) to HOUR) FROM tstz1_n0; +SELECT FLOOR(CAST(`__time` AS timestamp) to HOUR) FROM tstz1_n0;
http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/druid_topn.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/druid_topn.q b/ql/src/test/queries/clientpositive/druid_topn.q index 24d5051..2aa6aff 100644 --- a/ql/src/test/queries/clientpositive/druid_topn.q +++ b/ql/src/test/queries/clientpositive/druid_topn.q @@ -1,15 +1,15 @@ set hive.druid.broker.address.default=localhost.test; -CREATE EXTERNAL TABLE druid_table_1 +CREATE EXTERNAL TABLE druid_table_1_n1 STORED BY 'org.apache.hadoop.hive.druid.QTestDruidStorageHandler' TBLPROPERTIES ("druid.datasource" = "wikipedia"); -DESCRIBE FORMATTED druid_table_1; +DESCRIBE FORMATTED druid_table_1_n1; -- GRANULARITY: ALL EXPLAIN SELECT robot, max(added) as m, sum(variation) -FROM druid_table_1 +FROM druid_table_1_n1 GROUP BY robot ORDER BY m DESC LIMIT 100; @@ -17,7 +17,7 @@ LIMIT 100; -- GRANULARITY: NONE EXPLAIN SELECT robot, `__time`, max(added), sum(variation) as s -FROM druid_table_1 +FROM druid_table_1_n1 GROUP BY robot, `__time` ORDER BY s DESC LIMIT 100; @@ -25,7 +25,7 @@ LIMIT 100; -- GRANULARITY: YEAR EXPLAIN SELECT robot, floor_year(`__time`), max(added), sum(variation) as s -FROM druid_table_1 +FROM druid_table_1_n1 GROUP BY robot, floor_year(`__time`) ORDER BY s DESC LIMIT 10; @@ -33,7 +33,7 @@ LIMIT 10; -- ASC: TRANSFORM INTO GROUP BY EXPLAIN SELECT robot, floor_month(`__time`), max(added), sum(variation) as s -FROM druid_table_1 +FROM druid_table_1_n1 GROUP BY robot, floor_month(`__time`) ORDER BY s LIMIT 10; @@ -41,7 +41,7 @@ LIMIT 10; -- MULTIPLE ORDER: TRANSFORM INTO GROUP BY EXPLAIN SELECT robot, floor_month(`__time`), max(added) as m, sum(variation) as s -FROM druid_table_1 +FROM druid_table_1_n1 GROUP BY robot, namespace, floor_month(`__time`) ORDER BY s DESC, m DESC LIMIT 10; @@ -49,7 +49,7 @@ LIMIT 10; -- MULTIPLE ORDER MIXED: TRANSFORM INTO GROUP BY EXPLAIN SELECT robot, floor_month(`__time`), max(added) as m, sum(variation) as s -FROM druid_table_1 +FROM druid_table_1_n1 GROUP BY robot, namespace, floor_month(`__time`) ORDER BY robot ASC, m DESC LIMIT 10; @@ -57,7 +57,7 @@ LIMIT 10; -- WITH FILTER ON DIMENSION: TRANSFORM INTO GROUP BY EXPLAIN SELECT robot, floor_year(`__time`), max(added), sum(variation) as s -FROM druid_table_1 +FROM druid_table_1_n1 WHERE robot='1' GROUP BY robot, floor_year(`__time`) ORDER BY s @@ -66,7 +66,7 @@ LIMIT 10; -- WITH FILTER ON TIME EXPLAIN SELECT robot, floor_hour(`__time`), max(added) as m, sum(variation) -FROM druid_table_1 +FROM druid_table_1_n1 WHERE floor_hour(`__time`) BETWEEN CAST('2010-01-01 00:00:00' AS TIMESTAMP WITH LOCAL TIME ZONE) AND CAST('2014-01-01 00:00:00' AS TIMESTAMP WITH LOCAL TIME ZONE) http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/druidmini_expressions.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/druidmini_expressions.q b/ql/src/test/queries/clientpositive/druidmini_expressions.q index 882d7af..01e2d6f 100644 --- a/ql/src/test/queries/clientpositive/druidmini_expressions.q +++ b/ql/src/test/queries/clientpositive/druidmini_expressions.q @@ -1,5 +1,7 @@ +--! qt:dataset:alltypesorc + SET hive.vectorized.execution.enabled=false; -CREATE TABLE druid_table +CREATE TABLE druid_table_n0 STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' TBLPROPERTIES ("druid.segment.granularity" = "HOUR", "druid.query.granularity" = "MINUTE") AS @@ -18,36 +20,36 @@ SELECT cast (`ctimestamp1` as timestamp with local time zone) as `__time`, -- MATH AND STRING functions -SELECT count(*) FROM druid_table WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3; +SELECT count(*) FROM druid_table_n0 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3; -SELECT count(*) FROM druid_table WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10; +SELECT count(*) FROM druid_table_n0 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10; -SELECT count(*) FROM druid_table WHERE power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3; +SELECT count(*) FROM druid_table_n0 WHERE power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3; SELECT SUM(cfloat + 1), CAST(SUM(cdouble + ctinyint) AS INTEGER), SUM(ctinyint) + 1 , CAST(SUM(csmallint) + SUM(cint) AS DOUBLE), SUM(cint), SUM(cbigint) -FROM druid_table WHERE ceil(cfloat) > 0 AND floor(cdouble) * 2 < 1000 OR ln(cdouble) / log10(10) > 0 AND COS(cint) > 0 OR SIN(cdouble) > 1; +FROM druid_table_n0 WHERE ceil(cfloat) > 0 AND floor(cdouble) * 2 < 1000 OR ln(cdouble) / log10(10) > 0 AND COS(cint) > 0 OR SIN(cdouble) > 1; SELECT SUM(cfloat + 1), CAST(SUM(cdouble + ctinyint) AS INTEGER), SUM(ctinyint) + 1 , CAST(SUM(csmallint) + SUM(cint) AS DOUBLE), SUM(cint), SUM(cbigint) -FROM druid_table WHERE ceil(cfloat) > 0 AND floor(cdouble) * 2 < 1000; +FROM druid_table_n0 WHERE ceil(cfloat) > 0 AND floor(cdouble) * 2 < 1000; SELECT SUM(cfloat + 1), CAST(SUM(cdouble + ctinyint) AS INTEGER), SUM(ctinyint) + 1 , CAST(SUM(csmallint) + SUM(cint) AS DOUBLE), SUM(cint), SUM(cbigint) -FROM druid_table WHERE ln(cdouble) / log10(10) > 0 AND COS(cint) > 0 OR SIN(cdouble) > 1; +FROM druid_table_n0 WHERE ln(cdouble) / log10(10) > 0 AND COS(cint) > 0 OR SIN(cdouble) > 1; SELECT SUM(cfloat + 1), CAST(SUM(cdouble + ctinyint) AS INTEGER), SUM(ctinyint) + 1 , CAST(SUM(csmallint) + SUM(cint) AS DOUBLE), SUM(cint), SUM(cbigint) -FROM druid_table WHERE SIN(cdouble) > 1; +FROM druid_table_n0 WHERE SIN(cdouble) > 1; -SELECT cstring1 || '_'|| cstring2, substring(cstring2, 2, 3) as concat , upper(cstring2), lower(cstring1), SUM(cdouble) as s FROM druid_table WHERE cstring1 IS NOT NULL AND cstring2 IS NOT NULL AND cstring2 like 'Y%' +SELECT cstring1 || '_'|| cstring2, substring(cstring2, 2, 3) as concat , upper(cstring2), lower(cstring1), SUM(cdouble) as s FROM druid_table_n0 WHERE cstring1 IS NOT NULL AND cstring2 IS NOT NULL AND cstring2 like 'Y%' GROUP BY cstring1 || '_'|| cstring2, substring(cstring2, 2, 3), upper(cstring2), lower(cstring1) ORDER BY concat DESC LIMIT 10; -EXPLAIN SELECT count(*) FROM druid_table WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3; +EXPLAIN SELECT count(*) FROM druid_table_n0 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3; EXPLAIN SELECT SUM(cfloat + 1), CAST(SUM(cdouble + ctinyint) AS INTEGER), SUM(ctinyint) + 1 , CAST(SUM(csmallint) + SUM(cint) AS DOUBLE), SUM(cint), SUM(cbigint) - FROM druid_table WHERE ceil(cfloat) > 0 AND floor(cdouble) * 2 < 1000 OR ln(cdouble) / log10(10) > 0 AND COS(cint) > 0 OR SIN(cdouble) > 1; + FROM druid_table_n0 WHERE ceil(cfloat) > 0 AND floor(cdouble) * 2 < 1000 OR ln(cdouble) / log10(10) > 0 AND COS(cint) > 0 OR SIN(cdouble) > 1; -EXPLAIN SELECT cstring1 || '_'|| cstring2, substring(cstring2, 2, 3) as concat , upper(cstring2), lower(cstring1), SUM(cdouble) as s FROM druid_table WHERE cstring1 IS NOT NULL AND cstring2 IS NOT NULL AND cstring2 like 'Y%' +EXPLAIN SELECT cstring1 || '_'|| cstring2, substring(cstring2, 2, 3) as concat , upper(cstring2), lower(cstring1), SUM(cdouble) as s FROM druid_table_n0 WHERE cstring1 IS NOT NULL AND cstring2 IS NOT NULL AND cstring2 like 'Y%' GROUP BY cstring1 || '_'|| cstring2, substring(cstring2, 2, 3), upper(cstring2), lower(cstring1) ORDER BY concat DESC LIMIT 10; -DROP TABLE druid_table; \ No newline at end of file +DROP TABLE druid_table_n0; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/druidmini_floorTime.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/druidmini_floorTime.q b/ql/src/test/queries/clientpositive/druidmini_floorTime.q index 4e14855..a526413 100644 --- a/ql/src/test/queries/clientpositive/druidmini_floorTime.q +++ b/ql/src/test/queries/clientpositive/druidmini_floorTime.q @@ -1,5 +1,7 @@ +--! qt:dataset:alltypesorc + SET hive.vectorized.execution.enabled=false; -CREATE TABLE druid_table +CREATE TABLE druid_table_n2 STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' TBLPROPERTIES ("druid.segment.granularity" = "HOUR", "druid.query.granularity" = "MINUTE") AS @@ -19,78 +21,78 @@ SELECT cast (`ctimestamp1` as timestamp with local time zone) as `__time`, -- GROUP BY TIME EXTRACT --SECONDS -SELECT floor(`__time` to SECOND) FROM druid_table +SELECT floor(`__time` to SECOND) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to SECOND); -EXPLAIN SELECT floor(`__time` to SECOND) FROM druid_table +EXPLAIN SELECT floor(`__time` to SECOND) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to SECOND); -- MINUTES -SELECT floor(`__time` to MINUTE) FROM druid_table +SELECT floor(`__time` to MINUTE) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to MINUTE); -EXPLAIN SELECT floor(`__time` to MINUTE) FROM druid_table +EXPLAIN SELECT floor(`__time` to MINUTE) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to MINUTE); -- HOUR -SELECT floor(`__time` to HOUR) FROM druid_table +SELECT floor(`__time` to HOUR) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to HOUR); -EXPLAIN SELECT floor(`__time` to HOUR) FROM druid_table +EXPLAIN SELECT floor(`__time` to HOUR) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to HOUR); -- DAY -SELECT EXTRACT(DAY from `__time`) FROM druid_table +SELECT EXTRACT(DAY from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(DAY from `__time`); -EXPLAIN SELECT EXTRACT(DAY from `__time`) FROM druid_table +EXPLAIN SELECT EXTRACT(DAY from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(DAY from `__time`); --WEEK -SELECT EXTRACT(WEEK from `__time`) FROM druid_table +SELECT EXTRACT(WEEK from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(WEEK from `__time`); -EXPLAIN SELECT EXTRACT(WEEK from `__time`) FROM druid_table +EXPLAIN SELECT EXTRACT(WEEK from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(WEEK from `__time`); --MONTH -SELECT EXTRACT(MONTH from `__time`) FROM druid_table +SELECT EXTRACT(MONTH from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(MONTH from `__time`); -EXPLAIN SELECT EXTRACT(MONTH from `__time`) FROM druid_table +EXPLAIN SELECT EXTRACT(MONTH from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(MONTH from `__time`); --QUARTER -SELECT EXTRACT(QUARTER from `__time`) FROM druid_table +SELECT EXTRACT(QUARTER from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(QUARTER from `__time`); -EXPLAIN SELECT EXTRACT(QUARTER from `__time`) FROM druid_table +EXPLAIN SELECT EXTRACT(QUARTER from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(QUARTER from `__time`); -- YEAR -SELECT EXTRACT(YEAR from `__time`) FROM druid_table +SELECT EXTRACT(YEAR from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(YEAR from `__time`); -EXPLAIN SELECT EXTRACT(YEAR from `__time`) FROM druid_table +EXPLAIN SELECT EXTRACT(YEAR from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(YEAR from `__time`); @@ -98,67 +100,67 @@ AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP B -- SECOND -EXPLAIN SELECT EXTRACT(SECOND from `__time`) FROM druid_table WHERE EXTRACT(SECOND from `__time`) = 0 LIMIT 1; +EXPLAIN SELECT EXTRACT(SECOND from `__time`) FROM druid_table_n2 WHERE EXTRACT(SECOND from `__time`) = 0 LIMIT 1; -SELECT EXTRACT(SECOND from `__time`) FROM druid_table WHERE EXTRACT(SECOND from `__time`) = 0 LIMIT 1; +SELECT EXTRACT(SECOND from `__time`) FROM druid_table_n2 WHERE EXTRACT(SECOND from `__time`) = 0 LIMIT 1; -- MINUTE -EXPLAIN SELECT EXTRACT(MINUTE from `__time`) FROM druid_table +EXPLAIN SELECT EXTRACT(MINUTE from `__time`) FROM druid_table_n2 WHERE EXTRACT(MINUTE from `__time`) >= 0 LIMIT 2; -SELECT EXTRACT(MINUTE from `__time`) as minute FROM druid_table +SELECT EXTRACT(MINUTE from `__time`) as minute FROM druid_table_n2 WHERE EXTRACT(MINUTE from `__time`) >= 0 order by minute LIMIT 2; -- HOUR -EXPLAIN SELECT EXTRACT(HOUR from `__time`) FROM druid_table +EXPLAIN SELECT EXTRACT(HOUR from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 LIMIT 1; -SELECT EXTRACT(HOUR from `__time`) FROM druid_table +SELECT EXTRACT(HOUR from `__time`) FROM druid_table_n2 WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10 AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 LIMIT 1; --DAY EXPLAIN SELECT EXTRACT(DAY from `__time`), EXTRACT(DAY from `__time`) DIV 7 AS WEEK, SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2) AS day_str -FROM druid_table WHERE SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2) = 31 LIMIT 1; +FROM druid_table_n2 WHERE SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2) = 31 LIMIT 1; SELECT EXTRACT(DAY from `__time`) , EXTRACT(DAY from `__time`) DIV 7 AS WEEK, SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2) AS dar_str -FROM druid_table WHERE SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2) = 31 LIMIT 1 ; +FROM druid_table_n2 WHERE SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2) = 31 LIMIT 1 ; -- WEEK -EXPLAIN SELECT EXTRACT(WEEK from `__time`) FROM druid_table WHERE EXTRACT(WEEK from `__time`) >= 1 +EXPLAIN SELECT EXTRACT(WEEK from `__time`) FROM druid_table_n2 WHERE EXTRACT(WEEK from `__time`) >= 1 AND EXTRACT(WEEK from `__time`) DIV 4 + 1 = 1 LIMIT 1; -SELECT EXTRACT(WEEK from `__time`) FROM druid_table WHERE EXTRACT(WEEK from `__time`) >= 1 +SELECT EXTRACT(WEEK from `__time`) FROM druid_table_n2 WHERE EXTRACT(WEEK from `__time`) >= 1 AND EXTRACT(WEEK from `__time`) DIV 4 + 1 = 1 LIMIT 1 ; --MONTH -EXPLAIN SELECT EXTRACT(MONTH FROM `__time`) / 4 + 1, EXTRACT(MONTH FROM `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 6, 2) as month_str FROM druid_table +EXPLAIN SELECT EXTRACT(MONTH FROM `__time`) / 4 + 1, EXTRACT(MONTH FROM `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 6, 2) as month_str FROM druid_table_n2 WHERE EXTRACT(MONTH FROM `__time`) / 4 + 1 = 4 AND EXTRACT(MONTH FROM `__time`) BETWEEN 11 AND 12 LIMIT 1; -SELECT EXTRACT(MONTH FROM `__time`) / 4 + 1, EXTRACT(MONTH FROM `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 6, 2) as month_str FROM druid_table +SELECT EXTRACT(MONTH FROM `__time`) / 4 + 1, EXTRACT(MONTH FROM `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 6, 2) as month_str FROM druid_table_n2 WHERE EXTRACT(MONTH FROM `__time`) / 4 + 1 = 4 AND EXTRACT(MONTH FROM `__time`) BETWEEN 11 AND 12 LIMIT 1; --QUARTER -EXPLAIN SELECT EXTRACT(QUARTER from `__time`), EXTRACT(MONTH FROM `__time`) / 4 + 1 as q_number FROM druid_table WHERE EXTRACT(QUARTER from `__time`) >= 4 +EXPLAIN SELECT EXTRACT(QUARTER from `__time`), EXTRACT(MONTH FROM `__time`) / 4 + 1 as q_number FROM druid_table_n2 WHERE EXTRACT(QUARTER from `__time`) >= 4 AND EXTRACT(MONTH FROM `__time`) / 4 + 1 = 4 LIMIT 1; -SELECT EXTRACT(QUARTER from `__time`), EXTRACT(MONTH FROM `__time`) / 4 + 1 as q_number FROM druid_table WHERE EXTRACT(QUARTER from `__time`) >= 4 +SELECT EXTRACT(QUARTER from `__time`), EXTRACT(MONTH FROM `__time`) / 4 + 1 as q_number FROM druid_table_n2 WHERE EXTRACT(QUARTER from `__time`) >= 4 AND EXTRACT(MONTH FROM `__time`) / 4 + 1 = 4 LIMIT 1; --YEAR -EXPLAIN SELECT EXTRACT(YEAR from `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 1, 4) AS year_str FROM druid_table WHERE EXTRACT(YEAR from `__time`) >= 1969 +EXPLAIN SELECT EXTRACT(YEAR from `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 1, 4) AS year_str FROM druid_table_n2 WHERE EXTRACT(YEAR from `__time`) >= 1969 AND CAST(EXTRACT(YEAR from `__time`) as STRING) = '1969' LIMIT 1; -SELECT EXTRACT(YEAR from `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 1, 4) as year_str FROM druid_table WHERE EXTRACT(YEAR from `__time`) >= 1969 +SELECT EXTRACT(YEAR from `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 1, 4) as year_str FROM druid_table_n2 WHERE EXTRACT(YEAR from `__time`) >= 1969 AND CAST(EXTRACT(YEAR from `__time`) as STRING) = '1969' LIMIT 1; -DROP TABLE druid_table; \ No newline at end of file +DROP TABLE druid_table_n2; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/druidmini_mv.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/druidmini_mv.q b/ql/src/test/queries/clientpositive/druidmini_mv.q index 8cf3d03..46b204e 100644 --- a/ql/src/test/queries/clientpositive/druidmini_mv.q +++ b/ql/src/test/queries/clientpositive/druidmini_mv.q @@ -6,7 +6,7 @@ set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.strict.checks.cartesian.product=false; set hive.materializedview.rewriting=true; -CREATE TABLE cmv_basetable +CREATE TABLE cmv_basetable_n2 STORED AS orc TBLPROPERTIES ('transactional'='true') AS @@ -25,91 +25,91 @@ FROM TABLE ( (3, 'charlie', 'charlie_a', 9.8, 1), (3, 'charlie', 'charlie_b', 15.8, 1)) as q (a, b, userid, c, d); -CREATE MATERIALIZED VIEW cmv_mat_view ENABLE REWRITE +CREATE MATERIALIZED VIEW cmv_mat_view_n2 ENABLE REWRITE STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' TBLPROPERTIES ("druid.segment.granularity" = "HOUR") AS SELECT cast(t AS timestamp with local time zone) as `__time`, a, b, c, userid -FROM cmv_basetable +FROM cmv_basetable_n2 WHERE a = 2; -SELECT a, b, c FROM cmv_mat_view; +SELECT a, b, c FROM cmv_mat_view_n2; -SHOW TBLPROPERTIES cmv_mat_view; +SHOW TBLPROPERTIES cmv_mat_view_n2; -CREATE MATERIALIZED VIEW IF NOT EXISTS cmv_mat_view2 ENABLE REWRITE +CREATE MATERIALIZED VIEW IF NOT EXISTS cmv_mat_view2_n0 ENABLE REWRITE STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' TBLPROPERTIES ("druid.segment.granularity" = "HOUR") AS SELECT cast(t AS timestamp with local time zone) as `__time`, a, b, c, userid -FROM cmv_basetable +FROM cmv_basetable_n2 WHERE a = 3; -SELECT a, c FROM cmv_mat_view2; +SELECT a, c FROM cmv_mat_view2_n0; -SHOW TBLPROPERTIES cmv_mat_view2; +SHOW TBLPROPERTIES cmv_mat_view2_n0; EXPLAIN SELECT a, c -FROM cmv_basetable +FROM cmv_basetable_n2 WHERE a = 3; SELECT a, c -FROM cmv_basetable +FROM cmv_basetable_n2 WHERE a = 3; EXPLAIN SELECT * FROM ( - (SELECT a, c FROM cmv_basetable WHERE a = 3) table1 + (SELECT a, c FROM cmv_basetable_n2 WHERE a = 3) table1 JOIN - (SELECT a, c FROM cmv_basetable WHERE d = 3) table2 + (SELECT a, c FROM cmv_basetable_n2 WHERE d = 3) table2 ON table1.a = table2.a); SELECT * FROM ( - (SELECT a, c FROM cmv_basetable WHERE a = 3) table1 + (SELECT a, c FROM cmv_basetable_n2 WHERE a = 3) table1 JOIN - (SELECT a, c FROM cmv_basetable WHERE d = 3) table2 + (SELECT a, c FROM cmv_basetable_n2 WHERE d = 3) table2 ON table1.a = table2.a); -INSERT INTO cmv_basetable VALUES +INSERT INTO cmv_basetable_n2 VALUES (cast(current_timestamp() AS timestamp), 3, 'charlie', 'charlie_c', 15.8, 1); -- TODO: CANNOT USE THE VIEW, IT IS OUTDATED EXPLAIN SELECT * FROM ( - (SELECT a, c FROM cmv_basetable WHERE a = 3) table1 + (SELECT a, c FROM cmv_basetable_n2 WHERE a = 3) table1 JOIN - (SELECT a, c FROM cmv_basetable WHERE d = 3) table2 + (SELECT a, c FROM cmv_basetable_n2 WHERE d = 3) table2 ON table1.a = table2.a); SELECT * FROM ( - (SELECT a, c FROM cmv_basetable WHERE a = 3) table1 + (SELECT a, c FROM cmv_basetable_n2 WHERE a = 3) table1 JOIN - (SELECT a, c FROM cmv_basetable WHERE d = 3) table2 + (SELECT a, c FROM cmv_basetable_n2 WHERE d = 3) table2 ON table1.a = table2.a); -- REBUILD EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view2 REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view2_n0 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view2 REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view2_n0 REBUILD; -SHOW TBLPROPERTIES cmv_mat_view2; +SHOW TBLPROPERTIES cmv_mat_view2_n0; -- NOW IT CAN BE USED AGAIN EXPLAIN SELECT * FROM ( - (SELECT a, c FROM cmv_basetable WHERE a = 3) table1 + (SELECT a, c FROM cmv_basetable_n2 WHERE a = 3) table1 JOIN - (SELECT a, c FROM cmv_basetable WHERE d = 3) table2 + (SELECT a, c FROM cmv_basetable_n2 WHERE d = 3) table2 ON table1.a = table2.a); SELECT * FROM ( - (SELECT a, c FROM cmv_basetable WHERE a = 3) table1 + (SELECT a, c FROM cmv_basetable_n2 WHERE a = 3) table1 JOIN - (SELECT a, c FROM cmv_basetable WHERE d = 3) table2 + (SELECT a, c FROM cmv_basetable_n2 WHERE d = 3) table2 ON table1.a = table2.a); -DROP MATERIALIZED VIEW cmv_mat_view; -DROP MATERIALIZED VIEW cmv_mat_view2; -DROP TABLE cmv_basetable; +DROP MATERIALIZED VIEW cmv_mat_view_n2; +DROP MATERIALIZED VIEW cmv_mat_view2_n0; +DROP TABLE cmv_basetable_n2; http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/druidmini_test1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/druidmini_test1.q b/ql/src/test/queries/clientpositive/druidmini_test1.q index 630e617..4a1bdc5 100644 --- a/ql/src/test/queries/clientpositive/druidmini_test1.q +++ b/ql/src/test/queries/clientpositive/druidmini_test1.q @@ -1,4 +1,5 @@ -CREATE TABLE druid_table +--! qt:dataset:alltypesorc +CREATE TABLE druid_table_n3 STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' TBLPROPERTIES ("druid.segment.granularity" = "HOUR", "druid.query.granularity" = "MINUTE") AS @@ -16,106 +17,106 @@ SELECT cast (`ctimestamp1` as timestamp with local time zone) as `__time`, FROM alltypesorc where ctimestamp1 IS NOT NULL; -- Time Series Query -explain select count(*) FROM druid_table; -SELECT count(*) FROM druid_table; +explain select count(*) FROM druid_table_n3; +SELECT count(*) FROM druid_table_n3; EXPLAIN SELECT floor_year(`__time`), SUM(cfloat), SUM(cdouble), SUM(ctinyint), SUM(csmallint),SUM(cint), SUM(cbigint) -FROM druid_table GROUP BY floor_year(`__time`); +FROM druid_table_n3 GROUP BY floor_year(`__time`); SELECT floor_year(`__time`), SUM(cfloat), SUM(cdouble), SUM(ctinyint), SUM(csmallint),SUM(cint), SUM(cbigint) -FROM druid_table GROUP BY floor_year(`__time`); +FROM druid_table_n3 GROUP BY floor_year(`__time`); EXPLAIN SELECT floor_year(`__time`), MIN(cfloat), MIN(cdouble), MIN(ctinyint), MIN(csmallint),MIN(cint), MIN(cbigint) -FROM druid_table GROUP BY floor_year(`__time`); +FROM druid_table_n3 GROUP BY floor_year(`__time`); SELECT floor_year(`__time`), MIN(cfloat), MIN(cdouble), MIN(ctinyint), MIN(csmallint),MIN(cint), MIN(cbigint) -FROM druid_table GROUP BY floor_year(`__time`); +FROM druid_table_n3 GROUP BY floor_year(`__time`); EXPLAIN SELECT floor_year(`__time`), MAX(cfloat), MAX(cdouble), MAX(ctinyint), MAX(csmallint),MAX(cint), MAX(cbigint) -FROM druid_table GROUP BY floor_year(`__time`); +FROM druid_table_n3 GROUP BY floor_year(`__time`); SELECT floor_year(`__time`), MAX(cfloat), MAX(cdouble), MAX(ctinyint), MAX(csmallint),MAX(cint), MAX(cbigint) -FROM druid_table GROUP BY floor_year(`__time`); +FROM druid_table_n3 GROUP BY floor_year(`__time`); -- Group By -EXPLAIN SELECT cstring1, SUM(cdouble) as s FROM druid_table GROUP BY cstring1 ORDER BY s ASC LIMIT 10; +EXPLAIN SELECT cstring1, SUM(cdouble) as s FROM druid_table_n3 GROUP BY cstring1 ORDER BY s ASC LIMIT 10; -SELECT cstring1, SUM(cdouble) as s FROM druid_table GROUP BY cstring1 ORDER BY s ASC LIMIT 10; +SELECT cstring1, SUM(cdouble) as s FROM druid_table_n3 GROUP BY cstring1 ORDER BY s ASC LIMIT 10; -EXPLAIN SELECT cstring2, MAX(cdouble) FROM druid_table GROUP BY cstring2 ORDER BY cstring2 ASC LIMIT 10; +EXPLAIN SELECT cstring2, MAX(cdouble) FROM druid_table_n3 GROUP BY cstring2 ORDER BY cstring2 ASC LIMIT 10; -SELECT cstring2, MAX(cdouble) FROM druid_table GROUP BY cstring2 ORDER BY cstring2 ASC LIMIT 10; +SELECT cstring2, MAX(cdouble) FROM druid_table_n3 GROUP BY cstring2 ORDER BY cstring2 ASC LIMIT 10; -- TIME STUFF EXPLAIN SELECT `__time` -FROM druid_table ORDER BY `__time` ASC LIMIT 10; +FROM druid_table_n3 ORDER BY `__time` ASC LIMIT 10; SELECT `__time` -FROM druid_table ORDER BY `__time` ASC LIMIT 10; +FROM druid_table_n3 ORDER BY `__time` ASC LIMIT 10; EXPLAIN SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE `__time` < '1970-03-01 00:00:00' ORDER BY `__time` ASC LIMIT 10; SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE `__time` < '1970-03-01 00:00:00' ORDER BY `__time` ASC LIMIT 10; EXPLAIN SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE `__time` >= '1968-01-01 00:00:00' AND `__time` <= '1970-03-01 00:00:00' ORDER BY `__time` ASC LIMIT 10; SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE `__time` >= '1968-01-01 00:00:00' AND `__time` <= '1970-03-01 00:00:00' ORDER BY `__time` ASC LIMIT 10; EXPLAIN SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE `__time` >= '1968-01-01 00:00:00' AND `__time` <= '1970-03-01 00:00:00' AND `__time` < '2011-01-01 00:00:00' ORDER BY `__time` ASC LIMIT 10; SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE `__time` >= '1968-01-01 00:00:00' AND `__time` <= '1970-03-01 00:00:00' AND `__time` < '2011-01-01 00:00:00' ORDER BY `__time` ASC LIMIT 10; EXPLAIN SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE `__time` BETWEEN '1968-01-01 00:00:00' AND '1970-01-01 00:00:00' ORDER BY `__time` ASC LIMIT 10;; SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE `__time` BETWEEN '1968-01-01 00:00:00' AND '1970-01-01 00:00:00' ORDER BY `__time` ASC LIMIT 10;; EXPLAIN SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE (`__time` BETWEEN '1968-01-01 00:00:00' AND '1970-01-01 00:00:00') OR (`__time` BETWEEN '1968-02-01 00:00:00' AND '1970-04-01 00:00:00') ORDER BY `__time` ASC LIMIT 10; SELECT `__time` -FROM druid_table +FROM druid_table_n3 WHERE (`__time` BETWEEN '1968-01-01 00:00:00' AND '1970-01-01 00:00:00') OR (`__time` BETWEEN '1968-02-01 00:00:00' AND '1970-04-01 00:00:00') ORDER BY `__time` ASC LIMIT 10; http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/druidmini_test_alter.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/druidmini_test_alter.q b/ql/src/test/queries/clientpositive/druidmini_test_alter.q index 15ae952..e19a006 100644 --- a/ql/src/test/queries/clientpositive/druidmini_test_alter.q +++ b/ql/src/test/queries/clientpositive/druidmini_test_alter.q @@ -1,4 +1,4 @@ -CREATE TABLE druid_alltypesorc +CREATE TABLE druid_alltypesorc_n0 STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' TBLPROPERTIES ("druid.segment.granularity" = "HOUR", "druid.query.granularity" = "MINUTE") AS @@ -13,21 +13,21 @@ cbigint, cboolean1 FROM alltypesorc where ctimestamp2 IS NOT NULL; -DESCRIBE druid_alltypesorc; +DESCRIBE druid_alltypesorc_n0; -DESCRIBE extended druid_alltypesorc; +DESCRIBE extended druid_alltypesorc_n0; -SELECT COUNT(*) FROM druid_alltypesorc; +SELECT COUNT(*) FROM druid_alltypesorc_n0; -ALTER TABLE druid_alltypesorc ADD COLUMNS (cstring2 string, cboolean2 boolean, cint2 int); +ALTER TABLE druid_alltypesorc_n0 ADD COLUMNS (cstring2 string, cboolean2 boolean, cint2 int); -DESCRIBE druid_alltypesorc; +DESCRIBE druid_alltypesorc_n0; -DESCRIBE extended druid_alltypesorc; +DESCRIBE extended druid_alltypesorc_n0; -SELECT COUNT(*) FROM druid_alltypesorc WHERE cstring2 IS NOT NULL; +SELECT COUNT(*) FROM druid_alltypesorc_n0 WHERE cstring2 IS NOT NULL; -INSERT INTO TABLE druid_alltypesorc +INSERT INTO TABLE druid_alltypesorc_n0 SELECT cast (`ctimestamp1` as timestamp with local time zone) as `__time`, cstring1, cdouble, @@ -43,10 +43,10 @@ cint FROM alltypesorc where ctimestamp1 IS NOT NULL; -SELECT COUNT(*) FROM druid_alltypesorc; +SELECT COUNT(*) FROM druid_alltypesorc_n0; -SELECT COUNT(*) FROM druid_alltypesorc WHERE cstring2 IS NULL; +SELECT COUNT(*) FROM druid_alltypesorc_n0 WHERE cstring2 IS NULL; -SELECT COUNT(*) FROM druid_alltypesorc WHERE cstring2 IS NOT NULL; +SELECT COUNT(*) FROM druid_alltypesorc_n0 WHERE cstring2 IS NOT NULL; -DROP TABLE druid_alltypesorc; +DROP TABLE druid_alltypesorc_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/druidmini_test_insert.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/druidmini_test_insert.q b/ql/src/test/queries/clientpositive/druidmini_test_insert.q index cc21dda..cdd4c23 100644 --- a/ql/src/test/queries/clientpositive/druidmini_test_insert.q +++ b/ql/src/test/queries/clientpositive/druidmini_test_insert.q @@ -57,23 +57,23 @@ DROP TABLE druid_alltypesorc; create database druid_test_create_then_insert; use druid_test_create_then_insert; - create table test_table(`timecolumn` timestamp, `userid` string, `num_l` float); + create table test_table_n9(`timecolumn` timestamp, `userid` string, `num_l` float); - insert into test_table values ('2015-01-08 00:00:00', 'i1-start', 4); - insert into test_table values ('2015-01-08 23:59:59', 'i1-end', 1); + insert into test_table_n9 values ('2015-01-08 00:00:00', 'i1-start', 4); + insert into test_table_n9 values ('2015-01-08 23:59:59', 'i1-end', 1); - CREATE TABLE druid_table (`__time` timestamp with local time zone, `userid` string, `num_l` float) + CREATE TABLE druid_table_n1 (`__time` timestamp with local time zone, `userid` string, `num_l` float) STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' TBLPROPERTIES ("druid.segment.granularity" = "DAY"); - INSERT INTO TABLE druid_table - select cast(`timecolumn` as timestamp with local time zone) as `__time`, `userid`, `num_l` FROM test_table; + INSERT INTO TABLE druid_table_n1 + select cast(`timecolumn` as timestamp with local time zone) as `__time`, `userid`, `num_l` FROM test_table_n9; - select count(*) FROM druid_table; + select count(*) FROM druid_table_n1; - DROP TABLE test_table; - DROP TABLE druid_table; + DROP TABLE test_table_n9; + DROP TABLE druid_table_n1; DROP DATABASE druid_test_create_then_insert; -- Day light saving time test insert into test @@ -89,54 +89,54 @@ insert into test_base_table values ('2015-03-09 23:59:59', 'i2-end', 1); insert into test_base_table values ('2015-03-10 00:00:00', 'i3-start', 2); insert into test_base_table values ('2015-03-10 23:59:59', 'i3-end', 2); -CREATE TABLE druid_test_table +CREATE TABLE druid_test_table_n9 STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' TBLPROPERTIES ("druid.segment.granularity" = "DAY") AS select cast(`timecolumn` as timestamp with local time zone) as `__time`, `userid`, `num_l` FROM test_base_table; -select * FROM druid_test_table; +select * FROM druid_test_table_n9; -select * from druid_test_table where `__time` = cast('2015-03-08 00:00:00' as timestamp with local time zone); -select * from druid_test_table where `__time` = cast('2015-03-08 23:59:59' as timestamp with local time zone); +select * from druid_test_table_n9 where `__time` = cast('2015-03-08 00:00:00' as timestamp with local time zone); +select * from druid_test_table_n9 where `__time` = cast('2015-03-08 23:59:59' as timestamp with local time zone); -select * from druid_test_table where `__time` = cast('2015-03-09 00:00:00' as timestamp with local time zone); -select * from druid_test_table where `__time` = cast('2015-03-09 23:59:59' as timestamp with local time zone); +select * from druid_test_table_n9 where `__time` = cast('2015-03-09 00:00:00' as timestamp with local time zone); +select * from druid_test_table_n9 where `__time` = cast('2015-03-09 23:59:59' as timestamp with local time zone); -select * from druid_test_table where `__time` = cast('2015-03-10 00:00:00' as timestamp with local time zone); -select * from druid_test_table where `__time` = cast('2015-03-10 23:59:59' as timestamp with local time zone); +select * from druid_test_table_n9 where `__time` = cast('2015-03-10 00:00:00' as timestamp with local time zone); +select * from druid_test_table_n9 where `__time` = cast('2015-03-10 23:59:59' as timestamp with local time zone); -explain select * from druid_test_table where `__time` = cast('2015-03-08 00:00:00' as timestamp with local time zone); -explain select * from druid_test_table where `__time` = cast('2015-03-08 23:59:59' as timestamp with local time zone); +explain select * from druid_test_table_n9 where `__time` = cast('2015-03-08 00:00:00' as timestamp with local time zone); +explain select * from druid_test_table_n9 where `__time` = cast('2015-03-08 23:59:59' as timestamp with local time zone); -explain select * from druid_test_table where `__time` = cast('2015-03-09 00:00:00' as timestamp with local time zone); -explain select * from druid_test_table where `__time` = cast('2015-03-09 23:59:59' as timestamp with local time zone); +explain select * from druid_test_table_n9 where `__time` = cast('2015-03-09 00:00:00' as timestamp with local time zone); +explain select * from druid_test_table_n9 where `__time` = cast('2015-03-09 23:59:59' as timestamp with local time zone); -explain select * from druid_test_table where `__time` = cast('2015-03-10 00:00:00' as timestamp with local time zone); -explain select * from druid_test_table where `__time` = cast('2015-03-10 23:59:59' as timestamp with local time zone); +explain select * from druid_test_table_n9 where `__time` = cast('2015-03-10 00:00:00' as timestamp with local time zone); +explain select * from druid_test_table_n9 where `__time` = cast('2015-03-10 23:59:59' as timestamp with local time zone); -select * from druid_test_table where `__time` = cast('2015-03-08 00:00:00' as timestamp ); -select * from druid_test_table where `__time` = cast('2015-03-08 23:59:59' as timestamp ); +select * from druid_test_table_n9 where `__time` = cast('2015-03-08 00:00:00' as timestamp ); +select * from druid_test_table_n9 where `__time` = cast('2015-03-08 23:59:59' as timestamp ); -select * from druid_test_table where `__time` = cast('2015-03-09 00:00:00' as timestamp ); -select * from druid_test_table where `__time` = cast('2015-03-09 23:59:59' as timestamp ); +select * from druid_test_table_n9 where `__time` = cast('2015-03-09 00:00:00' as timestamp ); +select * from druid_test_table_n9 where `__time` = cast('2015-03-09 23:59:59' as timestamp ); -select * from druid_test_table where `__time` = cast('2015-03-10 00:00:00' as timestamp ); -select * from druid_test_table where `__time` = cast('2015-03-10 23:59:59' as timestamp ); +select * from druid_test_table_n9 where `__time` = cast('2015-03-10 00:00:00' as timestamp ); +select * from druid_test_table_n9 where `__time` = cast('2015-03-10 23:59:59' as timestamp ); -EXPLAIN select * from druid_test_table where `__time` = cast('2015-03-08 00:00:00' as timestamp ); -EXPLAIN select * from druid_test_table where `__time` = cast('2015-03-08 23:59:59' as timestamp ); +EXPLAIN select * from druid_test_table_n9 where `__time` = cast('2015-03-08 00:00:00' as timestamp ); +EXPLAIN select * from druid_test_table_n9 where `__time` = cast('2015-03-08 23:59:59' as timestamp ); -EXPLAIN select * from druid_test_table where `__time` = cast('2015-03-09 00:00:00' as timestamp ); -EXPLAIN select * from druid_test_table where `__time` = cast('2015-03-09 23:59:59' as timestamp ); +EXPLAIN select * from druid_test_table_n9 where `__time` = cast('2015-03-09 00:00:00' as timestamp ); +EXPLAIN select * from druid_test_table_n9 where `__time` = cast('2015-03-09 23:59:59' as timestamp ); -EXPLAIN select * from druid_test_table where `__time` = cast('2015-03-10 00:00:00' as timestamp ); -EXPLAIN select * from druid_test_table where `__time` = cast('2015-03-10 23:59:59' as timestamp ); +EXPLAIN select * from druid_test_table_n9 where `__time` = cast('2015-03-10 00:00:00' as timestamp ); +EXPLAIN select * from druid_test_table_n9 where `__time` = cast('2015-03-10 23:59:59' as timestamp ); DROP TABLE test_base_table; -DROP TABLE druid_test_table; +DROP TABLE druid_test_table_n9; drop database druid_test_dst; http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/dynamic_partition_insert.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/dynamic_partition_insert.q b/ql/src/test/queries/clientpositive/dynamic_partition_insert.q index ab70ff1..ee13bd5 100644 --- a/ql/src/test/queries/clientpositive/dynamic_partition_insert.q +++ b/ql/src/test/queries/clientpositive/dynamic_partition_insert.q @@ -1,57 +1,57 @@ SET hive.vectorized.execution.enabled=false; set hive.mapred.mode=nonstrict; -CREATE TABLE t1 (c1 BIGINT, c2 STRING); +CREATE TABLE t1_n131 (c1 BIGINT, c2 STRING); -CREATE TABLE t2 (c1 INT, c2 STRING) +CREATE TABLE t2_n78 (c1 INT, c2 STRING) PARTITIONED BY (p1 STRING); -LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1; -LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1; -LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1; -LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1; -LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1; +LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1_n131; +LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1_n131; +LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1_n131; +LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1_n131; +LOAD DATA LOCAL INPATH '../../data/files/dynamic_partition_insert.txt' INTO TABLE t1_n131; SET hive.exec.dynamic.partition.mode=nonstrict; -INSERT OVERWRITE TABLE t2 partition(p1) SELECT *,c1 AS p1 FROM t1 DISTRIBUTE BY p1; -SELECT * FROM t2; +INSERT OVERWRITE TABLE t2_n78 partition(p1) SELECT *,c1 AS p1 FROM t1_n131 DISTRIBUTE BY p1; +SELECT * FROM t2_n78; -- no partition spec -TRUNCATE TABLE t2; -INSERT OVERWRITE TABLE t2 SELECT *,c1 AS p1 FROM t1 DISTRIBUTE BY p1; -SHOW PARTITIONS t2; -SELECT * FROM t2; +TRUNCATE TABLE t2_n78; +INSERT OVERWRITE TABLE t2_n78 SELECT *,c1 AS p1 FROM t1_n131 DISTRIBUTE BY p1; +SHOW PARTITIONS t2_n78; +SELECT * FROM t2_n78; -DROP TABLE t1; -DROP TABLE t2; +DROP TABLE t1_n131; +DROP TABLE t2_n78; -- Single partition with buckets -CREATE TABLE table1 (id int) partitioned by (key string) clustered by (id) into 2 buckets ; +CREATE TABLE table1_n15 (id int) partitioned by (key string) clustered by (id) into 2 buckets ; -- without partition schema -INSERT INTO TABLE table1 VALUES (1, '101'), (2, '202'), (3, '303'), (4, '404'), (5, '505'); -SHOW PARTITIONS table1; -SELECT * FROM table1; -DROP TABLE table1; +INSERT INTO TABLE table1_n15 VALUES (1, '101'), (2, '202'), (3, '303'), (4, '404'), (5, '505'); +SHOW PARTITIONS table1_n15; +SELECT * FROM table1_n15; +DROP TABLE table1_n15; -- Multiple partitions -CREATE TABLE table1 (name string, age int) PARTITIONED BY (country string, state string); -INSERT INTO table1 values ('John Doe', 23, 'USA', 'CA'), ('Jane Doe', 22, 'USA', 'TX'); -SHOW PARTITIONS table1; - -CREATE TABLE table2 (name string, age int) PARTITIONED BY (country string, state string); -INSERT INTO TABLE table2 SELECT * FROM table1; -SHOW PARTITIONS table2; -SELECT * FROM table2; -DROP TABLE table2; -DROP TABLE table1; - -CREATE TABLE dest1(key string) partitioned by (value string); -CREATE TABLE dest2(key string) partitioned by (value string); +CREATE TABLE table1_n15 (name string, age int) PARTITIONED BY (country string, state string); +INSERT INTO table1_n15 values ('John Doe', 23, 'USA', 'CA'), ('Jane Doe', 22, 'USA', 'TX'); +SHOW PARTITIONS table1_n15; + +CREATE TABLE table2_n10 (name string, age int) PARTITIONED BY (country string, state string); +INSERT INTO TABLE table2_n10 SELECT * FROM table1_n15; +SHOW PARTITIONS table2_n10; +SELECT * FROM table2_n10; +DROP TABLE table2_n10; +DROP TABLE table1_n15; + +CREATE TABLE dest1_n143(key string) partitioned by (value string); +CREATE TABLE dest2_n37(key string) partitioned by (value string); FROM src -INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100 -INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200; +INSERT OVERWRITE TABLE dest1_n143 SELECT src.* WHERE src.key < 100 +INSERT OVERWRITE TABLE dest2_n37 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200; SELECT distinct value FROM SRC WHERE src.key < 100; -SHOW PARTITIONS dest1; +SHOW PARTITIONS dest1_n143; SELECT distinct value FROM SRC WHERE src.key >= 100 and src.key < 200; -SHOW PARTITIONS dest2; -DROP TABLE dest1; -DROP TABLE dest2; +SHOW PARTITIONS dest2_n37; +DROP TABLE dest1_n143; +DROP TABLE dest2_n37; http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/dynamic_partition_pruning.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/dynamic_partition_pruning.q b/ql/src/test/queries/clientpositive/dynamic_partition_pruning.q index 8361316..a8f2815 100644 --- a/ql/src/test/queries/clientpositive/dynamic_partition_pruning.q +++ b/ql/src/test/queries/clientpositive/dynamic_partition_pruning.q @@ -13,74 +13,74 @@ set hive.tez.bigtable.minsize.semijoin.reduction=1; select distinct ds from srcpart; select distinct hr from srcpart; -EXPLAIN create table srcpart_date as select ds as ds, ds as `date` from srcpart group by ds; -create table srcpart_date as select ds as ds, ds as `date` from srcpart group by ds; -create table srcpart_hour as select hr as hr, hr as hour from srcpart group by hr; -create table srcpart_date_hour as select ds as ds, ds as `date`, hr as hr, hr as hour from srcpart group by ds, hr; -create table srcpart_double_hour as select (hr*2) as hr, hr as hour from srcpart group by hr; +EXPLAIN create table srcpart_date_n2 as select ds as ds, ds as `date` from srcpart group by ds; +create table srcpart_date_n2 as select ds as ds, ds as `date` from srcpart group by ds; +create table srcpart_hour_n0 as select hr as hr, hr as hour from srcpart group by hr; +create table srcpart_date_hour_n0 as select ds as ds, ds as `date`, hr as hr, hr as hour from srcpart group by ds, hr; +create table srcpart_double_hour_n0 as select (hr*2) as hr, hr as hour from srcpart group by hr; -- single column, single key -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; set hive.tez.dynamic.partition.pruning=false; -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; set hive.tez.dynamic.partition.pruning=true; select count(*) from srcpart where ds = '2008-04-08'; -- single column, single key, udf with typechange -EXPLAIN select count(*) from srcpart join srcpart_date on (day(srcpart.ds) = day(srcpart_date.ds)) where srcpart_date.`date` = '2008-04-08'; -select count(*) from srcpart join srcpart_date on (day(srcpart.ds) = day(srcpart_date.ds)) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (day(srcpart.ds) = day(srcpart_date_n2.ds)) where srcpart_date_n2.`date` = '2008-04-08'; +select count(*) from srcpart join srcpart_date_n2 on (day(srcpart.ds) = day(srcpart_date_n2.ds)) where srcpart_date_n2.`date` = '2008-04-08'; set hive.tez.dynamic.partition.pruning=false; -EXPLAIN select count(*) from srcpart join srcpart_date on (day(srcpart.ds) = day(srcpart_date.ds)) where srcpart_date.`date` = '2008-04-08'; -select count(*) from srcpart join srcpart_date on (day(srcpart.ds) = day(srcpart_date.ds)) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (day(srcpart.ds) = day(srcpart_date_n2.ds)) where srcpart_date_n2.`date` = '2008-04-08'; +select count(*) from srcpart join srcpart_date_n2 on (day(srcpart.ds) = day(srcpart_date_n2.ds)) where srcpart_date_n2.`date` = '2008-04-08'; set hive.tez.dynamic.partition.pruning=true; -- multiple sources, single key -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11; set hive.tez.dynamic.partition.pruning=false; -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11; set hive.tez.dynamic.partition.pruning=true; select count(*) from srcpart where hr = 11 and ds = '2008-04-08'; -- multiple columns single source -EXPLAIN select count(*) from srcpart join srcpart_date_hour on (srcpart.ds = srcpart_date_hour.ds and srcpart.hr = srcpart_date_hour.hr) where srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11; -select count(*) from srcpart join srcpart_date_hour on (srcpart.ds = srcpart_date_hour.ds and srcpart.hr = srcpart_date_hour.hr) where srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_date_hour_n0 on (srcpart.ds = srcpart_date_hour_n0.ds and srcpart.hr = srcpart_date_hour_n0.hr) where srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_date_hour_n0 on (srcpart.ds = srcpart_date_hour_n0.ds and srcpart.hr = srcpart_date_hour_n0.hr) where srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11; set hive.tez.dynamic.partition.pruning=false; -EXPLAIN select count(*) from srcpart join srcpart_date_hour on (srcpart.ds = srcpart_date_hour.ds and srcpart.hr = srcpart_date_hour.hr) where srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11; -select count(*) from srcpart join srcpart_date_hour on (srcpart.ds = srcpart_date_hour.ds and srcpart.hr = srcpart_date_hour.hr) where srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_date_hour_n0 on (srcpart.ds = srcpart_date_hour_n0.ds and srcpart.hr = srcpart_date_hour_n0.hr) where srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_date_hour_n0 on (srcpart.ds = srcpart_date_hour_n0.ds and srcpart.hr = srcpart_date_hour_n0.hr) where srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11; set hive.tez.dynamic.partition.pruning=true; select count(*) from srcpart where ds = '2008-04-08' and hr = 11; -- empty set -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = 'I DONT EXIST'; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = 'I DONT EXIST'; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = 'I DONT EXIST'; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = 'I DONT EXIST'; set hive.tez.dynamic.partition.pruning=false; -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = 'I DONT EXIST'; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = 'I DONT EXIST'; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = 'I DONT EXIST'; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = 'I DONT EXIST'; set hive.tez.dynamic.partition.pruning=true; select count(*) from srcpart where ds = 'I DONT EXIST'; -- expressions -EXPLAIN select count(*) from srcpart join srcpart_double_hour on (srcpart.hr = cast(srcpart_double_hour.hr/2 as int)) where srcpart_double_hour.hour = 11; -select count(*) from srcpart join srcpart_double_hour on (srcpart.hr = cast(srcpart_double_hour.hr/2 as int)) where srcpart_double_hour.hour = 11; -EXPLAIN select count(*) from srcpart join srcpart_double_hour on (srcpart.hr*2 = srcpart_double_hour.hr) where srcpart_double_hour.hour = 11; -select count(*) from srcpart join srcpart_double_hour on (srcpart.hr*2 = srcpart_double_hour.hr) where srcpart_double_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr = cast(srcpart_double_hour_n0.hr/2 as int)) where srcpart_double_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr = cast(srcpart_double_hour_n0.hr/2 as int)) where srcpart_double_hour_n0.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr*2 = srcpart_double_hour_n0.hr) where srcpart_double_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr*2 = srcpart_double_hour_n0.hr) where srcpart_double_hour_n0.hour = 11; set hive.tez.dynamic.partition.pruning=false; -EXPLAIN select count(*) from srcpart join srcpart_double_hour on (srcpart.hr = cast(srcpart_double_hour.hr/2 as int)) where srcpart_double_hour.hour = 11; -select count(*) from srcpart join srcpart_double_hour on (srcpart.hr = cast(srcpart_double_hour.hr/2 as int)) where srcpart_double_hour.hour = 11; -EXPLAIN select count(*) from srcpart join srcpart_double_hour on (srcpart.hr*2 = srcpart_double_hour.hr) where srcpart_double_hour.hour = 11; -select count(*) from srcpart join srcpart_double_hour on (srcpart.hr*2 = srcpart_double_hour.hr) where srcpart_double_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr = cast(srcpart_double_hour_n0.hr/2 as int)) where srcpart_double_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr = cast(srcpart_double_hour_n0.hr/2 as int)) where srcpart_double_hour_n0.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr*2 = srcpart_double_hour_n0.hr) where srcpart_double_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr*2 = srcpart_double_hour_n0.hr) where srcpart_double_hour_n0.hour = 11; set hive.tez.dynamic.partition.pruning=true; select count(*) from srcpart where hr = 11; -EXPLAIN select count(*) from srcpart join srcpart_double_hour on (cast(srcpart.hr*2 as string) = cast(srcpart_double_hour.hr as string)) where srcpart_double_hour.hour = 11; -select count(*) from srcpart join srcpart_double_hour on (cast(srcpart.hr*2 as string) = cast(srcpart_double_hour.hr as string)) where srcpart_double_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_double_hour_n0 on (cast(srcpart.hr*2 as string) = cast(srcpart_double_hour_n0.hr as string)) where srcpart_double_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_double_hour_n0 on (cast(srcpart.hr*2 as string) = cast(srcpart_double_hour_n0.hr as string)) where srcpart_double_hour_n0.hour = 11; set hive.tez.dynamic.partition.pruning=true; select count(*) from srcpart where cast(hr as string) = 11; @@ -91,29 +91,29 @@ select count(*) from srcpart join (select ds as ds, ds as `date` from srcpart gr select count(*) from srcpart where ds = '2008-04-08'; -- non-equi join -EXPLAIN select count(*) from srcpart, srcpart_date_hour where (srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11) and (srcpart.ds = srcpart_date_hour.ds or srcpart.hr = srcpart_date_hour.hr); -select count(*) from srcpart, srcpart_date_hour where (srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11) and (srcpart.ds = srcpart_date_hour.ds or srcpart.hr = srcpart_date_hour.hr); +EXPLAIN select count(*) from srcpart, srcpart_date_hour_n0 where (srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11) and (srcpart.ds = srcpart_date_hour_n0.ds or srcpart.hr = srcpart_date_hour_n0.hr); +select count(*) from srcpart, srcpart_date_hour_n0 where (srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11) and (srcpart.ds = srcpart_date_hour_n0.ds or srcpart.hr = srcpart_date_hour_n0.hr); -- old style join syntax -EXPLAIN select count(*) from srcpart, srcpart_date_hour where srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11 and srcpart.ds = srcpart_date_hour.ds and srcpart.hr = srcpart_date_hour.hr; -select count(*) from srcpart, srcpart_date_hour where srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11 and srcpart.ds = srcpart_date_hour.ds and srcpart.hr = srcpart_date_hour.hr; +EXPLAIN select count(*) from srcpart, srcpart_date_hour_n0 where srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11 and srcpart.ds = srcpart_date_hour_n0.ds and srcpart.hr = srcpart_date_hour_n0.hr; +select count(*) from srcpart, srcpart_date_hour_n0 where srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11 and srcpart.ds = srcpart_date_hour_n0.ds and srcpart.hr = srcpart_date_hour_n0.hr; -- left join -EXPLAIN select count(*) from srcpart left join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; -EXPLAIN select count(*) from srcpart_date left join srcpart on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart left join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart_date_n2 left join srcpart on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; -- full outer -EXPLAIN select count(*) from srcpart full outer join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart full outer join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; -- with static pruning -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11 and srcpart.hr = 11; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11 and srcpart.hr = 11; -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart.hr = 13; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart.hr = 13; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11 and srcpart.hr = 11; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11 and srcpart.hr = 11; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart.hr = 13; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart.hr = 13; -- union + subquery EXPLAIN select count(*) from srcpart where srcpart.ds in (select max(srcpart.ds) from srcpart union all select min(srcpart.ds) from srcpart); @@ -128,35 +128,35 @@ set hive.auto.convert.join.noconditionaltask = true; set hive.auto.convert.join.noconditionaltask.size = 10000000; -- single column, single key -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; select count(*) from srcpart where ds = '2008-04-08'; -- single column, single key, udf with typechange -EXPLAIN select count(*) from srcpart join srcpart_date on (day(srcpart.ds) = day(srcpart_date.ds)) where srcpart_date.`date` = '2008-04-08'; -select count(*) from srcpart join srcpart_date on (day(srcpart.ds) = day(srcpart_date.ds)) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (day(srcpart.ds) = day(srcpart_date_n2.ds)) where srcpart_date_n2.`date` = '2008-04-08'; +select count(*) from srcpart join srcpart_date_n2 on (day(srcpart.ds) = day(srcpart_date_n2.ds)) where srcpart_date_n2.`date` = '2008-04-08'; -- multiple sources, single key -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11; select count(*) from srcpart where hr = 11 and ds = '2008-04-08'; -- multiple columns single source -EXPLAIN select count(*) from srcpart join srcpart_date_hour on (srcpart.ds = srcpart_date_hour.ds and srcpart.hr = srcpart_date_hour.hr) where srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11; -select count(*) from srcpart join srcpart_date_hour on (srcpart.ds = srcpart_date_hour.ds and srcpart.hr = srcpart_date_hour.hr) where srcpart_date_hour.`date` = '2008-04-08' and srcpart_date_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_date_hour_n0 on (srcpart.ds = srcpart_date_hour_n0.ds and srcpart.hr = srcpart_date_hour_n0.hr) where srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_date_hour_n0 on (srcpart.ds = srcpart_date_hour_n0.ds and srcpart.hr = srcpart_date_hour_n0.hr) where srcpart_date_hour_n0.`date` = '2008-04-08' and srcpart_date_hour_n0.hour = 11; select count(*) from srcpart where ds = '2008-04-08' and hr = 11; -- empty set -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = 'I DONT EXIST'; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = 'I DONT EXIST'; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = 'I DONT EXIST'; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = 'I DONT EXIST'; -- expressions -EXPLAIN select count(*) from srcpart join srcpart_double_hour on (srcpart.hr = cast(srcpart_double_hour.hr/2 as int)) where srcpart_double_hour.hour = 11; -select count(*) from srcpart join srcpart_double_hour on (srcpart.hr = cast(srcpart_double_hour.hr/2 as int)) where srcpart_double_hour.hour = 11; -EXPLAIN select count(*) from srcpart join srcpart_double_hour on (srcpart.hr*2 = srcpart_double_hour.hr) where srcpart_double_hour.hour = 11; -select count(*) from srcpart join srcpart_double_hour on (srcpart.hr*2 = srcpart_double_hour.hr) where srcpart_double_hour.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr = cast(srcpart_double_hour_n0.hr/2 as int)) where srcpart_double_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr = cast(srcpart_double_hour_n0.hr/2 as int)) where srcpart_double_hour_n0.hour = 11; +EXPLAIN select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr*2 = srcpart_double_hour_n0.hr) where srcpart_double_hour_n0.hour = 11; +select count(*) from srcpart join srcpart_double_hour_n0 on (srcpart.hr*2 = srcpart_double_hour_n0.hr) where srcpart_double_hour_n0.hour = 11; select count(*) from srcpart where hr = 11; @@ -169,22 +169,22 @@ select count(*) from srcpart where ds = '2008-04-08'; set hive.stats.fetch.column.stats=true; -- left join -EXPLAIN select count(*) from srcpart left join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; -EXPLAIN select count(*) from srcpart_date left join srcpart on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart left join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart_date_n2 left join srcpart on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; -- full outer -EXPLAIN select count(*) from srcpart full outer join srcpart_date on (srcpart.ds = srcpart_date.ds) where srcpart_date.`date` = '2008-04-08'; +EXPLAIN select count(*) from srcpart full outer join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) where srcpart_date_n2.`date` = '2008-04-08'; -- with static pruning -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11 and srcpart.hr = 11; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart_hour.hour = 11 and srcpart.hr = 11; -EXPLAIN select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart.hr = 13; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11 and srcpart.hr = 11; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart_hour_n0.hour = 11 and srcpart.hr = 11; +EXPLAIN select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart.hr = 13; -select count(*) from srcpart join srcpart_date on (srcpart.ds = srcpart_date.ds) join srcpart_hour on (srcpart.hr = srcpart_hour.hr) -where srcpart_date.`date` = '2008-04-08' and srcpart.hr = 13; +select count(*) from srcpart join srcpart_date_n2 on (srcpart.ds = srcpart_date_n2.ds) join srcpart_hour_n0 on (srcpart.hr = srcpart_hour_n0.hr) +where srcpart_date_n2.`date` = '2008-04-08' and srcpart.hr = 13; -- union + subquery EXPLAIN select distinct(ds) from srcpart where srcpart.ds in (select max(srcpart.ds) from srcpart union all select min(srcpart.ds) from srcpart); @@ -200,12 +200,12 @@ set hive.vectorized.execution.enabled=false; set hive.exec.max.dynamic.partitions=1000; insert into table srcpart_orc partition (ds, hr) select key, value, ds, hr from srcpart; -EXPLAIN select count(*) from srcpart_orc join srcpart_date_hour on (srcpart_orc.ds = srcpart_date_hour.ds and srcpart_orc.hr = srcpart_date_hour.hr) where srcpart_date_hour.hour = 11 and (srcpart_date_hour.`date` = '2008-04-08' or srcpart_date_hour.`date` = '2008-04-09'); -select count(*) from srcpart_orc join srcpart_date_hour on (srcpart_orc.ds = srcpart_date_hour.ds and srcpart_orc.hr = srcpart_date_hour.hr) where srcpart_date_hour.hour = 11 and (srcpart_date_hour.`date` = '2008-04-08' or srcpart_date_hour.`date` = '2008-04-09'); +EXPLAIN select count(*) from srcpart_orc join srcpart_date_hour_n0 on (srcpart_orc.ds = srcpart_date_hour_n0.ds and srcpart_orc.hr = srcpart_date_hour_n0.hr) where srcpart_date_hour_n0.hour = 11 and (srcpart_date_hour_n0.`date` = '2008-04-08' or srcpart_date_hour_n0.`date` = '2008-04-09'); +select count(*) from srcpart_orc join srcpart_date_hour_n0 on (srcpart_orc.ds = srcpart_date_hour_n0.ds and srcpart_orc.hr = srcpart_date_hour_n0.hr) where srcpart_date_hour_n0.hour = 11 and (srcpart_date_hour_n0.`date` = '2008-04-08' or srcpart_date_hour_n0.`date` = '2008-04-09'); select count(*) from srcpart where (ds = '2008-04-08' or ds = '2008-04-09') and hr = 11; drop table srcpart_orc; -drop table srcpart_date; -drop table srcpart_hour; -drop table srcpart_date_hour; -drop table srcpart_double_hour; +drop table srcpart_date_n2; +drop table srcpart_hour_n0; +drop table srcpart_date_hour_n0; +drop table srcpart_double_hour_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/9bf28a3c/ql/src/test/queries/clientpositive/dynamic_rdd_cache.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/dynamic_rdd_cache.q b/ql/src/test/queries/clientpositive/dynamic_rdd_cache.q index bb041c9..8c0e863 100644 --- a/ql/src/test/queries/clientpositive/dynamic_rdd_cache.q +++ b/ql/src/test/queries/clientpositive/dynamic_rdd_cache.q @@ -20,33 +20,33 @@ ON (X.key = Z.key) SELECT sum(hash(Y.key,Y.value)) GROUP BY Y.key; -CREATE TABLE dest1(key INT, value STRING); -CREATE TABLE dest2(key INT, value STRING); +CREATE TABLE dest1_n90(key INT, value STRING); +CREATE TABLE dest2_n24(key INT, value STRING); 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; +INSERT OVERWRITE TABLE dest1_n90 SELECT src.key, sum(SUBSTR(src.value,5)) GROUP BY src.key +INSERT OVERWRITE TABLE dest2_n24 SELECT src.key, sum(SUBSTR(src.value,5)) GROUP BY src.key; -SELECT dest1.* FROM dest1; -SELECT dest2.* FROM dest2; +SELECT dest1_n90.* FROM dest1_n90; +SELECT dest2_n24.* FROM dest2_n24; -DROP TABLE dest1; -DROP TABLE dest2; +DROP TABLE dest1_n90; +DROP TABLE dest2_n24; -- UNION TEST -CREATE TABLE tmptable(key STRING, value INT); +CREATE TABLE tmptable_n8(key STRING, value INT); EXPLAIN -INSERT OVERWRITE TABLE tmptable +INSERT OVERWRITE TABLE tmptable_n8 SELECT unionsrc.key, unionsrc.value FROM (SELECT 'tst1' AS key, count(1) AS value FROM src s1 UNION ALL SELECT 'tst2' AS key, count(1) AS value FROM src s2 UNION ALL SELECT 'tst3' AS key, count(1) AS value FROM src s3) unionsrc; -SELECT * FROM tmptable x SORT BY x.key; +SELECT * FROM tmptable_n8 x SORT BY x.key; DROP TABLE tmtable;
