Alter Table
The ALTER TABLE statement changes the schema or properties of a table.
RENAME
ALTER TABLE RENAME statement changes the table name of an existing table in the database.
Syntax
ALTER TABLE [db_name.]old_table_name RENAME TO [db_name.]new_table_name
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
Parameters
old_table_name Name of an existing table.db_name Name of the existing database.new_table_name New name using which the table has to be renamed.partition_spec Partition to be renamed.
ADD COLUMNS
ALTER TABLE ADD COLUMNS statement adds mentioned columns to an existing table.
Syntax
ALTER TABLE table_name ADD COLUMNS (col_spec[, col_spec ...])
Parameters
table_name The name of an existing table. COLUMNS (col_spec) Specifies the columns to be added to be renamed.
SET AND UNSET
Set table properties
ALTER TABLE SET command is used for setting the table properties. If a particular property was already set, this overrides the old value with the new one.
ALTER TABLE UNSET is used to drop the table property.
Syntax
--Set Table Properties
ALTER TABLE table_name SET TBLPROPERTIES (key1=val1, key2=val2, ...)
--Unset Table Properties
ALTER TABLE table_name UNSET TBLPROPERTIES [IF EXISTS] (key1, key2, ...)
Examples
--Set Table Properties
--RENAME table
DESC student;
+--------------------------+------------+----------+--+
| col_name | data_type | comment |
+--------------------------+------------+----------+--+
| name | string | NULL |
| rollno | int | NULL |
| age | int | NULL |
| # Partition Information | | |
| # col_name | data_type | comment |
| age | int | NULL |
+--------------------------+------------+----------+--+
ALTER TABLE Student RENAME TO StudentInfo;
--After Renaming the table
DESC StudentInfo;
+--------------------------+------------+----------+--+
| col_name | data_type | comment |
+--------------------------+------------+----------+--+
| name | string | NULL |
| rollno | int | NULL |
| age | int | NULL |
| # Partition Information | | |
| # col_name | data_type | comment |
| age | int | NULL |
+--------------------------+------------+----------+--+
--RENAME partition
SHOW PARTITIONS StudentInfo;
+------------+--+
| partition |
+------------+--+
| age=10 |
| age=11 |
| age=12 |
+------------+--+
ALTER TABLE default.StudentInfo PARTITION (age='10') RENAME TO PARTITION (age='15');
--After renaming Partition
SHOW PARTITIONS StudentInfo;
+------------+--+
| partition |
+------------+--+
| age=11 |
| age=12 |
| age=15 |
+------------+--+
-- Add new column to a table
DESC StudentInfo;
+--------------------------+------------+----------+--+
| col_name | data_type | comment |
+--------------------------+------------+----------+--+
| name | string | NULL |
| rollno | int | NULL |
| age | int | NULL |
| # Partition Information | | |
| # col_name | data_type | comment |
| age | int | NULL |
+--------------------------+------------+----------+
ALTER TABLE StudentInfo ADD columns (LastName string, DOB timestamp);
--After Adding New columns to the table
DESC StudentInfo;
+--------------------------+------------+----------+--+
| col_name | data_type | comment |
+--------------------------+------------+----------+--+
| name | string | NULL |
| rollno | int | NULL |
| LastName | string | NULL |
| DOB | timestamp | NULL |
| age | int | NULL |
| # Partition Information | | |
| # col_name | data_type | comment |
| age | int | NULL |
+--------------------------+------------+----------+--+
--SET TABLE PROPERTIES
ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('winner' = 'loser')
--DROP TABLE PROPERTIES
ALTER TABLE dbx.tab1 UNSET TBLPROPERTIES ('winner')