更改表或视图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.

对于托管表,重命名表将移动表位置;对于非托管(外部)表,重命名表不会移动表位置。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 Managed and unmanaged tables.

设置表或视图属性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.

备注

  • 属性名称区分大小写。Property names are case sensitive. 如果有 key1,并在稍后设置 Key1,则将创建新的表属性。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.

设置 SerDe 或 SerDe 属性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, ...)

设置表或分区的 SerDe 或 SerDe 属性。Set the SerDe or the SerDe properties of a table or partition. 如果已设置指定的 SerDe 属性,这将用新属性覆盖旧值。If a specified SerDe property was already set, this overrides the old value with the new one. 仅允许使用 Hive 格式创建的表设置 SerDe。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 架构构造Delta Lake schema constructs

Delta Lake 还支持其他可修改表架构的构造:添加、更改和替换列。Delta Lake supports additional constructs for modifying table schema: add, change, and replace columns.

有关添加、更改和替换列示例的详细说明,请参阅显式更新架构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 (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.

备注

在 Databricks Runtime 7.0 及更高版本中可用。Available in Databricks Runtime 7.0 and above.

更改列(Hive 语法)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.

备注

在 Databricks Runtime 7.0 及更高版本中你无法使用 CHANGE COLUMNIn Databricks Runtime 7.0 and above you cannot use CHANGE COLUMN:

  • 要更改复杂数据类型(如结构)的内容。To change the contents of complex data types such as structs. 改用 ADD COLUMNS 来将新列添加到嵌套字段,或使用 ALTER COLUMN 更改嵌套列的属性。Instead use ADD COLUMNS to add new columns to nested fields, or ALTER COLUMN to change the properties of a nested column.
  • 若要放宽 Delta 表中列的为空性。To relax the nullability of a column in a Delta table. 改用 ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULLInstead 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.