修改表

描述

ALTER TABLE 语句更改表的结构或属性。

重命名

ALTER TABLE RENAME TO 语句更改数据库中现有表的名称。表重命名命令不能用于将表在数据库之间移动，只能在同一数据库中重命名表。

如果表被缓存，命令将清除表的缓存数据。下次访问表时，缓存将被延迟填充。此外：

表重命名命令会取消所有依赖于该表的对象的缓存，例如指向该表的视图。依赖对象应该显式重新缓存。
分区重命名命令会清除所有表依赖对象的缓存，同时保持它们为已缓存状态。因此，当下次访问时它们的缓存将被懒加载。

语法

      ALTER TABLE table_identifier RENAME TO table_identifier
ALTER TABLE table_identifier partition_spec RENAME TO partition_spec

参数

table_identifier

指定一个表名，可以选择性地带有数据库名称。

语法: [ database_name. ] table_name
partition_spec

要重命名的分区。注意，可以在分区规范中使用类型文字（例如，date’2019-01-02’）。

语法: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

添加列

ALTER TABLE ADD COLUMNS 语句将提到的列添加到现有表中。

语法

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

     

参数

table_identifier

指定一个表名，表名可以选择性地以数据库名进行限定。

语法: [ database_name. ] table_name
COLUMNS ( col_spec )

指定要添加的列。

删除列

ALTER TABLE DROP COLUMNS 语句从现有表中删除提到的列。注意，此语句仅支持 v2 表。

语法

      ALTER TABLE table_identifier DROP { COLUMN | COLUMNS } [ ( ] col_name [ , ... ] [ ) ]

     

参数

table_identifier

指定一个表名，可以选择性地用数据库名进行限定。

语法: [ database_name. ] table_name
col_name

指定列的名称。

重命名列

ALTER TABLE RENAME COLUMN 语句用于更改现有表的列名。注意，此语句仅在v2表中受支持。

语法

      ALTER TABLE table_identifier RENAME COLUMN col_name TO col_name

     

参数

table_identifier

指定表名，可以选择性地用数据库名来限定。

语法： [ database_name. ] table_name
col_name

指定列的名称。

修改或变更列

ALTER TABLE ALTER COLUMN 或 ALTER TABLE CHANGE COLUMN 语句更改列的定义。

语法

      ALTER TABLE table_identifier { ALTER | CHANGE } [ COLUMN ] col_name alterColumnAction

     

参数

table_identifier

指定一个表名，表名可以选择性地带上数据库名。

语法： [ database_name. ] table_name
col_name

指定列的名称。
alterColumnAction

更改列的定义。

替换列

ALTER TABLE REPLACE COLUMNS 语句删除所有现有列并添加新的列集。注意，此语句仅支持 v2 表。

语法

      ALTER TABLE table_identifier [ partition_spec ] REPLACE COLUMNS
[ ( ] qualified_col_type_with_position_list [ ) ]

参数

table_identifier

指定一个表名，可以选择性地使用数据库名称进行限定。

语法: [ database_name. ] table_name
partition_spec

要替换的分区。注意，可以在分区规格中使用一个类型字面量（例如，date’2019-01-02’）。

语法: PARTITION ( partition_col_name = partition_col_val [ , ... ] )
qualified_col_type_with_position_list

要添加的列的列表

语法: col_name col_type [ col_comment ] [ col_position ] [ , ... ]

添加和删除分区

添加分区

ALTER TABLE ADD 语句将分区添加到分区表。

如果表被缓存，该命令将清除表及所有引用它的依赖项的缓存数据。缓存将在下次访问表或依赖项时懒惰地填充。

语法

      ALTER TABLE table_identifier ADD [IF NOT EXISTS]
( partition_spec [ partition_spec ... ] )

参数

table_identifier

指定一个表名，可以选择性地以数据库名进行限定。

语法: [ database_name. ] table_name
partition_spec

要添加的分区。请注意，可以在分区规范中使用类型字面量（例如，date’2019-01-02’）。

语法: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

删除分区

ALTER TABLE DROP 语句删除表的分区。

如果表被缓存，该命令将清除表及其所有引用的依赖项的缓存数据。缓存将在下次访问表或依赖项时懒惰地填充。

语法

      ALTER TABLE table_identifier DROP [ IF EXISTS ] partition_spec [PURGE]

     

参数

table_identifier

指定表名，表名可以可选地以数据库名作为前缀。

语法: [ database_name. ] table_name
partition_spec

要被删除的分区。请注意，可以在分区规范中使用类型字面量（例如，date’2019-01-02’）。

语法: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

设置和取消设置

设置表属性

ALTER TABLE SET 命令用于设置表属性。如果某个特定属性已经被设置，这将用新值覆盖旧值。

ALTER TABLE UNSET 用于删除表属性。

语法

      -- 设置表属性 
ALTER TABLE table_identifier SET TBLPROPERTIES ( key1 = val1, key2 = val2, ... )
-- 取消设置表属性
ALTER TABLE table_identifier UNSET TBLPROPERTIES [ IF EXISTS ] ( key1, key2, ... )

     

设置 SERDE

ALTER TABLE SET 命令用于在 Hive 表中设置 SERDE 或 SERDE 属性。如果某个特定属性已经设置，这将用新值覆盖旧值。

语法

      -- 设置 SERDE 属性
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, ... ) ]

     

设置位置和设置文件格式

ALTER TABLE SET 命令也可以用于更改现有表的文件位置和文件格式。

如果表被缓存， ALTER TABLE .. SET LOCATION 命令会清除表及其所有相关依赖项的缓存数据。当下次访问表或依赖项时，缓存将会被懒惰地填充。

语法

      -- 更改文件格式
ALTER TABLE table_identifier [ partition_spec ] SET FILEFORMAT file_format
-- 更改文件位置
ALTER TABLE table_identifier [ partition_spec ] SET LOCATION 'new_location'

     

参数

table_identifier

指定一个表名，表名可以选择性地带有数据库名。

Syntax: [ database_name. ] table_name
partition_spec

指定要设置属性的分区。注意，可以在分区规范中使用类型字面量（例如，date’2019-01-02’）。

Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )
SERDEPROPERTIES ( key1 = val1, key2 = val2, … )

指定要设置的SERDE属性。

恢复分区

ALTER TABLE RECOVER PARTITIONS 语句恢复表目录中的所有分区并更新 Hive metastore。恢复分区的另一种方法是使用 MSCK REPAIR TABLE 。

语法

      ALTER TABLE table_identifier 恢复 分区

     

参数

table_identifier

指定一个表名，名称前可选地带有数据库名称。

语法： [ 数据库名称. ] 表名称

示例

      -- 重命名表 
DESC student;
+-----------------------+---------+-------+
| col_name|data_type|comment|
+-----------------------+---------+-------+
| name| string| NULL|
| rollno| int| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type|comment|
| age| int| NULL|
+-----------------------+---------+-------+
ALTER TABLE Student RENAME TO StudentInfo;
-- 重命名表后
DESC StudentInfo;
+-----------------------+---------+-------+
| col_name|data_type|comment|
+-----------------------+---------+-------+
| name| string| NULL|
| rollno| int| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type|comment|
| age| int| NULL|
+-----------------------+---------+-------+
-- 重命名分区
SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
| age=10|
| age=11|
| age=12|
+---------+
ALTER TABLE default.StudentInfo PARTITION (age='10') RENAME TO PARTITION (age=);
-- 重命名分区后
SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
| age=11|
| age=12|
| age=15|
+---------+
-- 向表中添加新列
DESC StudentInfo;
+-----------------------+---------+-------+
| col_name|data_type|comment|
+-----------------------+---------+-------+
| name| string| NULL|
| rollno| int| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type|comment|
| age| int| NULL|
+-----------------------+---------+-------+
ALTER TABLE StudentInfo ADD columns (LastName string, DOB timestamp);
-- 添加新列到表后
DESC StudentInfo;
+-----------------------+---------+-------+
| col_name|data_type|comment|
+-----------------------+---------+-------+
| name| string| NULL|
| rollno| int| NULL|
| LastName| string| NULL|
| DOB|timestamp| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type|comment|
| age| int| NULL|
+-----------------------+---------+-------+
-- 删除表的列
DESC StudentInfo;
+-----------------------+---------+-------+
| col_name|data_type|comment|
+-----------------------+---------+-------+
| name| string| NULL|
| rollno| int| NULL|
| LastName| string| NULL|
| DOB|timestamp| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type|comment|
| age| int| NULL|
+-----------------------+---------+-------+
ALTER TABLE StudentInfo DROP columns (LastName, DOB);
-- 在删除表的列后
DESC StudentInfo;
+-----------------------+---------+-------+
| col_name|data_type|comment|
+-----------------------+---------+-------+
| name| string| NULL|
| rollno| int| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type|comment|
| age| int| NULL|
+-----------------------+---------+-------+
-- 重命名表的一列
DESC StudentInfo;
+-----------------------+---------+-------+
| col_name|data_type|comment|
+-----------------------+---------+-------+
| name| string| NULL|
| rollno| int| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type|comment|
| age| int| NULL|
+-----------------------+---------+-------+
ALTER TABLE StudentInfo RENAME COLUMN name TO FirstName;
-- 在重命名表的一列后
DESC StudentInfo;
+-----------------------+---------+-------+
| col_name|data_type|comment|
+-----------------------+---------+-------+
| FirstName| string| NULL|
| rollno| int| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type|comment|
| age| int| NULL|
+-----------------------+---------+-------+
-- ALTER 或更改列
DESC StudentInfo;
+-----------------------+---------+-------+
| col_name|data_type|comment|
+-----------------------+---------+-------+
| FirstName| string| NULL|
| rollno| int| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type|comment|
| age| int| NULL|
+-----------------------+---------+-------+
ALTER TABLE StudentInfo ALTER COLUMN FirstName COMMENT "新评论";
-- 在ALTER或更改列后
DESC StudentInfo;
+-----------------------+---------+-----------+
| col_name|data_type| comment|
+-----------------------+---------+-----------+
| FirstName| string|new comment|
| rollno| int| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type| comment|
| age| int| NULL|
+-----------------------+---------+-----------+
-- 替换列
DESC StudentInfo;
+-----------------------+---------+-----------+
| col_name|data_type| comment|
+-----------------------+---------+-----------+
| FirstName| string|new comment|
| rollno| int| NULL|
| age| int| NULL|
|# 分区 信息| | |
| # col_name|data_type| comment|
| age| int| NULL|
+-----------------------+---------+-----------+
ALTER TABLE StudentInfo REPLACE COLUMNS (name string, ID int COMMENT '新评论');
-- 替换列后
DESC StudentInfo;
+-----=---------+---------+-----------+
| col_name|data_type| comment|
+---------------+---------+-----------+
| name| string| NULL|
| ID| int|new comment|
| # 分区| | |
|没有 分区| | |
+---------------+---------+-----------+
-- 向表中添加新分区 
SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
| age=11|
| age=12|
| age=15|
+---------+
ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18);
-- 在向表中添加新分区后
SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
| age=11|
| age=12|
| age=15|
| age=18|
+---------+
-- 从表中删除分区 
SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
| age=11|
| age=12|
| age=15|
| age=18|
+---------+
ALTER TABLE StudentInfo DROP IF EXISTS PARTITION (age=18);
-- 在删除表的分区后
SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
| age=11|
| age=12|
| age=15|
+---------+
-- 向表中添加多个分区
SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
| age=11|
| age=12|
| age=15|
+---------+
ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18) PARTITION (age=20);
-- 在向表中添加多个分区后
SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
| age=11|
| age=12|
| age=15|
| age=18|
| age=20|
+---------+
-- 更改文件格式
ALTER TABLE loc_orc SET fileformat orc;
ALTER TABLE p1 partition (month=2, day=2) SET fileformat parquet;
-- 更改文件位置
ALTER TABLE dbx.tab1 PARTITION (a='1', b=) SET LOCATION -- 设置SERDE/ SERDE属性
ALTER TABLE test_tab SET SERDE ;
ALTER TABLE dbx.tab1 SET SERDE WITH SERDEPROPERTIES (= , = )
-- 设置表属性
ALTER TABLE dbx.tab1 SET TBLPROPERTIES (= );
-- 使用SET属性设置表注释
ALTER TABLE dbx.tab1 SET TBLPROPERTIES (= );
-- 使用SET属性修改表注释
ALTER TABLE dbx.tab1 SET TBLPROPERTIES (= );
-- 删除表属性
ALTER TABLE dbx.tab1 UNSET TBLPROPERTIES ();
-- 恢复分区
ALTER TABLE dbx.tab1 RECOVER PARTITIONS;

     

Spark SQL 指南

修改表

描述

重命名

语法

参数

添加列

语法

参数

删除列

语法

参数