Executing SQL Queries

6.0-SNAPSHOT 5.6 5.5 5.4 5.3 5.2 5.1 5.0 4.2022.01 4.2021.12 4.2021.12-1

You can use Management Center to execute SQL queries on a Hazelcast cluster.

Before you Begin

If a map contains domain objects, the classes for those objects must be on the classpath of Management Center before you can query them. See the Providing an Extra Classpath.

Using the SQL Browser

Click SQL in the Side Bar.

SQL Browser Window

Before you can query an existing map in a cluster, you must create a mapping to it by executing the CREATE MAPPING SQL statement. This statement maps the data structure to a table that can be queried with Hazelcast SQL. You can type this statement into the editor, or you can generate it automatically.

You can generate CREATE MAPPING statements automatically using Management Center’s connector wizard. Using this wizard, you can create mappings with the connectors for Hazelcast Platform maps, Kafka, and other file systems. Launch the wizard by clicking the Connector Wizard button and then select the type of the mapping that you want to create from the dropdown.

Connecter Wizard Connector Type

See the following sections for each connector type.

Create Mapping for Hazelcast Map

  1. Select the connector type as Create Mapping for Hazelcast Map.

  2. Select the name of an existing map from the dropdown; only maps that don’t have any mappings are shown.

SQL Browser Connect Maps

If you need to change the generated statement, you can use the editor.

You cannot generate a CREATE MAPPING statement for empty maps or maps that store entries in the NATIVE format without indexes.

Click Confirm & Run.

Create Mapping for Kafka

  1. Select the connector type as Create Mapping for Kafka.

  2. Fill out the newly opened form. While you are filling this form, the editor should be updated appropriately.

SQL Browser Connector Wizard Kafka

If you need to change the generated statement, you can use the editor.

Click Confirm & Run.

Create Mapping for a File System

  1. Select the connector type as Create Mapping for a File System.

  2. Fill out the newly opened form. While you are filling this form, the editor should be updated appropriately.

SQL Browser Connector Wizard File System

If you need to change the generated statement, you can use the editor.

Click Confirm & Run.

Create Mapping for JDBC

  1. Select the connector type as Create Mapping for JDBC.

  2. Fill out the newly opened form. While you are filling this form, the editor should be updated appropriately.

SQL Browser Connector Wizard Kafka

If you need to change the generated statement, you can use the editor.

Click Confirm & Run.

Create Mapping for MongoDB

  1. Select the connector type as Create Mapping for MongoDB.

  2. Fill out the newly opened form. While you are filling this form, the editor should be updated appropriately.

SQL Browser Connector Wizard Kafka

If you need to change the generated statement, you can use the editor.

Click Confirm & Run.

After executing the CREATE MAPPING statement, you should see your mapping in the Queryable objects tree view:

SQL Browser Queryable Objects

Under the mapping name, you can find columns and indexes with their types.

Executing Queries

To execute a query, click Execute Query.

When the query is executed, the results are displayed under Query Results:

Example query results

A list of query results

Truncating Displayed Query Results

To reduce the time it takes for Management Center to display query results in the browser, the size of each table cell is limited. If a query returns too much data to fit into a cell, the result is truncated. You can adjust this limit by editing the hazelcast.mc.sql.max-cell-length property.

Viewing Map Metadata

If you run simple SELECT queries without joins or aggregations over maps, you can view metadata for each entry that is returned by the query.

A list of query results with buttons

To view an entry’s metadata, click the chevron (⌄) button next to a map entry in the query results.

A list of query results with metadata view

Autocompletion Support

The SQL editor supports autocompletion to suggest SQL keywords and identifiers for you while you type. To use autocompletion, start typing a query in the SQL editor and press Ctrl+Space. The SQL editor supports the following suggestions:

  • Table and column names

  • SQL keywords such as SELECT, INSERT, WHERE and JOIN

  • SQL functions such as COUNT, AVG and SUM

SQl Browser Autocomplete Feature

Exporting Query Results

You can use Export to export the query result as a CSV file in the RFC 4180 format or as a JSON file.

Viewing Executed SQL Queries

In the History tab, you can see the history of query execution. The history holds the last one hundred executed queries. You can re-execute any query from the history by pushing a button in the Execute column.

A history of executed queries

To remove the query results, click Clear Query Result.

Creating a Map

  1. Open the SQL browser and execute the following SQL statement to create a map called my_first_map.

    CREATE MAPPING my_first_map TYPE IMap OPTIONS ('keyFormat'='varchar','valueFormat'='varchar');

  2. Delete the above statement and now execute the following.

    SINK INTO my_first_map VALUES
('1', 'John'),
('2', 'Mary'),
('3', 'Jane');

  3. Close the SQL browser and go to Storage > Maps to verify that my_first_map is created.

    Map is Created using SQL Browser

Management Center allows you to access contents of Hazelcast data structures (for instance map entries) via SQL Browser or Map Browser. It may be useful to restrict data access for Management Center if sensitive financial or personal information is stored in the cluster. Management Center cannot access the data if at least one member has the data access disabled. You can disable data access for Management Center in the member configuration file. See the Managing Data Access section.

Next Steps

If you’re interested in learning more about SQL in Hazelcast, see the SQL reference in the Platform documentation.

lifebuoy

Help and support