Executing SQL Queries

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

SQL Browser Toolbar Icon

Click SQL Browser in the toolbar.

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.

To generate the CREATE MAPPING statement automatically, click the Connect Maps button and select the name of an existing map from the dropdown:

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.

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

You can view results in two modes:

Show first 1000 records: Display only the first 1000 records that are returned by the query.
Stream last 1000 records: Display only the last 1000 records returned by the streaming query. The most recent record is always on the top of the list.

This mode is only for displaying the results of streaming SQL queries.

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

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');
```

Delete the above statement and now execute the following.

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

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

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.