ALTER TABLE
功能描述
ALTER TABLE为DataArtsFabric SQL服务下特有语法,该语法功能是修改LakeFormation上表的元数据信息。
注意事项
- ADD COLUMNS、DROP COLUMNS、COLUMN RENAME、ALTER COLUMN语法仅对ICEBERG表生效,ORC、PARQUET表无法对列进行操作。
- 外表(EXTERNAL TABLE)不支持ALTER TABLE。
语法格式
1 2 3 4 5 6 7 8 9 |
ALTER TABLE table_name DROP PARTITIONS ( col_name = col_value [, ... ] ) [, ... ]; ALTER TABLE table_name ADD COLUMNS ( col_name col_type [ COMMENT col_comment ] [, ... ] ); ALTER TABLE table_name DROP COLUMNS ( col_name [, ... ] ); ALTER TABLE table_name COLUMN col_name RENAME TO col_name_new; ALTER TABLE table_name ALTER COLUMN col_name col_name_new col_type [ COMMENT col_comment ]; 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语法中,参数规格与建表规格一致。
示例
在表test_table中添加sales bigint列:
1
|
ALTER TABLE test_table ADD COLUMNS ( sales bigint COMMENT 'factory sales volume' ); |
将表test_table中sales列更名为my_sales:
1
|
ALTER TABLE test_table COLUMN sales RENAME TO my_sales; |
将表test_table中my_sales列类型更换为String:
1
|
ALTER TABLE test_table ALTER COLUMN my_sales my_sales 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; |