4

Recently I am studying Apache Calcite, by now I can use explain plan for via JDBC to view the logical plan, and I am wondering how can I view the physical sql in the plan execution? Since there may be bugs in the physical sql generation so I need to make sure the correctness.

    val connection = DriverManager.getConnection("jdbc:calcite:")
    val calciteConnection = connection.asInstanceOf[CalciteConnection]
    val rootSchema = calciteConnection.getRootSchema()

    val dsInsightUser = JdbcSchema.dataSource("jdbc:mysql://localhost:13306/insight?useSSL=false&serverTimezone=UTC", "com.mysql.jdbc.Driver", "insight_admin","xxxxxx")
    val dsPerm = JdbcSchema.dataSource("jdbc:mysql://localhost:13307/permission?useSSL=false&serverTimezone=UTC", "com.mysql.jdbc.Driver", "perm_admin", "xxxxxx")

    rootSchema.add("insight_user", JdbcSchema.create(rootSchema, "insight_user", dsInsightUser, null, null))
    rootSchema.add("perm", JdbcSchema.create(rootSchema, "perm", dsPerm, null, null))

    val stmt = connection.createStatement()
    val rs = stmt.executeQuery("""explain plan for select "perm"."user_table".* from "perm"."user_table" join "insight_user"."user_tab" on "perm"."user_table"."id"="insight_user"."user_tab"."id" """)
    val metaData = rs.getMetaData()

    while(rs.next()) {
      for(i <- 1 to metaData.getColumnCount) printf("%s ", rs.getObject(i))
      println()
    }

result is 

EnumerableCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}])
  EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
    JdbcToEnumerableConverter
      JdbcTableScan(table=[[perm, user_table]])
    JdbcToEnumerableConverter
      JdbcProject(id=[$0])
        JdbcTableScan(table=[[insight_user, user_tab]])
jerryleooo
  • 843
  • 10
  • 16

2 Answers2

4

There is a Calcite Hook, Hook.QUERY_PLAN that is triggered with the JDBC query strings. From the source:

/** Called with a query that has been generated to send to a back-end system.
* The query might be a SQL string (for the JDBC adapter), a list of Mongo
* pipeline expressions (for the MongoDB adapter), et cetera. */
QUERY_PLAN;

You can register a listener to log any query strings, like this in Java:

Hook.QUERY_PLAN.add((Consumer<String>) s -> LOG.info("Query sent over JDBC:\n" + s));
Mzzzzzz
  • 4,770
  • 7
  • 30
  • 47
3

It is possible to see the generated SQL query by setting calcite.debug=true system property. The exact place where this is happening is in JdbcToEnumerableConverter. As this is happening during the execution of the query you will have to remove the "explain plan for" from stmt.executeQuery.

Note that by setting debug mode to true you will get a lot of other messages as well as other information regarding generated code.

zabetak
  • 412
  • 4
  • 13