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

增量 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. 您可以更改列的数据类型、注释、可为 null 的列或对列重新排序。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.
  • 若要放宽增量表中的列的为空性。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.