Alter Table

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')

Did this page help you?