Alter Table or View

Rename table or view

ALTER [TABLE|VIEW] [db_name.]table_name RENAME TO [db_name.]new_table_name

Rename an existing table or view. If the destination table name already exists, an exception is thrown. This operation does not support moving tables across databases.

Set table or view properties

ALTER [TABLE|VIEW] table_name SET TBLPROPERTIES (key1=val1, key2=val2, ...)

Set the properties of an existing table or view. If a particular property was already set, this overrides the old value with the new one.

Drop table or view properties

ALTER (TABLE|VIEW) table_name UNSET TBLPROPERTIES
    [IF EXISTS] (key1, key2, ...)

Drop one or more properties of an existing table or view. If a specified property does not exist, an exception is thrown.

IF EXISTS
If a specified property does not exist, nothing will happen.

Set SerDe or SerDe properties

ALTER TABLE table_name [PARTITION part_spec] SET SERDE serde
    [WITH SERDEPROPERTIES (key1=val1, key2=val2, ...)]

ALTER TABLE table_name [PARTITION part_spec]
    SET SERDEPROPERTIES (key1=val1, key2=val2, ...)

part_spec:
    : (part_col_name1=val1, part_col_name2=val2, ...)

Set the SerDe or the SerDe properties of a table or partition. If a specified SerDe property was already set, this overrides the old value with the new one. Setting the SerDe is allowed only for tables created using the Hive format.

Databricks Delta Schema Constructs

Databricks Delta supports additional constructs for modifying table schema:

For add, change, and replace column examples, see Explicitly update schema.

Add columns

ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment] [FIRST|AFTER colA_name], ...)

ALTER TABLE table_name ADD COLUMNS (col_name.nested_col_name data_type [COMMENT col_comment] [FIRST|AFTER colA_name], ...)

Add columns to an existing table. It supports adding nested column. If a column with the same name already exists in the table or the same nested struct, an exception is thrown.

Change columns

ALTER TABLE table_name CHANGE [COLUMN] col_name col_name data_type [COMMENT col_comment] [FIRST|AFTER colA_name]

ALTER TABLE table_name CHANGE [COLUMN] col_name.nested_col_name col_name data_type [COMMENT col_comment] [FIRST|AFTER colA_name]

Change a column definition of an existing table. You can change the comment of the column and reorder columns.

Replace columns

ALTER TABLE table_name REPLACE COLUMNS (col_name1 col_type1 [COMMENT col_comment1], ...)

Replace the column definitions of an existing table. It supports changing the comments of columns, adding columns, and reordering columns. If specified column definitions are not compatible with the existing definitions, an exception is thrown.