Altering an OTF Table - Teradata Vantage

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.