Thanks Sergi. I somehow had this intuition and started with the affinity-collocation earlier and got into some issues. Could you please review this question <http://apache-ignite-users.70518.x6.nabble.com/AffinityKey-for-co-location-tp2772p2781.html> of mine (posted earlier) in the current context.
Any help or direction would be of great help. Regards, Moiz On Tue, Feb 9, 2016 at 9:41 PM, Sergi Vladykin <[email protected]> wrote: > 1. SQL in partitioned cache works the following way: Ignite splits top > level query into 2 separate queries - one for "map" phase and another one > for "reduce". All the "map" queries are executed on all the data nodes, the > results are combined together on the node which initiated the query and > those combined results are getting reduced by "reduce" part of the query. > Since currently we split query only at the top level, your sub-query (which > becomes part of "map" query) must produce correct independent result part > on each node. For that you need to setup correct data collocation. In your > example it looks like you should collocate your entries by Customer, so > that all the entries for a single customer will be on the same node. > See https://apacheignite.readme.io/docs/affinity-collocation > > 2. There are virtually no limitation for queries with correct collocation > :) And we continuously improve SQL support. The limitations are mostly come > up from the way Ignite splits queries and the way you collocate data. How > you collocate your data you decide yourself, what does Ignite with SQL you > can check by running EXPLAIN, it will return plan for "map" and "reduce" > parts. > > Sergi > > > > 2016-02-09 18:26 GMT+03:00 Syed Moizuddin <[email protected]>: > >> Thanks Sergi, >> >> The model provided in the example is just a simplified version of our >> production model which I am evaluating on ignite. One of our production >> query is a two-level query as in the example. I have two follow-up >> questions then: >> 1. Most of our queries are two level nested and the outer one is always >> group by on 2 particular keys, and inner query is group by on one of the >> two. Can you please suggest how to setup the data model to make such >> queries work. >> 2. As per Ignite documentation >> <https://apacheignite.readme.io/docs/sql-queries> , there is virtually >> no limitation on queries. I would like to know if there are more such >> scenarios so that I could verify my production queries are compliant with >> Ignite. >> >> Regards, >> Moiz >> >> On Tue, Feb 9, 2016 at 8:14 PM, Sergi Vladykin <[email protected]> >> wrote: >> >>> Hi Syed, >>> >>> I'm not sure I understand you data model, but the problem here is that >>> Ignite supports distributive aggregate functions in SQL only at the top >>> level query. I mean *sum(bal)* will work correctly always, but *min(balance) >>> *will run on each node separately (in general all the sub-queries will >>> run on each partition). To make this query work you have to setup you data >>> model so that every group in sub-query GROUP BY will be on separate node. >>> I'm not sure I understand your data model to give a more precise advice. >>> >>> Sergi >>> >>> >>> >>> 2016-02-09 7:18 GMT+03:00 vkulichenko <[email protected]>: >>> >>>> Hi, >>>> >>>> I'm having the same behavior and it doesn't look correct. I created a >>>> ticket >>>> for this: https://issues.apache.org/jira/browse/IGNITE-2592. Someone >>>> in the >>>> community will take a look. >>>> >>>> -Val >>>> >>>> >>>> >>>> -- >>>> View this message in context: >>>> http://apache-ignite-users.70518.x6.nabble.com/SQL-query-result-variation-tp2889p2892.html >>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com. >>>> >>> >>> >> >
