Thanks for the pointers. I've been able to get an early version of what I was looking do working based looking at CalcitePrepareImpl and some of the examples in https://medium.com/@mpathirage/query-planning-with-apache-calcite-part-1-fe957b011c36 .
Essentially my code is currently: a) Creating an instance of the FrameworkConfig and using that to get a instance of the Planner b) Using the planner, I "parse" the input / ad-hoc sql query string to a SqlNode c) Then again using the planner, I perform a "validate" on the SqlNode to get the logical plan as a RelNode d) From there I use the planner to perform a "transform" (with a traitset of EnumerableConvention) e) Subsequently I'm using JdbcImplementor to convert the RelNode to SqlImplementor.Result f) Finally I'm using SqlPrettyWriter to conver the SqlNode back to a string in the required dialect It's still work in progress (and I'm still learning!), but at the moment this seems to give me back some promising results. Thanks, Andrew On Thu, 18 Apr 2019, 12:35 Hongze Zhang, <[email protected]> wrote: > Hi Andrew, > > My feeling is that maybe what you actually need is the util class > RelToSqlConverter[1]. But if you are using JdbcAdapter, it does not always > translate the whole SQL you executed as you want. For example in this > case[2], the logical plan is only partially pushed down to the source > storage. Which means, the SQL Calcite uses to query your database is not > always equivalent to your original input. > > Best, > Hongze > > > [1] > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java > [2] > https://github.com/apache/calcite/blob/b03cdc486cf5c7232bbc6fa9b5f02f564e9601c3/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java#L426-L431 > > > On Apr 18, 2019, at 14:50, Andrew O <[email protected]> wrote: > > > > Thanks. To try and illustrate a little more - I have users creating > ad-hoc > > SQL that provides the data they want, but may not be written optimally. > > I'd like to be able to use Calcite in some form to accept a SQL string > > from them and return an improved / optimised SQL string. > > > > For my use case, calcite can have a live connection to the DBMS to get > > schema information and and other metadata it needs for optimization > > purposes. However I would like for it to avoid the normal final step of > > actually executing the user's query to fetch data. > > > > Hopefully that explains the goal / context better, but if not please let > > me know. > > > > Regards > > > > Andrew > > > > On Thu, 18 Apr 2019, 05:34 Yuzhao Chen, <[email protected]> wrote: > > > >> Andrew, I’m not very sure if I got your idea right. > >> > >> Do you want a promoted `Sql` compared to the original ? Then you should > >> transform Calcite’s RelNodes tree to sql, but Calcite do not support > this > >> yet, The SqlNode unparse to sql is actually supported. > >> > >> It is not that equivalent for RelNodes tree and sql text, e.g. How to > >> describe a SemiJoin in sql ? Another question is the pure text to data > >> source will actually have another planning phrase, we can not make sure > >> this plan is the best and efficient. > >> > >> Best, > >> Danny Chan > >> 在 2019年4月18日 +0800 AM5:22，Andrew O <[email protected]>，写道： > >>> I'm interested in using Calcite in a "no execute" mode that would > >>> effectively return the optimized SQL but not actually execute it. The > >>> intention would be to leverage a) the query planning / optimization b) > >> the > >>> support for outputting different SQL dialects. > >>> > >>> I can see some Tests (e.g. JdbcTest.java) use CalciteAssert and that it > >> has > >>> a check "planHasSql" function. This seems to use a Hook to capture the > >> sql > >>> as part of the execution flow, so is not quite what I'm trying to do. > >>> > >>> I appreciate Calcite attempts to provide abstractions for different > >>> execution layers to allow cross adaptor function, so in theory a single > >> SQL > >>> statement may not always be available. However in my context I know it > >>> will be executing only against a single JDBC source and not other > types. > >>> > >>> Is there any suggested / recommended approaches to this, or pointers to > >>> bits of code to look at? > >>> > >>> Thanks in advance > >>> > >>> Andrew > >> > >
