SQL¶
- 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.
- 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
orCannot 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 thehazelcast-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: str, *params: Any, cursor_buffer_size: int = 4096, timeout: float = - 1, expected_result_type: int = 0, schema: Optional[str] = None) hazelcast.future.Future[hazelcast.sql.SqlResult] [source]¶
Executes an SQL statement.
- Parameters
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.cursor_buffer_size –
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 –
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 – The expected result type.
schema –
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
andFoo
are different schemas.The default value is
None
meaning only the default search path is used.
- Returns
The execution result.
- Raises
HazelcastSqlError – In case of execution error.
AssertionError – If the
sql
parameter is not a string, theschema
is not a string orNone
, thetimeout
is not an integer or float, or thecursor_buffer_size
is not an integer.ValueError – If the
sql
parameter is an empty string, thetimeout
is negative and not equal to-1
, thecursor_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 theSqlExpectedResultType
.
- class SqlColumnMetadata(name, column_type, nullable, is_nullable_exists)[source]¶
Bases:
object
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¶
True
if this column values can beNone
,False
otherwise.
- 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
.
- 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
withNone
tzinfo
.
- TIMESTAMP_WITH_TIME_ZONE = 12¶
Represented by
datetime.datetime
withnon-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 isNone
.
- JSON = 15¶
Represented by
hazelcast.core.HazelcastJsonValue
.
- 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.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)[source]¶
Bases:
object
Metadata for the returned rows.
- COLUMN_NOT_FOUND = -1¶
Constant indicating that the column is not found.
- property columns: List[hazelcast.sql.SqlColumnMetadata]¶
List of column metadata.
- property column_count: int¶
Number of columns in the row.
- get_column(index: int) hazelcast.sql.SqlColumnMetadata [source]¶
- Parameters
index – Zero-based column index.
- Returns
Metadata for the given column index.
- Raises
IndexError – If the index is out of bounds.
AssertionError – If the index is not an integer.
- find_column(column_name: str) int [source]¶
- Parameters
column_name – Name of the column.
- Returns
Column index or
COLUMN_NOT_FOUND
if a column with the given name is not found.- 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
orlist
and columns can be retrieved using the[]
operator.If an integer value is passed to the
[]
operator, it will implicitly call theget_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()
expectsstr
values. Hence, the[]
operator will raise error for any type other than integer and string.- get_object(column_name: str) Any [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 useSqlRowMetadata.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 – The column name.
- 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.
- get_object_with_index(column_index: int) Any [source]¶
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.
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_index – Zero-based column index.
- Returns
Value of the column.
- Raises
IndexError – If the column index is out of bounds.
AssertionError – If the column index is not an integer.
HazelcastSqlError – If the object cannot be deserialized.
- property metadata: hazelcast.sql.SqlRowMetadata¶
The row metadata.
- class SqlResult(sql_service, connection, query_id, cursor_buffer_size, execute_response)[source]¶
Bases:
Iterable
[hazelcast.sql.SqlRow
]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() Iterator[hazelcast.future.Future[hazelcast.sql.SqlRow]] [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.
- update_count() int [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.
- get_row_metadata() hazelcast.sql.SqlRowMetadata [source]¶
Gets the row metadata.
- Raises
ValueError – If the result only contains an update count.
- close() hazelcast.future.Future[None] [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.