ALTER TABLE

2020-01-21

Description

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 table_identifier RENAME TO table_identifier

ALTER TABLE table_identifier partition_spec RENAME TO partition_spec

Parameters

table_identifier
Specifies a table name, which may be optionally qualified with a database name.

Syntax: [ database_name. ] table_name
partition_spec
Partition to be renamed.

Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

ADD COLUMNS

ALTER TABLE ADD COLUMNS statement adds mentioned columns to an existing table.

Syntax

ALTER TABLE table_identifier ADD COLUMNS ( col_spec [ , col_spec ... ] )

Parameters

table_identifier
Specifies a table name, which may be optionally qualified with a database name.

Syntax: [ database_name. ] table_name
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_identifier SET TBLPROPERTIES ( key1 = val1, key2 = val2, ... )

--Unset Table Properties ALTER TABLE table_identifier UNSET TBLPROPERTIES [ IF EXISTS ]( key1, key2, ... )

SET SERDE

ALTER TABLE SET command is used for setting the SERDE or SERDE properties in Hive tables. If a particular property was already set,
this overrides the old value with the new one.

Syntax

--Set SERDE Properties ALTER TABLE table_identifier [ partition_spec ] SET SERDEPROPERTIES ( key1 = val1, key2 = val2, ... )

ALTER TABLE table_identifier [ partition_spec ] SET SERDE serde_class_name [ WITH SERDEPROPERTIES ( key1 = val1, key2 = val2, ... ) ]

SET LOCATION And SET FILE FORMAT

ALTER TABLE SET command can also be used for changing the file location and file format for existing tables.

Syntax

--Changing File Format ALTER TABLE table_identifier [ partition_spec ] SET FILEFORMAT file_format

--Changing File Location ALTER TABLE table_identifier [ partition_spec ] SET LOCATION 'new_location'

Parameters

table_identifier
Specifies a table name, which may be optionally qualified with a database name.

Syntax: [ database_name. ] table_name
partition_spec
Specifies the partition on which the property has to be set.

Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )
SERDEPROPERTIES ( key1 = val1, key2 = val2, ... )
Specifies the SERDE properties to be set.

Examples

--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 | +--------------------------+------------+----------+--+

--Change the fileformat ALTER TABLE loc_orc SET fileformat orc;

ALTER TABLE p1 partition (month=2, day=2) SET fileformat parquet;

--Change the file Location ALTER TABLE dbx.tab1 PARTITION (a='1', b='2') SET LOCATION '/path/to/part/ways'

-- SET SERDE/ SERDE Properties ALTER TABLE test_tab SET SERDE 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe';

ALTER TABLE dbx.tab1 SET SERDE 'org.apache.madoop' WITH SERDEPROPERTIES ('k' = 'v', 'kay' = 'vee')

--SET TABLE PROPERTIES ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('winner' = 'loser')

--DROP TABLE PROPERTIES ALTER TABLE dbx.tab1 UNSET TBLPROPERTIES ('winner')