I can share the couple of lines I'm trying for that step, although away from the code at the moment so can't share immediately.
Regards Andrew On Fri, 19 Apr 2019, 21:01 Mark Pasterkamp, <[email protected]> wrote: > 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 > > > >> > > > > > > > > >
