SQL API
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++ 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.

using namespace ignite;
using namespace cache;

IgniteConfiguration cfg;
Ignite grid = Ignition::Start(cfg);

Cache<int, Person> cache = grid.GetOrCreateCache<int, Person>("myCache");

SqlFieldsQuery qry(
  "select concat(FirstName, ' ', LastName), Organization.Name "
  "from Person, Organization where "
  "Person.OrgId = Organization.Id and "
  "Person.Salary > ?");

qry.AddArgument(1000);

QueryFieldsCursor cursor = cache.Query(qry);

// Iterate over results.
while (cursor.HasNext())
{
  QueryFieldsRow row = cursor.GetNext();

  std::cout << row.GetNext<std::string>() << " " 
            << row.GetNext<std::string>() << std::endl;
}

using namespace ignite;
using namespace cache;

IgniteConfiguration cfg;
Ignite grid = Ignition::Start(cfg);

Cache<int, Person> cache = grid.GetOrCreateCache<int, Person>("myCache");

SqlFieldsQuery qry(
  "select Person.name "
  "from Person, \"orgCache\".Organization where "
  "Person.orgId = Organization.id "
  "and Organization.name = ?");

qry.AddArgument("Ignite");

QueryFieldsCursor cursor = cache.Query(qry);

// Iterate over results.
while (cursor.HasNext())
{
  QueryFieldsRow row = cursor.GetNext();

  std::cout << "Person name: " << row.GetNext<std::string>() << std::endl;
}

🚧

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 QueryEntity based configuration for the fields definition.

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

cache.Query(SqlFieldsQuery("INSERT INTO Person(id, firstName, "
    "lastName) values (1, 'John', 'Smith'), (5, 'Mary', 'Jones')"));

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

cache.Query(SqlFieldsQuery(
    "UPDATE Person set lastName = 'Jones' WHERE id >= 2");

cache.Query(SqlFieldsQuery("DELETE FROM Person WHERE id >= 2"));

Updated about 6 years ago