Alter Table or View

Important

This documentation has been retired and might not be updated. The products, services, or technologies mentioned in this content are no longer supported. See ALTER TABLE.

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.

Note

If you use AWS Glue Data Catalog as the metastore, RENAME is not supported.

For managed tables, renaming a table moves the table location; for unmanaged (external) tables, renaming a table does not move the table location.

For further information on managed versus unmanaged (external) tables, see Data objects in the Databricks Lakehouse.

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.

Note

  • Property names are case sensitive. If you have key1 and then later set Key1, a new table property is created.

  • To view table properties, run:

    DESCRIBE EXTENDED table_name

Set a table comment

To set a table comment, run:

ALTER TABLE table_name SET TBLPROPERTIES ('comment' = 'A table comment.')

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.

Assign owner 

ALTER (TABLE|VIEW) object-name OWNER TO `user_name@user_domain.com`

Assign an owner to the table or view.

Delta Lake schema constructs

Delta Lake supports additional constructs for modifying table schema: add, change, and replace columns.

For add, change, and replace column examples, see Update Delta Lake table 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 (ALTER|CHANGE) [COLUMN] alterColumnAction

ALTER TABLE table_name (ALTER|CHANGE) [COLUMN] alterColumnAction

alterColumnAction:
    : TYPE dataType
    : [COMMENT col_comment]
    : [FIRST|AFTER colA_name]
    : (SET | DROP) NOT NULL

Change a column definition of an existing table. You can change the data type, comment, nullability of a column or reorder columns.

Note

Available in Databricks Runtime 7.0 and above.

Change columns (Hive syntax) 

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.

Note

In Databricks Runtime 7.0 and above you cannot use CHANGE COLUMN:

  • To change the contents of complex data types such as structs. Instead use ADD COLUMNS to add new columns to nested fields, or ALTER COLUMN to change the properties of a nested column.

  • To relax the nullability of a column in a Delta table. Instead use ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL.

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.