Adding SQL Support to an Existing Cache

classic Classic list List threaded Threaded
5 messages Options
anicn anicn
Reply | Threaded
Open this post in threaded view
|

Adding SQL Support to an Existing Cache

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(
    @QuerySqlField
    val name: String,
    @QuerySqlField
    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")
        .setIndexedTypes(String::class.java, Person::class.java)
    )

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(Person::class.java,
sqlString)
    sql.setArgs("Jon Snow")

    personCache.query(sql)

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?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Adding SQL Support to an Existing Cache

Hello!

- People not always have long-running clusters, but when they do, they need to create a new cache and migrate data in batches.
- Yes, there are considerations of making all caches SQL enabled. There's no reason why this would be impossible.
- No.

Regards,
--
Ilya Kasnacheev


вт, 4 июн. 2019 г. в 23:21, anicn <[hidden email]>:
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(
    @QuerySqlField
    val name: String,
    @QuerySqlField
    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")
        .setIndexedTypes(String::class.java, Person::class.java)
    )

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(Person::class.java,
sqlString)
    sql.setArgs("Jon Snow")

    personCache.query(sql)

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?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
anicn anicn
Reply | Threaded
Open this post in threaded view
|

Re: Adding SQL Support to an Existing Cache

I'm curious to hear more about what considerations there are. For example:
- Is any of the cache data duplicated in the table?
- Are there indexes created for the primary key on this table?
- Does the index incur a write performance penalty?

Thanks!



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Adding SQL Support to an Existing Cache

Hello!

- No.
- Yes.
- Yes.

Regards,
--
Ilya Kasnacheev


ср, 5 июн. 2019 г. в 19:20, anicn <[hidden email]>:
I'm curious to hear more about what considerations there are. For example:
- Is any of the cache data duplicated in the table?
- Are there indexes created for the primary key on this table?
- Does the index incur a write performance penalty?

Thanks!



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
dmagda dmagda
Reply | Threaded
Open this post in threaded view
|

Re: Adding SQL Support to an Existing Cache

In reply to this post by anicn
Please use DDL if you'd like to configure SQL dynamically:

This page shows how to use both SQL and key-value APIs:

-
Denis


On Wed, Jun 5, 2019 at 9:20 AM anicn <[hidden email]> wrote:
I'm curious to hear more about what considerations there are. For example:
- Is any of the cache data duplicated in the table?
- Are there indexes created for the primary key on this table?
- Does the index incur a write performance penalty?

Thanks!



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/