Schema and Indexes

❗️

This is a legacy Apache Ignite documentation

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

In addition to common DDL commands, Java developers can use special SQL APIs for schema and indexes definition:

Overview

If tables and indexes are configured using either annotation based or QueryEntities based approach, then the schema name they belong to will correspond to the name of the cache of their CacheConfiguration object. To change the schema name, you need to use the CacheConfiguration.setSqlSchema method.

At the same time, the schema name will be completely different if the tables and indexes are set up with Data Definition Language (DDL) commands. In this scenario, all tables and corresponding indexes will end up in the PUBLIC schema that is set by default.

In cases where tables are configured with all of the approaches mentioned above, make sure to set the correct schema name for the tables in SQL queries. For instance, assuming that 80% of the tables are configured with DDL, it makes sense to set PUBLIC schema for a query explicitly with SqlQuery.setSchema("PUBLIC") method:

IgniteCache cache = ignite.getOrCreateCache(
    new CacheConfiguration<>()
        .setName("Person")
        .setIndexedTypes(Long.class, Person.class));

// Creating City table.
cache.query(new SqlFieldsQuery("CREATE TABLE City " +
    "(id int primary key, name varchar, region varchar)").setSchema("PUBLIC")).getAll();

// Creating Organization table.
cache.query(new SqlFieldsQuery("CREATE TABLE Organization " +
    "(id int primary key, name varchar, cityName varchar)").setSchema("PUBLIC")).getAll();

// Joining data between City, Organizaion and Person tables. The latter
// was created with either annotations or QueryEntity approach.
SqlFieldsQuery qry = new SqlFieldsQuery("SELECT o.name from Organization o " +
    "inner join \"Person\".Person p on o.id = p.orgId " +
    "inner join City c on c.name = o.cityName " +
    "where p.age > 25 and c.region <> 'Texas'");

// Setting the query's default schema to PUBLIC.
// Table names from the query without the schema set will be
// resolved against PUBLIC schema.
// Person table belongs to "Person" schema (person cache) and this is why
// that schema name is set explicitly.
qry.setSchema("PUBLIC");

// Executing the query.
cache.query(qry).getAll();

Annotation Based Configuration

Indexes, as well as queryable fields, can be configured from code with the usage of @QuerySqlField annotation. As shown in the example below, desired fields should be marked with this annotation.

public class Person implements Serializable {
  /** Indexed field. Will be visible for SQL engine. */
  @QuerySqlField (index = true)
  private long id;
  
  /** Queryable field. Will be visible for SQL engine. */
  @QuerySqlField
  private String name;
  
  /** Will NOT be visible for SQL engine. */
  private int age;
  
  /**
   * Indexed field sorted in descending order. 
   * Will be visible for SQL engine.
   */
  @QuerySqlField(index = true, descending = true)
  private float salary;
}
case class Person (
  /** Indexed field. Will be visible for SQL engine. */
  @(QuerySqlField @field)(index = true) id: Long,

  /** Queryable field. Will be visible for SQL engine. */
  @(QuerySqlField @field) name: String,
  
  /** Will NOT be visisble for SQL engine. */
  age: Int
  
  /**
   * Indexed field sorted in descending order. 
   * Will be visible for SQL engine.
   */
  @(QuerySqlField @field)(index = true, descending = true) salary: Float
) extends Serializable {
  ...
}

A type name is used as a table name in SQL queries. In this case, our table name will be Person (schema name usage and definition is explained in the previous section).

Both id and salary are indexed fields. id field will be sorted in the ascending order (default) while salary in the descending order.

If you don't want to index a field but still need to use it in a SQL query, then the field has to be annotated as well omitting the index = true parameter. Such a field is called as a queryable field. As an example, name is defined as a queryable field above.

Finally, age is neither queryable nor indexed field and won't be accessible from SQL queries in Apache Ignite.

Now you can execute the SQL query as follows:

SqlFieldsQuery qry = new SqlFieldsQuery("SELECT id, name FROM Person" +
		"WHERE id > 1500 LIMIT 10");

👍

Updating Indexes and Queryable Fields at Runtime

Use ALTER TABLE, CREATE/DROP INDEX commands if it's needed to manage indexes or make new fields of the object visible to SQL engine at​ runtime.

Indexing Nested Objects

Fields of nested objects can also be indexed and queried using annotations. For example, consider Person object that has Address object as a field:

public class Person {
  /** Indexed field. Will be visible for SQL engine. */
  @QuerySqlField(index = true)
  private long id;

  /** Queryable field. Will be visible for SQL engine. */
  @QuerySqlField
  private String name;
  
  /** Will NOT be visible for SQL engine. */
  private int age;
  
  /** Indexed field. Will be visible for SQL engine. */
  @QuerySqlField(index = true)
  private Address address;
}

Where the structure of the Address class may look like:

public class Address {
  /** Indexed field. Will be visible for SQL engine. */
  @QuerySqlField (index = true)
  private String street;

  /** Indexed field. Will be visible for SQL engine. */
  @QuerySqlField(index = true)
  private int zip;
}

In the above example, @QuerySqlField(index = true) annotation is specified on all the fields of the Address class, as well as the Address object in the Person class.

This allows executing SQL queries like the following:

QueryCursor<List<?>> cursor = personCache.query(new SqlFieldsQuery(
  "select * from Person where street = 'street1'"));

Note that you do not need to specify address.street in the where clause of the SQL query. This is because the fields of the Address class are flattened within the Person table which simply allows us to access the Address fields in the queries directly.

📘

Scala Annotations

In Scala classes, the @QuerySqlField annotation must be accompanied by the @field annotation in order for a field to be visible for Ignite, like so: @(QuerySqlField @field).

Alternatively, you can also use the @ScalarCacheQuerySqlField annotation from the ignite-scalar module which is just a type alias for the @field annotation.

Registering Indexed Types

After indexed and queryable fields are defined, they have to be registered in the SQL engine along with the object types they belong to.

To tell Ignite which types should be indexed, key-value pairs can be passed into CacheConfiguration.setIndexedTypes method as it's shown in the example below.

// Preparing configuration.
CacheConfiguration<Long, Person> ccfg = new CacheConfiguration<>();

// Registering indexed type.
ccfg.setIndexedTypes(Long.class, Person.class);

Note that this method accepts only pairs of types - one for key class and another for value class. Primitives are passed as boxed types.

📘

Predefined Fields

In addition to all the fields marked with @QuerySqlField annotation, each table will have two special predefined fields: _key and _val, which represent links to whole key and value objects. This is useful, for instance, when one of them is of a primitive type and you want to filter out by its value. To do this, execute a query like SELECT * FROM Person WHERE _key = 100.

📘

Since Ignite supports Binary Marshaller, there is no need to add classes of indexed types to the classpath of cluster nodes. SQL query engine is able to pick up values of indexed and queryable fields avoiding object deserialization.

Group Indexes

To set up a multi-field index that will allow accelerating queries with complex conditions, you can use @QuerySqlField.Group annotation. It is possible to put multiple @QuerySqlField.Group annotations into orderedGroups if you want a field to be a part of more than one group.

For instance, in the Person class below we have field age which belongs to an indexed group named age_salary_idx with group order 0 and descending sort order. Also, in the same group, we have field salary with group order 3 and ascending sort order. Furthermore, field salary itself is a single column index (there is index = true parameter specified in addition to orderedGroups declaration). Group order does not have to be a particular number. It is needed just to sort fields inside of a particular group.

public class Person implements Serializable {
  /** Indexed in a group index with "salary". */
  @QuerySqlField(orderedGroups={@QuerySqlField.Group(
    name = "age_salary_idx", order = 0, descending = true)})
  private int age;

  /** Indexed separately and in a group index with "age". */
  @QuerySqlField(index = true, orderedGroups={@QuerySqlField.Group(
    name = "age_salary_idx", order = 3)})
  private double salary;
}

🚧

Note that annotating a field with @QuerySqlField.Group outside of @QuerySqlField(orderedGroups={...}) will have no effect.

QueryEntity Based Configuration

Indexes and queryable fields can also be configured via the org.apache.ignite.cache.QueryEntity class which is convenient for Spring XML based configuration.

All concepts that are discussed as part of the annotation based configuration above are also valid for the QueryEntity based approach. Furthermore, types whose fields are configured with the @QuerySqlField annotation and are registered with the CacheConfiguration.setIndexedTypes method are internally turned into query entities.

The example below shows to define a single field index, group indexes, as well as queryable fields.

<bean class="org.apache.ignite.configuration.CacheConfiguration">
    <property name="name" value="mycache"/>
    <!-- Configure query entities -->
    <property name="queryEntities">
        <list>
            <bean class="org.apache.ignite.cache.QueryEntity">
                <!-- Setting indexed type's key class -->
                <property name="keyType" value="java.lang.Long"/>
              
                <!-- Key field name to be used in INSERT and SELECT queries -->
                <property name="keyFieldName" value="id"/>
              
                <!-- Setting indexed type's value class -->
                <property name="valueType"
                          value="org.apache.ignite.examples.Person"/>

                <!-- Defining fields that will be either indexed or queryable.
                Indexed fields are added to 'indexes' list below.-->
                <property name="fields">
                    <map>
                        <entry key="id" value="java.lang.Long"/>
                        <entry key="name" value="java.lang.String"/>
                        <entry key="salary" value="java.lang.Long "/>
                    </map>
                </property>

                <!-- Defining indexed fields.-->
                <property name="indexes">
                    <list>
                        <!-- Single field (aka. column) index -->
                        <bean class="org.apache.ignite.cache.QueryIndex">
                            <constructor-arg value="name"/>
                        </bean>
                      
                        <!-- Group index. -->
                        <bean class="org.apache.ignite.cache.QueryIndex">
                            <constructor-arg>
                                <list>
                                    <value>id</value>
                                    <value>salary</value>
                                </list>
                            </constructor-arg>
                            <constructor-arg value="SORTED"/>
                        </bean>
                    </list>
                </property>
            </bean>
        </list>
    </property>
</bean>

A short name of the valueType is used as a table name in SQL queries. In this case, our table name will be Person (schema name usage and definition is explained in overview section).

Once the QueryEntity is defined, you can execute the SQL query as follows:

SqlFieldsQuery qry = new SqlFieldsQuery("SELECT id, name FROM Person" +
		"WHERE id > 1500 LIMIT 10");

👍

Updating Indexes and Queryable Fields at Runtime

Use ALTER TABLE, CREATE/DROP INDEX commands if it's needed to manage indexes or make new fields of the object visible to SQL engine at​ runtime.

Custom Keys

If you use only predefined SQL data types for primary keys, then there is no need to perform additional manipulation with the SQL schema configuration. Those data types are defined by GridQueryProcessor.SQL_TYPES constant, as listed below.

📘

Predefined SQL Data Types

  • all the primitives and their wrappers except char and Character.
  • String.
  • BigDecimal.
  • byte[].
  • java.util.Date, java.sql.Date, java.sql.Timestamp.
  • java.util.UUID.

However, once you decide to introduce a custom complex key and refer to its fields from DML statements, you have to:

  • Define those fields in the QueryEntity the same way as you set fields for the value object.
  • Use the new configuration parameter QueryEntity.setKeyFields(..) to distinguish key fields from value fields.

The example below shows how to achieve this.

// Preparing cache configuration.
CacheConfiguration cacheCfg = new CacheConfiguration<>("personCache");

// Creating the query entity. 
QueryEntity entity = new QueryEntity("CustomKey", "Person");

// Listing all the queryable fields.
LinkedHashMap<String, String> flds = new LinkedHashMap<>();

flds.put("intKeyField", Integer.class.getName());
flds.put("strKeyField", String.class.getName());

flds.put("firstName", String.class.getName());
flds.put("lastName", String.class.getName());

entity.setFields(flds);

// Listing a subset of the fields that belong to the key.
Set<String> keyFlds = new HashSet<>();

keyFlds.add("intKeyField");
keyFlds.add("strKeyField");

entity.setKeyFields(keyFlds);

// End of new settings, nothing else here is DML related

entity.setIndexes(Collections.<QueryIndex>emptyList());

cacheCfg.setQueryEntities(Collections.singletonList(entity));

ignite.createCache(cacheCfg);
<bean class="org.apache.ignite.configuration.CacheConfiguration">
    <property name="name" value="personCache"/>
    <!-- Configure query entities -->
    <property name="queryEntities">
        <list>
            <bean class="org.apache.ignite.cache.QueryEntity">
                <!-- Registering key's class. -->
                <property name="keyType" value="CustomKey"/>
              
                <!-- Registering value's class. -->
                <property name="valueType"
                          value="org.apache.ignite.examples.Person"/>

                <!-- 
                    Defining all the fields that will be accessible from DML.
                -->
                <property name="fields">
                    <map>
                        <entry key="firstName" value="java.lang.String"/>
                        <entry key="lastName" value="java.lang.String"/>
                      	<entry key="intKeyField" value="java.lang.Integer"/>
                      	<entry key="strKeyField" value="java.lang.String"/>
                    </map>
                </property>
              
                <!-- Defining the subset of key's fields -->
                <property name="keyFields">
                    <set>
                      	<value>intKeyField<value/>
                      	<value>strKeyField<value/>
                    </set>
                </property>
            </bean>
        </list>
    </property>
</bean>

👍

Automatic Hash Code Calculation and Equals Implementation

If a custom key can be serialized into a binary form, then Ignite will calculate its hash code and implement equals method automatically.

However, if the key is of Externalizable type, that cannot be serialized into the binary form, then you are required to implement the hashCode and equals methods manually. See the binary marshaller page for more details.

Geospatial Queries

The geospatial module is usable only for the objects of com.vividsolutions.jts type.

To configure indexes and/or queryable​ fields of geometric types, you need to use the same approached that is used for non-geospatial types. First, the indexes can be defined with the help of org.apache.ignite.cache.QueryEntity which is convenient for Spring XML based configurations. Second, you can achieve the same outcome by annotating indexes with @QuerySqlField annotations which will be converted QueryEntities internally.

/**
 * Map point with indexed coordinates.
 */
private static class MapPoint {
    /** Coordinates. */
    @QuerySqlField(index = true)
    private Geometry coords;

    /**
     * @param coords Coordinates.
     */
    private MapPoint(Geometry coords) {
        this.coords = coords;
    }
}
<bean class="org.apache.ignite.configuration.CacheConfiguration">
    <property name="name" value="mycache"/>
    <!-- Configure query entities -->
    <property name="queryEntities">
        <list>
            <bean class="org.apache.ignite.cache.QueryEntity">
                <property name="keyType" value="java.lang.Integer"/>
                <property name="valueType" value="org.apache.ignite.examples.MapPoint"/>

                <property name="fields">
                    <map>
                        <entry key="coords" value="com.vividsolutions.jts.geom.Geometry"/>
                    </map>
                </property>

                <property name="indexes">
                    <list>
                        <bean class="org.apache.ignite.cache.QueryIndex">
                            <constructor-arg value="coords"/>
                        </bean>
                    </list>
                </property>
            </bean>
        </list>
    </property>
</bean>

After the fields of geometry types are defined using on of the methods above, it's time to execute queries using the values stored in those fields.

// Query to find points that fit into a polygon.
SqlQuery<Integer, MapPoint> query = new SqlQuery<>(MapPoint.class, "coords && ?");

// Defining the polygon's boundaries.
query.setArgs("POLYGON((0 0, 0 99, 400 500, 300 0, 0 0))");

// Executing the query.
Collection<Cache.Entry<Integer, MapPoint>> entries = cache.query(query).getAll();

// Printing number of points that fit into the area defined by the polygon.
System.out.println("Fetched points [" + entries.size() + ']');

👍

Complete Example

A ready-to-be-run example that demonstrates the usage of geospatial queries in Ignite can be found here.