[spark] branch branch-3.0 updated: [SPARK-30962][SQL][DOC] Documentation for Alter table command phase 2

yamamuro Tue, 10 Mar 2020 16:49:23 -0700

This is an automated email from the ASF dual-hosted git repository.

yamamuro pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/spark.git



The following commit(s) were added to refs/heads/branch-3.0 by this push:
     new b8e2cb3  [SPARK-30962][SQL][DOC] Documentation for Alter table command 
phase 2
b8e2cb3 is described below

commit b8e2cb32cbc75601d6d7a841362676cf2f273bda
Author: Qianyang Yu <[email protected]>
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 <[email protected]>
    Signed-off-by: Takeshi Yamamuro <[email protected]>
    (cherry picked from commit 0f54dc7c03ed975ecb7f776a0151b9325d21e85c)
    Signed-off-by: Takeshi Yamamuro <[email protected]>
---
 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: [email protected]
For additional commands, e-mail: [email protected]

[spark] branch branch-3.0 updated: [SPARK-30962][SQL][DOC] Documentation for Alter table command phase 2

Reply via email to