SQL¶

class SqlService(internal_sql_service)[source]¶

Bases: object

A service to execute SQL statements.

The service allows you to query data stored in a Map.

Warning

The service is in beta state. Behavior and API might change in future releases.

Querying an IMap

Every Map instance is exposed as a table with the same name in the partitioned schema. The partitioned schema is included into a default search path, therefore a Map could be referenced in an SQL statement with or without the schema name.

Column resolution

Every table backed by a Map has a set of columns that are resolved automatically. Column resolution uses Map entries located on the member that initiates the query. The engine extracts columns from a key and a value and then merges them into a single column set. In case the key and the value have columns with the same name, the key takes precedence.

Columns are extracted from objects as follows (which happens on the server-side):

For non-Portable objects, public getters and fields are used to populate the column list. For getters, the first letter is converted to lower case. A getter takes precedence over a field in case of naming conflict.
For Portable objects, field names used in the write_portable() method are used to populate the column list.

The whole key and value objects could be accessed through special fields __key and this, respectively. If key (value) object has fields, then the whole key (value) field is exposed as a normal field. Otherwise the field is hidden. Hidden fields can be accessed directly, but are not returned by SELECT * FROM ... queries.

Consider the following key/value model:

class PersonKey(Portable):
    def __init__(self, person_id=None, department_id=None):
        self.person_id = person_id
        self.department_id = department_id

    def write_portable(self, writer):
        writer.write_long("person_id", self.person_id)
        writer.write_long("department_id", self.department_id)

    ...

class Person(Portable):
    def __init__(self, name=None):
        self.name = name

    def write_portable(self, writer):
        writer.write_string("name", self.name)

    ...

This model will be resolved to the following table columns:

person_id BIGINT
department_id BIGINT
name VARCHAR
__key OBJECT (hidden)
this OBJECT (hidden)

Consistency

Results returned from Map query are weakly consistent:

If an entry was not updated during iteration, it is guaranteed to be returned exactly once
If an entry was modified during iteration, it might be returned zero, one or several times

Usage

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")

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.

Notes

When an SQL statement is submitted to a member, it is parsed and optimized by the hazelcast-sql module. The hazelcast-sql must be in the classpath, otherwise an exception will be thrown. If you’re using the hazelcast-all or hazelcast-enterprise-all packages, the hazelcast-sql module is included in them by default. If not, i.e., you are using hazelcast or hazelcast-enterprise, then you need to have hazelcast-sql in the classpath. If you are using the Docker image, the SQL module is included by default.

execute(sql, *params)[source]¶

Convenient method to execute a distributed query with the given parameters.

Converts passed SQL string and parameters into an SqlStatement object and invokes execute_statement().

Parameters

sql (str) – SQL string.
*params – Query parameters that will be passed to SqlStatement.add_parameter().

Returns

The execution result.

Return type

SqlResult

Raises

HazelcastSqlError – In case of execution error.
AssertionError – If the SQL parameter is not a string.
ValueError – If the SQL parameter is an empty string.

execute_statement(statement)[source]¶

Executes an SQL statement.

Parameters: statement (SqlStatement) – Statement to be executed
Returns: The execution result.
Return type: SqlResult
Raises: HazelcastSqlError – In case of execution error.

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 str.

REAL = 7¶: Represented by float.

DOUBLE = 8¶: Represented by float.

DATE = 9¶: Represented by str with the YYYY-MM-DD format.

TIME = 10¶: Represented by str with the HH:MM:SS[.ffffff] format.

TIMESTAMP = 11¶: Represented by str with the YYYY-MM-DDTHH:MM:SS[.ffffff] format.

TIMESTAMP_WITH_TIME_ZONE = 12¶: Represented by str with the YYYY-MM-DDTHH:MM:SS[.ffffff](+|-)HH:MM[:SS] format.

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)[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

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)[source]¶

Bases: object

One of the rows of an SQL query result.

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.

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.

See also

metadata

SqlColumnMetadata.type

property metadata¶

The row metadata.

Type: SqlRowMetadata

class SqlResult(sql_service, connection, query_id, cursor_buffer_size, execute_future)[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 ...")
for row in result:
    # Process the row.
    print(row)

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

result = client.sql.execute("SELECT ...")
it = result.iterator()  # Future of iterator

def on_iterator_response(iterator_future):
    iterator = iterator_future.result()

    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)

it.add_done_callback(on_iterator_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 ...") 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("SELECT ...").update_count().result()

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.