[
https://issues.apache.org/jira/browse/FLINK-21634?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17440006#comment-17440006
]
Yuepeng Pan edited comment on FLINK-21634 at 11/7/21, 1:25 PM:
---------------------------------------------------------------
Hi, [~jark] , Thank you so much for looking into this.
And a minor doubt from me.
{code:java}
– modify definition of column log_ts and ts, primary key, watermark. they must
exist in table schema
ALTER TABLE mytable ADD (
log_ts STRING COMMENT 'log timestamp string' AFTER `id`, – reoder columns
ts AS TO_TIMESTAMP(log_ts) AFTER log_ts,
PRIMARY KEY (id) NOT ENFORCED,
WATERMARK FOR ts AS ts - INTERVAL '3' SECOND
); {code}
The keyword 'ADD' what you mean is 'MODIFY' ?
Maybe I misunderstanding the demo.
was (Author: rocmarshal):
Hi, [~jark] , Thank you so much for looking into this.
And a minor doubt from me.
{code:java}
– modify definition of column log_ts and ts, primary key, watermark. they must
exist in table schemaALTER TABLE mytable ADD (
log_ts STRING COMMENT 'log timestamp string' AFTER `id`, – reoder columns ts AS
TO_TIMESTAMP(log_ts) AFTER log_ts, PRIMARY KEY (id) NOT ENFORCED,
WATERMARK FOR ts AS ts - INTERVAL '3' SECOND); {code}
The keyword 'ADD' what you mean is 'MODIFY' ?
Maybe I misunderstanding the demo.
> 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
> Assignee: Jark Wu
> Priority: Major
> Labels: auto-unassigned, stale-assigned
> Fix For: 1.15.0
>
>
> 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 (except
> {{SET}} and {{RENAME TO}}, others are all new introduced syntax):
> {code:sql}
> ALTER TABLE table_name {
> ADD { <schema_component> | (<schema_component> [, ...]) }
> | MODIFY { <schema_component> | (<schema_component> [, ...]) }
> | DROP {column_name | (column_name, column_name, ....) | PRIMARY KEY |
> CONSTRAINT constraint_name | WATERMARK}
> | RENAME old_column_name TO new_column_name
> | RENAME TO new_table_name
> | SET (key1=val1, ...)
> | RESET (key1, ...)
> }
> <schema_component>::
> { <column_component> | <constraint_component> | <watermark_component> }
> <column_component>::
> column_name <column_definition> [FIRST | AFTER column_name]
> <constraint_component>::
> [CONSTRAINT constraint_name] PRIMARY KEY (column_name, ...) NOT ENFORCED
> <watermark_component>::
> WATERMARK FOR rowtime_column_name AS watermark_strategy_expression
> <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}
> And some examples:
> {code:sql}
> -- add a new column
> ALTER TABLE mytable ADD new_column STRING COMMENT 'new_column docs';
> -- add columns, constraint, and watermark
> ALTER TABLE mytable ADD (
> log_ts STRING COMMENT 'log timestamp string' FIRST,
> ts AS TO_TIMESTAMP(log_ts) AFTER log_ts,
> PRIMARY KEY (id) NOT ENFORCED,
> WATERMARK FOR ts AS ts - INTERVAL '3' SECOND
> );
> -- modify a column type
> ALTER TABLE prod.db.sample MODIFY measurement double COMMENT 'unit is bytes
> per second' AFTER `id`;
> -- modify definition of column log_ts and ts, primary key, watermark. they
> must exist in table schema
> ALTER TABLE mytable ADD (
> log_ts STRING COMMENT 'log timestamp string' AFTER `id`, -- reoder
> columns
> ts AS TO_TIMESTAMP(log_ts) AFTER log_ts,
> PRIMARY KEY (id) NOT ENFORCED,
> WATERMARK FOR ts AS ts - INTERVAL '3' SECOND
> );
> -- drop an old column
> ALTER TABLE prod.db.sample DROP measurement;
> -- drop columns
> ALTER TABLE prod.db.sample DROP (col1, col2, col3);
> -- drop a watermark
> ALTER TABLE prod.db.sample DROP WATERMARK;
> -- rename column name
> ALTER TABLE prod.db.sample RENAME `data` TO payload;
> -- rename table name
> ALTER TABLE mytable RENAME TO mytable2;
> -- set options
> ALTER TABLE kafka_table SET (
> 'scan.startup.mode' = 'specific-offsets',
> 'scan.startup.specific-offsets' = 'partition:0,offset:42'
> );
> -- reset options
> ALTER TABLE 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.20.1#820001)
