JavaC#/.NETC++SQL*IntegrationsIgnite for SparkTools
Guides
ExamplesAsk a Question

SQL API

❗️

This is a legacy Apache Ignite documentation

The new documentation is hosted here: https://ignite.apache.org/docs/latest/

C#/.NET developers can use special SQL APIs to query and modify data stored in the database:

SqlFieldsQueries

SqlFieldsQuery accepts a standard SQL query as its constructor​ parameter and executes it as shown in the example below. You can choose to select only specific fields in order to minimize network and serialization overhead.

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 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:

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

Updated over 4 years ago