This is a prerelease version.

View latest

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:

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.

  • Binary

  • Docker

Mac and Linux
bin/hz-cli sql
Windows
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:6.0.0-SNAPSHOT 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:

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.