You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Enrico Olivelli <eo...@gmail.com> on 2017/09/26 14:36:57 UTC
Starting with Apache Calcite as SQLPlanner
Hi,
I would like to use Calcite as SQLPlanner in my open source project HerdDB
https://github.com/diennea/herddb
HerdDB is a distributed database built in Java, it is essentially a
distributed key-value store but is has an SQL layer which is the entrypoint
for JDBC clients.
Currently we have a very simple SQLPlanner, that is a component which maps
SQL language to an internal AST which represents the access plan to data.
I heard about Calcite and as far as I can see it would be easy to integrate
Calcite as SQL Planner.
I am expecting to have something like this:
1) Give to Calcite an SQL query + Current schema + available indexes....
2) Calcite which creates a access plan using Calcite AST
3) Convert Calcite AST to HerdDB access plan AST
I am not looking at something on the client-side I would like to use
Calcite on the server
is is possible ?
Thank you in advance
Enrico Olivelli
eolivelli@apache.org
Re: Starting with Apache Calcite as SQLPlanner
Posted by Enrico Olivelli <eo...@gmail.com>.
Thank you all very much!
You gave me good ideas and pointers!
I hope I will find my way for indexes...
I will be back with news
Enrico
On mar 26 set 2017, 19:50 Julian Hyde <jh...@apache.org> wrote:
> On the subject of indexes. There are a couple of threads on this list
> about how to represent indexes as materialized views (sorted projections)
> so that the planner can consider using them. Phoenix has used this
> technique. I think you could find them if you search.
>
> Julian
>
>
> > On Sep 26, 2017, at 9:38 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
> >
> > Yes, using Planner does abstract away some of the details for you which
> > could be a good way to get started. The code snippet you posted would be
> a
> > good start. core/src/test/java/org/apache/calcite/tools/PlannerTest.java
> > has a lot of examples of the use of the planner interface. If you're
> > looking for a more comprehensive example, Apache Hive uses Calcite's
> > optimizer.
> >
> > The other things you mentioned should all be possible with Calcite and
> > possible to add incrementally.
> >
> > --
> > Michael Mior
> > mmior@apache.org
> >
> > 2017-09-26 11:35 GMT-04:00 Enrico Olivelli <eo...@gmail.com>:
> >
> >> Thank you for your Quick response Michael.
> >> I will try to follow your suggestions
> >> I will be back soon with my results
> >>
> >> There is another API Frameworks.getPlanner() which does something
> similar
> >> Planner planner = Frameworks.getPlanner(config);
> >> SqlNode parsed = planner.parse("SELECT * FROM MYTABLE");
> >> RelRoot rel = planner.rel(parsed);
> >>
> >>
> >> I will have to deal with:
> >> - schema changes
> >> - caching plans
> >> - multiple schemas
> >> - indexes/costs based planer
> >>
> >> Do you (or any one else) have some links to test cases or other usages
> of
> >> Calcite as planner ?
> >>
> >> Cheers
> >> -- Enrico
> >>
> >>
> >>
> >> 2017-09-26 17:27 GMT+02:00 Michael Mior <mm...@uwaterloo.ca>:
> >>
> >>> That's definitely possible and is definitely the kind of use case
> Calcite
> >>> is designed for. In terms of Calcite APIs, the approach would be
> >> something
> >>> like the following:
> >>>
> >>> Use SqlParser to get a SqlNode representing the query
> >>> Implement CatalogReader to provide access to the schema
> >>> Convert the SqlNode to a RelNode (relational algebra expression) using
> >>> SqlToRelConverter
> >>> Construct a RelOptPlanner instance and set the root of your planner to
> >> the
> >>> RelNode from your query
> >>> Call findBestExp on the planner and then walk the generated expression
> >> tree
> >>> to generate a HerdDB plan
> >>>
> >>> This can hopefully get you pointed in the right direction. Personally,
> >> I'm
> >>> not sure how you would best incorporate the use of indexes, but I'm
> sure
> >>> someone else will be able to chime in.
> >>>
> >>> Cheers,
> >>> --
> >>> Michael Mior
> >>> mmior@apache.org
> >>>
> >>> 2017-09-26 10:36 GMT-04:00 Enrico Olivelli <eo...@gmail.com>:
> >>>
> >>>> Hi,
> >>>> I would like to use Calcite as SQLPlanner in my open source project
> >>> HerdDB
> >>>> https://github.com/diennea/herddb
> >>>>
> >>>> HerdDB is a distributed database built in Java, it is essentially a
> >>>> distributed key-value store but is has an SQL layer which is the
> >>> entrypoint
> >>>> for JDBC clients.
> >>>>
> >>>> Currently we have a very simple SQLPlanner, that is a component which
> >>> maps
> >>>> SQL language to an internal AST which represents the access plan to
> >> data.
> >>>>
> >>>> I heard about Calcite and as far as I can see it would be easy to
> >>> integrate
> >>>> Calcite as SQL Planner.
> >>>>
> >>>>
> >>>> I am expecting to have something like this:
> >>>> 1) Give to Calcite an SQL query + Current schema + available
> >> indexes....
> >>>> 2) Calcite which creates a access plan using Calcite AST
> >>>> 3) Convert Calcite AST to HerdDB access plan AST
> >>>>
> >>>> I am not looking at something on the client-side I would like to use
> >>>> Calcite on the server
> >>>> is is possible ?
> >>>>
> >>>> Thank you in advance
> >>>>
> >>>> Enrico Olivelli
> >>>> eolivelli@apache.org
> >>>>
> >>>
> >>
>
> --
-- Enrico Olivelli
Re: Starting with Apache Calcite as SQLPlanner
Posted by Julian Hyde <jh...@apache.org>.
On the subject of indexes. There are a couple of threads on this list about how to represent indexes as materialized views (sorted projections) so that the planner can consider using them. Phoenix has used this technique. I think you could find them if you search.
Julian
> On Sep 26, 2017, at 9:38 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
>
> Yes, using Planner does abstract away some of the details for you which
> could be a good way to get started. The code snippet you posted would be a
> good start. core/src/test/java/org/apache/calcite/tools/PlannerTest.java
> has a lot of examples of the use of the planner interface. If you're
> looking for a more comprehensive example, Apache Hive uses Calcite's
> optimizer.
>
> The other things you mentioned should all be possible with Calcite and
> possible to add incrementally.
>
> --
> Michael Mior
> mmior@apache.org
>
> 2017-09-26 11:35 GMT-04:00 Enrico Olivelli <eo...@gmail.com>:
>
>> Thank you for your Quick response Michael.
>> I will try to follow your suggestions
>> I will be back soon with my results
>>
>> There is another API Frameworks.getPlanner() which does something similar
>> Planner planner = Frameworks.getPlanner(config);
>> SqlNode parsed = planner.parse("SELECT * FROM MYTABLE");
>> RelRoot rel = planner.rel(parsed);
>>
>>
>> I will have to deal with:
>> - schema changes
>> - caching plans
>> - multiple schemas
>> - indexes/costs based planer
>>
>> Do you (or any one else) have some links to test cases or other usages of
>> Calcite as planner ?
>>
>> Cheers
>> -- Enrico
>>
>>
>>
>> 2017-09-26 17:27 GMT+02:00 Michael Mior <mm...@uwaterloo.ca>:
>>
>>> That's definitely possible and is definitely the kind of use case Calcite
>>> is designed for. In terms of Calcite APIs, the approach would be
>> something
>>> like the following:
>>>
>>> Use SqlParser to get a SqlNode representing the query
>>> Implement CatalogReader to provide access to the schema
>>> Convert the SqlNode to a RelNode (relational algebra expression) using
>>> SqlToRelConverter
>>> Construct a RelOptPlanner instance and set the root of your planner to
>> the
>>> RelNode from your query
>>> Call findBestExp on the planner and then walk the generated expression
>> tree
>>> to generate a HerdDB plan
>>>
>>> This can hopefully get you pointed in the right direction. Personally,
>> I'm
>>> not sure how you would best incorporate the use of indexes, but I'm sure
>>> someone else will be able to chime in.
>>>
>>> Cheers,
>>> --
>>> Michael Mior
>>> mmior@apache.org
>>>
>>> 2017-09-26 10:36 GMT-04:00 Enrico Olivelli <eo...@gmail.com>:
>>>
>>>> Hi,
>>>> I would like to use Calcite as SQLPlanner in my open source project
>>> HerdDB
>>>> https://github.com/diennea/herddb
>>>>
>>>> HerdDB is a distributed database built in Java, it is essentially a
>>>> distributed key-value store but is has an SQL layer which is the
>>> entrypoint
>>>> for JDBC clients.
>>>>
>>>> Currently we have a very simple SQLPlanner, that is a component which
>>> maps
>>>> SQL language to an internal AST which represents the access plan to
>> data.
>>>>
>>>> I heard about Calcite and as far as I can see it would be easy to
>>> integrate
>>>> Calcite as SQL Planner.
>>>>
>>>>
>>>> I am expecting to have something like this:
>>>> 1) Give to Calcite an SQL query + Current schema + available
>> indexes....
>>>> 2) Calcite which creates a access plan using Calcite AST
>>>> 3) Convert Calcite AST to HerdDB access plan AST
>>>>
>>>> I am not looking at something on the client-side I would like to use
>>>> Calcite on the server
>>>> is is possible ?
>>>>
>>>> Thank you in advance
>>>>
>>>> Enrico Olivelli
>>>> eolivelli@apache.org
>>>>
>>>
>>
Re: Starting with Apache Calcite as SQLPlanner
Posted by Michael Mior <mm...@uwaterloo.ca>.
Yes, using Planner does abstract away some of the details for you which
could be a good way to get started. The code snippet you posted would be a
good start. core/src/test/java/org/apache/calcite/tools/PlannerTest.java
has a lot of examples of the use of the planner interface. If you're
looking for a more comprehensive example, Apache Hive uses Calcite's
optimizer.
The other things you mentioned should all be possible with Calcite and
possible to add incrementally.
--
Michael Mior
mmior@apache.org
2017-09-26 11:35 GMT-04:00 Enrico Olivelli <eo...@gmail.com>:
> Thank you for your Quick response Michael.
> I will try to follow your suggestions
> I will be back soon with my results
>
> There is another API Frameworks.getPlanner() which does something similar
> Planner planner = Frameworks.getPlanner(config);
> SqlNode parsed = planner.parse("SELECT * FROM MYTABLE");
> RelRoot rel = planner.rel(parsed);
>
>
> I will have to deal with:
> - schema changes
> - caching plans
> - multiple schemas
> - indexes/costs based planer
>
> Do you (or any one else) have some links to test cases or other usages of
> Calcite as planner ?
>
> Cheers
> -- Enrico
>
>
>
> 2017-09-26 17:27 GMT+02:00 Michael Mior <mm...@uwaterloo.ca>:
>
> > That's definitely possible and is definitely the kind of use case Calcite
> > is designed for. In terms of Calcite APIs, the approach would be
> something
> > like the following:
> >
> > Use SqlParser to get a SqlNode representing the query
> > Implement CatalogReader to provide access to the schema
> > Convert the SqlNode to a RelNode (relational algebra expression) using
> > SqlToRelConverter
> > Construct a RelOptPlanner instance and set the root of your planner to
> the
> > RelNode from your query
> > Call findBestExp on the planner and then walk the generated expression
> tree
> > to generate a HerdDB plan
> >
> > This can hopefully get you pointed in the right direction. Personally,
> I'm
> > not sure how you would best incorporate the use of indexes, but I'm sure
> > someone else will be able to chime in.
> >
> > Cheers,
> > --
> > Michael Mior
> > mmior@apache.org
> >
> > 2017-09-26 10:36 GMT-04:00 Enrico Olivelli <eo...@gmail.com>:
> >
> > > Hi,
> > > I would like to use Calcite as SQLPlanner in my open source project
> > HerdDB
> > > https://github.com/diennea/herddb
> > >
> > > HerdDB is a distributed database built in Java, it is essentially a
> > > distributed key-value store but is has an SQL layer which is the
> > entrypoint
> > > for JDBC clients.
> > >
> > > Currently we have a very simple SQLPlanner, that is a component which
> > maps
> > > SQL language to an internal AST which represents the access plan to
> data.
> > >
> > > I heard about Calcite and as far as I can see it would be easy to
> > integrate
> > > Calcite as SQL Planner.
> > >
> > >
> > > I am expecting to have something like this:
> > > 1) Give to Calcite an SQL query + Current schema + available
> indexes....
> > > 2) Calcite which creates a access plan using Calcite AST
> > > 3) Convert Calcite AST to HerdDB access plan AST
> > >
> > > I am not looking at something on the client-side I would like to use
> > > Calcite on the server
> > > is is possible ?
> > >
> > > Thank you in advance
> > >
> > > Enrico Olivelli
> > > eolivelli@apache.org
> > >
> >
>
Re: Starting with Apache Calcite as SQLPlanner
Posted by Gian Merlino <gi...@imply.io>.
Druid uses Calcite for SQL parsing and planning. The code is all in
https://github.com/druid-io/druid/tree/master/sql/src/main/java/io/druid/sql
if you want to take a look. The user-facing docs are at
http://druid.io/docs/latest/querying/sql.html if you want to see how users
interact with it.
The "PlannerFactory" at
https://github.com/druid-io/druid/blob/master/sql/src/main/java/io/druid/sql/calcite/planner/PlannerFactory.java
is the part that sets up the Calcite stuff and the "DruidPlanner" at
https://github.com/druid-io/druid/blob/master/sql/src/main/java/io/druid/sql/calcite/planner/DruidPlanner.java
is the part that calls into it.
There's also "DruidMeta" at
https://github.com/druid-io/druid/blob/master/sql/src/main/java/io/druid/sql/avatica/DruidMeta.java
which
sets up an Avatica based JDBC server.
Gian
On Tue, Sep 26, 2017 at 8:35 AM, Enrico Olivelli <eo...@gmail.com>
wrote:
> Thank you for your Quick response Michael.
> I will try to follow your suggestions
> I will be back soon with my results
>
> There is another API Frameworks.getPlanner() which does something similar
> Planner planner = Frameworks.getPlanner(config);
> SqlNode parsed = planner.parse("SELECT * FROM MYTABLE");
> RelRoot rel = planner.rel(parsed);
>
>
> I will have to deal with:
> - schema changes
> - caching plans
> - multiple schemas
> - indexes/costs based planer
>
> Do you (or any one else) have some links to test cases or other usages of
> Calcite as planner ?
>
> Cheers
> -- Enrico
>
>
>
> 2017-09-26 17:27 GMT+02:00 Michael Mior <mm...@uwaterloo.ca>:
>
> > That's definitely possible and is definitely the kind of use case Calcite
> > is designed for. In terms of Calcite APIs, the approach would be
> something
> > like the following:
> >
> > Use SqlParser to get a SqlNode representing the query
> > Implement CatalogReader to provide access to the schema
> > Convert the SqlNode to a RelNode (relational algebra expression) using
> > SqlToRelConverter
> > Construct a RelOptPlanner instance and set the root of your planner to
> the
> > RelNode from your query
> > Call findBestExp on the planner and then walk the generated expression
> tree
> > to generate a HerdDB plan
> >
> > This can hopefully get you pointed in the right direction. Personally,
> I'm
> > not sure how you would best incorporate the use of indexes, but I'm sure
> > someone else will be able to chime in.
> >
> > Cheers,
> > --
> > Michael Mior
> > mmior@apache.org
> >
> > 2017-09-26 10:36 GMT-04:00 Enrico Olivelli <eo...@gmail.com>:
> >
> > > Hi,
> > > I would like to use Calcite as SQLPlanner in my open source project
> > HerdDB
> > > https://github.com/diennea/herddb
> > >
> > > HerdDB is a distributed database built in Java, it is essentially a
> > > distributed key-value store but is has an SQL layer which is the
> > entrypoint
> > > for JDBC clients.
> > >
> > > Currently we have a very simple SQLPlanner, that is a component which
> > maps
> > > SQL language to an internal AST which represents the access plan to
> data.
> > >
> > > I heard about Calcite and as far as I can see it would be easy to
> > integrate
> > > Calcite as SQL Planner.
> > >
> > >
> > > I am expecting to have something like this:
> > > 1) Give to Calcite an SQL query + Current schema + available
> indexes....
> > > 2) Calcite which creates a access plan using Calcite AST
> > > 3) Convert Calcite AST to HerdDB access plan AST
> > >
> > > I am not looking at something on the client-side I would like to use
> > > Calcite on the server
> > > is is possible ?
> > >
> > > Thank you in advance
> > >
> > > Enrico Olivelli
> > > eolivelli@apache.org
> > >
> >
>
Re: Starting with Apache Calcite as SQLPlanner
Posted by Enrico Olivelli <eo...@gmail.com>.
Thank you for your Quick response Michael.
I will try to follow your suggestions
I will be back soon with my results
There is another API Frameworks.getPlanner() which does something similar
Planner planner = Frameworks.getPlanner(config);
SqlNode parsed = planner.parse("SELECT * FROM MYTABLE");
RelRoot rel = planner.rel(parsed);
I will have to deal with:
- schema changes
- caching plans
- multiple schemas
- indexes/costs based planer
Do you (or any one else) have some links to test cases or other usages of
Calcite as planner ?
Cheers
-- Enrico
2017-09-26 17:27 GMT+02:00 Michael Mior <mm...@uwaterloo.ca>:
> That's definitely possible and is definitely the kind of use case Calcite
> is designed for. In terms of Calcite APIs, the approach would be something
> like the following:
>
> Use SqlParser to get a SqlNode representing the query
> Implement CatalogReader to provide access to the schema
> Convert the SqlNode to a RelNode (relational algebra expression) using
> SqlToRelConverter
> Construct a RelOptPlanner instance and set the root of your planner to the
> RelNode from your query
> Call findBestExp on the planner and then walk the generated expression tree
> to generate a HerdDB plan
>
> This can hopefully get you pointed in the right direction. Personally, I'm
> not sure how you would best incorporate the use of indexes, but I'm sure
> someone else will be able to chime in.
>
> Cheers,
> --
> Michael Mior
> mmior@apache.org
>
> 2017-09-26 10:36 GMT-04:00 Enrico Olivelli <eo...@gmail.com>:
>
> > Hi,
> > I would like to use Calcite as SQLPlanner in my open source project
> HerdDB
> > https://github.com/diennea/herddb
> >
> > HerdDB is a distributed database built in Java, it is essentially a
> > distributed key-value store but is has an SQL layer which is the
> entrypoint
> > for JDBC clients.
> >
> > Currently we have a very simple SQLPlanner, that is a component which
> maps
> > SQL language to an internal AST which represents the access plan to data.
> >
> > I heard about Calcite and as far as I can see it would be easy to
> integrate
> > Calcite as SQL Planner.
> >
> >
> > I am expecting to have something like this:
> > 1) Give to Calcite an SQL query + Current schema + available indexes....
> > 2) Calcite which creates a access plan using Calcite AST
> > 3) Convert Calcite AST to HerdDB access plan AST
> >
> > I am not looking at something on the client-side I would like to use
> > Calcite on the server
> > is is possible ?
> >
> > Thank you in advance
> >
> > Enrico Olivelli
> > eolivelli@apache.org
> >
>
Re: Starting with Apache Calcite as SQLPlanner
Posted by Michael Mior <mm...@uwaterloo.ca>.
That's definitely possible and is definitely the kind of use case Calcite
is designed for. In terms of Calcite APIs, the approach would be something
like the following:
Use SqlParser to get a SqlNode representing the query
Implement CatalogReader to provide access to the schema
Convert the SqlNode to a RelNode (relational algebra expression) using
SqlToRelConverter
Construct a RelOptPlanner instance and set the root of your planner to the
RelNode from your query
Call findBestExp on the planner and then walk the generated expression tree
to generate a HerdDB plan
This can hopefully get you pointed in the right direction. Personally, I'm
not sure how you would best incorporate the use of indexes, but I'm sure
someone else will be able to chime in.
Cheers,
--
Michael Mior
mmior@apache.org
2017-09-26 10:36 GMT-04:00 Enrico Olivelli <eo...@gmail.com>:
> Hi,
> I would like to use Calcite as SQLPlanner in my open source project HerdDB
> https://github.com/diennea/herddb
>
> HerdDB is a distributed database built in Java, it is essentially a
> distributed key-value store but is has an SQL layer which is the entrypoint
> for JDBC clients.
>
> Currently we have a very simple SQLPlanner, that is a component which maps
> SQL language to an internal AST which represents the access plan to data.
>
> I heard about Calcite and as far as I can see it would be easy to integrate
> Calcite as SQL Planner.
>
>
> I am expecting to have something like this:
> 1) Give to Calcite an SQL query + Current schema + available indexes....
> 2) Calcite which creates a access plan using Calcite AST
> 3) Convert Calcite AST to HerdDB access plan AST
>
> I am not looking at something on the client-side I would like to use
> Calcite on the server
> is is possible ?
>
> Thank you in advance
>
> Enrico Olivelli
> eolivelli@apache.org
>