EXPLAIN Statements

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. 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.

  1. StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
  2. StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);
  4. // register a table named "Orders"
  5. tEnv.executeSql("CREATE TABLE MyTable1 (count bigint, work VARCHAR(256) WITH (...)");
  6. tEnv.executeSql("CREATE TABLE MyTable2 (count bigint, work VARCHAR(256) WITH (...)");
  8. // explain SELECT statement through TableEnvironment.explainSql()
  9. String explanation = tEnv.explainSql(
  10.   "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " +
  11.   "UNION ALL " + 
  12.   "SELECT count, word FROM MyTable2");
  13. System.out.println(explanation);
  15. // explain SELECT statement through TableEnvironment.executeSql()
  16. TableResult tableResult = tEnv.executeSql(
  17.   "EXPLAIN PLAN FOR " + 
  18.   "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " +
  19.   "UNION ALL " + 
  20.   "SELECT count, word FROM MyTable2");
  21. tableResult.print();
  1. val env = StreamExecutionEnvironment.getExecutionEnvironment()
  2. val tEnv = StreamTableEnvironment.create(env)
  4. // register a table named "Orders"
  5. tEnv.executeSql("CREATE TABLE MyTable1 (count bigint, work VARCHAR(256) WITH (...)")
  6. tEnv.executeSql("CREATE TABLE MyTable2 (count bigint, work VARCHAR(256) WITH (...)")
  8. // explain SELECT statement through TableEnvironment.explainSql()
  9. val explanation = tEnv.explainSql(
  10.   "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " +
  11.   "UNION ALL " + 
  12.   "SELECT count, word FROM MyTable2")
  13. println(explanation)
  15. // explain SELECT statement through TableEnvironment.executeSql()
  16. val tableResult = tEnv.executeSql(
  17.   "EXPLAIN PLAN FOR " + 
  18.   "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' " +
  19.   "UNION ALL " + 
  20.   "SELECT count, word FROM MyTable2")
  21. tableResult.print()
  1. settings = EnvironmentSettings.new_instance()...
  2. table_env = StreamTableEnvironment.create(env, settings)
  4. t_env.execute_sql("CREATE TABLE MyTable1 (count bigint, work VARCHAR(256) WITH (...)")
  5. t_env.execute_sql("CREATE TABLE MyTable2 (count bigint, work VARCHAR(256) WITH (...)")
  7. # explain SELECT statement through TableEnvironment.explain_sql()
  8. explanation1 = t_env.explain_sql(
  9.     "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' "
  10.     "UNION ALL "
  11.     "SELECT count, word FROM MyTable2")
  12. print(explanation1)
  14. # explain SELECT statement through TableEnvironment.execute_sql()
  15. table_result = t_env.execute_sql(
  16.     "EXPLAIN PLAN FOR "
  17.     "SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' "
  18.     "UNION ALL "
  19.     "SELECT count, word FROM MyTable2")
  20. table_result.print()
  1. Flink SQL> CREATE TABLE MyTable1 (count bigint, work VARCHAR(256);
  2. [INFO] Table has been created.
  4. Flink SQL> CREATE TABLE MyTable2 (count bigint, work VARCHAR(256);
  5. [INFO] Table has been created.
  7. Flink SQL> EXPLAIN PLAN FOR SELECT count, word FROM MyTable1 WHERE word LIKE 'F%' 
  8. > UNION ALL 
  9. > SELECT count, word FROM MyTable2;

The EXPLAIN result is:

  1. == Abstract Syntax Tree ==
  2. LogicalUnion(all=[true])
  3.   LogicalFilter(condition=[LIKE($1, _UTF-16LE'F%')])
  4.     FlinkLogicalTableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word])
  5.   FlinkLogicalTableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word])
  8. == Optimized Logical Plan ==
  9. DataStreamUnion(all=[true], union all=[count, word])
  10.   DataStreamCalc(select=[count, word], where=[LIKE(word, _UTF-16LE'F%')])
  11.     TableSourceScan(table=[[default_catalog, default_database, MyTable1]], fields=[count, word])
  12.   TableSourceScan(table=[[default_catalog, default_database, MyTable2]], fields=[count, word])
  14. == Physical Execution Plan ==
  15. Stage 1 : Data Source
  16.     content : collect elements with CollectionInputFormat
  18. Stage 2 : Data Source
  19.     content : collect elements with CollectionInputFormat
  21.     Stage 3 : Operator
  22.         content : from: (count, word)
  23.         ship_strategy : REBALANCE
  25.         Stage 4 : Operator
  26.             content : where: (LIKE(word, _UTF-16LE'F%')), select: (count, word)
  27.             ship_strategy : FORWARD
  29.             Stage 5 : Operator
  30.                 content : from: (count, word)
  31.                 ship_strategy : REBALANCE

Back to top

Syntax

  1. EXPLAIN PLAN FOR <query_statement_or_insert_statement>

For query syntax, please refer to Queries page. For INSERT, please refer to INSERT page.