GridGain Community Edition

Expand all | Collapse all

Adding SQL Support to an Existing Cache

  • 1.  Adding SQL Support to an Existing Cache

    Posted 06-14-2019 03:03 PM
    I have a cache called Person which initially was created like so:

    personCache = igniteClient.getOrCreateCache<String, Person>("Person")

    and a Person is defined as:

    data class Person(
        val name: String,
        val age: Int,
        val city: String

    I now want to be able to run SQL queries on this cache and based on the
    Ignite docs have added some setup code to allow for this. Basically what I
    did was I added the annotations on the fields of the Person class which I
    would like to run queries on like so:
    data class Person(
        val name: String,
        val age: Int,
        val city: String
    and in addition to this I have created a CacheConfiguration which gets
    passed to my cache which sets the indexedTypes that match my Person cache
    like so:
    personCache = igniteClient.getOrCreateCache<String, Person>(
        CacheConfiguration<String, Person>("Person")

    The problem that I run into once doing all of this is upon the first time
    that I try to run a SQL query, like for example:

        val sqlString = "name=?"
        val sql: SqlQuery<String, Person> = SqlQuery(,
        sql.setArgs("Jon Snow")


    I get Ignite SQL exceptions that the table Person does not exist.

    Now after some research into this problem, it seems that if I destroy the
    cache first once it goes to create the Person cache again Ignite will now
    also create the Person table and the queries will now work.

    So after all of this my question is, is there any other way that I could add
    SQL support to an existing Cache that wouldn't require me to destroy the
    existing cache and lose all the data that is currently in it?

    Follow up questions:
    - If the answer to the above is no that you cannot add this support without
    destroying the cache, how do people deal with this and not lose the data
    that is in their caches?
    - If there is no way around this data loss, is there any downside then to
    just making every cache SQL enabled immediately even if you don't know that
    you will use it (i.e. memory or performance costs)?
    - Are there any DDL queries possible that would allow me to create the table
    for the existing cache with no data loss?

    Steve Bremmer
    Software engineer

  • 2.  RE: Adding SQL Support to an Existing Cache

    Posted 06-17-2019 01:28 PM
    Hi DBA Guy,

    I suggest using Data Definition Language (DDL) if you'd like to configure SQL dynamically. More info here:

    Also, this page shows how to use both SQL and key-value APIs:

    Hope that helps.