[
https://issues.apache.org/jira/browse/FLINK-41363?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Muhammad updated FLINK-41363:
-----------------------------
Description:
h3. Problem
The addition of _ALTER TABLE <table> ADD COLUMN_ to a Flink job makes the job
kind of non-idempotent. This means the job will run fine for the first time as
the column doesn't exist but later it'll fail with an exception until the ALTER
TABLE command is removed.
{code:java}
org.apache.flink.table.api.ValidationException: Failed to execute ALTER TABLE
statement.
Column `col_1` already exists in the table.
{code}
h3. Solution
A broader idea is to support something like ALTER TABLE <table> ADD [COLUMN] IF
NOT EXISTS pattern. This pattern is pretty common with many datastores like
Postgres, Cassandra, Clickhouse etc. The scope of this ticket is to keep it
restricted to COLUMN, but can be extended to ADD PARTITIONS etc
This involves the following work.
* The first is about extending Flink’s SQL grammar to support the IF NOT EXISTS
pattern for column addition. The `COLUMN` keyword following the `ADD` construct
is optional, so both of those patterns needs to be handled.
* This also involves preserving the guard (IF NOT EXISTS) in the parsed SQL
representation.
* Also, it requires touching the planner. This means to read the table's
current schema and decide what operations are needed.
* Concurrent operations for `ADD column IF NOT EXISTS` is not much different
from the existing`ADD column`. If the 2 concurrent jobs both commit the schema
change together, one of them will fail. This case doesn't appear to be legit
and will not be handled.
* Unit tests and documentation for the above solution.
* Adding a column with the name `IF` requires to be quoted.
was:
h3. Problem
The addition of _ALTER TABLE <table> ADD COLUMN_ to a Flink job makes the job
kind of non-idempotent. This means the job will run fine for the first time as
the column doesn't exist but later it'll fail with an exception until the ALTER
TABLE command is removed.
{code:java}
org.apache.flink.table.api.ValidationException: Failed to execute ALTER TABLE
statement.
Column `col_1` already exists in the table.
{code}
h3. Solution
A broader idea is to support something like ALTER TABLE <table> ADD [COLUMN] IF
NOT EXISTS pattern. This pattern is pretty common with many datastores like
Postgres, Cassandra, Clickhouse etc. The scope of this ticket is to keep it
restricted to COLUMN, but can be extended to ADD PARTITIONS etc
This involves the following work.
* The first is about extending Flink’s SQL grammar to support the IF NOT EXISTS
pattern for column addition. The `COLUMN` keyword following the `ADD` construct
is optional, so both of those patterns needs to be handled.
* This also involves preserving the guard (IF NOT EXISTS) in the parsed SQL
representation.
* Also, it requires touching the planner. This means to read the table's
current schema and decide what operations are needed.
* Concurrent operations for `ADD column IF NOT EXISTS` is not much different
from the existing`ADD column`. If the 2 concurrent jobs both commit the schema
change together, one of them will fail. This case doesn't appear to be legit
and will not be handled.
* Unit tests and documentation for the above solution.
> Support IF NOT EXISTS for ALTER TABLE ADD COLUMN
> ------------------------------------------------
>
> Key: FLINK-41363
> URL: https://issues.apache.org/jira/browse/FLINK-41363
> Project: Flink
> Issue Type: Improvement
> Components: Table SQL / API
> Reporter: Muhammad
> Priority: Major
> Labels: pull-request-available
>
> h3. Problem
> The addition of _ALTER TABLE <table> ADD COLUMN_ to a Flink job makes the
> job kind of non-idempotent. This means the job will run fine for the first
> time as the column doesn't exist but later it'll fail with an exception until
> the ALTER TABLE command is removed.
> {code:java}
> org.apache.flink.table.api.ValidationException: Failed to execute ALTER TABLE
> statement.
> Column `col_1` already exists in the table.
> {code}
> h3. Solution
> A broader idea is to support something like ALTER TABLE <table> ADD [COLUMN]
> IF NOT EXISTS pattern. This pattern is pretty common with many datastores
> like Postgres, Cassandra, Clickhouse etc. The scope of this ticket is to keep
> it restricted to COLUMN, but can be extended to ADD PARTITIONS etc
> This involves the following work.
> * The first is about extending Flink’s SQL grammar to support the IF NOT
> EXISTS pattern for column addition. The `COLUMN` keyword following the `ADD`
> construct is optional, so both of those patterns needs to be handled.
> * This also involves preserving the guard (IF NOT EXISTS) in the parsed SQL
> representation.
> * Also, it requires touching the planner. This means to read the table's
> current schema and decide what operations are needed.
> * Concurrent operations for `ADD column IF NOT EXISTS` is not much different
> from the existing`ADD column`. If the 2 concurrent jobs both commit the
> schema change together, one of them will fail. This case doesn't appear to be
> legit and will not be handled.
> * Unit tests and documentation for the above solution.
> * Adding a column with the name `IF` requires to be quoted.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
