maropu commented on a change in pull request #29056:
URL: https://github.com/apache/spark/pull/29056#discussion_r454721838
##########
File path: docs/sql-ref-syntax-ddl-create-table-hiveformat.md
##########
@@ -117,6 +165,40 @@ 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 native 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",
Review comment:
Could you check if the indents are the same the other formats.
##########
File path: docs/sql-ref-syntax-ddl-create-table-hiveformat.md
##########
@@ -117,6 +165,40 @@ 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>
Review comment:
nit: add a space: `street: STRING`
##########
File path: docs/sql-ref-syntax-qry-select-case.md
##########
@@ -0,0 +1,114 @@
+---
+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 rule to return 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 ]
Review comment:
Please follow the other formats (wrong indents?), e.g.,:
https://github.com/apache/spark/blob/master/docs/sql-ref-syntax-ddl-create-table-like.md
##########
File path: docs/sql-ref-syntax-qry-select.md
##########
@@ -74,6 +74,12 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { named_expression
[ , ... ] }
An expression with an assigned name. In general, it denotes a column
expression.
**Syntax:** `expression [AS] [alias]`
+
+ * **FROM**
+
+ Specific the data set to be selected.
+
+ **Syntax:** `FROM` `from_item` [ LATERAL VIEW Clause ] [ ... ] [ PIVOT
Clause ]
Review comment:
Could you move this syntax into the line 45? Then, add an entry like `**
lateral_view **`?
https://docs.databricks.com/spark/latest/spark-sql/language-manual/select.html#lateral-view
##########
File path: docs/sql-ref-syntax-qry-select-pivot.md
##########
@@ -0,0 +1,98 @@
+---
+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
+
+`PIVOT` clause is used for data perspective, we can get the aggregated values
based on specific column value.
+
+### Syntax
+
+```sql
+PIVOT ( { aggregate_Expression [ AS aggregate_Expression_Alias ] } [ , ... ]
FOR column_List IN ( expression_List ) )
+```
+
+### Parameters
+
+* **aggregate_Expression**
+
+ We will get specific aggregated results by the `aggregate_Expression`,
such as `SUM(a)` or `COUNT(DISTINCT b)`.
+
+* **aggregate_Expression_Alias**
+
+ It is the alias for `aggregate_Expression`, which is optional, we can use
it in `SELECT` clause.
+
+* **column_List**
+
+ It contains columns in the `FROM` clause, which specific the columns we
want to replaced with new columns, we can use brackets to surround the columns,
such as `( c1, c2 )`.
+
+* **expression_List**
+
+ It specifics new columns , which used to match values in `column_List` as
the aggregating condition, we can also add alias 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)
+);
++------+-----------+---------+---------+---------+---------+--+
Review comment:
remove `--+` in the end....
##########
File path: 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)
Review comment:
Also, I think a `case` clause should be included somewhere in the SELECT
syntax.
##########
File path: docs/sql-ref-syntax-qry-select-groupby.md
##########
@@ -38,6 +38,8 @@ GROUP BY GROUPING SETS (grouping_set [ , ...])
While aggregate functions are defined as
```sql
aggregate_name ( [ DISTINCT ] expression [ , ... ] ) [ FILTER ( WHERE
boolean_expression ) ]
+
+[ FIRST | LAST ] ( expression [ IGNORE NULLS ] ) ]
Review comment:
first/last cannot be used together with `FILTER`?
##########
File path: docs/sql-ref-syntax-qry-select-lateral-view.md
##########
@@ -0,0 +1,123 @@
+---
+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
+
+`LATERAL VIEW` clause is used in conjunction with user-defined table
generating functions such as explode(), a UDTF generates zero or more output
rows foreach input row. A lateral view first applies the UDTF to each row of
base table and then joins resulting output rows to the input rows to form a
virtual table having the supplied table alias.
+
+
+### Syntax
+
+```sql
+LATERAL VIEW [ OUTER ] { udtf_Expression [ table_Alias ] AS column_alias [ ,
... ] } [ ... ]
+```
+
+### Parameters
+
+* **OUTER**
+
+ If `LATERAL VIEW` is used without `OUTER`, and `udtf_Expression` return
empty, then no results will be output in select.
+ If `LATERAL VIEW` is used with `OUTER`, and `udtf_Expression` return
empty, then results will be output normally with `NULL` as `udtf_Expression`
output. .
+
+* **udtf_Expression**
+
+ This expression will output an vitual table with single input row.
+
+* **table_Alias**
+
+ It is the alias for `udtf_Expression`, which is optional.
+
+* **column_alias**
+
+ It lists the column alias of `udtf_Expression`, which may be used in
output rows, we may have multiple alias if `udtf_Expression` 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
Review comment:
need indents and add a space `30, 60`
##########
File path: docs/sql-ref-syntax-qry-select.md
##########
@@ -74,6 +74,12 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { named_expression
[ , ... ] }
An expression with an assigned name. In general, it denotes a column
expression.
**Syntax:** `expression [AS] [alias]`
+
+ * **FROM**
Review comment:
We already have the FROM entry?
https://github.com/apache/spark/pull/29056/files#diff-bc52347d9c1373190bce4389d3066ad5R84
##########
File path: docs/sql-ref-syntax-ddl-create-table-hiveformat.md
##########
@@ -117,6 +165,40 @@ 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 native 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'
Review comment:
8 spaces for this indent?
##########
File path: docs/sql-ref-syntax-qry-select-case.md
##########
@@ -0,0 +1,114 @@
+---
+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 rule to return specific result based on the specified
condition, similar to if/else statements in other programming languages
Review comment:
Add `.` in the end.
##########
File path: docs/sql-ref-syntax-ddl-create-table-hiveformat.md
##########
@@ -117,6 +165,40 @@ 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 native 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 custom serde(need load the class first)
+CREATE EXTERNAL TABLE family (id INT,name STRING)
Review comment:
add a space `INT, name`
##########
File path: docs/sql-ref-syntax-ddl-create-table-hiveformat.md
##########
@@ -117,6 +165,40 @@ 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 native 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 custom serde(need load the class first)
+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'
Review comment:
ditto
##########
File path: docs/sql-ref-syntax-qry-select-lateral-view.md
##########
@@ -0,0 +1,123 @@
+---
+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
+
+`LATERAL VIEW` clause is used in conjunction with user-defined table
generating functions such as explode(), a UDTF generates zero or more output
rows foreach input row. A lateral view first applies the UDTF to each row of
base table and then joins resulting output rows to the input rows to form a
virtual table having the supplied table alias.
Review comment:
We need to say `user-defined` here? Seems like it is just copied from
the hive doc, but could you rewrite it based on the Spark behaviour?
##########
File path: docs/sql-ref-syntax-qry-select-pivot.md
##########
@@ -0,0 +1,98 @@
+---
+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
+
+`PIVOT` clause is used for data perspective, we can get the aggregated values
based on specific column value.
+
+### Syntax
+
+```sql
+PIVOT ( { aggregate_Expression [ AS aggregate_Expression_Alias ] } [ , ... ]
FOR column_List IN ( expression_List ) )
Review comment:
Could you follow the naming formats with the other pages: e.g.,
`aggregate_Expression ` -> `group_expression`?
##########
File path: docs/sql-ref-syntax-qry-select-lateral-view.md
##########
@@ -0,0 +1,123 @@
+---
+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
+
+`LATERAL VIEW` clause is used in conjunction with user-defined table
generating functions such as explode(), a UDTF generates zero or more output
rows foreach input row. A lateral view first applies the UDTF to each row of
base table and then joins resulting output rows to the input rows to form a
virtual table having the supplied table alias.
+
+
+### Syntax
+
+```sql
+LATERAL VIEW [ OUTER ] { udtf_Expression [ table_Alias ] AS column_alias [ ,
... ] } [ ... ]
+```
+
+### Parameters
+
+* **OUTER**
+
+ If `LATERAL VIEW` is used without `OUTER`, and `udtf_Expression` return
empty, then no results will be output in select.
+ If `LATERAL VIEW` is used with `OUTER`, and `udtf_Expression` return
empty, then results will be output normally with `NULL` as `udtf_Expression`
output. .
+
+* **udtf_Expression**
+
+ This expression will output an vitual table with single input row.
+
+* **table_Alias**
+
+ It is the alias for `udtf_Expression`, which is optional.
+
+* **column_alias**
+
+ It lists the column alias of `udtf_Expression`, which may be used in
output rows, we may have multiple alias if `udtf_Expression` 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;
Review comment:
ditto
##########
File path: docs/sql-ref-syntax-qry-select-lateral-view.md
##########
@@ -0,0 +1,123 @@
+---
+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
+
+`LATERAL VIEW` clause is used in conjunction with user-defined table
generating functions such as explode(), a UDTF generates zero or more output
rows foreach input row. A lateral view first applies the UDTF to each row of
base table and then joins resulting output rows to the input rows to form a
virtual table having the supplied table alias.
Review comment:
please remove unnecessary spaces, `clause is`
##########
File path: docs/sql-ref-syntax-qry-select-lateral-view.md
##########
@@ -0,0 +1,123 @@
+---
+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
+
+`LATERAL VIEW` clause is used in conjunction with user-defined table
generating functions such as explode(), a UDTF generates zero or more output
rows foreach input row. A lateral view first applies the UDTF to each row of
base table and then joins resulting output rows to the input rows to form a
virtual table having the supplied table alias.
+
+
+### Syntax
+
+```sql
+LATERAL VIEW [ OUTER ] { udtf_Expression [ table_Alias ] AS column_alias [ ,
... ] } [ ... ]
+```
+
+### Parameters
+
+* **OUTER**
+
+ If `LATERAL VIEW` is used without `OUTER`, and `udtf_Expression` return
empty, then no results will be output in select.
+ If `LATERAL VIEW` is used with `OUTER`, and `udtf_Expression` return
empty, then results will be output normally with `NULL` as `udtf_Expression`
output. .
+
+* **udtf_Expression**
+
+ This expression will output an vitual table with single input row.
+
+* **table_Alias**
+
+ It is the alias for `udtf_Expression`, which is optional.
+
+* **column_alias**
+
+ It lists the column alias of `udtf_Expression`, which may be used in
output rows, we may have multiple alias if `udtf_Expression` 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;
++------+-------+-------+--------+-----------+--------+--------+
+| 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
Review comment:
ditto
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
