testdata/workloads/functional-query/queries/QueryTest/multiple-filesystems.test

====
---- QUERY
# Verify various CREATE TABLE for unpartitioned tables on non-default
# filesystem (e.g. S3A).
create external table tinytable_like like functional.tinytable
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/tinytable'
---- RESULTS
====
---- QUERY
select * from tinytable_like
---- RESULTS
'aaaaaaa','bbbbbbb'
'ccccc','dddd'
'eeeeeeee','f'
---- TYPES
STRING, STRING
====
---- QUERY
compute stats tinytable_like
---- RESULTS
'Updated 1 partition(s) and 2 column(s).'
---- TYPES
STRING
====
---- QUERY
show table stats tinytable_like
---- RESULTS
3,1,'38B','NOT CACHED','NOT CACHED','TEXT','false',regex:.*
---- TYPES
BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING
====
---- QUERY
create external table tinytable_copy (a string, b string)
row format delimited fields terminated by ','
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/tinytable'
---- RESULTS
====
---- QUERY
select * from tinytable_copy
---- RESULTS
'aaaaaaa','bbbbbbb'
'ccccc','dddd'
'eeeeeeee','f'
---- TYPES
STRING, STRING
====
---- QUERY
# Verify CREATE TABLE for partitioned table on default filesystem.
create external table alltypes like functional_parquet.alltypes
---- RESULTS
====
---- QUERY
select * from alltypes
---- RESULTS
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
====
---- QUERY
# Verify ADD PARTITION for non-default filesystem.
alter table alltypes add partition(year=2009, month=1)
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/alltypes_parquet/year=2009/month=1'
---- RESULTS
====
---- QUERY
alter table alltypes add partition(year=2009, month=2)
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/alltypes_parquet/year=2009/month=2'
---- RESULTS
====
---- QUERY
alter table alltypes add partition(year=2010, month=1)
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/alltypes_parquet/year=2010/month=1'
---- RESULTS
====
---- QUERY
alter table alltypes add partition(year=2010, month=2)
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/alltypes_parquet/year=2010/month=2'
---- RESULTS
====
---- QUERY
select * from alltypes where id%100=0 order by id
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
100,true,0,0,0,0,0,0,'01/11/09','0',2009-01-11 01:40:04.500000000,2009,1
200,true,0,0,0,0,0,0,'01/21/09','0',2009-01-21 03:20:09,2009,1
300,true,0,0,0,0,0,0,'01/31/09','0',2009-01-31 05:00:13.500000000,2009,1
400,true,0,0,0,0,0,0,'02/10/09','0',2009-02-10 01:30:04.500000000,2009,2
500,true,0,0,0,0,0,0,'02/20/09','0',2009-02-20 03:10:08.550000000,2009,2
3700,true,0,0,0,0,0,0,'01/06/10','0',2010-01-06 00:50:02.250000000,2010,1
3800,true,0,0,0,0,0,0,'01/16/10','0',2010-01-16 02:30:06.750000000,2010,1
3900,true,0,0,0,0,0,0,'01/26/10','0',2010-01-26 04:10:11.250000000,2010,1
4000,true,0,0,0,0,0,0,'02/05/10','0',2010-02-05 00:40:01.800000000,2010,2
4100,true,0,0,0,0,0,0,'02/15/10','0',2010-02-15 02:20:06.300000000,2010,2
4200,true,0,0,0,0,0,0,'02/25/10','0',2010-02-25 04:00:10.800000000,2010,2
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
====
---- QUERY
# Verify DROP PARTITION for non-default filesystem.
alter table alltypes drop partition(year=2010, month=1)
---- RESULTS
====
---- QUERY
alter table alltypes drop partition(year=2010, month=2)
---- RESULTS
====
---- QUERY
select * from alltypes where id%100=0 order by id
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
100,true,0,0,0,0,0,0,'01/11/09','0',2009-01-11 01:40:04.500000000,2009,1
200,true,0,0,0,0,0,0,'01/21/09','0',2009-01-21 03:20:09,2009,1
300,true,0,0,0,0,0,0,'01/31/09','0',2009-01-31 05:00:13.500000000,2009,1
400,true,0,0,0,0,0,0,'02/10/09','0',2009-02-10 01:30:04.500000000,2009,2
500,true,0,0,0,0,0,0,'02/20/09','0',2009-02-20 03:10:08.550000000,2009,2
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
====
---- QUERY
# Verify tables with partitions that span multiple filesystems.
# Note: intentionally not using $SECONDARY_FILESYSTEM so that the partition points
# to the default filesystem.
alter table alltypes add partition(year=2010, month=3)
location '/test-warehouse/alltypes_parquet/year=2010/month=3'
---- RESULTS
====
---- QUERY
# Note: intentionally not using $SECONDARY_FILESYSTEM so that the partition points
# to the default filesystem.
alter table alltypes add partition(year=2010, month=4)
location '/test-warehouse/alltypes_parquet/year=2010/month=4'
---- RESULTS
====
---- QUERY
select * from alltypes where id%100=0 order by id
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
100,true,0,0,0,0,0,0,'01/11/09','0',2009-01-11 01:40:04.500000000,2009,1
200,true,0,0,0,0,0,0,'01/21/09','0',2009-01-21 03:20:09,2009,1
300,true,0,0,0,0,0,0,'01/31/09','0',2009-01-31 05:00:13.500000000,2009,1
400,true,0,0,0,0,0,0,'02/10/09','0',2009-02-10 01:30:04.500000000,2009,2
500,true,0,0,0,0,0,0,'02/20/09','0',2009-02-20 03:10:08.550000000,2009,2
4300,true,0,0,0,0,0,0,'03/07/10','0',2010-03-07 01:00:02.700000000,2010,3
4400,true,0,0,0,0,0,0,'03/17/10','0',2010-03-17 01:40:07.200000000,2010,3
4500,true,0,0,0,0,0,0,'03/27/10','0',2010-03-27 03:20:11.700000000,2010,3
4600,true,0,0,0,0,0,0,'04/06/10','0',2010-04-06 00:50:02.250000000,2010,4
4700,true,0,0,0,0,0,0,'04/16/10','0',2010-04-16 02:30:06.750000000,2010,4
4800,true,0,0,0,0,0,0,'04/26/10','0',2010-04-26 04:10:11.250000000,2010,4
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
====
---- QUERY
compute stats alltypes
---- RESULTS
'Updated 4 partition(s) and 11 column(s).'
---- TYPES
STRING
====
---- QUERY
show table stats alltypes
---- RESULTS
'2009','1',310,1,regex:.+KB,'NOT CACHED','NOT CACHED','PARQUET','false',regex:.*
'2009','2',280,1,regex:.+KB,'NOT CACHED','NOT CACHED','PARQUET','false',regex:.*
'2010','3',310,1,regex:.+KB,'NOT CACHED','NOT CACHED','PARQUET','false',regex:.*
'2010','4',300,1,regex:.+KB,'NOT CACHED','NOT CACHED','PARQUET','false',regex:.*
'Total','',1200,4,regex:.+KB,'0B','','','',''
---- TYPES
STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING
====
---- QUERY
drop table alltypes
---- RESULTS
====
---- QUERY
# Verify CREATE TABLE for partitioned table on non-default filesystem.
create external table alltypes like functional_parquet.alltypes
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/alltypes'
---- RESULTS
====
---- QUERY
select * from alltypes
---- RESULTS
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
====
---- QUERY
# Verify tables with partitions that span multiple filesystems, split on the other partition.
# Note: intentionally not using $SECONDARY_FILESYSTEM so that the partition points
# to the default filesystem.
alter table alltypes add partition(year=2009, month=5)
location '/test-warehouse/alltypes_parquet/year=2009/month=5'
---- RESULTS
====
---- QUERY
alter table alltypes add partition(year=2009, month=6)
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/alltypes_parquet/year=2009/month=6'
---- RESULTS
====
---- QUERY
# Note: intentionally not using $SECONDARY_FILESYSTEM so that the partition points
# to the default filesystem.
alter table alltypes add partition(year=2010, month=5)
location '/test-warehouse/alltypes_parquet/year=2010/month=5'
---- RESULTS
====
---- QUERY
# This partition directory was dropped earlier, so this also verifies the partition
# directory was not deleted.
alter table alltypes add partition(year=2010, month=2)
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/alltypes_parquet/year=2010/month=2'
---- RESULTS
====
---- QUERY
select * from alltypes where id%100=0 order by id
---- RESULTS
1200,true,0,0,0,0,0,0,'05/01/09','0',2009-05-01 00:00:00,2009,5
1300,true,0,0,0,0,0,0,'05/11/09','0',2009-05-11 01:40:04.500000000,2009,5
1400,true,0,0,0,0,0,0,'05/21/09','0',2009-05-21 03:20:09,2009,5
1500,true,0,0,0,0,0,0,'05/31/09','0',2009-05-31 05:00:13.500000000,2009,5
1600,true,0,0,0,0,0,0,'06/10/09','0',2009-06-10 01:30:04.500000000,2009,6
1700,true,0,0,0,0,0,0,'06/20/09','0',2009-06-20 03:10:08.550000000,2009,6
1800,true,0,0,0,0,0,0,'06/30/09','0',2009-06-30 04:50:13.500000000,2009,6
4000,true,0,0,0,0,0,0,'02/05/10','0',2010-02-05 00:40:01.800000000,2010,2
4100,true,0,0,0,0,0,0,'02/15/10','0',2010-02-15 02:20:06.300000000,2010,2
4200,true,0,0,0,0,0,0,'02/25/10','0',2010-02-25 04:00:10.800000000,2010,2
4900,true,0,0,0,0,0,0,'05/06/10','0',2010-05-06 00:50:02.250000000,2010,5
5000,true,0,0,0,0,0,0,'05/16/10','0',2010-05-16 02:30:06.750000000,2010,5
5100,true,0,0,0,0,0,0,'05/26/10','0',2010-05-26 04:10:11.250000000,2010,5
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
====
---- QUERY
compute stats alltypes
---- RESULTS
'Updated 4 partition(s) and 11 column(s).'
---- TYPES
STRING
====
---- QUERY
show table stats alltypes
---- RESULTS
'2009','5',310,1,regex:.+KB,'NOT CACHED','NOT CACHED','PARQUET','false',regex:.*
'2009','6',300,1,regex:.+KB,'NOT CACHED','NOT CACHED','PARQUET','false',regex:.*
'2010','2',280,1,regex:.+KB,'NOT CACHED','NOT CACHED','PARQUET','false',regex:.*
'2010','5',310,1,regex:.+KB,'NOT CACHED','NOT CACHED','PARQUET','false',regex:.*
'Total','',1200,4,regex:.+KB,'0B','','','',''
---- TYPES
STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING
====
---- QUERY
drop table alltypes
---- RESULTS
====
---- QUERY
# Create a table on the default filesystem (note: database location was on defaultFS).
create table ids as select id from functional.alltypes
where id = 13 or id = 1533 or id = 2571 or id = 3125 or id = 4862
---- RESULTS
'Inserted 5 row(s)'
---- TYPES
STRING
====
---- QUERY
select id from ids order by id
---- RESULTS
13
1533
2571
3125
4862
---- TYPES
INT
====
---- QUERY
# Join between default (HDFS) and non-default filesystem (e.g. S3A) table.
select ids.id, date_string_col from functional_seq_record_bzip.alltypes, ids
where alltypes.id = ids.id
order by id
---- RESULTS
13,'01/02/09'
1533,'06/03/09'
2571,'09/15/09'
3125,'11/09/09'
4862,'05/02/10'
---- TYPES
INT, STRING
====
---- QUERY
create table alltypes_multipart_insert like functional_parquet.alltypes
---- RESULTS
====
---- QUERY
# ADD PARTITION on a non-default filesystem.
alter table alltypes_multipart_insert add partition(year=2009, month=1)
location '$SECONDARY_FILESYSTEM/multi_fs_tests/$DATABASE.db/alltypes_multipart_insert/year=2009/month=1'
---- RESULTS
====
---- QUERY
insert into alltypes_multipart_insert partition (year=2009, month=1)
select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col,
float_col, double_col, date_string_col, string_col, timestamp_col
from functional.alltypes where year = 2009 and month = 1
---- RESULTS
year=2009/month=1/: 310
====
---- QUERY
# ADD PARTITION on the default filesystem.
# Point to unique database so we don't overwrite someone else's data.
alter table alltypes_multipart_insert add partition(year=2009, month=2)
location '/test-warehouse/$DATABASE.db/alltypes_multipart_insert/year=2009/month=2'
---- RESULTS
====
---- QUERY
insert into alltypes_multipart_insert partition (year=2009, month=2)
select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col,
float_col, double_col, date_string_col, string_col, timestamp_col
from functional.alltypes where year = 2009 and month = 2
---- RESULTS
year=2009/month=2/: 280
====
---- QUERY
select * from alltypes_multipart_insert where id%100=0 order by id;
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
100,true,0,0,0,0,0,0,'01/11/09','0',2009-01-11 01:40:04.500000000,2009,1
200,true,0,0,0,0,0,0,'01/21/09','0',2009-01-21 03:20:09,2009,1
300,true,0,0,0,0,0,0,'01/31/09','0',2009-01-31 05:00:13.500000000,2009,1
400,true,0,0,0,0,0,0,'02/10/09','0',2009-02-10 01:30:04.500000000,2009,2
500,true,0,0,0,0,0,0,'02/20/09','0',2009-02-20 03:10:08.550000000,2009,2
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
====