[
https://issues.apache.org/jira/browse/FLINK-41489?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18144412#comment-18144412
]
Muhammad commented on FLINK-41489:
----------------------------------
I am willing to work on this, and feel free to assign this to me.
> Ensure ALTER TABLE SQL commands are idempotent
> ----------------------------------------------
>
> Key: FLINK-41489
> URL: https://issues.apache.org/jira/browse/FLINK-41489
> Project: Flink
> Issue Type: New Feature
> Components: Table SQL / API
> Affects Versions: 2.4.1
> Reporter: Muhammad
> Priority: Major
>
> The FLIP can be found as under (as I don't have permissions to post on Wiki)
> h1. Motivation
> Unconditional ALTER TABLE statements can fail when application initialization
> or a SQL script is executed again against an existing catalog table. For
> example, adding a column fails if an earlier execution already added it. This
> FLIP introduces column-level existence guards so that individual supported
> ALTER TABLE statements can be repeated without failing solely because the
> column already exists or is absent.
> To give an understanding of the problem, let’s say we have a streaming Flink
> SQL job.
> {code:java}
> CREATE TABLE IF NOT EXISTS dst_table (
> key INT,
> val STRING
> );
> INSERT INTO dst_table SELECT key,val FROM src_table;
> {code}
> The above streaming job works fine unless there is a need for addition of a
> new column. Let’s say an engineer now adds a column via ALTER statement to it.
> {code:java}
> CREATE TABLE IF NOT EXISTS dst_table (
> key INT,
> val STRING
> );
> ALTER TABLE dst_table ADD COLUMN new_column INT;
> INSERT INTO dst_table SELECT key, val, new_column FROM src_table;
> {code}
> After making the changes, the above streaming job works for the first time,
> but fails once the column is created.
> The aim of this FLIP is to make the ALTER TABLE operations idempotent by
> supporting the IF EXISTS and IF NOT EXISTS constructs. These options turn the
> error for a missing column or not existing column into a noop. The scope of
> this FLIP is limited to
> # Supporting IF NOT EXISTS construct with ADD COLUMN. This will make the
> ALTER operation a no-op if a column with the same name exists. It will skip
> even if its type, nullability, comment differ. A missing table remains an
> error unless the statement also uses ALTER TABLE IF EXISTS. Multi column list
> will be supported and each column is skipped independently.
> # Supporting IF EXISTS for DROP COLUMN operation. This will make the ALTER
> operation a no-op if a column with the same name does not exist. All the
> existing constraints checks will be applied if the column exists.
> # Supporting IF EXISTS for RENAME column. If the source column is absent,
> the statement is a no-op regardless of whether the destination exists.
> Otherwise, existing rename validation applies, including rejecting an
> existing destination column.
> The ALTER TABLE ADD PARTITION already supports IF NOT EXISTS pattern, so this
> is aligned with it. The ALTER TABLE MODIFY can become tricky and is outside
> the scope of this FLIP.
> h1. Public Interfaces
> h2. ADD COLUMN IF NOT EXISTS
> {code:java}
> ALTER TABLE dst_table ADD COLUMN IF NOT EXISTS new_column INT;
> ALTER TABLE dst_table ADD IF NOT EXISTS new_column INT;
> ALTER TABLE dst_table ADD IF NOT EXISTS (c1 INT, c2 STRING);
> {code}
> h2. DROP COLUMN IF EXISTS
> {code:java}
> ALTER TABLE dst_table DROP COLUMN IF EXISTS new_column;
> ALTER TABLE dst_table DROP IF EXISTS new_column;
> ALTER TABLE dst_table DROP IF EXISTS (c1, c2);
> {code}
> h2. RENAME COLUMN IF EXISTS
> {code:java}
> ALTER TABLE dst_table RENAME COLUMN IF EXISTS old_column TO new_column ;
> ALTER TABLE dst_table RENAME IF EXISTS old_column TO new_column;
> {code}
> h1. Proposed Changes
> At a high level, the changes should involve the following
> * The IF EXISTS and IF NOT EXISTS patterns shouldn’t require Apache Calcite
> changes and the above can be implemented by extending the Flink’s grammar.
> * The operations would require some changes to the planner. This means to
> read the table / database / catalog and decide whether the operation is
> needed or not.
> * This FLIP adds no atomicity or concurrency guarantees beyond those
> provided by the catalog. Existence checks use the schema observed during
> statement conversion.
> * Concurrent alterations may fail or overwrite intervening changes,
> depending on the catalog implementation. If concurrent executions of the same
> alteration race and one succeeds, retrying the failed SQL statement with
> fresh planning against the updated catalog schema makes it no-op.
> * Ensure that IF is backticked if used as column name. It is currently a
> non-reserved word
> ([https://github.com/apache/flink/blob/58143561cd00e9f5cab1eda67c478191fd0581ec/flink-table/flink-sql-parser/src/main/codegen/data/Parser.tdd#L626]).
> There is no intention of converting it into a reserved word with this FLIP.
> * Updating documentation and Unit Tests. The tests should provide coverage
> for Parser/unparser, Operation-conversion, Multi-column and missing table
> scenarios.
> h1. Compatibility, Deprecation, and Migration Plan
> Using column name IF would require backticks if used with ALTER TABLE
> statement like
> {code:java}
> ALTER TABLE dst_table ADD `IF` INT;
> {code}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)