- class SqlExpectedResultType¶
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.
- class SqlService(internal_sql_service)¶
A service to execute SQL statements.
In order to use this service, Jet engine must be enabled on the members and the
hazelcast-sqlmodule 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 disabledor
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-sqlmodule to your member’s classpath.
Hazelcast is currently able to execute distributed SQL queries using the following connectors:
IMap (to query data stored in a
SQL statements are not atomic. INSERT/SINK can fail and commit part of the data.
Before you can access any object using SQL, a mapping has to be created. See the documentation for the
When a query is executed, an
SqlResultis 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
SqlResultfor more information about different iteration methods.
- execute(sql: str, *params: Any, cursor_buffer_size: int = 4096, timeout: float = -1, expected_result_type: int = 0, schema: Optional[str] = None) Future[SqlResult] ¶
Executes an SQL statement.
sql – 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.
The cursor buffer size measured in the number of rows.
When a statement is submitted for execution, a
SqlResultis 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.
The execution timeout in seconds.
If the timeout is reached for a running statement, it will be cancelled forcefully.
Zero value means no timeout.
-1means that the value from the server-side config will be used. Other negative values are prohibited.
expected_result_type – The expected result type.
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,
Fooare different schemas.
The default value is
Nonemeaning only the default search path is used.
The execution result.
HazelcastSqlError – In case of execution error.
AssertionError – If the
sqlparameter is not a string, the
schemais not a string or
timeoutis not an integer or float, or the
cursor_buffer_sizeis not an integer.
ValueError – If the
sqlparameter is an empty string, the
timeoutis negative and not equal to
cursor_buffer_sizeis not positive.
TypeError – If the
expected_result_typedoes not equal to one of the values or names of the members of the
- class SqlColumnMetadata(name, column_type, nullable, is_nullable_exists)¶
Metadata of a column in an SQL row.
- property name: str¶
Name of the column.
- property type: int¶
Type of the column.
- property nullable: bool¶
Trueif this column values can be
- class SqlColumnType¶
- VARCHAR = 0¶
- BOOLEAN = 1¶
- TINYINT = 2¶
- SMALLINT = 3¶
- INTEGER = 4¶
- BIGINT = 5¶
- DECIMAL = 6¶
- REAL = 7¶
- DOUBLE = 8¶
- DATE = 9¶
- TIME = 10¶
- TIMESTAMP = 11¶
- TIMESTAMP_WITH_TIME_ZONE = 12¶
- OBJECT = 13¶
Could be represented by any Python class.
- NULL = 14¶
The type of the generic SQL
The only valid value of
- exception HazelcastSqlError(originating_member_uuid, code, message, cause, suggestion=None)¶
Represents an error occurred during the SQL query execution.
- property originating_member_uuid: UUID¶
UUID of the member that caused or initiated an error condition.
- property suggestion: str¶
Suggested SQL statement to remediate experienced error.
- class SqlRowMetadata(columns)¶
Metadata for the returned rows.
- COLUMN_NOT_FOUND = -1¶
Constant indicating that the column is not found.
- property column_count: int¶
Number of columns in the row.
- get_column(index: int) SqlColumnMetadata ¶
index – Zero-based column index.
Metadata for the given column index.
IndexError – If the index is out of bounds.
AssertionError – If the index is not an integer.
- class SqlRow(row_metadata, row)¶
One of the rows of an SQL query result.
The columns of the rows can be retrieved using
Apart from these methods, the row objects can also be treated as a
listand columns can be retrieved using the
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
get_object()will be called. Note that,
strvalues. Hence, the
operator will raise error for any type other than integer and string.
- get_object(column_name: str) Any ¶
Gets the value in the column indicated by the column name.
The type of the returned value depends on the SQL type of the column. No implicit conversions are performed on the value.
column_name – The column name.
Value of the column.
ValueError – If a column with the given name does not exist.
AssertionError – If the column name is not a string.
- get_object_with_index(column_index: int) Any ¶
Gets the value of the column by index.
The class of the returned value depends on the SQL type of the column. No implicit conversions are performed on the value.
column_index – Zero-based column index.
Value of the column.
IndexError – If the column index is out of bounds.
AssertionError – If the column index is not an integer.
- class SqlResult(sql_service, connection, query_id, cursor_buffer_size, execute_response)¶
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
withstatement 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 = 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() Iterator[Future[SqlRow]] ¶
Returns the iterator over the result rows.
The iterator may be requested only once.
ValueError – If the result only contains an update count, or the iterator is already requested.
Iterator that produces Future of
SqlRows. See the class documentation for the correct way to use this.
- is_row_set() bool ¶
Returns whether this result has rows to iterate.
- update_count() int ¶
Returns the number of rows updated by the statement or
-1if this result is a row set. In case the result doesn’t contain rows but the update count isn’t applicable or known,
- get_row_metadata() SqlRowMetadata ¶
Gets the row metadata.
ValueError – If the result only contains an update count.
- close() Future[None] ¶
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.