Updated on 2025-09-18 GMT+08:00

ALTER TABLE

Function

ALTER TABLE is specific to DataArts Fabric SQL. This syntax is used to modify metadata of tables in LakeFormation.

Precautions

  • The ADD COLUMNS, DROP COLUMNS, COLUMN RENAME, and ALTER COLUMN syntaxes take effect only for Hudi and Iceberg tables.
  • External tables do not support ALTER TABLE.

Syntax

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;

Parameter Description

In the ALTER TABLE syntax, the parameter specifications are the same as those for table creation.

Examples

Add the sales bigint column to the test_table table.

1
ALTER TABLE test_table ADD COLUMNS ( sales bigint COMMENT 'factory sales volume' );

Rename the sales column in the test_table table to my_sales.

1
ALTER TABLE test_table COLUMN sales RENAME TO my_sales;

Change the type of the my_sales column in the test_table table to String.

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

Delete the my_sales column from the test_table table.

1
ALTER TABLE test_table DROP COLUMNS ( my_sales );

Rename the test_table table to factory_info.

1
ALTER TABLE test_table RENAME TO factory_info;

Enable the hoodie.metadata.enable parameter in the factory_info table.

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

Restore the parameters in the factory_info table.

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

Synchronize the partition information of the factory_info table.

1
ALTER TABLE factory_info UPDATE COLUMNS;