Re: [Dev] DAS Lucene Query group by using multiple fields

[Gimantha Bandara]

Hi all,

What if we create separate spark tables for each dimension, index those
tables and retrieve the top users? Getting top users is now possible using
sorting.

Using Spark, we can count the distinct eventIds grouped by the user.

Creating a new API method for getting the unique sub-category count of
categories is not possible from Lucene side, since they don't provide
sorting (sorting is for getting the top users) functionality for facet
Results.

On Wed, Mar 9, 2016 at 9:45 PM, Seshika Fernando  wrote:

> Only thing is then we will have to create multiple columns for the
> different dimensions - eg:- username/eventid, role/eventid, IDP/eventId etc;
>
> On Wed, Mar 9, 2016 at 9:25 PM, Gimantha Bandara 
> wrote:
>
>> Hi all,
>>
>> I think we cannot use primary keys since this table has row data and same
>> eventId can have multiple roles associated with the same username. We need
>> the role information. If we define primary keys we will probably have only
>> one record per eventId, because the last record that comes with the same
>> eventId and username will place the older record.
>>
>> What we can do is, as Rukshan said, create a separate one column with
>> with username and eventId (comma separated), index it as a facet and then
>> implement a custom aggregate function to get the unique eventId count per
>> user. At runtime, we cannot combine random fields and create facets. a
>> single field should contain comma separated values ( In this scenario,
>> "username, eventId").
>>
>> On Wed, Mar 9, 2016 at 8:39 PM, Rukshan Premathunga 
>> wrote:
>>
>>> Hi all,
>>>
>>> we need this two level of grouping is due to duplicated rows right? in
>>> that case can we define the userName, authSuccess as primary key when
>>> persist. Then this duplication will not occur and unique rows will persist.
>>> If it worked we can use the query damith mention earlier, right?
>>>
>>> If it not worked, i'm sure that we need extra attribute with composition
>>> of  "eventId" and "userName", if we need multi level grouping.
>>>
>>> Thanks and regards.
>>>
>>>
>>> On Wed, Mar 9, 2016 at 8:09 PM, Seshika Fernando 
>>> wrote:
>>>
 Hi Rukshan,

 Could you clarify whether we need to create a seperate row for this? My
 understanding of facets was that you can combine fields and create facets,
 without creating a column (that combines other fields) explicitly.

 seshi

 On Wed, Mar 9, 2016 at 8:03 PM, Damith Wickramasinghe  wrote:

> Hi Rukshan,
>
> Thanks for the response. For above approach we need to have another
> column which has comma separated values for the facet values which is not
> having in my row data table at the moment. I was thinking of finding a way
> to achieve it without creating this additional field.
>
> @seshika if this is the case we will have to run a spark query to
> create this additional column. Either we can send this as another 
> attribute
> from IS side but I don't think its  a correct approach since we have to
> handle these data customizations in DAS side. WDYT ?
>
> Regards,
> Damith.
>
> On Wed, Mar 9, 2016 at 7:29 PM, Rukshan Premathunga 
> wrote:
>
>> Hi Damith,
>>
>> you can define a  facet attribute (ex: eventId_userName_facet) with
>> composition of eventId and userName. Then you can request the query
>> as below.
>>
>> {
>> tableName : "AUTHENTICATIONANALYTICSSTREAM",
>> groupByField:"eventId_userName_facet",
>> aggregateLevel: 1,
>> query  : "timestamp:[" + startTimeStamp + " TO " +
>> endTimeStamp + "] AND authenticationSuccess:True",
>> NoOfRecords : 100,
>> aggregateFields : [
>> {
>> fieldName:"eventId",
>> aggregate:"COUNT",
>> alias:"AuthSuccess_Sum"
>> }
>> ]
>> }
>>
>>
>> Thanks and Regards.
>>
>> On Wed, Mar 9, 2016 at 5:25 PM, Damith Wickramasinghe <
>> dami...@wso2.com> wrote:
>>
>>> Hi,
>>>
>>> Assume following row data set is persisted in DAS for stream
>>> definition of eventId , userName, authSuccess .
>>>
>>> 1,user1,true
>>> 1,user1,true
>>> 2,user1,true
>>> 3,user2,true
>>> 3,user2,true
>>> 3,user2,true
>>> 4,user3,true
>>> 4,user3,true
>>> 5,user3,true
>>> 6,user3,true
>>>
>>>
>>> I need to query above data set and get per user success count for a
>>> given time period. Since we have duplicate event ids we need to group by
>>> eventid first and then group by username.Assume above data range is for 
>>> a
>>> 

Re: [Dev] DAS Lucene Query group by using multiple fields

[Seshika Fernando]

Only thing is then we will have to create multiple columns for the
different dimensions - eg:- username/eventid, role/eventid, IDP/eventId etc;

On Wed, Mar 9, 2016 at 9:25 PM, Gimantha Bandara  wrote:

> Hi all,
>
> I think we cannot use primary keys since this table has row data and same
> eventId can have multiple roles associated with the same username. We need
> the role information. If we define primary keys we will probably have only
> one record per eventId, because the last record that comes with the same
> eventId and username will place the older record.
>
> What we can do is, as Rukshan said, create a separate one column with with
> username and eventId (comma separated), index it as a facet and then
> implement a custom aggregate function to get the unique eventId count per
> user. At runtime, we cannot combine random fields and create facets. a
> single field should contain comma separated values ( In this scenario,
> "username, eventId").
>
> On Wed, Mar 9, 2016 at 8:39 PM, Rukshan Premathunga 
> wrote:
>
>> Hi all,
>>
>> we need this two level of grouping is due to duplicated rows right? in
>> that case can we define the userName, authSuccess as primary key when
>> persist. Then this duplication will not occur and unique rows will persist.
>> If it worked we can use the query damith mention earlier, right?
>>
>> If it not worked, i'm sure that we need extra attribute with composition
>> of  "eventId" and "userName", if we need multi level grouping.
>>
>> Thanks and regards.
>>
>>
>> On Wed, Mar 9, 2016 at 8:09 PM, Seshika Fernando 
>> wrote:
>>
>>> Hi Rukshan,
>>>
>>> Could you clarify whether we need to create a seperate row for this? My
>>> understanding of facets was that you can combine fields and create facets,
>>> without creating a column (that combines other fields) explicitly.
>>>
>>> seshi
>>>
>>> On Wed, Mar 9, 2016 at 8:03 PM, Damith Wickramasinghe 
>>> wrote:
>>>
 Hi Rukshan,

 Thanks for the response. For above approach we need to have another
 column which has comma separated values for the facet values which is not
 having in my row data table at the moment. I was thinking of finding a way
 to achieve it without creating this additional field.

 @seshika if this is the case we will have to run a spark query to
 create this additional column. Either we can send this as another attribute
 from IS side but I don't think its  a correct approach since we have to
 handle these data customizations in DAS side. WDYT ?

 Regards,
 Damith.

 On Wed, Mar 9, 2016 at 7:29 PM, Rukshan Premathunga 
 wrote:

> Hi Damith,
>
> you can define a  facet attribute (ex: eventId_userName_facet) with
> composition of eventId and userName. Then you can request the query
> as below.
>
> {
> tableName : "AUTHENTICATIONANALYTICSSTREAM",
> groupByField:"eventId_userName_facet",
> aggregateLevel: 1,
> query  : "timestamp:[" + startTimeStamp + " TO " +
> endTimeStamp + "] AND authenticationSuccess:True",
> NoOfRecords : 100,
> aggregateFields : [
> {
> fieldName:"eventId",
> aggregate:"COUNT",
> alias:"AuthSuccess_Sum"
> }
> ]
> }
>
>
> Thanks and Regards.
>
> On Wed, Mar 9, 2016 at 5:25 PM, Damith Wickramasinghe <
> dami...@wso2.com> wrote:
>
>> Hi,
>>
>> Assume following row data set is persisted in DAS for stream
>> definition of eventId , userName, authSuccess .
>>
>> 1,user1,true
>> 1,user1,true
>> 2,user1,true
>> 3,user2,true
>> 3,user2,true
>> 3,user2,true
>> 4,user3,true
>> 4,user3,true
>> 5,user3,true
>> 6,user3,true
>>
>>
>> I need to query above data set and get per user success count for a
>> given time period. Since we have duplicate event ids we need to group by
>> eventid first and then group by username.Assume above data range is for a
>> specific time period. Final result I need is below which has two fields
>> username and authSuccessCount.
>>
>> user1 , 2
>> user2 , 1
>> user3 , 3
>>
>> I can follow below type of query to group by using one field by
>> making the eventId a Facet.
>>
>> {
>> tableName : "AUTHENTICATIONANALYTICSSTREAM",
>> groupByField:"eventId",
>> query  : "timestamp:[" + startTimeStamp + " TO " +
>> endTimeStamp + "] AND authenticationSuccess:True",
>> NoOfRecords : 100,
>> aggregateFields : [
>> {
>> 

Re: [Dev] DAS Lucene Query group by using multiple fields

[Gimantha Bandara]

Hi all,

I think we cannot use primary keys since this table has row data and same
eventId can have multiple roles associated with the same username. We need
the role information. If we define primary keys we will probably have only
one record per eventId, because the last record that comes with the same
eventId and username will place the older record.

What we can do is, as Rukshan said, create a separate one column with with
username and eventId (comma separated), index it as a facet and then
implement a custom aggregate function to get the unique eventId count per
user. At runtime, we cannot combine random fields and create facets. a
single field should contain comma separated values ( In this scenario,
"username, eventId").

On Wed, Mar 9, 2016 at 8:39 PM, Rukshan Premathunga 
wrote:

> Hi all,
>
> we need this two level of grouping is due to duplicated rows right? in
> that case can we define the userName, authSuccess as primary key when
> persist. Then this duplication will not occur and unique rows will persist.
> If it worked we can use the query damith mention earlier, right?
>
> If it not worked, i'm sure that we need extra attribute with composition
> of  "eventId" and "userName", if we need multi level grouping.
>
> Thanks and regards.
>
>
> On Wed, Mar 9, 2016 at 8:09 PM, Seshika Fernando  wrote:
>
>> Hi Rukshan,
>>
>> Could you clarify whether we need to create a seperate row for this? My
>> understanding of facets was that you can combine fields and create facets,
>> without creating a column (that combines other fields) explicitly.
>>
>> seshi
>>
>> On Wed, Mar 9, 2016 at 8:03 PM, Damith Wickramasinghe 
>> wrote:
>>
>>> Hi Rukshan,
>>>
>>> Thanks for the response. For above approach we need to have another
>>> column which has comma separated values for the facet values which is not
>>> having in my row data table at the moment. I was thinking of finding a way
>>> to achieve it without creating this additional field.
>>>
>>> @seshika if this is the case we will have to run a spark query to create
>>> this additional column. Either we can send this as another attribute from
>>> IS side but I don't think its  a correct approach since we have to handle
>>> these data customizations in DAS side. WDYT ?
>>>
>>> Regards,
>>> Damith.
>>>
>>> On Wed, Mar 9, 2016 at 7:29 PM, Rukshan Premathunga 
>>> wrote:
>>>
 Hi Damith,

 you can define a  facet attribute (ex: eventId_userName_facet) with
 composition of eventId and userName. Then you can request the query as
 below.

 {
 tableName : "AUTHENTICATIONANALYTICSSTREAM",
 groupByField:"eventId_userName_facet",
 aggregateLevel: 1,
 query  : "timestamp:[" + startTimeStamp + " TO " +
 endTimeStamp + "] AND authenticationSuccess:True",
 NoOfRecords : 100,
 aggregateFields : [
 {
 fieldName:"eventId",
 aggregate:"COUNT",
 alias:"AuthSuccess_Sum"
 }
 ]
 }


 Thanks and Regards.

 On Wed, Mar 9, 2016 at 5:25 PM, Damith Wickramasinghe  wrote:

> Hi,
>
> Assume following row data set is persisted in DAS for stream
> definition of eventId , userName, authSuccess .
>
> 1,user1,true
> 1,user1,true
> 2,user1,true
> 3,user2,true
> 3,user2,true
> 3,user2,true
> 4,user3,true
> 4,user3,true
> 5,user3,true
> 6,user3,true
>
>
> I need to query above data set and get per user success count for a
> given time period. Since we have duplicate event ids we need to group by
> eventid first and then group by username.Assume above data range is for a
> specific time period. Final result I need is below which has two fields
> username and authSuccessCount.
>
> user1 , 2
> user2 , 1
> user3 , 3
>
> I can follow below type of query to group by using one field by making
> the eventId a Facet.
>
> {
> tableName : "AUTHENTICATIONANALYTICSSTREAM",
> groupByField:"eventId",
> query  : "timestamp:[" + startTimeStamp + " TO " +
> endTimeStamp + "] AND authenticationSuccess:True",
> NoOfRecords : 100,
> aggregateFields : [
> {
> fieldName:"eventId",
> aggregate:"COUNT",
> alias:"AuthSuccess_Sum"
> }
> ]
> }
>
> How can we have multiple grouping functionality to achieve above ?
>
> Thanks,
> Damith.
>
>
>
>
> --
> 

Re: [Dev] DAS Lucene Query group by using multiple fields

[Rukshan Premathunga]

Hi all,

we need this two level of grouping is due to duplicated rows right? in that
case can we define the userName, authSuccess as primary key when persist.
Then this duplication will not occur and unique rows will persist. If it
worked we can use the query damith mention earlier, right?

If it not worked, i'm sure that we need extra attribute with composition of
 "eventId" and "userName", if we need multi level grouping.

Thanks and regards.


On Wed, Mar 9, 2016 at 8:09 PM, Seshika Fernando  wrote:

> Hi Rukshan,
>
> Could you clarify whether we need to create a seperate row for this? My
> understanding of facets was that you can combine fields and create facets,
> without creating a column (that combines other fields) explicitly.
>
> seshi
>
> On Wed, Mar 9, 2016 at 8:03 PM, Damith Wickramasinghe 
> wrote:
>
>> Hi Rukshan,
>>
>> Thanks for the response. For above approach we need to have another
>> column which has comma separated values for the facet values which is not
>> having in my row data table at the moment. I was thinking of finding a way
>> to achieve it without creating this additional field.
>>
>> @seshika if this is the case we will have to run a spark query to create
>> this additional column. Either we can send this as another attribute from
>> IS side but I don't think its  a correct approach since we have to handle
>> these data customizations in DAS side. WDYT ?
>>
>> Regards,
>> Damith.
>>
>> On Wed, Mar 9, 2016 at 7:29 PM, Rukshan Premathunga 
>> wrote:
>>
>>> Hi Damith,
>>>
>>> you can define a  facet attribute (ex: eventId_userName_facet) with
>>> composition of eventId and userName. Then you can request the query as
>>> below.
>>>
>>> {
>>> tableName : "AUTHENTICATIONANALYTICSSTREAM",
>>> groupByField:"eventId_userName_facet",
>>> aggregateLevel: 1,
>>> query  : "timestamp:[" + startTimeStamp + " TO " +
>>> endTimeStamp + "] AND authenticationSuccess:True",
>>> NoOfRecords : 100,
>>> aggregateFields : [
>>> {
>>> fieldName:"eventId",
>>> aggregate:"COUNT",
>>> alias:"AuthSuccess_Sum"
>>> }
>>> ]
>>> }
>>>
>>>
>>> Thanks and Regards.
>>>
>>> On Wed, Mar 9, 2016 at 5:25 PM, Damith Wickramasinghe 
>>> wrote:
>>>
 Hi,

 Assume following row data set is persisted in DAS for stream definition
 of eventId , userName, authSuccess .

 1,user1,true
 1,user1,true
 2,user1,true
 3,user2,true
 3,user2,true
 3,user2,true
 4,user3,true
 4,user3,true
 5,user3,true
 6,user3,true


 I need to query above data set and get per user success count for a
 given time period. Since we have duplicate event ids we need to group by
 eventid first and then group by username.Assume above data range is for a
 specific time period. Final result I need is below which has two fields
 username and authSuccessCount.

 user1 , 2
 user2 , 1
 user3 , 3

 I can follow below type of query to group by using one field by making
 the eventId a Facet.

 {
 tableName : "AUTHENTICATIONANALYTICSSTREAM",
 groupByField:"eventId",
 query  : "timestamp:[" + startTimeStamp + " TO " +
 endTimeStamp + "] AND authenticationSuccess:True",
 NoOfRecords : 100,
 aggregateFields : [
 {
 fieldName:"eventId",
 aggregate:"COUNT",
 alias:"AuthSuccess_Sum"
 }
 ]
 }

 How can we have multiple grouping functionality to achieve above ?

 Thanks,
 Damith.




 --
 Software Engineer
 WSO2 Inc.; http://wso2.com
 
 lean.enterprise.middleware

 mobile: *+94728671315 <%2B94728671315>*


 ___
 Dev mailing list
 Dev@wso2.org
 http://wso2.org/cgi-bin/mailman/listinfo/dev


>>>
>>>
>>> --
>>> Rukshan Chathuranga.
>>> Software Engineer.
>>> WSO2, Inc.
>>>
>>
>>
>>
>> --
>> Software Engineer
>> WSO2 Inc.; http://wso2.com
>> 
>> lean.enterprise.middleware
>>
>> mobile: *+94728671315 <%2B94728671315>*
>>
>>
>


-- 
Rukshan Chathuranga.
Software Engineer.
WSO2, Inc.
___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev

Re: [Dev] DAS Lucene Query group by using multiple fields

[Seshika Fernando]

Hi Rukshan,

Could you clarify whether we need to create a seperate row for this? My
understanding of facets was that you can combine fields and create facets,
without creating a column (that combines other fields) explicitly.

seshi

On Wed, Mar 9, 2016 at 8:03 PM, Damith Wickramasinghe 
wrote:

> Hi Rukshan,
>
> Thanks for the response. For above approach we need to have another column
> which has comma separated values for the facet values which is not having
> in my row data table at the moment. I was thinking of finding a way to
> achieve it without creating this additional field.
>
> @seshika if this is the case we will have to run a spark query to create
> this additional column. Either we can send this as another attribute from
> IS side but I don't think its  a correct approach since we have to handle
> these data customizations in DAS side. WDYT ?
>
> Regards,
> Damith.
>
> On Wed, Mar 9, 2016 at 7:29 PM, Rukshan Premathunga 
> wrote:
>
>> Hi Damith,
>>
>> you can define a  facet attribute (ex: eventId_userName_facet) with
>> composition of eventId and userName. Then you can request the query as
>> below.
>>
>> {
>> tableName : "AUTHENTICATIONANALYTICSSTREAM",
>> groupByField:"eventId_userName_facet",
>> aggregateLevel: 1,
>> query  : "timestamp:[" + startTimeStamp + " TO " +
>> endTimeStamp + "] AND authenticationSuccess:True",
>> NoOfRecords : 100,
>> aggregateFields : [
>> {
>> fieldName:"eventId",
>> aggregate:"COUNT",
>> alias:"AuthSuccess_Sum"
>> }
>> ]
>> }
>>
>>
>> Thanks and Regards.
>>
>> On Wed, Mar 9, 2016 at 5:25 PM, Damith Wickramasinghe 
>> wrote:
>>
>>> Hi,
>>>
>>> Assume following row data set is persisted in DAS for stream definition
>>> of eventId , userName, authSuccess .
>>>
>>> 1,user1,true
>>> 1,user1,true
>>> 2,user1,true
>>> 3,user2,true
>>> 3,user2,true
>>> 3,user2,true
>>> 4,user3,true
>>> 4,user3,true
>>> 5,user3,true
>>> 6,user3,true
>>>
>>>
>>> I need to query above data set and get per user success count for a
>>> given time period. Since we have duplicate event ids we need to group by
>>> eventid first and then group by username.Assume above data range is for a
>>> specific time period. Final result I need is below which has two fields
>>> username and authSuccessCount.
>>>
>>> user1 , 2
>>> user2 , 1
>>> user3 , 3
>>>
>>> I can follow below type of query to group by using one field by making
>>> the eventId a Facet.
>>>
>>> {
>>> tableName : "AUTHENTICATIONANALYTICSSTREAM",
>>> groupByField:"eventId",
>>> query  : "timestamp:[" + startTimeStamp + " TO " +
>>> endTimeStamp + "] AND authenticationSuccess:True",
>>> NoOfRecords : 100,
>>> aggregateFields : [
>>> {
>>> fieldName:"eventId",
>>> aggregate:"COUNT",
>>> alias:"AuthSuccess_Sum"
>>> }
>>> ]
>>> }
>>>
>>> How can we have multiple grouping functionality to achieve above ?
>>>
>>> Thanks,
>>> Damith.
>>>
>>>
>>>
>>>
>>> --
>>> Software Engineer
>>> WSO2 Inc.; http://wso2.com
>>> 
>>> lean.enterprise.middleware
>>>
>>> mobile: *+94728671315 <%2B94728671315>*
>>>
>>>
>>> ___
>>> Dev mailing list
>>> Dev@wso2.org
>>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>>
>>>
>>
>>
>> --
>> Rukshan Chathuranga.
>> Software Engineer.
>> WSO2, Inc.
>>
>
>
>
> --
> Software Engineer
> WSO2 Inc.; http://wso2.com
> 
> lean.enterprise.middleware
>
> mobile: *+94728671315 <%2B94728671315>*
>
>
___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev

Re: [Dev] DAS Lucene Query group by using multiple fields

[Damith Wickramasinghe]

Hi Rukshan,

Thanks for the response. For above approach we need to have another column
which has comma separated values for the facet values which is not having
in my row data table at the moment. I was thinking of finding a way to
achieve it without creating this additional field.

@seshika if this is the case we will have to run a spark query to create
this additional column. Either we can send this as another attribute from
IS side but I don't think its  a correct approach since we have to handle
these data customizations in DAS side. WDYT ?

Regards,
Damith.

On Wed, Mar 9, 2016 at 7:29 PM, Rukshan Premathunga 
wrote:

> Hi Damith,
>
> you can define a  facet attribute (ex: eventId_userName_facet) with
> composition of eventId and userName. Then you can request the query as
> below.
>
> {
> tableName : "AUTHENTICATIONANALYTICSSTREAM",
> groupByField:"eventId_userName_facet",
> aggregateLevel: 1,
> query  : "timestamp:[" + startTimeStamp + " TO " +
> endTimeStamp + "] AND authenticationSuccess:True",
> NoOfRecords : 100,
> aggregateFields : [
> {
> fieldName:"eventId",
> aggregate:"COUNT",
> alias:"AuthSuccess_Sum"
> }
> ]
> }
>
>
> Thanks and Regards.
>
> On Wed, Mar 9, 2016 at 5:25 PM, Damith Wickramasinghe 
> wrote:
>
>> Hi,
>>
>> Assume following row data set is persisted in DAS for stream definition
>> of eventId , userName, authSuccess .
>>
>> 1,user1,true
>> 1,user1,true
>> 2,user1,true
>> 3,user2,true
>> 3,user2,true
>> 3,user2,true
>> 4,user3,true
>> 4,user3,true
>> 5,user3,true
>> 6,user3,true
>>
>>
>> I need to query above data set and get per user success count for a given
>> time period. Since we have duplicate event ids we need to group by eventid
>> first and then group by username.Assume above data range is for a specific
>> time period. Final result I need is below which has two fields username and
>> authSuccessCount.
>>
>> user1 , 2
>> user2 , 1
>> user3 , 3
>>
>> I can follow below type of query to group by using one field by making
>> the eventId a Facet.
>>
>> {
>> tableName : "AUTHENTICATIONANALYTICSSTREAM",
>> groupByField:"eventId",
>> query  : "timestamp:[" + startTimeStamp + " TO " +
>> endTimeStamp + "] AND authenticationSuccess:True",
>> NoOfRecords : 100,
>> aggregateFields : [
>> {
>> fieldName:"eventId",
>> aggregate:"COUNT",
>> alias:"AuthSuccess_Sum"
>> }
>> ]
>> }
>>
>> How can we have multiple grouping functionality to achieve above ?
>>
>> Thanks,
>> Damith.
>>
>>
>>
>>
>> --
>> Software Engineer
>> WSO2 Inc.; http://wso2.com
>> 
>> lean.enterprise.middleware
>>
>> mobile: *+94728671315 <%2B94728671315>*
>>
>>
>> ___
>> Dev mailing list
>> Dev@wso2.org
>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>
>>
>
>
> --
> Rukshan Chathuranga.
> Software Engineer.
> WSO2, Inc.
>



-- 
Software Engineer
WSO2 Inc.; http://wso2.com

lean.enterprise.middleware

mobile: *+94728671315*
___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev

[Dev] DAS Lucene Query group by using multiple fields

[Damith Wickramasinghe]

Hi,

Assume following row data set is persisted in DAS for stream definition of
eventId , userName, authSuccess .

1,user1,true
1,user1,true
2,user1,true
3,user2,true
3,user2,true
3,user2,true
4,user3,true
4,user3,true
5,user3,true
6,user3,true


I need to query above data set and get per user success count for a given
time period. Since we have duplicate event ids we need to group by eventid
first and then group by username.Assume above data range is for a specific
time period. Final result I need is below which has two fields username and
authSuccessCount.

user1 , 2
user2 , 1
user3 , 3

I can follow below type of query to group by using one field by making the
eventId a Facet.

{
tableName : "AUTHENTICATIONANALYTICSSTREAM",
groupByField:"eventId",
query  : "timestamp:[" + startTimeStamp + " TO " +
endTimeStamp + "] AND authenticationSuccess:True",
NoOfRecords : 100,
aggregateFields : [
{
fieldName:"eventId",
aggregate:"COUNT",
alias:"AuthSuccess_Sum"
}
]
}

How can we have multiple grouping functionality to achieve above ?

Thanks,
Damith.




-- 
Software Engineer
WSO2 Inc.; http://wso2.com

lean.enterprise.middleware

mobile: *+94728671315*
___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev