GridGain Developers Hub
GitHub logo GridGain iso GridGain.com
GridGain Software Documentation

Defining Indexes

In addition to common DDL commands, such as CREATE/DROP INDEX, developers can use GridGain’s SQL APIs for index definition.

Configuring Indexes Using Annotations

Indexes, as well as queryable fields, can be configured from code via the @QuerySqlField annotation. In the example below, the GridGain SQL engine will create indexes for the id and salary fields.

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

  /** Queryable field. Will be visible to the SQL engine. */
  @QuerySqlField
  private String name;

  /** Will NOT be visible to the SQL engine. */
  private int age;

  /**
   * Indexed field sorted in descending order.
   * Will be visible to the SQL engine.
   */
  @QuerySqlField(index = true, descending = true)
  private float salary;
}
// Indexed field. Will be visible to the SQL engine.
[QuerySqlField(IsIndexed = true)] public long Id;

//Queryable field. Will be visible to the SQL engine
[QuerySqlField] public string Name;

//Will NOT be visible to the SQL engine.
public int Age;
/**
  * Indexed field sorted in descending order.
  * Will be visible to the SQL engine.
*/
[QuerySqlField(IsIndexed = true, IsDescending = true)]
public 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 Schemas section).

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

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

Finally, age is neither queryable nor is it an indexed field, and thus it will not be accessible from SQL queries in GridGain.

Now you can execute the SQL query as follows:

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

Indexing Nested Objects

Fields of nested objects can also be indexed and queried using annotations. For example, consider a Person object that has an 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 might 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, the @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 makes it possible to execute 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.

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 specify which types should be indexed, key-value pairs can be passed into the CacheConfiguration.setIndexedTypes() method as shown in the example below.

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

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

This method accepts only pairs of types: one for key class and another for value class. Primitives are passed as boxed types.

Group Indexes

To set up a multi-field index that can accelerate queries with complex conditions, you can use a @QuerySqlField.Group annotation. You can add multiple @QuerySqlField.Group annotations in orderedGroups if you want a field to be a part of more than one group.

For instance, in the Person class below we have the field age which belongs to an indexed group named age_salary_idx with a group order of "0" and descending sort order. Also, in the same group, we have the field salary with a group order of "3" and ascending sort order. Furthermore, the field salary itself is a single column index (the index = true parameter is specified in addition to the orderedGroups declaration). Group order does not have to be a particular number. It is needed only 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;
}
class Person
{
    [QuerySqlField(IndexGroups = new[] {"age_salary_idx"})]
    public int Age;

    [QuerySqlField(IsIndexed = true, IndexGroups = new[] {"age_salary_idx"})]
    public double Salary;
}

Configuring Indexes Using Query Entities

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, the types whose fields are configured with the @QuerySqlField annotation and are registered with the CacheConfiguration.setIndexedTypes() method are internally converted into query entities.

The example below shows how to define a single field index, group indexes, and 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>
var cacheCfg = new CacheConfiguration
{
    Name = "myCache",
    QueryEntities = new[]
    {
        new QueryEntity
        {
            KeyType = typeof(long),
            KeyFieldName = "id",
            ValueType = typeof(dotnet_helloworld.Person),
            Fields = new[]
            {
                new QueryField
                {
                    Name = "id",
                    FieldType = typeof(long)
                },
                new QueryField
                {
                    Name = "name",
                    FieldType = typeof(string)
                },
                new QueryField
                {
                    Name = "salary",
                    FieldType = typeof(long)
                },
            },
            Indexes = new[]
            {
                new QueryIndex("name"),
                new QueryIndex(false, QueryIndexType.Sorted, new[] {"id", "salary"})
            }
        }
    }
};
Ignition.Start(new IgniteConfiguration
{
    CacheConfiguration = new[] {cacheCfg}
});

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 on the Schemas page).

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

Custom Keys

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

Predefined SQL data types include:

  • 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 need 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 do 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>
Unsupported