| --! qt:disabled:HIVE-24816 |
| --! qt:dataset:alltypesorc |
| SET hive.ctas.external.tables=true; |
| SET hive.external.table.purge.default = true; |
| CREATE EXTERNAL TABLE druid_alltypesorc |
| STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler' |
| TBLPROPERTIES ("druid.segment.granularity" = "HOUR", "druid.query.granularity" = "MINUTE") |
| AS |
| SELECT cast (`ctimestamp2` as timestamp with local time zone) as `__time`, |
| cstring1, |
| cstring2, |
| cdouble, |
| cfloat, |
| ctinyint, |
| csmallint, |
| cint, |
| cbigint, |
| cboolean1, |
| cboolean2 |
| FROM alltypesorc where ctimestamp2 IS NOT NULL; |
| |
| SELECT COUNT(*) FROM druid_alltypesorc; |
| |
| INSERT INTO TABLE druid_alltypesorc |
| SELECT cast (`ctimestamp1` as timestamp with local time zone) as `__time`, |
| cstring1, |
| cstring2, |
| cdouble, |
| cfloat, |
| ctinyint, |
| csmallint, |
| cint, |
| cbigint, |
| cboolean1, |
| cboolean2 |
| FROM alltypesorc where ctimestamp1 IS NOT NULL; |
| |
| -- ANALYZE COLUMN STATS FOR DRUID TABLE |
| analyze table druid_alltypesorc compute statistics for columns; |
| |
| SELECT COUNT(*) FROM druid_alltypesorc; |
| |
| INSERT OVERWRITE TABLE druid_alltypesorc |
| SELECT cast (`ctimestamp1` as timestamp with local time zone) as `__time`, |
| cstring1, |
| cstring2, |
| cdouble, |
| cfloat, |
| ctinyint, |
| csmallint, |
| cint, |
| cbigint, |
| cboolean1, |
| cboolean2 |
| FROM alltypesorc where ctimestamp1 IS NOT NULL; |
| |
| SELECT COUNT(*) FROM druid_alltypesorc; |
| |
| DROP TABLE druid_alltypesorc; |
| |
| -- Test create then insert |
| |
| create database druid_test_create_then_insert; |
| use druid_test_create_then_insert; |
| |
| create table test_table_n9(`timecolumn` timestamp, `userid` string, `num_l` float); |
| |
| 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 EXTERNAL 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_n1 |
| select cast(`timecolumn` as timestamp with local time zone) as `__time`, `userid`, `num_l` FROM test_table_n9; |
| |
| select count(*) FROM druid_table_n1; |
| |
| 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 |
| |
| create database druid_test_dst; |
| use druid_test_dst; |
| |
| create table test_base_table(`timecolumn` timestamp, `userid` string, `num_l` float); |
| insert into test_base_table values ('2015-03-08 00:00:00', 'i1-start', 4); |
| insert into test_base_table values ('2015-03-08 23:59:59', 'i1-end', 1); |
| insert into test_base_table values ('2015-03-09 00:00:00', 'i2-start', 4); |
| 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 EXTERNAL 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_n9; |
| |
| 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_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_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_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_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_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_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_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_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_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_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_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 ); |
| |
| -- disable rollup and insert identical rows |
| SET hive.druid.rollup=false; |
| insert into druid_test_table_n9 values |
| ('2015-03-12 00:00:00', 'i4-1', 4), |
| ('2015-03-12 00:00:00', 'i4-1', 2), |
| ('2015-03-12 00:00:00', 'i4-1', 1); |
| |
| select * from druid_test_table_n9 where `__time` = cast('2015-03-12 00:00:00' as timestamp ); |
| |
| DROP TABLE test_base_table; |
| DROP TABLE druid_test_table_n9; |
| |
| drop database druid_test_dst; |