Complex data types

Complex data types can be indexed, searched and sorted. Supported complex data types:

• Complex Java types
• Collections
• Maps
• Ignite Cache Entry (_KEY, _VAL)

The best way to show you how it works is with some examples.

You can find samples source code at Hawkore's Apache Ignite extensions sample project.

Let's create a sample Profile table indexed with Advanced Lucene Index using Java Annotations Based Configuration.

Profile Entity

public class Profile {

    @QuerySqlField(index=true)
    private String login;

    @QuerySqlField
    private String firstName;

    @QuerySqlField
    private String lastName ;

    /**
     * Simple types can be indexed inside collections
     */
    @QueryTextField(stringMappers=@StringMapper(case_sensitive=false))
    @QuerySqlField
    private ArrayList<String> cities;

    /**
     * Simple types can be indexed inside maps
     */
    @QueryTextField(
        stringMappers={
                // below  mapper is equivalent to @StringMapper(name="textAddresses", case_sensitive=true)
                // as if name is not provided will takes annotated property name
                @StringMapper(case_sensitive=true),
                @StringMapper(name="textAddresses._key", case_sensitive=true),
                @StringMapper(name="textAddresses._value", case_sensitive=false)
                }
    )
    @QuerySqlField
    private HashMap<String, String> textAddresses;

    /**
     * Complex java type's components can be indexed
     * 
     * <p>
     * NOTE that if components within Address were not annotated with {@code @QueryTextField} you need to
     * define index here as follows:
     * 
     * <pre>
     * {@code @QueryTextField}(
     *   stringMappers={
     *           {@code @StringMapper}(name="address.city", case_sensitive=false)
     *           },
     *   integerMappers = {@code @IntegerMapper}(name="address.zip")
     * )
     * </pre>
     */
    @QueryTextField
    @QuerySqlField (hidden=true)
    private Address address;

   /**
    * Complex java type's components can be indexed even while being inside maps
    * 
    * <p>
    * NOTE that components within Address do not require to be annotated with {@code @QueryTextField}
    * as index MUST be defined here
    */
    @QueryTextField(
        stringMappers={
                @StringMapper(name="addresses._key", case_sensitive=true),
                @StringMapper(name="addresses.city", case_sensitive=false)
                },
        integerMappers = @IntegerMapper(name="addresses.zip")
    )
    @QuerySqlField
    private HashMap<String, Address> addresses;

    /**
     * Complex java type's components can be indexed even while being inside collections
     * 
     * <p>
     * NOTE that components within Address do not require to be annotated with {@code @QueryTextField}
     * as index MUST be defined here
     * 
     */
    @QueryTextField(
        stringMappers={
                @StringMapper(name="addressList.city", case_sensitive=false)
                },
        integerMappers = @IntegerMapper(name="addressList.zip")
    )
    @QuerySqlField
    private ArrayList<Address> addressList;

  [...]  
 }

Address Entity

/**
 * Address
 *
 * <p>
 *  
 * NOTE that below {@code @QueryTextField} annotations will be used for complex
 * java type indexing of Address as a direct member of Profile (out of map and collections).
 * 
 * <p>
 * 
 * They are not required if you define index within Profile.
 *
 * @author Manuel Núñez ([email protected])
 * 
 */
public class Address {

    @QuerySqlField
    private String street;

    @QueryTextField(stringMappers = @StringMapper(case_sensitive = false))
    @QuerySqlField
    private String city;

    @QueryTextField(integerMappers = @IntegerMapper)
    @QuerySqlField
    private int zip;

    [...]    
}

Profiles Cache configuration

<bean id="profilesCache" class="org.apache.ignite.configuration.CacheConfiguration">
    <property name="name" value="profiles" />
    <property name="rebalanceMode" value="ASYNC"/> 
    <property name="cacheMode" value="PARTITIONED" />
    <property name="indexedTypes">
        <array>
            <value>java.lang.String</value>
            <value>com.hawkore.ignite.examples.entities.profiles.Profile</value>
        </array>
    </property> 
</bean>

Complex Java Types

The components of Complex Types can be indexed, searched and sorted this way:

SQL Sample 1

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
ADDRESS_CITY,
ADDRESS_ZIP,
ADDRESS_STREET
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "address.city",
      value: "Chicago"
   }
}';

SQL Sample 2

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
ADDRESS_CITY,
ADDRESS_ZIP,
ADDRESS_STREET
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "address.zip",
      value: 60601
   }
}';

SQL Sample 3

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
ADDRESS_CITY,
ADDRESS_ZIP,
ADDRESS_STREET
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   sort: {
      field: "address.zip",
      reverse: true
   }
}';

Collections

Any java.util.Collection non-abstract sub type can be indexed.

Lists and sets are indexed in the same way as regular columns, using their base type:

Searches are also done in the same way as with regular columns:

SQL Sample 1

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
CITIES
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "cities",
      value: "London"
   }
}';

SQL Sample 2

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
CITIES
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "cities",
      value: "OtherCity_20"
   }
}';

Complex types can be searched even while being inside collections:

SQL Sample 1

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
addressList
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "addressList.city",
      value: "Chicago"
   }
}';

SQL Sample 2

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
addressList
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "addressList.zip",
      value: 60601
   }
}';

Maps

Any java.util.Map non-abstract sub type can be indexed.

Map keys and values can also be indexed by adding the suffixes ._key or ._value to the mapped column name.

Please don't use map keys containing the separator chars, which are . and $.

Maps values are indexed by default using their keys as field name suffixes. For searching map values under a certain key you should use $ as field-key separator (fieldName$keyValue):

SQL Sample 1

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
textAddresses
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "textAddresses$London",
      value: "Camden Road #12"
   }
}';

SQL Sample 2

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
textAddresses
FROM "profiles".profile WHERE lucene = '{
       filter: {
          type: "match",
          field: "textAddresses._key",
          value: "OtherCity_17"
       }
}';

SQL Sample 3

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
textAddresses
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "textAddresses._value",
      value: "Paseo de la Castellana #50"
   }
}';

Complex types can be searched even while being inside maps:

SQL Sample 1

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
addresses
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "addresses.city$Illinois",
      value: "Chicago"
   }
}';

SQL Sample 2

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
addresses
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "addresses.zip$Illinois",
      value: 60601
   }
}';

SQL Sample 3

SELECT 
LOGIN, 
FIRSTNAME, 
LASTNAME,
addresses
FROM "profiles".profile WHERE lucene = '{
   refresh: true,
   filter: {
      type: "match",
      field: "addresses._key",
      value: "OtherState_17"
   }
}';

Ignite Cache Entry

If Ignite Cache Entry is composed by simple types, you can index and search _KEY and _VAL using Advanced Lucene index, where _KEY is the Cache Entry's key and _VAL is the Cache Entry's value.

QueryEntity configuration

<!-- QueryEntity Based Configuration for String valueType. As you know this could be do it programmatically :) -->  
    <bean id="stringCache" class="org.apache.ignite.configuration.CacheConfiguration">
        <property name="name" value="str"/>

        <!-- Configure query entities -->
        <property name="queryEntities">
            <list>
                <bean class="org.apache.ignite.cache.QueryEntity">

                    <property name="tableName" value="TABLE"/>

                     <!-- Setting indexed type's key SIMPLE class -->
                    <property name="keyType" 
                             value="java.lang.Integer"/>

                     <!-- Setting indexed type's value SIMPLE class -->
                    <property name="valueType"
                              value="java.lang.String"/>                          

                    <!-- Defining indexed fields.-->
                    <property name="indexes">
                        <list>
                            <!-- Defining lucene index on _key and _val (key and value cache entry).-->
                            <bean class="org.apache.ignite.cache.QueryIndex">
                                <constructor-arg>
                                    <list>
                                        <value>LUCENE</value>
                                    </list>
                                </constructor-arg>
                                <constructor-arg value="FULLTEXT"/>

                                <!-- Defining advanced lucene index configuration.-->   
                                <property name="luceneIndexOptions">
                                    <value><![CDATA[{
                                    'version':'0',
                                    'refresh_seconds':'60',
                                    'directory_path':'',
                                    'ram_buffer_mb':'10',
                                    'max_cached_mb':'-1',
                                    'optimizer_enabled':'true',
                                    'optimizer_schedule':'0 1 * * *',
                                    'schema':'{
                                               "fields":{
                                                    "_KEY":{"type":"integer","validated":false},
                                                    "_VAL":{"type":"text","validated":false,"analyzer":"standard"}
                                                    }
                                              }'
                                     }]]></value>
                                </property>                             
                            </bean>
                        </list>
                    </property>                 
                </bean>
            </list>
        </property>
    </bean>

SQL query samples

-- Lucene index search on cache entry with simple types

insert into "str".TABLE (_key,_val) values (1, 'a very large text one');
insert into "str".TABLE (_key,_val) values (2, 'a very large text two');
insert into "str".TABLE (_key,_val) values (3, 'a very large text three');
insert into "str".TABLE (_key,_val) values (4, 'a very large text four');


-- Refresh index
SELECT * FROM "str".TABLE
WHERE lucene = '{ refresh : true}';


-- Phrase query on _VAL cache entry
SELECT * FROM "str".TABLE
WHERE lucene = '{refresh : true, query : {
                            type   : "phrase",
                            field  : "_VAL",
                            value : "three" }}';

-- Match query on _KEY cache entry
SELECT * FROM "str".TABLE
WHERE lucene = '{refresh : true, query : {
                            type   : "match",
                            field  : "_KEY",
                            value : 2 }}';

Summary

Using Advanced Lucene indexing on collections and maps may produce surprising results.

Let's create a sample Blog table indexed with Advanced Lucene Index using Java Annotations Based Configuration.

BlogEntry Entity

public class BlogEntry {

    @QuerySqlField
    @QueryTextField(textMappers=@TextMapper(analyzer="english"))
    private String comment;

    @QuerySqlField
    @QueryTextField(stringMappers=@StringMapper(case_sensitive=false))
    private ArrayList<String> tags;

    [...]
}

BlogEntryKey

public class BlogEntryKey {

    @QueryTextField(longMappers = @LongMapper)
    @QuerySqlField(index = true)
    private long id;  

    @QueryTextField(stringMappers = @StringMapper)
    @QuerySqlField(index = true)
    @AffinityKeyMapped
    private String author;

    [...]
}

Blog Cache configuration

<bean id="blogCache" class="org.apache.ignite.configuration.CacheConfiguration">
    <property name="name" value="blog" />
    <property name="rebalanceMode" value="ASYNC"/> 
    <property name="cacheMode" value="PARTITIONED" />
    <property name="indexedTypes">
        <array>
            <value>com.hawkore.ignite.examples.entities.blog.BlogEntryKey</value>
            <value>com.hawkore.ignite.examples.entities.blog.BlogEntry</value>
        </array>
    </property> 
</bean> 

Let's insert some data:

-- insert blog some entries
INSERT INTO "blog".blogEntry(id, author, comment, tags) VALUES(1, 'Jose', 'Apache Ignite is awesome', public.hkjson_to_list('["Apache Ignite"]'));
INSERT INTO "blog".blogEntry(id, author, comment, tags) VALUES(2, 'Manuel', 'Apache Ignite''s web console app is great', public.hkjson_to_list('["Apache Ignite", "management"]'));
INSERT INTO "blog".blogEntry(id, author, comment, tags) VALUES(3, 'Jorge', 'New Apache Ignite 2.7.0 has been released', public.hkjson_to_list('["Apache Ignite", "development"]'));
INSERT INTO "blog".blogEntry(id, author, comment, tags) VALUES(4, 'Arturo', 'Is there any embedded Apache Ignite service for tests?', public.hkjson_to_list('["Apache Ignite", "testing"]'));
INSERT INTO "blog".blogEntry(id, author, comment, tags) VALUES(5, 'Manuel', 'Apache Ignite''s SQL distributed database is a surprising feature.', public.hkjson_to_list('["Apache Ignite", "management", "development", "testing"]'));

Search for blog entries that contains "Apache Ignite" on tags:

SQL

SELECT * FROM "blog".blogEntry WHERE lucene = '{
   refresh: true,
   filter: {
      type: "boolean",
      must: [
         {type: "match", field: "tags", value: "Apache Ignite"}
      ]
   }
}';

Results

+----+--------+-------------------------------------------------------------------+---------------------------------------------------+
| ID | AUTHOR |                                                           COMMENT |                                              TAGS |
+====+========+===================================================================+===================================================+
|  1 |   Jose |                                          Apache Ignite is awesome |                                   [Apache Ignite] |
+----+--------+-------------------------------------------------------------------+---------------------------------------------------+
|  4 |   Jose |            Is there any embedded Apache Ignite service for tests? |                          [Apache Ignite, testing] |
+----+--------+-------------------------------------------------------------------+---------------------------------------------------+
|  3 |  Jorge |                         New Apache Ignite 2.7.0 has been released |                      [Apache Ignite, development] |
+----+--------+-------------------------------------------------------------------+---------------------------------------------------+
|  2 | Manuel |                          Apache Ignite's web console app is great |                       [Apache Ignite, management] |
+----+--------+-------------------------------------------------------------------+---------------------------------------------------+
|  5 | Manuel | Apache Ignite's SQL distributed database is a surprising feature. | [Apache Ignite, management, development, testing] |
+----+--------+-------------------------------------------------------------------+---------------------------------------------------+

Search for blog entries that contains "Apache Ignite" and "management" on tags:

SQL

SELECT * FROM "blog".blogEntry WHERE lucene = '{
   refresh: true,
   filter: {
      type: "boolean",
      must: [
         {type: "match", field: "tags", value: "Apache Ignite"},
         {type: "match", field: "tags", value: "management"}
      ]
   }
}';

Results

+----+--------+-------------------------------------------------------------------+---------------------------------------------------+
| ID | AUTHOR |                                                           COMMENT |                                              TAGS |
+====+========+===================================================================+===================================================+
|  2 | Manuel |                          Apache Ignite's web console app is great |                       [Apache Ignite, management] |
+----+--------+-------------------------------------------------------------------+---------------------------------------------------+
|  5 | Manuel | Apache Ignite's SQL distributed database is a surprising feature. | [Apache Ignite, management, development, testing] |
+----+--------+-------------------------------------------------------------------+---------------------------------------------------+