Avatica results mapping
Hi, I have a data-type I'm mapping as SqlTypeName/OTHER - in this case java.util.UUID. When I return this field from an enumerator via an adapter it works OK - the UUID is passed through into the JDBC result set. Over the wire though, Avatica throws an error " Remote driver error: RuntimeException: Unhandled type in Frame: class java.util.UUID". I'm contemplating what to do here - is it possible to convert the UUID to a string in the case of 'on the wire' for avatica only? But then pass through the UUID for straight through Calcite? Regards, Jon.
Adapter usage of RexImpTable
Hi, Is it possible for an adapter to leverage RexImpTable to handle calculations? >From RexImpTable I can get an Implementor for a given operation in a RexCall, but then the implement method that would generate an expression, takes a RexToLixTranslator. The RexToLixTranslator has a private constructor, so I'm not sure how to get hold of one. I also couldn't find any supporting prior art in the various adapters in the code-base. Does my approach make sense, to want to use Calcite for handling calculations across projects, filters & join conditions? Context is that our database is a rule-engine that runs in-process - I want to pass it arbitrary functions that could potentially wrap Linq expressions, to implement a wider range of SQL backed by Calcite. Regards, Jon.
Re: Building a Calcite Adapter
That last plan should read:
CruxToEnumerableConverter
CruxProject(NAME=[$1], EXPR$1=[*(2, $3)])
CruxTableScan(table=[[crux, PERSON]])
The point being that it would great to get EnumerableCalc going.
I did consult the JDBC adapter and saw it makes use of a JDBCCalc rule -
and I attempted to get a similar rule firing for us - but no joy (I also
couldn't find an example query plan of JdbcCalc in action).
On Fri, 1 May 2020 at 17:39, Jon Pither wrote:
> Hi,
>
> Thanks for bearing with.
>
> I'm stuck as to when EnumerableCalc fires vs Project.
>
> We added a Project rule because it's a) desirable so we only pull back
> fields we need from the underlying db, and b) we needed it for inner join
> joins to be pushed down to our DB.
>
> That is to say that with us implementing a LogicalProject, our query plan
> for "SELECT PERSON.NAME, PLANET.NAME AS PLANET FROM PERSON INNER JOIN
> PLANET ON PERSON.PLANET = PLANET.NAME" is:
>
> CruxToEnumerableConverter
> CruxProject(NAME=[$1], PLANET=[$0])
> CruxJoin(condition=[=($2, $0)], joinType=[inner])
> CruxProject(NAME=[$1])
> CruxTableScan(table=[[crux, PLANET]])
> CruxProject(NAME=[$1], PLANET=[$2])
> CruxTableScan(table=[[crux, PERSON]])
>
> If I remove the Project rule:
>
> EnumerableCalc(expr#0..2=[{inputs}], NAME=[$t0], PLANET=[$t2])
> EnumerableHashJoin(condition=[=($1, $2)], joinType=[inner])
> EnumerableCalc(expr#0..3=[{inputs}], NAME=[$t1], PLANET=[$t2])
> CruxToEnumerableConverter
> CruxTableScan(table=[[crux, PERSON]])
> EnumerableCalc(expr#0..2=[{inputs}], NAME=[$t1])
> CruxToEnumerableConverter
> CruxTableScan(table=[[crux, PLANET]])
>
> So looks we need it for efficient joins - granted I need do to more
> learning to understand exactly why the EnumerableCalc couldn't work at the
> top level only.
>
> The problem following on from this, is that with us implementing
> LogicalProject, EnumerableCalc seems nearly taken out entirely. Therefore
> queries such as:
>
> "SELECT NAME, (2 * PERSON.AGE) FROM PERSON
>
> Have a plan:
>
> CruxToEnumerableConverter
> CruxProject(NAME=[$1], FOO=[true])
> CruxTableScan(table=[[crux, PERSON]]
>
> I'm not sure how to get EnumerableCalc firing again so that the above
> query works.
>
> Regards,
>
> Jon.
>
>
> On Fri, 1 May 2020 at 11:09, Jon Pither wrote:
>
>> Thanks. Another Q:
>>
>> In Crux we store documents, and values within those documents can be
>> Clojure.lang.Keywords. I'm still deciding how to handle them with Calcite -
>> one option being simply to wash them into Strings via the enumerator, which
>> is our first pass.
>>
>> I did experiment with defining this keyword column type as
>> SqlTypeName/OTHER and letting these keywords pass through. This works for
>> Calcite (keywords are in the results returned to the client), but not -
>> understandably - for Avatica, where I get "Remote driver error:
>> RuntimeException: Unhandled type in Frame: class clojure.lang.Keyword".
>>
>> Is there a way achieve both goals - so that using Calcite in-process, the
>> keywords come through the result-set, but then we hook in via Avatica so
>> that before results are serisalised on the wire, they get converted to
>> Strings?
>>
>> Related: to allow users to filter using keywords we offer a function,
>> i.e.: SELECT NAME FROM PERSON WHERE GENDER = KEYWORD('female'), the
>> keywords then get passed down to our underlying query engine.
>>
>> Jon.
>>
>> On Thu, 30 Apr 2020 at 18:30, Julian Hyde wrote:
>>
>>>
>>>
>>> > On Apr 30, 2020, at 8:56 AM, Jon Pither wrote:
>>> >
>>> > 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
>>> > :-)
>>>
>>> Overriding the prepare process with, say, a different parser or
>>> validator is a bit messy but it sounds like you’re doing the right thing.
>>>
>>> > Another question. Our underlying DB supports datetime fields, returning
>>> > java.util.Dates back from queries for datetime columns. I'm thinki
Re: Building a Calcite Adapter
Hi,
Thanks for bearing with.
I'm stuck as to when EnumerableCalc fires vs Project.
We added a Project rule because it's a) desirable so we only pull back
fields we need from the underlying db, and b) we needed it for inner join
joins to be pushed down to our DB.
That is to say that with us implementing a LogicalProject, our query plan
for "SELECT PERSON.NAME, PLANET.NAME AS PLANET FROM PERSON INNER JOIN
PLANET ON PERSON.PLANET = PLANET.NAME" is:
CruxToEnumerableConverter
CruxProject(NAME=[$1], PLANET=[$0])
CruxJoin(condition=[=($2, $0)], joinType=[inner])
CruxProject(NAME=[$1])
CruxTableScan(table=[[crux, PLANET]])
CruxProject(NAME=[$1], PLANET=[$2])
CruxTableScan(table=[[crux, PERSON]])
If I remove the Project rule:
EnumerableCalc(expr#0..2=[{inputs}], NAME=[$t0], PLANET=[$t2])
EnumerableHashJoin(condition=[=($1, $2)], joinType=[inner])
EnumerableCalc(expr#0..3=[{inputs}], NAME=[$t1], PLANET=[$t2])
CruxToEnumerableConverter
CruxTableScan(table=[[crux, PERSON]])
EnumerableCalc(expr#0..2=[{inputs}], NAME=[$t1])
CruxToEnumerableConverter
CruxTableScan(table=[[crux, PLANET]])
So looks we need it for efficient joins - granted I need do to more
learning to understand exactly why the EnumerableCalc couldn't work at the
top level only.
The problem following on from this, is that with us implementing
LogicalProject, EnumerableCalc seems nearly taken out entirely. Therefore
queries such as:
"SELECT NAME, (2 * PERSON.AGE) FROM PERSON
Have a plan:
CruxToEnumerableConverter
CruxProject(NAME=[$1], FOO=[true])
CruxTableScan(table=[[crux, PERSON]]
I'm not sure how to get EnumerableCalc firing again so that the above query
works.
Regards,
Jon.
On Fri, 1 May 2020 at 11:09, Jon Pither wrote:
> Thanks. Another Q:
>
> In Crux we store documents, and values within those documents can be
> Clojure.lang.Keywords. I'm still deciding how to handle them with Calcite -
> one option being simply to wash them into Strings via the enumerator, which
> is our first pass.
>
> I did experiment with defining this keyword column type as
> SqlTypeName/OTHER and letting these keywords pass through. This works for
> Calcite (keywords are in the results returned to the client), but not -
> understandably - for Avatica, where I get "Remote driver error:
> RuntimeException: Unhandled type in Frame: class clojure.lang.Keyword".
>
> Is there a way achieve both goals - so that using Calcite in-process, the
> keywords come through the result-set, but then we hook in via Avatica so
> that before results are serisalised on the wire, they get converted to
> Strings?
>
> Related: to allow users to filter using keywords we offer a function,
> i.e.: SELECT NAME FROM PERSON WHERE GENDER = KEYWORD('female'), the
> keywords then get passed down to our underlying query engine.
>
> Jon.
>
> On Thu, 30 Apr 2020 at 18:30, Julian Hyde wrote:
>
>>
>>
>> > On Apr 30, 2020, at 8:56 AM, Jon Pither wrote:
>> >
>> > 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
>> > :-)
>>
>> Overriding the prepare process with, say, a different parser or validator
>> is a bit messy but it sounds like you’re doing the right thing.
>>
>> > 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?
>>
>> You should probably use java.sql.Date, java.sql.Timestamp etc. They are
>> sub-classes of java.util.Date but convey information about the semantics.
>>
>> Our Enumerable convention decided to use Java long values (or
>> java.lang.Long if nullable) for SQL TIMESTAMP values, and int values for
>> DATE and TIME values. These are lighter weight than java.sql.Timestamp, and
>> also make it less likely to inadvertently do computations in the local time
>> zone.
>>
>> If the enumerator is just passing values through, then conversion to and
>> from might seem like significant effort. But sorry, it’s just how it is.
>>
>> Julian
>>
>>
Re: Building a Calcite Adapter
Thanks. Another Q:
In Crux we store documents, and values within those documents can be
Clojure.lang.Keywords. I'm still deciding how to handle them with Calcite -
one option being simply to wash them into Strings via the enumerator, which
is our first pass.
I did experiment with defining this keyword column type as
SqlTypeName/OTHER and letting these keywords pass through. This works for
Calcite (keywords are in the results returned to the client), but not -
understandably - for Avatica, where I get "Remote driver error:
RuntimeException: Unhandled type in Frame: class clojure.lang.Keyword".
Is there a way achieve both goals - so that using Calcite in-process, the
keywords come through the result-set, but then we hook in via Avatica so
that before results are serisalised on the wire, they get converted to
Strings?
Related: to allow users to filter using keywords we offer a function,
i.e.: SELECT NAME FROM PERSON WHERE GENDER = KEYWORD('female'), the
keywords then get passed down to our underlying query engine.
Jon.
On Thu, 30 Apr 2020 at 18:30, Julian Hyde wrote:
>
>
> > On Apr 30, 2020, at 8:56 AM, Jon Pither wrote:
> >
> > 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
> > :-)
>
> Overriding the prepare process with, say, a different parser or validator
> is a bit messy but it sounds like you’re doing the right thing.
>
> > 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?
>
> You should probably use java.sql.Date, java.sql.Timestamp etc. They are
> sub-classes of java.util.Date but convey information about the semantics.
>
> Our Enumerable convention decided to use Java long values (or
> java.lang.Long if nullable) for SQL TIMESTAMP values, and int values for
> DATE and TIME values. These are lighter weight than java.sql.Timestamp, and
> also make it less likely to inadvertently do computations in the local time
> zone.
>
> If the enumerator is just passing values through, then conversion to and
> from might seem like significant effort. But sorry, it’s just how it is.
>
> Julian
>
>
Re: Building a Calcite Adapter
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 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 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
>> 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 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 Cl
Re: Building a Calcite Adapter
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 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
> 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 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.
>&
Re: Building a Calcite Adapter
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 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 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
> >
>
Building a Calcite Adapter
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
