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 <[email protected]> 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 <[email protected]>
> 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 <[email protected]> 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
>> >
>>
>
