Hi, I see a way to improve the approach. 1) You use "affinity collocation"[1] by TradeType (that allow to match partition of cache and TreadeType).
2) Add index by TradeStatus[2] (that accelerate a SQL query by TradeStatus). 3) Use affinity call[3] (with asinc) by particular TradeType and execute a SQL like this Select TradeType, Count(*) where Trade.TradeStatus = %1 with flag "o.a.i.cache.query.SqlQuery#setLocal()". 4) After execute this, you can aggregate all results to table like yours. [1]: https://apacheignite.readme.io/docs/affinity-collocation [2]: https://apacheignite.readme.io/docs/indexes [3]: https://apacheignite.readme.io/docs/collocate-compute-and-data#affinity-call-and-run-methods On Mon, Dec 5, 2016 at 4:56 PM, Andrey Mashenkov <[email protected] > wrote: > Hi, > You can try to add index with setting: > cacheConfig.setIndexedTypes(Integer.class, > Trade.class); > and annotate "status" field with @QuerySqlField(index = true) > > Then you will be able to make sql query with grouping, smth like: "select > count(*) from Trade group by status; > > If you need to group by multiple fields: > Create group index with annotating class Trade with @QueryGroupIndex(name > = "%group_name%") > Add field to group index with annotate field with @QuerySqlField(index > = true) and @QuerySqlField.Group(name = "%group_name%", order = > %field_order_in_group%) > You are free to choose %group_name%. > > > On Mon, Dec 5, 2016 at 4:20 PM, begineer <[email protected]> wrote: > >> Hi, I have below sample bean which I am storing as value in cache. I want >> to >> build a map such that it gives me count of trade status for each trade >> type(Pls see sample output, done thru java 8 streams). >> Problem with this approach is I have to pull millions of entries from >> cache >> to some collection and manipulate them. >> >> Is there a way to query cache using SQL/ScanQueries to build same map in >> more efficient way. Below is my sample code to explain the problem. >> >> public class TradeCacheExample { >> public static void main(String[] args) { >> Trade trade1 = new Trade(1, TradeStatus.NEW, "type1"); >> Trade trade2 = new Trade(2, TradeStatus.FAILED, "type2"); >> Trade trade3 = new Trade(3, TradeStatus.NEW, "type1"); >> Trade trade4 = new Trade(4, TradeStatus.NEW, "type3"); >> Trade trade5 = new Trade(5, TradeStatus.CHANGED, "type2"); >> Trade trade6 = new Trade(6, TradeStatus.EXPIRED, "type1"); >> >> Ignite ignite = Ignition.start("examples/confi >> g/example-ignite.xml"); >> CacheConfiguration<Integer, Trade> config = new >> CacheConfiguration<>("mycache"); >> config.setIndexedTypes(Integer.class, Trade.class); >> IgniteCache<Integer, Trade> cache = >> ignite.getOrCreateCache(config); >> cache.put(trade1.getId(), trade1); >> cache.put(trade2.getId(), trade2); >> cache.put(trade3.getId(), trade3); >> cache.put(trade4.getId(), trade4); >> cache.put(trade5.getId(), trade5); >> cache.put(trade6.getId(), trade6); >> List<Trade> trades = cache.query(new ScanQuery<Integer, >> Trade>()).getAll().stream().map(item->item.getValue()).collect(toList()); >> >> Map<String, Map<TradeStatus, Long>> resultMap = >> trades.stream().collect( >> groupingBy(item -> item.getTradeType(), >> groupingBy(Trade::getStatus, >> counting()))); >> System.out.println(resultMap); >> //{type3={NEW=1}, type2={CHANGED=1, FAILED=1}, >> type1={EXPIRED=1, NEW=2}} >> } >> } >> >> public class Trade { >> private int id; >> private TradeStatus status; >> private String tradeType; >> public Trade(int id, TradeStatus status, String tradeType) { >> this.id = id; >> this.status = status; >> this.tradeType = tradeType; >> } >> >> //setter getter, equals, hashcode methods >> >> public enum TradeStatus { >> NEW, CHANGED, EXPIRED, FAILED, UNCHANGED >> } >> >> >> >> >> -- >> View this message in context: http://apache-ignite-users.705 >> 18.x6.nabble.com/Building-complex-queries-to-query-ignite- >> Cache-tp9392.html >> Sent from the Apache Ignite Users mailing list archive at Nabble.com. >> > > > > -- > С уважением, > Машенков Андрей Владимирович > Тел. +7-921-932-61-82 > > Best regards, > Andrey V. Mashenkov > Cerr: +7-921-932-61-82 > -- Vladislav Pyatkov
