Executing SQL Queries
You can use Management Center to execute SQL queries on a Hazelcast cluster.
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.
Click SQL Browser in the toolbar.
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:
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:
Under the mapping name, you can find columns and indexes with their types.
To execute a query, click Execute Query.
When the query is executed, the results are displayed under 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.
You can use Export to export the query result as a CSV file in the RFC 4180 format or as a JSON file.
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.
To remove the query results, click Clear Query Result.
Open the SQL browser and execute the following SQL statement to create a map called
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
|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.
If you’re interested in learning more about SQL in Hazelcast, see the SQL reference in the Platform documentation.