You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "LadyForest (via GitHub)" <gi...@apache.org> on 2023/02/07 08:42:22 UTC
[GitHub] [flink] LadyForest commented on a diff in pull request #21647: [FLINK-22320][docs][table] Add documentation for new introduced ALTER TABLE statements
LadyForest commented on code in PR #21647:
URL: https://github.com/apache/flink/pull/21647#discussion_r1098343861
##########
docs/content/docs/dev/table/sql/alter.md:
##########
@@ -125,38 +179,258 @@ 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`. The default is to add the column 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
+);
+```
+
+### 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`.
+
+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.
+### DROP
+Use `DROP` clause to drop columns, primary key and watermark strategy to an existing table.
-* Set or Alter Table Properties
+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 `data` TO payload;
Review Comment:
> nit:
>
> ```
> `data` -> data
> ```
`DATA` is a reserved keyword, https://nightlies.apache.org/flink/flink-docs-release-1.16/docs/dev/table/sql/overview/
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@flink.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org