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
>>> >
>>>
>>
