Dear Andrew, I would like to do something similar like you are doing and I was wondering how you were able to transform the relnode using the planner with the traitset, would you mind sharing those lines of code with me? Also, something which might interest you is the following example: https://github.com/michaelmior/calcite-notebooks/blob/master/query-optimization.ipynb. It can show you how to create a volcano planner to further optimize the query. You could even add materialized table information to the volcano planner to also rewrite the queries using materialized views if that is something you would be interested in.
With kind regards, Mark On Thu, 18 Apr 2019 at 22:54, Andrew O <[email protected]> wrote: > 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 > > >> > > > > >
