You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Prasad Bhalerao <pr...@gmail.com> on 2018/08/21 18:15:57 UTC
Affinity key in SQL execution
Hi,
I am using key value store and I have configured multiple caches. These
caches have different affinity key. To store the values in cache I am using
following key. Id and affinityId column can be different for different
caches.
public class DefaultDataAffinityKey implements DataKey<Long> {
private static final long serialVersionUID = -6858038049181201872L;
private long id;
@AffinityKeyMapped
private long affinityId;
As per this doc
<https://apacheignite-sql.readme.io/docs/performance-and-debugging#section-query-execution-flow-optimizations>
SQL
engine automatically optimizes queries that use primary or affinity keys in
the condition section of a SELECT statement.
Now consider this sql: select * from cache_1 where id=? and subscriptionId=?
In this case id is primary key and subscriptionId is a affinitykey for this
cache_1.
But how does the ignite identify that id column is primary key of this
cache and subscriptionId is a affinityKey of this cache?
Thanks,
Prasad
Re: Affinity key in SQL execution
Posted by vkulichenko <va...@gmail.com>.
Prasad,
To achieve the best performance, join criteria should be the same as
collocation criteria. E.g., if you join by userId, then userId should be
used as affinity key. Please learn more about distributed joins here:
https://apacheignite-sql.readme.io/docs/distributed-joins
-Val
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
3. I did not understand your answer in point 3.
I must join by userId to get the correct data.
Are you saying that (c1.affinityId = c2.affinityId) condition should be
included in on clause?
If I rewrite SQL as follows, will it work?
Select * from cache1 c1 join cache2 c2 on ( c1.userId = c2.userId and
and c1.affinityId
= c2.affinityId)
Where c1.affinityId = ?
Can you please explain how ignite breaks and parse and executes this SQL
internally?
I will also try to spend some time to debug into ingite code to understand
this.
My colocation strategy as follows:
I have around 20 caches. These caches contains subscription's data.
I have around 5000 subscriptions.
Affinity key for all 20 caches in "subscriptionId". This is done to make
sure that data in all 20 caches which belongs to same the subscriptionId
will landup on same node.
Although this leaves my cluster unbalanced as data is not distributed
equally.
On Wed, Aug 29, 2018, 3:21 AM vkulichenko <va...@gmail.com>
wrote:
> Prasad,
>
> 1. Yes, you will always see it in the execution plan because there are
> always two stages. Of course, if only one server node participates in the
> execution, the reduce stage is effectively no-op.
>
> 2. Yes, you need to put the annotation to make sure you can access it from
> queries. I would recommend you to treat it in the same way as any other SQL
> field.
>
> 3. The issue here is that you do not join by affinity key, but by user ID.
> This is a non-collocated join, so it can't be executed on a single node in
> the first place. I believe you should revisit your collocation strategy
> here.
>
> 4. The fact that it exists does not mean it would be used. I think you
> should fix the point #3 above before diving into further optimizations.
>
> -Val
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
3. I did not understand your answer in point 3.
My colocation strategy as follows:
I have around 20 caches. These caches contains subscription's data.
I have around 5000 subscriptions.
Affinity key for all 20 caches in "subscriptionId". This is done to make
sure that data in all 20 caches which belongs to same the subscriptionId
will landup on same node.
Although this leaves my cluster unbalanced as data is not distributed
equally.
On Wed, Aug 29, 2018, 3:21 AM vkulichenko <va...@gmail.com>
wrote:
> Prasad,
>
> 1. Yes, you will always see it in the execution plan because there are
> always two stages. Of course, if only one server node participates in the
> execution, the reduce stage is effectively no-op.
>
> 2. Yes, you need to put the annotation to make sure you can access it from
> queries. I would recommend you to treat it in the same way as any other SQL
> field.
>
> 3. The issue here is that you do not join by affinity key, but by user ID.
> This is a non-collocated join, so it can't be executed on a single node in
> the first place. I believe you should revisit your collocation strategy
> here.
>
> 4. The fact that it exists does not mean it would be used. I think you
> should fix the point #3 above before diving into further optimizations.
>
> -Val
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Affinity key in SQL execution
Posted by vkulichenko <va...@gmail.com>.
Prasad,
1. Yes, you will always see it in the execution plan because there are
always two stages. Of course, if only one server node participates in the
execution, the reduce stage is effectively no-op.
2. Yes, you need to put the annotation to make sure you can access it from
queries. I would recommend you to treat it in the same way as any other SQL
field.
3. The issue here is that you do not join by affinity key, but by user ID.
This is a non-collocated join, so it can't be executed on a single node in
the first place. I believe you should revisit your collocation strategy
here.
4. The fact that it exists does not mean it would be used. I think you
should fix the point #3 above before diving into further optimizations.
-Val
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi val
1. If the second execution plan is for reduce phase, should it come in
queries which includes affinity key in their where clause? Because there is
going to be no reduce phase in this case.
2. Is it mandatory to anotate affinity id field in my key class with
@QueryField?
Without this annotation I was getting error something like field not found?
3. E.g. if my all cache's data is colacated on same affinity key i.e.
subscriptionId.
Select * from cache1 c1 join cache2 c2 on ( c1.userId = c2.userId)
Where c1.affinityId = ? And c2.affinityId = ?
In above example, to execute the SQL only on single data node is it enough
to write only one condition i.e. "c1.affinityId = ?".
Or Do I have to write affinityId in where clause for every cache involved
in sql like "cach2.affinityId =?"
4. If the ignite automatically creates index on affinity key field then why
it is not showing in execution plan?
Thanks,
Prasad
On Tue, Aug 28, 2018, 1:48 AM vkulichenko <va...@gmail.com>
wrote:
> Hi Prasad,
>
> 1. First plan is for map phase (executed on server side) and the second one
> is for reduce phase (executed on client side). Merge scan means that it
> just
> merges result sets from all participating server nodes. Sometime it can
> contain additional reduce steps like final groupings, ordering, etc.
>
> 2. This sounds like index is not actually declared correctly and doesn't
> exist. Most likely this is reason why Ignite doesn't use it automatically
> as
> well. Please show your full indexing configuration.
>
> 3. Of course you can, it can participate in any indexes as any other field.
> Individual index for affinity key field is created automatically.
>
> 4. Unfortunately, I don't think there is a way to check this except
> debugging the code.
>
> 5. That depends on what is the foreign key. As a matter of fact, I'm a bit
> confused by the query. Looks like the affinity key is not the one you join
> on. That means that join is not collocated which is likely to cause
> performance issues and/or incorrect results.
>
> 6. Can you give a particular query example?
>
> -Val
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Affinity key in SQL execution
Posted by vkulichenko <va...@gmail.com>.
Hi Prasad,
1. First plan is for map phase (executed on server side) and the second one
is for reduce phase (executed on client side). Merge scan means that it just
merges result sets from all participating server nodes. Sometime it can
contain additional reduce steps like final groupings, ordering, etc.
2. This sounds like index is not actually declared correctly and doesn't
exist. Most likely this is reason why Ignite doesn't use it automatically as
well. Please show your full indexing configuration.
3. Of course you can, it can participate in any indexes as any other field.
Individual index for affinity key field is created automatically.
4. Unfortunately, I don't think there is a way to check this except
debugging the code.
5. That depends on what is the foreign key. As a matter of fact, I'm a bit
confused by the query. Looks like the affinity key is not the one you join
on. That means that join is not collocated which is likely to cause
performance issues and/or incorrect results.
6. Can you give a particular query example?
-Val
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi Val,
Can you please help me out with these questions?
On Thu, Aug 23, 2018 at 8:38 PM Prasad Bhalerao <
prasadbhalerao1983@gmail.com> wrote:
> Problem is resolved now. The index type setting in cache configuration was
> incorrect. After setting DefaultDataAffinityKey.class in cache config index
> type query executed successfully.
>
> New sql is as follows. I have few questions on it.
>
> 1) Execution plan is showing 2 sqls. What does second sql (highlighted)
> indicate ? What is merge scan?
>
> 3) Can I create index on affinityId?
>
> 4) After making affinityId change, how can I check that SQL is going the
> single node only?
>
> 5) Do I need to include extra condition " ag.affinityId = uad.affinityId"
> in JOIN ON clause?
>
> 6) If I am using sub queries or join queries, is it necessary to write
> affinity key condition for each Cache where caluse?
>
>
>
> SELECT ag.assetGroupId,
> ag.name
> FROM AssetGroupData ag
> JOIN USER_ACCOUNT_CACHE.UserAccountData uad
> ON (ag.userId = uad.userId)
> WHERE ag.affinityId = ?
> AND uad.unitId = ?
> AND uad.userRole = 1
>
> Execution Plan:
>
> SELECT
> AG__Z0.ASSETGROUPID AS __C0_0,
> AG__Z0.NAME <http://ag__z0.name/> AS __C0_1
> FROM USER_ACCOUNT_CACHE.USERACCOUNTDATA UAD__Z1
> /* *USER_ACCOUNT_CACHE.USER_ACCOUNT_IDX2: UNITID = ?2* */
> /* WHERE (UAD__Z1.USERROLE = 83)
> AND (UAD__Z1.UNITID = ?2)
> */
> INNER JOIN ASSET_GROUP_CACHE.ASSETGROUPDATA AG__Z0
> /* ASSET_GROUP_CACHE.AFFINITY_KEY: AFFINITYID = ?1 */
> ON 1=1
> WHERE (AG__Z0.USERID = UAD__Z1.USERID)
> AND ((UAD__Z1.USERROLE = 83)
> AND ((AG__Z0.AFFINITYID = ?1)
> AND (UAD__Z1.UNITID = ?2)))
>
> SELECT
> __C0_0 AS ASSETGROUPID,
> __C0_1 AS NAME
> FROM PUBLIC.__T0
> /* ASSET_GROUP_CACHE."merge_scan" */
>
>
>
> Thanks,
> Prasad
>
>>
>> On Thu, Aug 23, 2018 at 2:31 PM Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I tried your suggestion but I am getting query parsing error now. I am
>>> attaching my data and data key class in this mail. Could you please help me
>>> out?
>>>
>>> SQL : select assetGroupId, name from AssetGroupData where affinityId =
>>> ?
>>>
>>> *Exception:*
>>> javax.cache.CacheException: Failed to parse query. Column "AFFINITYID"
>>> not found; SQL statement:
>>> explain select assetGroupId, name from AssetGroupData where affinityId =
>>> ? [42122-196]
>>> at
>>> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
>>> at
>>> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:615)
>>> at
>>> org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:356)
>>>
>>> Test code I am using to push data to cache:
>>>
>>> private void pushData(String cacheName,List<? extends Data> datas){
>>>
>>> final IgniteCache<DataKey, Data> cache = ignite
>>> .cache(cacheName);
>>> for (Data data : datas) {
>>> cache.put(data.getKey(), data);
>>> }
>>> }
>>>
>>>
>>>
>>>
>>> Thanks,
>>> Prasad
>>>
>>> On Wed, Aug 22, 2018 at 10:18 PM Prasad Bhalerao <
>>> prasadbhalerao1983@gmail.com> wrote:
>>>
>>>> Ok, I tried to write generic impl to use the same key class with
>>>> different caches . That's why kept the name affinityId. The reason I am not
>>>> getting error is I have the subscriptionId in Data(value) class as well.
>>>>
>>>> So it means the affinity key field name matters. I was thinking/trying
>>>> to map the affinity column name "subscriptionId" to field "affinityId"
>>>> without keeping the field name same. Was looking in wrong direction.
>>>>
>>>> Thanks,
>>>> Prasad
>>>>
>>>>
>>>> Thanks,
>>>> Prasad
>>>>
>>>>
>>>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <
>>>> valentin.kulichenko@gmail.com> wrote:
>>>>
>>>>> Prasad,
>>>>>
>>>>> In this case using subscriptionId in query would be a syntax error,
>>>>> because
>>>>> the name of the field is affinityId. If you use affinityId, however,
>>>>> Ignite
>>>>> will route the query to a single node. It knows that it's affinity key
>>>>> based
>>>>> on @AffinityKeyMapped annotation.
>>>>>
>>>>> -Val
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>>
>>>>
>>>>
>>>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <
>>>> valentin.kulichenko@gmail.com> wrote:
>>>>
>>>>> Prasad,
>>>>>
>>>>> In this case using subscriptionId in query would be a syntax error,
>>>>> because
>>>>> the name of the field is affinityId. If you use affinityId, however,
>>>>> Ignite
>>>>> will route the query to a single node. It knows that it's affinity key
>>>>> based
>>>>> on @AffinityKeyMapped annotation.
>>>>>
>>>>> -Val
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>>
>>>>
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
Can someone please reply to this?
On Thu, Aug 23, 2018, 8:38 PM Prasad Bhalerao <pr...@gmail.com>
wrote:
> Problem is resolved now. The index type setting in cache configuration was
> incorrect. After setting DefaultDataAffinityKey.class in cache config index
> type query executed successfully.
>
> New sql is as follows. I have few questions on it.
>
> 1) Execution plan is showing 2 sqls. What does second sql (highlighted)
> indicate ? What it merge scan?
>
> 2) The sql is using index on unitid but it not using any index for
> condition (AG__Z0.USERID = UAD__Z1.USERID).
> I tried to use index hints : USE INDEX(user_account_idx2,
> asset_group_idx2) but I got error Index "USER_ACCOUNT_IDX2" not found.
> Index user_account_idx2 is present in UserAccountData class.
>
> How can I make use of index created on userId and unitId in this case?
>
> 3) Can I create index on affinityId?
>
> 4) After making affinityId change, how can I check that SQL is going the
> single node only?
>
> 5) Do I need to include extra condition " ag.affinityId = uad.affinityId"
> in JOIN ON clause?
>
> 6) If I am using sub queries or join queries, is it necessary to write
> affinity key condition for each Cache where caluse?
>
>
>
> SELECT ag.assetGroupId,
> ag.name
> FROM AssetGroupData ag
> JOIN USER_ACCOUNT_CACHE.UserAccountData uad
> ON (ag.userId = uad.userId)
> WHERE ag.affinityId = ?
> AND uad.unitId = ?
> AND uad.userRole = 1
>
> Execution Plan:
>
> SELECT
> AG__Z0.ASSETGROUPID AS __C0_0,
> AG__Z0.NAME <http://ag__z0.name/> AS __C0_1
> FROM USER_ACCOUNT_CACHE.USERACCOUNTDATA UAD__Z1
> /* *USER_ACCOUNT_CACHE.USER_ACCOUNT_IDX2: UNITID = ?2* */
> /* WHERE (UAD__Z1.USERROLE = 83)
> AND (UAD__Z1.UNITID = ?2)
> */
> INNER JOIN ASSET_GROUP_CACHE.ASSETGROUPDATA AG__Z0
> /* ASSET_GROUP_CACHE.AFFINITY_KEY: AFFINITYID = ?1 */
> ON 1=1
> WHERE (AG__Z0.USERID = UAD__Z1.USERID)
> AND ((UAD__Z1.USERROLE = 83)
> AND ((AG__Z0.AFFINITYID = ?1)
> AND (UAD__Z1.UNITID = ?2)))
>
> SELECT
> __C0_0 AS ASSETGROUPID,
> __C0_1 AS NAME
> FROM PUBLIC.__T0
> /* ASSET_GROUP_CACHE."merge_scan" */
>
>
>
> Thanks,
> Prasad
>
>>
>> On Thu, Aug 23, 2018 at 2:31 PM Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I tried your suggestion but I am getting query parsing error now. I am
>>> attaching my data and data key class in this mail. Could you please help me
>>> out?
>>>
>>> SQL : select assetGroupId, name from AssetGroupData where affinityId =
>>> ?
>>>
>>> *Exception:*
>>> javax.cache.CacheException: Failed to parse query. Column "AFFINITYID"
>>> not found; SQL statement:
>>> explain select assetGroupId, name from AssetGroupData where affinityId =
>>> ? [42122-196]
>>> at
>>> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
>>> at
>>> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:615)
>>> at
>>> org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:356)
>>>
>>> Test code I am using to push data to cache:
>>>
>>> private void pushData(String cacheName,List<? extends Data> datas){
>>>
>>> final IgniteCache<DataKey, Data> cache = ignite
>>> .cache(cacheName);
>>> for (Data data : datas) {
>>> cache.put(data.getKey(), data);
>>> }
>>> }
>>>
>>>
>>>
>>>
>>> Thanks,
>>> Prasad
>>>
>>> On Wed, Aug 22, 2018 at 10:18 PM Prasad Bhalerao <
>>> prasadbhalerao1983@gmail.com> wrote:
>>>
>>>> Ok, I tried to write generic impl to use the same key class with
>>>> different caches . That's why kept the name affinityId. The reason I am not
>>>> getting error is I have the subscriptionId in Data(value) class as well.
>>>>
>>>> So it means the affinity key field name matters. I was thinking/trying
>>>> to map the affinity column name "subscriptionId" to field "affinityId"
>>>> without keeping the field name same. Was looking in wrong direction.
>>>>
>>>> Thanks,
>>>> Prasad
>>>>
>>>>
>>>> Thanks,
>>>> Prasad
>>>>
>>>>
>>>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <
>>>> valentin.kulichenko@gmail.com> wrote:
>>>>
>>>>> Prasad,
>>>>>
>>>>> In this case using subscriptionId in query would be a syntax error,
>>>>> because
>>>>> the name of the field is affinityId. If you use affinityId, however,
>>>>> Ignite
>>>>> will route the query to a single node. It knows that it's affinity key
>>>>> based
>>>>> on @AffinityKeyMapped annotation.
>>>>>
>>>>> -Val
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>>
>>>>
>>>>
>>>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <
>>>> valentin.kulichenko@gmail.com> wrote:
>>>>
>>>>> Prasad,
>>>>>
>>>>> In this case using subscriptionId in query would be a syntax error,
>>>>> because
>>>>> the name of the field is affinityId. If you use affinityId, however,
>>>>> Ignite
>>>>> will route the query to a single node. It knows that it's affinity key
>>>>> based
>>>>> on @AffinityKeyMapped annotation.
>>>>>
>>>>> -Val
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>>
>>>>
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
Problem is resolved now. The index type setting in cache configuration was
incorrect. After setting DefaultDataAffinityKey.class in cache config index
type query executed successfully.
New sql is as follows. I have few questions on it.
1) Execution plan is showing 2 sqls. What does second sql (highlighted)
indicate ? What it merge scan?
2) The sql is using index on unitid but it not using any index for
condition (AG__Z0.USERID = UAD__Z1.USERID).
I tried to use index hints : USE INDEX(user_account_idx2, asset_group_idx2)
but I got error Index "USER_ACCOUNT_IDX2" not found.
Index user_account_idx2 is present in UserAccountData class.
How can I make use of index created on userId and unitId in this case?
3) Can I create index on affinityId?
4) After making affinityId change, how can I check that SQL is going the
single node only?
5) Do I need to include extra condition " ag.affinityId = uad.affinityId"
in JOIN ON clause?
6) If I am using sub queries or join queries, is it necessary to write
affinity key condition for each Cache where caluse?
SELECT ag.assetGroupId,
ag.name
FROM AssetGroupData ag
JOIN USER_ACCOUNT_CACHE.UserAccountData uad
ON (ag.userId = uad.userId)
WHERE ag.affinityId = ?
AND uad.unitId = ?
AND uad.userRole = 1
Execution Plan:
SELECT
AG__Z0.ASSETGROUPID AS __C0_0,
AG__Z0.NAME <http://ag__z0.name/> AS __C0_1
FROM USER_ACCOUNT_CACHE.USERACCOUNTDATA UAD__Z1
/* *USER_ACCOUNT_CACHE.USER_ACCOUNT_IDX2: UNITID = ?2* */
/* WHERE (UAD__Z1.USERROLE = 83)
AND (UAD__Z1.UNITID = ?2)
*/
INNER JOIN ASSET_GROUP_CACHE.ASSETGROUPDATA AG__Z0
/* ASSET_GROUP_CACHE.AFFINITY_KEY: AFFINITYID = ?1 */
ON 1=1
WHERE (AG__Z0.USERID = UAD__Z1.USERID)
AND ((UAD__Z1.USERROLE = 83)
AND ((AG__Z0.AFFINITYID = ?1)
AND (UAD__Z1.UNITID = ?2)))
SELECT
__C0_0 AS ASSETGROUPID,
__C0_1 AS NAME
FROM PUBLIC.__T0
/* ASSET_GROUP_CACHE."merge_scan" */
Thanks,
Prasad
>
> On Thu, Aug 23, 2018 at 2:31 PM Prasad Bhalerao <
> prasadbhalerao1983@gmail.com> wrote:
>
>> Hi,
>>
>> I tried your suggestion but I am getting query parsing error now. I am
>> attaching my data and data key class in this mail. Could you please help me
>> out?
>>
>> SQL : select assetGroupId, name from AssetGroupData where affinityId =
>> ?
>>
>> *Exception:*
>> javax.cache.CacheException: Failed to parse query. Column "AFFINITYID"
>> not found; SQL statement:
>> explain select assetGroupId, name from AssetGroupData where affinityId =
>> ? [42122-196]
>> at
>> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
>> at
>> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:615)
>> at
>> org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:356)
>>
>> Test code I am using to push data to cache:
>>
>> private void pushData(String cacheName,List<? extends Data> datas){
>>
>> final IgniteCache<DataKey, Data> cache = ignite
>> .cache(cacheName);
>> for (Data data : datas) {
>> cache.put(data.getKey(), data);
>> }
>> }
>>
>>
>>
>>
>> Thanks,
>> Prasad
>>
>> On Wed, Aug 22, 2018 at 10:18 PM Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com> wrote:
>>
>>> Ok, I tried to write generic impl to use the same key class with
>>> different caches . That's why kept the name affinityId. The reason I am not
>>> getting error is I have the subscriptionId in Data(value) class as well.
>>>
>>> So it means the affinity key field name matters. I was thinking/trying
>>> to map the affinity column name "subscriptionId" to field "affinityId"
>>> without keeping the field name same. Was looking in wrong direction.
>>>
>>> Thanks,
>>> Prasad
>>>
>>>
>>> Thanks,
>>> Prasad
>>>
>>>
>>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <va...@gmail.com>
>>> wrote:
>>>
>>>> Prasad,
>>>>
>>>> In this case using subscriptionId in query would be a syntax error,
>>>> because
>>>> the name of the field is affinityId. If you use affinityId, however,
>>>> Ignite
>>>> will route the query to a single node. It knows that it's affinity key
>>>> based
>>>> on @AffinityKeyMapped annotation.
>>>>
>>>> -Val
>>>>
>>>>
>>>>
>>>> --
>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>
>>>
>>>
>>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <va...@gmail.com>
>>> wrote:
>>>
>>>> Prasad,
>>>>
>>>> In this case using subscriptionId in query would be a syntax error,
>>>> because
>>>> the name of the field is affinityId. If you use affinityId, however,
>>>> Ignite
>>>> will route the query to a single node. It knows that it's affinity key
>>>> based
>>>> on @AffinityKeyMapped annotation.
>>>>
>>>> -Val
>>>>
>>>>
>>>>
>>>> --
>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>
>>>
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi,
Problem is resolved now. The index type setting in cache configuration was
incorrect. After setting DefaultDataAffinityKey.class in cache config index
type query executed successfully.
New sql is as follows. I have couple of questions on it.
1) Execution plan is showing 2 sqls. What does second sql (highlighted)
indicate ? What it merge scan?
2) The sql is using index on unitid but it not using any index for
condition (AG__Z0.USERID = UAD__Z1.USERID).
SELECT ag.assetGroupId,
ag.name
FROM AssetGroupData ag
JOIN USER_ACCOUNT_CACHE.UserAccountData uad
ON (ag.userId = uad.userId)
WHERE ag.affinityId = ?
AND uad.unitId = ?
AND uad.userRole = 1
Execution Plan:
SELECT
AG__Z0.ASSETGROUPID AS __C0_0,
AG__Z0.NAME AS __C0_1
FROM USER_ACCOUNT_CACHE.USERACCOUNTDATA UAD__Z1
/* *USER_ACCOUNT_CACHE.USER_ACCOUNT_IDX2: UNITID = ?2* */
/* WHERE (UAD__Z1.USERROLE = 83)
AND (UAD__Z1.UNITID = ?2)
*/
INNER JOIN ASSET_GROUP_CACHE.ASSETGROUPDATA AG__Z0
/* ASSET_GROUP_CACHE.AFFINITY_KEY: AFFINITYID = ?1 */
ON 1=1
WHERE (AG__Z0.USERID = UAD__Z1.USERID)
AND ((UAD__Z1.USERROLE = 83)
AND ((AG__Z0.AFFINITYID = ?1)
AND (UAD__Z1.UNITID = ?2)))
SELECT
__C0_0 AS ASSETGROUPID,
__C0_1 AS NAME
FROM PUBLIC.__T0
/* ASSET_GROUP_CACHE."merge_scan" */
Thanks,
Prasad
On Thu, Aug 23, 2018 at 2:31 PM Prasad Bhalerao <
prasadbhalerao1983@gmail.com> wrote:
> Hi,
>
> I tried your suggestion but I am getting query parsing error now. I am
> attaching my data and data key class in this mail. Could you please help me
> out?
>
> SQL : select assetGroupId, name from AssetGroupData where affinityId = ?
>
> *Exception:*
> javax.cache.CacheException: Failed to parse query. Column "AFFINITYID" not
> found; SQL statement:
> explain select assetGroupId, name from AssetGroupData where affinityId =
> ? [42122-196]
> at
> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
> at
> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:615)
> at
> org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:356)
>
> Test code I am using to push data to cache:
>
> private void pushData(String cacheName,List<? extends Data> datas){
>
> final IgniteCache<DataKey, Data> cache = ignite
> .cache(cacheName);
> for (Data data : datas) {
> cache.put(data.getKey(), data);
> }
> }
>
>
>
>
> Thanks,
> Prasad
>
> On Wed, Aug 22, 2018 at 10:18 PM Prasad Bhalerao <
> prasadbhalerao1983@gmail.com> wrote:
>
>> Ok, I tried to write generic impl to use the same key class with
>> different caches . That's why kept the name affinityId. The reason I am not
>> getting error is I have the subscriptionId in Data(value) class as well.
>>
>> So it means the affinity key field name matters. I was thinking/trying to
>> map the affinity column name "subscriptionId" to field "affinityId" without
>> keeping the field name same. Was looking in wrong direction.
>>
>> Thanks,
>> Prasad
>>
>>
>> Thanks,
>> Prasad
>>
>>
>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <va...@gmail.com>
>> wrote:
>>
>>> Prasad,
>>>
>>> In this case using subscriptionId in query would be a syntax error,
>>> because
>>> the name of the field is affinityId. If you use affinityId, however,
>>> Ignite
>>> will route the query to a single node. It knows that it's affinity key
>>> based
>>> on @AffinityKeyMapped annotation.
>>>
>>> -Val
>>>
>>>
>>>
>>> --
>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>
>>
>>
>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <va...@gmail.com>
>> wrote:
>>
>>> Prasad,
>>>
>>> In this case using subscriptionId in query would be a syntax error,
>>> because
>>> the name of the field is affinityId. If you use affinityId, however,
>>> Ignite
>>> will route the query to a single node. It knows that it's affinity key
>>> based
>>> on @AffinityKeyMapped annotation.
>>>
>>> -Val
>>>
>>>
>>>
>>> --
>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>
>>
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi,
I tried your suggestion but I am getting query parsing error now. I am
attaching my data and data key class in this mail. Could you please help me
out?
SQL : select assetGroupId, name from AssetGroupData where affinityId = ?
*Exception:*
javax.cache.CacheException: Failed to parse query. Column "AFFINITYID" not
found; SQL statement:
explain select assetGroupId, name from AssetGroupData where affinityId = ?
[42122-196]
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:615)
at
org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:356)
Test code I am using to push data to cache:
private void pushData(String cacheName,List<? extends Data> datas){
final IgniteCache<DataKey, Data> cache = ignite
.cache(cacheName);
for (Data data : datas) {
cache.put(data.getKey(), data);
}
}
Thanks,
Prasad
On Wed, Aug 22, 2018 at 10:18 PM Prasad Bhalerao <
prasadbhalerao1983@gmail.com> wrote:
> Ok, I tried to write generic impl to use the same key class with different
> caches . That's why kept the name affinityId. The reason I am not getting
> error is I have the subscriptionId in Data(value) class as well.
>
> So it means the affinity key field name matters. I was thinking/trying to
> map the affinity column name "subscriptionId" to field "affinityId" without
> keeping the field name same. Was looking in wrong direction.
>
> Thanks,
> Prasad
>
>
> Thanks,
> Prasad
>
>
> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <va...@gmail.com>
> wrote:
>
>> Prasad,
>>
>> In this case using subscriptionId in query would be a syntax error,
>> because
>> the name of the field is affinityId. If you use affinityId, however,
>> Ignite
>> will route the query to a single node. It knows that it's affinity key
>> based
>> on @AffinityKeyMapped annotation.
>>
>> -Val
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>
>
> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <va...@gmail.com>
> wrote:
>
>> Prasad,
>>
>> In this case using subscriptionId in query would be a syntax error,
>> because
>> the name of the field is affinityId. If you use affinityId, however,
>> Ignite
>> will route the query to a single node. It knows that it's affinity key
>> based
>> on @AffinityKeyMapped annotation.
>>
>> -Val
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
Ok, I tried to write generic impl to use the same key class with different
caches . That's why kept the name affinityId. The reason I am not getting
error is I have the subscriptionId in Data(value) class as well.
So it means the affinity key field name matters. I was thinking/trying to
map the affinity column name "subscriptionId" to field "affinityId" without
keeping the field name same. Was looking in wrong direction.
Thanks,
Prasad
Thanks,
Prasad
On Wed, Aug 22, 2018, 9:29 PM vkulichenko <va...@gmail.com>
wrote:
> Prasad,
>
> In this case using subscriptionId in query would be a syntax error, because
> the name of the field is affinityId. If you use affinityId, however, Ignite
> will route the query to a single node. It knows that it's affinity key
> based
> on @AffinityKeyMapped annotation.
>
> -Val
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
On Wed, Aug 22, 2018, 9:29 PM vkulichenko <va...@gmail.com>
wrote:
> Prasad,
>
> In this case using subscriptionId in query would be a syntax error, because
> the name of the field is affinityId. If you use affinityId, however, Ignite
> will route the query to a single node. It knows that it's affinity key
> based
> on @AffinityKeyMapped annotation.
>
> -Val
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Affinity key in SQL execution
Posted by vkulichenko <va...@gmail.com>.
Prasad,
In this case using subscriptionId in query would be a syntax error, because
the name of the field is affinityId. If you use affinityId, however, Ignite
will route the query to a single node. It knows that it's affinity key based
on @AffinityKeyMapped annotation.
-Val
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
I am still not getting it.
The field affinityId in my data key is just like any other field which hold
the value of affinity key column. For cache1 it will hold subscriptionId
and for other cache it might hold value of some other affinity key.
But when I write SQL like 'select * from Cahce_1 where subscriptionId = ?'
, how does ignite understands that column name subscriptionId is a affifiny
key of cache_1. I have not mentioned anywhere that column name
subscriptionId is a affinity key.
I put an entries in my caches by creating key as follows.
new DefaultDataAffinityKey(id1, subscriptionId);
new DefaultDataAffinityKey(id2, someotherId);
Here I am just passing the values to this key. Nowhere I am mentioning the
column name of primary key or affinity key.
How ignite idetifies that out of 5 columns provided in where clause of SQL
, this particular column is a affinity key or primary key.
On Wed, Aug 22, 2018, 7:52 AM vkulichenko <va...@gmail.com>
wrote:
> There are several ways to do this.
>
> First one is shown in your first message - DefaultDataAffinityKey instance
> plays the role of primary key, while enclosed affinityId field plays the of
> affinity key, because it's annotated with @AffinityKeyMapped.
>
> In case you don't have classes at all and therefore can't use annotations
> (for example, if you create all your objects using BinaryObjectBuilder),
> you
> can specify field name to be used as affinity key via
> CacheKeyConfiguration.
>
> Finally, if you use DDL to create your tables, affinity key field name can
> be specified as a part of CREATE TABLE command:
> https://apacheignite-sql.readme.io/docs/create-table
>
> Either way, affinity key is ALWAYS a part of the primary key, so there is
> no
> way to select by primary key without specifying the value for affinity key.
> But it's enough to provide ONLY affinity key to achieve the routing.
>
> -Val
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Affinity key in SQL execution
Posted by vkulichenko <va...@gmail.com>.
There are several ways to do this.
First one is shown in your first message - DefaultDataAffinityKey instance
plays the role of primary key, while enclosed affinityId field plays the of
affinity key, because it's annotated with @AffinityKeyMapped.
In case you don't have classes at all and therefore can't use annotations
(for example, if you create all your objects using BinaryObjectBuilder), you
can specify field name to be used as affinity key via CacheKeyConfiguration.
Finally, if you use DDL to create your tables, affinity key field name can
be specified as a part of CREATE TABLE command:
https://apacheignite-sql.readme.io/docs/create-table
Either way, affinity key is ALWAYS a part of the primary key, so there is no
way to select by primary key without specifying the value for affinity key.
But it's enough to provide ONLY affinity key to achieve the routing.
-Val
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Affinity key in SQL execution
Posted by Prasad Bhalerao <pr...@gmail.com>.
I could not find any cache config setting where one can provide the primary
key column name or affinity column name of a given cache.
How does ignite identify that column provided in select query's where
clause is a primary key or affinity key?
Can you give an example?
Thanks,
Prasad
On Wed, Aug 22, 2018, 1:36 AM vkulichenko <va...@gmail.com>
wrote:
> Prasad,
>
> Affinity key is always a part of primary key. For example, in your case
> primary key consists of two fields - id and subscriptionId. Therefore you
> can query by primary key without providing the affinity key. On the other
> hand, however, query can be routed to a single node even if only affinity
> key is provided in select clause.
>
> -Val
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Affinity key in SQL execution
Posted by vkulichenko <va...@gmail.com>.
Prasad,
Affinity key is always a part of primary key. For example, in your case
primary key consists of two fields - id and subscriptionId. Therefore you
can query by primary key without providing the affinity key. On the other
hand, however, query can be routed to a single node even if only affinity
key is provided in select clause.
-Val
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/