You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by do...@apache.org on 2020/12/23 03:55:44 UTC
[spark] branch branch-3.1 updated: [SPARK-33877][SQL] SQL reference
documents for INSERT w/ a column list
This is an automated email from the ASF dual-hosted git repository.
dongjoon pushed a commit to branch branch-3.1
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.1 by this push:
new b33363b [SPARK-33877][SQL] SQL reference documents for INSERT w/ a column list
b33363b is described below
commit b33363b9132a5ae289f87c17b87bf7a13919f0bf
Author: Kent Yao <ya...@hotmail.com>
AuthorDate: Tue Dec 22 19:46:37 2020 -0800
[SPARK-33877][SQL] SQL reference documents for INSERT w/ a column list
We support a column list of INSERT for Spark v3.1.0 (See: SPARK-32976 (https://github.com/apache/spark/pull/29893)). So, this PR targets at documenting it in the SQL documents.
### What changes were proposed in this pull request?
improve doc
### Why are the changes needed?
### Does this PR introduce _any_ user-facing change?
doc
### How was this patch tested?
passing GA doc gen.
Closes #30888 from yaooqinn/SPARK-33877.
Authored-by: Kent Yao <ya...@hotmail.com>
Signed-off-by: Dongjoon Hyun <dh...@apple.com>
(cherry picked from commit a3dd8dacee8f6b316be90500f9fd8ec8997a5784)
Signed-off-by: Dongjoon Hyun <dh...@apple.com>
---
docs/sql-ref-syntax-dml-insert-into.md | 41 +++++++++++++++++++--
docs/sql-ref-syntax-dml-insert-overwrite-table.md | 43 +++++++++++++++++++++--
2 files changed, 80 insertions(+), 4 deletions(-)
diff --git a/docs/sql-ref-syntax-dml-insert-into.md b/docs/sql-ref-syntax-dml-insert-into.md
index 39d1580..96a95b1 100644
--- a/docs/sql-ref-syntax-dml-insert-into.md
+++ b/docs/sql-ref-syntax-dml-insert-into.md
@@ -26,7 +26,7 @@ The `INSERT INTO` statement inserts new rows into a table. The inserted rows can
### Syntax
```sql
-INSERT INTO [ TABLE ] table_identifier [ partition_spec ]
+INSERT INTO [ TABLE ] table_identifier [ partition_spec ] [ ( column_list ) ]
{ VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
```
@@ -40,11 +40,20 @@ INSERT INTO [ TABLE ] table_identifier [ partition_spec ]
* **partition_spec**
- An optional parameter that specifies a comma separated list of key and value pairs
+ An optional parameter that specifies a comma-separated list of key and value pairs
for partitions.
**Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
+* **column_list**
+
+ An optional parameter that specifies a comma-separated list of columns belonging to the `table_identifier` table.
+
+ **Note:**The current behaviour has some limitations:
+ - All specified columns should exist in the table and not be duplicated from each other. It includes all columns except the static partition columns.
+ - The size of the column list should be exactly the size of the data from `VALUES` clause or query.
+ - The order of the column list is alterable and determines how the data from `VALUES` clause or query to be inserted by position.
+
* **VALUES ( { value `|` NULL } [ , ... ] ) [ , ( ... ) ]**
Specifies the values to be inserted. Either an explicitly specified value or a NULL can be inserted.
@@ -198,6 +207,34 @@ SELECT * FROM students;
+-------------+--------------------------+----------+
```
+#### Insert with a column list
+
+```sql
+INSERT INTO students (address, name, student_id) VALUES
+ ('Hangzhou, China', 'Kent Yao', 11215016);
+
+SELECT * FROM students WHERE name = 'Kent Yao';
++---------+----------------------+----------+
+| name| address|student_id|
++---------+----------------------+----------+
+|Kent Yao | Hangzhou, China| 11215016|
++---------+----------------------+----------+
+```
+
+#### Insert with both a partition spec and a column list
+
+```sql
+INSERT INTO students PARTITION (student_id = 11215017) (address, name) VALUES
+ ('Hangzhou, China', 'Kent Yao Jr.');
+
+SELECT * FROM students WHERE student_id = 11215017;
++------------+----------------------+----------+
+| name| address|student_id|
++------------+----------------------+----------+
+|Kent Yao Jr.| Hangzhou, China| 11215017|
++------------+----------------------+----------+
+```
+
### Related Statements
* [INSERT OVERWRITE statement](sql-ref-syntax-dml-insert-overwrite-table.html)
diff --git a/docs/sql-ref-syntax-dml-insert-overwrite-table.md b/docs/sql-ref-syntax-dml-insert-overwrite-table.md
index 638dcb3..f2413fb 100644
--- a/docs/sql-ref-syntax-dml-insert-overwrite-table.md
+++ b/docs/sql-ref-syntax-dml-insert-overwrite-table.md
@@ -26,7 +26,7 @@ The `INSERT OVERWRITE` statement overwrites the existing data in the table using
### Syntax
```sql
-INSERT OVERWRITE [ TABLE ] table_identifier [ partition_spec [ IF NOT EXISTS ] ]
+INSERT OVERWRITE [ TABLE ] table_identifier [ partition_spec [ IF NOT EXISTS ] ] [ ( column_list ) ]
{ VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
```
@@ -40,11 +40,22 @@ INSERT OVERWRITE [ TABLE ] table_identifier [ partition_spec [ IF NOT EXISTS ] ]
* **partition_spec**
- An optional parameter that specifies a comma separated list of key and value pairs
+ An optional parameter that specifies a comma-separated list of key and value pairs
for partitions.
**Syntax:** `PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] )`
+* **column_list**
+
+ An optional parameter that specifies a comma-separated list of columns belonging to the `table_identifier` table.
+
+ **Note**
+
+ The current behaviour has some limitations:
+ - All specified columns should exist in the table and not be duplicated from each other. It includes all columns except the static partition columns.
+ - The size of the column list should be exactly the size of the data from `VALUES` clause or query.
+ - The order of the column list is alterable and determines how the data from `VALUES` clause or query to be inserted by position.
+
* **VALUES ( { value `|` NULL } [ , ... ] ) [ , ( ... ) ]**
Specifies the values to be inserted. Either an explicitly specified value or a NULL can be inserted.
@@ -169,6 +180,34 @@ SELECT * FROM students;
+-----------+-------------------------+----------+
```
+#### Insert with a column list
+
+```sql
+INSERT OVERWRITE students (address, name, student_id) VALUES
+ ('Hangzhou, China', 'Kent Yao', 11215016);
+
+SELECT * FROM students WHERE name = 'Kent Yao';
++---------+----------------------+----------+
+| name| address|student_id|
++---------+----------------------+----------+
+|Kent Yao | Hangzhou, China| 11215016|
++---------+----------------------+----------+
+```
+
+#### Insert with both a partition spec and a column list
+
+```sql
+INSERT OVERWRITE students PARTITION (student_id = 11215016) (address, name) VALUES
+ ('Hangzhou, China', 'Kent Yao Jr.');
+
+SELECT * FROM students WHERE student_id = 11215016;
++------------+----------------------+----------+
+| name| address|student_id|
++------------+----------------------+----------+
+|Kent Yao Jr.| Hangzhou, China| 11215016|
++------------+----------------------+----------+
+```
+
### Related Statements
* [INSERT INTO statement](sql-ref-syntax-dml-insert-into.html)
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org