This is an automated email from the ASF dual-hosted git repository.
wenchen 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 3c9c858 [SPARK-37952][DOCS] Add missing statements to ALTER TABLE
document
3c9c858 is described below
commit 3c9c858d740b88792ac235d3813e44300d9f9591
Author: Yuto Akutsu <[email protected]>
AuthorDate: Wed Feb 9 17:38:52 2022 +0800
[SPARK-37952][DOCS] Add missing statements to ALTER TABLE document
### What changes were proposed in this pull request?
Add some missing statements to the ALTER TABLE document (which are mainly
supported with v2 table).
### Why are the changes needed?
To let users know those statements and how to use them.
### Does this PR introduce _any_ user-facing change?
Yes, docs changed.
### How was this patch tested?
`SKIP_API=1 bundle exec jekyll build`
Closes #35239 from yutoacts/SPARK-37952.
Authored-by: Yuto Akutsu <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
docs/sql-ref-syntax-ddl-alter-table.md | 250 ++++++++++++++++++++++++++++-----
1 file changed, 215 insertions(+), 35 deletions(-)
diff --git a/docs/sql-ref-syntax-ddl-alter-table.md
b/docs/sql-ref-syntax-ddl-alter-table.md
index 2d42eb4..566e73d 100644
--- a/docs/sql-ref-syntax-ddl-alter-table.md
+++ b/docs/sql-ref-syntax-ddl-alter-table.md
@@ -75,6 +75,52 @@ ALTER TABLE table_identifier ADD COLUMNS ( col_spec [ , ...
] )
Specifies the columns to be added.
+### DROP COLUMNS
+
+`ALTER TABLE DROP COLUMNS` statement drops mentioned columns from an existing
table.
+Note that this statement is only supported with v2 tables.
+
+#### Syntax
+
+```sql
+ALTER TABLE table_identifier DROP { COLUMN | COLUMNS } [ ( ] col_name [ , ...
] [ ) ]
+```
+
+#### Parameters
+
+* **table_identifier**
+
+ Specifies a table name, which may be optionally qualified with a database
name.
+
+ **Syntax:** `[ database_name. ] table_name`
+
+* **col_name**
+
+ Specifies the name of the column.
+
+### RENAME COLUMN
+
+`ALTER TABLE RENAME COLUMN` statement changes the column name of an existing
table.
+Note that this statement is only supported with v2 tables.
+
+#### Syntax
+
+```sql
+ALTER TABLE table_identifier RENAME COLUMN col_name TO col_name
+```
+
+#### Parameters
+
+* **table_identifier**
+
+ Specifies a table name, which may be optionally qualified with a database
name.
+
+ **Syntax:** `[ database_name. ] table_name`
+
+* **col_name**
+
+ Specifies the name of the column.
+
### ALTER OR CHANGE COLUMN
`ALTER TABLE ALTER COLUMN` or `ALTER TABLE CHANGE COLUMN` statement changes
column's definition.
@@ -82,7 +128,7 @@ ALTER TABLE table_identifier ADD COLUMNS ( col_spec [ , ...
] )
#### Syntax
```sql
-ALTER TABLE table_identifier { ALTER | CHANGE } [ COLUMN ] col_spec
alterColumnAction
+ALTER TABLE table_identifier { ALTER | CHANGE } [ COLUMN ] col_name
alterColumnAction
```
#### Parameters
@@ -93,14 +139,46 @@ ALTER TABLE table_identifier { ALTER | CHANGE } [ COLUMN ]
col_spec alterColumnA
**Syntax:** `[ database_name. ] table_name`
-* **COLUMNS ( col_spec )**
+* **col_name**
- Specifies the column to be altered or be changed.
+ Specifies the name of the column.
* **alterColumnAction**
Change column's definition.
+### REPLACE COLUMNS
+
+`ALTER TABLE REPLACE COLUMNS` statement removes all existing columns and adds
the new set of columns.
+Note that this statement is only supported with v2 tables.
+
+#### Syntax
+
+```sql
+ALTER TABLE table_identifier [ partition_spec ] REPLACE COLUMNS
+ [ ( ] qualified_col_type_with_position_list [ ) ]
+```
+
+#### Parameters
+
+* **table_identifier**
+
+ Specifies a table name, which may be optionally qualified with a database
name.
+
+ **Syntax:** `[ database_name. ] table_name`
+
+* **partition_spec**
+
+ Partition to be replaced. Note that one can use a typed literal (e.g.,
date'2019-01-02') in the partition spec.
+
+ **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
+
+* **qualified_col_type_with_position_list**
+
+ The list of the column(s) to be added
+
+ **Syntax:** `col_name col_type [ col_comment ] [ col_position ] [ , ... ]`
+
### ADD AND DROP PARTITION
#### ADD PARTITION
@@ -225,6 +303,25 @@ ALTER TABLE table_identifier [ partition_spec ] SET
LOCATION 'new_location'
Specifies the SERDE properties to be set.
+### RECOVER PARTITIONS
+
+`ALTER TABLE RECOVER PARTITIONS` statement recovers all the partitions in the
directory of a table and updates the Hive metastore.
+Another way to recover partitions is to use `MSCK REPAIR TABLE`.
+
+#### Syntax
+
+```sql
+ALTER TABLE table_identifier RECOVER PARTITIONS
+```
+
+#### Parameters
+
+* **table_identifier**
+
+ Specifies a table name, which may be optionally qualified with a database
name.
+
+ **Syntax:** `[ database_name. ] table_name`
+
### Examples
```sql
@@ -309,6 +406,118 @@ DESC StudentInfo;
| age| int| NULL|
+-----------------------+---------+-------+
+-- Drop columns of a table
+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 DROP columns (LastName, DOB);
+
+-- After dropping columns of the table
+DESC StudentInfo;
++-----------------------+---------+-------+
+| col_name|data_type|comment|
++-----------------------+---------+-------+
+| name| string| NULL|
+| rollno| int| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type|comment|
+| age| int| NULL|
++-----------------------+---------+-------+
+
+-- Rename a column of a table
+DESC StudentInfo;
++-----------------------+---------+-------+
+| col_name|data_type|comment|
++-----------------------+---------+-------+
+| name| string| NULL|
+| rollno| int| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type|comment|
+| age| int| NULL|
++-----------------------+---------+-------+
+
+ALTER TABLE StudentInfo RENAME COLUMN name TO FirstName;
+
+-- After renaming a column of the table
+DESC StudentInfo;
++-----------------------+---------+-------+
+| col_name|data_type|comment|
++-----------------------+---------+-------+
+| FirstName| string| NULL|
+| rollno| int| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type|comment|
+| age| int| NULL|
++-----------------------+---------+-------+
+
+-- ALTER OR CHANGE COLUMNS
+DESC StudentInfo;
++-----------------------+---------+-------+
+| col_name|data_type|comment|
++-----------------------+---------+-------+
+| FirstName| string| NULL|
+| rollno| int| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type|comment|
+| age| int| NULL|
++-----------------------+---------+-------+
+
+ALTER TABLE StudentInfo ALTER COLUMN FirstName COMMENT "new comment";
+
+-- After ALTER or CHANGE COLUMNS
+DESC StudentInfo;
++-----------------------+---------+-----------+
+| col_name|data_type| comment|
++-----------------------+---------+-----------+
+| FirstName| string|new comment|
+| rollno| int| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type| comment|
+| age| int| NULL|
++-----------------------+---------+-----------+
+
+-- REPLACE COLUMNS
+DESC StudentInfo;
++-----------------------+---------+-----------+
+| col_name|data_type| comment|
++-----------------------+---------+-----------+
+| FirstName| string|new comment|
+| rollno| int| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type| comment|
+| age| int| NULL|
++-----------------------+---------+-----------+
+
+ALTER TABLE StudentInfo REPLACE COLUMNS (name string, ID int COMMENT 'new
comment');
+
+-- After replacing COLUMNS
+DESC StudentInfo;
++-----=---------+---------+-----------+
+| col_name|data_type| comment|
++---------------+---------+-----------+
+| name| string| NULL|
+| ID| int|new comment|
+| # Partitioning| | |
+|Not partitioned| | |
++---------------+---------+-----------+
+
-- Add a new partition to a table
SHOW PARTITIONS StudentInfo;
+---------+
@@ -379,38 +588,6 @@ SHOW PARTITIONS StudentInfo;
| 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;
@@ -435,6 +612,9 @@ ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('comment' = 'This
is a new comment.');
-- DROP TABLE PROPERTIES
ALTER TABLE dbx.tab1 UNSET TBLPROPERTIES ('winner');
+
+-- RECOVER PARTITIONS
+ALTER TABLE dbx.tab1 RECOVER PARTITIONS;
```
### Related Statements
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
