Muhammad created FLINK-41489:
--------------------------------
Summary: 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
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)
