You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ya...@apache.org on 2020/07/28 00:47:29 UTC
[spark] branch branch-3.0 updated: [SPARK-31753][SQL][DOCS] Add
missing keywords in the SQL docs
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 6ed93c3 [SPARK-31753][SQL][DOCS] Add missing keywords in the SQL docs
6ed93c3 is described below
commit 6ed93c3e86c60323328b44cab45faa9ae3050dab
Author: GuoPhilipse <gu...@126.com>
AuthorDate: Tue Jul 28 09:41:53 2020 +0900
[SPARK-31753][SQL][DOCS] Add missing keywords in the SQL docs
### What changes were proposed in this pull request?
update sql-ref docs, the following key words will be added in this PR.
CASE/ELSE
WHEN/THEN
MAP KEYS TERMINATED BY
NULL DEFINED AS
LINES TERMINATED BY
ESCAPED BY
COLLECTION ITEMS TERMINATED BY
PIVOT
LATERAL VIEW OUTER?
ROW FORMAT SERDE
ROW FORMAT DELIMITED
FIELDS TERMINATED BY
IGNORE NULLS
FIRST
LAST
### Why are the changes needed?
let more users know the sql key words usage
### Does this PR introduce _any_ user-facing change?
### How was this patch tested?
No
Closes #29056 from GuoPhilipse/add-missing-keywords.
Lead-authored-by: GuoPhilipse <gu...@126.com>
Co-authored-by: GuoPhilipse <46...@users.noreply.github.com>
Signed-off-by: Takeshi Yamamuro <ya...@apache.org>
(cherry picked from commit 8de43338be879f0cfeebca328dbbcfd1e5bd70da)
Signed-off-by: Takeshi Yamamuro <ya...@apache.org>
---
docs/_data/menu-sql.yaml | 6 +
docs/sql-ref-syntax-ddl-create-table-hiveformat.md | 94 +++++++++++++++-
docs/sql-ref-syntax-qry-select-case.md | 109 ++++++++++++++++++
docs/sql-ref-syntax-qry-select-clusterby.md | 3 +
docs/sql-ref-syntax-qry-select-distribute-by.md | 3 +
docs/sql-ref-syntax-qry-select-groupby.md | 27 +++++
docs/sql-ref-syntax-qry-select-having.md | 3 +
docs/sql-ref-syntax-qry-select-lateral-view.md | 125 +++++++++++++++++++++
docs/sql-ref-syntax-qry-select-limit.md | 3 +
docs/sql-ref-syntax-qry-select-orderby.md | 3 +
docs/sql-ref-syntax-qry-select-pivot.md | 101 +++++++++++++++++
docs/sql-ref-syntax-qry-select-sortby.md | 3 +
docs/sql-ref-syntax-qry-select-where.md | 3 +
docs/sql-ref-syntax-qry-select.md | 56 +++++----
docs/sql-ref-syntax-qry.md | 3 +
docs/sql-ref-syntax.md | 3 +
16 files changed, 520 insertions(+), 25 deletions(-)
diff --git a/docs/_data/menu-sql.yaml b/docs/_data/menu-sql.yaml
index eea657e..22fae0c 100644
--- a/docs/_data/menu-sql.yaml
+++ b/docs/_data/menu-sql.yaml
@@ -187,6 +187,12 @@
url: sql-ref-syntax-qry-select-tvf.html
- text: Window Function
url: sql-ref-syntax-qry-select-window.html
+ - text: CASE Clause
+ url: sql-ref-syntax-qry-select-case.html
+ - text: LATERAL VIEW Clause
+ url: sql-ref-syntax-qry-select-lateral-view.html
+ - text: PIVOT Clause
+ url: sql-ref-syntax-qry-select-pivot.html
- text: EXPLAIN
url: sql-ref-syntax-qry-explain.html
- text: Auxiliary Statements
diff --git a/docs/sql-ref-syntax-ddl-create-table-hiveformat.md b/docs/sql-ref-syntax-ddl-create-table-hiveformat.md
index 38f8856..7bf847d 100644
--- a/docs/sql-ref-syntax-ddl-create-table-hiveformat.md
+++ b/docs/sql-ref-syntax-ddl-create-table-hiveformat.md
@@ -36,6 +36,14 @@ CREATE [ EXTERNAL ] TABLE [ IF NOT EXISTS ] table_identifier
[ LOCATION path ]
[ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]
[ AS select_statement ]
+
+row_format:
+ : SERDE serde_class [ WITH SERDEPROPERTIES (k1=v1, k2=v2, ... ) ]
+ | DELIMITED [ FIELDS TERMINATED BY fields_termiated_char [ ESCAPED BY escaped_char ] ]
+ [ COLLECTION ITEMS TERMINATED BY collection_items_termiated_char ]
+ [ MAP KEYS TERMINATED BY map_key_termiated_char ]
+ [ LINES TERMINATED BY row_termiated_char ]
+ [ NULL DEFINED AS null_char ]
```
Note that, the clauses between the columns definition clause and the AS SELECT clause can come in
@@ -51,15 +59,55 @@ as any order. For example, you can write COMMENT table_comment after TBLPROPERTI
* **EXTERNAL**
- Table is defined using the path provided as LOCATION, does not use default location for this table.
+ Table is defined using the path provided as `LOCATION`, does not use default location for this table.
* **PARTITIONED BY**
Partitions are created on the table, based on the columns specified.
+
+* **row_format**
+
+ Use the `SERDE` clause to specify a custom SerDe for one table. Otherwise, use the `DELIMITED` clause to use the native SerDe and specify the delimiter, escape character, null character and so on.
+
+* **SERDE**
+
+ Specifies a custom SerDe for one table.
+
+* **serde_class**
+
+ Specifies a fully-qualified class name of a custom SerDe.
+
+* **SERDEPROPERTIES**
+
+ A list of key-value pairs that is used to tag the SerDe definition.
+
+* **DELIMITED**
+
+ The `DELIMITED` clause can be used to specify the native SerDe and state the delimiter, escape character, null character and so on.
+
+* **FIELDS TERMINATED BY**
-* **ROW FORMAT**
+ Used to define a column separator.
+
+* **COLLECTION ITEMS TERMINATED BY**
- SERDE is used to specify a custom SerDe or the DELIMITED clause in order to use the native SerDe.
+ Used to define a collection item separator.
+
+* **MAP KEYS TERMINATED BY**
+
+ Used to define a map key separator.
+
+* **LINES TERMINATED BY**
+
+ Used to define a row separator.
+
+* **NULL DEFINED AS**
+
+ Used to define the specific value for NULL.
+
+* **ESCAPED BY**
+
+ Used for escape mechanism.
* **STORED AS**
@@ -114,9 +162,47 @@ CREATE TABLE student (id INT, name STRING)
PARTITIONED BY (age INT);
--Use Row Format and file format
-CREATE TABLE student (id INT,name STRING)
+CREATE TABLE student (id INT, name STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
+
+--Use complex datatype
+CREATE EXTERNAL TABLE family(
+ name STRING,
+ friends ARRAY<STRING>,
+ children MAP<STRING, INT>,
+ address STRUCT<street: STRING, city: STRING>
+ )
+ ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'
+ COLLECTION ITEMS TERMINATED BY '_'
+ MAP KEYS TERMINATED BY ':'
+ LINES TERMINATED BY '\n'
+ NULL DEFINED AS 'foonull'
+ STORED AS TEXTFILE
+ LOCATION '/tmp/family/';
+
+--Use predefined custom SerDe
+CREATE TABLE avroExample
+ ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
+ STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
+ OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
+ TBLPROPERTIES ('avro.schema.literal'='{ "namespace": "org.apache.hive",
+ "name": "first_schema",
+ "type": "record",
+ "fields": [
+ { "name":"string1", "type":"string" },
+ { "name":"string2", "type":"string" }
+ ] }');
+
+--Use personalized custom SerDe(we may need to `ADD JAR xxx.jar` first to ensure we can find the serde_class,
+--or you may run into `CLASSNOTFOUND` exception)
+ADD JAR /tmp/hive_serde_example.jar;
+
+CREATE EXTERNAL TABLE family (id INT, name STRING)
+ ROW FORMAT SERDE 'com.ly.spark.serde.SerDeExample'
+ STORED AS INPUTFORMAT 'com.ly.spark.example.serde.io.SerDeExampleInputFormat'
+ OUTPUTFORMAT 'com.ly.spark.example.serde.io.SerDeExampleOutputFormat'
+ LOCATION '/tmp/family/';
```
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-case.md b/docs/sql-ref-syntax-qry-select-case.md
new file mode 100644
index 0000000..6136b16
--- /dev/null
+++ b/docs/sql-ref-syntax-qry-select-case.md
@@ -0,0 +1,109 @@
+---
+layout: global
+title: CASE Clause
+displayTitle: CASE Clause
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+`CASE` clause uses a rule to return a specific result based on the specified condition, similar to if/else statements in other programming languages.
+
+### Syntax
+
+```sql
+CASE [ expression ] { WHEN boolean_expression THEN then_expression } [ ... ]
+ [ ELSE else_expression ]
+END
+```
+
+### Parameters
+
+* **boolean_expression**
+
+ Specifies any expression that evaluates to a result type `boolean`. Two or
+ more expressions may be combined together using the logical
+ operators ( `AND`, `OR` ).
+
+* **then_expression**
+
+ Specifies the then expression based on the `boolean_expression` condition; `then_expression` and `else_expression` should all be same type or coercible to a common type.
+
+* **else_expression**
+
+ Specifies the default expression; `then_expression` and `else_expression` should all be same type or coercible to a common type.
+
+### Examples
+
+```sql
+CREATE TABLE person (id INT, name STRING, age INT);
+INSERT INTO person VALUES
+ (100, 'John', 30),
+ (200, 'Mary', NULL),
+ (300, 'Mike', 80),
+ (400, 'Dan', 50);
+
+SELECT id, CASE WHEN id > 200 THEN 'bigger' ELSE 'small' END FROM person;
++------+--------------------------------------------------+
+| id | CASE WHEN (id > 200) THEN bigger ELSE small END |
++------+--------------------------------------------------+
+| 100 | small |
+| 200 | small |
+| 300 | bigger |
+| 400 | bigger |
++------+--------------------------------------------------+
+
+SELECT id, CASE id WHEN 100 then 'bigger' WHEN id > 300 THEN '300' ELSE 'small' END FROM person;
++------+-----------------------------------------------------------------------------------------------+
+| id | CASE WHEN (id = 100) THEN bigger WHEN (id = CAST((id > 300) AS INT)) THEN 300 ELSE small END |
++------+-----------------------------------------------------------------------------------------------+
+| 100 | bigger |
+| 200 | small |
+| 300 | small |
+| 400 | small |
++------+-----------------------------------------------------------------------------------------------+
+
+SELECT * FROM person
+ WHERE
+ CASE 1 = 1
+ WHEN 100 THEN 'big'
+ WHEN 200 THEN 'bigger'
+ WHEN 300 THEN 'biggest'
+ ELSE 'small'
+ END = 'small';
++------+-------+-------+
+| id | name | age |
++------+-------+-------+
+| 100 | John | 30 |
+| 200 | Mary | NULL |
+| 300 | Mike | 80 |
+| 400 | Dan | 50 |
++------+-------+-------+
+```
+
+### Related Statements
+
+* [SELECT Main](sql-ref-syntax-qry-select.html)
+* [WHERE Clause](sql-ref-syntax-qry-select-where.html)
+* [GROUP BY Clause](sql-ref-syntax-qry-select-groupby.html)
+* [HAVING Clause](sql-ref-syntax-qry-select-having.html)
+* [ORDER BY Clause](sql-ref-syntax-qry-select-orderby.html)
+* [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
+* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
+* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
diff --git a/docs/sql-ref-syntax-qry-select-clusterby.md b/docs/sql-ref-syntax-qry-select-clusterby.md
index e3bd2ed..9bcfac5 100644
--- a/docs/sql-ref-syntax-qry-select-clusterby.md
+++ b/docs/sql-ref-syntax-qry-select-clusterby.md
@@ -99,3 +99,6 @@ SELECT age, name FROM person CLUSTER BY age;
* [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
diff --git a/docs/sql-ref-syntax-qry-select-distribute-by.md b/docs/sql-ref-syntax-qry-select-distribute-by.md
index 1fdfb91..fbf662d 100644
--- a/docs/sql-ref-syntax-qry-select-distribute-by.md
+++ b/docs/sql-ref-syntax-qry-select-distribute-by.md
@@ -94,3 +94,6 @@ SELECT age, name FROM person DISTRIBUTE BY age;
* [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
* [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
\ No newline at end of file
diff --git a/docs/sql-ref-syntax-qry-select-groupby.md b/docs/sql-ref-syntax-qry-select-groupby.md
index bd9377e..6137c0d 100644
--- a/docs/sql-ref-syntax-qry-select-groupby.md
+++ b/docs/sql-ref-syntax-qry-select-groupby.md
@@ -260,6 +260,30 @@ SELECT city, car_model, sum(quantity) AS sum FROM dealer
| San Jose| HondaAccord| 8|
| San Jose| HondaCivic| 5|
+---------+------------+---+
+
+--Prepare data for ignore nulls example
+CREATE TABLE person (id INT, name STRING, age INT);
+INSERT INTO person VALUES
+ (100, 'Mary', NULL),
+ (200, 'John', 30),
+ (300, 'Mike', 80),
+ (400, 'Dan', 50);
+
+--Select the first row in cloumn age
+SELECT FIRST(age) FROM person;
++--------------------+
+| first(age, false) |
++--------------------+
+| NULL |
++--------------------+
+
+--Get the first row in cloumn `age` ignore nulls,last row in column `id` and sum of cloumn `id`.
+SELECT FIRST(age IGNORE NULLS), LAST(id), SUM(id) FROM person;
++-------------------+------------------+----------+
+| first(age, true) | last(id, false) | sum(id) |
++-------------------+------------------+----------+
+| 30 | 400 | 1000 |
++-------------------+------------------+----------+
```
### Related Statements
@@ -272,3 +296,6 @@ SELECT city, car_model, sum(quantity) AS sum FROM dealer
* [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
diff --git a/docs/sql-ref-syntax-qry-select-having.md b/docs/sql-ref-syntax-qry-select-having.md
index 935782c..59a8c68 100644
--- a/docs/sql-ref-syntax-qry-select-having.md
+++ b/docs/sql-ref-syntax-qry-select-having.md
@@ -125,3 +125,6 @@ SELECT sum(quantity) AS sum FROM dealer HAVING sum(quantity) > 10;
* [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
diff --git a/docs/sql-ref-syntax-qry-select-lateral-view.md b/docs/sql-ref-syntax-qry-select-lateral-view.md
new file mode 100644
index 0000000..f742c8f
--- /dev/null
+++ b/docs/sql-ref-syntax-qry-select-lateral-view.md
@@ -0,0 +1,125 @@
+---
+layout: global
+title: LATERAL VIEW Clause
+displayTitle: LATERAL VIEW Clause
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `LATERAL VIEW` clause is used in conjunction with generator functions such as `EXPLODE`, which will generate a virtual table containing one or more rows. `LATERAL VIEW` will apply the rows to each original output row.
+
+### Syntax
+
+```sql
+LATERAL VIEW [ OUTER ] generator_function ( expression [ , ... ] ) [ table_alias ] AS column_alias [ , ... ]
+```
+
+### Parameters
+
+* **OUTER**
+
+ If `OUTER` specified, returns null if an input array/map is empty or null.
+
+* **generator_function**
+
+ Specifies a generator function (EXPLODE, INLINE, etc.).
+
+* **table_alias**
+
+ The alias for `generator_function`, which is optional.
+
+* **column_alias**
+
+ Lists the column aliases of `generator_function`, which may be used in output rows. We may have multiple aliases if `generator_function` have multiple output columns.
+
+### Examples
+
+```sql
+CREATE TABLE person (id INT, name STRING, age INT, class INT, address STRING);
+INSERT INTO person VALUES
+ (100, 'John', 30, 1, 'Street 1'),
+ (200, 'Mary', NULL, 1, 'Street 2'),
+ (300, 'Mike', 80, 3, 'Street 3'),
+ (400, 'Dan', 50, 4, 'Street 4');
+
+SELECT * FROM person
+ LATERAL VIEW EXPLODE(ARRAY(30, 60)) tabelName AS c_age
+ LATERAL VIEW EXPLODE(ARRAY(40, 80)) AS d_age;
++------+-------+-------+--------+-----------+--------+--------+
+| id | name | age | class | address | c_age | d_age |
++------+-------+-------+--------+-----------+--------+--------+
+| 100 | John | 30 | 1 | Street 1 | 30 | 40 |
+| 100 | John | 30 | 1 | Street 1 | 30 | 80 |
+| 100 | John | 30 | 1 | Street 1 | 60 | 40 |
+| 100 | John | 30 | 1 | Street 1 | 60 | 80 |
+| 200 | Mary | NULL | 1 | Street 2 | 30 | 40 |
+| 200 | Mary | NULL | 1 | Street 2 | 30 | 80 |
+| 200 | Mary | NULL | 1 | Street 2 | 60 | 40 |
+| 200 | Mary | NULL | 1 | Street 2 | 60 | 80 |
+| 300 | Mike | 80 | 3 | Street 3 | 30 | 40 |
+| 300 | Mike | 80 | 3 | Street 3 | 30 | 80 |
+| 300 | Mike | 80 | 3 | Street 3 | 60 | 40 |
+| 300 | Mike | 80 | 3 | Street 3 | 60 | 80 |
+| 400 | Dan | 50 | 4 | Street 4 | 30 | 40 |
+| 400 | Dan | 50 | 4 | Street 4 | 30 | 80 |
+| 400 | Dan | 50 | 4 | Street 4 | 60 | 40 |
+| 400 | Dan | 50 | 4 | Street 4 | 60 | 80 |
++------+-------+-------+--------+-----------+--------+--------+
+
+SELECT c_age, COUNT(1) FROM person
+ LATERAL VIEW EXPLODE(ARRAY(30, 60)) AS c_age
+ LATERAL VIEW EXPLODE(ARRAY(40, 80)) AS d_age
+GROUP BY c_age;
++--------+-----------+
+| c_age | count(1) |
++--------+-----------+
+| 60 | 8 |
+| 30 | 8 |
++--------+-----------+
+
+SELECT * FROM person
+ LATERAL VIEW EXPLODE(ARRAY()) tabelName AS c_age;
++-----+-------+------+--------+----------+--------+
+| id | name | age | class | address | c_age |
++-----+-------+------+--------+----------+--------+
++-----+-------+------+--------+----------+--------+
+
+SELECT * FROM person
+ LATERAL VIEW OUTER EXPLODE(ARRAY()) tabelName AS c_age;
++------+-------+-------+--------+-----------+--------+
+| id | name | age | class | address | c_age |
++------+-------+-------+--------+-----------+--------+
+| 100 | John | 30 | 1 | Street 1 | NULL |
+| 200 | Mary | NULL | 1 | Street 2 | NULL |
+| 300 | Mike | 80 | 3 | Street 3 | NULL |
+| 400 | Dan | 50 | 4 | Street 4 | NULL |
++------+-------+-------+--------+-----------+--------+
+```
+
+### Related Statements
+
+* [SELECT Main](sql-ref-syntax-qry-select.html)
+* [WHERE Clause](sql-ref-syntax-qry-select-where.html)
+* [GROUP BY Clause](sql-ref-syntax-qry-select-groupby.html)
+* [HAVING Clause](sql-ref-syntax-qry-select-having.html)
+* [ORDER BY Clause](sql-ref-syntax-qry-select-orderby.html)
+* [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
+* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
+* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
diff --git a/docs/sql-ref-syntax-qry-select-limit.md b/docs/sql-ref-syntax-qry-select-limit.md
index 03c4df3..bd64ba8 100644
--- a/docs/sql-ref-syntax-qry-select-limit.md
+++ b/docs/sql-ref-syntax-qry-select-limit.md
@@ -104,3 +104,6 @@ org.apache.spark.sql.AnalysisException: The limit expression must evaluate to a
* [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
* [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
diff --git a/docs/sql-ref-syntax-qry-select-orderby.md b/docs/sql-ref-syntax-qry-select-orderby.md
index 85bbe51..13f0ae4 100644
--- a/docs/sql-ref-syntax-qry-select-orderby.md
+++ b/docs/sql-ref-syntax-qry-select-orderby.md
@@ -143,3 +143,6 @@ SELECT * FROM person ORDER BY name ASC, age DESC;
* [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
diff --git a/docs/sql-ref-syntax-qry-select-pivot.md b/docs/sql-ref-syntax-qry-select-pivot.md
new file mode 100644
index 0000000..649c251
--- /dev/null
+++ b/docs/sql-ref-syntax-qry-select-pivot.md
@@ -0,0 +1,101 @@
+---
+layout: global
+title: PIVOT Clause
+displayTitle: PIVOT Clause
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `PIVOT` clause is used for data perspective. We can get the aggregated values based on specific column values, which will be turned to multiple columns used in `SELECT` clause. The `PIVOT` clause can be specified after the table name or subquery.
+
+### Syntax
+
+```sql
+PIVOT ( { aggregate_expression [ AS aggregate_expression_alias ] } [ , ... ]
+ FOR column_list IN ( expression_list ) )
+```
+
+### Parameters
+
+* **aggregate_expression**
+
+ Specifies an aggregate expression (SUM(a), COUNT(DISTINCT b), etc.).
+
+* **aggregate_expression_alias**
+
+ Specifies an alias for the aggregate expression.
+
+* **column_list**
+
+ Contains columns in the `FROM` clause, which specifies the columns we want to replace with new columns. We can use brackets to surround the columns, such as `(c1, c2)`.
+
+* **expression_list**
+
+ Specifies new columns, which are used to match values in `column_list` as the aggregating condition. We can also add aliases for them.
+
+### Examples
+
+```sql
+CREATE TABLE person (id INT, name STRING, age INT, class INT, address STRING);
+INSERT INTO person VALUES
+ (100, 'John', 30, 1, 'Street 1'),
+ (200, 'Mary', NULL, 1, 'Street 2'),
+ (300, 'Mike', 80, 3, 'Street 3'),
+ (400, 'Dan', 50, 4, 'Street 4');
+
+SELECT * FROM person
+ PIVOT (
+ SUM(age) AS a, AVG(class) AS c
+ FOR name IN ('John' AS john, 'Mike' AS mike)
+ );
++------+-----------+---------+---------+---------+---------+
+| id | address | john_a | john_c | mike_a | mike_c |
++------+-----------+---------+---------+---------+---------+
+| 200 | Street 2 | NULL | NULL | NULL | NULL |
+| 100 | Street 1 | 30 | 1.0 | NULL | NULL |
+| 300 | Street 3 | NULL | NULL | 80 | 3.0 |
+| 400 | Street 4 | NULL | NULL | NULL | NULL |
++------+-----------+---------+---------+---------+---------+
+
+SELECT * FROM person
+ PIVOT (
+ SUM(age) AS a, AVG(class) AS c
+ FOR (name, age) IN (('John', 30) AS c1, ('Mike', 40) AS c2)
+ );
++------+-----------+-------+-------+-------+-------+
+| id | address | c1_a | c1_c | c2_a | c2_c |
++------+-----------+-------+-------+-------+-------+
+| 200 | Street 2 | NULL | NULL | NULL | NULL |
+| 100 | Street 1 | 30 | 1.0 | NULL | NULL |
+| 300 | Street 3 | NULL | NULL | NULL | NULL |
+| 400 | Street 4 | NULL | NULL | NULL | NULL |
++------+-----------+-------+-------+-------+-------+
+```
+
+### Related Statements
+
+* [SELECT Main](sql-ref-syntax-qry-select.html)
+* [WHERE Clause](sql-ref-syntax-qry-select-where.html)
+* [GROUP BY Clause](sql-ref-syntax-qry-select-groupby.html)
+* [HAVING Clause](sql-ref-syntax-qry-select-having.html)
+* [ORDER BY Clause](sql-ref-syntax-qry-select-orderby.html)
+* [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
+* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
+* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
diff --git a/docs/sql-ref-syntax-qry-select-sortby.md b/docs/sql-ref-syntax-qry-select-sortby.md
index 554bdb5..09e559a 100644
--- a/docs/sql-ref-syntax-qry-select-sortby.md
+++ b/docs/sql-ref-syntax-qry-select-sortby.md
@@ -176,3 +176,6 @@ SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person
* [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
diff --git a/docs/sql-ref-syntax-qry-select-where.md b/docs/sql-ref-syntax-qry-select-where.md
index ca3f5ec..9ff7993 100644
--- a/docs/sql-ref-syntax-qry-select-where.md
+++ b/docs/sql-ref-syntax-qry-select-where.md
@@ -125,3 +125,6 @@ SELECT * FROM person AS parent
* [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
* [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
* [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
\ No newline at end of file
diff --git a/docs/sql-ref-syntax-qry-select.md b/docs/sql-ref-syntax-qry-select.md
index 987e647..453737a 100644
--- a/docs/sql-ref-syntax-qry-select.md
+++ b/docs/sql-ref-syntax-qry-select.md
@@ -31,10 +31,10 @@ of a query along with examples.
```sql
[ WITH with_query [ , ... ] ]
select_statement [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select_statement, ... ]
- [ ORDER BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ...] } ]
- [ SORT BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ...] } ]
- [ CLUSTER BY { expression [ , ...] } ]
- [ DISTRIBUTE BY { expression [, ...] } ]
+ [ ORDER BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] } ]
+ [ SORT BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] } ]
+ [ CLUSTER BY { expression [ , ... ] } ]
+ [ DISTRIBUTE BY { expression [, ... ] } ]
[ WINDOW { named_window [ , WINDOW named_window, ... ] } ]
[ LIMIT { ALL | expression } ]
```
@@ -42,9 +42,11 @@ select_statement [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select_stat
While `select_statement` is defined as
```sql
SELECT [ hints , ... ] [ ALL | DISTINCT ] { named_expression [ , ... ] }
- FROM { from_item [ , ...] }
+ FROM { from_item [ , ... ] }
+ [ PIVOT clause ]
+ [ LATERAL VIEW clause ] [ ... ]
[ WHERE boolean_expression ]
- [ GROUP BY expression [ , ...] ]
+ [ GROUP BY expression [ , ... ] ]
[ HAVING boolean_expression ]
```
@@ -75,7 +77,7 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { named_expression [ , ... ] }
**Syntax:** `expression [AS] [alias]`
- * **from_item**
+* **from_item**
Specifies a source of input for the query. It can be one of the following:
* Table relation
@@ -83,62 +85,71 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { named_expression [ , ... ] }
* [Table-value function](sql-ref-syntax-qry-select-tvf.html)
* [Inline table](sql-ref-syntax-qry-select-inline-table.html)
* Subquery
+
+* **PIVOT**
+ The `PIVOT` clause is used for data perspective; We can get the aggregated values based on specific column value.
- * **WHERE**
+* **LATERAL VIEW**
+
+ The `LATERAL VIEW` clause is used in conjunction with generator functions such as `EXPLODE`, which will generate a virtual table containing one or more rows. `LATERAL VIEW` will apply the rows to each original output row.
+
+* **WHERE**
Filters the result of the FROM clause based on the supplied predicates.
- * **GROUP BY**
+* **GROUP BY**
Specifies the expressions that are used to group the rows. This is used in conjunction with aggregate functions
(MIN, MAX, COUNT, SUM, AVG, etc.) to group rows based on the grouping expressions and aggregate values in each group.
When a FILTER clause is attached to an aggregate function, only the matching rows are passed to that function.
- * **HAVING**
+* **HAVING**
Specifies the predicates by which the rows produced by GROUP BY are filtered. The HAVING clause is used to
filter rows after the grouping is performed. If HAVING is specified without GROUP BY, it indicates a GROUP BY
without grouping expressions (global aggregate).
- * **ORDER BY**
+* **ORDER BY**
Specifies an ordering of the rows of the complete result set of the query. The output rows are ordered
across the partitions. This parameter is mutually exclusive with `SORT BY`,
`CLUSTER BY` and `DISTRIBUTE BY` and can not be specified together.
- * **SORT BY**
+* **SORT BY**
Specifies an ordering by which the rows are ordered within each partition. This parameter is mutually
exclusive with `ORDER BY` and `CLUSTER BY` and can not be specified together.
- * **CLUSTER BY**
+* **CLUSTER BY**
Specifies a set of expressions that is used to repartition and sort the rows. Using this clause has
the same effect of using `DISTRIBUTE BY` and `SORT BY` together.
- * **DISTRIBUTE BY**
+* **DISTRIBUTE BY**
Specifies a set of expressions by which the result rows are repartitioned. This parameter is mutually
exclusive with `ORDER BY` and `CLUSTER BY` and can not be specified together.
- * **LIMIT**
+* **LIMIT**
Specifies the maximum number of rows that can be returned by a statement or subquery. This clause
is mostly used in the conjunction with `ORDER BY` to produce a deterministic result.
- * **boolean_expression**
+* **boolean_expression**
- Specifies an expression with a return type of boolean.
+ Specifies any expression that evaluates to a result type `boolean`. Two or
+ more expressions may be combined together using the logical
+ operators ( `AND`, `OR` ).
- * **expression**
+* **expression**
Specifies a combination of one or more values, operators, and SQL functions that evaluates to a value.
- * **named_window**
+* **named_window**
- Specifies aliases for one or more source window specifications. The source window specifications can
- be referenced in the widow definitions in the query.
+ Specifies aliases for one or more source window specifications. The source window specifications can
+ be referenced in the widow definitions in the query.
### Related Statements
@@ -159,3 +170,6 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { named_expression [ , ... ] }
* [TABLESAMPLE](sql-ref-syntax-qry-select-sampling.html)
* [Table-valued Function](sql-ref-syntax-qry-select-tvf.html)
* [Window Function](sql-ref-syntax-qry-select-window.html)
+* [CASE Clause](sql-ref-syntax-qry-select-case.html)
+* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
diff --git a/docs/sql-ref-syntax-qry.md b/docs/sql-ref-syntax-qry.md
index 167c394..d55ea43 100644
--- a/docs/sql-ref-syntax-qry.md
+++ b/docs/sql-ref-syntax-qry.md
@@ -45,4 +45,7 @@ ability to generate logical and physical plan for a given query using
* [TABLESAMPLE](sql-ref-syntax-qry-select-sampling.html)
* [Table-valued Function](sql-ref-syntax-qry-select-tvf.html)
* [Window Function](sql-ref-syntax-qry-select-window.html)
+ * [CASE Clause](sql-ref-syntax-qry-select-case.html)
+ * [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+ * [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
* [EXPLAIN Statement](sql-ref-syntax-qry-explain.html)
diff --git a/docs/sql-ref-syntax.md b/docs/sql-ref-syntax.md
index 4bf1858..4318cd6 100644
--- a/docs/sql-ref-syntax.md
+++ b/docs/sql-ref-syntax.md
@@ -66,6 +66,9 @@ Spark SQL is Apache Spark's module for working with structured data. The SQL Syn
* [Table-valued Function](sql-ref-syntax-qry-select-tvf.html)
* [WHERE Clause](sql-ref-syntax-qry-select-where.html)
* [Window Function](sql-ref-syntax-qry-select-window.html)
+ * [CASE Clause](sql-ref-syntax-qry-select-case.html)
+ * [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
+ * [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
* [EXPLAIN](sql-ref-syntax-qry-explain.html)
### Auxiliary Statements
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org