You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Vladimir Ozerov <vo...@gridgain.com> on 2017/02/07 11:34:48 UTC
Rethink native SQL API in Apache Ignite 2.0
Igniters,
Our SQL engine becomes more and more sophisticated. Initially we had only
SELECTs, now we have DML, in AI 2.x we will have DDL.
This is very cool, but it seems that we completely forgot about extending
our native SQL API (IgniteCache.query, SqlQuery, SqlFieldsQuery) in
response to these changes. For example:
1) How one should execute DML/DDL command and validate the result?
int updateCnt = IgniteCache.query(SqlQuery).getAll().get(0);
Counter-intuitive and too verbose.
2) How one should perform batched operations? One by one?
IgniteCache.query(SqlQuery.setArgs(1));
IgniteCache.query(SqlQuery.setArgs(2));
...
IgniteCache.query(SqlQuery.setArgs(N));
I think it is time to rework our API so that it supports all recent
features in consistent way and is extensible enough for future improvements
(e.g. transactional SQL).
Probably we can take ideas behind JDBC standard as starting point and move
SQL to separate API. Several very rough examples:
1) Getting facade:
IgniteSql sql = ignite.sql("MY_SCHEMA");
2) Running SELECT:
QueryCursor<K, V> cursor = sql.select(SqlCommand);
QueryCursor<List<?>> cursor = sql.selectFields(SqlCommand); // No more need
for separate SqlQuery and SqlQueryFields classes.
3) Running DML/DDL:
SqlCommandResult res = sql.execute(SqlCommand);
or
int updatedCnt = sql.execute(SqlCommand);
4) Running batch commands:
SqlCommand cmd = new SqlCommand(...).addBatch(arg1).addBatch(arg2);
SqlCommandResult res = sql.execute(cmd);
5) Re-use query parsing results (PreparedStatement):
SqlCommand qry = sql.prepare("SELECT ...");
Our JDBC driver is not applicable here because it is either not peformant
enough (V1), or starts unnecessary client inside (V2).
Thoughts? Does anyone else think it is time to re-approach SQL API?
Vladimir.
Re: Rethink native SQL API in Apache Ignite 2.0
Posted by Pavel Tupitsyn <pt...@apache.org>.
+1
SqlFieldsQuery API is quite a convoluted way to do DML.
2.0 release should certainly address this.
On Tue, Feb 7, 2017 at 2:34 PM, Vladimir Ozerov <vo...@gridgain.com>
wrote:
> Igniters,
>
> Our SQL engine becomes more and more sophisticated. Initially we had only
> SELECTs, now we have DML, in AI 2.x we will have DDL.
>
> This is very cool, but it seems that we completely forgot about extending
> our native SQL API (IgniteCache.query, SqlQuery, SqlFieldsQuery) in
> response to these changes. For example:
>
> 1) How one should execute DML/DDL command and validate the result?
> int updateCnt = IgniteCache.query(SqlQuery).getAll().get(0);
>
> Counter-intuitive and too verbose.
>
> 2) How one should perform batched operations? One by one?
> IgniteCache.query(SqlQuery.setArgs(1));
> IgniteCache.query(SqlQuery.setArgs(2));
> ...
> IgniteCache.query(SqlQuery.setArgs(N));
>
> I think it is time to rework our API so that it supports all recent
> features in consistent way and is extensible enough for future improvements
> (e.g. transactional SQL).
>
> Probably we can take ideas behind JDBC standard as starting point and move
> SQL to separate API. Several very rough examples:
>
> 1) Getting facade:
> IgniteSql sql = ignite.sql("MY_SCHEMA");
>
> 2) Running SELECT:
> QueryCursor<K, V> cursor = sql.select(SqlCommand);
> QueryCursor<List<?>> cursor = sql.selectFields(SqlCommand); // No more need
> for separate SqlQuery and SqlQueryFields classes.
>
> 3) Running DML/DDL:
> SqlCommandResult res = sql.execute(SqlCommand);
> or
> int updatedCnt = sql.execute(SqlCommand);
>
> 4) Running batch commands:
> SqlCommand cmd = new SqlCommand(...).addBatch(arg1).addBatch(arg2);
> SqlCommandResult res = sql.execute(cmd);
>
> 5) Re-use query parsing results (PreparedStatement):
> SqlCommand qry = sql.prepare("SELECT ...");
>
> Our JDBC driver is not applicable here because it is either not peformant
> enough (V1), or starts unnecessary client inside (V2).
>
> Thoughts? Does anyone else think it is time to re-approach SQL API?
>
> Vladimir.
>
Re: Rethink native SQL API in Apache Ignite 2.0
Posted by Jörn Franke <jo...@gmail.com>.
Keep in mind security, e.g. SQL injections. Ideally the API should be designed in such a way that the programmer cannot use it wrongly and allow sql injections.
> On 7 Feb 2017, at 12:34, Vladimir Ozerov <vo...@gridgain.com> wrote:
>
> Igniters,
>
> Our SQL engine becomes more and more sophisticated. Initially we had only
> SELECTs, now we have DML, in AI 2.x we will have DDL.
>
> This is very cool, but it seems that we completely forgot about extending
> our native SQL API (IgniteCache.query, SqlQuery, SqlFieldsQuery) in
> response to these changes. For example:
>
> 1) How one should execute DML/DDL command and validate the result?
> int updateCnt = IgniteCache.query(SqlQuery).getAll().get(0);
>
> Counter-intuitive and too verbose.
>
> 2) How one should perform batched operations? One by one?
> IgniteCache.query(SqlQuery.setArgs(1));
> IgniteCache.query(SqlQuery.setArgs(2));
> ...
> IgniteCache.query(SqlQuery.setArgs(N));
>
> I think it is time to rework our API so that it supports all recent
> features in consistent way and is extensible enough for future improvements
> (e.g. transactional SQL).
>
> Probably we can take ideas behind JDBC standard as starting point and move
> SQL to separate API. Several very rough examples:
>
> 1) Getting facade:
> IgniteSql sql = ignite.sql("MY_SCHEMA");
>
> 2) Running SELECT:
> QueryCursor<K, V> cursor = sql.select(SqlCommand);
> QueryCursor<List<?>> cursor = sql.selectFields(SqlCommand); // No more need
> for separate SqlQuery and SqlQueryFields classes.
>
> 3) Running DML/DDL:
> SqlCommandResult res = sql.execute(SqlCommand);
> or
> int updatedCnt = sql.execute(SqlCommand);
>
> 4) Running batch commands:
> SqlCommand cmd = new SqlCommand(...).addBatch(arg1).addBatch(arg2);
> SqlCommandResult res = sql.execute(cmd);
>
> 5) Re-use query parsing results (PreparedStatement):
> SqlCommand qry = sql.prepare("SELECT ...");
>
> Our JDBC driver is not applicable here because it is either not peformant
> enough (V1), or starts unnecessary client inside (V2).
>
> Thoughts? Does anyone else think it is time to re-approach SQL API?
>
> Vladimir.
Re: Rethink native SQL API in Apache Ignite 2.0
Posted by Vladimir Ozerov <vo...@gridgain.com>.
I created the ticket IGNITE-4701 [1]. Design is still be proposed. I think
it is better to continue discussion here in order to involve more community
members in the process.
[1] https://issues.apache.org/jira/browse/IGNITE-4701
On Sat, Feb 11, 2017 at 4:04 AM, Dmitriy Setrakyan <ds...@apache.org>
wrote:
> I think the proper process here is to create a JIRA ticket and propose the
> design there.
>
> D.
>
> On Fri, Feb 10, 2017 at 1:37 AM, Sergi Vladykin <se...@gmail.com>
> wrote:
>
> > I agree with Vladimir that we have to improve our API with respect to the
> > latest changes in our SQL capabilities.
> >
> > As for the given API proposal, it is a bit harsh right now, but it is ok
> > for the first draft, overall direction looks good.
> >
> > Also IMO we should not drop the existing API, thus adding a new one is
> not
> > urgent for 2.0, because adding new API is not a breaking change.
> >
> > Sergi
> >
> > 2017-02-10 4:55 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> >
> > > Vladimir,
> > >
> > > Will these changes be backward compatible?
> > >
> > > Sergi, given your SQL expertise, can you please comment here as well?
> > >
> > > D.
> > >
> > > On Tue, Feb 7, 2017 at 3:34 AM, Vladimir Ozerov <vo...@gridgain.com>
> > > wrote:
> > >
> > > > Igniters,
> > > >
> > > > Our SQL engine becomes more and more sophisticated. Initially we had
> > only
> > > > SELECTs, now we have DML, in AI 2.x we will have DDL.
> > > >
> > > > This is very cool, but it seems that we completely forgot about
> > extending
> > > > our native SQL API (IgniteCache.query, SqlQuery, SqlFieldsQuery) in
> > > > response to these changes. For example:
> > > >
> > > > 1) How one should execute DML/DDL command and validate the result?
> > > > int updateCnt = IgniteCache.query(SqlQuery).getAll().get(0);
> > > >
> > > > Counter-intuitive and too verbose.
> > > >
> > > > 2) How one should perform batched operations? One by one?
> > > > IgniteCache.query(SqlQuery.setArgs(1));
> > > > IgniteCache.query(SqlQuery.setArgs(2));
> > > > ...
> > > > IgniteCache.query(SqlQuery.setArgs(N));
> > > >
> > > > I think it is time to rework our API so that it supports all recent
> > > > features in consistent way and is extensible enough for future
> > > improvements
> > > > (e.g. transactional SQL).
> > > >
> > > > Probably we can take ideas behind JDBC standard as starting point and
> > > move
> > > > SQL to separate API. Several very rough examples:
> > > >
> > > > 1) Getting facade:
> > > > IgniteSql sql = ignite.sql("MY_SCHEMA");
> > > >
> > > > 2) Running SELECT:
> > > > QueryCursor<K, V> cursor = sql.select(SqlCommand);
> > > > QueryCursor<List<?>> cursor = sql.selectFields(SqlCommand); // No
> more
> > > need
> > > > for separate SqlQuery and SqlQueryFields classes.
> > > >
> > > > 3) Running DML/DDL:
> > > > SqlCommandResult res = sql.execute(SqlCommand);
> > > > or
> > > > int updatedCnt = sql.execute(SqlCommand);
> > > >
> > > > 4) Running batch commands:
> > > > SqlCommand cmd = new SqlCommand(...).addBatch(arg1).addBatch(arg2);
> > > > SqlCommandResult res = sql.execute(cmd);
> > > >
> > > > 5) Re-use query parsing results (PreparedStatement):
> > > > SqlCommand qry = sql.prepare("SELECT ...");
> > > >
> > > > Our JDBC driver is not applicable here because it is either not
> > peformant
> > > > enough (V1), or starts unnecessary client inside (V2).
> > > >
> > > > Thoughts? Does anyone else think it is time to re-approach SQL API?
> > > >
> > > > Vladimir.
> > > >
> > >
> >
>
Re: Rethink native SQL API in Apache Ignite 2.0
Posted by Dmitriy Setrakyan <ds...@apache.org>.
I think the proper process here is to create a JIRA ticket and propose the
design there.
D.
On Fri, Feb 10, 2017 at 1:37 AM, Sergi Vladykin <se...@gmail.com>
wrote:
> I agree with Vladimir that we have to improve our API with respect to the
> latest changes in our SQL capabilities.
>
> As for the given API proposal, it is a bit harsh right now, but it is ok
> for the first draft, overall direction looks good.
>
> Also IMO we should not drop the existing API, thus adding a new one is not
> urgent for 2.0, because adding new API is not a breaking change.
>
> Sergi
>
> 2017-02-10 4:55 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
>
> > Vladimir,
> >
> > Will these changes be backward compatible?
> >
> > Sergi, given your SQL expertise, can you please comment here as well?
> >
> > D.
> >
> > On Tue, Feb 7, 2017 at 3:34 AM, Vladimir Ozerov <vo...@gridgain.com>
> > wrote:
> >
> > > Igniters,
> > >
> > > Our SQL engine becomes more and more sophisticated. Initially we had
> only
> > > SELECTs, now we have DML, in AI 2.x we will have DDL.
> > >
> > > This is very cool, but it seems that we completely forgot about
> extending
> > > our native SQL API (IgniteCache.query, SqlQuery, SqlFieldsQuery) in
> > > response to these changes. For example:
> > >
> > > 1) How one should execute DML/DDL command and validate the result?
> > > int updateCnt = IgniteCache.query(SqlQuery).getAll().get(0);
> > >
> > > Counter-intuitive and too verbose.
> > >
> > > 2) How one should perform batched operations? One by one?
> > > IgniteCache.query(SqlQuery.setArgs(1));
> > > IgniteCache.query(SqlQuery.setArgs(2));
> > > ...
> > > IgniteCache.query(SqlQuery.setArgs(N));
> > >
> > > I think it is time to rework our API so that it supports all recent
> > > features in consistent way and is extensible enough for future
> > improvements
> > > (e.g. transactional SQL).
> > >
> > > Probably we can take ideas behind JDBC standard as starting point and
> > move
> > > SQL to separate API. Several very rough examples:
> > >
> > > 1) Getting facade:
> > > IgniteSql sql = ignite.sql("MY_SCHEMA");
> > >
> > > 2) Running SELECT:
> > > QueryCursor<K, V> cursor = sql.select(SqlCommand);
> > > QueryCursor<List<?>> cursor = sql.selectFields(SqlCommand); // No more
> > need
> > > for separate SqlQuery and SqlQueryFields classes.
> > >
> > > 3) Running DML/DDL:
> > > SqlCommandResult res = sql.execute(SqlCommand);
> > > or
> > > int updatedCnt = sql.execute(SqlCommand);
> > >
> > > 4) Running batch commands:
> > > SqlCommand cmd = new SqlCommand(...).addBatch(arg1).addBatch(arg2);
> > > SqlCommandResult res = sql.execute(cmd);
> > >
> > > 5) Re-use query parsing results (PreparedStatement):
> > > SqlCommand qry = sql.prepare("SELECT ...");
> > >
> > > Our JDBC driver is not applicable here because it is either not
> peformant
> > > enough (V1), or starts unnecessary client inside (V2).
> > >
> > > Thoughts? Does anyone else think it is time to re-approach SQL API?
> > >
> > > Vladimir.
> > >
> >
>
Re: Rethink native SQL API in Apache Ignite 2.0
Posted by Sergi Vladykin <se...@gmail.com>.
I agree with Vladimir that we have to improve our API with respect to the
latest changes in our SQL capabilities.
As for the given API proposal, it is a bit harsh right now, but it is ok
for the first draft, overall direction looks good.
Also IMO we should not drop the existing API, thus adding a new one is not
urgent for 2.0, because adding new API is not a breaking change.
Sergi
2017-02-10 4:55 GMT+03:00 Dmitriy Setrakyan <ds...@apache.org>:
> Vladimir,
>
> Will these changes be backward compatible?
>
> Sergi, given your SQL expertise, can you please comment here as well?
>
> D.
>
> On Tue, Feb 7, 2017 at 3:34 AM, Vladimir Ozerov <vo...@gridgain.com>
> wrote:
>
> > Igniters,
> >
> > Our SQL engine becomes more and more sophisticated. Initially we had only
> > SELECTs, now we have DML, in AI 2.x we will have DDL.
> >
> > This is very cool, but it seems that we completely forgot about extending
> > our native SQL API (IgniteCache.query, SqlQuery, SqlFieldsQuery) in
> > response to these changes. For example:
> >
> > 1) How one should execute DML/DDL command and validate the result?
> > int updateCnt = IgniteCache.query(SqlQuery).getAll().get(0);
> >
> > Counter-intuitive and too verbose.
> >
> > 2) How one should perform batched operations? One by one?
> > IgniteCache.query(SqlQuery.setArgs(1));
> > IgniteCache.query(SqlQuery.setArgs(2));
> > ...
> > IgniteCache.query(SqlQuery.setArgs(N));
> >
> > I think it is time to rework our API so that it supports all recent
> > features in consistent way and is extensible enough for future
> improvements
> > (e.g. transactional SQL).
> >
> > Probably we can take ideas behind JDBC standard as starting point and
> move
> > SQL to separate API. Several very rough examples:
> >
> > 1) Getting facade:
> > IgniteSql sql = ignite.sql("MY_SCHEMA");
> >
> > 2) Running SELECT:
> > QueryCursor<K, V> cursor = sql.select(SqlCommand);
> > QueryCursor<List<?>> cursor = sql.selectFields(SqlCommand); // No more
> need
> > for separate SqlQuery and SqlQueryFields classes.
> >
> > 3) Running DML/DDL:
> > SqlCommandResult res = sql.execute(SqlCommand);
> > or
> > int updatedCnt = sql.execute(SqlCommand);
> >
> > 4) Running batch commands:
> > SqlCommand cmd = new SqlCommand(...).addBatch(arg1).addBatch(arg2);
> > SqlCommandResult res = sql.execute(cmd);
> >
> > 5) Re-use query parsing results (PreparedStatement):
> > SqlCommand qry = sql.prepare("SELECT ...");
> >
> > Our JDBC driver is not applicable here because it is either not peformant
> > enough (V1), or starts unnecessary client inside (V2).
> >
> > Thoughts? Does anyone else think it is time to re-approach SQL API?
> >
> > Vladimir.
> >
>
Re: Rethink native SQL API in Apache Ignite 2.0
Posted by Dmitriy Setrakyan <ds...@apache.org>.
Vladimir,
Will these changes be backward compatible?
Sergi, given your SQL expertise, can you please comment here as well?
D.
On Tue, Feb 7, 2017 at 3:34 AM, Vladimir Ozerov <vo...@gridgain.com>
wrote:
> Igniters,
>
> Our SQL engine becomes more and more sophisticated. Initially we had only
> SELECTs, now we have DML, in AI 2.x we will have DDL.
>
> This is very cool, but it seems that we completely forgot about extending
> our native SQL API (IgniteCache.query, SqlQuery, SqlFieldsQuery) in
> response to these changes. For example:
>
> 1) How one should execute DML/DDL command and validate the result?
> int updateCnt = IgniteCache.query(SqlQuery).getAll().get(0);
>
> Counter-intuitive and too verbose.
>
> 2) How one should perform batched operations? One by one?
> IgniteCache.query(SqlQuery.setArgs(1));
> IgniteCache.query(SqlQuery.setArgs(2));
> ...
> IgniteCache.query(SqlQuery.setArgs(N));
>
> I think it is time to rework our API so that it supports all recent
> features in consistent way and is extensible enough for future improvements
> (e.g. transactional SQL).
>
> Probably we can take ideas behind JDBC standard as starting point and move
> SQL to separate API. Several very rough examples:
>
> 1) Getting facade:
> IgniteSql sql = ignite.sql("MY_SCHEMA");
>
> 2) Running SELECT:
> QueryCursor<K, V> cursor = sql.select(SqlCommand);
> QueryCursor<List<?>> cursor = sql.selectFields(SqlCommand); // No more need
> for separate SqlQuery and SqlQueryFields classes.
>
> 3) Running DML/DDL:
> SqlCommandResult res = sql.execute(SqlCommand);
> or
> int updatedCnt = sql.execute(SqlCommand);
>
> 4) Running batch commands:
> SqlCommand cmd = new SqlCommand(...).addBatch(arg1).addBatch(arg2);
> SqlCommandResult res = sql.execute(cmd);
>
> 5) Re-use query parsing results (PreparedStatement):
> SqlCommand qry = sql.prepare("SELECT ...");
>
> Our JDBC driver is not applicable here because it is either not peformant
> enough (V1), or starts unnecessary client inside (V2).
>
> Thoughts? Does anyone else think it is time to re-approach SQL API?
>
> Vladimir.
>