Run sql query on key-value cache

[rakshita04]

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]

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:

https://github.com/apache/ignite/blob/f37ec9eece4db627f2d5190e589f0522e445a251/modules/platforms/cpp/examples/query-example/src/query_example.cpp

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]

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]

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,

пт, 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]

Ilya, seems you mistaken, check [1]

Seems there is no additional documentation, but API is simple , check example [2].

 

[1] https://issues.apache.org/jira/browse/IGNITE-12808

[2] https://github.com/apache/ignite/pull/7627/files#diff-d0f1fdd4e070c92459cb4f1e600977bd4819216e658ff2114fe19bfeb2a93232R1000

 

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,

 

пт, 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]

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,

пт, 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].

 

[1] https://issues.apache.org/jira/browse/IGNITE-12808

[2] https://github.com/apache/ignite/pull/7627/files#diff-d0f1fdd4e070c92459cb4f1e600977bd4819216e658ff2114fe19bfeb2a93232R1000

 

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]

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]

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]

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]

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.

[1] - https://ignite.apache.org/docs/latest/sql-reference/ddl#create-index

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/