You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by begineer <re...@gmail.com> on 2016/12/05 13:20:37 UTC
Building complex queries to query ignite Cache
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/config/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.70518.x6.nabble.com/Building-complex-queries-to-query-ignite-Cache-tp9392.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Building complex queries to query ignite Cache
Posted by vdpyatkov <vl...@gmail.com>.
Please, see my answer in upper post.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Building-complex-queries-to-query-ignite-Cache-tp9392p9398.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Building complex queries to query ignite Cache
Posted by begineer <re...@gmail.com>.
Hi, Thanks for reply. I need this map to build a dashboard UI page which
looks like below(image attached). So specific part of entries would still be
more than 100,000 items which is minimum as data set for given input
parameters.
So just wondering if some inbuilt query could help me here.
<http://apache-ignite-users.70518.x6.nabble.com/file/n9395/ignite.png>
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Building-complex-queries-to-query-ignite-Cache-tp9392p9395.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Building complex queries to query ignite Cache
Posted by Vladislav Pyatkov <vl...@gmail.com>.
Hi
You should explain your task in detail.
What for are you get all entries as map in one node? You can get increase
performance if you will get only local entries (or particular partition
entries on thread) on node.
What will you do with huge map? You can to get only specific part of
entries (using SQL by indexed fields).
I think, you should review algorithm, in order to it could support
distributed paradigm.
On Mon, Dec 5, 2016 at 4:20 PM, begineer <re...@gmail.com> 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/
> config/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.
> 70518.x6.nabble.com/Building-complex-queries-to-query-
> ignite-Cache-tp9392.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Vladislav Pyatkov
Re: Building complex queries to query ignite Cache
Posted by begineer <re...@gmail.com>.
Thanks for reply.
This is perfect and much simpler if I have to get counts of various
statuses.
Although using this approach to build 3D kind of mapping(i.e. sample table
shown in reply 2 in this thread) requires a lot of manipulation to data.
Thanks all for help. Will come back again if more help required.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Building-complex-queries-to-query-ignite-Cache-tp9392p9524.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Building complex queries to query ignite Cache
Posted by Vladislav Pyatkov <vl...@gmail.com>.
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 <andrey.mashenkov@gmail.com
> 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 <re...@gmail.com> 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
Re: Building complex queries to query ignite Cache
Posted by Andrey Mashenkov <an...@gmail.com>.
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 <re...@gmail.com> 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/
> config/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.
> 70518.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