[jira] [Created] (FLINK-21634) ALTER TABLE statement enhancement

["Jark Wu (Jira)" <ji...@apache.org>]

Jark Wu created FLINK-21634:
-------------------------------

             Summary: ALTER TABLE statement enhancement
                 Key: FLINK-21634
                 URL: https://issues.apache.org/jira/browse/FLINK-21634
             Project: Flink
          Issue Type: New Feature
          Components: Table SQL / API, Table SQL / Client
            Reporter: Jark Wu


We already introduced ALTER TABLE statement in FLIP-69 [1], but only support to rename table name and change table options. One useful feature of ALTER TABLE statement is modifying schema. This is also heavily required by integration with data lakes (e.g. iceberg). 

Therefore, I propose to support following ALTER TABLE statements:

*Add Column*

{code:sql}
ALTER TABLE <table_name> 
    ADD COLUMN <column_name> <data_type> [COMMENT column_comment]
{code}

This follows SQL standard 2011 Section 11.10. And Iceberg[2], Trino[3], MySQL[4] also are the same. 

*Drop Column*

{code:sql}
ALTER TABLE <table_name> DROP COLUMN <column_name>
{code}

This follows SQL standard 2011 Section 11.10. And Iceberg[2], Trino[3], MySQL[4] also are the same. 


*Alter Column*

{code:sql}
ALTER TABLE <table_name> ALTER COLUMN <column_name> 
  SET DATA TYPE <data_type>  [COMMENT column_comment]
{code}

This follows SQL standard 2011 Section 11.10. Same to PG [5], and similar to  Iceberg[2], Trino[3], MySQL[4].

*Rename Column*
{code:sql}
ALTER TABLE <table_name> REANME COLUMN <old_name> TO <new_name>
{code}

This is not listed in SQL standard, but is also very useful. Follows the syntax of Iceberg[2], Trino[3], MySQL[4].

*Unset Options*
{code:sql}
ALTER TABLE <table_name> RESET (key1=val1, key2=val2, ...)
{code}

Out of SQL standard, but is useful. Has been discussed in FLINK-17845. Use {{RESET}} to keep align with {{SET key=value}} and {{RESET key}} proposed in FLIP-163. And PG[5] also uses the {{RESET}} keyword.

For example:

{code:sql}
-- add a new column 
ALTER TABLE mytable ADD COLUMN new_column STRING COMMENT 'new_column docs';

-- drop an old column
ALTER TABLE prod.db.sample DROP COLUMN legacy_name;

-- rename column name
ALTER TABLE prod.db.sample RENAME COLUMN data TO payload;

-- alter table type
ALTER TABLE prod.db.sample ALTER COLUMN measurement 
  SET DATA TYPE double COMMENT 'unit is bytes per second';
{code}


[1]: https://cwiki.apache.org/confluence/display/FLINK/FLIP+69+-+Flink+SQL+DDL+Enhancement
[2]: http://iceberg.apache.org/spark-ddl/#alter-table-alter-column
[3]: https://trino.io/docs/current/sql/alter-table.html
[4]: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
[5]: https://www.postgresql.org/docs/9.1/sql-altertable.html



--
This message was sent by Atlassian Jira
(v8.3.4#803005)