SQL and Key-Value Usage

GridGain lets you use SQL and the key-value API to access cached data even when tables/caches were created and pre-loaded with SQL. You can use one or both of the methods, according to your application requirements.

This tutorial shows you how to query the cluster using both SQL and the key-value APIs using a sample project available on GitHub.

The project creates a SQL schema of cities of the world, populates it with data from a script, and:

Accesses the loaded data using SQL queries.
Accesses the loaded data using key-value operations.
Processes data remotely.

Creating Schema and Loading Data

To create a SQL schema and load data, follow the instructions provided in the README.md file. Basically, you need to create tables using the CREATE TABLE statement.

The CREATE TABLE statement supports a number of additional parameters allowing you to specify the properties of the underlying cache. For example, the statement that creates the city table is as follows:

CREATE TABLE City (
  ID INT(11),
  Name CHAR(35),
  CountryCode CHAR(3),
  District CHAR(20),
  Population INT(11),
  PRIMARY KEY (ID, CountryCode)
) WITH "template=partitioned, backups=1, affinityKey=CountryCode, CACHE_NAME=City, KEY_TYPE=demo.model.CityKey, VALUE_TYPE=demo.model.City";

Note that we define the key class and the value class using the KEY_TYPE and VALUE_TYPE parameters at the end of the statement. If you do not define these parameters, Ignite will create the required classes with default names.

For further information on how the names of the cache and corresponding keys are generated, see CREATE TABLE.

Using Key-Value API

The IgniteCache interface provides a number of methods used to access the cache via the key-value API. For example, IgniteCache.get(key) allows you to retrieve the value for a specific key. In the example below, we retrieve the Amsterdam record and update the POPULATION field. This code is executed on the client node (the data is fetched from the server nodes).

try (Ignite ignite = Ignition.start("config/ignite-config.xml")) {
    IgniteCache<CityKey, City> cityCache = ignite.cache("City");

    CityKey key = new CityKey(5, "NLD");

    //getting the city by ID and country code
    City city = cityCache.get(key);

    System.out.println(">> Updating Amsterdam record:");

    city.setPopulation(city.getPopulation() - 10_000);

    cityCache.put(key, city);

    System.out.println(cityCache.get(key));
}

This API is not presently available for C#/.NET.

IgniteConfiguration cfg;
cfg.springCfgPath = "/path/to/configuration.xml";

Ignite ignite = Ignition::Start(cfg);

Cache<CityKey, City> cityCache = ignite.GetOrCreateCache<CityKey, City>("City");

CityKey key = CityKey(5, "NLD");

cityCache.Put(key, 100000);

//getting the city by ID and country code
City city = cityCache.Get(key);

std::cout << ">> Updating Amsterdam record:" << std::endl;
city.population = city.population - 10000;

cityCache.Put(key, city);

std::cout << cityCache.Get(key).ToString() << std::endl;

You can also use binary objects to access the cached data. The benefit of using binary objects is that you avoid deserialization, which is important if you access objects from a server node that does not have the object’s class representation.

try (Ignite ignite = Ignition.start("config/ignite-config.xml")) {
    IgniteCache<BinaryObject, BinaryObject> cityCacheBinary = ignite.cache(CITY_CACHE_NAME).withKeepBinary();

    BinaryObjectBuilder cityKeyBuilder = ignite.binary().builder("demo.model.CityKey");

    cityKeyBuilder.setField("ID", 5);
    cityKeyBuilder.setField("COUNTRYCODE", "NLD");

    BinaryObject amKey = cityKeyBuilder.build();

    BinaryObject amsterdam = cityCache.get(amKey);

    System.out.printf("%1s people live in %2s \n", amsterdam.field("population"), amsterdam.field("name"));

    System.out.println(">> Updating Amsterdam record:");
    amsterdam = amsterdam.toBuilder().setField("POPULATION", (int) amsterdam.field("POPULATION") - 10_000).build();

    cityCache.put(amKey, amsterdam);

}

This API is not presently available for C++.

Executing SQL Queries

In this example, we execute SQL queries using a SqlFieldsQuery object and then iterate through the results:

try (Ignite ignite = Ignition.start("config/ignite-config.xml")) {
    IgniteCache cityCache = ignite.cache(CITY_CACHE_NAME);
    IgniteCache countryCache = ignite.cache(COUNTRY_CACHE_NAME);
    IgniteCache languageCache = ignite.cache(COUNTRY_LANGUAGE_CACHE_NAME);

    SqlFieldsQuery query = new SqlFieldsQuery(
        "SELECT name, population FROM country " +
        "ORDER BY population DESC LIMIT 10");

    FieldsQueryCursor<List<?>> cursor = countryCache.query(query);

    Iterator<List<?>> iterator = cursor.iterator();

    while (iterator.hasNext()) {
        List row = iterator.next();

        System.out.println("    >>> " + row.get(1) + " people live in " + row.get(0));
    }
}

IgniteConfiguration cfg;
cfg.springCfgPath = "config/sql.xml";

Ignite ignite = Ignition::Start(cfg);

Cache<int64_t, std::string> cityCache = ignite.GetOrCreateCache<int64_t, std::string>(CITY_CACHE_NAME);
Cache<int64_t, Country> countryCache = ignite.GetOrCreateCache<int64_t, Country>(COUNTRY_CACHE_NAME);
Cache<int64_t, std::string> languageCache = ignite.GetOrCreateCache<int64_t, std::string>(COUNTRY_LANGUAGE_CACHE_NAME);

// SQL Fields Query can only be performed using fields that have been listed in "QueryEntity" been of the config!
SqlFieldsQuery query = SqlFieldsQuery("SELECT name, population FROM country ORDER BY population DESC LIMIT 10");

QueryFieldsCursor cursor = countryCache.Query(query);
while (cursor.HasNext())
{
    QueryFieldsRow row = cursor.GetNext();
    std::string name = row.GetNext<std::string>();
    std::string population = row.GetNext<std::string>();
    std::cout << "    >>> " << population << " people live in " << name << std::endl;
}

See the SQL API page for more information on how to use SQL queries.

Running Compute Tasks

In the example above, where we updated the population in the Amsterdam record, the data was fetched from the server node. With affinity colocation, you can run custom code on the node at which a specific key is located without pulling the data to the client node.

In the next example, we update the Amsterdam record directly on the server node. Note that you need to specify the country code as the value of the affinity key in the second argument of the affinityRun() method.

ignite.compute().affinityRun(CITY_CACHE_NAME, "NLD", new IgniteRunnable() {

    @IgniteInstanceResource
    private Ignite ignite;

    @Override
    public void run() {

        IgniteCache<BinaryObject, BinaryObject> cityCache = ignite.cache(CITY_CACHE_NAME).withKeepBinary();
        //building the key for Amsterdam
        BinaryObject key = ignite.binary().builder("demo.model.CityKey").setField("ID", 5)
                .setField("COUNTRYCODE", "NLD").build();

        BinaryObject city = cityCache.localPeek(key);

        city = city.toBuilder().setField("POPULATION", (int) city.field("POPULATION") - 10_000).build();
        cityCache.put(key, city);

        System.out.println(cityCache.localPeek(key));
    }
});

This API is not presently available for C++.

In this example, we access the data using binary objects. This means that the data is not de-serialized into objects of the City class (and, therefore, the class files are not required on the server node).

© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Nov 12, 2024