| ==== |
| ---- QUERY |
| # First create an unpartitioned table |
| create external table t1 (i int) |
| location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/t1_tmp1' |
| ---- RESULTS |
| 'Table has been created.' |
| ==== |
| ---- QUERY |
| describe t1 |
| ---- RESULTS |
| 'i','int','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| # Add some columns |
| alter table t1 add columns (t tinyint, s string comment 'Str Col') |
| ---- RESULTS |
| 'New column(s) have been added to the table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Add columns that already exist with "if not exists" clause. |
| alter table t1 add if not exists columns (t tinyint, s string comment 'Str Col') |
| ---- RESULTS |
| 'No new column(s) have been added to the table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Add columns that do not exist with "if not exists" clause. |
| alter table t1 add if not exists columns (t2 tinyint, s2 string comment 'Str Col') |
| ---- RESULTS |
| 'New column(s) have been added to the table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Add a column that already exists and a new column that does not exist with |
| # "if not exists" clause. |
| alter table t1 add if not exists columns (t3 tinyint, s2 string comment 'Str Col') |
| ---- RESULTS |
| 'New column(s) have been added to the table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Add a new column that does not exist. |
| alter table t1 add column t4 tinyint |
| ---- RESULTS |
| 'New column(s) have been added to the table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Add a new column that does not exist with "if not exists" clause. |
| alter table t1 add column if not exists t5 tinyint |
| ---- RESULTS |
| 'New column(s) have been added to the table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Add a new column that already exists with "if not exists" clause. |
| alter table t1 add column if not exists t5 tinyint |
| ---- RESULTS |
| 'No new column(s) have been added to the table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| describe t1 |
| ---- RESULTS |
| 'i','int','' |
| 't','tinyint','' |
| 's','string','Str Col' |
| 't2','tinyint','' |
| 's2','string','Str Col' |
| 't3','tinyint','' |
| 't4','tinyint','' |
| 't5','tinyint','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| alter table t1 rename to t2 |
| ---- RESULTS |
| 'Renaming was successful.' |
| ==== |
| ---- QUERY |
| show tables |
| ---- RESULTS |
| 't2' |
| 'part_data' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Move the table to a different database |
| alter table t2 rename to $DATABASE2.t1_inotherdb |
| ---- RESULTS |
| 'Renaming was successful.' |
| ==== |
| ---- QUERY |
| # No longer appears in this database |
| show tables |
| ---- RESULTS |
| 'part_data' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Shows up in the second database |
| show tables in $DATABASE2 |
| ---- RESULTS |
| 't1_inotherdb' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Move the table back to this database |
| alter table $DATABASE2.t1_inotherdb rename to t2 |
| ---- RESULTS |
| 'Renaming was successful.' |
| ==== |
| ---- QUERY |
| # make sure the new table shows the same columns as the old table |
| describe t2 |
| ---- RESULTS |
| 'i','int','' |
| 't','tinyint','' |
| 's','string','Str Col' |
| 't2','tinyint','' |
| 's2','string','Str Col' |
| 't3','tinyint','' |
| 't4','tinyint','' |
| 't5','tinyint','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| alter table t2 drop column t |
| ---- RESULTS |
| 'Column has been dropped.' |
| ==== |
| ---- QUERY |
| # The dropped column no longer shows up |
| describe t2 |
| ---- RESULTS |
| 'i','int','' |
| 's','string','Str Col' |
| 't2','tinyint','' |
| 's2','string','Str Col' |
| 't3','tinyint','' |
| 't4','tinyint','' |
| 't5','tinyint','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| # Replace the columns with new values |
| alter table t2 replace columns (c1 bigint comment 'id col', c2 string, c3 int) |
| ---- RESULTS |
| 'Table columns have been replaced.' |
| ==== |
| ---- QUERY |
| describe t2 |
| ---- RESULTS |
| 'c1','bigint','id col' |
| 'c2','string','' |
| 'c3','int','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| # Should be able to read/write using the new column types |
| insert overwrite table t2 select 1, '50', 2 from functional.alltypes limit 2 |
| ---- RESULTS |
| : 2 |
| ==== |
| ---- QUERY |
| select * from t2 |
| ---- RESULTS |
| 1,'50',2 |
| 1,'50',2 |
| ---- TYPES |
| bigint,string,int |
| ==== |
| ---- QUERY |
| alter table t2 change column c2 int_col int comment 'changed to int col' |
| ---- RESULTS |
| 'Column has been altered.' |
| ==== |
| ---- QUERY |
| alter table t2 change column c1 id_col bigint |
| ---- RESULTS |
| 'Column has been altered.' |
| ==== |
| ---- QUERY |
| alter table t2 change column c3 c3 int comment 'added a comment' |
| ---- RESULTS |
| 'Column has been altered.' |
| ==== |
| ---- QUERY |
| describe t2 |
| ---- RESULTS |
| 'id_col','bigint','id col' |
| 'int_col','int','changed to int col' |
| 'c3','int','added a comment' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| select * from t2 |
| ---- RESULTS |
| 1,50,2 |
| 1,50,2 |
| ---- TYPES |
| bigint,int,int |
| ==== |
| ---- QUERY |
| # Add some complex-typed columns |
| alter table t2 add columns ( |
| x array<int>, |
| y map<string,float> comment 'Map Col', |
| z struct<f1:boolean,f2:bigint>) |
| ---- RESULTS |
| 'New column(s) have been added to the table.' |
| ==== |
| ---- QUERY |
| describe t2 |
| ---- RESULTS |
| 'id_col','bigint','id col' |
| 'int_col','int','changed to int col' |
| 'c3','int','added a comment' |
| 'x','array<int>','' |
| 'y','map<string,float>','Map Col' |
| 'z','struct<\n f1:boolean,\n f2:bigint\n>','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| # Replace columns with some complex-typed columns |
| alter table t2 replace columns ( |
| a int comment 'Int Col', |
| b struct<f1:array<int>,f2:map<string,struct<f1:bigint>>>, |
| c double) |
| ---- RESULTS |
| 'Table columns have been replaced.' |
| ==== |
| ---- QUERY |
| describe t2 |
| ---- RESULTS |
| 'a','int','Int Col' |
| 'b','struct<\n f1:array<int>,\n f2:map<string,struct<\n f1:bigint\n >>\n>','' |
| 'c','double','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| # IMPALA-2974: Change a single column to a complex type. |
| alter table t2 change column c c array<struct<f1:decimal(10,2)>>; |
| describe t2; |
| ---- RESULTS |
| 'a','int','Int Col' |
| 'b','struct<\n f1:array<int>,\n f2:map<string,struct<\n f1:bigint\n >>\n>','' |
| 'c','array<struct<\n f1:decimal(10,2)\n>>','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| # Change a single complex-typed column to a scalar type. |
| alter table t2 change column c c varchar(20); |
| describe t2; |
| ---- RESULTS |
| 'a','int','Int Col' |
| 'b','struct<\n f1:array<int>,\n f2:map<string,struct<\n f1:bigint\n >>\n>','' |
| 'c','varchar(20)','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| # set a comment using the 'ALTER COLUMN' syntax. |
| alter table t2 alter column a set comment 'alter comment'; |
| describe t2; |
| ---- RESULTS |
| 'a','int','alter comment' |
| 'b','struct<\n f1:array<int>,\n f2:map<string,struct<\n f1:bigint\n >>\n>','' |
| 'c','varchar(20)','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| create external table jointbl_test like functional.jointbl |
| ---- RESULTS |
| 'Table has been created.' |
| ==== |
| ---- QUERY |
| # Expect new table to be empty |
| select * from jointbl_test |
| ---- RESULTS |
| ---- TYPES |
| bigint,string,int,int |
| ==== |
| ---- QUERY |
| # change the location to point to some data |
| alter table jointbl_test set location '$FILESYSTEM_PREFIX/test-warehouse/jointbl'; |
| # Check the location is correct |
| show table stats jointbl_test; |
| ---- RESULTS |
| -1,1,regex:.*B,'NOT CACHED','NOT CACHED','TEXT','false','$NAMENODE/test-warehouse/jointbl' |
| ---- TYPES |
| bigint,bigint,string,string,string,string,string,string |
| ==== |
| ---- QUERY |
| # should get some results back now |
| select * from jointbl_test order by test_id limit 3 |
| ---- RESULTS |
| 1001,'Name1',94611,5000 |
| 1002,'Name2',94611,5000 |
| 1003,'Name3',94611,5000 |
| ---- TYPES |
| bigint,string,int,int |
| ==== |
| ---- QUERY |
| # change the location to point to some data in another file format |
| alter table jointbl_test set location '$FILESYSTEM_PREFIX/test-warehouse/jointbl_seq'; |
| show table stats jointbl_test; |
| ---- RESULTS |
| -1,1,regex:.*B,'NOT CACHED','NOT CACHED','TEXT','false','$NAMENODE/test-warehouse/jointbl_seq' |
| ---- TYPES |
| bigint,bigint,string,string,string,string,string,string |
| ==== |
| ---- QUERY |
| # change the table fileformat to match the data |
| alter table jointbl_test set fileformat sequencefile |
| ---- RESULTS |
| 'Updated table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # now the proper data should be returned |
| select * from jointbl_test order by test_id limit 3 |
| ---- RESULTS |
| 1001,'Name1',94611,5000 |
| 1002,'Name2',94611,5000 |
| 1003,'Name3',94611,5000 |
| ---- TYPES |
| bigint,string,int,int |
| ==== |
| ---- QUERY |
| # Create a partitioned table. Specify the location so we know what dirs need |
| # to be cleaned after the test finishes executing. |
| create external table t_part (i int) partitioned by (j int, s string) |
| location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/t_part_tmp'; |
| alter table t_part add partition (j=cast(2-1 as int), s='2012'); |
| alter table t_part add if not exists partition (j=1, s='2012'); |
| alter table t_part add if not exists partition (j=1, s='2012/withslash'); |
| alter table t_part add partition (j=1, s=substring('foo2013bar', 4, 8)); |
| ---- RESULTS |
| 'New partition has been added to the table.' |
| ==== |
| ---- QUERY |
| # Add another partition that points to the same location as another partition. |
| # This will cause the data to be read twice, but shouldn't result in an error. |
| alter table t_part add partition (j=100, s='same_location') |
| location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/t_part_tmp/j=1/s=2012' |
| ---- RESULTS |
| 'New partition has been added to the table.' |
| ==== |
| ---- QUERY |
| # Add another partition that points to an existing data location that does not |
| # follow the key=value directory structure. |
| alter table t_part add partition (j=101, s='different_part_dir') |
| location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/part_data/' |
| ---- RESULTS |
| 'New partition has been added to the table.' |
| ==== |
| ---- QUERY |
| insert overwrite table t_part partition(j=1, s='2012') select 2 from functional.alltypes limit 2 |
| ---- RESULTS |
| j=1/s=2012/: 2 |
| ==== |
| ---- QUERY |
| insert overwrite table t_part partition(j=1, s='2013') select 3 from functional.alltypes limit 3 |
| ---- RESULTS |
| j=1/s=2013/: 3 |
| ==== |
| ---- QUERY |
| insert overwrite table t_part partition(j=1, s='2012/withslash') |
| select 1 from functional.alltypes limit 1 |
| ---- RESULTS |
| j=1/s=2012%2Fwithslash/: 1 |
| ==== |
| ---- QUERY |
| # Refresh the table to ensure all partitions have up-to-date list of file and block |
| # metadata. |
| refresh t_part; |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| select i, j, s from t_part |
| ---- RESULTS |
| 1,1,'2012/withslash' |
| 2,1,'2012' |
| 2,1,'2012' |
| 2,100,'same_location' |
| 2,100,'same_location' |
| 3,1,'2013' |
| 3,1,'2013' |
| 3,1,'2013' |
| 1984,101,'different_part_dir' |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| alter table t_part add partition (j=NULL, s='2013') |
| ---- RESULTS |
| 'New partition has been added to the table.' |
| ==== |
| ---- QUERY |
| alter table t_part add partition (j=NULL, s=NULL) |
| ---- RESULTS |
| 'New partition has been added to the table.' |
| ==== |
| ---- QUERY |
| # Drop the partition that points to a duplication location. The data will no longer |
| # be read twice. |
| alter table t_part drop partition (j=100, s='same_location') |
| ---- RESULTS |
| 'Dropped 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| alter table t_part drop partition (j=101, s='different_part_dir') |
| ---- RESULTS |
| 'Dropped 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| insert overwrite table t_part partition(j=NULL, s=NULL) |
| select 4 from functional.alltypes limit 5 |
| ---- RESULTS |
| j=__HIVE_DEFAULT_PARTITION__/s=__HIVE_DEFAULT_PARTITION__/: 5 |
| ==== |
| ---- QUERY |
| select i, j, s from t_part |
| ---- RESULTS |
| 1,1,'2012/withslash' |
| 2,1,'2012' |
| 2,1,'2012' |
| 3,1,'2013' |
| 3,1,'2013' |
| 3,1,'2013' |
| 4,NULL,'NULL' |
| 4,NULL,'NULL' |
| 4,NULL,'NULL' |
| 4,NULL,'NULL' |
| 4,NULL,'NULL' |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| insert overwrite table t_part partition(j=NULL, s='2013') |
| select 5 from functional.alltypes limit 5 |
| ---- RESULTS |
| j=__HIVE_DEFAULT_PARTITION__/s=2013/: 5 |
| ==== |
| ---- QUERY |
| select i, j, s from t_part |
| ---- RESULTS |
| 1,1,'2012/withslash' |
| 2,1,'2012' |
| 2,1,'2012' |
| 3,1,'2013' |
| 3,1,'2013' |
| 3,1,'2013' |
| 4,NULL,'NULL' |
| 4,NULL,'NULL' |
| 4,NULL,'NULL' |
| 4,NULL,'NULL' |
| 4,NULL,'NULL' |
| 5,NULL,'2013' |
| 5,NULL,'2013' |
| 5,NULL,'2013' |
| 5,NULL,'2013' |
| 5,NULL,'2013' |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| alter table t_part drop partition (j=NULL, s=NULL) |
| ---- RESULTS |
| 'Dropped 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select i, j, s from t_part |
| ---- RESULTS |
| 1,1,'2012/withslash' |
| 2,1,'2012' |
| 2,1,'2012' |
| 3,1,'2013' |
| 3,1,'2013' |
| 3,1,'2013' |
| 5,NULL,'2013' |
| 5,NULL,'2013' |
| 5,NULL,'2013' |
| 5,NULL,'2013' |
| 5,NULL,'2013' |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| alter table t_part drop partition (j=NULL, s=trim(' 2013 ')) |
| ---- RESULTS |
| 'Dropped 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select i, j, s from t_part |
| ---- RESULTS |
| 1,1,'2012/withslash' |
| 2,1,'2012' |
| 2,1,'2012' |
| 3,1,'2013' |
| 3,1,'2013' |
| 3,1,'2013' |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| alter table t_part drop partition (j=1, s='2013') |
| ---- RESULTS |
| 'Dropped 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select i, j, s from t_part |
| ---- RESULTS |
| 1,1,'2012/withslash' |
| 2,1,'2012' |
| 2,1,'2012' |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| alter table t_part drop partition (j=1, s='2012/withslash') |
| ---- RESULTS |
| 'Dropped 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select i, j, s from t_part |
| ---- RESULTS |
| 2,1,'2012' |
| 2,1,'2012' |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| # Test that empty string as partition column maps onto NULL |
| # using static partition insert |
| insert into table t_part partition(j=2, s='') |
| select 1 from functional.alltypes limit 1 |
| ---- RESULTS |
| j=2/s=__HIVE_DEFAULT_PARTITION__/: 1 |
| ==== |
| ---- QUERY |
| # Test that empty string as partition column maps onto NULL |
| # using dynamic partition insert |
| insert into table t_part partition(j=2, s) |
| select 10, '' from functional.alltypes limit 1 |
| ---- RESULTS |
| j=2/s=__HIVE_DEFAULT_PARTITION__/: 1 |
| ==== |
| ---- QUERY |
| # Validate the previous inserts |
| select i, j, s from t_part where s is NULL |
| ---- RESULTS |
| 1,2,'NULL' |
| 10,2,'NULL' |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| # Drop default partition using empty string as key |
| alter table t_part drop partition (j=2, s='') |
| ---- RESULTS |
| 'Dropped 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Validate previous partition drop |
| select i, j, s from t_part where s is NULL |
| ---- RESULTS |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| # rename a partitioned table |
| alter table t_part rename to t_part2 |
| ---- RESULTS |
| 'Renaming was successful.' |
| ==== |
| ---- QUERY |
| # only the new table shows up |
| show tables like 't_part*' |
| ---- RESULTS |
| 't_part2' |
| ==== |
| ---- QUERY |
| # should be able to read the same data from this table |
| select i, j, s from t_part2 |
| ---- RESULTS |
| 2,1,'2012' |
| 2,1,'2012' |
| ---- TYPES |
| int,int,string |
| ==== |
| ---- QUERY |
| create external table alltypes_test like functional.alltypes; |
| alter table alltypes_test add partition(month=4, year=2009); |
| alter table alltypes_test add partition(month=5, year=2009); |
| # Table is empty |
| select int_col from alltypes_test; |
| ---- RESULTS |
| ---- TYPES |
| int |
| ==== |
| ---- QUERY |
| # Point one partition at some data (file format does not match table) |
| alter table alltypes_test partition(month=4, year=2009) |
| set location '$FILESYSTEM_PREFIX/test-warehouse/alltypes_seq_snap/year=2009/month=4' |
| ---- RESULTS |
| 'New location has been set for the specified partition.' |
| ==== |
| ---- QUERY |
| alter table alltypes_test partition(month=4, year=2009) |
| set fileformat sequencefile |
| ---- RESULTS |
| 'Updated 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select int_col, count(*) from alltypes_test |
| group by int_col order by 1 limit 100 |
| ---- RESULTS |
| 0,30 |
| 1,30 |
| 2,30 |
| 3,30 |
| 4,30 |
| 5,30 |
| 6,30 |
| 7,30 |
| 8,30 |
| 9,30 |
| ---- TYPES |
| int,bigint |
| ==== |
| ---- QUERY |
| # Point the other partition at some more data. This time in a different |
| # file format. |
| alter table alltypes_test partition(month=cast(1+4 as int), year=cast(100*20+9 as int)) |
| set location '$FILESYSTEM_PREFIX/test-warehouse/alltypes_rc/year=2009/month=5' |
| ---- RESULTS |
| 'New location has been set for the specified partition.' |
| ==== |
| ---- QUERY |
| alter table alltypes_test partition(month=cast(2+3 as int), year=2009) |
| set fileformat rcfile |
| ---- RESULTS |
| 'Updated 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select int_col, count(*) from alltypes_test |
| group by int_col order by 1 limit 100 |
| ---- RESULTS |
| 0,61 |
| 1,61 |
| 2,61 |
| 3,61 |
| 4,61 |
| 5,61 |
| 6,61 |
| 7,61 |
| 8,61 |
| 9,61 |
| ---- TYPES |
| int,bigint |
| ==== |
| ---- QUERY |
| # Show the table stats before altering. |
| show table stats alltypes_test |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| '2009','4',-1,1,regex:.+KB,'NOT CACHED','NOT CACHED','SEQUENCE_FILE','false',regex:.* |
| '2009','5',-1,1,regex:.+KB,'NOT CACHED','NOT CACHED','RC_FILE','false',regex:.* |
| 'Total','',-1,2,regex:.+KB,'0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| # Test altering the 'numRows' table property of a table. |
| alter table alltypes_test set tblproperties ('numRows'='200') |
| ---- RESULTS |
| 'Updated table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Test altering the 'numRows' table property of a partition. |
| alter table alltypes_test partition(year=2009, month=4) |
| set tblproperties ('numRows'='30', 'STATS_GENERATED_VIA_STATS_TASK'='true') |
| ---- RESULTS |
| 'Updated 1 partition(s).' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Show the table stats after altering the table and partition stats. |
| show table stats alltypes_test |
| ---- LABELS |
| YEAR, MONTH, #ROWS, #FILES, SIZE, BYTES CACHED, CACHE REPLICATION, FORMAT, INCREMENTAL STATS, LOCATION |
| ---- RESULTS |
| '2009','4',30,1,regex:.+KB,'NOT CACHED','NOT CACHED','SEQUENCE_FILE','false',regex:.* |
| '2009','5',-1,1,regex:.+KB,'NOT CACHED','NOT CACHED','RC_FILE','false',regex:.* |
| 'Total','',200,2,regex:.+KB,'0B','','','','' |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| # IMPALA-1016: Testing scanning newly added columns |
| CREATE TABLE imp1016 (string1 string) |
| ---- RESULTS |
| 'Table has been created.' |
| ==== |
| ---- QUERY |
| INSERT INTO imp1016 VALUES ('test') |
| ---- RESULTS |
| : 1 |
| ==== |
| ---- QUERY |
| ALTER TABLE imp1016 ADD COLUMNS (string2 string) |
| ---- RESULTS |
| 'New column(s) have been added to the table.' |
| ==== |
| ---- QUERY |
| DESCRIBE imp1016 |
| ---- RESULTS |
| 'string1','string','' |
| 'string2','string','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| SELECT * FROM imp1016 |
| ---- RESULTS |
| 'test','NULL' |
| ---- TYPES |
| string,string |
| ==== |
| ---- QUERY |
| SELECT string1 FROM imp1016 |
| ---- RESULTS |
| 'test' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| SELECT string2 FROM imp1016 |
| ---- RESULTS |
| 'NULL' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| SELECT COUNT(DISTINCT string1) FROM imp1016 |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| SELECT COUNT(DISTINCT string2) FROM imp1016 |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Create a larger table to test scanning newly added columns |
| CREATE TABLE imp1016Large (string1 string) |
| ---- RESULTS |
| 'Table has been created.' |
| ==== |
| ---- QUERY |
| # There is a codepath that operates on chunks of 1024 tuples, inserting |
| # more than 1024 tuples |
| INSERT INTO imp1016Large SELECT 'test' FROM functional.alltypes LIMIT 2000 |
| ---- RESULTS |
| : 2000 |
| ==== |
| ---- QUERY |
| ALTER TABLE imp1016Large ADD COLUMNS (string2 string) |
| ---- RESULTS |
| 'New column(s) have been added to the table.' |
| ==== |
| ---- QUERY |
| DESCRIBE imp1016Large |
| ---- RESULTS |
| 'string1','string','' |
| 'string2','string','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| SELECT COUNT(string2) FROM imp1016Large |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| SELECT COUNT(*), COUNT(DISTINCT string1) FROM imp1016Large |
| ---- RESULTS |
| 2000,1 |
| ---- TYPES |
| bigint,bigint |
| ==== |
| ---- QUERY |
| SELECT COUNT(*), COUNT(DISTINCT string2) FROM imp1016Large |
| ---- RESULTS |
| 2000,0 |
| ---- TYPES |
| bigint,bigint |
| ==== |
| ---- QUERY |
| # Tests that renaming a table with column stats across databases |
| # preserves table and column stats, and allows the renamed table |
| # to be dropped (IMPALA-1711/HIVE-9720). |
| create table $DATABASE.mv (x int, y string); |
| insert into $DATABASE.mv values(1, 'a'), (2, 'b'), (NULL, NULL); |
| compute stats $DATABASE.mv; |
| alter table $DATABASE.mv rename to $DATABASE2.mv2; |
| invalidate metadata $DATABASE2.mv2 |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| show tables in $DATABASE like '*mv*' |
| ---- RESULTS |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| show tables in $DATABASE2 like '*mv*' |
| ---- RESULTS |
| 'mv2' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| show table stats $DATABASE2.mv2 |
| ---- RESULTS |
| 3,1,'14B','NOT CACHED','NOT CACHED','TEXT','false',regex:.* |
| ---- TYPES |
| BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show column stats $DATABASE2.mv2 |
| ---- RESULTS |
| 'x','INT',2,1,4,4 |
| 'y','STRING',2,1,1,1 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| drop table $DATABASE2.mv2 |
| ---- RESULTS |
| 'Table has been dropped.' |
| ==== |
| ---- QUERY |
| show tables in $DATABASE2 like '*mv*' |
| ---- RESULTS |
| ---- TYPES |
| STRING |
| ==== |
| ---- HIVE_MAJOR_VERSION |
| 2 |
| ---- QUERY |
| # Tests that renaming a partitioned table with column stats across databases |
| # succeeds and preserves table and column stats, and allows the renamed table |
| # to be dropped (IMPALA-2810). |
| # |
| # Skip in Hive 3, see HIVE-21806 for details. |
| create table $DATABASE.mv (x int) partitioned by (y string); |
| insert into $DATABASE.mv partition(y='a') values(1); |
| insert into $DATABASE.mv partition(y='b') values(2); |
| insert into $DATABASE.mv partition(y=NULL) values(NULL); |
| compute stats $DATABASE.mv; |
| alter table $DATABASE.mv rename to $DATABASE2.mv2; |
| invalidate metadata $DATABASE2.mv2 |
| ---- RESULTS |
| ==== |
| ---- HIVE_MAJOR_VERSION |
| 2 |
| ---- QUERY |
| show tables in $DATABASE like '*mv*' |
| ---- RESULTS |
| ---- TYPES |
| STRING |
| ==== |
| ---- HIVE_MAJOR_VERSION |
| 2 |
| ---- QUERY |
| show tables in $DATABASE2 like '*mv*' |
| ---- RESULTS |
| 'mv2' |
| ---- TYPES |
| STRING |
| ==== |
| ---- HIVE_MAJOR_VERSION |
| 2 |
| ---- QUERY |
| show table stats $DATABASE2.mv2 |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 'NULL',1,1,'3B','NOT CACHED','NOT CACHED','TEXT','false',regex:.* |
| 'a',1,1,'2B','NOT CACHED','NOT CACHED','TEXT','false',regex:.* |
| 'b',1,1,'2B','NOT CACHED','NOT CACHED','TEXT','false',regex:.* |
| 'Total',3,3,'7B','0B','','','','' |
| ---- TYPES |
| STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- HIVE_MAJOR_VERSION |
| 2 |
| ---- QUERY |
| show column stats $DATABASE2.mv2 |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 'x','INT',2,1,4,4 |
| 'y','STRING',3,1,-1,-1 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT, BIGINT, DOUBLE |
| ==== |
| ---- HIVE_MAJOR_VERSION |
| 2 |
| ---- QUERY |
| drop table $DATABASE2.mv2 |
| ---- RESULTS |
| 'Table has been dropped.' |
| ==== |
| ---- HIVE_MAJOR_VERSION |
| 2 |
| ---- QUERY |
| show tables in $DATABASE2 like '*mv*' |
| ---- RESULTS |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| # alter with serde property lengths just within limits |
| create table $DATABASE.property_length (i int); |
| alter table $DATABASE.property_length |
| set serdeproperties( |
| 'keykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeyk' |
| = |
| 'valuevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevalue' |
| ) |
| ---- RESULTS |
| 'Updated table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # alter with property lengths just within limits |
| alter table $DATABASE.property_length |
| set tblproperties( |
| 'keykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeykeyk' |
| = |
| 'valuevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevaluevalue' |
| ) |
| ---- RESULTS |
| 'Updated table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| -- Change table location after a partition has been created in the original table location |
| create table $DATABASE.change_loc (s string) partitioned by (i int); |
| insert into $DATABASE.change_loc partition (i=1) values ('a'); |
| alter table $DATABASE.change_loc |
| set location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/garbage'; |
| select * from $DATABASE.change_loc; |
| ---- RESULTS |
| 'a',1 |
| ---- TYPES |
| STRING,INT |
| ==== |
| ---- QUERY |
| -- ALTER TABLE .. SET LOCATION works when setting the location of a partition to be the |
| -- the same as a partition of a different table with a different number of clustering |
| -- columns. |
| create table $DATABASE.table_one_part (s string) partitioned by (i int); |
| create table $DATABASE.table_many_part (s string) partitioned by |
| (i0 int, i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8 int, i9 int); |
| insert into $DATABASE.table_one_part partition (i=0) values ('a'); |
| insert into $DATABASE.table_one_part partition (i=1) values ('b'); |
| insert into $DATABASE.table_many_part partition |
| (i0=0, i1=1, i2=2, i3=3, i4=4, i5=5, i6=6, i7=7, i8=8, i9=9) values ('c'); |
| insert into $DATABASE.table_many_part partition |
| (i0=10, i1=11, i2=12, i3=13, i4=14, i5=15, i6=16, i7=17, i8=18, i9=19) values ('d'); |
| alter table $DATABASE.table_one_part partition (i=1) |
| set location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/table_many_part/i0=10/i1=11/i2=12/i3=13/i4=14/i5=15/i6=16/i7=17/i8=18/i9=19'; |
| alter table $DATABASE.table_many_part partition |
| (i0=10, i1=11, i2=12, i3=13, i4=14, i5=15, i6=16, i7=17, i8=18, i9=19) |
| set location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/table_one_part/i=0/'; |
| select i,s from $DATABASE.table_one_part order by i; |
| ---- RESULTS |
| 0,'a' |
| 1,'d' |
| ---- TYPES |
| INT,STRING |
| ==== |
| ---- QUERY |
| select i0, i1, i2, i3, i4, i5, i6, i7, i8, i9, s |
| from $DATABASE.table_many_part order by i0; |
| ---- RESULTS |
| 0,1,2,3,4,5,6,7,8,9,'c' |
| 10,11,12,13,14,15,16,17,18,19,'a' |
| ---- TYPES |
| INT,INT,INT,INT,INT,INT,INT,INT,INT,INT,STRING |
| ==== |
| ---- QUERY |
| show partitions $DATABASE.table_one_part |
| ---- RESULTS |
| '0',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_one_part/i=0 |
| '1',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_many_part/i0=10/i1=11/i2=12/i3=13/i4=14/i5=15/i6=16/i7=17/i8=18/i9=19 |
| 'Total',-1,2,regex:.+,regex:.+,'','','','' |
| ---- TYPES |
| STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show partitions $DATABASE.table_many_part |
| ---- RESULTS |
| '0','1','2','3','4','5','6','7','8','9',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_many_part/i0=0/i1=1/i2=2/i3=3/i4=4/i5=5/i6=6/i7=7/i8=8/i9=9 |
| '10','11','12','13','14','15','16','17','18','19',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_one_part/i=0 |
| 'Total','','','','','','','','','',-1,2,regex:.+,regex:.+,'','','','' |
| ---- TYPES |
| STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| -- Refreshing the tables does not create invalid partition locations |
| refresh $DATABASE.table_one_part; |
| refresh $DATABASE.table_many_part; |
| select i,s from $DATABASE.table_one_part order by i; |
| ---- RESULTS |
| 0,'a' |
| 1,'d' |
| ---- TYPES |
| INT,STRING |
| ==== |
| ---- QUERY |
| select i0, i1, i2, i3, i4, i5, i6, i7, i8, i9, s |
| from $DATABASE.table_many_part order by i0; |
| ---- RESULTS |
| 0,1,2,3,4,5,6,7,8,9,'c' |
| 10,11,12,13,14,15,16,17,18,19,'a' |
| ---- TYPES |
| INT,INT,INT,INT,INT,INT,INT,INT,INT,INT,STRING |
| ==== |
| ---- QUERY |
| show partitions $DATABASE.table_one_part |
| ---- RESULTS |
| '0',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_one_part/i=0 |
| '1',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_many_part/i0=10/i1=11/i2=12/i3=13/i4=14/i5=15/i6=16/i7=17/i8=18/i9=19 |
| 'Total',-1,2,regex:.+,regex:.+,'','','','' |
| ---- TYPES |
| STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show partitions $DATABASE.table_many_part |
| ---- RESULTS |
| '0','1','2','3','4','5','6','7','8','9',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_many_part/i0=0/i1=1/i2=2/i3=3/i4=4/i5=5/i6=6/i7=7/i8=8/i9=9 |
| '10','11','12','13','14','15','16','17','18','19',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_one_part/i=0 |
| 'Total','','','','','','','','','',-1,2,regex:.+,regex:.+,'','','','' |
| ---- TYPES |
| STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| -- Altering the table location does not alter the partition locations |
| alter table $DATABASE.table_one_part set location '$FILESYSTEM_PREFIX/garbage/'; |
| alter table $DATABASE.table_many_part set location '$FILESYSTEM_PREFIX/garbage/'; |
| select i,s from $DATABASE.table_one_part order by i; |
| ---- RESULTS |
| 0,'a' |
| 1,'d' |
| ---- TYPES |
| INT,STRING |
| ==== |
| ---- QUERY |
| select i0, i1, i2, i3, i4, i5, i6, i7, i8, i9, s |
| from $DATABASE.table_many_part order by i0; |
| ---- RESULTS |
| 0,1,2,3,4,5,6,7,8,9,'c' |
| 10,11,12,13,14,15,16,17,18,19,'a' |
| ---- TYPES |
| INT,INT,INT,INT,INT,INT,INT,INT,INT,INT,STRING |
| ==== |
| ---- QUERY |
| show partitions $DATABASE.table_one_part |
| ---- RESULTS |
| '0',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_one_part/i=0 |
| '1',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_many_part/i0=10/i1=11/i2=12/i3=13/i4=14/i5=15/i6=16/i7=17/i8=18/i9=19 |
| 'Total',-1,2,regex:.+,regex:.+,'','','','' |
| ---- TYPES |
| STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| show partitions $DATABASE.table_many_part |
| ---- RESULTS |
| '0','1','2','3','4','5','6','7','8','9',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_many_part/i0=0/i1=1/i2=2/i3=3/i4=4/i5=5/i6=6/i7=7/i8=8/i9=9 |
| '10','11','12','13','14','15','16','17','18','19',-1,1,regex:.+,regex:.+,regex:.+,regex:.+,regex:.+,regex:.*/test-warehouse/$DATABASE.db/table_one_part/i=0 |
| 'Total','','','','','','','','','',-1,2,regex:.+,regex:.+,'','','','' |
| ---- TYPES |
| STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| # IMPALA-1740: Test setting the skip.header.line.count tblproperty |
| create table i1740_alter_1 (i1 integer); |
| alter table i1740_alter_1 set tblproperties ('skip.header.line.count'='2'); |
| ---- RESULTS |
| 'Updated table.' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # IMPALA-1740: Test setting the skip.header.line.count tblproperty |
| create table i1740_alter_2 (i1 integer); |
| alter table i1740_alter_2 set tblproperties ('skip.header.line.count'='-1'); |
| ---- CATCH |
| Invalid value for table property skip.header.line.count: |
| ==== |
| ---- QUERY |
| # IMPALA-1740: Test setting the skip.header.line.count tblproperty |
| create table i1740_alter_3 (i1 integer); |
| alter table i1740_alter_3 set tblproperties ('skip.header.line.count'='foo'); |
| ---- CATCH |
| Invalid value for table property skip.header.line.count: |
| ==== |
| ---- QUERY |
| # IMPALA-1740: Test setting the skip.header.line.count tblproperty |
| create table i1740_alter_4 (i1 integer) stored as parquet; |
| alter table i1740_alter_4 set tblproperties ('skip.header.line.count'='2'); |
| ==== |
| ---- QUERY |
| # IMPALA-4155: Verify that the default partition is updated by the alter table statement |
| create table i4155_alter (c1 int) |
| partitioned by (p1 string) row format delimited fields terminated by ','; |
| alter table i4155_alter set fileformat PARQUET; |
| insert into i4155_alter partition(p1="abc") values (1); |
| select * from i4155_alter; |
| ---- RESULTS |
| 1,'abc' |
| ---- TYPES |
| INT, STRING |
| ==== |
| ---- QUERY |
| # IMPALA-1670: Support adding multiple partitions in ALTER TABLE ADD PARTITION |
| create table i1670A_alter (s string) partitioned by (i integer); |
| alter table i1670A_alter add |
| partition (i=1) location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/i1670A_alter/i1' |
| partition (i=2) location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/i1670A_alter/i2' |
| partition (i=3) uncached; |
| show partitions i1670A_alter; |
| ---- RESULTS |
| '1',-1,0,'0B','NOT CACHED','NOT CACHED','TEXT','false',regex:.*/i1 |
| '2',-1,0,'0B','NOT CACHED','NOT CACHED','TEXT','false',regex:.*/i2 |
| '3',-1,0,'0B','NOT CACHED','NOT CACHED','TEXT','false',regex:.*/i=3 |
| 'Total',-1,0,'0B','0B','','','','' |
| ---- TYPES |
| STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| # IMPALA-1670: After INVALIDATE METADATA Impala can access previously added partitions and |
| # partition data. |
| create table i1670A_alter_1 (a int) partitioned by (x int); |
| alter table i1670A_alter_1 add partition (x=1) |
| partition (x=2) uncached |
| partition (x=3) location '$FILESYSTEM_PREFIX/test-warehouse/$DATABASE.db/i1670A_alter_1/x3'; |
| insert into i1670A_alter_1 partition(x=1) values (1), (2), (3); |
| insert into i1670A_alter_1 partition(x=2) values (1), (2), (3), (4); |
| insert into i1670A_alter_1 partition(x=3) values (1); |
| invalidate metadata i1670A_alter_1; |
| ==== |
| ---- QUERY |
| show partitions i1670A_alter_1; |
| ---- RESULTS |
| '1',-1,1,regex:.*,'NOT CACHED','NOT CACHED','TEXT','false',regex:.*/x=1 |
| '2',-1,1,regex:.*,'NOT CACHED','NOT CACHED','TEXT','false',regex:.*/x=2 |
| '3',-1,1,regex:.*,'NOT CACHED','NOT CACHED','TEXT','false',regex:.*/x3 |
| 'Total',-1,3,regex:.*,'0B','','','','' |
| ---- TYPES |
| STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING |
| ==== |
| ---- QUERY |
| select x, a from i1670A_alter_1 order by x, a; |
| ---- RESULTS |
| 1,1 |
| 1,2 |
| 1,3 |
| 2,1 |
| 2,2 |
| 2,3 |
| 2,4 |
| 3,1 |
| ---- TYPES |
| INT, INT |
| ==== |
| ---- QUERY |
| create table insert_data (i int, d double, f float, b boolean); |
| insert into insert_data values (1, 2, 3, false), (4, 5, 6, true); |
| ==== |
| ---- QUERY |
| create table insert_sorted (i int, d double, f float, b boolean); |
| ==== |
| ---- QUERY |
| # Test setting the sort.columns property |
| alter table insert_sorted sort by(i, d); |
| describe formatted insert_sorted; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ','i,d ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test inserting after alter table |
| insert into table insert_sorted select i, d, f, b from insert_data; |
| ---- RUNTIME_PROFILE |
| row_regex: .*order by: i ASC NULLS LAST, d ASC NULLS LAST |
| ==== |
| ---- QUERY |
| # Test selection after alter table |
| select count(*) from insert_sorted; |
| ---- RESULTS |
| 2 |
| ==== |
| ---- QUERY |
| # Test altering the sort.columns property |
| alter table insert_sorted sort by(b, d, f); |
| describe formatted insert_sorted; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ','b,d,f ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test inserting after alter table |
| insert into table insert_sorted select i, d, f, b from insert_data; |
| ---- RUNTIME_PROFILE |
| row_regex: .*order by: b ASC NULLS LAST, d ASC NULLS LAST, f ASC NULLS LAST |
| ==== |
| ---- QUERY |
| # Test selection after alter table |
| select count(*) from insert_sorted; |
| ---- RESULTS |
| 4 |
| ==== |
| ---- QUERY |
| # Test renaming a column in the sort by list. |
| alter table insert_sorted change d e double; |
| describe formatted insert_sorted; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ','b,e,f ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test inserting after alter table |
| insert into table insert_sorted select i, d, f, b from insert_data; |
| ---- RUNTIME_PROFILE |
| row_regex: .*order by: b ASC NULLS LAST, d ASC NULLS LAST, f ASC NULLS LAST |
| ==== |
| ---- QUERY |
| # Test selection after alter table |
| select count(*) from insert_sorted; |
| ---- RESULTS |
| 6 |
| ==== |
| ---- QUERY |
| # Test replacing the column list, including a column in the sort by list. |
| alter table insert_sorted replace columns (i bigint, e decimal(12,2), f boolean); |
| describe formatted insert_sorted; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ','e,f ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test inserting after alter table |
| insert into table insert_sorted select i, cast(d as decimal(12,2)), b from insert_data; |
| ---- RUNTIME_PROFILE |
| row_regex: .*order by: CAST\(d AS DECIMAL\(12,2\)\) ASC NULLS LAST, b ASC NULLS LAST |
| ==== |
| ---- QUERY |
| # Test selection after alter table |
| select count(*) from insert_sorted; |
| ---- RESULTS |
| 8 |
| ==== |
| ---- QUERY |
| # Test dropping a column in the sort by list |
| alter table insert_sorted drop column f; |
| describe formatted insert_sorted; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ','e ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test that a dropped column cannot be added as a sort column |
| alter table insert_sorted sort by(f); |
| ---- CATCH |
| AnalysisException: Could not find SORT BY column 'f' in table. |
| ==== |
| ---- QUERY |
| # Test that erroneous query didn't change sort columns |
| describe formatted insert_sorted; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ','e ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test inserting after alter table |
| insert into table insert_sorted select i, cast(d as decimal(12,2)) from insert_data; |
| ---- RUNTIME_PROFILE |
| row_regex: .*order by: CAST\(d AS DECIMAL\(12,2\)\) ASC NULLS LAST |
| ==== |
| ---- QUERY |
| # Test selection after alter table |
| select count(*) from insert_sorted; |
| ---- RESULTS |
| 10 |
| ==== |
| ---- QUERY |
| # Test removing the sort.columns property |
| alter table insert_sorted sort by(); |
| describe formatted insert_sorted; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ',' ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test inserting after alter table |
| insert into table insert_sorted select i, cast(d as decimal(12,2)) from insert_data; |
| ---- RUNTIME_PROFILE |
| aggregation(SUM, InitialRunsCreated): 0 |
| ==== |
| ---- QUERY |
| # Test selection after alter table |
| select count(*) from insert_sorted; |
| ---- RESULTS |
| 12 |
| ==== |
| ---- QUERY |
| create table insert_sorted_partitioned (i int, d double, f float, b boolean) partitioned by (p int) sort by (i); |
| ==== |
| ---- QUERY |
| # Test removing all sort columns. |
| alter table insert_sorted_partitioned sort by(); |
| describe formatted insert_sorted_partitioned; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ',' ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test inserting after alter table |
| insert into table insert_sorted_partitioned partition (p=1) select i, d, f, b from insert_data; |
| ---- RUNTIME_PROFILE |
| aggregation(SUM, InitialRunsCreated): 0 |
| ==== |
| ---- QUERY |
| # Test selection after alter table |
| select count(*) from insert_sorted_partitioned; |
| ---- RESULTS |
| 2 |
| ==== |
| ---- QUERY |
| # Re-add a sort column. |
| alter table insert_sorted_partitioned sort by(i); |
| describe formatted insert_sorted_partitioned; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ','i ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test inserting after alter table |
| insert into table insert_sorted_partitioned partition (p=1) select i, d, f, b from insert_data; |
| ---- RUNTIME_PROFILE |
| row_regex: .*order by: i ASC NULLS LAST |
| ==== |
| ---- QUERY |
| # Test selection after alter table |
| select count(*) from insert_sorted_partitioned; |
| ---- RESULTS |
| 4 |
| ==== |
| ---- QUERY |
| # Test dropping the sort column |
| alter table insert_sorted_partitioned drop column i; |
| describe formatted insert_sorted_partitioned; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| '','sort.columns ',' ' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test inserting after alter table |
| insert into table insert_sorted_partitioned partition (p=1) select d, f, b from insert_data; |
| ---- RUNTIME_PROFILE |
| aggregation(SUM, InitialRunsCreated): 0 |
| ==== |
| ---- QUERY |
| # Test selection after alter table |
| select count(*) from insert_sorted_partitioned; |
| ---- RESULTS |
| 6 |
| ==== |
| ---- QUERY |
| # IMPALA-4323: Test alter row format statement. Ensure alter statement updates metadata. |
| # Spaces after text will become field delimiters after the alter statement. |
| # Tildes at the end of the line will become line delimiters after the second alter statement. |
| create table del_table (c1 string, c2 string, c3 string) |
| row format delimited fields terminated by '\002' lines terminated by '\001' stored as textfile; |
| insert into del_table values ("the ", "quick ", "brown~"), |
| ("fox ","jumped ","over~"); |
| select * from del_table order by c3; |
| ---- RESULTS |
| 'the ','quick ','brown~' |
| 'fox ','jumped ','over~' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test select after alter to ensure field delimiters change to spaces and |
| # the line delimiters remain '\001'. |
| alter table del_table set row format delimited fields terminated by ' '; |
| select * from del_table order by c3; |
| ---- RESULTS |
| 'the','\x02quick','\x02brown~' |
| 'fox','\x02jumped','\x02over~' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test select after alter to ensure line delimiters change. |
| # We end up with an extra record because the new delimiter is |
| # before the original delimiter which now becomes data. |
| alter table del_table set row format delimited fields terminated by '\002' |
| lines terminated by '~'; |
| select * from del_table order by c3; |
| ---- RESULTS |
| '\x01','NULL','' |
| 'the ','quick ','brown' |
| '\x01fox ','jumped ','over' |
| ---- TYPES |
| STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # IMPALA-4323: Test alter row format statement with partitions. |
| # Ensure alter statement updates metadata. |
| create table del_table_part (c1 string, c2 string, c3 string) partitioned by (c0 int) |
| row format delimited fields terminated by '\002' lines terminated by '\001' stored as textfile; |
| insert into del_table_part partition (c0=0) values |
| ("the ", "quick ", "brown"); |
| insert into del_table_part partition (c0=1) values |
| ("fox ","jumped ","over"); |
| select * from del_table_part order by c0; |
| ---- RESULTS |
| 'the ','quick ','brown',0 |
| 'fox ','jumped ','over',1 |
| ---- TYPES |
| STRING,STRING,STRING,INT |
| ==== |
| ---- QUERY |
| # Test select after alter to ensure only one partition changes. |
| alter table del_table_part partition (c0=1) set row format delimited fields terminated by ' '; |
| select * from del_table_part order by c0; |
| ---- RESULTS |
| 'the ','quick ','brown',0 |
| 'fox','\x02jumped','\x02over',1 |
| ---- TYPES |
| STRING,STRING,STRING,INT |
| ==== |
| ---- QUERY |
| # Test select after alter table to ensure no partition changes. |
| alter table del_table_part set row format delimited fields terminated by '_'; |
| select * from del_table_part order by c0; |
| ---- RESULTS |
| 'the ','quick ','brown',0 |
| 'fox','\x02jumped','\x02over',1 |
| ---- TYPES |
| STRING,STRING,STRING,INT |
| ==== |
| ---- QUERY |
| # Ensure new partitions use table-level format |
| # First we set a terminator, insert some data in a new partition that should use it |
| # then change the terminator so the original will show in the results. |
| alter table del_table_part set row format delimited fields terminated by '_'; |
| insert into del_table_part partition (c0=2) values |
| ('the','\002lazy','\002dog'); |
| alter table del_table_part partition (c0=2) set row format delimited fields terminated by '\002'; |
| select * from del_table_part order by c0; |
| ---- RESULTS |
| 'the ','quick ','brown',0 |
| 'fox','\x02jumped','\x02over',1 |
| 'the_','lazy_','dog',2 |
| ---- TYPES |
| STRING,STRING,STRING,INT |
| ==== |
