[
https://issues.apache.org/jira/browse/FLINK-21634?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17297985#comment-17297985
]
Timo Walther edited comment on FLINK-21634 at 3/9/21, 11:43 AM:
----------------------------------------------------------------
Thanks for the proposal [~jark]. +1 for adding this functionality. Here is some
feedback from my side:
{{ADD COLUMN}}:
1) Shall we make {{COLUMN}} optional? I see many vendors doing this.
2) Shall we add optional {{FIRST}} and {{LAST}} to make the behavior more
explicit where {{LAST}} is the default?
3) How about we support the same syntax as in a regular {{CREATE TABLE}} DDL
schema part? This means that we can support all kinds of columns such as {{ADD
col_name col_type}} or {{ADD (col1_name col1_type, col2_name col2_type)}}
including metadata and computed columns.
{{ALTER COLUMN}}:
1) Following my suggestion from above. We could allow more than just altering
the data type. We could simply allow all DDL column definitions for supporting
metadata and computed columns as well. Such as {{MODIFY COLUMN col_name AS 1
+1}}. I think Oracle uses {{MODIFY}} to distinguish from standard's {{ALTER}}.
was (Author: twalthr):
Thanks for the proposal [~jark]. +1 for adding this functionality. Here is some
feedback from my side:
{{ADD COLUMN}}:
1) Shall we make {{COLUMN}} optional? I see many vendors doing this.
2) Shall we add optional {{FIRST}} and {{LAST}} to make the behavior more
explicit where {{LAST}} is the default?
3) How about we support the same syntax as in a regular {{CREATE TABLE}} DDL
schema part? This means that we can support all kinds of columns such as {{ADD
col_name col_type}} or {{ADD (col1_name col1_type, col2_name col2_type)}}
including metadata and computed columns.
{{ALTER COLUMN}}:
1) Following my suggestion from above. We could allow more than just altering
the data type. We could simply allow all DDL column definitions for supporting
metadata and computed columns as well. Such as {{ALTER COLUMN col_name AS 1
+1}}.
> 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
> Priority: Major
>
> 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].
> *Reset Options*
> {code:sql}
> ALTER TABLE <table_name> RESET (key1, key2, ...)
> {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';
> -- reset options
> ALTER TALBE kafka_table RESET ('scan.startup.mode',
> 'scan.startup.specific-offsets');
> {code}
> Note: we don't need to introduce new interfaces, because all the alter table
> operation will be forward to catalog through {{Catalog#alterTable(tablePath,
> newTable, ignoreIfNotExists)}}.
> [1]:
> https://ci.apache.org/projects/flink/flink-docs-master/docs/dev/table/sql/alter/#alter-table
> [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)
