[
https://issues.apache.org/jira/browse/FLINK-21634?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Timo Walther updated FLINK-21634:
---------------------------------
Description:
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 <column_definition> [FIRST | AFTER column_name]
<column_definition>::
{ <physical_column_definition> | <metadata_column_definition> |
<computed_column_definition> } [COMMENT column_comment]
<physical_column_definition>::
column_type
<metadata_column_definition>::
column_type METADATA [ FROM metadata_key ] [ VIRTUAL ]
<computed_column_definition>::
AS computed_column_expression
{code}
This follows SQL standard 2011 Section 11.10. And Iceberg[2], Trino[3],
MySQL[4] also are the same.
*Add Columns*
In order to support adding multiple columns easily, we will introduce {{ADD
COLUMNS}}:
{code:sql}
ALTER TABLE table_name
ADD COLUMNS (column_name <column_definition> [, ...]) [FIRST | AFTER
column_name]
{code}
*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.
*Modify Column*
{code:sql}
ALTER TABLE table_name
MODIFY COLUMN column_name <column_definition> [FIRST | AFTER column_name]
{code}
This is not in SQL standard 2011 Section 11.10, but is in MySQL and Oracle.
Modify Column can change column into arbitrary definition which works better
with Flink. So we don't introduce {{ALTER COLUMN}} syntax that is not even able
to change every aspect of the column.
*Rename Column*
{code:sql}
ALTER TABLE <table_name> RENAME 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 double COMMENT 'unit is
bytes per second' AFTER `id`;
-- 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
was:
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 <column_definition> [FIRST | AFTER column_name]
<column_definition>::
{ <physical_column_definition> | <metadata_column_definition> |
<computed_column_definition> } [COMMENT column_comment]
<physical_column_definition>::
column_type
<metadata_column_definition>::
column_type METADATA [ FROM metadata_key ] [ VIRTUAL ]
<computed_column_definition>::
AS computed_column_expression
{code}
This follows SQL standard 2011 Section 11.10. And Iceberg[2], Trino[3],
MySQL[4] also are the same.
*Add Columns*
In order to support adding multiple columns easily, we will introduce {{ADD
COLUMNS}}:
{code:sql}
ALTER TABLE table_name
ADD COLUMNS (column_name <column_definition> [, ...]) [FIRST | AFTER
column_name]
{code}
*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.
*Modify Column*
{code:sql}
ALTER TABLE table_name
MODIFY COLUMN column_name <column_definition> [FIRST | AFTER column_name]
{code}
This is not in SQL standard 2011 Section 11.10, but is in MySQL and Oracle.
Modify Column can change column into arbitrary definition which works better
with Flink. So we don't introduce {{ALTER COLUMN}} syntax that is not even able
to change every aspect of the column.
*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 double COMMENT 'unit is
bytes per second' AFTER `id`;
-- 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
> 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 <column_definition> [FIRST | AFTER column_name]
> <column_definition>::
> { <physical_column_definition> | <metadata_column_definition> |
> <computed_column_definition> } [COMMENT column_comment]
> <physical_column_definition>::
> column_type
> <metadata_column_definition>::
> column_type METADATA [ FROM metadata_key ] [ VIRTUAL ]
> <computed_column_definition>::
> AS computed_column_expression
> {code}
> This follows SQL standard 2011 Section 11.10. And Iceberg[2], Trino[3],
> MySQL[4] also are the same.
> *Add Columns*
> In order to support adding multiple columns easily, we will introduce {{ADD
> COLUMNS}}:
> {code:sql}
> ALTER TABLE table_name
> ADD COLUMNS (column_name <column_definition> [, ...]) [FIRST | AFTER
> column_name]
> {code}
> *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.
> *Modify Column*
> {code:sql}
> ALTER TABLE table_name
> MODIFY COLUMN column_name <column_definition> [FIRST | AFTER column_name]
> {code}
> This is not in SQL standard 2011 Section 11.10, but is in MySQL and Oracle.
> Modify Column can change column into arbitrary definition which works better
> with Flink. So we don't introduce {{ALTER COLUMN}} syntax that is not even
> able to change every aspect of the column.
> *Rename Column*
> {code:sql}
> ALTER TABLE <table_name> RENAME 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 double COMMENT 'unit is
> bytes per second' AFTER `id`;
> -- 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)
