Hazelcast: SQL: Introduce public API

[devozerov]

We should introduce a public API for the SQL engine. General considerations:

1. Service - SQL should be accessed through a new service interface exposed from HazelcastInstance. Old predicate API was exposed from the IMap interface. This approach doesn't work for us because SQL could access multiple IMap-s and other data sources, such as ReplicatedMap or Jet connectors.

2. Do not use JDBC interfaces - it might be tempting to re-use JDBC Connection/Statement/ResultSet interfaces. They are well-known to users and part of JDK. The problem is that these interfaces are much more complex than most users need: you need to instantiate a lot of intermediate objects, remember to close them properly, handle checked exceptions, etc. Our API will be much simpler, while still reusing some ideas from JDBC.

3. SqlQuery object - when running a query user may need to pass various configuration properties in addition to the query itself. This includes parameters, timeout, page size, schema name, etc. Trying to expose them in a flat form will lead to a combinatorial explosion of a number of methods. Therefore, we will introduce SqlQuery class to encapsulate the state. Simple shortcut method for the most common case - query string and parameters - will be exposed as well:

class SqlQuery {
    String schema;
    String sql;
    Object[] params;
    long timeout;
    ...
}

interface SqlService {
    SqlResult execute(SqlQuery query);

    default SqlResult execute(String sql, Object... params) {
        return execute(new SqlQuery(sql, params));
    }
}

1. Result types - SQL may return different types of results: rows for SELECT and some other commands (such as SHOW), update count for other DML statements, void for DDL. We will expose the result type through an enum:

enum SqlResultType {
    ROWS,
    UPDATE_COUNT,
    VOID
}

1. Result - will encapsulate the result. For ROWS we will expose an Iterator as opposed to Set in the old predicate API. The result will be AutoCloseable to allow users to stop query execution before all rows are consumed. Also, it will implement Iterable interface to allow for convenient consumption of rows. Last, it will expose row metadata so that the user knows type of values within a row:

interface SqlResult extends Iterable<SqlRow>, AutoCloseable  {
    SqlResultType resultType();
    long updateCount();
    SqlRowMetadata rowMetadata();
    Iterator<SqlRow> iterator(); // Inherited from Iterable
    void close();                // Inherited from AutoCloseable
}

1. Row - provides access to row data. At the moment we will only expose a generic accessor. In future we may want to add specialized versions, such as getInt, getLong, etc. Row will also expose own metadata, so that it could be introspected without the parent SqlResult:

interface SqlRow {
    Object getObject(int index);
    SqlRowMetadata rowMetadata();
}

1. No support for multi-result queries - consider the query string SELECT ... FROM r; SELECT ... FROM s. This query string consists of two actual queries, and hence two results should be returned. We will not support such use cases at the moment, even though JDBC interfaces support it. The reason is that they are not used widely, but adds considerable complexity to both API and implementation. Many JDBC implementations don't support them as well. If needed, we will add support for such queries later like this:

interface SqlService {
    ...
    List<SqlResult> executeMultiple(...);
}

1. No support for sessions - we will not support any kind of sessions in the first release. If we understand that there are important use cases in 4.1 where they are absolutely needed, we will add them separately. Sessions should not affect already described API much.

[puzpuzpuz]

I see mentioning of page size. It would be nice to know how paginated (or cursor based?) queries will work.

[viliam-durina]

We should also explicitly say that we allow parallel queries: one can submit two queries and iterate their results in parallel.

Also async queries could be beneficial, at least as a future option.

[devozerov]

@puzpuzpuz

I see mentioning of page size. It would be nice to know how paginated (or cursor based?) queries will work.

From the user perspective, he works with a cursor. But from the implementation standpoint, we move data from nodes to user applications in batches of various sizes. Page size determines the maximum number of rows that are copied to a buffer of user iterator

[devozerov]

@viliam-durina

Also async queries could be beneficial, at least as a future option.

Yes, on internal example is passing results to client members - async row consumption could prevent a handler thread from being blocked waiting for results, thus increasing throughput. It will be possible to adopt async consumption of results in the future if possible without changing the current API, which is synchronous.

[puzpuzpuz]

From the user perspective, he works with a cursor. But from the implementation standpoint, we move data from nodes to user applications in batches of various sizes. Page size determines the maximum number of rows that are copied to a buffer of user iterator

I see. Thanks for the clarification.

[viliam-durina]

I wonder if this is a good idea:

<T> T getObject(int index);

It's convenient, but if we assign to incorrect type, there will be CCE at runtime. I'd rather force the user to cast explicitly, at least he better sees what the call is doing. Especially if we plan to add methods like getInt in the future which can do some type conversions.

[devozerov]

If the underlying object is of incorrect type, CCE will be thrown anyway. Without generics we add complexity to the code - users will have to do conversions by hand always, while in reality, the assignment will be valid in most cases.

[puzpuzpuz]

Will the timeout parameter specify total amount of time spent on each member for query execution? I'm considering the interactive application scenario, where the first page is fetched initially and then the application waits for a user input to fetch the next one. In this scenario there should be an idle timeout to avoid resources kept on members for such idle queries.

[jerrinot]

silly idea: do not add QueryService, but expose the query() methods directly on the HazelcastInstance.

Pros:

• it's not a data structure on its own. you can see this as a meta-service. something spanning other data-structure.
• concise code (for users). think of instance.query("select * from HerosMap where name = ?", 'devozerov')

Cons:

• HazelcastInstance is a precious resource. There is a danger of API creep, especially if we add more query-related methods.

[devozerov]

@puzpuzpuz at the moment we have a timeout for total query execution time. It is tracked on a coordinator node. When the timeout is reached, the query cancel command is sent to all participants. There is no notion of "idle timeout".

[devozerov]

@jerrinot The cons you mentioned was precisely the reason for a separate service. At the moment we already have two methods, and there is a chance that more will be added in the future versions. E.g. methods to spawn a session (out of scope now), methods for management and monitoring, methods to execute a multi-query string, etc.

[puzpuzpuz]

@puzpuzpuz at the moment we have a timeout for total query execution time. It is tracked on a coordinator node. When the timeout is reached, the query cancel command is sent to all participants. There is no notion of "idle timeout".

Is the timeout (or to be precise the query lifecycle) tracked from the query start and up to SqlResult#close() call? Or the logic is different?

Sounds like the proposed design is more or less ok for interactive applications, yet a large timeout has to be set for queries they run.

[viliam-durina]

@puzpuzpuz When doing pagination, I always used to execute a new query for each page. Retaining a cursor is expensive, esp. when the user doesn't request a next page. In JDBC you also block the connection. Our cursor is also not scrollable - you can't go back to the first page. And one more point: the cursor does not tolerate migration; if there's a migration while data is fetched, you have to execute the query anew.

[puzpuzpuz]

@puzpuzpuz When doing pagination, I always used to execute a new query for each page. Retaining a cursor is expensive, esp. when the user doesn't request a next page.

@viliam-durina
I know about best practices. Yet, initial SQL implementation won't support LIMIT/OFFSET clauses. So, I'm considering workaround options here.

[devozerov]

@puzpuzpuz Query start is an instant when the query is submitted to a member (aka initiator). Query finish is an instant when the initiator received the last chunks of data from all participants, i.e. no member is currently executing the query.
Query timeout defines the maximum duration defined as duration = query_finish - query_start. If the query is still active on the initiator after the timeout, it is canceled forcefully.

[viliam-durina]

This is the API when used:
```java
HazelcastInstance instance = HazelcastClient.newHazelcastClient();
SqlService sqlService = instance.getSqlService();

try (SqlResult result = sqlService.query("SELECT ...")) {
assert result.getResultType() == ROWS;
for (SqlRow row : result) {
Integer i = row.getObject(0);
// ...
}
}```

[viliam-durina]

Just realized, we have two methods returning the iterator of rows: getRows() and iterator(). The latter is inherited from Iterable.

[cangencer]

We can skip get on all the methods, it's just additional typing. So if it already implements Iterable, why do we need an additional getRows() iterator?

[erosb]

Hello,

thank you for sharing a design at an early stage.

My comments per points:
point 3) - In the SqlService it would make sense to pass a default timeout to the ctors of the implementations, and this default would be overrideable with SqlQuery#timeout if necessary. I think people would want an application-wide timeout value, so it would be noisy to pass it in every query. I would also consider having a default for the schema too (I assume most applications will operate on a single schema).

point 4) - Queries and DML statements could be represented by separate classes, so SqlQuery would represent only SELECTs, and SqlStatement would represent DML statements (I'm not sure about this name though). Then we could also have separate methods in SqlService, ie. have #query(SqlQuery) and #perform(SqlStatement).
This would also probably make SqlQueryResultType unnecessary, so query() would also return rows, and perform() would be void (or would always return the number of affected rows, for that matter).

point 5) - getRows() could also return a Stream (not sure if that has any advantage).

point 6) - regarding the specialized versions, if we want to return primitive values in getInt() and getLong() , I would design that conciously. JDBC does that in an odd way: first you read the value, then call wasNull(), and if it is true then you ignore the value. So it isn't intuitive, at least to say.

Other notes/questions:

• will there be any way to pass the SQL statement in any way beside a string? An AST-like representation, usable as a query builder API would be very useful for queries eg. with dynamic WHERE conditions.
• regarding <T> T getObject(int index); , it is a tempting idea, but I would prefer not doing it, due to the implicit casting that may cause CCE. It is of course opinion-based if the casting should be explicit or implicit.

[cangencer]

How will the result work for streaming queries? Return a blocking/never-ending iterator?

[devozerov]

@viliam-durina @cangencer Agree, getters do not add any value, removed them.
Regarding rows() and iterator() methods - yes, they are doing the same. In the current prototype, we have only iterator(). My idea was that it might be easier for users to understand the API if we have an explicit method in addition to inherited one. But I agree that it may cause confusion as well. So let's do the opposite - remove that method and add it back if we observe any user difficulties with the API.

[devozerov]

@cangencer For streaming queries, the user will just continue reading the iterator.

[devozerov]

@erosb thanks for detailed comments. My answers:

point 3) - In the SqlService it would make sense to pass a default timeout to the ctors

Agree. We may make it simpler from the API standpoint - define the default timeout on the configuration level, and use it when the default null value of SqlQuery.timeout is observed. This way there will be no need to add more parameters to SqlService.query(String sql, Object... params) method

point 4) - Queries and DML statements could be represented by separate classes ... i.e. have #query(SqlQuery) and #perform(SqlStatement)

We thought about it. The fundamental problem is that the user will have to know in advance whether the given command returns a result set or not, but this is not always possible. Consider a user who tries to execute a sequence of commands taken from a file. He doesn't know the nature of every command, so with query/perform separation he will have to perform a kind of manual parsing beforehand which is not very convenient. Instead, we may think of adding specialized commands in addition to the generic query command. E.g. long executeUpdate(...) for DML/DDL commands.

point 5) - getRows() could also return a Stream (not sure if that has any advantage).

Maybe we may add a version returning a stream as a separate method in the future if we see demand. In the first release, we would like to deliver the API with the smallest API footprint possible to keep the focus on the core feature set.

point 6) - regarding the specialized versions, if we want to return primitive values in getInt() and getLong() , I would design that conciously. JDBC does that in an odd way: first you read the value, then call wasNull(), and if it is true then you ignore the value. So it isn't intuitive, at least to say.

Agree, design of these methods is not trivial. This is why we postpone it for now.

• will there be any way to pass the SQL statement in any way besides a string? An AST-like representation, usable as a query builder API would be very useful for queries eg. with dynamic WHERE conditions.

Maybe we will have it in the future, but not now, since it requires considerable engineering efforts and increases API surface significantly.

regarding <T> T getObject(int index); , it is a tempting idea, but I would prefer not doing it, due to the implicit casting that may cause CCE. It is of course opinion-based if the casting should be explicit or implicit.

This is the second time the concern about CCE due to implicit cast is mentioned in this issue. I'll remove it then to keep us on the safe side. Let's see how users will react to this and add implicit casting back if needed.

[TomaszGaweda]

Hi,

Do not use JDBC interfaces

Will text queries be also possible? If yes, then JDBC - compatible way of querying data would be welcome. As a person with Spark background, I found that often Spark SQL was queries by some external tools via JDBC. Similar functionality for Hazelcast would be great!

[devozerov]

@TomaszGaweda Could you please elaborate on what you mean by text queries? We are going to have our own JDBC driver in future releases.

[ldziedziul]

Great initiative! 💪

I don't understand what's the difference between implicit casting <T> T getObject(int index) and explicit of Object getObject(int index);. In both cases you can end up with the CCE and both can't be checked during compile-time. The latter adds extra work to your API users with casting.

Also, I would suggest adding an overloaded version: getObject(String columnName) to make the application code more maintainable. I could easily imagine that after some time developers will add a new column to query, right in the middle of existing columns, which will result in shifted indices.

Regarding the design of a completely new API for relational-like database access, I would recommend taking a look at work done by https://r2dbc.io/. I know it's a reactive library, but it was written from scratch bypassing JDBC stack, the same as you plan. Maybe you can find some hints there. Also, it's worth to check https://docs.spring.io/spring-data/r2dbc/docs/current/reference/html/#introduction to see how r2dbc API was simplified and made more user-friendly.

[devozerov]

Hi @ldziedziul, thank you for your inputs. I also tend to think that implicit casting should not do any harm. But given that this causes controversy within community, I think it is better to start with explicit casts.

Regarding r2dbc - do you have any specific part of their API in mind, that could be useful for us?

[devozerov]

The PR with the SQL API is prepared: https://github.com/hazelcast/hazelcast/pull/17130