You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Anton Petrov <pe...@gmail.com> on 2014/11/13 21:11:19 UTC
Calcite with MySQL
All,
I am trying to use Calcite with MySQL as a backend storage but I keep
getting exceptions when attempting to query a table. Here are the details
of my setup:
- I have checked out calcite-0.9.1-incubating
- running Ubuntu 12.04 with MySQL 5.5
- the mysql.json model I am using has been attached to this e-mail
I am able to list all tables in the MySQL instance (I've loaded up the
TPC-H tables) but when I attempt to query any individual one, I get into
trouble. Here are the steps I perform once sqlline fires up:
sqlline version 1.1.7
sqlline> !verbose
verbose: on
sqlline> !connect jdbc:calcite:model=mysql.json root rootpass
Connecting to jdbc:calcite:model=mysql.json
Connected to: Calcite (version 0.9.1-incubating)
Driver: Calcite JDBC Driver (version 0.9.1-incubating)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:calcite:model=mysql.json> !tables
+-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_CAT |
TYPE_SCHEM | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION |
+-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
| null | metadata | COLUMNS | SYSTEM_TABLE | null | null
| null | null | null | null |
| null | metadata | TABLES | SYSTEM_TABLE | null | null
| null | null | null | null |
| null | tpch | customer | TABLE | null | null |
null | null | null | null |
| null | tpch | lineitem | TABLE | null | null |
null | null | null | null |
| null | tpch | nation | TABLE | null | null |
null | null | null | null |
| null | tpch | orders | TABLE | null | null |
null | null | null | null |
| null | tpch | part | TABLE | null | null |
null | null | null | null |
| null | tpch | partsupp | TABLE | null | null |
null | null | null | null |
| null | tpch | region | TABLE | null | null |
null | null | null | null |
| null | tpch | supplier | TABLE | null | null |
null | null | null | null |
+-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
0: jdbc:calcite:model=mysql.json> select * from nation;
Nov 13, 2014 2:57:56 PM org.eigenbase.sql.validate.SqlValidatorException
<init>
SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Table 'NATION'
not found
Nov 13, 2014 2:57:56 PM org.eigenbase.util.EigenbaseException <init>
SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1, column
15 to line 1, column 20: Table 'NATION' not found
Error: while executing SQL: select * from nation (state=,code=0)
java.sql.SQLException: while executing SQL: select * from nation
at net.hydromatic.avatica.Helper.createException(Helper.java:39)
at
net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:70)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)
Caused by: org.eigenbase.util.EigenbaseContextException: From line 1,
column 15 to line 1, column 20: Table 'NATION' not found
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at
org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:671)
at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:659)
at
org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3551)
at
org.eigenbase.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:76)
at
org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at
org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:747)
at
org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:736)
at
org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2599)
at
org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2584)
at
org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2802)
at
org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:59)
at
org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at
org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:747)
at
org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:736)
at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:207)
at
org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:710)
at
org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:426)
at
org.eigenbase.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:406)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:211)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:178)
at
net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare2_(OptiqPrepareImpl.java:412)
at
net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:318)
at
net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:287)
at
net.hydromatic.optiq.jdbc.OptiqConnectionImpl.parseQuery(OptiqConnectionImpl.java:170)
at net.hydromatic.optiq.jdbc.MetaImpl.prepare(MetaImpl.java:617)
at
net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:67)
... 6 more
Caused by: org.eigenbase.sql.validate.SqlValidatorException: Table 'NATION'
not found
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at
org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
at org.eigenbase.resource.Resources$ExInst.ex(Resources.java:457)
... 32 more
Am I missing something? Any and all help is greatly appreciated!
--Anton
Re: Calcite with MySQL
Posted by Anton Petrov <pe...@gmail.com>.
Adding double quotes around the table name fixed it :) Thanks!
--Anton
On Wed, Nov 19, 2014 at 1:45 PM, Julian Hyde <ju...@gmail.com> wrote:
> Anton,
>
> This might be as simple as case-sensitivity. Try
>
> > select * from “nation”;
>
> or
>
> > select * from “tech”.”nation”;
>
> and let me know whether that works.
>
> By default Calcite is case-sensitive. You can see that in the catalog the
> tables are defined with lower-case names, and when you use unquoted
> identifiers in a query, they get converted to upper-case before they are
> resolved. (See
> https://groups.google.com/forum/#!topic/optiq-dev/VF8f8HWP-9g.)
>
> Calcite has a mode where it resolves names case-insensitively, and if
> case-sensitivity turns out to the be problem I’ll show you how to use it.
>
> Sorry your message got ignored for several days. It somehow dropped off
> the first page of my inbox. Feel free to bump in the future if you get
> ignored.
>
> Julian
>
>
> On Nov 19, 2014, at 10:37 AM, Anton Petrov <pe...@gmail.com> wrote:
>
> > Bump.
> >
> > I still can't solve this issue. My problem is probably too user focused
> to
> > be posted on the dev list but I couldn't find a user list so I thought
> this
> > one would be the second best place.
> >
> > I am trying to get this setup so I can see whether Calcite can handle
> TPC-H
> > queries unmodified (or with slight modifications) and how it handles the
> > re-writing of sub-queries in TPC-H. I would like to trace the code with
> my
> > debugger and get a grip on the data structures and overall design. Have
> > people setup Calcite with a MySQL backend before? The docs imply that any
> > JDBC compatible backend should work. I understand it's a busy time with
> the
> > move to Apache and all the package renames, etc., so any hints, however
> > trivial, would be greatly appreciated!
> >
> > Thanks,
> >
> > --Anton
> >
> > On Thu, Nov 13, 2014 at 3:11 PM, Anton Petrov <pe...@gmail.com>
> wrote:
> >
> >> All,
> >>
> >> I am trying to use Calcite with MySQL as a backend storage but I keep
> >> getting exceptions when attempting to query a table. Here are the
> details
> >> of my setup:
> >>
> >> - I have checked out calcite-0.9.1-incubating
> >> - running Ubuntu 12.04 with MySQL 5.5
> >> - the mysql.json model I am using has been attached to this e-mail
> >>
> >> I am able to list all tables in the MySQL instance (I've loaded up the
> >> TPC-H tables) but when I attempt to query any individual one, I get into
> >> trouble. Here are the steps I perform once sqlline fires up:
> >>
> >> sqlline version 1.1.7
> >> sqlline> !verbose
> >> verbose: on
> >> sqlline> !connect jdbc:calcite:model=mysql.json root rootpass
> >> Connecting to jdbc:calcite:model=mysql.json
> >> Connected to: Calcite (version 0.9.1-incubating)
> >> Driver: Calcite JDBC Driver (version 0.9.1-incubating)
> >> Autocommit status: true
> >> Transaction isolation: TRANSACTION_REPEATABLE_READ
> >> 0: jdbc:calcite:model=mysql.json> !tables
> >>
> >>
> +-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
> >> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS |
> TYPE_CAT |
> >> TYPE_SCHEM | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION |
> >>
> >>
> +-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
> >> | null | metadata | COLUMNS | SYSTEM_TABLE | null | null
> >> | null | null | null | null |
> >> | null | metadata | TABLES | SYSTEM_TABLE | null | null
> >> | null | null | null | null |
> >> | null | tpch | customer | TABLE | null | null
> |
> >> null | null | null | null |
> >> | null | tpch | lineitem | TABLE | null | null
> |
> >> null | null | null | null |
> >> | null | tpch | nation | TABLE | null | null
> |
> >> null | null | null | null |
> >> | null | tpch | orders | TABLE | null | null
> |
> >> null | null | null | null |
> >> | null | tpch | part | TABLE | null | null
> |
> >> null | null | null | null |
> >> | null | tpch | partsupp | TABLE | null | null
> |
> >> null | null | null | null |
> >> | null | tpch | region | TABLE | null | null
> |
> >> null | null | null | null |
> >> | null | tpch | supplier | TABLE | null | null
> |
> >> null | null | null | null |
> >>
> >>
> +-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
> >> 0: jdbc:calcite:model=mysql.json> select * from nation;
> >> Nov 13, 2014 2:57:56 PM org.eigenbase.sql.validate.SqlValidatorException
> >> <init>
> >> SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Table 'NATION'
> >> not found
> >> Nov 13, 2014 2:57:56 PM org.eigenbase.util.EigenbaseException <init>
> >> SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1,
> column
> >> 15 to line 1, column 20: Table 'NATION' not found
> >> Error: while executing SQL: select * from nation (state=,code=0)
> >> java.sql.SQLException: while executing SQL: select * from nation
> >> at net.hydromatic.avatica.Helper.createException(Helper.java:39)
> >> at
> >>
> net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:70)
> >> at sqlline.Commands.execute(Commands.java:822)
> >> at sqlline.Commands.sql(Commands.java:732)
> >> at sqlline.SqlLine.dispatch(SqlLine.java:808)
> >> at sqlline.SqlLine.begin(SqlLine.java:681)
> >> at sqlline.SqlLine.start(SqlLine.java:398)
> >> at sqlline.SqlLine.main(SqlLine.java:292)
> >> Caused by: org.eigenbase.util.EigenbaseContextException: From line 1,
> >> column 15 to line 1, column 20: Table 'NATION' not found
> >> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> >> Method)
> >> at
> >>
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
> >> at
> >>
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> >> at
> java.lang.reflect.Constructor.newInstance(Constructor.java:526)
> >> at
> >> org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
> >> at
> org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:671)
> >> at
> org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:659)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3551)
> >> at
> >>
> org.eigenbase.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:76)
> >> at
> >>
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:747)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:736)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2599)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2584)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2802)
> >> at
> >>
> org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:59)
> >> at
> >>
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:747)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:736)
> >> at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:207)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:710)
> >> at
> >>
> org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:426)
> >> at
> >>
> org.eigenbase.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:406)
> >> at
> >> net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:211)
> >> at
> >> net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:178)
> >> at
> >>
> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare2_(OptiqPrepareImpl.java:412)
> >> at
> >>
> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:318)
> >> at
> >>
> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:287)
> >> at
> >>
> net.hydromatic.optiq.jdbc.OptiqConnectionImpl.parseQuery(OptiqConnectionImpl.java:170)
> >> at net.hydromatic.optiq.jdbc.MetaImpl.prepare(MetaImpl.java:617)
> >> at
> >>
> net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:67)
> >> ... 6 more
> >> Caused by: org.eigenbase.sql.validate.SqlValidatorException: Table
> >> 'NATION' not found
> >> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> >> Method)
> >> at
> >>
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
> >> at
> >>
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> >> at
> java.lang.reflect.Constructor.newInstance(Constructor.java:526)
> >> at
> >> org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
> >> at org.eigenbase.resource.Resources$ExInst.ex(Resources.java:457)
> >> ... 32 more
> >>
> >>
> >> Am I missing something? Any and all help is greatly appreciated!
> >>
> >> --Anton
> >>
> >>
>
>
Re: Calcite with MySQL
Posted by Julian Hyde <ju...@gmail.com>.
Anton,
This might be as simple as case-sensitivity. Try
> select * from “nation”;
or
> select * from “tech”.”nation”;
and let me know whether that works.
By default Calcite is case-sensitive. You can see that in the catalog the tables are defined with lower-case names, and when you use unquoted identifiers in a query, they get converted to upper-case before they are resolved. (See https://groups.google.com/forum/#!topic/optiq-dev/VF8f8HWP-9g.)
Calcite has a mode where it resolves names case-insensitively, and if case-sensitivity turns out to the be problem I’ll show you how to use it.
Sorry your message got ignored for several days. It somehow dropped off the first page of my inbox. Feel free to bump in the future if you get ignored.
Julian
On Nov 19, 2014, at 10:37 AM, Anton Petrov <pe...@gmail.com> wrote:
> Bump.
>
> I still can't solve this issue. My problem is probably too user focused to
> be posted on the dev list but I couldn't find a user list so I thought this
> one would be the second best place.
>
> I am trying to get this setup so I can see whether Calcite can handle TPC-H
> queries unmodified (or with slight modifications) and how it handles the
> re-writing of sub-queries in TPC-H. I would like to trace the code with my
> debugger and get a grip on the data structures and overall design. Have
> people setup Calcite with a MySQL backend before? The docs imply that any
> JDBC compatible backend should work. I understand it's a busy time with the
> move to Apache and all the package renames, etc., so any hints, however
> trivial, would be greatly appreciated!
>
> Thanks,
>
> --Anton
>
> On Thu, Nov 13, 2014 at 3:11 PM, Anton Petrov <pe...@gmail.com> wrote:
>
>> All,
>>
>> I am trying to use Calcite with MySQL as a backend storage but I keep
>> getting exceptions when attempting to query a table. Here are the details
>> of my setup:
>>
>> - I have checked out calcite-0.9.1-incubating
>> - running Ubuntu 12.04 with MySQL 5.5
>> - the mysql.json model I am using has been attached to this e-mail
>>
>> I am able to list all tables in the MySQL instance (I've loaded up the
>> TPC-H tables) but when I attempt to query any individual one, I get into
>> trouble. Here are the steps I perform once sqlline fires up:
>>
>> sqlline version 1.1.7
>> sqlline> !verbose
>> verbose: on
>> sqlline> !connect jdbc:calcite:model=mysql.json root rootpass
>> Connecting to jdbc:calcite:model=mysql.json
>> Connected to: Calcite (version 0.9.1-incubating)
>> Driver: Calcite JDBC Driver (version 0.9.1-incubating)
>> Autocommit status: true
>> Transaction isolation: TRANSACTION_REPEATABLE_READ
>> 0: jdbc:calcite:model=mysql.json> !tables
>>
>> +-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
>> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_CAT |
>> TYPE_SCHEM | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION |
>>
>> +-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
>> | null | metadata | COLUMNS | SYSTEM_TABLE | null | null
>> | null | null | null | null |
>> | null | metadata | TABLES | SYSTEM_TABLE | null | null
>> | null | null | null | null |
>> | null | tpch | customer | TABLE | null | null |
>> null | null | null | null |
>> | null | tpch | lineitem | TABLE | null | null |
>> null | null | null | null |
>> | null | tpch | nation | TABLE | null | null |
>> null | null | null | null |
>> | null | tpch | orders | TABLE | null | null |
>> null | null | null | null |
>> | null | tpch | part | TABLE | null | null |
>> null | null | null | null |
>> | null | tpch | partsupp | TABLE | null | null |
>> null | null | null | null |
>> | null | tpch | region | TABLE | null | null |
>> null | null | null | null |
>> | null | tpch | supplier | TABLE | null | null |
>> null | null | null | null |
>>
>> +-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
>> 0: jdbc:calcite:model=mysql.json> select * from nation;
>> Nov 13, 2014 2:57:56 PM org.eigenbase.sql.validate.SqlValidatorException
>> <init>
>> SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Table 'NATION'
>> not found
>> Nov 13, 2014 2:57:56 PM org.eigenbase.util.EigenbaseException <init>
>> SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1, column
>> 15 to line 1, column 20: Table 'NATION' not found
>> Error: while executing SQL: select * from nation (state=,code=0)
>> java.sql.SQLException: while executing SQL: select * from nation
>> at net.hydromatic.avatica.Helper.createException(Helper.java:39)
>> at
>> net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:70)
>> at sqlline.Commands.execute(Commands.java:822)
>> at sqlline.Commands.sql(Commands.java:732)
>> at sqlline.SqlLine.dispatch(SqlLine.java:808)
>> at sqlline.SqlLine.begin(SqlLine.java:681)
>> at sqlline.SqlLine.start(SqlLine.java:398)
>> at sqlline.SqlLine.main(SqlLine.java:292)
>> Caused by: org.eigenbase.util.EigenbaseContextException: From line 1,
>> column 15 to line 1, column 20: Table 'NATION' not found
>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
>> Method)
>> at
>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
>> at
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
>> at
>> org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
>> at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:671)
>> at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:659)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3551)
>> at
>> org.eigenbase.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:76)
>> at
>> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:747)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:736)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2599)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2584)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2802)
>> at
>> org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:59)
>> at
>> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:747)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:736)
>> at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:207)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:710)
>> at
>> org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:426)
>> at
>> org.eigenbase.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:406)
>> at
>> net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:211)
>> at
>> net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:178)
>> at
>> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare2_(OptiqPrepareImpl.java:412)
>> at
>> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:318)
>> at
>> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:287)
>> at
>> net.hydromatic.optiq.jdbc.OptiqConnectionImpl.parseQuery(OptiqConnectionImpl.java:170)
>> at net.hydromatic.optiq.jdbc.MetaImpl.prepare(MetaImpl.java:617)
>> at
>> net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:67)
>> ... 6 more
>> Caused by: org.eigenbase.sql.validate.SqlValidatorException: Table
>> 'NATION' not found
>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
>> Method)
>> at
>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
>> at
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
>> at
>> org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
>> at org.eigenbase.resource.Resources$ExInst.ex(Resources.java:457)
>> ... 32 more
>>
>>
>> Am I missing something? Any and all help is greatly appreciated!
>>
>> --Anton
>>
>>
Re: Calcite with MySQL
Posted by Anton Petrov <pe...@gmail.com>.
Bump.
I still can't solve this issue. My problem is probably too user focused to
be posted on the dev list but I couldn't find a user list so I thought this
one would be the second best place.
I am trying to get this setup so I can see whether Calcite can handle TPC-H
queries unmodified (or with slight modifications) and how it handles the
re-writing of sub-queries in TPC-H. I would like to trace the code with my
debugger and get a grip on the data structures and overall design. Have
people setup Calcite with a MySQL backend before? The docs imply that any
JDBC compatible backend should work. I understand it's a busy time with the
move to Apache and all the package renames, etc., so any hints, however
trivial, would be greatly appreciated!
Thanks,
--Anton
On Thu, Nov 13, 2014 at 3:11 PM, Anton Petrov <pe...@gmail.com> wrote:
> All,
>
> I am trying to use Calcite with MySQL as a backend storage but I keep
> getting exceptions when attempting to query a table. Here are the details
> of my setup:
>
> - I have checked out calcite-0.9.1-incubating
> - running Ubuntu 12.04 with MySQL 5.5
> - the mysql.json model I am using has been attached to this e-mail
>
> I am able to list all tables in the MySQL instance (I've loaded up the
> TPC-H tables) but when I attempt to query any individual one, I get into
> trouble. Here are the steps I perform once sqlline fires up:
>
> sqlline version 1.1.7
> sqlline> !verbose
> verbose: on
> sqlline> !connect jdbc:calcite:model=mysql.json root rootpass
> Connecting to jdbc:calcite:model=mysql.json
> Connected to: Calcite (version 0.9.1-incubating)
> Driver: Calcite JDBC Driver (version 0.9.1-incubating)
> Autocommit status: true
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> 0: jdbc:calcite:model=mysql.json> !tables
>
> +-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_CAT |
> TYPE_SCHEM | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION |
>
> +-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
> | null | metadata | COLUMNS | SYSTEM_TABLE | null | null
> | null | null | null | null |
> | null | metadata | TABLES | SYSTEM_TABLE | null | null
> | null | null | null | null |
> | null | tpch | customer | TABLE | null | null |
> null | null | null | null |
> | null | tpch | lineitem | TABLE | null | null |
> null | null | null | null |
> | null | tpch | nation | TABLE | null | null |
> null | null | null | null |
> | null | tpch | orders | TABLE | null | null |
> null | null | null | null |
> | null | tpch | part | TABLE | null | null |
> null | null | null | null |
> | null | tpch | partsupp | TABLE | null | null |
> null | null | null | null |
> | null | tpch | region | TABLE | null | null |
> null | null | null | null |
> | null | tpch | supplier | TABLE | null | null |
> null | null | null | null |
>
> +-----------+-------------+------------+------------+---------+----------+------------+-----------+---------------------------+----------------+
> 0: jdbc:calcite:model=mysql.json> select * from nation;
> Nov 13, 2014 2:57:56 PM org.eigenbase.sql.validate.SqlValidatorException
> <init>
> SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Table 'NATION'
> not found
> Nov 13, 2014 2:57:56 PM org.eigenbase.util.EigenbaseException <init>
> SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1, column
> 15 to line 1, column 20: Table 'NATION' not found
> Error: while executing SQL: select * from nation (state=,code=0)
> java.sql.SQLException: while executing SQL: select * from nation
> at net.hydromatic.avatica.Helper.createException(Helper.java:39)
> at
> net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:70)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:808)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
> Caused by: org.eigenbase.util.EigenbaseContextException: From line 1,
> column 15 to line 1, column 20: Table 'NATION' not found
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
> at
> org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
> at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:671)
> at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:659)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3551)
> at
> org.eigenbase.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:76)
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:747)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:736)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2599)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2584)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2802)
> at
> org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:59)
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:747)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:736)
> at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:207)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:710)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:426)
> at
> org.eigenbase.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:406)
> at
> net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:211)
> at
> net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:178)
> at
> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare2_(OptiqPrepareImpl.java:412)
> at
> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:318)
> at
> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:287)
> at
> net.hydromatic.optiq.jdbc.OptiqConnectionImpl.parseQuery(OptiqConnectionImpl.java:170)
> at net.hydromatic.optiq.jdbc.MetaImpl.prepare(MetaImpl.java:617)
> at
> net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:67)
> ... 6 more
> Caused by: org.eigenbase.sql.validate.SqlValidatorException: Table
> 'NATION' not found
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
> at
> org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
> at org.eigenbase.resource.Resources$ExInst.ex(Resources.java:457)
> ... 32 more
>
>
> Am I missing something? Any and all help is greatly appreciated!
>
> --Anton
>
>