SQL
You can use SQL to query data in maps, Kafka topics, or a variety of file systems. Results can be sent directly to the client or inserted into maps or Kafka topics.
For a hands-on introduction to SQL, see Get Started with SQL Over Maps. |
Before you Begin
To use the SQL service, the Jet engine must be enabled on the cluster. For information about how to enable the Jet engine, see Securing Jobs.
Connecting to the SQL Service
You can connect to the SQL service of a Hazelcast member using one of the following options:
-
SQL shell or Management Center: For fast prototyping.
-
JDBC driver or the Java client: For Java applications.
You cannot run SQL queries on lite members. This limitation will be removed in future releases. |
Using the SQL Shell
The easiest way to run SQL queries on a cluster is to connect to the SQL shell.
To connect to the SQL shell, run the hz-cli sql
command of your member’s built-in CLI.
bin/hz-cli sql
bin\hz-cli.bat sql
Replace the <cluster-name>
placeholder with the name of your cluster and the <address>
placeholder with your member’s IP address.
docker run --network hazelcast-network -it --rm hazelcast/hazelcast:5.5.1 hz-cli --targets <cluster-name>@<address> sql
Mappings
Before you can query data in SQL, you need to create a mapping to the data source. Mappings store essential metadata about the source’s data model, data access patterns, and serialization formats so that the SQL service can connect to the data source and query it.
Currently, you can create mappings for the following data sources:
SQL Metadata
To avoid the loss of SQL mappings, data connections, or views after a cluster restart, you can enable the persistence of SQL metadata.
Case Sensitivity
Mapping names and field names are case-sensitive.
For example, you can access an employee
map
as employee
but not as Employee
.
Keywords or built-in function names are case-insensitive.
Permissions and Security
Enterprise Edition
If security is enabled, you can set permissions for the following:
-
Connectors (mappings)
-
Some SQL statements
When you run a query, Hazelcast runs it as a job. As a result, clients with the read permission for jobs can see the SQL query text and the arguments. See Job Permissions.
|
Supported Statements
Hazelcast supports a subset of standard ANSI SQL statements as well as some custom ones for creating jobs, mappings, and streaming queries.
For a list of supported statements, see SQL Statements.
Limitations
SQL has the following limitations. We plan to remove these limitations in future releases.
-
The only supported Hazelcast data structure is map.
You cannot query other data structures such as replicated maps.
-
Although you can create bitmap indexes in SQL, SQL queries do not leverage those indexes to improve query performance.
-
You cannot run SQL queries on lite members. This limitation will be removed in future releases.
SQL Jobs
When a SQL statement is submitted for execution, the SQL service parses and optimizes it. The result is an execution plan in the form of a job that is cached and reused by subsequent executions of the same statement.
For details about how Hazelcast models and executes jobs, see Jet: How Hazelcast Models and Executes Jobs.