SQL usability: catalogs, schemas and tables

[Vladimir Ozerov]

Igniters,

[long read, take a cup of coffee]

Historically every SQL in Ignite must be executed against particular cache:
SqlQuery requires cache, JDBC and ODBC drivers require cache name. This
works fine until we add CREATE TABLE. Consider an empty cluster - how do
you connect to it if you have no caches yet? Nohow.

It looks like we cannot have convenient access to cluster unless we
properly define and implement *schema* abstraction. ANSI SQL'92 defines
several abstractions: cluster -> catalog -> schema -> table/view/etc..
Every "catalog" has *INFORMATION_SCHEMA* schema, containing database
metadata. Almost all vendors support it (notable exclusion - Oracle). Looks
like we need to introduce similar concept and finally decouple caches from
schemas.

High-level proposal from my side

1) Let's consider Ignite cluster as a single database ("catalog" in ANSI
SQL'92 terms).

2) It should be possible to connect to the cluster without a single user
cache. In this case schema is not defined.

3) We must have a kind of storage for metadata. It could be either another
system cache, or something analogous to binary metadata cache, which is
essentially node-local data structure exchanged on node join. It should be
aligned well with persistence feature, which is expected in AI 2.1.

4) Content of this storage will be accessible through INFORMATION_SCHEMA
abstraction.

5) We must support "CREATE SCHEMA", "DROP SCHEMA" commands which will
effectively create records in system cache and invoke relevant commands on
local H2 engines of every node (now it happens implicitly on cache
start/stop).

6) From JDBC/ODBC driver perspective schema will be defined either in
connection string, or in runtime through "SET SCHEMA" command which is
already supported by H2.

7) We must finally introduce new native SQL API, which will not use caches
directly. Something like "IgniteSql sql()". See *IGNITE-4701*.

Once schema is there, usage of "CREATE TABLE" and "DROP TABLE" commands
will be simple and convenient, and it will fit naturally in user's past
experience with conventional RDBMS.

Thoughts?

P.S.: CREATE/DROP TABLE feature is not blocked by this problem. It will
work, but will be inconvenient for users.

[1] https://issues.apache.org/jira/browse/IGNITE-4701

[Sergi]

Yes, we need to move on making Ignite work as any usual SQL db.

But please avoid mixing all this stuff together, lets have a separate task
(and discussion if needed) for each item in your list.

Sergi

2017-04-24 16:58 GMT+03:00 Vladimir Ozerov <[hidden email]>:

> Igniters,
>
> [long read, take a cup of coffee]
>
> Historically every SQL in Ignite must be executed against particular cache:
> SqlQuery requires cache, JDBC and ODBC drivers require cache name. This
> works fine until we add CREATE TABLE. Consider an empty cluster - how do
> you connect to it if you have no caches yet? Nohow.
>
> It looks like we cannot have convenient access to cluster unless we
> properly define and implement *schema* abstraction. ANSI SQL'92 defines
> several abstractions: cluster -> catalog -> schema -> table/view/etc..
> Every "catalog" has *INFORMATION_SCHEMA* schema, containing database
> metadata. Almost all vendors support it (notable exclusion - Oracle). Looks
> like we need to introduce similar concept and finally decouple caches from
> schemas.
>
> High-level proposal from my side
>
> 1) Let's consider Ignite cluster as a single database ("catalog" in ANSI
> SQL'92 terms).
>
> 2) It should be possible to connect to the cluster without a single user
> cache. In this case schema is not defined.
>
> 3) We must have a kind of storage for metadata. It could be either another
> system cache, or something analogous to binary metadata cache, which is
> essentially node-local data structure exchanged on node join. It should be
> aligned well with persistence feature, which is expected in AI 2.1.
>
> 4) Content of this storage will be accessible through INFORMATION_SCHEMA
> abstraction.
>
> 5) We must support "CREATE SCHEMA", "DROP SCHEMA" commands which will
> effectively create records in system cache and invoke relevant commands on
> local H2 engines of every node (now it happens implicitly on cache
> start/stop).
>
> 6) From JDBC/ODBC driver perspective schema will be defined either in
> connection string, or in runtime through "SET SCHEMA" command which is
> already supported by H2.
>
> 7) We must finally introduce new native SQL API, which will not use caches
> directly. Something like "IgniteSql sql()". See *IGNITE-4701*.
>
> Once schema is there, usage of "CREATE TABLE" and "DROP TABLE" commands
> will be simple and convenient, and it will fit naturally in user's past
> experience with conventional RDBMS.
>
> Thoughts?
>
> P.S.: CREATE/DROP TABLE feature is not blocked by this problem. It will
> work, but will be inconvenient for users.
>
> [1] https://issues.apache.org/jira/browse/IGNITE-4701
>

[dsetrakyan]

Vladimir, great suggestions. Can you please make an umbrella ticket with
sub-tasks, so we can tackle some of them for 2.1?

D.

On Mon, Apr 24, 2017 at 5:28 PM, Sergi Vladykin <[hidden email]>
wrote:

> Yes, we need to move on making Ignite work as any usual SQL db.
>
> But please avoid mixing all this stuff together, lets have a separate task
> (and discussion if needed) for each item in your list.
>
> Sergi
>
> 2017-04-24 16:58 GMT+03:00 Vladimir Ozerov <[hidden email]>:
>
> > Igniters,
> >
> > [long read, take a cup of coffee]
> >
> > Historically every SQL in Ignite must be executed against particular
> cache:
> > SqlQuery requires cache, JDBC and ODBC drivers require cache name. This
> > works fine until we add CREATE TABLE. Consider an empty cluster - how do
> > you connect to it if you have no caches yet? Nohow.
> >
> > It looks like we cannot have convenient access to cluster unless we
> > properly define and implement *schema* abstraction. ANSI SQL'92 defines
> > several abstractions: cluster -> catalog -> schema -> table/view/etc..
> > Every "catalog" has *INFORMATION_SCHEMA* schema, containing database
> > metadata. Almost all vendors support it (notable exclusion - Oracle).
> Looks
> > like we need to introduce similar concept and finally decouple caches
> from
> > schemas.
> >
> > High-level proposal from my side
> >
> > 1) Let's consider Ignite cluster as a single database ("catalog" in ANSI
> > SQL'92 terms).
> >
> > 2) It should be possible to connect to the cluster without a single user
> > cache. In this case schema is not defined.
> >
> > 3) We must have a kind of storage for metadata. It could be either
> another
> > system cache, or something analogous to binary metadata cache, which is
> > essentially node-local data structure exchanged on node join. It should
> be
> > aligned well with persistence feature, which is expected in AI 2.1.
> >
> > 4) Content of this storage will be accessible through INFORMATION_SCHEMA
> > abstraction.
> >
> > 5) We must support "CREATE SCHEMA", "DROP SCHEMA" commands which will
> > effectively create records in system cache and invoke relevant commands
> on
> > local H2 engines of every node (now it happens implicitly on cache
> > start/stop).
> >
> > 6) From JDBC/ODBC driver perspective schema will be defined either in
> > connection string, or in runtime through "SET SCHEMA" command which is
> > already supported by H2.
> >
> > 7) We must finally introduce new native SQL API, which will not use
> caches
> > directly. Something like "IgniteSql sql()". See *IGNITE-4701*.
> >
> > Once schema is there, usage of "CREATE TABLE" and "DROP TABLE" commands
> > will be simple and convenient, and it will fit naturally in user's past
> > experience with conventional RDBMS.
> >
> > Thoughts?
> >
> > P.S.: CREATE/DROP TABLE feature is not blocked by this problem. It will
> > work, but will be inconvenient for users.
> >
> > [1] https://issues.apache.org/jira/browse/IGNITE-4701
> >
>