You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Subramanyam Satyanarayana <ss...@metabiota.com> on 2016/02/17 19:47:48 UTC
Pagination with Phoenix
We have micro services built within Play that generate Phoenix queries to serve RESTful requests. We are trying to figure a good way to implement pagination in the services.
We were curious to know if there is any prescribed way of implementing either Row Keys ( to determine start & stop conditions for scan) and/or indexes to help support this. Happy to share more details.
-
Regards,
Subbu
Manager, Platform Services
Metabiota, Inc. (www.metabiota.com<http://www.metabiota.com/>)
CONFIDENTIALITY NOTICE: The information contained in this electronic mail (email) transmission (including attachments), is intended by Metabiota for the use of the named individual or entity to which it is addressed and may contain information that is privileged or otherwise confidential. It is not intended for transmission to, or receipt by, any individual or entity other than the named addressee except as otherwise expressly permitted in this email transmission. If you have received this email in error, please delete it without copying or forwarding it, and notify the sender of the error by email reply.
Re: Pagination with Phoenix
Posted by Subramanyam Satyanarayana <ss...@metabiota.com>.
Thanks all for the responses. We will incorporate the suggestions in our design. Much appreciated
~Subbu
From: Sachin Katakdound <sa...@gmail.com>>
Reply-To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Date: Wednesday, February 17, 2016 at 11:53 AM
To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Subject: Re: Pagination with Phoenix
We used a similar approach;
If your data set never changes (no inserts or deletes) then you can cache the keys before hand and use them for pagination.
However for our use case data changes so we found a work around with sub query and nth value function.
Roughly like this,
Select * from table where col1='ABC' and (pkey1) > (select nth_value(pkey1,100) within group (order by key1, pkey1) from table where col1='ABC') order by key1, pkey1 limit 100
Thanks,
Sachin
On Feb 17, 2016, at 2:15 PM, James Taylor <ja...@apache.org>> wrote:
See https://phoenix.apache.org/paged.html and the unit test for QueryMoreIT. The row value constructor (RVC) was implemented specifically to provide an efficient means of pagination over HBase data.
Thanks,
James
On Wed, Feb 17, 2016 at 10:54 AM, Steve Terrell <st...@oculus360.us>> wrote:
I was just thinking about this today. I was going to try to implement it by using a LIMIT <number of rows> on every query, with an addition of
WHERE (rowkey_field_1 > last_rowkey_field_1_value_from_previous_query) OR (rowkey_field_2 > last_rowkey_field_2_value_from_previous_query) OR …
But I haven't tried it yet.
On Wed, Feb 17, 2016 at 12:47 PM, Subramanyam Satyanarayana <ss...@metabiota.com>> wrote:
We have micro services built within Play that generate Phoenix queries to serve RESTful requests. We are trying to figure a good way to implement pagination in the services.
We were curious to know if there is any prescribed way of implementing either Row Keys ( to determine start & stop conditions for scan) and/or indexes to help support this. Happy to share more details.
—
Regards,
Subbu
Manager, Platform Services
Metabiota, Inc. (www.metabiota.com<http://www.metabiota.com/>)
CONFIDENTIALITY NOTICE: The information contained in this electronic mail (email) transmission (including attachments), is intended by Metabiota for the use of the named individual or entity to which it is addressed and may contain information that is privileged or otherwise confidential. It is not intended for transmission to, or receipt by, any individual or entity other than the named addressee except as otherwise expressly permitted in this email transmission. If you have received this email in error, please delete it without copying or forwarding it, and notify the sender of the error by email reply.
Re: Pagination with Phoenix
Posted by Sachin Katakdound <sa...@gmail.com>.
We used a similar approach;
If your data set never changes (no inserts or deletes) then you can cache the keys before hand and use them for pagination.
However for our use case data changes so we found a work around with sub query and nth value function.
Roughly like this,
Select * from table where col1='ABC' and (pkey1) > (select nth_value(pkey1,100) within group (order by key1, pkey1) from table where col1='ABC') order by key1, pkey1 limit 100
Thanks,
Sachin
> On Feb 17, 2016, at 2:15 PM, James Taylor <ja...@apache.org> wrote:
>
> See https://phoenix.apache.org/paged.html and the unit test for QueryMoreIT. The row value constructor (RVC) was implemented specifically to provide an efficient means of pagination over HBase data.
>
> Thanks,
> James
>
>> On Wed, Feb 17, 2016 at 10:54 AM, Steve Terrell <st...@oculus360.us> wrote:
>> I was just thinking about this today. I was going to try to implement it by using a LIMIT <number of rows> on every query, with an addition of
>>
>> WHERE (rowkey_field_1 > last_rowkey_field_1_value_from_previous_query) OR (rowkey_field_2 > last_rowkey_field_2_value_from_previous_query) OR …
>>
>> But I haven't tried it yet.
>>
>>> On Wed, Feb 17, 2016 at 12:47 PM, Subramanyam Satyanarayana <ss...@metabiota.com> wrote:
>>> We have micro services built within Play that generate Phoenix queries to serve RESTful requests. We are trying to figure a good way to implement pagination in the services.
>>>
>>> We were curious to know if there is any prescribed way of implementing either Row Keys ( to determine start & stop conditions for scan) and/or indexes to help support this. Happy to share more details.
>>> —
>>> Regards,
>>> Subbu
>>> Manager, Platform Services
>>> Metabiota, Inc. (www.metabiota.com)
>>>
>>>
>>> CONFIDENTIALITY NOTICE: The information contained in this electronic mail (email) transmission (including attachments), is intended by Metabiota for the use of the named individual or entity to which it is addressed and may contain information that is privileged or otherwise confidential. It is not intended for transmission to, or receipt by, any individual or entity other than the named addressee except as otherwise expressly permitted in this email transmission. If you have received this email in error, please delete it without copying or forwarding it, and notify the sender of the error by email reply.
>
Re: Pagination with Phoenix
Posted by James Taylor <ja...@apache.org>.
See https://phoenix.apache.org/paged.html and the unit test for
QueryMoreIT. The row value constructor (RVC) was implemented specifically
to provide an efficient means of pagination over HBase data.
Thanks,
James
On Wed, Feb 17, 2016 at 10:54 AM, Steve Terrell <st...@oculus360.us>
wrote:
> I was just thinking about this today. I was going to try to implement it
> by using a LIMIT <number of rows> on every query, with an addition of
>
> WHERE (rowkey_field_1 > last_rowkey_field_1_value_from_previous_query)
> OR (rowkey_field_2 > last_rowkey_field_2_value_from_previous_query) OR …
>
> But I haven't tried it yet.
>
> On Wed, Feb 17, 2016 at 12:47 PM, Subramanyam Satyanarayana <
> ssatyanarayana@metabiota.com> wrote:
>
>> We have micro services built within Play that generate Phoenix queries
>> to serve RESTful requests. We are trying to figure a good way to implement
>> pagination in the services.
>>
>> We were curious to know if there is any prescribed way of implementing
>> either Row Keys ( to determine start & stop conditions for scan) and/or
>> indexes to help support this. Happy to share more details.
>> —
>> Regards,
>>
>> Subbu
>>
>> Manager, Platform Services
>>
>> Metabiota, Inc. (www.metabiota.com)
>>
>>
>>
>>
>> *CONFIDENTIALITY NOTICE: The information contained in this electronic
>> mail (email) transmission (including attachments), is intended by Metabiota
>> for the use of the named individual or entity to which it is addressed and
>> may contain information that is privileged or otherwise confidential. It is
>> not intended for transmission to, or receipt by, any individual or entity
>> other than the named addressee except as otherwise expressly permitted in
>> this email transmission. If you have received this email in error, please
>> delete it without copying or forwarding it, and notify the sender of the
>> error by email reply.*
>>
>
>
Re: Pagination with Phoenix
Posted by Steve Terrell <st...@oculus360.us>.
I was just thinking about this today. I was going to try to implement it
by using a LIMIT <number of rows> on every query, with an addition of
WHERE (rowkey_field_1 > last_rowkey_field_1_value_from_previous_query) OR
(rowkey_field_2 > last_rowkey_field_2_value_from_previous_query) OR …
But I haven't tried it yet.
On Wed, Feb 17, 2016 at 12:47 PM, Subramanyam Satyanarayana <
ssatyanarayana@metabiota.com> wrote:
> We have micro services built within Play that generate Phoenix queries to
> serve RESTful requests. We are trying to figure a good way to implement
> pagination in the services.
>
> We were curious to know if there is any prescribed way of implementing
> either Row Keys ( to determine start & stop conditions for scan) and/or
> indexes to help support this. Happy to share more details.
> —
> Regards,
>
> Subbu
>
> Manager, Platform Services
>
> Metabiota, Inc. (www.metabiota.com)
>
>
>
>
> *CONFIDENTIALITY NOTICE: The information contained in this electronic mail
> (email) transmission (including attachments), is intended by Metabiota for
> the use of the named individual or entity to which it is addressed and may
> contain information that is privileged or otherwise confidential. It is not
> intended for transmission to, or receipt by, any individual or entity other
> than the named addressee except as otherwise expressly permitted in this
> email transmission. If you have received this email in error, please delete
> it without copying or forwarding it, and notify the sender of the error by
> email reply.*
>