❗️

This is a legacy Apache Ignite documentation

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

Ignite lets you use both SQL and the key-value API to access cached data even if tables/caches were created and preloaded with SQL. It gives you the freedom to use one or both methods according to your application requirements.

In this tutorial, we will demonstrate how to query the cluster using both SQL and the key-value APIs using a sample project available on GitHub: https://github.com/dmagda/ignite_world_demo.

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

• Access the loaded data using SQL queries.
• Access the loaded data using key-value operations.
• Process 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 name of the cache and corresponding keys are generated, see CREATE TABLE.

Using Key-Value API

The IgniteCache interface provides a number of methods that can be used to access the cache via 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));
}

You can also use BinaryObjects to access the cached data. The benefit of using binary objects is that you avoid deserialization which is important if an object needs to be accessed from an Ignite server which might not have the ​object's class representation. See Binary Marshaller for more details.

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);
  	
}

Executing SQL Queries

In this example, we execute SQL queries using an SqlFieldsQuery object and iterate over 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));
    }
}

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

Running Compute Tasks

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

🚧

To use affinity collocation with custom classes which are not available in the classpath of the server nodes, set the IgniteConfiguration.peerClassLoadingEnabled parameter to true.

In the following 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));
    }
});

In this example, we access the data using BinaryObjects. It 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).

📘

You can also get the cache without keeping the binary format and operate with objects of the City class. In this case, the value class must be available in the classpath of the server nodes.