Apache Ignite SQL Documentation

The Apache Ignite SQL Developer Hub

Welcome to the Apache Ignite SQL developer hub. You'll find comprehensive guides and documentation to help you start working with Apache Ignite SQL as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started    

Retrieve data from a table or multiple tables.

SELECT
    [TOP term] [DISTINCT | ALL] selectExpression [,...]
    FROM tableExpression [,...] [WHERE expression]
    [GROUP BY expression [,...]] [HAVING expression]
    [{UNION [ALL] | MINUS | EXCEPT | INTERSECT} select] 
    [ORDER BY order [,...]]
    [{ LIMIT expression [OFFSET expression] 
    [SAMPLE_SIZE rowCountInt]} | {[OFFSET expression {ROW | ROWS}] 
    [{FETCH {FIRST | NEXT} expression {ROW | ROWS} ONLY}]}]

Parameters

  • DISTINCT - specifies to remove duplicate rows from a result set.
  • GROUP BY - groups the result by the given expression(s).
  • HAVING - filters rows after grouping.
  • ORDER BY - sorts the result by the given column(s) or expression(s).
  • LIMIT and FETCH FIRST/NEXT ROW(S) ONLY - limits the number of rows returned by the query (no limit if null or smaller than zero).
  • OFFSET - specifies​ how many rows to skip.
  • UNION, INTERSECT, MINUS, EXPECT - combines the result of this query with the results of another query.
  • tableExpression - Joins a table. The join expression is not supported for cross and natural joins. A natural join is an inner join, where the condition is automatically on the columns with the same name.
tableExpression = [[LEFT | RIGHT]{OUTER}] | INNER | CROSS | NATURAL] 
JOIN tableExpression 
[ON expression]
  • LEFT - LEFT JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records.
  • RIGHT - RIGHT JOIN performs a join starting with the second (right-most) table and then any matching first (left-most) table records.
  • OUTER - Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both).
  • INNER - An inner join requires each row in the two joined tables to have matching column values.
  • CROSS - CROSS JOIN returns the Cartesian product of rows from tables in the join.
  • NATURAL - The natural join is a special case of equi-join.
  • ON - Value or condition to join on.

Description

SELECT queries can be executed against both replicated and partitioned data.

When executed against fully replicated data, Ignite will send a query it to a single cluster node and run it over the local data there.

On the other hand, if a query is executed over partitioned data, then the execution flow will be the following:

  • The query will be parsed and split into multiple map queries and a single reduce query.
  • All the map queries are executed on all the nodes where required data resides.
  • All the nodes provide result sets of local execution to the query initiator (reducer) that, in turn, will accomplish the reduce phase by properly merging provided result sets.

JOINs

Ignite supports collocated and non-collocated distributed SQL joins. Furthermore, if the data resides in different tables (aka. caches in Ignite), Ignite allows for cross-table joins as well.

Joins between partitioned and replicated data sets always work without any limitations.

However, if you join partitioned data sets, then you have to make sure that the keys you are joining on are either collocated or you switched on the non-collocated joins parameter for a query.

Refer to Distributed Joins page for more details.

Group By and Order By Optimizations

SQL queries with ORDER BY clause do not require loading the whole result set to a query initiator (reducer) node in order to complete the sorting. Instead, every node where a query will be mapped to will sort its own part of the overall result set and the reducer will do the merge in a streaming fashion.

The same optimization is implemented for sorted GROUP BY queries - there is no need to load the whole result set to the reducer in order to do the grouping before giving it to an application. In Apache Ignite, partial result sets from the individual nodes can be streamed, merged, aggregated, and returned to the application gradually.

Examples

Retrieve all rows from Person table:

SELECT * FROM Person;

Get all rows in the alphabetical order:

SELECT * FROM Person ORDER BY name;

Calculate a number of Persons from a specific city:

SELECT city_id, COUNT(*) FROM Person GROUP BY city_id;

Join data stored in Person and City tables:

SELECT p.name, c.name
	FROM Person p, City c
	WHERE p.city_id = c.id;

Note

Java, .NET, C++ users can execute SELECT queries using native APIs:

See Also