You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@flink.apache.org by sh...@apache.org on 2023/02/07 11:41:23 UTC
[flink] branch release-1.17 updated: [FLINK-22320][docs][table] Add documentation for new introduced ALTER TABLE statements
This is an automated email from the ASF dual-hosted git repository.
shengkai pushed a commit to branch release-1.17
in repository https://gitbox.apache.org/repos/asf/flink.git
The following commit(s) were added to refs/heads/release-1.17 by this push:
new d2e927dc68d [FLINK-22320][docs][table] Add documentation for new introduced ALTER TABLE statements
d2e927dc68d is described below
commit d2e927dc68d8d31d50fbab876398adf9fb137537
Author: Jane Chan <qi...@gmail.com>
AuthorDate: Wed Jan 11 17:54:42 2023 +0800
[FLINK-22320][docs][table] Add documentation for new introduced ALTER TABLE statements
This closes #21647
---
docs/content.zh/docs/dev/table/sql/alter.md | 312 ++++++++++++++++++++++++++--
docs/content/docs/dev/table/sql/alter.md | 305 +++++++++++++++++++++++++--
2 files changed, 589 insertions(+), 28 deletions(-)
diff --git a/docs/content.zh/docs/dev/table/sql/alter.md b/docs/content.zh/docs/dev/table/sql/alter.md
index 63b40bb79c0..61a87858ec1 100644
--- a/docs/content.zh/docs/dev/table/sql/alter.md
+++ b/docs/content.zh/docs/dev/table/sql/alter.md
@@ -78,8 +78,26 @@ tableEnv.executeSql("CREATE TABLE Orders (`user` BIGINT, product STRING, amount
String[] tables = tableEnv.listTables();
// or tableEnv.executeSql("SHOW TABLES").print();
-// 把 “Orders” 的表名改为 “NewOrders”
-tableEnv.executeSql("ALTER TABLE Orders RENAME TO NewOrders;");
+// 新增列 `order` 并置于第一位
+tableEnv.executeSql("ALTER TABLE Orders ADD `order` INT COMMENT 'order identifier' FIRST");
+
+// 新增更多列, 以及主键和 watermark
+tableEnv.executeSql("ALTER TABLE Orders ADD (ts TIMESTAMP(3), category STRING AFTER product, PRIMARY KEY(`order`) NOT ENFORCED, WATERMARK FOR ts AS ts - INTERVAL '1' HOUR)");
+
+// 修改列类型, 注释及 watermark 策略
+tableEnv.executeSql("ALTER TABLE Orders MODIFY (amount DOUBLE NOT NULL, category STRING COMMENT 'category identifier' AFTER `order`, WATERMARK FOR ts AS ts)");
+
+// 删除 watermark
+tableEnv.executeSql("ALTER TABLE Orders DROP WATERMARK");
+
+// 删除列
+tableEnv.executeSql("ALTER TABLE Orders DROP (amount, ts, category)");
+
+// 重命名列
+tableEnv.executeSql("ALTER TABLE Orders RENAME `order` TO order_id");
+
+// "Orders" 的表名改为 "NewOrders"
+tableEnv.executeSql("ALTER TABLE Orders RENAME TO NewOrders");
// 字符串数组：["NewOrders"]
String[] tables = tableEnv.listTables();
@@ -93,12 +111,30 @@ val tableEnv = TableEnvironment.create(...)
// 注册名为 “Orders” 的表
tableEnv.executeSql("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)")
+// 新增列 `order` 并置于第一位
+tableEnv.executeSql("ALTER TABLE Orders ADD `order` INT COMMENT 'order identifier' FIRST")
+
+// 新增更多列, 以及主键和 watermark
+tableEnv.executeSql("ALTER TABLE Orders ADD (ts TIMESTAMP(3), category STRING AFTER product, PRIMARY KEY(`order`) NOT ENFORCED, WATERMARK FOR ts AS ts - INTERVAL '1' HOUR)")
+
+// 修改列类型, 注释, 以及主键和 watermark
+tableEnv.executeSql("ALTER TABLE Orders MODIFY (amount DOUBLE NOT NULL, category STRING COMMENT 'category identifier' AFTER `order`, WATERMARK FOR ts AS ts)")
+
+// 删除 watermark
+tableEnv.executeSql("ALTER TABLE Orders DROP WATERMARK")
+
+// 删除列
+tableEnv.executeSql("ALTER TABLE Orders DROP (amount, ts, category)")
+
+// 重命名列
+tableEnv.executeSql("ALTER TABLE Orders RENAME `order` TO order_id")
+
// 字符串数组： ["Orders"]
val tables = tableEnv.listTables()
// or tableEnv.executeSql("SHOW TABLES").print()
-// 把 “Orders” 的表名改为 “NewOrders”
-tableEnv.executeSql("ALTER TABLE Orders RENAME TO NewOrders;")
+// rename "Orders" to "NewOrders"
+tableEnv.executeSql("ALTER TABLE Orders RENAME TO NewOrders")
// 字符串数组：["NewOrders"]
val tables = tableEnv.listTables()
@@ -113,8 +149,26 @@ table_env = TableEnvironment.create(...)
tables = table_env.list_tables()
# or table_env.execute_sql("SHOW TABLES").print()
-# 把 “Orders” 的表名改为 “NewOrders”
-table_env.execute_sql("ALTER TABLE Orders RENAME TO NewOrders;")
+# 新增列 `order` 并置于第一位
+table_env.execute_sql("ALTER TABLE Orders ADD `order` INT COMMENT 'order identifier' FIRST");
+
+# 新增更多列, 主键及 watermark
+table_env.execute_sql("ALTER TABLE Orders ADD (ts TIMESTAMP(3), category STRING AFTER product, PRIMARY KEY(`order`) NOT ENFORCED, WATERMARK FOR ts AS ts - INTERVAL '1' HOUR)");
+
+# 修改列类型, 列注释, 主键及 watermark
+table_env.execute_sql("ALTER TABLE Orders MODIFY (amount DOUBLE NOT NULL, category STRING COMMENT 'category identifier' AFTER `order`, WATERMARK FOR ts AS ts)");
+
+# 删除 watermark
+table_env.execute_sql("ALTER TABLE Orders DROP WATERMARK");
+
+# 删除列
+table_env.execute_sql("ALTER TABLE Orders DROP (amount, ts, category)");
+
+# 重命名列
+table_env.execute_sql("ALTER TABLE Orders RENAME `order` TO order_id");
+
+# 把 "Orders" 的表名改为 "NewOrders"
+table_env.execute_sql("ALTER TABLE Orders RENAME TO NewOrders");
# 字符串数组：["NewOrders"]
tables = table_env.list_tables()
@@ -124,37 +178,259 @@ tables = table_env.list_tables()
{{< tab "SQL CLI" >}}
```sql
Flink SQL> CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...);
-[INFO] Table has been created.
+[INFO] Execute statement succeed.
+
+Flink SQL> ALTER TABLE Orders ADD `order` INT COMMENT 'order identifier' FIRST;
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++---------+--------+------+-----+--------+-----------+------------------+
+| name | type | null | key | extras | watermark | comment |
++---------+--------+------+-----+--------+-----------+------------------+
+| order | INT | TRUE | | | | order identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
+| amount | INT | TRUE | | | | |
++---------+--------+------+-----+--------+-----------+------------------+
+4 rows in set
+
+Flink SQL> ALTER TABLE Orders ADD (ts TIMESTAMP(3), category STRING AFTER product, PRIMARY KEY(`order`) NOT ENFORCED, WATERMARK FOR ts AS ts - INTERVAL '1' HOUR);
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++----------+------------------------+-------+------------+--------+--------------------------+------------------+
+| name | type | null | key | extras | watermark | comment |
++----------+------------------------+-------+------------+--------+--------------------------+------------------+
+| order | INT | FALSE | PRI(order) | | | order identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
+| category | STRING | TRUE | | | | |
+| amount | INT | TRUE | | | | |
+| ts | TIMESTAMP(3) *ROWTIME* | TRUE | | | `ts` - INTERVAL '1' HOUR | |
++----------+------------------------+-------+------------+--------+--------------------------+------------------+
+6 rows in set
+
+Flink SQL> ALTER TABLE Orders MODIFY (amount DOUBLE NOT NULL, category STRING COMMENT 'category identifier' AFTER `order`, WATERMARK FOR ts AS ts);
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++----------+------------------------+-------+------------+--------+-----------+---------------------+
+| name | type | null | key | extras | watermark | comment |
++----------+------------------------+-------+------------+--------+-----------+---------------------+
+| order | INT | FALSE | PRI(order) | | | order identifier |
+| category | STRING | TRUE | | | | category identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
+| amount | DOUBLE | FALSE | | | | |
+| ts | TIMESTAMP(3) *ROWTIME* | TRUE | | | `ts` | |
++----------+------------------------+-------+------------+--------+-----------+---------------------+
+6 rows in set
+
+Flink SQL> ALTER TABLE Orders DROP WATERMARK;
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++----------+--------------+-------+------------+--------+-----------+---------------------+
+| name | type | null | key | extras | watermark | comment |
++----------+--------------+-------+------------+--------+-----------+---------------------+
+| order | INT | FALSE | PRI(order) | | | order identifier |
+| category | STRING | TRUE | | | | category identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
+| amount | DOUBLE | FALSE | | | | |
+| ts | TIMESTAMP(3) | TRUE | | | | |
++----------+--------------+-------+------------+--------+-----------+---------------------+
+6 rows in set
+
+Flink SQL> ALTER TABLE Orders DROP (amount, ts, category);
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++---------+--------+-------+------------+--------+-----------+------------------+
+| name | type | null | key | extras | watermark | comment |
++---------+--------+-------+------------+--------+-----------+------------------+
+| order | INT | FALSE | PRI(order) | | | order identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
++---------+--------+-------+------------+--------+-----------+------------------+
+3 rows in set
+
+Flink SQL> ALTER TABLE Orders RENAME `order` to `order_id`;
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++----------+--------+-------+---------------+--------+-----------+------------------+
+| name | type | null | key | extras | watermark | comment |
++----------+--------+-------+---------------+--------+-----------+------------------+
+| order_id | INT | FALSE | PRI(order_id) | | | order identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
++----------+--------+-------+---------------+--------+-----------+------------------+
+3 rows in set
Flink SQL> SHOW TABLES;
-Orders
++------------+
+| table name |
++------------+
+| Orders |
++------------+
+1 row in set
Flink SQL> ALTER TABLE Orders RENAME TO NewOrders;
-[INFO] Table has been removed.
+[INFO] Execute statement succeed.
Flink SQL> SHOW TABLES;
-NewOrders
++------------+
+| table name |
++------------+
+| NewOrders |
++------------+
+1 row in set
```
{{< /tab >}}
{{< /tabs >}}
+{{< top >}}
+
## ALTER TABLE
-* 重命名表
+当前支持的 ALTER TABLE 语法如下
+```text
+ALTER TABLE [IF EXISTS] 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
+```
+
+**IF EXISTS**
+
+若表不存在，则不进行任何操作。
+
+### ADD
+使用 `ADD` 语句向已有表中增加 [columns]({{< ref "docs/dev/table/sql/create" >}}#columns)， [constraints]({{< ref "docs/dev/table/sql/create" >}}#primary-key)，[watermark]({{< ref "docs/dev/table/sql/create" >}}#watermark)。
+
+向表新增列时可通过 `FIRST` or `AFTER col_name` 指定位置，不指定位置时默认追加在最后。
+
+`ADD` 语句示例如下。
+
+```sql
+-- add a new column
+ALTER TABLE MyTable ADD category_id STRING COMMENT 'identifier of the category';
+
+-- 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
+);
+```
+<span class="label label-danger">注意</span> 指定列为主键列时会隐式修改该列的 nullability 为 false。
+
+### MODIFY
+使用 `MODIFY` 语句修改列的位置 、类型 、注释 、nullability，主键或 watermark。
+
+可使用 `FIRST` 或 `AFTER col_name` 将已有列移动至指定位置，不指定时默认保持位置不变。
+
+`MODIFY` 语句示例如下。
```sql
-ALTER TABLE [catalog_name.][db_name.]table_name RENAME TO new_table_name
+-- modify a column type, comment and position
+ALTER TABLE MyTable 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 MODIFY (
+ log_ts STRING COMMENT 'log timestamp string' AFTER `id`, -- reorder columns
+ ts AS TO_TIMESTAMP(log_ts) AFTER log_ts,
+ PRIMARY KEY (id) NOT ENFORCED,
+ WATERMARK FOR ts AS ts -- modify watermark strategy
+);
```
+<span class="label label-danger">注意</span> 指定列为主键列时会隐式修改该列的 nullability 为 false。
-把原有的表名更改为新的表名。
+### DROP
+使用 `DROP` 语句删除列 、主键或 watermark。
-* 设置或修改表属性
+`DROP` 语句示例如下。
```sql
-ALTER TABLE [catalog_name.][db_name.]table_name SET (key1=val1, key2=val2, ...)
+-- drop a column
+ALTER TABLE MyTable DROP measurement;
+
+-- drop columns
+ALTER TABLE MyTable DROP (col1, col2, col3);
+
+-- drop primary key
+ALTER TABLE MyTable DROP PRIMARY KEY;
+
+-- drop a watermark
+ALTER TABLE MyTable DROP WATERMARK;
```
-为指定的表设置一个或多个属性。若个别属性已经存在于表中，则使用新的值覆盖旧的值。
+### RENAME
+使用 `RENAME` 语句修改列名或表名。
+
+`RENAME` 语句示例如下。
+```sql
+-- rename column
+ALTER TABLE MyTable RENAME request_body TO payload;
+
+-- rename table
+ALTER TABLE MyTable RENAME TO MyTable2;
+```
+
+### SET
+
+为指定的表设置一个或多个属性。若个别属性已经存在于表中，则使用新值覆盖旧值。
+
+`SET` 语句示例如下。
+
+```sql
+-- set 'rows-per-second'
+ALTER TABLE DataGenSource SET ('rows-per-second' = '10');
+```
+
+### RESET
+
+为指定的表重置一个或多个属性。
+
+`RESET` 语句示例如下。
+
+```sql
+-- reset 'rows-per-second' to the default value
+ALTER TABLE DataGenSource RESET ('rows-per-second');
+```
+
+{{< top >}}
## ALTER VIEW
@@ -170,6 +446,8 @@ ALTER VIEW [catalog_name.][db_name.]view_name AS new_query_expression
Changes the underlying query defining the given view to a new query.
+{{< top >}}
+
## ALTER DATABASE
```sql
@@ -178,6 +456,8 @@ ALTER DATABASE [catalog_name.]db_name SET (key1=val1, key2=val2, ...)
在数据库中设置一个或多个属性。若个别属性已经在数据库中设定，将会使用新值覆盖旧值。
+{{< top >}}
+
## ALTER FUNCTION
```sql
diff --git a/docs/content/docs/dev/table/sql/alter.md b/docs/content/docs/dev/table/sql/alter.md
index 31ab5c5c8c0..fe42db40716 100644
--- a/docs/content/docs/dev/table/sql/alter.md
+++ b/docs/content/docs/dev/table/sql/alter.md
@@ -79,8 +79,26 @@ tableEnv.executeSql("CREATE TABLE Orders (`user` BIGINT, product STRING, amount
String[] tables = tableEnv.listTables();
// or tableEnv.executeSql("SHOW TABLES").print();
+// add a new column `order` to the first position
+tableEnv.executeSql("ALTER TABLE Orders ADD `order` INT COMMENT 'order identifier' FIRST");
+
+// add more columns, primary key and watermark
+tableEnv.executeSql("ALTER TABLE Orders ADD (ts TIMESTAMP(3), category STRING AFTER product, PRIMARY KEY(`order`) NOT ENFORCED, WATERMARK FOR ts AS ts - INTERVAL '1' HOUR)");
+
+// modify column type, column comment and watermark
+tableEnv.executeSql("ALTER TABLE Orders MODIFY (amount DOUBLE NOT NULL, category STRING COMMENT 'category identifier' AFTER `order`, WATERMARK FOR ts AS ts)");
+
+// drop watermark
+tableEnv.executeSql("ALTER TABLE Orders DROP WATERMARK");
+
+// drop column
+tableEnv.executeSql("ALTER TABLE Orders DROP (amount, ts, category)");
+
+// rename column
+tableEnv.executeSql("ALTER TABLE Orders RENAME `order` TO order_id");
+
// rename "Orders" to "NewOrders"
-tableEnv.executeSql("ALTER TABLE Orders RENAME TO NewOrders;");
+tableEnv.executeSql("ALTER TABLE Orders RENAME TO NewOrders");
// a string array: ["NewOrders"]
String[] tables = tableEnv.listTables();
@@ -94,12 +112,30 @@ val tableEnv = TableEnvironment.create(...)
// register a table named "Orders"
tableEnv.executeSql("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)")
+// add a new column `order` to the first position
+tableEnv.executeSql("ALTER TABLE Orders ADD `order` INT COMMENT 'order identifier' FIRST")
+
+// add more columns, primary key and watermark
+tableEnv.executeSql("ALTER TABLE Orders ADD (ts TIMESTAMP(3), category STRING AFTER product, PRIMARY KEY(`order`) NOT ENFORCED, WATERMARK FOR ts AS ts - INTERVAL '1' HOUR)")
+
+// modify column type, column comment and watermark
+tableEnv.executeSql("ALTER TABLE Orders MODIFY (amount DOUBLE NOT NULL, category STRING COMMENT 'category identifier' AFTER `order`, WATERMARK FOR ts AS ts)")
+
+// drop watermark
+tableEnv.executeSql("ALTER TABLE Orders DROP WATERMARK")
+
+// drop column
+tableEnv.executeSql("ALTER TABLE Orders DROP (amount, ts, category)")
+
+// rename column
+tableEnv.executeSql("ALTER TABLE Orders RENAME `order` TO order_id")
+
// a string array: ["Orders"]
val tables = tableEnv.listTables()
// or tableEnv.executeSql("SHOW TABLES").print()
// rename "Orders" to "NewOrders"
-tableEnv.executeSql("ALTER TABLE Orders RENAME TO NewOrders;")
+tableEnv.executeSql("ALTER TABLE Orders RENAME TO NewOrders")
// a string array: ["NewOrders"]
val tables = tableEnv.listTables()
@@ -114,8 +150,26 @@ table_env = TableEnvironment.create(...)
tables = table_env.list_tables()
# or table_env.execute_sql("SHOW TABLES").print()
+# add a new column `order` to the first position
+table_env.execute_sql("ALTER TABLE Orders ADD `order` INT COMMENT 'order identifier' FIRST");
+
+# add more columns, primary key and watermark
+table_env.execute_sql("ALTER TABLE Orders ADD (ts TIMESTAMP(3), category STRING AFTER product, PRIMARY KEY(`order`) NOT ENFORCED, WATERMARK FOR ts AS ts - INTERVAL '1' HOUR)");
+
+# modify column type, column comment and watermark
+table_env.execute_sql("ALTER TABLE Orders MODIFY (amount DOUBLE NOT NULL, category STRING COMMENT 'category identifier' AFTER `order`, WATERMARK FOR ts AS ts)");
+
+# drop watermark
+table_env.execute_sql("ALTER TABLE Orders DROP WATERMARK");
+
+# drop column
+table_env.execute_sql("ALTER TABLE Orders DROP (amount, ts, category)");
+
+# rename column
+table_env.execute_sql("ALTER TABLE Orders RENAME `order` TO order_id");
+
# rename "Orders" to "NewOrders"
-table_env.execute_sql("ALTER TABLE Orders RENAME TO NewOrders;")
+table_env.execute_sql("ALTER TABLE Orders RENAME TO NewOrders");
# a string array: ["NewOrders"]
tables = table_env.list_tables()
@@ -125,38 +179,261 @@ tables = table_env.list_tables()
{{< tab "SQL CLI" >}}
```sql
Flink SQL> CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...);
-[INFO] Table has been created.
+[INFO] Execute statement succeed.
+
+Flink SQL> ALTER TABLE Orders ADD `order` INT COMMENT 'order identifier' FIRST;
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++---------+--------+------+-----+--------+-----------+------------------+
+| name | type | null | key | extras | watermark | comment |
++---------+--------+------+-----+--------+-----------+------------------+
+| order | INT | TRUE | | | | order identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
+| amount | INT | TRUE | | | | |
++---------+--------+------+-----+--------+-----------+------------------+
+4 rows in set
+
+Flink SQL> ALTER TABLE Orders ADD (ts TIMESTAMP(3), category STRING AFTER product, PRIMARY KEY(`order`) NOT ENFORCED, WATERMARK FOR ts AS ts - INTERVAL '1' HOUR);
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++----------+------------------------+-------+------------+--------+--------------------------+------------------+
+| name | type | null | key | extras | watermark | comment |
++----------+------------------------+-------+------------+--------+--------------------------+------------------+
+| order | INT | FALSE | PRI(order) | | | order identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
+| category | STRING | TRUE | | | | |
+| amount | INT | TRUE | | | | |
+| ts | TIMESTAMP(3) *ROWTIME* | TRUE | | | `ts` - INTERVAL '1' HOUR | |
++----------+------------------------+-------+------------+--------+--------------------------+------------------+
+6 rows in set
+
+Flink SQL> ALTER TABLE Orders MODIFY (amount DOUBLE NOT NULL, category STRING COMMENT 'category identifier' AFTER `order`, WATERMARK FOR ts AS ts);
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++----------+------------------------+-------+------------+--------+-----------+---------------------+
+| name | type | null | key | extras | watermark | comment |
++----------+------------------------+-------+------------+--------+-----------+---------------------+
+| order | INT | FALSE | PRI(order) | | | order identifier |
+| category | STRING | TRUE | | | | category identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
+| amount | DOUBLE | FALSE | | | | |
+| ts | TIMESTAMP(3) *ROWTIME* | TRUE | | | `ts` | |
++----------+------------------------+-------+------------+--------+-----------+---------------------+
+6 rows in set
+
+Flink SQL> ALTER TABLE Orders DROP WATERMARK;
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++----------+--------------+-------+------------+--------+-----------+---------------------+
+| name | type | null | key | extras | watermark | comment |
++----------+--------------+-------+------------+--------+-----------+---------------------+
+| order | INT | FALSE | PRI(order) | | | order identifier |
+| category | STRING | TRUE | | | | category identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
+| amount | DOUBLE | FALSE | | | | |
+| ts | TIMESTAMP(3) | TRUE | | | | |
++----------+--------------+-------+------------+--------+-----------+---------------------+
+6 rows in set
+
+Flink SQL> ALTER TABLE Orders DROP (amount, ts, category);
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++---------+--------+-------+------------+--------+-----------+------------------+
+| name | type | null | key | extras | watermark | comment |
++---------+--------+-------+------------+--------+-----------+------------------+
+| order | INT | FALSE | PRI(order) | | | order identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
++---------+--------+-------+------------+--------+-----------+------------------+
+3 rows in set
+
+Flink SQL> ALTER TABLE Orders RENAME `order` to `order_id`;
+[INFO] Execute statement succeed.
+
+Flink SQL> DESCRIBE Orders;
++----------+--------+-------+---------------+--------+-----------+------------------+
+| name | type | null | key | extras | watermark | comment |
++----------+--------+-------+---------------+--------+-----------+------------------+
+| order_id | INT | FALSE | PRI(order_id) | | | order identifier |
+| user | BIGINT | TRUE | | | | |
+| product | STRING | TRUE | | | | |
++----------+--------+-------+---------------+--------+-----------+------------------+
+3 rows in set
Flink SQL> SHOW TABLES;
-Orders
++------------+
+| table name |
++------------+
+| Orders |
++------------+
+1 row in set
Flink SQL> ALTER TABLE Orders RENAME TO NewOrders;
-[INFO] Table has been removed.
+[INFO] Execute statement succeed.
Flink SQL> SHOW TABLES;
-NewOrders
++------------+
+| table name |
++------------+
+| NewOrders |
++------------+
+1 row in set
```
{{< /tab >}}
{{< /tabs >}}
+{{< top >}}
+
## ALTER TABLE
-* Rename Table
+The following grammar gives an overview about the available syntax:
+```text
+ALTER TABLE [IF EXISTS] 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
+```
+
+**IF EXISTS**
+
+If the table does not exist, nothing happens.
+
+### ADD
+Use `ADD` clause to add [columns]({{< ref "docs/dev/table/sql/create" >}}#columns), [constraints]({{< ref "docs/dev/table/sql/create" >}}#primary-key), [watermark]({{< ref "docs/dev/table/sql/create" >}}#watermark) to an existing table.
+
+To add a column at the specified position, use `FIRST` or `AFTER col_name`. By default, the column is appended at last.
+
+The following examples illustrate the usage of the `ADD` statements.
+
+```sql
+-- add a new column
+ALTER TABLE MyTable ADD category_id STRING COMMENT 'identifier of the category';
+
+-- 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
+);
+```
+<span class="label label-danger">Note</span> Add a column to be primary key will change the column's nullability to false implicitly.
+
+### MODIFY
+Use `MODIFY` clause to change column's position, type, comment or nullability, change primary key columns and watermark strategy to an existing table.
+
+To modify an existent column to a new position, use `FIRST` or `AFTER col_name`. By default, the position remains unchanged.
+
+The following examples illustrate the usage of the `MODIFY` statements.
```sql
-ALTER TABLE [catalog_name.][db_name.]table_name RENAME TO new_table_name
+-- modify a column type, comment and position
+ALTER TABLE MyTable 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 MODIFY (
+ log_ts STRING COMMENT 'log timestamp string' AFTER `id`, -- reorder columns
+ ts AS TO_TIMESTAMP(log_ts) AFTER log_ts,
+ PRIMARY KEY (id) NOT ENFORCED,
+ WATERMARK FOR ts AS ts -- modify watermark strategy
+);
```
-Rename the given table name to another new table name.
+<span class="label label-danger">Note</span> Modify a column to be primary key will change the column's nullability to false implicitly.
-* Set or Alter Table Properties
+### DROP
+Use `DROP` clause to drop columns, primary key and watermark strategy to an existing table.
+
+The following examples illustrate the usage of the `DROP` statements.
```sql
-ALTER TABLE [catalog_name.][db_name.]table_name SET (key1=val1, key2=val2, ...)
+-- drop a column
+ALTER TABLE MyTable DROP measurement;
+
+-- drop columns
+ALTER TABLE MyTable DROP (col1, col2, col3);
+
+-- drop primary key
+ALTER TABLE MyTable DROP PRIMARY KEY;
+
+-- drop a watermark
+ALTER TABLE MyTable DROP WATERMARK;
```
+### RENAME
+Use `RENAME` clause to rename column or an existing table.
+
+The following examples illustrate the usage of the `RENAME` statements.
+```sql
+-- rename column
+ALTER TABLE MyTable RENAME request_body TO payload;
+
+-- rename table
+ALTER TABLE MyTable RENAME TO MyTable2;
+```
+
+### SET
+
Set one or more properties in the specified table. If a particular property is already set in the table, override the old value with the new one.
+The following examples illustrate the usage of the `SET` statements.
+
+```sql
+-- set 'rows-per-second'
+ALTER TABLE DataGenSource SET ('rows-per-second' = '10');
+```
+
+### RESET
+
+Reset one or more properties to its default value.
+
+The following examples illustrate the usage of the `RESET` statements.
+
+```sql
+-- reset 'rows-per-second' to the default value
+ALTER TABLE DataGenSource RESET ('rows-per-second');
+```
+
+{{< top >}}
+
## ALTER VIEW
```sql
@@ -171,6 +448,8 @@ ALTER VIEW [catalog_name.][db_name.]view_name AS new_query_expression
Changes the underlying query defining the given view to a new query.
+{{< top >}}
+
## ALTER DATABASE
```sql
@@ -179,6 +458,8 @@ ALTER DATABASE [catalog_name.]db_name SET (key1=val1, key2=val2, ...)
Set one or more properties in the specified database. If a particular property is already set in the database, override the old value with the new one.
+{{< top >}}
+
## ALTER FUNCTION
```sql