This is an automated email from the ASF dual-hosted git repository. jark pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/flink.git
commit d9f2d394a48901fe90eac058aa2e3aa2d8e0633b Author: luoyuxia <[email protected]> AuthorDate: Mon Aug 29 15:14:20 2022 +0800 [FLINK-29025][docs] add lateral view page for Hive dialect --- .../hiveDialect/Queries/lateral-view.md | 90 ++++++++++++++++++++++ .../hiveDialect/Queries/lateral-view.md | 90 ++++++++++++++++++++++ 2 files changed, 180 insertions(+) diff --git a/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/lateral-view.md b/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/lateral-view.md new file mode 100644 index 00000000000..1bcd48123b5 --- /dev/null +++ b/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/lateral-view.md @@ -0,0 +1,90 @@ +--- +title: "Lateral View Clause" +weight: 6 +type: docs +--- +<!-- +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. +--> + +# Lateral View Clause + +## Description + +Lateral view clause is used in conjunction with user-defined table generating functions([UDTF](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inTable-GeneratingFunctions(UDTF))) such as `explode()`. +A UDTF generates zero or more output rows for each input row. + +A lateral view first applies the UDTF to each row of base and then joins results output rows to the input rows to form a virtual table having the supplied table alias. + +## Syntax + +```sql +lateralView: LATERAL VIEW [ OUTER ] udtf( expression ) tableAlias AS columnAlias [, ... ] +fromClause: FROM baseTable lateralView [, ... ] +``` +The column alias can be omitted. In this case, aliases are inherited from fields name of StructObjectInspector which is returned from UDTF. + +## Parameters + +- Lateral View Outer + + User can specify the optional `OUTER` keyword to generate rows even when a `LATERAL VIEW` usually would not generate a row. + This happens when the UDTF used does not generate any rows which happens easily with when the column to explode is empty. + In this case, the source row would never appear in the results. `OUTER` can be used to prevent that and rows will be generated with `NULL` + values in the columns coming from UDTF. +- Multiple Lateral Views + + A FROM clause can have multiple LATERAL VIEW clauses. + Subsequent LATERAL VIEWS can reference columns from any of the tables appearing to the left of the LATERAL VIEW. + + +## Examples + +Assuming you have one table: +```sql +create table pageAds(pageid string, addid_list array<int>); +``` +And the table contains two rows: +```sql +front_page, [1, 2, 3]; +contact_page, [3, 4, 5]; +``` +Now, you can use `LATERAL VIEW` to convert the column `addid_list` into separate rows: +```sql +SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid; +-- result +front_page, 1 +front_page, 2 +front_page, 3 +contact_page, 3 +contact_page, 4 +contact_page, 5 +``` +Also, if you have one table: +```sql +CREATE TABLE t1(c1 array<int>, addid_list array<int>); +`` +You can use multiple lateral view clauses to convert the column `c1` and `c2` into separate rows: +```sql +SELECT myc1, myc2 FROM t1 +LATERAL VIEW explode(c1) myTable1 AS myc1 +LATERAL VIEW explode(c2) myTable2 AS myc2; +``` +When the UDTF doesn't produce rows, then `LATERAL VIEW` won't produce rows. +You can use `LATERAL VIEW OUTER` to still produce rows, with `NULL` filling the corresponding column. +```sql +SELECT * FROM t1 LATERAL VIEW OUTER explode(array()) C AS a; +``` diff --git a/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/lateral-view.md b/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/lateral-view.md new file mode 100644 index 00000000000..b1463529bd0 --- /dev/null +++ b/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/lateral-view.md @@ -0,0 +1,90 @@ +--- +title: "Lateral View Clause" +weight: 6 +type: docs +--- +<!-- +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. +--> + +# Lateral View Clause + +## Description + +Lateral view clause is used in conjunction with user-defined table generating functions([UDTF](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inTable-GeneratingFunctions(UDTF))) such as `explode()`. +A UDTF generates zero or more output rows for each input row. + +A lateral view first applies the UDTF to each row of base and then joins results output rows to the input rows to form a virtual table having the supplied table alias. + +## Syntax + +```sql +lateralView: LATERAL VIEW [ OUTER ] udtf( expression ) tableAlias AS columnAlias [, ... ] +fromClause: FROM baseTable lateralView [, ... ] +``` +The column alias can be omitted. In this case, aliases are inherited from fields name of StructObjectInspector which is returned from UDTF. + +## Parameters + +- Lateral View Outer + + User can specify the optional `OUTER` keyword to generate rows even when a `LATERAL VIEW` usually would not generate a row. + This happens when the UDTF used does not generate any rows which happens easily with when the column to explode is empty. + In this case, the source row would never appear in the results. `OUTER` can be used to prevent that and rows will be generated with `NULL` + values in the columns coming from UDTF. +- Multiple Lateral Views + + A FROM clause can have multiple LATERAL VIEW clauses. + Subsequent LATERAL VIEWS can reference columns from any of the tables appearing to the left of the LATERAL VIEW. + + +## Examples + +Assuming you have one table: +```sql +create table pageAds(pageid string, addid_list array<int>); +``` +And the table contains two rows: +```sql +front_page, [1, 2, 3]; +contact_page, [3, 4, 5]; +``` +Now, you can use `LATERAL VIEW` to convert the column `addid_list` into separate rows: +```sql +SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid; +-- result +front_page, 1 +front_page, 2 +front_page, 3 +contact_page, 3 +contact_page, 4 +contact_page, 5 +``` +Also, if you have one table: +```sql +CREATE TABLE t1(c1 array<int>, addid_list array<int>); +``` +You can use multiple lateral view clauses to convert the column `c1` and `c2` into separate rows: +```sql +SELECT myc1, myc2 FROM t1 +LATERAL VIEW explode(c1) myTable1 AS myc1 +LATERAL VIEW explode(c2) myTable2 AS myc2; +``` +When the UDTF doesn't produce rows, then `LATERAL VIEW` won't produce rows. +You can use `LATERAL VIEW OUTER` to still produce rows, with `NULL` filling the corresponding column. +```sql +SELECT * FROM t1 LATERAL VIEW OUTER explode(array()) C AS a; +```
