文档首页/ 智能数据湖 AIDataLake/ Aura语法参考/ SQL语法参考/ DDL语法/ ALTER TABLE
更新时间：2026-07-03 GMT+08:00
查看PDF
分享

ALTER TABLE

功能描述

ALTER TABLE为Aura服务下特有语法，该语法功能是修改LakeFormation上表的元数据信息，用于调整数据表的结构或属性。

约束限制

  • ADD COLUMNS、DROP COLUMNS、COLUMN RENAME、ALTER COLUMN语法仅对ICEBERG表生效，ORC、PARQUET表无法对列进行操作。
  • 列位置操作、复杂类型字段操作仅对ICEBERG表生效。
  • 创建、删除branch/tag仅支持iceberg表。
  • 外表（EXTERNAL TABLE）不支持ALTER TABLE。

语法格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 
ALTER TABLE table_name DROP PARTITIONS ( col_name = col_value [, ... ] ) [, ... ];
ALTER TABLE table_name ADD COLUMN col_name col_type [ COMMENT col_comment ] [ FIRST |  BEFORE col_name | AFTER col_name ] [, ... ];
ALTER TABLE table_name DROP COLUMNS ( col_name [, ... ] );
ALTER TABLE table_name RENAME COLUMN col_name TO col_name_new;
ALTER TABLE table_name ALTER COLUMN col_name [ TYPE col_type ] [ COMMENT col_comment ] [ FIRST |  BEFORE col_name | AFTER col_name ] [, ... ];
ALTER TABLE table_name RENAME TO table_name_new;
ALTER TABLE table_name SET TABLEPROPERTIES ( option_key = option_value [, ... ] );
ALTER TABLE table_name UNSET TABLEPROPERTIES ( option_key [, ... ] );
ALTER TABLE table_name UPDATE COLUMNS;
ALTER TABLE table_name ADD PARTITION COLUMN transform [, ...];
ALTER TABLE table_name DROP PARTITION COLUMN transform [, ...];
ALTER TABLE table_name SET PARTITION COLUMN transform [, ...];
ALTER TABLE table_name OWNER TO USER RoleId;
ALTER TABLE table_name OWNER TO USER GROUP RoleId;
ALTER TABLE table_name CREATE [OR REPLACE] BRANCH [IF NOT EXISTS] <branch_name>[AS OF VERSION <snapshot_id>][RETAIN <int> { DAYS | HOURS | MINUTES }][WITH SNAPSHOT RETENTION  { minSnapshotsToKeep | maxSnapshotAge | minSnapshotsToKeep maxSnapshotAge }] minSnapshotsToKeep ::= <int> SNAPSHOTS maxSnapshotAge ::= <int> { DAYS | HOURS | MINUTES }
ALTER TABLE table_name CREATE [OR REPLACE] TAG [IF NOT EXISTS] <tag_name>[AS OF VERSION <snapshot_id>][RETAIN <int> { DAYS | HOURS | MINUTES }]
ALTER TABLE table_name DROP { BRANCH [IF EXISTS] <branch_name> | TAG [IF EXISTS] <tag_name> }
其中 transform为
col_name | year(col_name) | month(col_name) | day(col_name) | hour(col_name)| truncate(width, col_name) | bucket(bucket_num, col_name)

参数说明

该ALTER TABLE语法中，参数规格与建表规格一致。

示例

在表test_table中添加sales bigint列：

1
 
ALTER TABLE test_table ADD COLUMN sales bigint COMMENT 'factory sales volume';

将表test_table中sales列更名为my_sales：

1
 
ALTER TABLE test_table RENAME COLUMN sales TO my_sales;

将表test_table中my_sales列类型更换为String：

1
 
ALTER TABLE test_table ALTER COLUMN my_sales TYPE text COMMENT 'factory sales volume';

将表test_table中my_sales列删除：

1
 
ALTER TABLE test_table DROP COLUMNS ( my_sales );

将表test_table更名为factory_info：

1
 
ALTER TABLE test_table RENAME TO factory_info;

将表factory_info中参数hoodie.metadata.enable打开：

1
 
ALTER TABLE factory_info SET TABLEPROPERTIES ( 'hoodie.metadata.enable' = 'true' );

将表factory_info中参数恢复默认值：

1
 
ALTER TABLE factory_info UNSET TABLEPROPERTIES ( 'hoodie.metadata.enable' );

同步表factory_info分区信息：

1
 
ALTER TABLE factory_info UPDATE COLUMNS;
父主题： DDL语法

相关文档