[jira] [Commented] (CALCITE-3778) Provide ways to view the physical SQLs sent to database

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3778?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17032279#comment-17032279 ] 

Stamatis Zampetakis commented on CALCITE-3778:
----------------------------------------------

FYI: The creation of any JIRA is also send to the dev list so in general it is not necessary to do both. For questions, it is usually preferable to post in the dev list before creating a JIRA case. 

I am copying the reply to [stackoverflow|https://stackoverflow.com/questions/60091348/is-there-any-way-to-view-the-physical-sqls-executed-by-calcite-jdbc] also below.

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|https://github.com/apache/calcite/blob/6ea2fd03cbc366b673ac0adfcd246b2e3e7321dc/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcToEnumerableConverter.java#L98]. 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. 

> Provide ways to view the physical SQLs sent to database
> -------------------------------------------------------
>
>                 Key: CALCITE-3778
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3778
>             Project: Calcite
>          Issue Type: Wish
>          Components: core
>            Reporter: jerryleooo
>            Priority: Minor
>              Labels: newbie
>
> I am using Calcite JDBC, when executing a multiple join, I want to check if the queries/SQLs sent to databases are right. Currently seems there is no easy way to do this.
> I have enabled 'calcite.debug=true' but it just gave me some Java code.
> {code:java}
> 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() 
> }
> {code}
> Tried post the same question on Stackoverflow and mail list but got few replies, so tried here, forgive my taking the liberty.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)