You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Anil <an...@gmail.com> on 2017/01/02 06:37:57 UTC
Re: Affinity
Hi Val,
I created sample unit test and it is working i guess. Not sure why it is
not working on actual cluster. i will take a look.
Can you please check if that test is correct or not ? thanks.
On 30 December 2016 at 01:24, vkulichenko <va...@gmail.com>
wrote:
> Anil,
>
> Can you create a unit test that will demonstrate the problem?
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Affinity-tp9744p9803.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: Affinity
Posted by vkulichenko <va...@gmail.com>.
The thread continues here:
http://apache-ignite-users.70518.x6.nabble.com/Affinity-td9744i20.html
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Affinity-tp9744p9882.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Affinity
Posted by Anil <an...@gmail.com>.
Attached. thanks.
Please let me know if you need any additional details. thanks.
On 2 January 2017 at 17:36, dkarachentsev <dk...@gridgain.com>
wrote:
> Could you please add ignite-client.xml as well?
>
> Thanks!
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Affinity-tp9744p9815.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: Affinity
Posted by dkarachentsev <dk...@gridgain.com>.
Could you please add ignite-client.xml as well?
Thanks!
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Affinity-tp9744p9815.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Affinity
Posted by Anil <an...@gmail.com>.
Thank you Sergi. i will double check this again from my side.
On 4 January 2017 at 16:50, Sergi Vladykin <se...@gmail.com> wrote:
> Hi,
>
> The query should work because everything is colocated. If not, then it
> would be nice to have a self contained test case.
>
> Sergi
>
> 2017-01-04 14:08 GMT+03:00 Anil <an...@gmail.com>:
>
>> Hi Sergi,
>>
>> Did you get a chance to look into it ? Thanks.
>>
>
>
Re: Affinity
Posted by Sergi Vladykin <se...@gmail.com>.
Hi,
The query should work because everything is colocated. If not, then it
would be nice to have a self contained test case.
Sergi
2017-01-04 14:08 GMT+03:00 Anil <an...@gmail.com>:
> Hi Sergi,
>
> Did you get a chance to look into it ? Thanks.
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi Sergi,
Did you get a chance to look into it ? Thanks.
Re: Affinity
Posted by Anil <an...@gmail.com>.
Thank you Nikolai.
I will wait for Sergi's response. Thanks to both of you.
Re: Affinity
Posted by Nikolai Tikhonov <nt...@apache.org>.
Hi Anil,
It seems that it will not work correctly but I'm not sure 100%. Let's ask
guru SQL.
Sergi,
Could you please look at the following query? (We have two simple table:
Person and PersonDetail collocated by p.equivalentId)
SELECT p.name as name, dupPerson.dupCount as count, pd.startDate as sdt,
pd.endDate as edt from PERSON_CACHE.PERSON p
JOIN (select equivalentId, count(*) dupCount from PERSON_CACHE.PERSON group
by equivalentId ) dupPerson on p.equivalentId = dupPerson.equivalentId //
to get the number of persons with same equivalent Idjoin
DETAILS_CACHE.PersonDetail pd on p.equivalentId = pd.equivalentId
JOIN (select equivalentId, max(enddate) as enddate from
DETAILS_CACHE.PersonDetail group by equivalentId) maxPd on p.equivalentId
= maxPd.equivalentId and maxPd.endDate = pd.endDate
WHERE p.personId = '100'"
On Mon, Jan 2, 2017 at 4:06 PM, Anil <an...@gmail.com> wrote:
> Hi,
>
> I did not use group by query to determine the duplicate count or latest
> details as sub query. i used it as join.
>
> if group by query works in ignite , my join query also should work.
>
> I am not sure if IGNITE-3860 relates to my query. Correct me if I am
> wrong.
>
> Thanks.
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi,
I did not use group by query to determine the duplicate count or latest
details as sub query. i used it as join.
if group by query works in ignite , my join query also should work.
I am not sure if IGNITE-3860 relates to my query. Correct me if I am wrong.
Thanks.
Re: Affinity
Posted by Nikolay Tikhonov <nt...@gridgain.com>.
Anil,
This SQL query will not work correctly due to IGNITE-3860. It contains
subquery which will not distribute on all nodes and will execute locally.
Please, see on limitations
https://apacheignite.readme.io/v1.8/docs/dml#known-limitations
Thanks,
Nikolay
On Mon, Jan 2, 2017 at 3:48 PM, Anil <an...@gmail.com> wrote:
> In local cluster, it looks good to me. But when I move the code to actual
> vm cluster. query returning incorrect data.
>
> Thanks
>
> On 2 January 2017 at 18:15, Anil <an...@gmail.com> wrote:
>
>> Hi Nikolay,
>>
>> Thanks for the response. The use case is very simple.
>>
>> Each Person is associated with number of person details which holds
>> address, start and end dates.
>>
>> *Scenario* - Get person information with latest person detail for given
>> person ids. Person information must include duplicate person with same
>> equivalent Id
>>
>> 1. To get the duplicate persons with same equivalentid -
>>
>> join (select equivalentId, count(*) dupCount from PERSON_CACHE.PERSON
>> group by equivalentId ) dupPerson on p.equivalentId = dupPerson.equivalentId
>>
>> 2. To get the latest person details - based on recent end date
>>
>> join DETAILS_CACHE.PersonDetail pd on p.equivalentId = pd.equivalentId
>> join (select equivalentId, max(enddate) as enddate from
>> DETAILS_CACHE.PersonDetail group by equivalentId) maxPd on p.equivalentId
>> = maxPd.equivalentId and maxPd.endDate = pd.endDate
>>
>> To support group sorted index, i have created index on equivalentId in
>> DETAILS_CACHE.
>>
>> Please let me know if you have any questions.
>> Thanks
>>
>
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
In local cluster, it looks good to me. But when I move the code to actual
vm cluster. query returning incorrect data.
Thanks
On 2 January 2017 at 18:15, Anil <an...@gmail.com> wrote:
> Hi Nikolay,
>
> Thanks for the response. The use case is very simple.
>
> Each Person is associated with number of person details which holds
> address, start and end dates.
>
> *Scenario* - Get person information with latest person detail for given
> person ids. Person information must include duplicate person with same
> equivalent Id
>
> 1. To get the duplicate persons with same equivalentid -
>
> join (select equivalentId, count(*) dupCount from PERSON_CACHE.PERSON
> group by equivalentId ) dupPerson on p.equivalentId = dupPerson.equivalentId
>
> 2. To get the latest person details - based on recent end date
>
> join DETAILS_CACHE.PersonDetail pd on p.equivalentId = pd.equivalentId
> join (select equivalentId, max(enddate) as enddate from
> DETAILS_CACHE.PersonDetail group by equivalentId) maxPd on p.equivalentId
> = maxPd.equivalentId and maxPd.endDate = pd.endDate
>
> To support group sorted index, i have created index on equivalentId in
> DETAILS_CACHE.
>
> Please let me know if you have any questions.
> Thanks
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi Nikolay,
Thanks for the response. The use case is very simple.
Each Person is associated with number of person details which holds
address, start and end dates.
*Scenario* - Get person information with latest person detail for given
person ids. Person information must include duplicate person with same
equivalent Id
1. To get the duplicate persons with same equivalentid -
join (select equivalentId, count(*) dupCount from PERSON_CACHE.PERSON group
by equivalentId ) dupPerson on p.equivalentId = dupPerson.equivalentId
2. To get the latest person details - based on recent end date
join DETAILS_CACHE.PersonDetail pd on p.equivalentId = pd.equivalentId
join (select equivalentId, max(enddate) as enddate from
DETAILS_CACHE.PersonDetail group by equivalentId) maxPd on p.equivalentId
= maxPd.equivalentId and maxPd.endDate = pd.endDate
To support group sorted index, i have created index on equivalentId in
DETAILS_CACHE.
Please let me know if you have any questions.
Thanks
Re: Affinity
Posted by Nikolai Tikhonov <nt...@apache.org>.
I see in your code that you implemented collocation correctly. Person and
PersondDetail with the same equivalentId were mapped on the same partition.
But I don't know what you want to get by sql (don't know your use case).
SQL returns incorrect data?
On Mon, Jan 2, 2017 at 3:21 PM, Anil <an...@gmail.com> wrote:
> Hi Nikolay,
>
> If i am not wrong, unit test case worked. i am able to see the count from
> the group by query. Am I missing anything ?
>
> Thanks.
>
> On 2 January 2017 at 17:43, Nikolai Tikhonov <nt...@apache.org> wrote:
>
>> Hi Anil!
>>
>> In your case we faced with the following issue:
>> https://issues.apache.org/jira/browse/IGNITE-3860. It's mean that
>> ignite will not execute any distributed joins inside of this subquery. For
>> more details about the issue you can read the following thread:
>> http://apache-ignite-developers.2346864.n4.nabble.co
>> m/SELECT-subqueries-in-DML-statements-td13298.html
>>
>> Thanks,
>> Nikolay
>>
>> On Mon, Jan 2, 2017 at 2:21 PM, Anil <an...@gmail.com> wrote:
>>
>>> attached the files. thanks.
>>>
>>>
>>> On 2 January 2017 at 12:07, Anil <an...@gmail.com> wrote:
>>>
>>>> Hi Val,
>>>>
>>>> I created sample unit test and it is working i guess. Not sure why it
>>>> is not working on actual cluster. i will take a look.
>>>>
>>>> Can you please check if that test is correct or not ? thanks.
>>>>
>>>> On 30 December 2016 at 01:24, vkulichenko <
>>>> valentin.kulichenko@gmail.com> wrote:
>>>>
>>>>> Anil,
>>>>>
>>>>> Can you create a unit test that will demonstrate the problem?
>>>>>
>>>>> -Val
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> View this message in context: http://apache-ignite-users.705
>>>>> 18.x6.nabble.com/Affinity-tp9744p9803.html
>>>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>>>
>>>>
>>>>
>>>
>>
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi Nikolay,
If i am not wrong, unit test case worked. i am able to see the count from
the group by query. Am I missing anything ?
Thanks.
On 2 January 2017 at 17:43, Nikolai Tikhonov <nt...@apache.org> wrote:
> Hi Anil!
>
> In your case we faced with the following issue: https://issues.apache.org/
> jira/browse/IGNITE-3860. It's mean that ignite will not execute any
> distributed joins inside of this subquery. For more details about the issue
> you can read the following thread: http://apache-ignite-
> developers.2346864.n4.nabble.com/SELECT-subqueries-in-DML-
> statements-td13298.html
>
> Thanks,
> Nikolay
>
> On Mon, Jan 2, 2017 at 2:21 PM, Anil <an...@gmail.com> wrote:
>
>> attached the files. thanks.
>>
>>
>> On 2 January 2017 at 12:07, Anil <an...@gmail.com> wrote:
>>
>>> Hi Val,
>>>
>>> I created sample unit test and it is working i guess. Not sure why it is
>>> not working on actual cluster. i will take a look.
>>>
>>> Can you please check if that test is correct or not ? thanks.
>>>
>>> On 30 December 2016 at 01:24, vkulichenko <valentin.kulichenko@gmail.com
>>> > wrote:
>>>
>>>> Anil,
>>>>
>>>> Can you create a unit test that will demonstrate the problem?
>>>>
>>>> -Val
>>>>
>>>>
>>>>
>>>> --
>>>> View this message in context: http://apache-ignite-users.705
>>>> 18.x6.nabble.com/Affinity-tp9744p9803.html
>>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>>
>>>
>>>
>>
>
Re: Affinity
Posted by Nikolai Tikhonov <nt...@apache.org>.
Hi Anil!
In your case we faced with the following issue:
https://issues.apache.org/jira/browse/IGNITE-3860. It's mean that ignite
will not execute any distributed joins inside of this subquery. For more
details about the issue you can read the following thread:
http://apache-ignite-developers.2346864.n4.nabble.com/SELECT-subqueries-in-DML-statements-td13298.html
Thanks,
Nikolay
On Mon, Jan 2, 2017 at 2:21 PM, Anil <an...@gmail.com> wrote:
> attached the files. thanks.
>
>
> On 2 January 2017 at 12:07, Anil <an...@gmail.com> wrote:
>
>> Hi Val,
>>
>> I created sample unit test and it is working i guess. Not sure why it is
>> not working on actual cluster. i will take a look.
>>
>> Can you please check if that test is correct or not ? thanks.
>>
>> On 30 December 2016 at 01:24, vkulichenko <va...@gmail.com>
>> wrote:
>>
>>> Anil,
>>>
>>> Can you create a unit test that will demonstrate the problem?
>>>
>>> -Val
>>>
>>>
>>>
>>> --
>>> View this message in context: http://apache-ignite-users.705
>>> 18.x6.nabble.com/Affinity-tp9744p9803.html
>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>
>>
>>
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
attached the files. thanks.
On 2 January 2017 at 12:07, Anil <an...@gmail.com> wrote:
> Hi Val,
>
> I created sample unit test and it is working i guess. Not sure why it is
> not working on actual cluster. i will take a look.
>
> Can you please check if that test is correct or not ? thanks.
>
> On 30 December 2016 at 01:24, vkulichenko <va...@gmail.com>
> wrote:
>
>> Anil,
>>
>> Can you create a unit test that will demonstrate the problem?
>>
>> -Val
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/Affinity-tp9744p9803.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>