You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by David Holland <de...@gmail.com> on 2016/01/20 04:40:03 UTC
Researcher trying to use Calcite SQL parser
Calcite developers,
I am a university researcher trying to use Calcite to develop a distributed
query capability. I have read the Calcite tutorial and some example code,
but am still unclear on how to create a calcite SQL parser, parse a simple
SQL statement and examine its associated relational expression (tree).
I start by building a JSON object that represents operands consumed by a
JdbcSchema.Factory(), which builds a JdbcSchema supplied by a SQLite
database. Once I have the schema, I assign it to the Calcite framework
using the FrameworkConfig builder, then configure the SQL parser to ignore
case in a query (case insensitive). I verify the schema has been build and
print out its tables successfully, e.g. "Students", "Grades". I then obtain
the PLANNER and parser the following statement:
"SELECT * FROM Students WHERE age > 15.0"
Next, when I attempt to VALIDATE the syntax correctness of the statement,
an error reports:
org.apache.calcite.sql.validate.SqlValidatorException: Table 'STUDENTS' not
found
The framework appears to convert schema table names to UPPERCASE, even
though I try to configure the SQL parser to be case insensitive. It
therefore fails to find the "Students" table imported from the SQLite
database and reports the error.
Finally, If and when I get the SQL parser working, I want to examine the
relational tree produced by the Calcite planner, and perhaps display it
graphically? Later I intend to add rules to the planner that support
distributing a query across multiple nodes.
I see value in the Calcite project and would like to learn more to
contribute. My simple Eclipse program ParseSql.java is attached along with
the trivial SQLite database used to experiment with the parser. Your
suggestion and guidance is very much appreciated.
Is there a developer chat room?
Regards
David Holland
--
David Holland
512-300-8396
Re: Researcher trying to use Calcite SQL parser
Posted by Vladimir Sitnikov <si...@gmail.com>.
>Is there a developer chat room?
There's #calcite at irc.freenode.net.
Not sure if there is a gitter room.
> even though I try to configure the SQL parser to be case insensitive
Can you probably share the code?
I think it should be able to do case-insensitive table names.
Vladimir
Re: Researcher trying to use Calcite SQL parser
Posted by Milinda Pathirage <mp...@umail.iu.edu>.
Hi David,
Have a look at this [1]. I use Calcite's Frameworks class to planner which
can later use to parse, validate and plan SQL queries. I implemented this
base on Apache Drill's query planner. You will have to figure out how to
setup the environment (schemas, operator tables, rules) required to use the
planner. convertToRelNode [2] method converts parsed SQL query to a logical
plan.
Thanks
Milinda
[1]
https://github.com/milinda/samza-sql/blob/master/samza-sql-planner/src/main/java/org/apache/samza/sql/planner/QueryPlanner.java#L95
[2]
https://github.com/milinda/samza-sql/blob/master/samza-sql-planner/src/main/java/org/apache/samza/sql/planner/QueryPlanner.java#L129
On Wed, Jan 20, 2016 at 12:07 PM, Julian Hyde <jh...@apache.org> wrote:
>
> > On Jan 20, 2016, at 9:04 AM, Vladimir Sitnikov <
> sitnikov.vladimir@gmail.com> wrote:
> >
> >> Calcite’s SQL parser is case-sensitive but converts unquoted
> identifiers to upper-case.
> >
> > That is configurable, isn't it?
>
> Yes. As I said in the following paragraph,
>
> > (There are also connect-string options such as Lex=JAVA [1].)
>
> Julian
>
>
--
Milinda Pathirage
PhD Student | Research Assistant
School of Informatics and Computing | Data to Insight Center
Indiana University
twitter: milindalakmal
skype: milinda.pathirage
blog: http://milinda.pathirage.org
Re: Researcher trying to use Calcite SQL parser
Posted by Julian Hyde <jh...@apache.org>.
> On Jan 20, 2016, at 9:04 AM, Vladimir Sitnikov <si...@gmail.com> wrote:
>
>> Calcite’s SQL parser is case-sensitive but converts unquoted identifiers to upper-case.
>
> That is configurable, isn't it?
Yes. As I said in the following paragraph,
> (There are also connect-string options such as Lex=JAVA [1].)
Julian
Re: Researcher trying to use Calcite SQL parser
Posted by Vladimir Sitnikov <si...@gmail.com>.
>Calcite’s SQL parser is case-sensitive but converts unquoted identifiers to upper-case.
That is configurable, isn't it?
Vladimir
Re: Researcher trying to use Calcite SQL parser
Posted by Julian Hyde <jh...@apache.org>.
Calcite’s SQL parser is case-sensitive but converts unquoted identifiers to upper-case. To get the result you want you should enclose table and column names in double-quotes:
SELECT * FROM “Students" WHERE “age" > 15.0
(There are also connect-string options such as Lex=JAVA [1].)
To see the plan use the EXPLAIN command:
# Logical plan
EXPLAIN PLAN FOR SELECT * FROM “Students”;
# In XML format
EXPLAIN PLAN AS XML FOR SELECT * FROM “Students”;
# Physical plan
EXPLAIN PLAN WITH IMPLEMENTATION FOR SELECT * FROM “Students”;
Julian
[1] https://calcite.apache.org/apidocs/org/apache/calcite/config/Lex.html <https://calcite.apache.org/apidocs/org/apache/calcite/config/Lex.html>
> On Jan 19, 2016, at 7:40 PM, David Holland <de...@gmail.com> wrote:
>
> Calcite developers,
>
> I am a university researcher trying to use Calcite to develop a distributed query capability. I have read the Calcite tutorial and some example code, but am still unclear on how to create a calcite SQL parser, parse a simple SQL statement and examine its associated relational expression (tree).
>
> I start by building a JSON object that represents operands consumed by a JdbcSchema.Factory(), which builds a JdbcSchema supplied by a SQLite database. Once I have the schema, I assign it to the Calcite framework using the FrameworkConfig builder, then configure the SQL parser to ignore case in a query (case insensitive). I verify the schema has been build and print out its tables successfully, e.g. "Students", "Grades". I then obtain the PLANNER and parser the following statement:
>
> "SELECT * FROM Students WHERE age > 15.0"
>
> Next, when I attempt to VALIDATE the syntax correctness of the statement, an error reports:
>
> org.apache.calcite.sql.validate.SqlValidatorException: Table 'STUDENTS' not found
>
> The framework appears to convert schema table names to UPPERCASE, even though I try to configure the SQL parser to be case insensitive. It therefore fails to find the "Students" table imported from the SQLite database and reports the error.
>
> Finally, If and when I get the SQL parser working, I want to examine the relational tree produced by the Calcite planner, and perhaps display it graphically? Later I intend to add rules to the planner that support distributing a query across multiple nodes.
>
> I see value in the Calcite project and would like to learn more to contribute. My simple Eclipse program ParseSql.java is attached along with the trivial SQLite database used to experiment with the parser. Your suggestion and guidance is very much appreciated.
>
> Is there a developer chat room?
>
> Regards
> David Holland
>
>
> --
> David Holland
> 512-300-8396 <tel:512-300-8396><ParseSQL.java>