You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Surinder Mehra <re...@gmail.com> on 2022/06/22 15:38:20 UTC
Query default Index in Ignite Sql cache
Hi,
We have defined indexes on sql enabled ignite cache and are able to see
indexes being used while querying.
sqline *!indexes* also shows those indexes in output. But we cant see
default index created by ignite on *primary key *and *affinity key*.
We would like to use index on key and affinity key to get results sorted
accordingly.
For example, in official docs link below, Is it possible to use the above
mentioned default indexes instead of ORG_SALARY_IDX ?
How can we get more details about these indexes which are not shown in
sqlline commands.
https://ignite.apache.org/docs/latest/key-value-api/using-cache-queries#executing-index-queries
QueryCursor<Cache.Entry<Integer, Person>> cursor = cache.query(
new IndexQuery<Integer, Person>(Person.class, "ORG_SALARY_IDX")
.setCriteria(eq("orgId", 1), gt("salary", 1000)));
Re: Query default Index in Ignite Sql cache
Posted by Surinder Mehra <re...@gmail.com>.
Thanks Maksim !
On Thu, Jun 23, 2022 at 11:01 AM Maksim Timonin <ti...@apache.org>
wrote:
> Hi,
>
> > 1. Department object has an id and affinity key on the name(it
> doesn't make sense in the real world). Does ignite create an index for
> affinity keys as well ? I don't see the affinity key index in the list
> below.
>
> You need to configure affinity key with annotations a little bit
> differently, please check docs [1]: AffinityKeyMapped must be part of cache
> key, not value object. AffinityKeyMapped doesn't affect if it is used for
> field in cache value.
> So, it should look like
>
> DepartmentKey {
> @QuerySqlField
> @AffinityKeyMapped
> private final String deptName;
> ...
> }
>
> and then cache will be like IgniteCache<DepartmentKey, Department>.
>
> > 2. When I use the default index explicitly in IndexQuery, it throws an
> exception that the index name doesn't exist.
>
> Which index name did you use?
>
> > So by default it uses primary key index or hash index when* IndexQuery* is
> executed with _KEY in criteria* ?*
>
> They PrimaryKey index executes if criteria consist of single _KEY criteria.
>
> [1]
> https://ignite.apache.org/docs/2.11.1/data-modeling/affinity-collocation#configuring-affinity-key
>
> On Wed, Jun 22, 2022 at 9:49 PM Surinder Mehra <re...@gmail.com> wrote:
>
>> Thanks for the reply. I have another question on the affinity key
>> index(if any). When we enable sql on ignite, it by default creates an index
>> on the primary key(highlighted in red below). We have created a custom
>> index on deptId(highlighted in red).
>>
>> 1. Department object has an id and affinity key on the name(it
>> doesn't make sense in the real world). Does ignite create an index for
>> affinity keys as well ? I don't see the affinity key index in the list
>> below.
>> 2. When I use the default index explicitly in IndexQuery, it throws an
>> exception that the index name doesn't exist. So by default it uses primary
>> key index or hash index when* IndexQuery* is executed with _KEY in
>> criteria* ?*
>>
>> [image: image.png]
>> [image: image.png]
>>
>> On Wed, Jun 22, 2022 at 9:35 PM Николай Ижиков <ni...@apache.org>
>> wrote:
>>
>>> SELECT * FROM SYS.INDEXES
>>>
>>> 22 июня 2022 г., в 18:38, Surinder Mehra <re...@gmail.com>
>>> написал(а):
>>>
>>> Hi,
>>> We have defined indexes on sql enabled ignite cache and are able to see
>>> indexes being used while querying.
>>> sqline *!indexes* also shows those indexes in output. But we cant see
>>> default index created by ignite on *primary key *and *affinity key*.
>>> We would like to use index on key and affinity key to get results sorted
>>> accordingly.
>>>
>>> For example, in official docs link below, Is it possible to use the
>>> above mentioned default indexes instead of ORG_SALARY_IDX ?
>>> How can we get more details about these indexes which are not shown in
>>> sqlline commands.
>>>
>>>
>>>
>>> https://ignite.apache.org/docs/latest/key-value-api/using-cache-queries#executing-index-queries
>>>
>>>
>>> QueryCursor<Cache.Entry<Integer, Person>> cursor = cache.query(
>>> new IndexQuery<Integer, Person>(Person.class, "ORG_SALARY_IDX")
>>> .setCriteria(eq("orgId", 1), gt("salary", 1000)));
>>>
>>>
>>>
Re: Query default Index in Ignite Sql cache
Posted by Maksim Timonin <ti...@apache.org>.
Hi,
> 1. Department object has an id and affinity key on the name(it
doesn't make sense in the real world). Does ignite create an index for
affinity keys as well ? I don't see the affinity key index in the list
below.
You need to configure affinity key with annotations a little bit
differently, please check docs [1]: AffinityKeyMapped must be part of cache
key, not value object. AffinityKeyMapped doesn't affect if it is used for
field in cache value.
So, it should look like
DepartmentKey {
@QuerySqlField
@AffinityKeyMapped
private final String deptName;
...
}
and then cache will be like IgniteCache<DepartmentKey, Department>.
> 2. When I use the default index explicitly in IndexQuery, it throws an
exception that the index name doesn't exist.
Which index name did you use?
> So by default it uses primary key index or hash index when* IndexQuery* is
executed with _KEY in criteria* ?*
They PrimaryKey index executes if criteria consist of single _KEY criteria.
[1]
https://ignite.apache.org/docs/2.11.1/data-modeling/affinity-collocation#configuring-affinity-key
On Wed, Jun 22, 2022 at 9:49 PM Surinder Mehra <re...@gmail.com> wrote:
> Thanks for the reply. I have another question on the affinity key index(if
> any). When we enable sql on ignite, it by default creates an index on the
> primary key(highlighted in red below). We have created a custom index on
> deptId(highlighted in red).
>
> 1. Department object has an id and affinity key on the name(it
> doesn't make sense in the real world). Does ignite create an index for
> affinity keys as well ? I don't see the affinity key index in the list
> below.
> 2. When I use the default index explicitly in IndexQuery, it throws an
> exception that the index name doesn't exist. So by default it uses primary
> key index or hash index when* IndexQuery* is executed with _KEY in
> criteria* ?*
>
> [image: image.png]
> [image: image.png]
>
> On Wed, Jun 22, 2022 at 9:35 PM Николай Ижиков <ni...@apache.org>
> wrote:
>
>> SELECT * FROM SYS.INDEXES
>>
>> 22 июня 2022 г., в 18:38, Surinder Mehra <re...@gmail.com> написал(а):
>>
>> Hi,
>> We have defined indexes on sql enabled ignite cache and are able to see
>> indexes being used while querying.
>> sqline *!indexes* also shows those indexes in output. But we cant see
>> default index created by ignite on *primary key *and *affinity key*.
>> We would like to use index on key and affinity key to get results sorted
>> accordingly.
>>
>> For example, in official docs link below, Is it possible to use the above
>> mentioned default indexes instead of ORG_SALARY_IDX ?
>> How can we get more details about these indexes which are not shown in
>> sqlline commands.
>>
>>
>>
>> https://ignite.apache.org/docs/latest/key-value-api/using-cache-queries#executing-index-queries
>>
>>
>> QueryCursor<Cache.Entry<Integer, Person>> cursor = cache.query(
>> new IndexQuery<Integer, Person>(Person.class, "ORG_SALARY_IDX")
>> .setCriteria(eq("orgId", 1), gt("salary", 1000)));
>>
>>
>>
Re: Query default Index in Ignite Sql cache
Posted by Surinder Mehra <re...@gmail.com>.
Thanks for the reply. I have another question on the affinity key index(if
any). When we enable sql on ignite, it by default creates an index on the
primary key(highlighted in red below). We have created a custom index on
deptId(highlighted in red).
1. Department object has an id and affinity key on the name(it doesn't make
sense in the real world). Does ignite create an index for affinity keys as
well ? I don't see the affinity key index in the list below.
2. When I use the default index explicitly in IndexQuery, it throws an
exception that the index name doesn't exist. So by default it uses primary
key index or hash index when* IndexQuery* is executed with _KEY in criteria*
?*
[image: image.png]
[image: image.png]
On Wed, Jun 22, 2022 at 9:35 PM Николай Ижиков <ni...@apache.org> wrote:
> SELECT * FROM SYS.INDEXES
>
> 22 июня 2022 г., в 18:38, Surinder Mehra <re...@gmail.com> написал(а):
>
> Hi,
> We have defined indexes on sql enabled ignite cache and are able to see
> indexes being used while querying.
> sqline *!indexes* also shows those indexes in output. But we cant see
> default index created by ignite on *primary key *and *affinity key*.
> We would like to use index on key and affinity key to get results sorted
> accordingly.
>
> For example, in official docs link below, Is it possible to use the above
> mentioned default indexes instead of ORG_SALARY_IDX ?
> How can we get more details about these indexes which are not shown in
> sqlline commands.
>
>
>
> https://ignite.apache.org/docs/latest/key-value-api/using-cache-queries#executing-index-queries
>
>
> QueryCursor<Cache.Entry<Integer, Person>> cursor = cache.query(
> new IndexQuery<Integer, Person>(Person.class, "ORG_SALARY_IDX")
> .setCriteria(eq("orgId", 1), gt("salary", 1000)));
>
>
>
Re: Query default Index in Ignite Sql cache
Posted by Николай Ижиков <ni...@apache.org>.
SELECT * FROM SYS.INDEXES
> 22 июня 2022 г., в 18:38, Surinder Mehra <re...@gmail.com> написал(а):
>
> Hi,
> We have defined indexes on sql enabled ignite cache and are able to see indexes being used while querying.
> sqline !indexes also shows those indexes in output. But we cant see default index created by ignite on primary key and affinity key.
> We would like to use index on key and affinity key to get results sorted accordingly.
>
> For example, in official docs link below, Is it possible to use the above mentioned default indexes instead of ORG_SALARY_IDX ?
> How can we get more details about these indexes which are not shown in sqlline commands.
>
>
> https://ignite.apache.org/docs/latest/key-value-api/using-cache-queries#executing-index-queries <https://ignite.apache.org/docs/latest/key-value-api/using-cache-queries#executing-index-queries>
> QueryCursor<Cache.Entry<Integer, Person>> cursor = cache.query(
> new IndexQuery<Integer, Person>(Person.class, "ORG_SALARY_IDX")
> .setCriteria(eq("orgId", 1), gt("salary", 1000))
> );