The ALTER TABLE SQL can be used to change the table definition of an OTF table.
Syntax
ALTER TABLE <datalake_name>.<OTFdatabase_name>.<OTFtable_name> [<alter_option>[, ...]] <datalake_name> ::= !! name of the data lake object <OTFdatabase_name> ::= !! name of the database in the OTF catalog <OTFtable_name> ::= !! name of the OTF table <alter_option> ::= ADD <add_option> | MODIFY <modify_option> | RENAME <column_name> TO <column_name> | DROP <drop_option> add_option ::= <add_column_specification> | (<add_column_specification>[, ...]) | <column_name> {BEFORE | AFTER} <column_name> | (<column_name>[, ...]) {BEFORE | AFTER} <column_name> | {PARTITIONED | PARTITION} BY {({<transform> | <column_name>}[, ...]) | <transform> | <column_name>} | {SORTED | ORDER} BY {<column_name> [<sort_type>]}[, ...] | {TBLPROPERTIES | PROPERTIES} (<custom_clause_list>) modify_option ::= (<mod_column_specification>[, ...]) | <mod_column_specification> drop_option ::= <column_name> | {PARTITIONED | PARTITION} BY {({<transform> | <column_name>}[, ...]) | <transform> | <column_name>} | {SORTED | ORDER} BY | {TBLPROPERTIES | PROPERTIES} ([<custom_clause_list>]) <add_column_specification> ::= <column_name> <column_type> [NULL] [COMMENT <comment_literal>] <mod_column_specification> ::= <column_name> [<column_type>] [NULL] [COMMENT <comment_literal>] <comment_literal> ::= <quote> <comment_string> <quote> <comment_string> ::= !! description text, up to 255 characters. <transform> ::= IDENTITY(<column_name>) | BUCKET(<hash_value>, <column_name>) | TRUNCATE(<truncate_value>, <column_name> | YEAR(<column_name>) | MONTH(<column_name>) | DAY(<column_name>) | HOUR(<column_name>) | NULL(<column_name>) <hash_value> ::= !! bucket number <truncate_value> ::= !! width or length <sort_type> ::= ASC | DESC <custom_clause_list> ::= <custom_clause>[, ...] <custom_clause> ::= <quote><name><quote>=<quote><value><quote>
Examples
Add two columns:
ALTER TABLE MyIceberg.MyDatabase.Tab1 ADD c3 INT NULL, ADD count INTEGER NULL;
Drop two columns:
ALTER TABLE MyIceberg.MyDatabase.Tab1 DROP c3, DROP c4;
Rename column c2 to count:
ALTER TABLE MyIceberg.MyDatabase.Tab1 RENAME c2 TO count;
Make existing column not required:
ALTER TABLE MyIceberg.MyDatabase.Tab1 MODIFY c3 NULL;
Change column type:
ALTER TABLE MyIceberg.MyDatabase.Tab1 MODIFY c3 VARCHAR(255);
Reorder the columns AFTER
ALTER TABLE MyIceberg.MyDatabase.Tab1 ADD (c7,c8) AFTER c6;
Reorder the columns BEFORE
ALTER TABLE MyIceberg.MyDatabase.Tab1 ADD (c7, c8) BEFORE c6;
Add a comment
ALTER TABLE MyIceberg.MyDatabase.Tab1 MODIFY c3 COMMENT 'This is a comment';
Add USING name/value pair:
ALTER TABLE MyIceberg.MyDatabase.Tab1 ADD TBLPROPERTIES('write.merge.mode'='copy-on-write');
Updating USING name/value pair:
ALTER TABLE MyIceberg.MyDatabase.Tab1 ADD TBLPROPERTIES('write.merge.mode'='merge-on-read');
Removing USING name/value pair:
ALTER TABLE MyIceberg.MyDatabase.Tab1 DROP TBLPROPERTIES('write.merge.mode'='merge-on-read');
Add partition field:
ALTER TABLE MyIceberg.MyDatabase.Tab1 ADD PARTITIONED BY c3;
Drop partition field:
ALTER TABLE MyIceberg.MyDatabase.Tab1 DROP PARTITIONED BY c3;
Add sort order:
ALTER TABLE MyIceberg.MyDatabase.Tab1 ADD SORTED BY c2 ASC, c3 DESC;
Drop sort order:
ALTER TABLE MyIceberg.MyDatabase.Tab1 DROP SORTED BY;
Usage Considerations
- The ADD option is used to add table properties and partitions or change the sort order.
- For an ADD clause using <column_specificiation>, if the column does exist, an error is returned.
- For an ADD clause using BEFORE or AFTER, all columns listed in the statement must exist.
- The MODIFY option is used to modify column properties.
- Modification of a column data type and nullability/comment cannot be done on one statement.
- For a MODIFY clause, if the column does not exist, an error is returned.
- Different alter table operations cannot be combined into one statement. For example, the statement cannot add columns and modify columns.
- The following ALTER TABLE options or clauses are not allowed:
- Table kind, such as SET, MULTISET, GLOBAL TEMPORARY, or VOLATILE
- FALLBACK
- WITH JOURNAL TABLE
- LOG
- CHECKSUM
- FREESPACE
- INDEX
- ON COMMIT
- MERGEBLOCKRATIO
- DATABLOCKSIZE
- BLOCKCOMPRESSION
- The sort order cannot be changed. It has to be dropped and then added with the new sort order.
- PARTITIONED | PARTITION BY cannot be combined with other table changes.
- TBLPROPERTIES/PROPERTIES cannot be combined with other table changes.
- The same rules apply for TBLPROPERTIES/PROPERTIES as the create data lake table statement section.
- A given name in name/value pair must not occur multiple times in TBLPROPERTIES/PROPERTIES.
- SORTED/ORDER BY cannot be combined with other table changes.
- SORTED/ORDER BY does not support individual columns.
- For Unity catalog, there is no schema evolution or partition evolution.
Usage Considerations: Iceberg
- For Iceberg partition rules, see Spec - Apache Iceberg.
- For Iceberg schema evolution restrictions, see Schema Evolution.
Usage Considerations: Delta Lake
- The following are not supported:
SORTED/ORDER BY order ASC, DESC
- Partition Evolution, so columns cannot be added and dropped to partitions; the following error is displayed: “Partition evolution is not supported for Delta Lake Tables.”
- Column reorder using BEFORE and AFTER.
- To rename a column or drop one or more columns, columns mapping must be enabled by setting the table property delta.columnMapping.mode to name (in CREATE TABLE or ALTER TABLE), as the following:
ALTER TABLE MyDeltaLake.MyDatabase.Tab1 ADD TBLPROPERTIES('delta.columnMapping.mode'='name');
For more information, see the Delta Lake specification: Rename and drop columns with Delta Lake column mapping | Databricks on AWS.