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    

C#/.NET 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:

var cache = ignite.GetOrCreateCache<int, Person>("personCache");

var sql = new SqlQuery(typeof(Person), "Salary > ?");

var cursor = cache.Query(sql);

foreach (var cacheEntry in cursor)
    Console.WriteLine(cacheEntry.Value);
var cache = ignite.GetOrCreateCache<int, Person>("personCache");

// SQL join on Person and Organization.
var sql = new SqlQuery(typeof(Person), "from Person as p," +
    "\"orgCache\".Organization as org" + 
    "where p.OrgId = org.Id " +
    "and lower(org.Name) = lower(?)");

// Find all persons working for Ignite organization.
foreach (var cacheEntry in cache.Query(sql))
    Console.WriteLine(cacheEntry.Value);

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 has a concept of fields queries. SqlFieldsQuery accepts a standard SQL query as its constructor​ parameter and executes it as shown in the example below: :

var cache = ignite.GetOrCreateCache<int, Person>("personCache");

// Execute query to get names of all employees.
var sql = new SqlFieldsQuery(
    "select concat(FirstName, ' ', LastName) from Person as p");

// Iterate over the result set.
foreach (var fields in cache.QueryFields(sql))
    Console.WriteLine("Person Name = {0}", fields[0]);
// In this example, suppose Person objects are stored in a 
// cache named 'personCache' and Organization objects 
// are stored in a cache named 'orgCache'.
var personCache = ignite.GetOrCreateCache<int, Person>("personCache");

// Select with join between Person and Organization to 
// get the names of all the employees of a specific organization.
var sql = new SqlFieldsQuery(
    "select p.Name  " +
    "from Person as p, \"orgCache\".Organization as org where " +
    "p.OrgId = org.Id " +
    "and org.Name = ?", "Ignite");

foreach (IList fields in personCache.QueryFields(sql))
    Console.WriteLine("Person Name = {0}", fields[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 .NET specific attributes and QueryEntity based configurations for the fields definition.

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

cache.QueryFields(new SqlFieldsQuery("INSERT INTO Person(id, firstName, " +
    "lastName) values (1, 'John', 'Smith'), (5, 'Mary', 'Jones')"));
cache.QueryFields(new SqlFieldsQuery("MERGE INTO Person(id, firstName, " +
    "lastName) values (1, 'John', 'Smith'), (5, 'Mary', 'Jones')"));
cache.QueryFields(new SqlFieldsQuery("UPDATE Person set lastName = ? " +
    "WHERE id >= ?", "Jones", 2L));
cache.QueryFields(new SqlFieldsQuery("DELETE FROM Person " +
    "WHERE id >= ?", 2));

Example

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

Troubleshooting SQL Queries

When SQL query fails (Failed to parse query and other exceptions), make sure to examine the InnerException property: it contains full error message from Ignite SQL engine with details on what exactly has failed. You can do that in Visual Studio debugger or by calling ToString() on the exception object:

try 
{
    IQueryCursor<List> cursor = cache.QueryFields(query);  
}
catch (IgniteException e) 
{
    Console.WriteLine(e.ToString());
}