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    

In addition to JDBC drivers, Java developers can use special SQL APIs to query and modify data stored in the database:

SqlQuery

SqlQuery is useful for scenarios when at the end of a query execution you need to get the whole object back in the result set. The code snippet below shows how to achieve this:

IgniteCache<Long, Person> cache = ignite.cache("personCache");

SqlQuery sql = new SqlQuery(Person.class, "salary > ?");

// Find all persons earning more than 1,000.
try (QueryCursor<Entry<Long, Person>> cursor = cache.query(sql.setArgs(1000))) {
  for (Entry<Long, Person> e : cursor)
    System.out.println(e.getValue().toString());
}

SqlQuery only supports queries of the type select * from ... or select alias.* from ... and does not support queries with a subset of columns. Nor does it support SELECT TOP or SELECT DISTINCT clauses. Any attempt to execute unsupported queries will throw an exception. Use SqlFieldsQueries for such queries instead.

Because SqlQuery always returns all fields, you can omit the SELECT statement in the query entirely, e.g. use FROM Persons WHERE ... or provide only the WHERE clause, e.g. salary > ? as in the example above.

SqlFieldsQueries

Instead of selecting the whole object, you can choose to select only specific fields in order to minimize network and serialization overhead. For this purpose, Ignite implements a concept of fields queries. SqlFieldsQuery accepts a standard SQL query as its constructor​ parameter and executes it as shown in the example below:

IgniteCache<Long, Person> cache = ignite.cache("personCache");

// Execute query to get names of all employees.
SqlFieldsQuery sql = new SqlFieldsQuery(
  "select concat(firstName, ' ', lastName) from Person");

// Iterate over the result set.
try (QueryCursor<List<?>> cursor = cache.query(sql)) {
  for (List<?> row : cursor)
    System.out.println("personName=" + row.get(0));
}

Queryable Fields Definition

Before specific fields can be accessed from SqlQuery or SqlFieldsQuery, they have to be a part of the SQL schema. Use standard DDL commands or Java specific annotations and QueryEntity configuration for the fields definition.

With SqlFieldsQuery you can execute the rest of DML commands in order to modify the data:

IgniteCache<Long, Person> cache = ignite.cache("personCache");

cache.query(new SqlFieldsQuery(
    "INSERT INTO Person(id, firstName, lastName) VALUES(?, ?, ?)").
    setArgs(1L, "John", "Smith"));
IgniteCache<Long, Person> cache = ignite.cache("personCache");

cache.query(new SqlFieldsQuery("UPDATE Person set lastName = ? " +
         "WHERE id >= ?").setArgs("Jones", 2L));
IgniteCache<Long, Person> cache = ignite.cache("personCache");

cache.query(new SqlFieldsQuery("DELETE FROM Person " +
         "WHERE id >= ?").setArgs(2L));
IgniteCache<Long, Person> cache = ignite.cache("personCache");

cache.query(new SqlFieldsQuery("MERGE INTO Person(id, firstName, lastName)" +
           " values (1, 'John', 'Smith'), (5, 'Mary', 'Jones')"));

Example

Ignite distribution includes ready-to-run SqlDmlExample as a part of its sources. This example demonstrates the usage of all the above-mentioned DML operations.

SQL API


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.