GridGain Developers Hub

Querying

The SQL screen allows you to execute SQL queries against the cluster and view results. You can also view running queries and query statistics.

sql screen

Overview of SQL Screen

The SQL screen consists of three tabs:

  • QUERIES LIST — execute SQL queries

  • RUNNING QUERIES — view the list of running queries

  • QUERY STATISTICS — view query statistics

The QUERIES LIST tab displays a tree that lets you view the tables, caches, and nodes that are available in the cluster.

Tree

The tree consists of the following branches:

Executing SQL Queries

Control Center allows you to execute both DML and DDL statements supported by GridGain/Apache Ignite. See the SQL Reference guide for details.

To execute a SQL statement, you click the + icon in the queries tab bar and select SQL Query. The, you enter a statement in the query editor, and click EXECUTE.

Query Editor

The query results are displayed below the query editor.

Selecting a Schema

You can set the schema for SQL statements in the Default Schema field. The drop-down field contains a list of existing schemas. Control Center uses the schema that you selected in the Default Schema field to resolve any unqualified reference that is included within any SQL statement that is executed on the tab. Refer to the Understanding Schemas page for more information about schemas in GridGain.

Non-Colocated Joins

Allow non-collocated joins. Set this flag if you want to execute a join query that joins tables on a non-affinity key. If you don’t set the flag, the results of the query might be incorrect. Refer to this section for details.

Join Order

Enforce join order. This option enforces GridGain to use the order of joins as the order is specified in the query, rather than to rely on the optimizer. The optimizer cannot always determine the best order. Refer to this section for details.

Lazy Loading

Lazy result set. You set this flag to lazily load the results of the query. Use of this flag can help to prevent OutOfMemory exceptions that may occur when the result set is too large. Refer to the Lazy Result Loading section for details.

Executing Queries on a Specific Node

In a multi-node cluster, the data that is held in a table is distributed among all server nodes. When you run a SQL query, the query is distributed among the nodes in a map-reduce manner. However, you can limit the scope of the query to a specific node. If you limit the scope, the query is run against the data that is held on the specified node.

Click SELECT PARTICULAR NODE, and, in the dialog box that appears, you select the node.

Select Node

Using the Explain Statement

You can use the EXPLAIN statement to view the execution plan for a SELECT query. The execution plan can help you to analyze the query for performance optimization. Simply add 'EXPLAIN' at the beginning of the statement and click EXECUTE.

Distributed queries are executed in a map-reduce manner. The execution plan consists of two parts: the map query (the query executed on each node with data) and the reduce query (the "reduce" part, which is performed on the node that initiates the query, the coordinator node).

Explain

Running Queries

The RUNNING QUERIES tab displays all running queries.

Column Description

Type

  • SCAN, a scan query

  • SQL, a SQL query that is executed via JDBC/ODBC

  • SQL_FIELDS, a SQL query that is executed via the SqlFieldsQuery API

Query

The query

Nodes

The number of nodes

Schema

The schema

Start Time

The time that the query started

Duration

The duration of the query

You can filter the queries by query substring, type, and duration.

Query Statistics

The QUERY STATISTICS tab displays statistics on the queries that have been executed in the cluster. The list includes the queries that where executed via Control Center and also via other tools.

The list is limited to the most recent 1000 queries.

The statistics includes the following:

  • how many times the query was executed

  • how many times the query failed or succeeded

  • what where the minimum, average, and maximum execution time

  • when the query was most recently executed

Query Statistics

You can find the queries that match a specific text string by using the Filters field. Simply enter the text in the input field, and the list is filtered as you type.