phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nicolas Maillard <nmaill...@hortonworks.com>
Subject Re: Re: How to use Paged Queries?
Date Fri, 25 Jul 2014 17:53:34 GMT
Hey james

Looking at your reply got me thinking. Could pagination of very large
result sets be set up in a temporary hbase table.
Say your query for a large dataset with a complex query, the result could
be "cached" in a temp table and served paginated where rowkeys would be
postions. This way you could re-ask in a short window of time for the parts
of the data.
Maybe with a cached hint in the query with a TTL?


On Thu, Jul 24, 2014 at 10:30 AM, Michael.Calvin <77231541@qq.com> wrote:

> Understood now.
> Thanks for the help.
>
> ------------------
> Michael.Calvin.Shi
>
> ___________________________
> Hi Michael,
> You need to re-bind the variables with the id of the last row you get
> back from the SELECT statement (the 10th row in this case). Also, you
> only need to compare only the PK column in the WHERE clause, so you
> could do something like this:
>     SELECT id, interfaceid FROM moninorlog WHERE id > ?
>     ORDER BY id LIMIT 10
> This would allow you to step through your rows 10 at a time.
>
> You could also step through them ordered by interfaceid since you have
> a secondary index on it:
>     SELECT id, interfaceid FROM moninorlog WHERE (interfaceid,id) > (?,?)
>     ORDER BY interfaceid,id LIMIT 10
>
> You need to include both interfaceid and id in the WHERE clause since
> (presumably) interfaceid might repeat with the same value. The row
> value constructor syntax, (i.e. listing more than one column, like
> this: (?, ?,?) ) is only necessary if you have multiple PK columns.
>
> The important part of this paging mechanism is that it provides for an
> efficient way of "re-establishing" your cursor position.
>
> HTH. Thanks,
> James
>
> On Wed, Jul 23, 2014 at 7:00 PM, Michael.Calvin <77231541@qq.com> wrote:
> > Using HBase 0.98-hadoop-1, phoenix 4.0.0-incubating.‍
> > I have a table(named moninorlog, pk is id) created by phoenix with 13
> > columns and 1 secondary-index(named interfaceid).
> > Then I tried query like "SELECT id,interfaceid FROM moninorlog WHERE
> > (id,interfaceid) > (?,?) ORDER BY id,interfaceid LIMIT 10‍".
> > I ran this query twice in the same connection with same variables (even
> in
> > same SreparedStatement object) , but they retuned the same results.
> > Did I missed something?
> >
> > ------------------
> > Michael.Calvin.Shi
>

-- 
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is confidential, 
privileged and exempt from disclosure under applicable law. If the reader 
of this message is not the intended recipient, you are hereby notified that 
any printing, copying, dissemination, distribution, disclosure or 
forwarding of this communication is strictly prohibited. If you have 
received this communication in error, please contact the sender immediately 
and delete it from your system. Thank You.

Mime
View raw message