This is an automated email from the ASF dual-hosted git repository. yamamuro pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push: new 0f54dc7 [SPARK-30962][SQL][DOC] Documentation for Alter table command phase 2 0f54dc7 is described below commit 0f54dc7c03ed975ecb7f776a0151b9325d21e85c Author: Qianyang Yu <q...@us.ibm.com> AuthorDate: Wed Mar 11 08:47:30 2020 +0900 [SPARK-30962][SQL][DOC] Documentation for Alter table command phase 2 ### What changes were proposed in this pull request? ### Why are the changes needed? Based on [JIRA 30962](https://issues.apache.org/jira/browse/SPARK-30962), we want to add all the support `Alter Table` syntax for V1 table. ### Does this PR introduce any user-facing change? Yes ### How was this patch tested? Before: The documentation looks like [Alter Table](https://github.com/apache/spark/pull/25590) After: <img width="850" alt="Screen Shot 2020-03-03 at 2 02 23 PM" src="https://user-images.githubusercontent.com/7550280/75824837-168c7e00-5d59-11ea-9751-d1dab0f5a892.png"> <img width="977" alt="Screen Shot 2020-03-03 at 2 02 41 PM" src="https://user-images.githubusercontent.com/7550280/75824859-21dfa980-5d59-11ea-8b49-3adf6eb55fc6.png"> <img width="1028" alt="Screen Shot 2020-03-03 at 2 02 59 PM" src="https://user-images.githubusercontent.com/7550280/75824884-2e640200-5d59-11ea-81ef-d77d0a8efee2.png"> <img width="864" alt="Screen Shot 2020-03-03 at 2 03 14 PM" src="https://user-images.githubusercontent.com/7550280/75824910-39b72d80-5d59-11ea-84d0-bffa2499f086.png"> <img width="823" alt="Screen Shot 2020-03-03 at 2 03 28 PM" src="https://user-images.githubusercontent.com/7550280/75824937-45a2ef80-5d59-11ea-932c-314924856834.png"> <img width="811" alt="Screen Shot 2020-03-03 at 2 03 42 PM" src="https://user-images.githubusercontent.com/7550280/75824965-4cc9fd80-5d59-11ea-815b-8c1ebad310b1.png"> <img width="827" alt="Screen Shot 2020-03-03 at 2 03 53 PM" src="https://user-images.githubusercontent.com/7550280/75824978-518eb180-5d59-11ea-8a55-2fa26376b9c1.png"> <img width="783" alt="Screen Shot 2020-03-03 at 2 04 03 PM" src="https://user-images.githubusercontent.com/7550280/75825001-5bb0b000-5d59-11ea-8dd9-dcfbfa1b4330.png"> Notes: Those syntaxes are not supported by v1 Table. - `ALTER TABLE .. RENAME COLUMN` - `ALTER TABLE ... DROP (COLUMN | COLUMNS)` - `ALTER TABLE ... (ALTER | CHANGE) COLUMN? alterColumnAction` only support change comments, not other actions: `datatype, position, (SET | DROP) NOT NULL` - `ALTER TABLE .. CHANGE COLUMN?` - `ALTER TABLE .... REPLACE COLUMNS` - `ALTER TABLE ... RECOVER PARTITIONS` - Closes #27779 from kevinyu98/spark-30962-alterT. Authored-by: Qianyang Yu <q...@us.ibm.com> Signed-off-by: Takeshi Yamamuro <yamam...@apache.org> --- docs/sql-ref-syntax-ddl-alter-table.md | 213 ++++++++++++++++++++++++++++++++- 1 file changed, 210 insertions(+), 3 deletions(-) diff --git a/docs/sql-ref-syntax-ddl-alter-table.md b/docs/sql-ref-syntax-ddl-alter-table.md index 373fa8d..2dd808b 100644 --- a/docs/sql-ref-syntax-ddl-alter-table.md +++ b/docs/sql-ref-syntax-ddl-alter-table.md @@ -23,14 +23,13 @@ license: | `ALTER TABLE` statement changes the schema or properties of a table. ### RENAME -`ALTER TABLE RENAME` statement changes the table name of an existing table in the database. +`ALTER TABLE RENAME TO` statement changes the table name of an existing table in the database. #### Syntax {% highlight sql %} ALTER TABLE table_identifier RENAME TO table_identifier ALTER TABLE table_identifier partition_spec RENAME TO partition_spec - {% endhighlight %} #### Parameters @@ -83,6 +82,109 @@ ALTER TABLE table_identifier ADD COLUMNS ( col_spec [ , col_spec ... ] ) </dl> +### ALTER OR CHANGE COLUMN +`ALTER TABLE ALTER COLUMN` or `ALTER TABLE CHANGE COLUMN` statement changes column's comment. + +#### Syntax +{% highlight sql %} +ALTER TABLE table_identifier { ALTER | CHANGE } [ COLUMN ] col_spec alterColumnAction +{% endhighlight %} + +#### Parameters +<dl> + <dt><code><em>table_identifier</em></code></dt> + <dd> + Specifies a table name, which may be optionally qualified with a database name.<br><br> + <b>Syntax:</b> + <code> + [ database_name. ] table_name + </code> + </dd> +</dl> + +<dl> + <dt><code><em>COLUMN col_spec</em></code></dt> + <dd>Specifies the column to be altered or be changed.</dd> +</dl> + +<dl> + <dt><code><em>alterColumnAction</em></code></dt> + <dd> + Change the comment string.<br><br> + <b>Syntax:</b> + <code> + COMMENT STRING + </code> + </dd> +</dl> + + +### ADD AND DROP PARTITION + +#### ADD PARTITION +`ALTER TABLE ADD` statement adds partition to the partitioned table. + +##### Syntax +{% highlight sql %} +ALTER TABLE table_identifier ADD [IF NOT EXISTS] + ( partition_spec [ partition_spec ... ] ) +{% endhighlight %} + +##### Parameters +<dl> + <dt><code><em>table_identifier</em></code></dt> + <dd> + Specifies a table name, which may be optionally qualified with a database name.<br><br> + <b>Syntax:</b> + <code> + [ database_name. ] table_name + </code> + </dd> +</dl> + +<dl> + <dt><code><em>partition_spec</em></code></dt> + <dd> + Partition to be added. <br><br> + <b>Syntax:</b> + <code> + PARTITION ( partition_col_name = partition_col_val [ , ... ] ) + </code> + </dd> +</dl> + +#### DROP PARTITION +`ALTER TABLE DROP` statement drops the partition of the table. + +##### Syntax +{% highlight sql %} +ALTER TABLE table_identifier DROP [ IF EXISTS ] partition_spec [PURGE] +{% endhighlight %} + +##### Parameters +<dl> + <dt><code><em>table_identifier</em></code></dt> + <dd> + Specifies a table name, which may be optionally qualified with a database name.<br><br> + <b>Syntax:</b> + <code> + [ database_name. ] table_name + </code> + </dd> +</dl> + +<dl> + <dt><code><em>partition_spec</em></code></dt> + <dd> + Partition to be dropped. <br><br> + <b>Syntax:</b> + <code> + PARTITION ( partition_col_name = partition_col_val [ , ... ] ) + </code> + </dd> +</dl> + + ### SET AND UNSET #### SET TABLE PROPERTIES @@ -217,7 +319,7 @@ SHOW PARTITIONS StudentInfo; | age=15 | +------------+--+ --- Add new column to a table +-- Add new columns to a table DESC StudentInfo; +--------------------------+------------+----------+--+ @@ -248,6 +350,111 @@ DESC StudentInfo; | age | int | NULL | +--------------------------+------------+----------+--+ +-- Add a new partition to a table + +SHOW PARTITIONS StudentInfo; ++------------+--+ +| partition | ++------------+--+ +| age=11 | +| age=12 | +| age=15 | ++------------+--+ + +ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18); + +-- After adding a new partition to the table +SHOW PARTITIONS StudentInfo; ++------------+--+ +| partition | ++------------+--+ +| age=11 | +| age=12 | +| age=15 | +| age=18 | ++------------+--+ + +-- Drop a partition from the table + +SHOW PARTITIONS StudentInfo; ++------------+--+ +| partition | ++------------+--+ +| age=11 | +| age=12 | +| age=15 | +| age=18 | ++------------+--+ + +ALTER TABLE StudentInfo DROP IF EXISTS PARTITION (age=18); + +-- After dropping the partition of the table +SHOW PARTITIONS StudentInfo; ++------------+--+ +| partition | ++------------+--+ +| age=11 | +| age=12 | +| age=15 | ++------------+--+ + +-- Adding multiple partitions to the table + +SHOW PARTITIONS StudentInfo; ++------------+--+ +| partition | ++------------+--+ +| age=11 | +| age=12 | +| age=15 | ++------------+--+ + +ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18) PARTITION (age=20); + +-- After adding multiple partitions to the table +SHOW PARTITIONS StudentInfo; ++------------+--+ +| partition | ++------------+--+ +| age=11 | +| age=12 | +| age=15 | +| age=18 | +| age=20 | ++------------+--+ + +-- ALTER OR CHANGE COLUMNS + +DESC StudentInfo; ++--------------------------+------------+----------+--+ +| col_name | data_type | comment | ++--------------------------+------------+----------+--+ +| name | string | NULL | +| rollno | int | NULL | +| LastName | string | NULL | +| DOB | timestamp | NULL | +| age | int | NULL | +| # Partition Information | | | +| # col_name | data_type | comment | +| age | int | NULL | ++--------------------------+------------+----------+--+ + +ALTER TABLE StudentInfo ALTER COLUMN name COMMENT "new comment"; + +--After ALTER or CHANGE COLUMNS +DESC StudentInfo; ++--------------------------+------------+------------+--+ +| col_name | data_type | comment | ++--------------------------+------------+------------+--+ +| name | string | new comment| +| rollno | int | NULL | +| LastName | string | NULL | +| DOB | timestamp | NULL | +| age | int | NULL | +| # Partition Information | | | +| # col_name | data_type | comment | +| age | int | NULL | ++--------------------------+------------+------------+--+ --Change the fileformat ALTER TABLE loc_orc SET fileformat orc; --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org