We went down the route of wrapping Calcite with our own JDBC driver that strips out the `VALIDTIME AS OF (...)` from ``VALIDTIME AS OF (...) SELECT * FROM FOO`. We do this by overriding CalcitePrepareImpl and adding internalParameters to the CalciteSignature, that our enumerator then uses when executing the actual query. Any feedback on this approach is welcome :-)
Another question. Our underlying DB supports datetime fields, returning java.util.Dates back from queries for datetime columns. I'm thinking that we ought be able to map these Dates through to a column we define using SqlTypeName/TIMESTAMP. To get this to work though, our enumerator has to convert our dates into millis, for Calcite to then convert them back into java.util.Dates.. Feel like I missing something obvious to skip this conversion? On Mon, 27 Apr 2020 at 16:53, Jon Pither <jon.pit...@gmail.com> wrote: > Hi, > > Another route we're looking at is to use `ALTER SESSION SET VALID_TIME = > date('2010....')`. When we experiment with this - hoping to trigger > `SqlSetOption` - we get an java.lang.UnsupportedOperationException: > > CalcitePrepareImpl.java: 369 > org.apache.calcite.prepare.CalcitePrepareImpl/executeDdl > > How could we make use of SqlSetOption? Do we need to extend the parser or > is there a simpler way? > > Regards, > > Jon. > > > On Mon, 27 Apr 2020 at 13:30, Jon Pither <jon.pit...@gmail.com> wrote: > >> Hi Stamatis & Calcite team, >> >> Thanks for your response. We've made some good progress since - following >> JdbcConvention as you suggest - and now we've got the Crux adapter handling >> joins, sorts and more. We're in a good place I feel, and it's exciting to >> see Calcite providing a SQL layer on top of our Datalog. Thanks again :-) >> >> One Q: is it possible to extend the Calcite parser to do the following: >> `VALIDTIME AS OF date('2010...') SELECT * FROM FOO`. So far I've played >> with extending the parser using fmpp & javacc and it certainly feels >> doable, but I can't quite grok what the extension point would be in Calcite >> to add this - for example you can hang off arbitrary extensions from >> subtrees such as CREATE and DROP (by extending SqlCreate and SqlDrop >> respectively)... where might an arbitrary precursor command such as >> `VALIDTIME AS OF date()` fit in? >> >> Regards, >> >> Jon. >> >> >> On Tue, 21 Apr 2020 at 22:43, Stamatis Zampetakis <zabe...@gmail.com> >> wrote: >> >>> Hi Jon, >>> >>> Thanks for your kind words. I'm sure people working on the project are >>> very >>> happy to receive some positive feedback for their work from time to time >>> :) >>> >>> I had a quick look on your project and definitely looks interesting. >>> >>> If your engine (Crux) uses better join algorithms than the ones provided >>> by >>> Calcite and if you have an optimizer that can apply join re-ordering and >>> other optimization techniques efficiently then I guess going further and >>> pushing joins and other things to Crux is a good idea. >>> >>> Having said that, I am not sure if the TranslatableTable approach will >>> get >>> you much further to this direction. >>> I would suggest to have a look in JdbcConvention [1] and see how the >>> notion >>> of Convention along with the respective rules and relational expressions >>> help to push operations into traditional RDBMs. The Cassandra, Mongo, and >>> Elastic adapters are not a very good example since the underlying engines >>> do not support joins. >>> >>> I am not aware if there are people offering consulting services for >>> Calcite >>> but I guess if there are you will know already. >>> Apart from that the project has many volunteers willing to help so if you >>> have more questions don't hesitate to send them to this list. >>> >>> Best, >>> Stamatis >>> >>> [1] >>> >>> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcConvention.java >>> >>> >>> On Tue, Apr 7, 2020, 12:22 PM Jon Pither <jon.pit...@gmail.com> wrote: >>> >>> > Hi Calcite Devs, >>> > >>> > Firstly, thank you to all of you for building this fantastic tool. >>> > >>> > I'm currently experimenting with using Calcite on top of our document >>> > database Crux (opencrux.com) offering bitemporal features using a >>> Datalog >>> > query language. You can see our efforts here, written in Clojure! >>> > >>> > >>> > >>> https://github.com/juxt/crux/blob/jp/calcite/crux-calcite/src/crux/calcite.clj >>> > >>> > >>> https://github.com/juxt/crux/blob/jp/calcite/crux-test/test/crux/calcite_test.clj >>> > >>> > So far we've been impressed at the power Calcite gives, with such >>> little >>> > amount of integration code needed. >>> > >>> > We now have an initial MVP working using the ProjectableFilterableTable >>> > route. The adapter is basically constructing a Datalog query that we >>> then >>> > execute against our DB. >>> > >>> > So far so good, and now I have some initial questions: >>> > >>> > Firstly, in this code we're making use of ProjectableFilterableTable >>> to get >>> > us up and running. I've looked at the Mongo and Elastic adapters in the >>> > Calcite source, and they opt for TranslatableTable which is a deeper >>> > integration. From I can see the immediate disadvantage of >>> > ProjectableFilterableTable is that it's a query per table, meaning >>> that we >>> > can't efficiently delegate joins to our DB. >>> > >>> > Moving to TranslatableTable would be a significant investment for us. >>> My >>> > first question is: would you encourage us to make this investment, >>> given >>> > we've got something up and running using ProjectableFilterableTable, >>> with >>> > Calcite doing the heavy lifting? Please could you also advise on >>> soliciting >>> > mentoring / consulting to help guide us, for which we can compensate. >>> > >>> > Our next question is around temporality. I can see in the Calcite code >>> that >>> > there is a concept of a TemporalTable, supporing "FOR SYSTEM_TIME AS >>> OF X". >>> > It looks like we wouldn't be able to make use of this using >>> > ProjectableFilterableTable, at least this is my experience thus far. In >>> > Crux we also expose VALID_TIME to our users to be able to query for, >>> > whereby users can query against VALID_TIME and/or SYSTEM_TIME. How >>> might >>> > you recommend we achieve this using Calcite? >>> > >>> > Thanks & Regards, >>> > >>> > Jon >>> > >>> >>