You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by "Lewin Joy (TMS)" <le...@toyota.com> on 2016/02/18 01:29:12 UTC
Hitting complex multilevel pivot queries in solr
Hi,
Is there an efficient way to hit solr for complex time consuming queries?
I have a requirement where I need to pivot on 4 fields. Two fields contain facet values close to 50. And the other 2 fields have 5000 and 8000 values. Pivoting on the 4 fields would crash the server.
Is there a better way to get the data?
Example Query Params looks like this:
&facet.pivot=country,state,part_num,part_code
Thanks,
Lewin
RE: Hitting complex multilevel pivot queries in solr
Posted by "Lewin Joy (TMS)" <le...@toyota.com>.
Hi Alvaro,
We had thought about this. But our requirement is dynamic.
The 4 fields to pivot on would change as per the many requirements.
So, this will need to be handled at query time.
Just considering the Endeca equivalent, it looks easy there.
If this feature is not available on Solr, would there be any effort in building this one?
P.S.
The endeca equivalent query below:
RETURN Results as SELECT Count(1) as "Total" GROUP BY "Country", "State", "part_num", "part_code" ORDER BY "Total" desc PAGE(0,100)
-Lewin
-----Original Message-----
From: Alvaro Cabrerizo [mailto:toporniz@gmail.com]
Sent: Friday, February 19, 2016 1:02 AM
To: solr-user@lucene.apache.org
Subject: Re: Hitting complex multilevel pivot queries in solr
Hi,
The only way I can imagine is to create that auxiliar field for performing the facet on it. It means that you have to know "a priori" the kind of report (facet field) you need.
For example if you current data (solrdocument) is:
{
"id": 3757,
"country": "CountryX",
"state": "StateY",
"part_num: "part_numZ",
"part_code": "part_codeW"
}
It should be changed at index time to:
{
"id": 3757,
"country": "CountryX",
"state": "StateY",
"part_num: "part_numZ",
"part_code": "part_codeW",
"auxField": "CountryX StateY part_numZ part_codeW"
}
And then perform the query faceting by auxField.
Regards.
On Fri, Feb 19, 2016 at 1:15 AM, Lewin Joy (TMS) <le...@toyota.com>
wrote:
> Hi,
>
> The fields are single valued. But, the requirement will be at query
> time rather than index time. This is because, we will be having many
> such scenarios with different fields.
> I hoped we could concatenate at query time. I just need top 100 counts
> from the leaf level of the pivot.
> I'm also looking at facet.threads which could give responses to an extent.
> But It does not solve my issue.
>
> Hovewer, the Endeca equivalent of this application seems to be working
> well.
> Example Endeca Query:
>
> RETURN Results as SELECT Count(1) as "Total" GROUP BY "Country",
> "State", "part_num", "part_code" ORDER BY "Total" desc PAGE(0,100)
>
>
> -Lewin
>
>
> -----Original Message-----
> From: Alvaro Cabrerizo [mailto:toporniz@gmail.com]
> Sent: Thursday, February 18, 2016 3:06 PM
> To: solr-user@lucene.apache.org
> Subject: Re: Hitting complex multilevel pivot queries in solr
>
> Hi,
>
> The idea of copying fields into a new one (or various) during indexing
> and then facet the new field (or fields) looks promising. More
> information about data will be helpful (for example if the
> fields:country, state.. are single or multivalued). For example if all
> of the fields are single valued, then the combination of
> country,state,part_num,part_code looks like a file path
> country/state/part_num/part_code and maybe (don't know your business
> rules), the solr.PathHierarchyTokenizerFactory
> <https://wiki.apache.org/solr/AnalyzersTokenizersTokenFilters> could
> be an option to research instead of facet pivoting. On the other hand,
> I don't think that the copy field <
> https://cwiki.apache.org/confluence/display/solr/Copying+Fields>
> feature can help you to build that auxiliary field. I think that
> configuring an updateRequestProcessorChain <
> https://wiki.apache.org/solr/UpdateRequestProcessor>and building your
> own UpdateRequestProcessorFactory to concat the
> country,state,part_num,part_code values can be better way.
>
> Hope it helps.
>
> On Thu, Feb 18, 2016 at 8:47 PM, Lewin Joy (TMS)
> <le...@toyota.com>
> wrote:
>
> > Still splitting my head over this one.
> > Let me know if anyone has any idea I could try.
> >
> > Or, is there a way to concatenate these 4 fields onto a dynamic
> > field and do a facet.field on top of this one?
> >
> > Thanks. Any idea is helpful to try.
> >
> > -Lewin
> >
> > -----Original Message-----
> > From: Lewin Joy (TMS) [mailto:lewin.joy@toyota.com]
> > Sent: Wednesday, February 17, 2016 4:29 PM
> > To: solr-user@lucene.apache.org
> > Subject: Hitting complex multilevel pivot queries in solr
> >
> > Hi,
> >
> > Is there an efficient way to hit solr for complex time consuming queries?
> > I have a requirement where I need to pivot on 4 fields. Two fields
> > contain facet values close to 50. And the other 2 fields have 5000
> > and
> 8000 values.
> > Pivoting on the 4 fields would crash the server.
> >
> > Is there a better way to get the data?
> >
> > Example Query Params looks like this:
> > &facet.pivot=country,state,part_num,part_code
> >
> > Thanks,
> > Lewin
> >
> >
> >
> >
>
Re: Hitting complex multilevel pivot queries in solr
Posted by Alvaro Cabrerizo <to...@gmail.com>.
Hi,
The only way I can imagine is to create that auxiliar field for performing
the facet on it. It means that you have to know "a priori" the kind of
report (facet field) you need.
For example if you current data (solrdocument) is:
{
"id": 3757,
"country": "CountryX",
"state": "StateY",
"part_num: "part_numZ",
"part_code": "part_codeW"
}
It should be changed at index time to:
{
"id": 3757,
"country": "CountryX",
"state": "StateY",
"part_num: "part_numZ",
"part_code": "part_codeW",
"auxField": "CountryX StateY part_numZ part_codeW"
}
And then perform the query faceting by auxField.
Regards.
On Fri, Feb 19, 2016 at 1:15 AM, Lewin Joy (TMS) <le...@toyota.com>
wrote:
> Hi,
>
> The fields are single valued. But, the requirement will be at query time
> rather than index time. This is because, we will be having many such
> scenarios with different fields.
> I hoped we could concatenate at query time. I just need top 100 counts
> from the leaf level of the pivot.
> I'm also looking at facet.threads which could give responses to an extent.
> But It does not solve my issue.
>
> Hovewer, the Endeca equivalent of this application seems to be working
> well.
> Example Endeca Query:
>
> RETURN Results as SELECT Count(1) as "Total" GROUP BY "Country", "State",
> "part_num", "part_code" ORDER BY "Total" desc PAGE(0,100)
>
>
> -Lewin
>
>
> -----Original Message-----
> From: Alvaro Cabrerizo [mailto:toporniz@gmail.com]
> Sent: Thursday, February 18, 2016 3:06 PM
> To: solr-user@lucene.apache.org
> Subject: Re: Hitting complex multilevel pivot queries in solr
>
> Hi,
>
> The idea of copying fields into a new one (or various) during indexing and
> then facet the new field (or fields) looks promising. More information
> about data will be helpful (for example if the fields:country, state.. are
> single or multivalued). For example if all of the fields are single valued,
> then the combination of country,state,part_num,part_code looks like a file
> path country/state/part_num/part_code and maybe (don't know your business
> rules), the solr.PathHierarchyTokenizerFactory
> <https://wiki.apache.org/solr/AnalyzersTokenizersTokenFilters> could be
> an option to research instead of facet pivoting. On the other hand, I don't
> think that the copy field <
> https://cwiki.apache.org/confluence/display/solr/Copying+Fields> feature
> can help you to build that auxiliary field. I think that configuring an
> updateRequestProcessorChain <
> https://wiki.apache.org/solr/UpdateRequestProcessor>and building your own
> UpdateRequestProcessorFactory to concat the
> country,state,part_num,part_code values can be better way.
>
> Hope it helps.
>
> On Thu, Feb 18, 2016 at 8:47 PM, Lewin Joy (TMS) <le...@toyota.com>
> wrote:
>
> > Still splitting my head over this one.
> > Let me know if anyone has any idea I could try.
> >
> > Or, is there a way to concatenate these 4 fields onto a dynamic field
> > and do a facet.field on top of this one?
> >
> > Thanks. Any idea is helpful to try.
> >
> > -Lewin
> >
> > -----Original Message-----
> > From: Lewin Joy (TMS) [mailto:lewin.joy@toyota.com]
> > Sent: Wednesday, February 17, 2016 4:29 PM
> > To: solr-user@lucene.apache.org
> > Subject: Hitting complex multilevel pivot queries in solr
> >
> > Hi,
> >
> > Is there an efficient way to hit solr for complex time consuming queries?
> > I have a requirement where I need to pivot on 4 fields. Two fields
> > contain facet values close to 50. And the other 2 fields have 5000 and
> 8000 values.
> > Pivoting on the 4 fields would crash the server.
> >
> > Is there a better way to get the data?
> >
> > Example Query Params looks like this:
> > &facet.pivot=country,state,part_num,part_code
> >
> > Thanks,
> > Lewin
> >
> >
> >
> >
>
RE: Hitting complex multilevel pivot queries in solr
Posted by "Lewin Joy (TMS)" <le...@toyota.com>.
Hi,
The fields are single valued. But, the requirement will be at query time rather than index time. This is because, we will be having many such scenarios with different fields.
I hoped we could concatenate at query time. I just need top 100 counts from the leaf level of the pivot.
I'm also looking at facet.threads which could give responses to an extent. But It does not solve my issue.
Hovewer, the Endeca equivalent of this application seems to be working well.
Example Endeca Query:
RETURN Results as SELECT Count(1) as "Total" GROUP BY "Country", "State", "part_num", "part_code" ORDER BY "Total" desc PAGE(0,100)
-Lewin
-----Original Message-----
From: Alvaro Cabrerizo [mailto:toporniz@gmail.com]
Sent: Thursday, February 18, 2016 3:06 PM
To: solr-user@lucene.apache.org
Subject: Re: Hitting complex multilevel pivot queries in solr
Hi,
The idea of copying fields into a new one (or various) during indexing and then facet the new field (or fields) looks promising. More information about data will be helpful (for example if the fields:country, state.. are single or multivalued). For example if all of the fields are single valued, then the combination of country,state,part_num,part_code looks like a file path country/state/part_num/part_code and maybe (don't know your business rules), the solr.PathHierarchyTokenizerFactory
<https://wiki.apache.org/solr/AnalyzersTokenizersTokenFilters> could be an option to research instead of facet pivoting. On the other hand, I don't think that the copy field <https://cwiki.apache.org/confluence/display/solr/Copying+Fields> feature can help you to build that auxiliary field. I think that configuring an updateRequestProcessorChain <https://wiki.apache.org/solr/UpdateRequestProcessor>and building your own UpdateRequestProcessorFactory to concat the country,state,part_num,part_code values can be better way.
Hope it helps.
On Thu, Feb 18, 2016 at 8:47 PM, Lewin Joy (TMS) <le...@toyota.com>
wrote:
> Still splitting my head over this one.
> Let me know if anyone has any idea I could try.
>
> Or, is there a way to concatenate these 4 fields onto a dynamic field
> and do a facet.field on top of this one?
>
> Thanks. Any idea is helpful to try.
>
> -Lewin
>
> -----Original Message-----
> From: Lewin Joy (TMS) [mailto:lewin.joy@toyota.com]
> Sent: Wednesday, February 17, 2016 4:29 PM
> To: solr-user@lucene.apache.org
> Subject: Hitting complex multilevel pivot queries in solr
>
> Hi,
>
> Is there an efficient way to hit solr for complex time consuming queries?
> I have a requirement where I need to pivot on 4 fields. Two fields
> contain facet values close to 50. And the other 2 fields have 5000 and 8000 values.
> Pivoting on the 4 fields would crash the server.
>
> Is there a better way to get the data?
>
> Example Query Params looks like this:
> &facet.pivot=country,state,part_num,part_code
>
> Thanks,
> Lewin
>
>
>
>
Re: Hitting complex multilevel pivot queries in solr
Posted by Alvaro Cabrerizo <to...@gmail.com>.
Hi,
The idea of copying fields into a new one (or various) during indexing and
then facet the new field (or fields) looks promising. More information
about data will be helpful (for example if the fields:country, state.. are
single or multivalued). For example if all of the fields are single valued,
then the combination of country,state,part_num,part_code looks like a file
path country/state/part_num/part_code and maybe (don't know your business
rules), the solr.PathHierarchyTokenizerFactory
<https://wiki.apache.org/solr/AnalyzersTokenizersTokenFilters> could be an
option to research instead of facet pivoting. On the other hand, I don't
think that the copy field
<https://cwiki.apache.org/confluence/display/solr/Copying+Fields> feature
can help you to build that auxiliary field. I think that configuring an
updateRequestProcessorChain
<https://wiki.apache.org/solr/UpdateRequestProcessor>and building your
own UpdateRequestProcessorFactory to concat the
country,state,part_num,part_code
values can be better way.
Hope it helps.
On Thu, Feb 18, 2016 at 8:47 PM, Lewin Joy (TMS) <le...@toyota.com>
wrote:
> Still splitting my head over this one.
> Let me know if anyone has any idea I could try.
>
> Or, is there a way to concatenate these 4 fields onto a dynamic field and
> do a facet.field on top of this one?
>
> Thanks. Any idea is helpful to try.
>
> -Lewin
>
> -----Original Message-----
> From: Lewin Joy (TMS) [mailto:lewin.joy@toyota.com]
> Sent: Wednesday, February 17, 2016 4:29 PM
> To: solr-user@lucene.apache.org
> Subject: Hitting complex multilevel pivot queries in solr
>
> Hi,
>
> Is there an efficient way to hit solr for complex time consuming queries?
> I have a requirement where I need to pivot on 4 fields. Two fields contain
> facet values close to 50. And the other 2 fields have 5000 and 8000 values.
> Pivoting on the 4 fields would crash the server.
>
> Is there a better way to get the data?
>
> Example Query Params looks like this:
> &facet.pivot=country,state,part_num,part_code
>
> Thanks,
> Lewin
>
>
>
>
RE: Hitting complex multilevel pivot queries in solr
Posted by "Lewin Joy (TMS)" <le...@toyota.com>.
Still splitting my head over this one.
Let me know if anyone has any idea I could try.
Or, is there a way to concatenate these 4 fields onto a dynamic field and do a facet.field on top of this one?
Thanks. Any idea is helpful to try.
-Lewin
-----Original Message-----
From: Lewin Joy (TMS) [mailto:lewin.joy@toyota.com]
Sent: Wednesday, February 17, 2016 4:29 PM
To: solr-user@lucene.apache.org
Subject: Hitting complex multilevel pivot queries in solr
Hi,
Is there an efficient way to hit solr for complex time consuming queries?
I have a requirement where I need to pivot on 4 fields. Two fields contain facet values close to 50. And the other 2 fields have 5000 and 8000 values. Pivoting on the 4 fields would crash the server.
Is there a better way to get the data?
Example Query Params looks like this:
&facet.pivot=country,state,part_num,part_code
Thanks,
Lewin