Flink SQL: EXPLAIN Statements

最新推荐文章于 2024-06-27 10:04:35 发布
原创 最新推荐文章于 2024-06-27 10:04:35 发布 · 452 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#flink #sql #java

本文介绍了如何使用EXPLAIN语句来解析SQL查询的逻辑及优化计划，并提供了在TableEnvironment和SQLCLI环境中执行EXPLAIN的具体示例。

EXPLAIN Statements

EXPLAIN statements are used to explain the logical and optimized query plans of a query or an INSERT statement.
EXPLAIN语句用于解释查询或INSERT语句的逻辑和优化查询计划。

Run an EXPLAIN statement

Java
EXPLAIN statements can be executed with the executeSql() method of the TableEnvironment. The executeSql() method returns explain result for a successful EXPLAIN operation, otherwise will throw an exception.
EXPLAIN语句可以使用TableEnvironment的executeSql()方法执行。executeSql()方法为成功的EXPLAIN操作返回解释结果，否则将引发异常。

The following examples show how to run an EXPLAIN statement in TableEnvironment.
以下示例演示如何在TableEnvironment中运行EXPLAIN语句。

StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);

// register a table named "Orders"
tEnv.executeSql("CREATE TABLE MyTable1 (`count` bigint, word VARCHAR(256)) WITH ('connector' = 'datagen')");
tEnv.executeSql("CREATE TABLE MyTable2 (`count` bigint, word VARCHAR(256)) WITH ('connector' = 'datagen')");

// 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();

TableResult tableResult2 = tEnv.executeSql(
  "EXPLAIN ESTIMATED_COST, CHANGELOG_MODE, JSON_EXECUTION_PLAN " + 
  "SELECT `count`, word FROM MyTable1 WHERE word LIKE 'F%' " +
  "UNION ALL " + 
  "SELECT `count`, word FROM MyTable2");
tableResult2.print();

The EXPLAIN result is:
EXPLAIN结果为：

EXPLAIN PLAN

== Abstract Syntax Tree ==
LogicalUnion(all=[true])
:- LogicalProject(count=[$0], word=[$1])
:  +- LogicalFilter(condition=[LIKE($1, _UTF-16LE'F%')])
:     +- LogicalTableScan(table=[[default_catalog, default_database, MyTable1]])
+- LogicalProject(count=[$0], word=[$1])
   +- LogicalTableScan(table=[[default_catalog, default_database, MyTable2]])

== Optimized Physical Plan ==
Union(all=[true], union=[count, word])
:- Calc(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])

== Optimized Execution Plan ==
Union(all=[true], union=[count, word])
:- Calc(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])

EXPLAIN PLAN WITH DETAILS

== Abstract Syntax Tree ==
LogicalUnion(all=[true])
:- LogicalProject(count=[$0], word=[$1])
:  +- LogicalFilter(condition=[LIKE($1, _UTF-16LE'F%')])
:     +- LogicalTableScan(table=[[default_catalog, default_database, MyTable1]])
+- LogicalProject(count=[$0], word=[$1])
   +- LogicalTableScan(table=[[default_catalog, default_database, MyTable2]])

== Optimized Physical Plan ==
Union(all=[true], union=[count, word], changelogMode=[I]): rowcount = 1.05E8, cumulative cost = {3.1E8 rows, 3.05E8 cpu, 4.0E9 io, 0.0 network, 0.0 memory}
:- Calc(select=[count, word], where=[LIKE(word, _UTF-16LE'F%')], changelogMode=[I]): rowcount = 5000000.0, cumulative cost = {1.05E8 rows, 1.0E8 cpu, 2.0E9 io, 0.0 network, 0.0 memory}
:  +- TableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word], changelogMode=[I]): rowcount = 1.0E8, cumulative cost = {1.0E8 rows, 1.0E8 cpu, 2.0E9 io, 0.0 network, 0.0 memory}
+- TableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word], changelogMode=[I]): rowcount = 1.0E8, cumulative cost = {1.0E8 rows, 1.0E8 cpu, 2.0E9 io, 0.0 network, 0.0 memory}

== Optimized Execution Plan ==
Union(all=[true], union=[count, word])
:- Calc(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 ==
{
  "nodes" : [ {
    "id" : 37,
    "type" : "Source: TableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word])",
    "pact" : "Data Source",
    "contents" : "Source: TableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word])",
    "parallelism" : 1
  }, {
    "id" : 38,
    "type" : "Calc(select=[count, word], where=[LIKE(word, _UTF-16LE'F%')])",
    "pact" : "Operator",
    "contents" : "Calc(select=[count, word], where=[LIKE(word, _UTF-16LE'F%')])",
    "parallelism" : 1,
    "predecessors" : [ {
      "id" : 37,
      "ship_strategy" : "FORWARD",
      "side" : "second"
    } ]
  }, {
    "id" : 39,
    "type" : "Source: TableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word])",
    "pact" : "Data Source",
    "contents" : "Source: TableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word])",
    "parallelism" : 1
  } ]

SQL CLI
EXPLAIN statements can be executed in SQL CLI.
EXPLAIN语句可以在SQL CLI中执行。

The following examples show how to run an EXPLAIN statement in SQL CLI.
以下示例演示如何在SQL CLI中运行EXPLAIN语句。

Flink SQL> CREATE TABLE MyTable1 (`count` bigint, word VARCHAR(256)) WITH ('connector' = 'datagen');
[INFO] Table has been created.

Flink SQL> CREATE TABLE MyTable2 (`count` bigint, word VARCHAR(256)) WITH ('connector' = 'datagen');
[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;
                                  
Flink SQL> EXPLAIN ESTIMATED_COST, CHANGELOG_MODE, JSON_EXECUTION_PLAN SELECT `count`, word FROM MyTable1 
> WHERE word LIKE 'F%' 
> UNION ALL 
> SELECT `count`, word FROM MyTable2;

The EXPLAIN result is:
EXPLAIN结果为：

EXPLAIN PLAN

== Abstract Syntax Tree ==
LogicalUnion(all=[true])
:- LogicalProject(count=[$0], word=[$1])
:  +- LogicalFilter(condition=[LIKE($1, _UTF-16LE'F%')])
:     +- LogicalTableScan(table=[[default_catalog, default_database, MyTable1]])
+- LogicalProject(count=[$0], word=[$1])
   +- LogicalTableScan(table=[[default_catalog, default_database, MyTable2]])

== Optimized Physical Plan ==
Union(all=[true], union=[count, word])
:- Calc(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])

== Optimized Execution Plan ==
Union(all=[true], union=[count, word])
:- Calc(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])

EXPLAIN PLAN WITH DETAILS

== Abstract Syntax Tree ==
LogicalUnion(all=[true])
:- LogicalProject(count=[$0], word=[$1])
:  +- LogicalFilter(condition=[LIKE($1, _UTF-16LE'F%')])
:     +- LogicalTableScan(table=[[default_catalog, default_database, MyTable1]])
+- LogicalProject(count=[$0], word=[$1])
   +- LogicalTableScan(table=[[default_catalog, default_database, MyTable2]])

== Optimized Physical Plan ==
Union(all=[true], union=[count, word], changelogMode=[I]): rowcount = 1.05E8, cumulative cost = {3.1E8 rows, 3.05E8 cpu, 4.0E9 io, 0.0 network, 0.0 memory}
:- Calc(select=[count, word], where=[LIKE(word, _UTF-16LE'F%')], changelogMode=[I]): rowcount = 5000000.0, cumulative cost = {1.05E8 rows, 1.0E8 cpu, 2.0E9 io, 0.0 network, 0.0 memory}
:  +- TableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word], changelogMode=[I]): rowcount = 1.0E8, cumulative cost = {1.0E8 rows, 1.0E8 cpu, 2.0E9 io, 0.0 network, 0.0 memory}
+- TableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word], changelogMode=[I]): rowcount = 1.0E8, cumulative cost = {1.0E8 rows, 1.0E8 cpu, 2.0E9 io, 0.0 network, 0.0 memory}

== Optimized Execution Plan ==
Union(all=[true], union=[count, word])
:- Calc(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 ==
{
  "nodes" : [ {
    "id" : 37,
    "type" : "Source: TableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word])",
    "pact" : "Data Source",
    "contents" : "Source: TableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word])",
    "parallelism" : 1
  }, {
    "id" : 38,
    "type" : "Calc(select=[count, word], where=[LIKE(word, _UTF-16LE'F%')])",
    "pact" : "Operator",
    "contents" : "Calc(select=[count, word], where=[LIKE(word, _UTF-16LE'F%')])",
    "parallelism" : 1,
    "predecessors" : [ {
      "id" : 37,
      "ship_strategy" : "FORWARD",
      "side" : "second"
    } ]
  }, {
    "id" : 39,
    "type" : "Source: TableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word])",
    "pact" : "Data Source",
    "contents" : "Source: TableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word])",
    "parallelism" : 1
  } ]

ExplainDetails

Print the plan for the statement with specified ExplainDetails.

ESTIMATED_COST: generates cost information on physical node estimated by optimizer, 
e.g. TableSourceScan(..., cumulative cost ={1.0E8 rows, 1.0E8 cpu, 2.4E9 io, 0.0 network, 0.0 memory})

CHANGELOG_MODE:generates changelog mode for every physical rel node. 
e.g. GroupAggregate(..., changelogMode=[I,UA,D])

JSON_EXECUTION_PLAN: generates the execution plan in json format of the program.

Syntax

EXPLAIN [([ExplainDetail[, ExplainDetail]*]) | PLAN FOR] <query_statement_or_insert_statement_or_statement_set>

statement_set:
EXECUTE STATEMENT SET
BEGIN
insert_statement;
...
insert_statement;
END;

For query syntax, please refer to Queries page. For insert syntax, please refer to INSERT page.
有关查询语法，请参阅Queries页面。有关插入语法，请参阅iINSERT页面。

【Flink】FlinkSQL如何触发执行计算
一杯咖啡半杯糖的博客
03-02 915
Table （通过 StatementSet.addInsert() 输出给某个 Sink）和 INSERT 语句 （通过调用 StatementSet.addInsertSql()）会先被缓存到 StatementSet 中，StatementSet.execute() 方法被调用时，所有的 sink 会被优化成一张有向无环图。该方法是用来将一个表的内容插入到目标表中，一旦该方法被调用， TABLE API 程序立即被翻译。该方法是用来执行一个 SQL 语句，一旦该方法被调用， SQL 语句立即被翻译。
flink查看sql执行计划
yy的博客
07-20 999
// 老版本 tableEnv.explain(Table类型) // 新版本 tableEnv.explainSql("select 字符串")
flink 源码调试flinksql的 explain功能
wppwpp1的专栏
04-09 757
1、因业务需要，需要把查看sql的执行计划，所以在代码里可以添加如下代码来查看相关数据,在 TableEnvironmentITCase测试类添加代码 //todo 查看sql的解析语法 @Test def testExplainAndExecuteSingleSink2(): Unit = { val sinkPath = TestTableSourceSinks.createCsvTemporarySinkTable( tEnv, new TableSchema(Arr.
Flink SQL: SET Statements
weixin_48813624的博客
11-08 1546
Flink SQL: SET Statements
Flink SQL: SHOW Statements
weixin_48813624的博客
11-08 533
Flink SQL: SHOW Statements
EXPLAIN Statement
Addyman的博客
12-11 422
Reference: https://dev.mysql.com/doc/refman/8.0/en/explain.html https://dev.mysql.com/doc/refman/8.0/en/explain-output.html 1、Obtaining Table Structure Information DESCRIBE|EXPLAIN order_tm_order...
【Flink SQL】Flink SQL 语法篇（十）：EXPLAIN、USE、LOAD、SET、SQL Hints
Code · Cloud · Think · Repeat
02-29 3080
如果熟悉 MySQL 的同学会非常熟悉这个子句，在 MySQL 中，USE 子句通常被用于切换库，那么在 Flink SQL 体系中，它的作用也是和 MySQL 中 USE 子句的功能基本一致，用于切换 Catalog，DataBase，使用 Module。
Flink SQL:SQL
weixin_48813624的博客
10-18 432
Flink SQL:SQL
[flink]flink-sql-gateway的调试过程(二)sql的解析过程
新一的技术笔记
01-28 1410
前言： sql的解析过程，看到代码中用到的是： RelMetadataQuery.THREAD_PROVIDERS .set(JaninoRelMetadataProvider.of(FlinkDefaultRelMetadataProvider.INSTANCE())); 整个SqlCommandCall用的就是这个类里面的静态类 /* * Licensed to the Apache Software Foundation (ASF) under one * or mor.
Flink语法扩展--SqlRichExplain为例
不甚了然的博客
04-17 2885
SqlRichExplain语法扩展示例
基于Flink 1.10版本的SQL演练
qq_31866793的博客
02-24 3501
1，搭建Flink集群,在节点node1,node2,node3安装 修改配置文件: flink-conf.yaml配置文件 2,启动hadoop集群，保证hive也是正常的： 启动Flink，成功如下： 3，Flink1.10与Hive的生产级别集成 内存优化点：使用多少内存就用多少内存。 4，我们需要做的流程： 1）...
数据库关系模型与SQL：揭秘高效数据操作
首先，介绍了数据库关系模型的理论基础和SQL操作语言的核心概念，包括数据定义、数据操作和数据控制语言。随后，深入分析了SQL的高级查询技巧和性能优化策略，如索引管理、查询计划调优及并发控制。在关系模型的实际...
flink使用StatementSet降低资源浪费
最新发布
oTianShangDiXia的博客
06-27 928
项目中有很多ods层（mysql 通过cannal）kafka，需要对这些ods kakfa做一些etl操作后写入下一层的kafka（dwd层）。当有多个ods->dwd操作放在同一个flink作业中时，发现这种方式会导致每次insert操作都是单独的DAG,非常消耗资源，特别是这些处理都是比较轻量级的，最好是能融合在同一个DAG中共享资源。
MySQL EXPLAIN 详解
疯狂的丸子的博客
04-20 322
EXPLAIN 简介 The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. 如官方文档所述，EXPLAIN 语句提供了 MySQL 如何执行一条 SQL 语句的信息。通过使用 EXPLAIN，我们可以查看 SQL 语句的执行方式，进而排查 SQL 语句
Flink SQL: USE Statements
weixin_48813624的博客
11-07 372
Flink SQL: USE Statements
flink批处理访问mysql_Flink批处理读取Hive写入MySql
weixin_39709178的博客
01-18 784
把hive 表stu77 的数据写入 mysql 表test_stu 中.中间可以加自己的逻辑.import org.apache.flink.table.api.EnvironmentSettings;import org.apache.flink.table.api.StatementSet;import org.apache.flink.table.api.Table;import org....
Flink SQL Blink Planner 执行流程解析(上)
http://www.54tianzhisheng.cn/
09-09 714
点击上方 "zhisheng"关注,星标或置顶一起成长Flink 从入门到精通系列文章https://blog.youkuaiyun.com/nazeniwaresakini/a...
flink写入 mysql_flink写入mysql的两种方式
weixin_42553588的博客
01-12 3038
方式一 通过JDBCOutputFormat在flink中没有现成的用来写入MySQL的sink，但是flink提供了一个类，JDBCOutputFormat，通过这个类，如果你提供了jdbc的driver，则可以当做sink使用。JDBCOutputFormat其实是flink的batch api，但也可以用来作为stream的api使用，社区也推荐通过这种方式来进行。JDBCOutputFor...
多库多表场景下使用 Amazon EMR CDC 实时入湖最佳实践
亚马逊云科技专栏
04-10 1110
一、前言 CDC(Change Data Capture) 从广义上讲所有能够捕获变更数据的技术都可以称为 CDC，但本篇文章中对 CDC 的定义限定为以非侵入的方式实时捕获数据库的变更数据。例如：通过解析 MySQL 数据库的 Binlog 日志捕获变更数据，而不是通过 SQL Query 源表捕获变更数据。 Hudi 作为最热的数据湖技术框架之一, 用于构建具有增量数据处理管道的流式数据湖。其核心的能力包括对象存储上数据行级别的快速更新和删除，增量查询(Incremental queries,Time
Flink SQL：流批统一处理详解
"Flink SQL和Table API的使用与理解" Flink SQL是Apache Flink提供的用于处理流数据和批数据的关系型API，它与Table API一起提供了统一的接口，允许开发者用SQL语法来处理流批一体的数据。这两种API的设计目标是...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值