SQL¶

class SqlService(internal_sql_service)[source]¶

Bases: object

A service to execute SQL statements.

Warning

In order to use this service, Jet engine must be enabled on the members and the hazelcast-sql module must be in the classpath of the members.

If you are using the CLI, Docker image, or distributions to start Hazelcast members, then you don’t need to do anything, as the above preconditions are already satisfied for such members.

However, if you are using Hazelcast members in the embedded mode, or receiving errors saying that The Jet engine is disabled or Cannot execute SQL query because "hazelcast-sql" module is not in the classpath. while executing queries, enable the Jet engine following one of the instructions pointed out in the error message, or add the hazelcast-sql module to your member’s classpath.

Overview

Hazelcast is currently able to execute distributed SQL queries using the following connectors:

IMap (to query data stored in a Map)
Kafka
Files

SQL statements are not atomic. INSERT/SINK can fail and commit part of the data.

Usage

Before you can access any object using SQL, a mapping has to be created. See the documentation for the CREATE MAPPING command.

When a query is executed, an SqlResult is returned. You may get row iterator from the result. The result must be closed at the end. The iterator will close the result automatically when it is exhausted given that no error is raised during the iteration. The code snippet below demonstrates a typical usage pattern:

client = hazelcast.HazelcastClient()

result = client.sql.execute("SELECT * FROM person").result()

for row in result:
    print(row.get_object("person_id"))
    print(row.get_object("name"))
    ...

See the documentation of the SqlResult for more information about different iteration methods.

execute(sql, *params, **kwargs)[source]¶

Executes an SQL statement.

Parameters

sql (str) – SQL string.
*params – Query parameters that will replace the placeholders at the server-side. You may define parameter placeholders in the query with the ? character. For every placeholder, a parameter value must be provided.

Keyword Arguments

cursor_buffer_size (int) –
The cursor buffer size measured in the number of rows.

When a statement is submitted for execution, a SqlResult is returned as a result. When rows are ready to be consumed, they are put into an internal buffer of the cursor. This parameter defines the maximum number of rows in that buffer. When the threshold is reached, the backpressure mechanism will slow down the execution, possibly to a complete halt, to prevent out-of-memory.

Only positive values are allowed.

The default value is expected to work well for most workloads. A bigger buffer size may give you a slight performance boost for queries with large result sets at the cost of increased memory consumption.

Defaults to 4096.
timeout (float or int) –
The execution timeout in seconds.

If the timeout is reached for a running statement, it will be cancelled forcefully.

Zero value means no timeout. -1 means that the value from the server-side config will be used. Other negative values are prohibited.

Defaults to -1.
expected_result_type (SqlExpectedResultType) – The expected result type.
schema (str or None) –
The schema name.

The engine will try to resolve the non-qualified object identifiers from the statement in the given schema. If not found, the default search path will be used.

The schema name is case sensitive. For example, foo and Foo are different schemas.

The default value is None meaning only the default search path is used.

Returns

The execution result.

Return type

hazelcast.future.Future[SqlResult]

Raises

HazelcastSqlError – In case of execution error.
AssertionError – If the sql parameter is not a string, the schema is not a string or None, the timeout is not an integer or float, or the cursor_buffer_size is not an integer.
ValueError – If the sql parameter is an empty string, the timeout is negative and not equal to -1, the cursor_buffer_size is not positive.
TypeError – If the expected_result_type does not equal to one of the values or names of the members of the SqlExpectedResultType.

class SqlColumnMetadata(name, column_type, nullable, is_nullable_exists)[source]¶

Bases: object

Metadata of a column in an SQL row.

property name¶

Name of the column.

Type: str

property type¶

Type of the column.

Type: SqlColumnType

property nullable¶

True if this column values can be None, False otherwise.

Type: bool

class SqlColumnType[source]¶

Bases: object

VARCHAR = 0¶: Represented by str.

BOOLEAN = 1¶: Represented by bool.

TINYINT = 2¶: Represented by int.

SMALLINT = 3¶: Represented by int.

INTEGER = 4¶: Represented by int.

BIGINT = 5¶: Represented by int (for Python 3) or long (for Python 2).

DECIMAL = 6¶: Represented by decimal.Decimal.

REAL = 7¶: Represented by float.

DOUBLE = 8¶: Represented by float.

DATE = 9¶: Represented by datetime.date.

TIME = 10¶: Represented by datetime.time.

TIMESTAMP = 11¶: Represented by datetime.datetime with None tzinfo.

TIMESTAMP_WITH_TIME_ZONE = 12¶: Represented by datetime.datetime with non-None tzinfo.

OBJECT = 13¶: Could be represented by any Python class.

NULL = 14¶

The type of the generic SQL NULL literal.

The only valid value of NULL type is None.

exception HazelcastSqlError(originating_member_uuid, code, message, cause, suggestion=None)[source]¶

Bases: hazelcast.errors.HazelcastError

Represents an error occurred during the SQL query execution.

property originating_member_uuid¶

UUID of the member that caused or initiated an error condition.

Type: uuid.UUID

property suggestion¶

Suggested SQL statement to remediate experienced error.

Type: str

class SqlRowMetadata(columns)[source]¶

Bases: object

Metadata for the returned rows.

COLUMN_NOT_FOUND = -1¶: Constant indicating that the column is not found.

property columns¶

List of column metadata.

Type: list[SqlColumnMetadata]

property column_count¶

Number of columns in the row.

Type: int

get_column(index)[source]¶

Parameters

index (int) – Zero-based column index.

Returns

Metadata for the given column index.

Return type

SqlColumnMetadata

Raises

IndexError – If the index is out of bounds.
AssertionError – If the index is not an integer.

find_column(column_name)[source]¶

Parameters: column_name (str) – Name of the column.
Returns: Column index or COLUMN_NOT_FOUND if a column with the given name is not found.
Return type: int
Raises: AssertionError – If the column name is not a string.

class SqlRow(row_metadata, row, deserialize_fn)[source]¶

Bases: object

One of the rows of an SQL query result.

The columns of the rows can be retrieved using

get_object() with column name.
get_object_with_index() with column index.

Apart from these methods, the row objects can also be treated as a dict or list and columns can be retrieved using the [] operator.

If an integer value is passed to the [] operator, it will implicitly call the get_object_with_index() and return the result.

For any other type passed into the the [] operator, get_object() will be called. Note that, get_object() expects str values. Hence, the [] operator will raise error for any type other than integer and string.

get_object(column_name)[source]¶

Gets the value in the column indicated by the column name.

Column name should be one of those defined in SqlRowMetadata, case-sensitive. You may also use SqlRowMetadata.find_column() to test for column existence.

The type of the returned value depends on the SQL type of the column. No implicit conversions are performed on the value.

Warning

Each call to this method might result in a deserialization if the column type for this object is SqlColumnType.OBJECT. It is advised to assign the result of this method call to some variable and reuse it.

Parameters

column_name (str) –

Returns

Value of the column.

Raises

ValueError – If a column with the given name does not exist.
AssertionError – If the column name is not a string.
HazelcastSqlError – If the object cannot be deserialized.

See also

metadata

SqlColumnMetadata.type

property metadata¶

The row metadata.

Type: SqlRowMetadata

class SqlResult(sql_service, connection, query_id, cursor_buffer_size, execute_response)[source]¶

Bases: object

SQL query result.

Depending on the statement type it represents a stream of rows or an update count.

To iterate over the stream of rows, there are two possible options.

The first, and the easiest one is to iterate over the rows in a blocking fashion.

result = client.sql.execute("SELECT ...").result()
for row in result:
    # Process the row.
    print(row)

The second option is to use the non-blocking API with callbacks.

future = client.sql.execute("SELECT ...")  # Future of SqlResult

def on_response(sql_result_future):
    iterator = sql_result_future.result().iterator()

    def on_next_row(row_future):
        try:
            row = row_future.result()
            # Process the row.
            print(row)

            # Iterate over the next row.
            next(iterator).add_done_callback(on_next_row)
        except StopIteration:
            # Exhausted the iterator. No more rows are left.
            pass

    next(iterator).add_done_callback(on_next_row)

future.add_done_callback(on_response)

When in doubt, use the blocking API shown in the first code sample.

Note that, iterators can be requested at most once per SqlResult.

One can call close() method of a result object to release the resources associated with the result on the server side. It might also be used to cancel query execution on the server side if it is still active.

When the blocking API is used, one might also use with statement to automatically close the query even if an exception is thrown in the iteration.

with client.sql.execute("SELECT ...").result() as result:
    for row in result:
        # Process the row.
        print(row)

To get the number of rows updated by the query, use the update_count().

update_count = client.sql.execute("UPDATE ...").result().update_count()

One does not have to call close() in this case, because the result will already be closed in the server-side.

iterator()[source]¶

Returns the iterator over the result rows.

The iterator may be requested only once.

Raises: ValueError – If the result only contains an update count, or the iterator is already requested.
Returns: Iterator that produces Future of SqlRow s. See the class documentation for the correct way to use this.
Return type: Iterator[Future[SqlRow]]

is_row_set()[source]¶

Returns whether this result has rows to iterate.

Returns
Return type: bool

update_count()[source]¶

Returns the number of rows updated by the statement or -1 if this result is a row set. In case the result doesn’t contain rows but the update count isn’t applicable or known, 0 is returned.

Returns
Return type: int

get_row_metadata()[source]¶

Gets the row metadata.

Raises: ValueError – If the result only contains an update count.
Returns
Return type: SqlRowMetadata

close()[source]¶

Release the resources associated with the query result.

The query engine delivers the rows asynchronously. The query may become inactive even before all rows are consumed. The invocation of this command will cancel the execution of the query on all members if the query is still active. Otherwise it is no-op. For a result with an update count it is always no-op.

The returned Future results with:

HazelcastSqlError: In case there is an error closing the result.

Returns
Return type: Future[None]

class SqlExpectedResultType[source]¶

Bases: object

The expected statement result type.

ANY = 0¶: The statement may produce either rows or an update count.

ROWS = 1¶: The statement must produce rows. An exception is thrown is the statement produces an update count.

UPDATE_COUNT = 2¶: The statement must produce an update count. An exception is thrown is the statement produces rows.