This is an automated email from the ASF dual-hosted git repository. twalthr pushed a commit to branch release-1.11 in repository https://gitbox.apache.org/repos/asf/flink.git
commit 6818f94c5565360a1a1b476c09a81c95d1165006 Author: godfreyhe <[email protected]> AuthorDate: Wed Jun 10 13:23:31 2020 +0800 [FLINK-17599][docs] Add documents for EXPLAIN statement --- docs/dev/table/common.md | 2 + docs/dev/table/common.zh.md | 2 + docs/dev/table/sql/explain.md | 180 ++++++++++++++++++++++++++++++++++++++ docs/dev/table/sql/explain.zh.md | 182 +++++++++++++++++++++++++++++++++++++++ docs/dev/table/sql/index.md | 1 + docs/dev/table/sql/index.zh.md | 1 + 6 files changed, 368 insertions(+) diff --git a/docs/dev/table/common.md b/docs/dev/table/common.md index 40bd4c8..e8a1861 100644 --- a/docs/dev/table/common.md +++ b/docs/dev/table/common.md @@ -1442,6 +1442,8 @@ This is done through the `Table.explain()` method or `StatementSet.explain()` me 2. the optimized logical query plan, and 3. the physical execution plan. +`TableEnvironment.explainSql()` and `TableEnvironment.executeSql()` support execute a `EXPLAIN` statement to get the plans, Please refer to [EXPLAIN]({{ site.baseurl }}/dev/table/sql/explain.html) page. + The following code shows an example and the corresponding output for given `Table` using `Table.explain()` method: <div class="codetabs" markdown="1"> diff --git a/docs/dev/table/common.zh.md b/docs/dev/table/common.zh.md index f295f39..0371bb8 100644 --- a/docs/dev/table/common.zh.md +++ b/docs/dev/table/common.zh.md @@ -1431,6 +1431,8 @@ Table API 提供了一种机制来解释计算 `Table` 的逻辑和优化查询 2. 优化的逻辑查询计划，以及 3. 物理执行计划。 +可以用 `TableEnvironment.explainSql()` 方法和 `TableEnvironment.executeSql()` 方法支持执行一个 `EXPLAIN` 语句获取逻辑和优化查询计划，请参阅 [EXPLAIN]({{ site.baseurl }}/zh/dev/table/sql/explain.html) 页面. + 以下代码展示了一个示例以及对给定 `Table` 使用 `Table.explain()` 方法的相应输出： <div class="codetabs" markdown="1"> diff --git a/docs/dev/table/sql/explain.md b/docs/dev/table/sql/explain.md new file mode 100644 index 0000000..ae26b06 --- /dev/null +++ b/docs/dev/table/sql/explain.md @@ -0,0 +1,180 @@ +--- +title: "EXPLAIN Statements" +nav-parent_id: sql +nav-pos: 8 +--- +<!-- +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. +--> + +* This will be replaced by the TOC +{:toc} + +EXPLAIN statements are used to explain the logical and optimized query plans of a query or an INSERT statement. + + +## Run an EXPLAIN statement + +EXPLAIN statements can be executed with the `executeSql()` method of the `TableEnvironment`, or executed in [SQL CLI]({{ site.baseurl }}/dev/table/sqlClient.html). The `executeSql()` method returns explain result for a successful EXPLAIN operation, otherwise will throw an exception. + +The following examples show how to run an EXPLAIN statement in `TableEnvironment` and in SQL CLI. + +<div class="codetabs" markdown="1"> +<div data-lang="java" markdown="1"> +{% highlight java %} +StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); +StreamTableEnvironment tEnv = StreamTableEnvironment.create(env); + +// register a table named "Orders" +tEnv.executeSql("CREATE TABLE MyTable1 (count bigint, work VARCHAR(256) WITH (...)"); +tEnv.executeSql("CREATE TABLE MyTable2 (count bigint, work VARCHAR(256) WITH (...)"); + +// explain SELECT statement through TableEnvironment.explainSql() +String explanation = tEnv.explainSql( + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + + "UNION ALL " + + "SELECT count, word FROM MyTable2"); +System.out.println(explanation); + +// explain SELECT statement through TableEnvironment.executeSql() +TableResult tableResult = tEnv.executeSql( + "EXPLAIN PLAN FOR " + + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + + "UNION ALL " + + "SELECT count, word FROM MyTable2"); +tableResult.print(); + +{% endhighlight %} +</div> + +<div data-lang="scala" markdown="1"> +{% highlight scala %} +val env = StreamExecutionEnvironment.getExecutionEnvironment() +val tEnv = StreamTableEnvironment.create(env) + +// register a table named "Orders" +tEnv.executeSql("CREATE TABLE MyTable1 (count bigint, work VARCHAR(256) WITH (...)") +tEnv.executeSql("CREATE TABLE MyTable2 (count bigint, work VARCHAR(256) WITH (...)") + +// explain SELECT statement through TableEnvironment.explainSql() +val explanation = tEnv.explainSql( + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + + "UNION ALL " + + "SELECT count, word FROM MyTable2") +println(explanation) + +// explain SELECT statement through TableEnvironment.executeSql() +val tableResult = tEnv.executeSql( + "EXPLAIN PLAN FOR " + + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + + "UNION ALL " + + "SELECT count, word FROM MyTable2") +tableResult.print() + +{% endhighlight %} +</div> + +<div data-lang="python" markdown="1"> +{% highlight python %} +settings = EnvironmentSettings.new_instance()... +table_env = StreamTableEnvironment.create(env, settings) + +t_env.execute_sql("CREATE TABLE MyTable1 (count bigint, work VARCHAR(256) WITH (...)") +t_env.execute_sql("CREATE TABLE MyTable2 (count bigint, work VARCHAR(256) WITH (...)") + +# explain SELECT statement through TableEnvironment.explain_sql() +explanation1 = t_env.explain_sql( + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + "UNION ALL " + "SELECT count, word FROM MyTable2") +print(explanation1) + +# explain SELECT statement through TableEnvironment.execute_sql() +table_result = t_env.execute_sql( + "EXPLAIN PLAN FOR " + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + "UNION ALL " + "SELECT count, word FROM MyTable2") +table_result.print() + +{% endhighlight %} +</div> + +<div data-lang="SQL CLI" markdown="1"> +{% highlight sql %} +Flink SQL> CREATE TABLE MyTable1 (count bigint, work VARCHAR(256); +[INFO] Table has been created. + +Flink SQL> CREATE TABLE MyTable2 (count bigint, work VARCHAR(256); +[INFO] Table has been created. + +Flink SQL> EXPLAIN PLAN FOR SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' +> UNION ALL +> SELECT count, word FROM MyTable2; + +{% endhighlight %} +</div> +</div> + +The `EXPLAIN` result is: +<div> +{% highlight text %} +== Abstract Syntax Tree == +LogicalUnion(all=[true]) + LogicalFilter(condition=[LIKE($1, _UTF-16LE'F%')]) + FlinkLogicalTableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word]) + FlinkLogicalTableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word]) + + +== Optimized Logical Plan == +DataStreamUnion(all=[true], union all=[count, word]) + DataStreamCalc(select=[count, word], where=[LIKE(word, _UTF-16LE'F%')]) + TableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word]) + TableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word]) + +== Physical Execution Plan == +Stage 1 : Data Source + content : collect elements with CollectionInputFormat + +Stage 2 : Data Source + content : collect elements with CollectionInputFormat + + Stage 3 : Operator + content : from: (count, word) + ship_strategy : REBALANCE + + Stage 4 : Operator + content : where: (LIKE(word, _UTF-16LE'F%')), select: (count, word) + ship_strategy : FORWARD + + Stage 5 : Operator + content : from: (count, word) + ship_strategy : REBALANCE +{% endhighlight %} +</div> + +{% top %} + +## Syntax + +{% highlight sql %} +EXPLAIN PLAN FOR <query_statement_or_insert_statement> +{% endhighlight %} + +For query syntax, please refer to [Queries]({{ site.baseurl }}/dev/table/sql/queries.html#supported-syntax) page. +For INSERT, please refer to [INSERT]({{ site.baseurl }}/dev/table/sql/insert.html) page. diff --git a/docs/dev/table/sql/explain.zh.md b/docs/dev/table/sql/explain.zh.md new file mode 100644 index 0000000..e2f2c1e --- /dev/null +++ b/docs/dev/table/sql/explain.zh.md @@ -0,0 +1,182 @@ +--- +title: "EXPLAIN 语句" +nav-parent_id: sql +nav-pos: 8 +--- +<!-- +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. +--> + +* This will be replaced by the TOC +{:toc} + +EXPLAIN 语句用来解释一条 query 语句或者 INSERT 语句的逻辑计划和优化后的计划。 + + +## 运行一条 EXPLAIN 语句 + +EXPLAIN 语句可以通过 `TableEnvironment` 的 `executeSql()` 执行，也可以在 [SQL CLI]({{ site.baseurl }}/zh/dev/table/sqlClient.html) 中执行 EXPLAIN 语句。 若 EXPLAIN 操作执行成功，`executeSql()` 方法返回解释的结果，否则会抛出异常。 + +以下的例子展示了如何在 TableEnvironment 和 SQL CLI 中执行一条 EXPLAIN 语句。 + +<div class="codetabs" markdown="1"> +<div data-lang="java" markdown="1"> +{% highlight java %} +StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); +StreamTableEnvironment tEnv = StreamTableEnvironment.create(env); + +// register a table named "Orders" +tEnv.executeSql("CREATE TABLE MyTable1 (count bigint, work VARCHAR(256) WITH (...)"); +tEnv.executeSql("CREATE TABLE MyTable2 (count bigint, work VARCHAR(256) WITH (...)"); + +// explain SELECT statement through TableEnvironment.explainSql() +String explanation = tEnv.explainSql( + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + + "UNION ALL " + + "SELECT count, word FROM MyTable2"); +System.out.println(explanation); + +// explain SELECT statement through TableEnvironment.executeSql() +TableResult tableResult = tEnv.executeSql( + "EXPLAIN PLAN FOR " + + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + + "UNION ALL " + + "SELECT count, word FROM MyTable2"); +tableResult.print(); + +{% endhighlight %} +</div> + +<div data-lang="scala" markdown="1"> +{% highlight scala %} +val env = StreamExecutionEnvironment.getExecutionEnvironment() +val tEnv = StreamTableEnvironment.create(env) + +// register a table named "Orders" +tEnv.executeSql("CREATE TABLE MyTable1 (count bigint, work VARCHAR(256) WITH (...)") +tEnv.executeSql("CREATE TABLE MyTable2 (count bigint, work VARCHAR(256) WITH (...)") + +// explain SELECT statement through TableEnvironment.explainSql() +val explanation = tEnv.explainSql( + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + + "UNION ALL " + + "SELECT count, word FROM MyTable2") +println(explanation) + +// explain SELECT statement through TableEnvironment.executeSql() +val tableResult = tEnv.executeSql( + "EXPLAIN PLAN FOR " + + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + + "UNION ALL " + + "SELECT count, word FROM MyTable2") +tableResult.print() + +{% endhighlight %} +</div> + +<div data-lang="python" markdown="1"> +{% highlight python %} +settings = EnvironmentSettings.new_instance()... +table_env = StreamTableEnvironment.create(env, settings) + +t_env.execute_sql("CREATE TABLE MyTable1 (count bigint, work VARCHAR(256) WITH (...)") +t_env.execute_sql("CREATE TABLE MyTable2 (count bigint, work VARCHAR(256) WITH (...)") + +# explain SELECT statement through TableEnvironment.explain_sql() +explanation1 = t_env.explain_sql( + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + "UNION ALL " + "SELECT count, word FROM MyTable2") +print(explanation1) + +# explain SELECT statement through TableEnvironment.execute_sql() +table_result = t_env.execute_sql( + "EXPLAIN PLAN FOR " + "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " + "UNION ALL " + "SELECT count, word FROM MyTable2") +table_result.print() + +{% endhighlight %} +</div> + +<div data-lang="SQL CLI" markdown="1"> +{% highlight sql %} +Flink SQL> CREATE TABLE MyTable1 (count bigint, work VARCHAR(256); +[INFO] Table has been created. + +Flink SQL> CREATE TABLE MyTable2 (count bigint, work VARCHAR(256); +[INFO] Table has been created. + +Flink SQL> EXPLAIN PLAN FOR SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' +> UNION ALL +> SELECT count, word FROM MyTable2; + +{% endhighlight %} +</div> +</div> + +执行 `EXPLAIN` 语句后的结果为： +<div> +{% highlight text %} +== Abstract Syntax Tree == +LogicalUnion(all=[true]) + LogicalFilter(condition=[LIKE($1, _UTF-16LE'F%')]) + FlinkLogicalTableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word]) + FlinkLogicalTableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word]) + + +== Optimized Logical Plan == +DataStreamUnion(all=[true], union all=[count, word]) + DataStreamCalc(select=[count, word], where=[LIKE(word, _UTF-16LE'F%')]) + TableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word]) + TableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word]) + +== Physical Execution Plan == +Stage 1 : Data Source + content : collect elements with CollectionInputFormat + +Stage 2 : Data Source + content : collect elements with CollectionInputFormat + + Stage 3 : Operator + content : from: (count, word) + ship_strategy : REBALANCE + + Stage 4 : Operator + content : where: (LIKE(word, _UTF-16LE'F%')), select: (count, word) + ship_strategy : FORWARD + + Stage 5 : Operator + content : from: (count, word) + ship_strategy : REBALANCE +{% endhighlight %} +</div> + +{% top %} + +## 语法 + +{% highlight sql %} +EXPLAIN PLAN FOR <query_statement_or_insert_statement> +{% endhighlight %} + +请参阅 [Queries]({{ site.baseurl }}/zh/dev/table/sql/queries.html#supported-syntax) 页面获得 query 的语法。 +请参阅 [INSERT]({{ site.baseurl }}/zh/dev/table/sql/insert.html) 页面获得 INSERT 的语法。 + +{% top %} diff --git a/docs/dev/table/sql/index.md b/docs/dev/table/sql/index.md index 06f8bd3..5ac4ff1 100644 --- a/docs/dev/table/sql/index.md +++ b/docs/dev/table/sql/index.md @@ -35,6 +35,7 @@ This page lists all the supported statements supported in Flink SQL for now: - [INSERT](insert.html) - [SQL HINTS](hints.html) - [DESCRIBE](describe.html) +- [EXPLAIN](explain.html) ## Data Types diff --git a/docs/dev/table/sql/index.zh.md b/docs/dev/table/sql/index.zh.md index 00d34ab..9b220f7 100644 --- a/docs/dev/table/sql/index.zh.md +++ b/docs/dev/table/sql/index.zh.md @@ -35,6 +35,7 @@ under the License. - [INSERT](insert.html) - [SQL HINTS](hints.html) - [DESCRIBE](describe.html) +- [EXPLAIN](explain.html) ## 数据类型
