You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by anicn <na...@invidi.com> on 2019/06/04 20:20:57 UTC
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/
Re: Adding SQL Support to an Existing Cache
Posted by Denis Magda <dm...@apache.org>.
Please use DDL if you'd like to configure SQL dynamically:
https://apacheignite-sql.readme.io/docs/ddl
This page shows how to use both SQL and key-value APIs:
https://apacheignite-sql.readme.io/docs/sql-and-key-value-usage
-
Denis
On Wed, Jun 5, 2019 at 9:20 AM anicn <na...@invidi.com> 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/
>
Re: Adding SQL Support to an Existing Cache
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
- No.
- Yes.
- Yes.
Regards,
--
Ilya Kasnacheev
ср, 5 июн. 2019 г. в 19:20, anicn <na...@invidi.com>:
> 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/
>
Re: Adding SQL Support to an Existing Cache
Posted by anicn <na...@invidi.com>.
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/
Re: Adding SQL Support to an Existing Cache
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <na...@invidi.com>:
> 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/
>