Run sql query on key-value cache

classic Classic list List threaded Threaded
10 messages Options
rakshita04 rakshita04
Reply | Threaded
Open this post in threaded view
|

Run sql query on key-value cache

Hi Team,

We are using apache-ignite node for our C++ application.
For one of the scenarios we need to get the result based on value of the the
columns(which is not key field).
If we do a getAll() on DB node then we have to linearly search the whole
Database which is taking too much time in case of more records.
If we run sql select query on the column value on our key-value cache, then
will it be faster than linear search?
Do we need to use "indexing" for faster results?
if yes can we create index on our existing key-value cache? if yes how?

regards,
Rakshita



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

Re: Run sql query on key-value cache

Just using SQL doesn’t automatically make your query faster (though it might), but it does mean you can use things like indexes.

To use SQL on a cache, you need to define Query Entities. See the sample code for an example:


See example-query.xml for the definition of the query entities.

Regards,
Stephen

On 11 Mar 2021, at 12:33, rakshita04 <[hidden email]> wrote:

Hi Team,

We are using apache-ignite node for our C++ application.
For one of the scenarios we need to get the result based on value of the the
columns(which is not key field).
If we do a getAll() on DB node then we have to linearly search the whole
Database which is taking too much time in case of more records.
If we run sql select query on the column value on our key-value cache, then
will it be faster than linear search?
Do we need to use "indexing" for faster results?
if yes can we create index on our existing key-value cache? if yes how?

regards,
Rakshita



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


rakshita04 rakshita04
Reply | Threaded
Open this post in threaded view
|

Re: Run sql query on key-value cache

How can we create indexes on our existing <key,value> cache.
As far as i could see on your portal no API support is available for C++ for
creating indexes.
does Query Entity automatically takes care of creating indexes? or we need
to explicitly create indexes on our <key,value> cache?
if we need to explicitly create , can you please help us how to do that?

regards,
Rakshita Chaudhary



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

Re: Run sql query on key-value cache

Hello!

Once a cache is created, you can't add indexes. You will need to recreate cache / restart cluster with updated configuration.

You can define caches with Query Entities in spring XML configuration, pass it to IgniteConfguration instance. 

Regards,
--
Ilya Kasnacheev


пт, 12 мар. 2021 г. в 07:07, rakshita04 <[hidden email]>:
How can we create indexes on our existing <key,value> cache.
As far as i could see on your portal no API support is available for C++ for
creating indexes.
does Query Entity automatically takes care of creating indexes? or we need
to explicitly create indexes on our <key,value> cache?
if we need to explicitly create , can you please help us how to do that?

regards,
Rakshita Chaudhary



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

Re[2]: Run sql query on key-value cache

Ilya, seems you mistaken, check [1]
Seems there is no additional documentation, but API is simple , check example [2].
 
 

Hello!
 
Once a cache is created, you can't add indexes. You will need to recreate cache / restart cluster with updated configuration.
 
You can define caches with Query Entities in spring XML configuration, pass it to IgniteConfguration instance. 
 
Regards,
--
Ilya Kasnacheev
 
пт, 12 мар. 2021 г. в 07:07, rakshita04 <rakshita.chaudhary@...>:
How can we create indexes on our existing <key,value> cache.
As far as i could see on your portal no API support is available for C++ for
creating indexes.
does Query Entity automatically takes care of creating indexes? or we need
to explicitly create indexes on our <key,value> cache?
if we need to explicitly create , can you please help us how to do that?

regards,
Rakshita Chaudhary



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

Re: Re[2]: Run sql query on key-value cache

Hello!

Yes, it does indeed seem that you can now enable indexing on a cache by doing "CREATE TABLE ... WITH "cache_name=<existing cache>";

+ dev@

Do we have this documented anywhere? Updating CREATE TABLE docs and Schema and Indexing would be nice.

https://ignite.apache.org/docs/latest/SQL/indexes#creating-indexes-with-sql should also refer to CREATE TABLE btw.

Regards,
--
Ilya Kasnacheev


пт, 12 мар. 2021 г. в 13:40, Zhenya Stanilovsky <[hidden email]>:
Ilya, seems you mistaken, check [1]
Seems there is no additional documentation, but API is simple , check example [2].
 
 

Hello!
 
Once a cache is created, you can't add indexes. You will need to recreate cache / restart cluster with updated configuration.
 
You can define caches with Query Entities in spring XML configuration, pass it to IgniteConfguration instance. 
 
Regards,
--
Ilya Kasnacheev
 
пт, 12 мар. 2021 г. в 07:07, rakshita04 <rakshita.chaudhary@...>:
How can we create indexes on our existing <key,value> cache.
As far as i could see on your portal no API support is available for C++ for
creating indexes.
does Query Entity automatically takes care of creating indexes? or we need
to explicitly create indexes on our <key,value> cache?
if we need to explicitly create , can you please help us how to do that?

regards,
Rakshita Chaudhary



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

Re: Re[2]: Run sql query on key-value cache

Hi Team,

Thanks for the response.
If we create schema(table) with indexes using existing cache , will this
schema be created in memory?
in our existing xml config , we are using persistence for ignite node, will
apache use same persistence storage or create schema in-memory(RAM)?
Is there a way to create schema on persistent storage rather than in-memory?

regards,
Rakshita Chaudhary



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

Re[4]: Run sql query on key-value cache


schema for persistent cache will be stored into appropriate config.


 
Hi Team,

Thanks for the response.
If we create schema(table) with indexes using existing cache , will this
schema be created in memory?
in our existing xml config , we are using persistence for ignite node, will
apache use same persistence storage or create schema in-memory(RAM)?
Is there a way to create schema on persistent storage rather than in-memory?

regards,
Rakshita Chaudhary



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

Re: Re[4]: Run sql query on key-value cache

Hi team,

I believe the example you mentioned above is using C++ -
https://github.com/apache/ignite/blob/f37ec9eece4db627f2d5190e589f0522e445a251/modules/platforms/cpp/examples/query-example/src/query_example.cpp

is there any performance or memory benefit is we use odbc rather than C++
API?
We are using C++ put/get APIs to write/read data on cache.
Is it okay to use C++ APIs to perform sql query, similar way? or using odbc
client is more beneficial?



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

Re: Re[4]: Run sql query on key-value cache

There is our DDL documentation, which describes in detail how one can create
indexes on existing table [1]. It can be used with both C++ using SqlFieldsQuery
and ODBC.

Now regarding benefits of ODBC over C++ - there are definitely memory benefits
if you are comparing ODBC and thick C++ client as thick client starts JVM internally.
On the other hand if you compare thin C++ client and ODBC then I don't think there
are any significant differences in performance or memory consumption.

Using C++ you are getting a more convenient API (e.g. cache API, transactions API)
and ability to work with objects. Also when you use cache operations in C++ thin client
it may be faster than inserting a value with ODBC as C++ thin client is affinity aware
and in many cases will send request to primary node, which is not true for ODBC.

There are many other small or not so small differences between those 3 options, but
this is just too much of an information to describe it all in a single email. So just ask
if you are interested in something particular and we'll try to answer.


Best Regards,
Igor


On Tue, Mar 16, 2021 at 2:30 PM rakshita04 <[hidden email]> wrote:
Hi team,

I believe the example you mentioned above is using C++ -
https://github.com/apache/ignite/blob/f37ec9eece4db627f2d5190e589f0522e445a251/modules/platforms/cpp/examples/query-example/src/query_example.cpp

is there any performance or memory benefit is we use odbc rather than C++
API?
We are using C++ put/get APIs to write/read data on cache.
Is it okay to use C++ APIs to perform sql query, similar way? or using odbc
client is more beneficial?



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