GridGain Developers Hub
GitHub logo GridGain iso GridGain.com
GridGain Software Documentation

SQL API

In addition to using the JDBC driver, Java developers can use GridGain’s SQL APIs to query and modify data stored in GridGain.

The SqlFieldsQuery class is an interface for executing SQL statements and navigating through the results. SqlFieldsQuery is executed through the IgniteCache.query(SqlFieldsQuery) method, which returns a query cursor.

Configuring Queryable Fields

If you want to query a cache using SQL statements, you need to define which fields of the value objects are queryable. Queryable fields are the fields of your data model that the SQL engine can "see" and query.

In Java, queryable fields can be configured in two ways:

  • using annotations

  • by defining query entities

@QuerySqlField Annotation

To make specific fields queryable, annotate the fields in the value class definition with the @QuerySqlField annotation and call CacheConfiguration.setIndexedTypes(…​).

public class Person implements Serializable {
  /** Indexed field. Will be visible to the SQL engine. */
  @QuerySqlField (index = true)
  private long id;

  /** Queryable field. Will be visible to the SQL engine. */
  @QuerySqlField
  private String name;

  /** Will NOT be visible to the SQL engine. */
  private int age;

  /**
   * Indexed field sorted in descending order.
   * Will be visible to the SQL engine.
   */
  @QuerySqlField(index = true, descending = true)
  private float salary;
}

public static void main(String[] args) {
  CacheConfiguration<Long, Person> personCacheCfg = new CacheConfiguration<String, Person>();
  personCacheCfg.setName("Person");

  personCacheCfg.setIndexedTypes(Long.class, Person.class);
  IgniteCache<Long, Person> cache = ignite.createCache(personCacheCfg);
}

Make sure to call CacheConfiguration.setIndexedTypes(…​) to let the SQL engine know about the annotated fields.

class Person
{
    // Indexed field. Will be visible to the SQL engine.
    [QuerySqlField(IsIndexed = true)] public long Id;

    //Queryable field. Will be visible to the SQL engine
    [QuerySqlField] public string Name;

    //Will NOT be visible to the SQL engine.
    public int Age;

    /**
      * Indexed field sorted in descending order.
      * Will be visible to the SQL engine.
    */
    [QuerySqlField(IsIndexed = true, IsDescending = true)]
    public float Salary;
}

public static void SqlQueryFieldDemo()
{
    var cacheCfg = new CacheConfiguration
    {
        Name = "cacheName",
        QueryEntities = new[]
        {
            new QueryEntity(typeof(int), typeof(Person))
        }
    };

    var ignite = Ignition.Start();
    var cache = ignite.CreateCache<int, Person>(cacheCfg);
}

Query Entities

You can define queryable fields using the QueryEntity class. Query entities can be configured via XML configuration.

<bean class="org.apache.ignite.configuration.CacheConfiguration">
    <property name="name" value="Person"/>
    <!-- Configure query entities -->
    <property name="queryEntities">
        <list>
            <bean class="org.apache.ignite.cache.QueryEntity">
                <!-- Setting indexed type's key class -->
                <property name="keyType" value="java.lang.Long"/>

                <!-- Key field name to be used in INSERT and SELECT queries -->
                <property name="keyFieldName" value="id"/>

                <!-- Setting indexed type's value class -->
                <property name="valueType"
                          value="org.apache.ignite.examples.Person"/>

                <!-- Defining fields that will be either indexed or queryable.
                Indexed fields are added to 'indexes' list below.-->
                <property name="fields">
                    <map>
                        <entry key="id" value="java.lang.Long"/>
                        <entry key="name" value="java.lang.String"/>
                        <entry key="salary" value="java.lang.Float "/>
                    </map>
                </property>

                <!-- Defining indexed fields.-->
                <property name="indexes">
                    <list>
                        <!-- Single field (aka. column) index -->
                        <bean class="org.apache.ignite.cache.QueryIndex">
                            <constructor-arg value="name"/>
                        </bean>

                        <!-- Group index. -->
                        <bean class="org.apache.ignite.cache.QueryIndex">
                            <constructor-arg>
                                <list>
                                    <value>id</value>
                                    <value>salary</value>
                                </list>
                            </constructor-arg>
                            <constructor-arg value="SORTED"/>
                        </bean>
                    </list>
                </property>
            </bean>
        </list>
    </property>
</bean>
public class Person implements Serializable {
  private long id;

  private String name;

  private int age;

  private float salary;
}

public static void main(String[] args) {
  CacheConfiguration<Long, Person> personCacheCfg = new CacheConfiguration<String, Person>();
  personCacheCfg.setName("Person");

  QueryEntity queryEntity = new QueryEntity(String.class, Person.class)
            .addQueryField("id", Long.class.getName(), null)
            .addQueryField("orgId", Integer.class.getName(), null)
            .addQueryField("salary", Float.class.getName(), null)
            .addQueryField("name", String.class.getName(), null);

  queryEntity.setIndexes(Arrays.asList(new QueryIndex("id"), new QueryIndex("salary", false)));

  personCacheCfg.setQueryEntities(Arrays.asList(queryEntity));
}
private class Person
{
    public long Id;

    public string Name;

    public int Age;

    public float Salary;
}

public static void QueryEntitiesDemo()
{
    var personCacheCfg = new CacheConfiguration
    {
        Name = "Person",
        QueryEntities = new[]
        {
            new QueryEntity
            {
                KeyType = typeof(long),
                ValueType = typeof(Person),
                Fields = new[]
                {
                    new QueryField("Id", typeof(long)),
                    new QueryField("Name", typeof(string)),
                    new QueryField("Age", typeof(int)),
                    new QueryField("Salary", typeof(float))
                },
                Indexes = new[]
                {
                    new QueryIndex("Id"),
                    new QueryIndex(true, "Salary"),
                }
            }
        }
    };
    var ignite = Ignition.Start();
    var personCache = ignite.CreateCache<int, Person>(personCacheCfg);
}

Querying

To execute a select query on a cache, simply create an object of SqlFieldsQuery providing the query string to the constructor and run cache.query(…​). Note that in the following example, the Person cache must be configured to be visible to the SQL engine.

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

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));
}
var cache = ignite.GetCache<long, Person>("Person");

var sql = new SqlFieldsQuery("select concat(FirstName, ' ', LastName) from Person");

using (var cursor = cache.Query(sql))
{
    foreach (var row in cursor)
    {
        Console.WriteLine("personName=" + row[0]);
    }
}
Cache<int64_t, Person> cache = ignite.GetOrCreateCache<int64_t, Person>("Person");

// Iterate over the result set.
// SQL Fields Query can only be performed using fields that have been listed in "QueryEntity" been of the config!
QueryFieldsCursor cursor = cache.Query(SqlFieldsQuery("select concat(firstName, ' ', lastName) from Person"));
while (cursor.HasNext())
{
    std::cout << "personName=" << cursor.GetNext().GetNext<std::string>() << std::endl;
}

SqlFieldsQuery return a cursor that iterates through the results that match the SQL query.

Local Execution

To force local execution of a query, use SqlFieldsQuery.setLocal(true). In this case, the query will be executed against the data stored on the node where the query is run. It means that the results of the query will almost always be incomplete. Use the local mode only if you are confident you understand this limitation.

Subqueries in WHERE Clause

SELECT queries used in INSERT and MERGE statements as well as SELECT queries generated by UPDATE and DELETE operations will be distributed and executed in either colocated or non-colocated distributed modes.

However, if there is a subquery that is executed as part of a WHERE clause, then it can be executed in the colocated mode only.

For instance, let’s consider the following query:

DELETE FROM Person WHERE id IN
    (SELECT personId FROM Salary s WHERE s.amount > 2000);

The SQL engine will generate the SELECT query in order to get a list of entries to be deleted. The query will be distributed and executed across the cluster and will look like the one below:

SELECT _key, _val FROM Person WHERE id IN
    (SELECT personId FROM Salary s WHERE s.amount > 2000);

However, the subquery from the IN clause (SELECT personId FROM Salary …​) will not be distributed further and will be executed over the local data set available on the node.

Inserting, Updating, Deleting, and Merging

With SqlFieldsQuery you can execute the other 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")).getAll();
IgniteCache<Long, Person> cache = ignite.cache("personCache");
IgniteCache<Long, Person> cache = ignite.cache("personCache");

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

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

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

When using SqlFieldsQuery to execute DDL statements, you must call getAll() on the cursor returned from the query(…​) method.

Specifying the Schema

By default, any SELECT statement executed via SqlFieldsQuery is resolved against the PUBLIC schema. However, if the table you want to query is in a different schema, you can specify the schema by calling SqlFieldsQuery.setSchema(…​). In this case, the statement will be run in the given schema.

SqlFieldsQuery sql = new SqlFieldsQuery("select name from City").setSchema("PERSON");
var sqlFieldsQuery = new SqlFieldsQuery("select name from City") {Schema = "PERSON"};
// SQL Fields Query can only be performed using fields that have been listed in "QueryEntity" been of the config!
SqlFieldsQuery sql = SqlFieldsQuery("select name from City");
sql.SetSchema("PERSON");

Alternatively, you can define the schema in the statement:

SqlFieldsQuery sql = new SqlFieldsQuery("select name from Person.City");

Creating Tables

You can pass any supported DDL statement to SqlFieldsQuery and execute it on a cache as shown below.

IgniteCache<Long, Person> cache = ignite.getOrCreateCache(
    new CacheConfiguration<>()
        .setName("Person");

// Creating City table.
cache.query(new SqlFieldsQuery("CREATE TABLE City (id int primary key, name varchar, region varchar)")).getAll();
var cache = ignite.GetOrCreateCache<long, Person>(
    new CacheConfiguration
    {
        Name = "Person"
    }
);

//Creating City table
cache.Query(new SqlFieldsQuery("CREATE TABLE City (id int primary key, name varchar, region varchar)"));
Cache<int64_t, Person> cache = ignite.GetOrCreateCache<int64_t, Person>("Person");

// Creating City table.
cache.Query(SqlFieldsQuery("CREATE TABLE City (id int primary key, name varchar, region varchar)"));

In terms of SQL schema, the following tables will be created as a result of executing the code:

  • Table "Person" in the "Person" schema (if it hasn’t been created before).

  • Table "City" in the "Person" schema.

To query the "City" table, use statements like select * from Person.City or new SqlFieldsQuery("select * from City").setSchema("PERSON") (note the uppercase).

Cancelling Queries

There are two ways to cancel long running queries.

The first approach is to prevent run away queries by setting a query execution timeout.

SqlFieldsQuery query = new SqlFieldsQuery("SELECT * from Person");

// Setting query execution timeout
query.setTimeout(10_000, TimeUnit.SECONDS);
var query = new SqlFieldsQuery("select * from Person") {Timeout = TimeSpan.FromSeconds(10)};

The second approach is to halt the query by using QueryCursor.close().

SqlFieldsQuery query = new SqlFieldsQuery("SELECT * FROM Person");

//Executing the query
QueryCursor<List<?>> cursor = cache.query(query);

// Halting the query that might be still in progress.
cursor.close();
var qry = new SqlFieldsQuery("select * from Person");
var cursor = cache.Query(qry);

//Executing query

//Halting the query that might be still in progress
cursor.Dispose();

Example

GridGain Community Edition distribution includes a ready-to-run SqlDmlExample as a part of its source code. This example demonstrates the usage of all the above-mentioned DML operations.