[FLINK-21634] ALTER TABLE statement enhancement

• Type: New Feature
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Fix Version/s: 2.0.0
• Component/s: Table SQL / API, Table SQL / Client
• Labels: auto-unassigned stale-assigned
• Assignee: Jark Wu
• Reporter: Jark Wu
• Created: 05/Mar/21 09:26
• Updated: 10/Jul/24 06:17

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 (except SET and RENAME TO, others are all new introduced syntax):

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

And some examples:

-- 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');

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

Issue Links

• duplicates: FLINK-19062 Improve alter table DDL
• is blocked by: FLINK-29072 Migrate DDL-related schema handling to the new Schema framework
• relates to: FLINK-17845 Can't remove a table connector property with ALTER TABLE
• relates to: FLINK-27237 Partitioned table statement enhancement
• relates to: FLINK-19062 Improve alter table DDL

Sub-Tasks

• Support ADD column/constraint/watermark for ALTER TABLE statement: Closed
• Support MODIFY column/constraint/watermark for ALTER TABLE statement: Closed
• Support DROP column/constraint/watermark for ALTER TABLE statement: Closed
• Support RENAME column name for ALTER TABLE statement: Closed
• Support RESET table option for ALTER TABLE statement: Closed
• Add documentation for new introduced ALTER TABLE statements: Closed
• Support 'ALTER TABLE ADD' statement: Closed
• Support "IF EXISTS" in DDL for ALTER TABLE: Closed
• Restrict ALTER TABLE from setting empty table options: In Progress
• Supports ADD/MODIFY column/watermark/constraint syntax parse for ALTER TABLE: Closed