You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Sandeep Nayak <ma...@gmail.com> on 2021/11/24 10:38:19 UTC
Calcite to build a query
Hello,
I have been looking for a library which will allow me to use an object tree
to generate a SQL. Calcite came up in my search but after taking a look at
the codebase my conclusion is that Calcite provides the ability to parse
SQL statements into an object tree represented as SQLNode (and derived
classes) instances which subsequently can be used to perform optimizations
by generating relational algebra for the logical query.
Calcite however does not provide classes which can be used to assemble a
tree and generate SQL statements. From the contracts at least SQLNode takes
in a Parser position and there are no other contracts which indicate such
an option. Can someone confirm if this is inaccurate?
If not Calcite, are there other open source libraries out there which allow
this?
Thanks in advance.
-Sandeep
Re:Calcite to build a query
Posted by Zhe Hu <il...@163.com>.
Hi, Sandeep
If I'm understanding you correctly, it seems that you wanna find a way to transfer a SqlNode or RelNode to SQL statement.
As far as I know, you can take a look at SqlNode, where the SqlNode can be transferred to SQL expression through toString() method. And, RelToSqlConverterTest illustrates how to assemble a SQL by RelNode.
Hope it’s helpful for you.
Best,
Zhe Hu
On 11/24/2021 18:38，Sandeep Nayak<ma...@gmail.com> wrote：
Hello,
I have been looking for a library which will allow me to use an object tree
to generate a SQL. Calcite came up in my search but after taking a look at
the codebase my conclusion is that Calcite provides the ability to parse
SQL statements into an object tree represented as SQLNode (and derived
classes) instances which subsequently can be used to perform optimizations
by generating relational algebra for the logical query.
Calcite however does not provide classes which can be used to assemble a
tree and generate SQL statements. From the contracts at least SQLNode takes
in a Parser position and there are no other contracts which indicate such
an option. Can someone confirm if this is inaccurate?
If not Calcite, are there other open source libraries out there which allow
this?
Thanks in advance.
-Sandeep
Re: Calcite to build a query
Posted by Sandeep Nayak <ma...@gmail.com>.
Thanks Stamatis for the examples and links.
On Fri, Nov 26, 2021 at 6:05 AM Stamatis Zampetakis <za...@gmail.com>
wrote:
> Hi Sandeep,
>
> If you have a column holding JSON data then if you want to apply predicates
> on some field inside the JSON you most likely need to create calls to
> JSON_EXIST function [1].
> If you check SqlToRelConverterTest you will find some examples of how the
> relational algebra should look like when you use JSON specific functions
> [2].
> To create the plan you will most likely need to use the RelBuilder#call [3]
> API. Have a look in RelBuilderTest for examples of using the call API.
>
> Best,
> Stamatis
>
> [1]
>
> https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java#L1377
> [2]
>
> https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml#L3406
> [3]
>
> https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L661
>
> On Wed, Nov 24, 2021 at 9:43 PM Sandeep Nayak <mailboxforsandeep@gmail.com
> >
> wrote:
>
> > Thanks all for the pointer to go with the relational algebra approach. I
> > will work with that route, I did have a follow up question on that path.
> Is
> > there support in the RelBuilder to apply predicates on JSON data stored
> in
> > a single column in a table?
> >
> > I noted tests in RelToSqlConverterTest for queries on json but did not
> see
> > one which used the RelBuilder to apply predicates on a field which
> carries
> > JSON data. Is there a convention to represent a field inside a json?
> > Example: table t has column a which has a json inside say { "x" :
> > "some-string-value", "y": some-integer-value } and say I want to express
> a
> > predicate like y > some-ceiling-value.
> >
> > -Sandeep
> >
> > On Wed, Nov 24, 2021 at 3:00 AM Florent Martineau <
> ftecmartineau@gmail.com
> > >
> > wrote:
> >
> > > Hi Sandeep,
> > >
> > > I'm no expert so take what I'm saying with a grain of salt.
> > >
> > > I use Calcite to generate queries by using the RelBuilder, which will
> > > create RelNodes that I can then convert to SQL when I need to execute
> the
> > > query.
> > >
> > > From my understanding, if we use Relational Algebra it's because it's
> > much
> > > easier to manipulate and reason with, than SQL. Therefore it makes more
> > > sense for me to build the query using relational algebra and then
> convert
> > > it back to SQL.
> > >
> > > TL;DR: I don't use SQL Nodes to build queries, but RelBuilder then
> > > RelToSQLConverter.
> > >
> > > Hope this helps,
> > >
> > > Florent
> > >
> > > On Wed, Nov 24, 2021, 11:38 AM Sandeep Nayak <
> > mailboxforsandeep@gmail.com>
> > > wrote:
> > >
> > > > Hello,
> > > >
> > > > I have been looking for a library which will allow me to use an
> object
> > > tree
> > > > to generate a SQL. Calcite came up in my search but after taking a
> look
> > > at
> > > > the codebase my conclusion is that Calcite provides the ability to
> > parse
> > > > SQL statements into an object tree represented as SQLNode (and
> derived
> > > > classes) instances which subsequently can be used to perform
> > > optimizations
> > > > by generating relational algebra for the logical query.
> > > >
> > > > Calcite however does not provide classes which can be used to
> assemble
> > a
> > > > tree and generate SQL statements. From the contracts at least SQLNode
> > > takes
> > > > in a Parser position and there are no other contracts which indicate
> > such
> > > > an option. Can someone confirm if this is inaccurate?
> > > >
> > > > If not Calcite, are there other open source libraries out there which
> > > allow
> > > > this?
> > > >
> > > > Thanks in advance.
> > > >
> > > > -Sandeep
> > > >
> > >
> >
>
Re: Calcite to build a query
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Sandeep,
If you have a column holding JSON data then if you want to apply predicates
on some field inside the JSON you most likely need to create calls to
JSON_EXIST function [1].
If you check SqlToRelConverterTest you will find some examples of how the
relational algebra should look like when you use JSON specific functions
[2].
To create the plan you will most likely need to use the RelBuilder#call [3]
API. Have a look in RelBuilderTest for examples of using the call API.
Best,
Stamatis
[1]
https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java#L1377
[2]
https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml#L3406
[3]
https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L661
On Wed, Nov 24, 2021 at 9:43 PM Sandeep Nayak <ma...@gmail.com>
wrote:
> Thanks all for the pointer to go with the relational algebra approach. I
> will work with that route, I did have a follow up question on that path. Is
> there support in the RelBuilder to apply predicates on JSON data stored in
> a single column in a table?
>
> I noted tests in RelToSqlConverterTest for queries on json but did not see
> one which used the RelBuilder to apply predicates on a field which carries
> JSON data. Is there a convention to represent a field inside a json?
> Example: table t has column a which has a json inside say { "x" :
> "some-string-value", "y": some-integer-value } and say I want to express a
> predicate like y > some-ceiling-value.
>
> -Sandeep
>
> On Wed, Nov 24, 2021 at 3:00 AM Florent Martineau <ftecmartineau@gmail.com
> >
> wrote:
>
> > Hi Sandeep,
> >
> > I'm no expert so take what I'm saying with a grain of salt.
> >
> > I use Calcite to generate queries by using the RelBuilder, which will
> > create RelNodes that I can then convert to SQL when I need to execute the
> > query.
> >
> > From my understanding, if we use Relational Algebra it's because it's
> much
> > easier to manipulate and reason with, than SQL. Therefore it makes more
> > sense for me to build the query using relational algebra and then convert
> > it back to SQL.
> >
> > TL;DR: I don't use SQL Nodes to build queries, but RelBuilder then
> > RelToSQLConverter.
> >
> > Hope this helps,
> >
> > Florent
> >
> > On Wed, Nov 24, 2021, 11:38 AM Sandeep Nayak <
> mailboxforsandeep@gmail.com>
> > wrote:
> >
> > > Hello,
> > >
> > > I have been looking for a library which will allow me to use an object
> > tree
> > > to generate a SQL. Calcite came up in my search but after taking a look
> > at
> > > the codebase my conclusion is that Calcite provides the ability to
> parse
> > > SQL statements into an object tree represented as SQLNode (and derived
> > > classes) instances which subsequently can be used to perform
> > optimizations
> > > by generating relational algebra for the logical query.
> > >
> > > Calcite however does not provide classes which can be used to assemble
> a
> > > tree and generate SQL statements. From the contracts at least SQLNode
> > takes
> > > in a Parser position and there are no other contracts which indicate
> such
> > > an option. Can someone confirm if this is inaccurate?
> > >
> > > If not Calcite, are there other open source libraries out there which
> > allow
> > > this?
> > >
> > > Thanks in advance.
> > >
> > > -Sandeep
> > >
> >
>
Re: Calcite to build a query
Posted by Sandeep Nayak <ma...@gmail.com>.
Thanks all for the pointer to go with the relational algebra approach. I
will work with that route, I did have a follow up question on that path. Is
there support in the RelBuilder to apply predicates on JSON data stored in
a single column in a table?
I noted tests in RelToSqlConverterTest for queries on json but did not see
one which used the RelBuilder to apply predicates on a field which carries
JSON data. Is there a convention to represent a field inside a json?
Example: table t has column a which has a json inside say { "x" :
"some-string-value", "y": some-integer-value } and say I want to express a
predicate like y > some-ceiling-value.
-Sandeep
On Wed, Nov 24, 2021 at 3:00 AM Florent Martineau <ft...@gmail.com>
wrote:
> Hi Sandeep,
>
> I'm no expert so take what I'm saying with a grain of salt.
>
> I use Calcite to generate queries by using the RelBuilder, which will
> create RelNodes that I can then convert to SQL when I need to execute the
> query.
>
> From my understanding, if we use Relational Algebra it's because it's much
> easier to manipulate and reason with, than SQL. Therefore it makes more
> sense for me to build the query using relational algebra and then convert
> it back to SQL.
>
> TL;DR: I don't use SQL Nodes to build queries, but RelBuilder then
> RelToSQLConverter.
>
> Hope this helps,
>
> Florent
>
> On Wed, Nov 24, 2021, 11:38 AM Sandeep Nayak <ma...@gmail.com>
> wrote:
>
> > Hello,
> >
> > I have been looking for a library which will allow me to use an object
> tree
> > to generate a SQL. Calcite came up in my search but after taking a look
> at
> > the codebase my conclusion is that Calcite provides the ability to parse
> > SQL statements into an object tree represented as SQLNode (and derived
> > classes) instances which subsequently can be used to perform
> optimizations
> > by generating relational algebra for the logical query.
> >
> > Calcite however does not provide classes which can be used to assemble a
> > tree and generate SQL statements. From the contracts at least SQLNode
> takes
> > in a Parser position and there are no other contracts which indicate such
> > an option. Can someone confirm if this is inaccurate?
> >
> > If not Calcite, are there other open source libraries out there which
> allow
> > this?
> >
> > Thanks in advance.
> >
> > -Sandeep
> >
>
Re: Calcite to build a query
Posted by Florent Martineau <ft...@gmail.com>.
Hi Sandeep,
I'm no expert so take what I'm saying with a grain of salt.
I use Calcite to generate queries by using the RelBuilder, which will
create RelNodes that I can then convert to SQL when I need to execute the
query.
From my understanding, if we use Relational Algebra it's because it's much
easier to manipulate and reason with, than SQL. Therefore it makes more
sense for me to build the query using relational algebra and then convert
it back to SQL.
TL;DR: I don't use SQL Nodes to build queries, but RelBuilder then
RelToSQLConverter.
Hope this helps,
Florent
On Wed, Nov 24, 2021, 11:38 AM Sandeep Nayak <ma...@gmail.com>
wrote:
> Hello,
>
> I have been looking for a library which will allow me to use an object tree
> to generate a SQL. Calcite came up in my search but after taking a look at
> the codebase my conclusion is that Calcite provides the ability to parse
> SQL statements into an object tree represented as SQLNode (and derived
> classes) instances which subsequently can be used to perform optimizations
> by generating relational algebra for the logical query.
>
> Calcite however does not provide classes which can be used to assemble a
> tree and generate SQL statements. From the contracts at least SQLNode takes
> in a Parser position and there are no other contracts which indicate such
> an option. Can someone confirm if this is inaccurate?
>
> If not Calcite, are there other open source libraries out there which allow
> this?
>
> Thanks in advance.
>
> -Sandeep
>
Re: Calcite to build a query
Posted by Cyril Catheu <cy...@startree.ai.INVALID>.
I'm not a Calcite expert but I've recently worked on a project involving
parsing and reconstructing SQL with Calcite.
You may want to have a look at the toSqlString
<https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/SqlNode.html#toSqlString(org.apache.calcite.sql.SqlDialect,boolean)>
method of SqlNode. It generates the SQL statements from the node.
Generation is based on a SQL Dialect, and a lot of Dialects are available
<https://github.com/apache/calcite/tree/master/core/src/main/java/org/apache/calcite/sql/dialect>.
You can build your custom dialect if need be.
Parsing SQL: SqlNode myNode = SqlParser.create(myQuery, mySqlParserConfig);
GeneratingSQL: myNode.toSqlString(myDialect)
Cheers
Cyril de Catheu
On Wed, Nov 24, 2021 at 11:38 AM Sandeep Nayak <ma...@gmail.com>
wrote:
> Hello,
>
> I have been looking for a library which will allow me to use an object tree
> to generate a SQL. Calcite came up in my search but after taking a look at
> the codebase my conclusion is that Calcite provides the ability to parse
> SQL statements into an object tree represented as SQLNode (and derived
> classes) instances which subsequently can be used to perform optimizations
> by generating relational algebra for the logical query.
>
> Calcite however does not provide classes which can be used to assemble a
> tree and generate SQL statements. From the contracts at least SQLNode takes
> in a Parser position and there are no other contracts which indicate such
> an option. Can someone confirm if this is inaccurate?
>
> If not Calcite, are there other open source libraries out there which allow
> this?
>
> Thanks in advance.
>
> -Sandeep
>