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 e8957f5d526224ed6205c64c3deb91486843ef90 Author: luoyuxia <[email protected]> AuthorDate: Mon Aug 29 15:18:52 2022 +0800 [FLINK-29025][docs] add cte page for Hive dialect --- .../hiveCompatibility/hiveDialect/Queries/cte.md | 67 ++++++++++++++++++++++ .../hiveCompatibility/hiveDialect/Queries/cte.md | 67 ++++++++++++++++++++++ 2 files changed, 134 insertions(+) diff --git a/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/cte.md b/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/cte.md new file mode 100644 index 00000000000..e609e912c9d --- /dev/null +++ b/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/cte.md @@ -0,0 +1,67 @@ +--- +title: "CTE" +weight: 9 +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. +--> + +# Common Table Expression (CTE) + +## Description + +A Common Table Expression (CTE) is a temporary result set derived from a query specified in a `WITH` clause, which immediately precedes a `SELECT` +or `INSERT` keyword. The CTE is defined only with the execution scope of a single statement, and can be referred in the scope. + +## Syntax + +```sql +withClause: cteClause [, ...] +cteClause: cte_name AS (select statment) +``` + + +{{< hint warning >}} +**Note:** +- The `WITH` clause is not supported within SubQuery block +- CTEs are supported in Views, `CTAS` and `INSERT` statement +- [Recursive Queries](https://wiki.postgresql.org/wiki/CTEReadme#Parsing_recursive_queries) are not supported + {{< /hint >}} + +## Examples + +```sql +WITH q1 AS ( SELECT key FROM src WHERE key = '5') +SELECT * +FROM q1; + +-- chaining CTEs +WITH q1 AS ( SELECT key FROM q2 WHERE key = '5'), +q2 AS ( SELECT key FROM src WHERE key = '5') +SELECT * FROM (SELECT key FROM q1) a; + +-- insert example +WITH q1 AS ( SELECT key, value FROM src WHERE key = '5') +FROM q1 +INSERT OVERWRITE TABLE t1 +SELECT *; + +-- ctas example +CREATE TABLE t2 AS +WITH q1 AS ( SELECT key FROM src WHERE key = '4') +SELECT * FROM q1; +``` diff --git a/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/cte.md b/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/cte.md new file mode 100644 index 00000000000..e609e912c9d --- /dev/null +++ b/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/cte.md @@ -0,0 +1,67 @@ +--- +title: "CTE" +weight: 9 +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. +--> + +# Common Table Expression (CTE) + +## Description + +A Common Table Expression (CTE) is a temporary result set derived from a query specified in a `WITH` clause, which immediately precedes a `SELECT` +or `INSERT` keyword. The CTE is defined only with the execution scope of a single statement, and can be referred in the scope. + +## Syntax + +```sql +withClause: cteClause [, ...] +cteClause: cte_name AS (select statment) +``` + + +{{< hint warning >}} +**Note:** +- The `WITH` clause is not supported within SubQuery block +- CTEs are supported in Views, `CTAS` and `INSERT` statement +- [Recursive Queries](https://wiki.postgresql.org/wiki/CTEReadme#Parsing_recursive_queries) are not supported + {{< /hint >}} + +## Examples + +```sql +WITH q1 AS ( SELECT key FROM src WHERE key = '5') +SELECT * +FROM q1; + +-- chaining CTEs +WITH q1 AS ( SELECT key FROM q2 WHERE key = '5'), +q2 AS ( SELECT key FROM src WHERE key = '5') +SELECT * FROM (SELECT key FROM q1) a; + +-- insert example +WITH q1 AS ( SELECT key, value FROM src WHERE key = '5') +FROM q1 +INSERT OVERWRITE TABLE t1 +SELECT *; + +-- ctas example +CREATE TABLE t2 AS +WITH q1 AS ( SELECT key FROM src WHERE key = '4') +SELECT * FROM q1; +```
