[ 
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)

Reply via email to