You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Ajay <aj...@gmail.com> on 2015/02/10 13:59:02 UTC
Pagination support on Java Driver Query API
Hi,
I am working on exposing the Cassandra Query APIs(Java Driver) as REST APIs
for our internal project.
To support Pagination, I looked at the Cassandra documentation, Source code
and other forums.
What I mean by pagination support is like below:
1) Client fires query to REST server
2) Server prepares the statement, caches the query and return a query id
(unique id)
3) Get the query id, offset and limit and return the set of rows according
to the offset and limit and also return the last returned row offset.
4) Client make subsequent calls to the server with the offset returned by
the server until all rows are returned. In case once call fails or times
out, the client will make a call again.
Below are the details I found:
1) Java driver implicitly support Pagination in the ResultSet (using
Iterator) which can be controlled through FetchSize. But it is limited in a
way that we cannot skip or go previous. The FetchState is not exposed.
2) Using token() function on the clustering keys of the last returned row,
we can skip the returned rows and using the LIMIT keyword, we can limit the
number of rows. But the problem I see is that the token() function cannot
be used if the query contains ORDER BY clause.
Is there any other way to achieve the pagination support?
Thanks
Ajay
Re: Pagination support on Java Driver Query API
Posted by Eric Stevens <mi...@gmail.com>.
Thanks Ondřej! Definitely much easier.
N/B, this is a new feature in 2.0.x, it will not work in 1.2.x.
cqlsh:scratch> SELECT * FROM foo WHERE partitionkey = 1 and (ck1, ck2) >
(1,2) limit 2;
Bad Request: line 1:45 no viable alternative at input '('
On Thu, Feb 12, 2015 at 8:44 AM, Ondřej Nešpor <on...@gmail.com>
wrote:
> There is a much easier way to do that (and I suppose the Java driver does
> it this way):
>
> page 1:
>
> SELECT * FROM foo WHERE partitionkey = 1 limit 2;
>
> partitionkey | ck1 | ck2 | col1 | col2
>
> --------------+-----+-----+------+------
>
> 1 | 1 | 3 | 3 | 3
>
> 1 | 1 | 2 | 2 | 2
>
>
>
> page2:
>
> SELECT * FROM foo WHERE partitionkey = 1 and (ck1, ck2) > (1,2) limit 2;
>
>
> partitionkey | ck1 | ck2 | col1 | col2
>
> --------------+-----+-----+------+------
>
> 1 | 1 | 1 | 1 | 1
>
> 1 | 2 | 3 | 6 | 6
>
>
>
>
> page 3:
>
> SELECT * FROM foo WHERE partitionkey = 1 and (ck1, ck2) > (2,3) limit 2;
>
> partitionkey | ck1 | ck2 | col1 | col2
>
> --------------+-----+-----+------+------
>
> 1 | 2 | 2 | 5 | 5
>
> 1 | 2 | 1 | 4 | 4
>
>
>
> Basically you pass ck1 and ck2 values from the last row of the previous
> result and tell C* you want results that are greater (next results in case
> the first clustering key is ASC).
>
>
>
> Andrew
>
>
>
> Dne 12.2.2015 v 15:50 Eric Stevens napsal(a):
>
> Your page state then needs to track the last ck1 and last ck2 you saw.
> Pages 2+ will end up needing to be up to two queries if the first query
> doesn't fill the page size.
>
> CREATE TABLE foo (
> partitionkey int,
> ck1 int,
> ck2 int,
> col1 int,
> col2 int,
> PRIMARY KEY ((partitionkey), ck1, ck2)
> ) WITH CLUSTERING ORDER BY (ck1 asc, ck2 desc);
>
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,1,1,1);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,2,2,2);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,3,3,3);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,1,4,4);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,2,5,5);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,3,6,6);
>
> If you're pulling the whole of partition 1 and your page size is 2, your
> first page looks like:
>
> *PAGE 1*
>
> SELECT * FROM foo WHERE partitionkey = 1 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 1 | 3 | 3 | 3
> 1 | 1 | 2 | 2 | 2
>
> You got enough rows to satisfy the page, Your page state is taken from
> the last row: (ck1=1, ck2=2)
>
>
> *PAGE 2*
> Notice that you have a page state, and add some limiting clauses on the
> statement:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 1 AND ck2 < 2 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 1 | 1 | 1 | 1
>
> Oops, we didn't get enough rows to satisfy the page limit, so we need to
> continue on, we just need one more:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 1 LIMIT 1;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 2 | 3 | 6 | 6
>
> We have enough to satisfy page 2 now, our new page state: (ck1 = 2, ck2
> = 3).
>
>
> *PAGE 3*
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 3 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 2 | 2 | 5 | 5
> 1 | 2 | 1 | 4 | 4
>
> Great, we satisfied this page with only one query, page state: (ck1 = 2,
> ck2 = 1).
>
>
> *PAGE 4*
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 1 LIMIT 2;
> (0 rows)
>
> Oops, our initial query was on the boundary of ck1, but this looks like
> any other time that the initial query returns < pageSize rows, we just move
> on to the next page:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 2 LIMIT 2;
> (0 rows)
>
> Aha, we've exhausted ck1 as well, so there are no more pages, page 3
> actually pulled the last possible value; page 4 is empty, and we're all
> done. Generally speaking you know you're done when your first clustering
> key is the only non-equality operator in the statement, and you got no rows
> back.
>
>
>
>
>
>
> On Wed, Feb 11, 2015 at 10:55 AM, Ajay <aj...@gmail.com> wrote:
>
>> Basically I am trying different queries with your approach.
>>
>> One such query is like
>>
>> Select * from mycf where condition on partition key order by ck1 asc, ck2
>> desc where ck1 and ck2 are clustering keys in that order.
>>
>> Here how do we achieve pagination support?
>>
>> Thanks
>> Ajay
>> On Feb 11, 2015 11:16 PM, "Ajay" <aj...@gmail.com> wrote:
>>
>>>
>>> Hi Eric,
>>>
>>> Thanks for your reply.
>>>
>>> I am using Cassandra 2.0.11 and in that I cannot append condition like
>>> last clustering key column > value of the last row in the previous batch.
>>> It fails Preceding column is either not restricted or by a non-EQ relation.
>>> It means I need to specify equal condition for all preceding clustering
>>> key columns. With this I cannot get the pagination correct.
>>>
>>> Thanks
>>> Ajay
>>> > I can't believe that everyone read & process all rows at once
>>> (without pagination).
>>>
>>> Probably not too many people try to read all rows in a table as a
>>> single rolling operation with a standard client driver. But those who do
>>> would use token() to keep track of where they are and be able to resume
>>> with that as well.
>>>
>>> But it sounds like you're talking about paginating a subset of data -
>>> larger than you want to process as a unit, but prefiltered by some other
>>> criteria which prevents you from being able to rely on token(). For this
>>> there is no general purpose solution, but it typically involves you
>>> maintaining your own paging state, typically keeping track of the last
>>> partitioning and clustering key seen, and using that to construct your next
>>> query.
>>>
>>> For example, we have client queries which can span several
>>> partitioning keys. We make sure that the List of partition keys generated
>>> by a given client query List(Pq) is deterministic, then our paging
>>> state is the index offset of the final Pq in the response, plus the
>>> value of the final clustering column. A query coming in with a paging
>>> state attached to it starts the next set of queries from the provided Pq offset
>>> where clusteringKey > the provided value.
>>>
>>> So if you can just track partition key offset (if spanning multiple
>>> partitions), and clustering key offset, you can construct your next query
>>> from those instead.
>>>
>>> On Tue, Feb 10, 2015 at 6:58 PM, Ajay <aj...@gmail.com> wrote:
>>>
>>>> Thanks Alex.
>>>>
>>>> But is there any workaround possible?. I can't believe that everyone
>>>> read & process all rows at once (without pagination).
>>>>
>>>> Thanks
>>>> Ajay
>>>> On Feb 10, 2015 11:46 PM, "Alex Popescu" <al...@datastax.com> wrote:
>>>>
>>>>>
>>>>> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
>>>>>
>>>>>> 1) Java driver implicitly support Pagination in the ResultSet (using
>>>>>> Iterator) which can be controlled through FetchSize. But it is limited in a
>>>>>> way that we cannot skip or go previous. The FetchState is not exposed.
>>>>>
>>>>>
>>>>> Cassandra doesn't support skipping so this is not really a limitation
>>>>> of the driver.
>>>>>
>>>>>
>>>>> --
>>>>>
>>>>> [:>-a)
>>>>>
>>>>> Alex Popescu
>>>>> Sen. Product Manager @ DataStax
>>>>> @al3xandru
>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>> send an email to java-driver-user+unsubscribe@lists.datastax.com.
>>>>>
>>>>
>>>
>
>
Re: Pagination support on Java Driver Query API
Posted by Ondřej Nešpor <on...@gmail.com>.
There is a much easier way to do that (and I suppose the Java driver
does it this way):
page 1:
SELECT * FROM foo WHERE partitionkey = 1 limit 2;
partitionkey | ck1 | ck2 | col1 | col2
--------------+-----+-----+------+------
1 | 1 | 3 | 3 | 3
1 | 1 | 2 | 2 | 2
page2:
SELECT * FROM foo WHERE partitionkey = 1 and (ck1, ck2) > (1,2) limit 2;
partitionkey | ck1 | ck2 | col1 | col2
--------------+-----+-----+------+------
1 | 1 | 1 | 1 | 1
1 | 2 | 3 | 6 | 6
page 3:
SELECT * FROM foo WHERE partitionkey = 1 and (ck1, ck2) > (2,3) limit 2;
partitionkey | ck1 | ck2 | col1 | col2
--------------+-----+-----+------+------
1 | 2 | 2 | 5 | 5
1 | 2 | 1 | 4 | 4
Basically you pass ck1 and ck2 values from the last row of the previous
result and tell C* you want results that are greater (next results in
case the first clustering key is ASC).
Andrew
Dne 12.2.2015 v 15:50 Eric Stevens napsal(a):
> Your page state then needs to track the last ck1 and last ck2 you
> saw. Pages 2+ will end up needing to be up to two queries if the
> first query doesn't fill the page size.
>
> CREATE TABLE foo (
> partitionkey int,
> ck1 int,
> ck2 int,
> col1 int,
> col2 int,
> PRIMARY KEY ((partitionkey), ck1, ck2)
> ) WITH CLUSTERING ORDER BY (ck1 asc, ck2 desc);
>
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,1,1,1);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,2,2,2);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,3,3,3);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,1,4,4);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,2,5,5);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,3,6,6);
>
> If you're pulling the whole of partition 1 and your page size is 2,
> your first page looks like:
>
> *PAGE 1*
>
> SELECT * FROM foo WHERE partitionkey = 1 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 1 | 3 | 3 | 3
> 1 | 1 | 2 | 2 | 2
>
> You got enough rows to satisfy the page, Your page state is taken from
> the last row: (ck1=1, ck2=2)
>
>
> *PAGE 2*
> Notice that you have a page state, and add some limiting clauses on
> the statement:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 1 AND ck2 < 2 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 1 | 1 | 1 | 1
>
> Oops, we didn't get enough rows to satisfy the page limit, so we need
> to continue on, we just need one more:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 1 LIMIT 1;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 2 | 3 | 6 | 6
>
> We have enough to satisfy page 2 now, our new page state: (ck1 = 2,
> ck2 = 3).
>
>
> *PAGE 3*
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 3 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 2 | 2 | 5 | 5
> 1 | 2 | 1 | 4 | 4
>
> Great, we satisfied this page with only one query, page state: (ck1 =
> 2, ck2 = 1).
>
>
> *PAGE 4*
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 1 LIMIT 2;
> (0 rows)
>
> Oops, our initial query was on the boundary of ck1, but this looks
> like any other time that the initial query returns < pageSize rows, we
> just move on to the next page:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 2 LIMIT 2;
> (0 rows)
>
> Aha, we've exhausted ck1 as well, so there are no more pages, page 3
> actually pulled the last possible value; page 4 is empty, and we're
> all done. Generally speaking you know you're done when your first
> clustering key is the only non-equality operator in the statement, and
> you got no rows back.
>
>
>
>
>
>
> On Wed, Feb 11, 2015 at 10:55 AM, Ajay <ajay.garga@gmail.com
> <ma...@gmail.com>> wrote:
>
> Basically I am trying different queries with your approach.
>
> One such query is like
>
> Select * from mycf where condition on partition key order by ck1
> asc, ck2 desc where ck1 and ck2 are clustering keys in that order.
>
> Here how do we achieve pagination support?
>
> Thanks
> Ajay
>
> On Feb 11, 2015 11:16 PM, "Ajay" <ajay.garga@gmail.com
> <ma...@gmail.com>> wrote:
>
>
> Hi Eric,
>
> Thanks for your reply.
>
> I am using Cassandra 2.0.11 and in that I cannot append
> condition like last clustering key column > value of the last
> row in the previous batch. It fails Preceding column is either
> not restricted or by a non-EQ relation. It means I need to
> specify equal condition for all preceding clustering key
> columns. With this I cannot get the pagination correct.
>
> Thanks
> Ajay
>
> > I can't believe that everyone read & process all rows at
> once (without pagination).
>
> Probably not too many people try to read all rows in a table
> as a single rolling operation with a standard client driver.
> But those who do would use token() to keep track of where they
> are and be able to resume with that as well.
>
> But it sounds like you're talking about paginating a subset of
> data - larger than you want to process as a unit, but
> prefiltered by some other criteria which prevents you from
> being able to rely on token(). For this there is no general
> purpose solution, but it typically involves you maintaining
> your own paging state, typically keeping track of the last
> partitioning and clustering key seen, and using that to
> construct your next query.
>
> For example, we have client queries which can span several
> partitioning keys. We make sure that the List of partition
> keys generated by a given client query List(Pq) is
> deterministic, then our paging state is the index offset of
> the final Pq in the response, plus the value of the final
> clustering column. A query coming in with a paging state
> attached to it starts the next set of queries from the
> provided Pq offset where clusteringKey > the provided value.
>
> So if you can just track partition key offset (if spanning
> multiple partitions), and clustering key offset, you can
> construct your next query from those instead.
>
> On Tue, Feb 10, 2015 at 6:58 PM, Ajay <ajay.garga@gmail.com
> <ma...@gmail.com>> wrote:
>
> Thanks Alex.
>
> But is there any workaround possible?. I can't believe
> that everyone read & process all rows at once (without
> pagination).
>
> Thanks
> Ajay
>
> On Feb 10, 2015 11:46 PM, "Alex Popescu"
> <alexp@datastax.com <ma...@datastax.com>> wrote:
>
>
> On Tue, Feb 10, 2015 at 4:59 AM, Ajay
> <ajay.garga@gmail.com <ma...@gmail.com>>
> wrote:
>
> 1) Java driver implicitly support Pagination in
> the ResultSet (using Iterator) which can be
> controlled through FetchSize. But it is limited in
> a way that we cannot skip or go previous. The
> FetchState is not exposed.
>
>
> Cassandra doesn't support skipping so this is not
> really a limitation of the driver.
>
>
> --
>
> [:>-a)
>
> Alex Popescu
> Sen. Product Manager @ DataStax
> @al3xandru
> To unsubscribe from this group and stop receiving
> emails from it, send an email to
> java-driver-user+unsubscribe@lists.datastax.com
> <ma...@lists.datastax.com>.
>
>
>
Re: Pagination support on Java Driver Query API
Posted by Ajay <aj...@gmail.com>.
The syntax suggested by Ondrej is not working in some case in 2.0.11 and
logged an issue for the same.
https://issues.apache.org/jira/browse/CASSANDRA-8797
Thanks
Ajay
On Feb 12, 2015 11:01 PM, "Bulat Shakirzyanov" <
bulat.shakirzyanov@datastax.com> wrote:
> Fixed my Mail.app settings so you can see my actual name, sorry.
>
> On Feb 12, 2015, at 8:55 AM, DataStax <bu...@datastax.com>
> wrote:
>
> Hello,
>
> As was mentioned earlier, the Java driver doesn’t actually perform
> pagination.
>
> Instead, it uses cassandra native protocol to set page size of the result
> set. (
> https://github.com/apache/cassandra/blob/trunk/doc/native_protocol_v2.spec#L699-L730
> )
> When Cassandra sends the result back to the java driver, it includes a
> some binary token.
> This token represents paging state. To fetch the next page, the driver
> re-executes the same
> statement with original page size and paging state attached. If there is
> another page available,
> Cassandra responds with a new paging state that can be used to fetch it.
>
> You could also try reporting this issue on the Cassandra user mailing list.
>
> On Feb 12, 2015, at 8:35 AM, Eric Stevens <mi...@gmail.com> wrote:
>
> I don't know what the shape of the page state data is deep inside the
> JavaDriver, I've actually tried to dig into that in the past and understand
> it to see if I could reproduce it as a general purpose any-query kind of
> thing. I gave up before I fully understood it, but I think it's actually a
> handle to an in-memory state maintained by the coordinator, which is only
> maintained for the lifetime of the statement (i.e. it's not stateless
> paging). That would make it a bad candidate for stateless paging scenarios
> such as REST requests where a typical setup would load balance across HTTP
> hosts, never mind across coordinators.
>
> It shouldn't be too much work to abstract this basic idea for manual
> paging into a general purpose class that takes List[ClusteringKeyDef[T,
> O<:Ordering]], and can produce a connection agnostic PageState from a
> ResultSet or Row, or accepts a PageState to produce a WHERE CQL fragment.
>
>
>
> Also RE: possibly multiple queries to satisfy a page - yes, that's
> unfortunate. Since you're on 2.0.11, see Ondřej's answer to avoid it.
>
> On Thu, Feb 12, 2015 at 8:13 AM, Ajay <aj...@gmail.com> wrote:
>
>> Thanks Eric. I figured out the same but didn't get time to put it on the
>> mail. Thanks.
>>
>> But it is highly tied up to how data is stored internally in Cassandra.
>> Basically how partition keys are used to distribute (less likely to change.
>> We are not directly dependence on the partition algo) and clustering keys
>> are used to sort the data with in a partition( multi level sorting and
>> henceforth the restrictions on the ORDER BY clause) which I think can
>> change likely down the lane in Cassandra 3.x or 4.x in an different way for
>> some better storage or retrieval.
>>
>> Thats said I am hesitant to implement this client side logic for
>> pagination for a) 2+ queries might need more than one query to Cassandra.
>> b) tied up implementation to Cassandra internal storage details which can
>> change(though not often). c) in our case, we are building REST Apis which
>> will be deployed Tomcat clusters. Hence whatever we cache to support
>> pagination, need to be cached in a distributed way for failover support.
>>
>> It (pagination support) is best done at the server side like ROWNUM in
>> SQL or better done in Java driver to hide the internal details and can be
>> optimized better as server sends the paging state with the driver.
>>
>> Thanks
>> Ajay
>> On Feb 12, 2015 8:22 PM, "Eric Stevens" <mi...@gmail.com> wrote:
>>
>>> Your page state then needs to track the last ck1 and last ck2 you saw.
>>> Pages 2+ will end up needing to be up to two queries if the first query
>>> doesn't fill the page size.
>>>
>>> CREATE TABLE foo (
>>> partitionkey int,
>>> ck1 int,
>>> ck2 int,
>>> col1 int,
>>> col2 int,
>>> PRIMARY KEY ((partitionkey), ck1, ck2)
>>> ) WITH CLUSTERING ORDER BY (ck1 asc, ck2 desc);
>>>
>>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,1,1,1);
>>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,2,2,2);
>>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,3,3,3);
>>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,1,4,4);
>>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,2,5,5);
>>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,3,6,6);
>>>
>>> If you're pulling the whole of partition 1 and your page size is 2, your
>>> first page looks like:
>>>
>>> *PAGE 1*
>>>
>>> SELECT * FROM foo WHERE partitionkey = 1 LIMIT 2;
>>> partitionkey | ck1 | ck2 | col1 | col2
>>> --------------+-----+-----+------+------
>>> 1 | 1 | 3 | 3 | 3
>>> 1 | 1 | 2 | 2 | 2
>>>
>>> You got enough rows to satisfy the page, Your page state is taken from
>>> the last row: (ck1=1, ck2=2)
>>>
>>>
>>> *PAGE 2*
>>> Notice that you have a page state, and add some limiting clauses on the
>>> statement:
>>>
>>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 1 AND ck2 < 2 LIMIT 2;
>>> partitionkey | ck1 | ck2 | col1 | col2
>>> --------------+-----+-----+------+------
>>> 1 | 1 | 1 | 1 | 1
>>>
>>> Oops, we didn't get enough rows to satisfy the page limit, so we need to
>>> continue on, we just need one more:
>>>
>>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 1 LIMIT 1;
>>> partitionkey | ck1 | ck2 | col1 | col2
>>> --------------+-----+-----+------+------
>>> 1 | 2 | 3 | 6 | 6
>>>
>>> We have enough to satisfy page 2 now, our new page state: (ck1 = 2, ck2
>>> = 3).
>>>
>>>
>>> *PAGE 3*
>>>
>>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 3 LIMIT 2;
>>> partitionkey | ck1 | ck2 | col1 | col2
>>> --------------+-----+-----+------+------
>>> 1 | 2 | 2 | 5 | 5
>>> 1 | 2 | 1 | 4 | 4
>>>
>>> Great, we satisfied this page with only one query, page state: (ck1 = 2,
>>> ck2 = 1).
>>>
>>>
>>> *PAGE 4*
>>>
>>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 1 LIMIT 2;
>>> (0 rows)
>>>
>>> Oops, our initial query was on the boundary of ck1, but this looks like
>>> any other time that the initial query returns < pageSize rows, we just move
>>> on to the next page:
>>>
>>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 2 LIMIT 2;
>>> (0 rows)
>>>
>>> Aha, we've exhausted ck1 as well, so there are no more pages, page 3
>>> actually pulled the last possible value; page 4 is empty, and we're all
>>> done. Generally speaking you know you're done when your first clustering
>>> key is the only non-equality operator in the statement, and you got no rows
>>> back.
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Feb 11, 2015 at 10:55 AM, Ajay <aj...@gmail.com> wrote:
>>>
>>>> Basically I am trying different queries with your approach.
>>>>
>>>> One such query is like
>>>>
>>>> Select * from mycf where condition on partition key order by ck1 asc,
>>>> ck2 desc where ck1 and ck2 are clustering keys in that order.
>>>>
>>>> Here how do we achieve pagination support?
>>>>
>>>> Thanks
>>>> Ajay
>>>> On Feb 11, 2015 11:16 PM, "Ajay" <aj...@gmail.com> wrote:
>>>>
>>>>>
>>>>> Hi Eric,
>>>>>
>>>>> Thanks for your reply.
>>>>>
>>>>> I am using Cassandra 2.0.11 and in that I cannot append condition like
>>>>> last clustering key column > value of the last row in the previous batch.
>>>>> It fails Preceding column is either not restricted or by a non-EQ relation.
>>>>> It means I need to specify equal condition for all preceding clustering
>>>>> key columns. With this I cannot get the pagination correct.
>>>>>
>>>>> Thanks
>>>>> Ajay
>>>>> > I can't believe that everyone read & process all rows at once
>>>>> (without pagination).
>>>>>
>>>>> Probably not too many people try to read all rows in a table as a
>>>>> single rolling operation with a standard client driver. But those who do
>>>>> would use token() to keep track of where they are and be able to resume
>>>>> with that as well.
>>>>>
>>>>> But it sounds like you're talking about paginating a subset of data -
>>>>> larger than you want to process as a unit, but prefiltered by some other
>>>>> criteria which prevents you from being able to rely on token(). For this
>>>>> there is no general purpose solution, but it typically involves you
>>>>> maintaining your own paging state, typically keeping track of the last
>>>>> partitioning and clustering key seen, and using that to construct your next
>>>>> query.
>>>>>
>>>>> For example, we have client queries which can span several
>>>>> partitioning keys. We make sure that the List of partition keys generated
>>>>> by a given client query List(Pq) is deterministic, then our paging
>>>>> state is the index offset of the final Pq in the response, plus the
>>>>> value of the final clustering column. A query coming in with a paging
>>>>> state attached to it starts the next set of queries from the provided P
>>>>> q offset where clusteringKey > the provided value.
>>>>>
>>>>> So if you can just track partition key offset (if spanning multiple
>>>>> partitions), and clustering key offset, you can construct your next query
>>>>> from those instead.
>>>>>
>>>>> On Tue, Feb 10, 2015 at 6:58 PM, Ajay <aj...@gmail.com> wrote:
>>>>>
>>>>>> Thanks Alex.
>>>>>>
>>>>>> But is there any workaround possible?. I can't believe that everyone
>>>>>> read & process all rows at once (without pagination).
>>>>>>
>>>>>> Thanks
>>>>>> Ajay
>>>>>> On Feb 10, 2015 11:46 PM, "Alex Popescu" <al...@datastax.com> wrote:
>>>>>>
>>>>>>>
>>>>>>> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
>>>>>>>
>>>>>>>> 1) Java driver implicitly support Pagination in the ResultSet
>>>>>>>> (using Iterator) which can be controlled through FetchSize. But it is
>>>>>>>> limited in a way that we cannot skip or go previous. The FetchState is not
>>>>>>>> exposed.
>>>>>>>
>>>>>>>
>>>>>>> Cassandra doesn't support skipping so this is not really a
>>>>>>> limitation of the driver.
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> [:>-a)
>>>>>>>
>>>>>>> Alex Popescu
>>>>>>> Sen. Product Manager @ DataStax
>>>>>>> @al3xandru
>>>>>>>
>>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>>> send an email to java-driver-user+unsubscribe@lists.datastax.com.
>>>>>>>
>>>>>>
>>>>>
>>>
>
>
>
Re: Pagination support on Java Driver Query API
Posted by Bulat Shakirzyanov <bu...@datastax.com>.
Fixed my Mail.app settings so you can see my actual name, sorry.
> On Feb 12, 2015, at 8:55 AM, DataStax <bu...@datastax.com> wrote:
>
> Hello,
>
> As was mentioned earlier, the Java driver doesn’t actually perform pagination.
>
> Instead, it uses cassandra native protocol to set page size of the result set. (https://github.com/apache/cassandra/blob/trunk/doc/native_protocol_v2.spec#L699-L730 <https://github.com/apache/cassandra/blob/trunk/doc/native_protocol_v2.spec#L699-L730>)
> When Cassandra sends the result back to the java driver, it includes a some binary token.
> This token represents paging state. To fetch the next page, the driver re-executes the same
> statement with original page size and paging state attached. If there is another page available,
> Cassandra responds with a new paging state that can be used to fetch it.
>
> You could also try reporting this issue on the Cassandra user mailing list.
>
>> On Feb 12, 2015, at 8:35 AM, Eric Stevens <mightye@gmail.com <ma...@gmail.com>> wrote:
>>
>> I don't know what the shape of the page state data is deep inside the JavaDriver, I've actually tried to dig into that in the past and understand it to see if I could reproduce it as a general purpose any-query kind of thing. I gave up before I fully understood it, but I think it's actually a handle to an in-memory state maintained by the coordinator, which is only maintained for the lifetime of the statement (i.e. it's not stateless paging). That would make it a bad candidate for stateless paging scenarios such as REST requests where a typical setup would load balance across HTTP hosts, never mind across coordinators.
>>
>> It shouldn't be too much work to abstract this basic idea for manual paging into a general purpose class that takes List[ClusteringKeyDef[T, O<:Ordering]], and can produce a connection agnostic PageState from a ResultSet or Row, or accepts a PageState to produce a WHERE CQL fragment.
>>
>>
>>
>> Also RE: possibly multiple queries to satisfy a page - yes, that's unfortunate. Since you're on 2.0.11, see Ondřej's answer to avoid it.
>>
>> On Thu, Feb 12, 2015 at 8:13 AM, Ajay <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
>> Thanks Eric. I figured out the same but didn't get time to put it on the mail. Thanks.
>>
>> But it is highly tied up to how data is stored internally in Cassandra. Basically how partition keys are used to distribute (less likely to change. We are not directly dependence on the partition algo) and clustering keys are used to sort the data with in a partition( multi level sorting and henceforth the restrictions on the ORDER BY clause) which I think can change likely down the lane in Cassandra 3.x or 4.x in an different way for some better storage or retrieval.
>>
>> Thats said I am hesitant to implement this client side logic for pagination for a) 2+ queries might need more than one query to Cassandra. b) tied up implementation to Cassandra internal storage details which can change(though not often). c) in our case, we are building REST Apis which will be deployed Tomcat clusters. Hence whatever we cache to support pagination, need to be cached in a distributed way for failover support.
>>
>> It (pagination support) is best done at the server side like ROWNUM in SQL or better done in Java driver to hide the internal details and can be optimized better as server sends the paging state with the driver.
>>
>> Thanks
>> Ajay
>>
>> On Feb 12, 2015 8:22 PM, "Eric Stevens" <mightye@gmail.com <ma...@gmail.com>> wrote:
>> Your page state then needs to track the last ck1 and last ck2 you saw. Pages 2+ will end up needing to be up to two queries if the first query doesn't fill the page size.
>>
>> CREATE TABLE foo (
>> partitionkey int,
>> ck1 int,
>> ck2 int,
>> col1 int,
>> col2 int,
>> PRIMARY KEY ((partitionkey), ck1, ck2)
>> ) WITH CLUSTERING ORDER BY (ck1 asc, ck2 desc);
>>
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,1,1,1);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,2,2,2);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,3,3,3);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,1,4,4);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,2,5,5);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,3,6,6);
>>
>> If you're pulling the whole of partition 1 and your page size is 2, your first page looks like:
>>
>> PAGE 1
>>
>> SELECT * FROM foo WHERE partitionkey = 1 LIMIT 2;
>> partitionkey | ck1 | ck2 | col1 | col2
>> --------------+-----+-----+------+------
>> 1 | 1 | 3 | 3 | 3
>> 1 | 1 | 2 | 2 | 2
>>
>> You got enough rows to satisfy the page, Your page state is taken from the last row: (ck1=1, ck2=2)
>>
>>
>> PAGE 2
>> Notice that you have a page state, and add some limiting clauses on the statement:
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 1 AND ck2 < 2 LIMIT 2;
>> partitionkey | ck1 | ck2 | col1 | col2
>> --------------+-----+-----+------+------
>> 1 | 1 | 1 | 1 | 1
>>
>> Oops, we didn't get enough rows to satisfy the page limit, so we need to continue on, we just need one more:
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 1 LIMIT 1;
>> partitionkey | ck1 | ck2 | col1 | col2
>> --------------+-----+-----+------+------
>> 1 | 2 | 3 | 6 | 6
>>
>> We have enough to satisfy page 2 now, our new page state: (ck1 = 2, ck2 = 3).
>>
>>
>> PAGE 3
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 3 LIMIT 2;
>> partitionkey | ck1 | ck2 | col1 | col2
>> --------------+-----+-----+------+------
>> 1 | 2 | 2 | 5 | 5
>> 1 | 2 | 1 | 4 | 4
>>
>> Great, we satisfied this page with only one query, page state: (ck1 = 2, ck2 = 1).
>>
>>
>> PAGE 4
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 1 LIMIT 2;
>> (0 rows)
>>
>> Oops, our initial query was on the boundary of ck1, but this looks like any other time that the initial query returns < pageSize rows, we just move on to the next page:
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 2 LIMIT 2;
>> (0 rows)
>>
>> Aha, we've exhausted ck1 as well, so there are no more pages, page 3 actually pulled the last possible value; page 4 is empty, and we're all done. Generally speaking you know you're done when your first clustering key is the only non-equality operator in the statement, and you got no rows back.
>>
>>
>>
>>
>>
>>
>> On Wed, Feb 11, 2015 at 10:55 AM, Ajay <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
>> Basically I am trying different queries with your approach.
>>
>> One such query is like
>>
>> Select * from mycf where condition on partition key order by ck1 asc, ck2 desc where ck1 and ck2 are clustering keys in that order.
>>
>> Here how do we achieve pagination support?
>>
>> Thanks
>> Ajay
>>
>> On Feb 11, 2015 11:16 PM, "Ajay" <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
>>
>> Hi Eric,
>>
>> Thanks for your reply.
>>
>> I am using Cassandra 2.0.11 and in that I cannot append condition like last clustering key column > value of the last row in the previous batch. It fails Preceding column is either not restricted or by a non-EQ relation. It means I need to specify equal condition for all preceding clustering key columns. With this I cannot get the pagination correct.
>>
>> Thanks
>> Ajay
>>
>> > I can't believe that everyone read & process all rows at once (without pagination).
>>
>> Probably not too many people try to read all rows in a table as a single rolling operation with a standard client driver. But those who do would use token() to keep track of where they are and be able to resume with that as well.
>>
>> But it sounds like you're talking about paginating a subset of data - larger than you want to process as a unit, but prefiltered by some other criteria which prevents you from being able to rely on token(). For this there is no general purpose solution, but it typically involves you maintaining your own paging state, typically keeping track of the last partitioning and clustering key seen, and using that to construct your next query.
>>
>> For example, we have client queries which can span several partitioning keys. We make sure that the List of partition keys generated by a given client query List(Pq) is deterministic, then our paging state is the index offset of the final Pq in the response, plus the value of the final clustering column. A query coming in with a paging state attached to it starts the next set of queries from the provided Pq offset where clusteringKey > the provided value.
>>
>> So if you can just track partition key offset (if spanning multiple partitions), and clustering key offset, you can construct your next query from those instead.
>>
>> On Tue, Feb 10, 2015 at 6:58 PM, Ajay <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
>> Thanks Alex.
>>
>> But is there any workaround possible?. I can't believe that everyone read & process all rows at once (without pagination).
>>
>> Thanks
>> Ajay
>>
>> On Feb 10, 2015 11:46 PM, "Alex Popescu" <alexp@datastax.com <ma...@datastax.com>> wrote:
>>
>> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
>> 1) Java driver implicitly support Pagination in the ResultSet (using Iterator) which can be controlled through FetchSize. But it is limited in a way that we cannot skip or go previous. The FetchState is not exposed.
>>
>> Cassandra doesn't support skipping so this is not really a limitation of the driver.
>>
>>
>> --
>>
>> [:>-a)
>>
>> Alex Popescu
>> Sen. Product Manager @ DataStax
>> @al3xandru
>>
>> To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com <ma...@lists.datastax.com>.
>>
>>
>>
>
Re: Pagination support on Java Driver Query API
Posted by DataStax <bu...@datastax.com>.
Hello,
As was mentioned earlier, the Java driver doesn’t actually perform pagination.
Instead, it uses cassandra native protocol to set page size of the result set. (https://github.com/apache/cassandra/blob/trunk/doc/native_protocol_v2.spec#L699-L730 <https://github.com/apache/cassandra/blob/trunk/doc/native_protocol_v2.spec#L699-L730>)
When Cassandra sends the result back to the java driver, it includes a some binary token.
This token represents paging state. To fetch the next page, the driver re-executes the same
statement with original page size and paging state attached. If there is another page available,
Cassandra responds with a new paging state that can be used to fetch it.
You could also try reporting this issue on the Cassandra user mailing list.
> On Feb 12, 2015, at 8:35 AM, Eric Stevens <mi...@gmail.com> wrote:
>
> I don't know what the shape of the page state data is deep inside the JavaDriver, I've actually tried to dig into that in the past and understand it to see if I could reproduce it as a general purpose any-query kind of thing. I gave up before I fully understood it, but I think it's actually a handle to an in-memory state maintained by the coordinator, which is only maintained for the lifetime of the statement (i.e. it's not stateless paging). That would make it a bad candidate for stateless paging scenarios such as REST requests where a typical setup would load balance across HTTP hosts, never mind across coordinators.
>
> It shouldn't be too much work to abstract this basic idea for manual paging into a general purpose class that takes List[ClusteringKeyDef[T, O<:Ordering]], and can produce a connection agnostic PageState from a ResultSet or Row, or accepts a PageState to produce a WHERE CQL fragment.
>
>
>
> Also RE: possibly multiple queries to satisfy a page - yes, that's unfortunate. Since you're on 2.0.11, see Ondřej's answer to avoid it.
>
> On Thu, Feb 12, 2015 at 8:13 AM, Ajay <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
> Thanks Eric. I figured out the same but didn't get time to put it on the mail. Thanks.
>
> But it is highly tied up to how data is stored internally in Cassandra. Basically how partition keys are used to distribute (less likely to change. We are not directly dependence on the partition algo) and clustering keys are used to sort the data with in a partition( multi level sorting and henceforth the restrictions on the ORDER BY clause) which I think can change likely down the lane in Cassandra 3.x or 4.x in an different way for some better storage or retrieval.
>
> Thats said I am hesitant to implement this client side logic for pagination for a) 2+ queries might need more than one query to Cassandra. b) tied up implementation to Cassandra internal storage details which can change(though not often). c) in our case, we are building REST Apis which will be deployed Tomcat clusters. Hence whatever we cache to support pagination, need to be cached in a distributed way for failover support.
>
> It (pagination support) is best done at the server side like ROWNUM in SQL or better done in Java driver to hide the internal details and can be optimized better as server sends the paging state with the driver.
>
> Thanks
> Ajay
>
> On Feb 12, 2015 8:22 PM, "Eric Stevens" <mightye@gmail.com <ma...@gmail.com>> wrote:
> Your page state then needs to track the last ck1 and last ck2 you saw. Pages 2+ will end up needing to be up to two queries if the first query doesn't fill the page size.
>
> CREATE TABLE foo (
> partitionkey int,
> ck1 int,
> ck2 int,
> col1 int,
> col2 int,
> PRIMARY KEY ((partitionkey), ck1, ck2)
> ) WITH CLUSTERING ORDER BY (ck1 asc, ck2 desc);
>
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,1,1,1);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,2,2,2);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,3,3,3);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,1,4,4);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,2,5,5);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,3,6,6);
>
> If you're pulling the whole of partition 1 and your page size is 2, your first page looks like:
>
> PAGE 1
>
> SELECT * FROM foo WHERE partitionkey = 1 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 1 | 3 | 3 | 3
> 1 | 1 | 2 | 2 | 2
>
> You got enough rows to satisfy the page, Your page state is taken from the last row: (ck1=1, ck2=2)
>
>
> PAGE 2
> Notice that you have a page state, and add some limiting clauses on the statement:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 1 AND ck2 < 2 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 1 | 1 | 1 | 1
>
> Oops, we didn't get enough rows to satisfy the page limit, so we need to continue on, we just need one more:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 1 LIMIT 1;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 2 | 3 | 6 | 6
>
> We have enough to satisfy page 2 now, our new page state: (ck1 = 2, ck2 = 3).
>
>
> PAGE 3
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 3 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 2 | 2 | 5 | 5
> 1 | 2 | 1 | 4 | 4
>
> Great, we satisfied this page with only one query, page state: (ck1 = 2, ck2 = 1).
>
>
> PAGE 4
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 1 LIMIT 2;
> (0 rows)
>
> Oops, our initial query was on the boundary of ck1, but this looks like any other time that the initial query returns < pageSize rows, we just move on to the next page:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 2 LIMIT 2;
> (0 rows)
>
> Aha, we've exhausted ck1 as well, so there are no more pages, page 3 actually pulled the last possible value; page 4 is empty, and we're all done. Generally speaking you know you're done when your first clustering key is the only non-equality operator in the statement, and you got no rows back.
>
>
>
>
>
>
> On Wed, Feb 11, 2015 at 10:55 AM, Ajay <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
> Basically I am trying different queries with your approach.
>
> One such query is like
>
> Select * from mycf where condition on partition key order by ck1 asc, ck2 desc where ck1 and ck2 are clustering keys in that order.
>
> Here how do we achieve pagination support?
>
> Thanks
> Ajay
>
> On Feb 11, 2015 11:16 PM, "Ajay" <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
>
> Hi Eric,
>
> Thanks for your reply.
>
> I am using Cassandra 2.0.11 and in that I cannot append condition like last clustering key column > value of the last row in the previous batch. It fails Preceding column is either not restricted or by a non-EQ relation. It means I need to specify equal condition for all preceding clustering key columns. With this I cannot get the pagination correct.
>
> Thanks
> Ajay
>
> > I can't believe that everyone read & process all rows at once (without pagination).
>
> Probably not too many people try to read all rows in a table as a single rolling operation with a standard client driver. But those who do would use token() to keep track of where they are and be able to resume with that as well.
>
> But it sounds like you're talking about paginating a subset of data - larger than you want to process as a unit, but prefiltered by some other criteria which prevents you from being able to rely on token(). For this there is no general purpose solution, but it typically involves you maintaining your own paging state, typically keeping track of the last partitioning and clustering key seen, and using that to construct your next query.
>
> For example, we have client queries which can span several partitioning keys. We make sure that the List of partition keys generated by a given client query List(Pq) is deterministic, then our paging state is the index offset of the final Pq in the response, plus the value of the final clustering column. A query coming in with a paging state attached to it starts the next set of queries from the provided Pq offset where clusteringKey > the provided value.
>
> So if you can just track partition key offset (if spanning multiple partitions), and clustering key offset, you can construct your next query from those instead.
>
> On Tue, Feb 10, 2015 at 6:58 PM, Ajay <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
> Thanks Alex.
>
> But is there any workaround possible?. I can't believe that everyone read & process all rows at once (without pagination).
>
> Thanks
> Ajay
>
> On Feb 10, 2015 11:46 PM, "Alex Popescu" <alexp@datastax.com <ma...@datastax.com>> wrote:
>
> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <ajay.garga@gmail.com <ma...@gmail.com>> wrote:
> 1) Java driver implicitly support Pagination in the ResultSet (using Iterator) which can be controlled through FetchSize. But it is limited in a way that we cannot skip or go previous. The FetchState is not exposed.
>
> Cassandra doesn't support skipping so this is not really a limitation of the driver.
>
>
> --
>
> [:>-a)
>
> Alex Popescu
> Sen. Product Manager @ DataStax
> @al3xandru
>
> To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com <ma...@lists.datastax.com>.
>
>
>
Re: Pagination support on Java Driver Query API
Posted by Eric Stevens <mi...@gmail.com>.
I don't know what the shape of the page state data is deep inside the
JavaDriver, I've actually tried to dig into that in the past and understand
it to see if I could reproduce it as a general purpose any-query kind of
thing. I gave up before I fully understood it, but I think it's actually a
handle to an in-memory state maintained by the coordinator, which is only
maintained for the lifetime of the statement (i.e. it's not stateless
paging). That would make it a bad candidate for stateless paging scenarios
such as REST requests where a typical setup would load balance across HTTP
hosts, never mind across coordinators.
It shouldn't be too much work to abstract this basic idea for manual paging
into a general purpose class that takes List[ClusteringKeyDef[T,
O<:Ordering]], and can produce a connection agnostic PageState from a
ResultSet or Row, or accepts a PageState to produce a WHERE CQL fragment.
Also RE: possibly multiple queries to satisfy a page - yes, that's
unfortunate. Since you're on 2.0.11, see Ondřej's answer to avoid it.
On Thu, Feb 12, 2015 at 8:13 AM, Ajay <aj...@gmail.com> wrote:
> Thanks Eric. I figured out the same but didn't get time to put it on the
> mail. Thanks.
>
> But it is highly tied up to how data is stored internally in Cassandra.
> Basically how partition keys are used to distribute (less likely to change.
> We are not directly dependence on the partition algo) and clustering keys
> are used to sort the data with in a partition( multi level sorting and
> henceforth the restrictions on the ORDER BY clause) which I think can
> change likely down the lane in Cassandra 3.x or 4.x in an different way for
> some better storage or retrieval.
>
> Thats said I am hesitant to implement this client side logic for
> pagination for a) 2+ queries might need more than one query to Cassandra.
> b) tied up implementation to Cassandra internal storage details which can
> change(though not often). c) in our case, we are building REST Apis which
> will be deployed Tomcat clusters. Hence whatever we cache to support
> pagination, need to be cached in a distributed way for failover support.
>
> It (pagination support) is best done at the server side like ROWNUM in SQL
> or better done in Java driver to hide the internal details and can be
> optimized better as server sends the paging state with the driver.
>
> Thanks
> Ajay
> On Feb 12, 2015 8:22 PM, "Eric Stevens" <mi...@gmail.com> wrote:
>
>> Your page state then needs to track the last ck1 and last ck2 you saw.
>> Pages 2+ will end up needing to be up to two queries if the first query
>> doesn't fill the page size.
>>
>> CREATE TABLE foo (
>> partitionkey int,
>> ck1 int,
>> ck2 int,
>> col1 int,
>> col2 int,
>> PRIMARY KEY ((partitionkey), ck1, ck2)
>> ) WITH CLUSTERING ORDER BY (ck1 asc, ck2 desc);
>>
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,1,1,1);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,2,2,2);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,3,3,3);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,1,4,4);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,2,5,5);
>> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,3,6,6);
>>
>> If you're pulling the whole of partition 1 and your page size is 2, your
>> first page looks like:
>>
>> *PAGE 1*
>>
>> SELECT * FROM foo WHERE partitionkey = 1 LIMIT 2;
>> partitionkey | ck1 | ck2 | col1 | col2
>> --------------+-----+-----+------+------
>> 1 | 1 | 3 | 3 | 3
>> 1 | 1 | 2 | 2 | 2
>>
>> You got enough rows to satisfy the page, Your page state is taken from
>> the last row: (ck1=1, ck2=2)
>>
>>
>> *PAGE 2*
>> Notice that you have a page state, and add some limiting clauses on the
>> statement:
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 1 AND ck2 < 2 LIMIT 2;
>> partitionkey | ck1 | ck2 | col1 | col2
>> --------------+-----+-----+------+------
>> 1 | 1 | 1 | 1 | 1
>>
>> Oops, we didn't get enough rows to satisfy the page limit, so we need to
>> continue on, we just need one more:
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 1 LIMIT 1;
>> partitionkey | ck1 | ck2 | col1 | col2
>> --------------+-----+-----+------+------
>> 1 | 2 | 3 | 6 | 6
>>
>> We have enough to satisfy page 2 now, our new page state: (ck1 = 2, ck2 =
>> 3).
>>
>>
>> *PAGE 3*
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 3 LIMIT 2;
>> partitionkey | ck1 | ck2 | col1 | col2
>> --------------+-----+-----+------+------
>> 1 | 2 | 2 | 5 | 5
>> 1 | 2 | 1 | 4 | 4
>>
>> Great, we satisfied this page with only one query, page state: (ck1 = 2,
>> ck2 = 1).
>>
>>
>> *PAGE 4*
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 1 LIMIT 2;
>> (0 rows)
>>
>> Oops, our initial query was on the boundary of ck1, but this looks like
>> any other time that the initial query returns < pageSize rows, we just move
>> on to the next page:
>>
>> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 2 LIMIT 2;
>> (0 rows)
>>
>> Aha, we've exhausted ck1 as well, so there are no more pages, page 3
>> actually pulled the last possible value; page 4 is empty, and we're all
>> done. Generally speaking you know you're done when your first clustering
>> key is the only non-equality operator in the statement, and you got no rows
>> back.
>>
>>
>>
>>
>>
>>
>> On Wed, Feb 11, 2015 at 10:55 AM, Ajay <aj...@gmail.com> wrote:
>>
>>> Basically I am trying different queries with your approach.
>>>
>>> One such query is like
>>>
>>> Select * from mycf where condition on partition key order by ck1 asc,
>>> ck2 desc where ck1 and ck2 are clustering keys in that order.
>>>
>>> Here how do we achieve pagination support?
>>>
>>> Thanks
>>> Ajay
>>> On Feb 11, 2015 11:16 PM, "Ajay" <aj...@gmail.com> wrote:
>>>
>>>>
>>>> Hi Eric,
>>>>
>>>> Thanks for your reply.
>>>>
>>>> I am using Cassandra 2.0.11 and in that I cannot append condition like
>>>> last clustering key column > value of the last row in the previous batch.
>>>> It fails Preceding column is either not restricted or by a non-EQ relation.
>>>> It means I need to specify equal condition for all preceding clustering
>>>> key columns. With this I cannot get the pagination correct.
>>>>
>>>> Thanks
>>>> Ajay
>>>> > I can't believe that everyone read & process all rows at once
>>>> (without pagination).
>>>>
>>>> Probably not too many people try to read all rows in a table as a
>>>> single rolling operation with a standard client driver. But those who do
>>>> would use token() to keep track of where they are and be able to resume
>>>> with that as well.
>>>>
>>>> But it sounds like you're talking about paginating a subset of data -
>>>> larger than you want to process as a unit, but prefiltered by some other
>>>> criteria which prevents you from being able to rely on token(). For this
>>>> there is no general purpose solution, but it typically involves you
>>>> maintaining your own paging state, typically keeping track of the last
>>>> partitioning and clustering key seen, and using that to construct your next
>>>> query.
>>>>
>>>> For example, we have client queries which can span several partitioning
>>>> keys. We make sure that the List of partition keys generated by a given
>>>> client query List(Pq) is deterministic, then our paging state is the
>>>> index offset of the final Pq in the response, plus the value of the
>>>> final clustering column. A query coming in with a paging state attached to
>>>> it starts the next set of queries from the provided Pq offset where
>>>> clusteringKey > the provided value.
>>>>
>>>> So if you can just track partition key offset (if spanning multiple
>>>> partitions), and clustering key offset, you can construct your next query
>>>> from those instead.
>>>>
>>>> On Tue, Feb 10, 2015 at 6:58 PM, Ajay <aj...@gmail.com> wrote:
>>>>
>>>>> Thanks Alex.
>>>>>
>>>>> But is there any workaround possible?. I can't believe that everyone
>>>>> read & process all rows at once (without pagination).
>>>>>
>>>>> Thanks
>>>>> Ajay
>>>>> On Feb 10, 2015 11:46 PM, "Alex Popescu" <al...@datastax.com> wrote:
>>>>>
>>>>>>
>>>>>> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
>>>>>>
>>>>>>> 1) Java driver implicitly support Pagination in the ResultSet (using
>>>>>>> Iterator) which can be controlled through FetchSize. But it is limited in a
>>>>>>> way that we cannot skip or go previous. The FetchState is not exposed.
>>>>>>
>>>>>>
>>>>>> Cassandra doesn't support skipping so this is not really a limitation
>>>>>> of the driver.
>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>> [:>-a)
>>>>>>
>>>>>> Alex Popescu
>>>>>> Sen. Product Manager @ DataStax
>>>>>> @al3xandru
>>>>>>
>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>> send an email to java-driver-user+unsubscribe@lists.datastax.com.
>>>>>>
>>>>>
>>>>
>>
Re: Pagination support on Java Driver Query API
Posted by Ajay <aj...@gmail.com>.
Thanks Eric. I figured out the same but didn't get time to put it on the
mail. Thanks.
But it is highly tied up to how data is stored internally in Cassandra.
Basically how partition keys are used to distribute (less likely to change.
We are not directly dependence on the partition algo) and clustering keys
are used to sort the data with in a partition( multi level sorting and
henceforth the restrictions on the ORDER BY clause) which I think can
change likely down the lane in Cassandra 3.x or 4.x in an different way for
some better storage or retrieval.
Thats said I am hesitant to implement this client side logic for pagination
for a) 2+ queries might need more than one query to Cassandra. b) tied up
implementation to Cassandra internal storage details which can
change(though not often). c) in our case, we are building REST Apis which
will be deployed Tomcat clusters. Hence whatever we cache to support
pagination, need to be cached in a distributed way for failover support.
It (pagination support) is best done at the server side like ROWNUM in SQL
or better done in Java driver to hide the internal details and can be
optimized better as server sends the paging state with the driver.
Thanks
Ajay
On Feb 12, 2015 8:22 PM, "Eric Stevens" <mi...@gmail.com> wrote:
> Your page state then needs to track the last ck1 and last ck2 you saw.
> Pages 2+ will end up needing to be up to two queries if the first query
> doesn't fill the page size.
>
> CREATE TABLE foo (
> partitionkey int,
> ck1 int,
> ck2 int,
> col1 int,
> col2 int,
> PRIMARY KEY ((partitionkey), ck1, ck2)
> ) WITH CLUSTERING ORDER BY (ck1 asc, ck2 desc);
>
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,1,1,1);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,2,2,2);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,3,3,3);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,1,4,4);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,2,5,5);
> INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,3,6,6);
>
> If you're pulling the whole of partition 1 and your page size is 2, your
> first page looks like:
>
> *PAGE 1*
>
> SELECT * FROM foo WHERE partitionkey = 1 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 1 | 3 | 3 | 3
> 1 | 1 | 2 | 2 | 2
>
> You got enough rows to satisfy the page, Your page state is taken from the
> last row: (ck1=1, ck2=2)
>
>
> *PAGE 2*
> Notice that you have a page state, and add some limiting clauses on the
> statement:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 1 AND ck2 < 2 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 1 | 1 | 1 | 1
>
> Oops, we didn't get enough rows to satisfy the page limit, so we need to
> continue on, we just need one more:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 1 LIMIT 1;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 2 | 3 | 6 | 6
>
> We have enough to satisfy page 2 now, our new page state: (ck1 = 2, ck2 =
> 3).
>
>
> *PAGE 3*
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 3 LIMIT 2;
> partitionkey | ck1 | ck2 | col1 | col2
> --------------+-----+-----+------+------
> 1 | 2 | 2 | 5 | 5
> 1 | 2 | 1 | 4 | 4
>
> Great, we satisfied this page with only one query, page state: (ck1 = 2,
> ck2 = 1).
>
>
> *PAGE 4*
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 1 LIMIT 2;
> (0 rows)
>
> Oops, our initial query was on the boundary of ck1, but this looks like
> any other time that the initial query returns < pageSize rows, we just move
> on to the next page:
>
> SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 2 LIMIT 2;
> (0 rows)
>
> Aha, we've exhausted ck1 as well, so there are no more pages, page 3
> actually pulled the last possible value; page 4 is empty, and we're all
> done. Generally speaking you know you're done when your first clustering
> key is the only non-equality operator in the statement, and you got no rows
> back.
>
>
>
>
>
>
> On Wed, Feb 11, 2015 at 10:55 AM, Ajay <aj...@gmail.com> wrote:
>
>> Basically I am trying different queries with your approach.
>>
>> One such query is like
>>
>> Select * from mycf where condition on partition key order by ck1 asc, ck2
>> desc where ck1 and ck2 are clustering keys in that order.
>>
>> Here how do we achieve pagination support?
>>
>> Thanks
>> Ajay
>> On Feb 11, 2015 11:16 PM, "Ajay" <aj...@gmail.com> wrote:
>>
>>>
>>> Hi Eric,
>>>
>>> Thanks for your reply.
>>>
>>> I am using Cassandra 2.0.11 and in that I cannot append condition like
>>> last clustering key column > value of the last row in the previous batch.
>>> It fails Preceding column is either not restricted or by a non-EQ relation.
>>> It means I need to specify equal condition for all preceding clustering
>>> key columns. With this I cannot get the pagination correct.
>>>
>>> Thanks
>>> Ajay
>>> > I can't believe that everyone read & process all rows at once
>>> (without pagination).
>>>
>>> Probably not too many people try to read all rows in a table as a single
>>> rolling operation with a standard client driver. But those who do would
>>> use token() to keep track of where they are and be able to resume with that
>>> as well.
>>>
>>> But it sounds like you're talking about paginating a subset of data -
>>> larger than you want to process as a unit, but prefiltered by some other
>>> criteria which prevents you from being able to rely on token(). For this
>>> there is no general purpose solution, but it typically involves you
>>> maintaining your own paging state, typically keeping track of the last
>>> partitioning and clustering key seen, and using that to construct your next
>>> query.
>>>
>>> For example, we have client queries which can span several partitioning
>>> keys. We make sure that the List of partition keys generated by a given
>>> client query List(Pq) is deterministic, then our paging state is the
>>> index offset of the final Pq in the response, plus the value of the
>>> final clustering column. A query coming in with a paging state attached to
>>> it starts the next set of queries from the provided Pq offset where
>>> clusteringKey > the provided value.
>>>
>>> So if you can just track partition key offset (if spanning multiple
>>> partitions), and clustering key offset, you can construct your next query
>>> from those instead.
>>>
>>> On Tue, Feb 10, 2015 at 6:58 PM, Ajay <aj...@gmail.com> wrote:
>>>
>>>> Thanks Alex.
>>>>
>>>> But is there any workaround possible?. I can't believe that everyone
>>>> read & process all rows at once (without pagination).
>>>>
>>>> Thanks
>>>> Ajay
>>>> On Feb 10, 2015 11:46 PM, "Alex Popescu" <al...@datastax.com> wrote:
>>>>
>>>>>
>>>>> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
>>>>>
>>>>>> 1) Java driver implicitly support Pagination in the ResultSet (using
>>>>>> Iterator) which can be controlled through FetchSize. But it is limited in a
>>>>>> way that we cannot skip or go previous. The FetchState is not exposed.
>>>>>
>>>>>
>>>>> Cassandra doesn't support skipping so this is not really a limitation
>>>>> of the driver.
>>>>>
>>>>>
>>>>> --
>>>>>
>>>>> [:>-a)
>>>>>
>>>>> Alex Popescu
>>>>> Sen. Product Manager @ DataStax
>>>>> @al3xandru
>>>>>
>>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>>> an email to java-driver-user+unsubscribe@lists.datastax.com.
>>>>>
>>>>
>>>
>
Re: Pagination support on Java Driver Query API
Posted by Eric Stevens <mi...@gmail.com>.
Your page state then needs to track the last ck1 and last ck2 you saw.
Pages 2+ will end up needing to be up to two queries if the first query
doesn't fill the page size.
CREATE TABLE foo (
partitionkey int,
ck1 int,
ck2 int,
col1 int,
col2 int,
PRIMARY KEY ((partitionkey), ck1, ck2)
) WITH CLUSTERING ORDER BY (ck1 asc, ck2 desc);
INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,1,1,1);
INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,2,2,2);
INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,1,3,3,3);
INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,1,4,4);
INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,2,5,5);
INSERT INTO foo (partitionkey, ck1, ck2, col1, col2) VALUES (1,2,3,6,6);
If you're pulling the whole of partition 1 and your page size is 2, your
first page looks like:
*PAGE 1*
SELECT * FROM foo WHERE partitionkey = 1 LIMIT 2;
partitionkey | ck1 | ck2 | col1 | col2
--------------+-----+-----+------+------
1 | 1 | 3 | 3 | 3
1 | 1 | 2 | 2 | 2
You got enough rows to satisfy the page, Your page state is taken from the
last row: (ck1=1, ck2=2)
*PAGE 2*
Notice that you have a page state, and add some limiting clauses on the
statement:
SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 1 AND ck2 < 2 LIMIT 2;
partitionkey | ck1 | ck2 | col1 | col2
--------------+-----+-----+------+------
1 | 1 | 1 | 1 | 1
Oops, we didn't get enough rows to satisfy the page limit, so we need to
continue on, we just need one more:
SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 1 LIMIT 1;
partitionkey | ck1 | ck2 | col1 | col2
--------------+-----+-----+------+------
1 | 2 | 3 | 6 | 6
We have enough to satisfy page 2 now, our new page state: (ck1 = 2, ck2 =
3).
*PAGE 3*
SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 3 LIMIT 2;
partitionkey | ck1 | ck2 | col1 | col2
--------------+-----+-----+------+------
1 | 2 | 2 | 5 | 5
1 | 2 | 1 | 4 | 4
Great, we satisfied this page with only one query, page state: (ck1 = 2,
ck2 = 1).
*PAGE 4*
SELECT * FROM foo WHERE partitionkey = 1 AND ck1 = 2 AND ck2 < 1 LIMIT 2;
(0 rows)
Oops, our initial query was on the boundary of ck1, but this looks like any
other time that the initial query returns < pageSize rows, we just move on
to the next page:
SELECT * FROM foo WHERE partitionkey = 1 AND ck1 > 2 LIMIT 2;
(0 rows)
Aha, we've exhausted ck1 as well, so there are no more pages, page 3
actually pulled the last possible value; page 4 is empty, and we're all
done. Generally speaking you know you're done when your first clustering
key is the only non-equality operator in the statement, and you got no rows
back.
On Wed, Feb 11, 2015 at 10:55 AM, Ajay <aj...@gmail.com> wrote:
> Basically I am trying different queries with your approach.
>
> One such query is like
>
> Select * from mycf where condition on partition key order by ck1 asc, ck2
> desc where ck1 and ck2 are clustering keys in that order.
>
> Here how do we achieve pagination support?
>
> Thanks
> Ajay
> On Feb 11, 2015 11:16 PM, "Ajay" <aj...@gmail.com> wrote:
>
>>
>> Hi Eric,
>>
>> Thanks for your reply.
>>
>> I am using Cassandra 2.0.11 and in that I cannot append condition like
>> last clustering key column > value of the last row in the previous batch.
>> It fails Preceding column is either not restricted or by a non-EQ relation.
>> It means I need to specify equal condition for all preceding clustering
>> key columns. With this I cannot get the pagination correct.
>>
>> Thanks
>> Ajay
>> > I can't believe that everyone read & process all rows at once (without
>> pagination).
>>
>> Probably not too many people try to read all rows in a table as a single
>> rolling operation with a standard client driver. But those who do would
>> use token() to keep track of where they are and be able to resume with that
>> as well.
>>
>> But it sounds like you're talking about paginating a subset of data -
>> larger than you want to process as a unit, but prefiltered by some other
>> criteria which prevents you from being able to rely on token(). For this
>> there is no general purpose solution, but it typically involves you
>> maintaining your own paging state, typically keeping track of the last
>> partitioning and clustering key seen, and using that to construct your next
>> query.
>>
>> For example, we have client queries which can span several partitioning
>> keys. We make sure that the List of partition keys generated by a given
>> client query List(Pq) is deterministic, then our paging state is the
>> index offset of the final Pq in the response, plus the value of the
>> final clustering column. A query coming in with a paging state attached to
>> it starts the next set of queries from the provided Pq offset where
>> clusteringKey > the provided value.
>>
>> So if you can just track partition key offset (if spanning multiple
>> partitions), and clustering key offset, you can construct your next query
>> from those instead.
>>
>> On Tue, Feb 10, 2015 at 6:58 PM, Ajay <aj...@gmail.com> wrote:
>>
>>> Thanks Alex.
>>>
>>> But is there any workaround possible?. I can't believe that everyone
>>> read & process all rows at once (without pagination).
>>>
>>> Thanks
>>> Ajay
>>> On Feb 10, 2015 11:46 PM, "Alex Popescu" <al...@datastax.com> wrote:
>>>
>>>>
>>>> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
>>>>
>>>>> 1) Java driver implicitly support Pagination in the ResultSet (using
>>>>> Iterator) which can be controlled through FetchSize. But it is limited in a
>>>>> way that we cannot skip or go previous. The FetchState is not exposed.
>>>>
>>>>
>>>> Cassandra doesn't support skipping so this is not really a limitation
>>>> of the driver.
>>>>
>>>>
>>>> --
>>>>
>>>> [:>-a)
>>>>
>>>> Alex Popescu
>>>> Sen. Product Manager @ DataStax
>>>> @al3xandru
>>>>
>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>> an email to java-driver-user+unsubscribe@lists.datastax.com.
>>>>
>>>
>>
Re: Pagination support on Java Driver Query API
Posted by Ajay <aj...@gmail.com>.
Basically I am trying different queries with your approach.
One such query is like
Select * from mycf where condition on partition key order by ck1 asc, ck2
desc where ck1 and ck2 are clustering keys in that order.
Here how do we achieve pagination support?
Thanks
Ajay
On Feb 11, 2015 11:16 PM, "Ajay" <aj...@gmail.com> wrote:
>
> Hi Eric,
>
> Thanks for your reply.
>
> I am using Cassandra 2.0.11 and in that I cannot append condition like
> last clustering key column > value of the last row in the previous batch.
> It fails Preceding column is either not restricted or by a non-EQ relation.
> It means I need to specify equal condition for all preceding clustering
> key columns. With this I cannot get the pagination correct.
>
> Thanks
> Ajay
> > I can't believe that everyone read & process all rows at once (without
> pagination).
>
> Probably not too many people try to read all rows in a table as a single
> rolling operation with a standard client driver. But those who do would
> use token() to keep track of where they are and be able to resume with that
> as well.
>
> But it sounds like you're talking about paginating a subset of data -
> larger than you want to process as a unit, but prefiltered by some other
> criteria which prevents you from being able to rely on token(). For this
> there is no general purpose solution, but it typically involves you
> maintaining your own paging state, typically keeping track of the last
> partitioning and clustering key seen, and using that to construct your next
> query.
>
> For example, we have client queries which can span several partitioning
> keys. We make sure that the List of partition keys generated by a given
> client query List(Pq) is deterministic, then our paging state is the
> index offset of the final Pq in the response, plus the value of the final
> clustering column. A query coming in with a paging state attached to it
> starts the next set of queries from the provided Pq offset where
> clusteringKey > the provided value.
>
> So if you can just track partition key offset (if spanning multiple
> partitions), and clustering key offset, you can construct your next query
> from those instead.
>
> On Tue, Feb 10, 2015 at 6:58 PM, Ajay <aj...@gmail.com> wrote:
>
>> Thanks Alex.
>>
>> But is there any workaround possible?. I can't believe that everyone read
>> & process all rows at once (without pagination).
>>
>> Thanks
>> Ajay
>> On Feb 10, 2015 11:46 PM, "Alex Popescu" <al...@datastax.com> wrote:
>>
>>>
>>> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
>>>
>>>> 1) Java driver implicitly support Pagination in the ResultSet (using
>>>> Iterator) which can be controlled through FetchSize. But it is limited in a
>>>> way that we cannot skip or go previous. The FetchState is not exposed.
>>>
>>>
>>> Cassandra doesn't support skipping so this is not really a limitation of
>>> the driver.
>>>
>>>
>>> --
>>>
>>> [:>-a)
>>>
>>> Alex Popescu
>>> Sen. Product Manager @ DataStax
>>> @al3xandru
>>>
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to java-driver-user+unsubscribe@lists.datastax.com.
>>>
>>
>
Re: Pagination support on Java Driver Query API
Posted by Ajay <aj...@gmail.com>.
Hi Eric,
Thanks for your reply.
I am using Cassandra 2.0.11 and in that I cannot append condition like last
clustering key column > value of the last row in the previous batch. It
fails Preceding column is either not restricted or by a non-EQ relation. It
means I need to specify equal condition for all preceding clustering key
columns. With this I cannot get the pagination correct.
Thanks
Ajay
> I can't believe that everyone read & process all rows at once (without
pagination).
Probably not too many people try to read all rows in a table as a single
rolling operation with a standard client driver. But those who do would
use token() to keep track of where they are and be able to resume with that
as well.
But it sounds like you're talking about paginating a subset of data -
larger than you want to process as a unit, but prefiltered by some other
criteria which prevents you from being able to rely on token(). For this
there is no general purpose solution, but it typically involves you
maintaining your own paging state, typically keeping track of the last
partitioning and clustering key seen, and using that to construct your next
query.
For example, we have client queries which can span several partitioning
keys. We make sure that the List of partition keys generated by a given
client query List(Pq) is deterministic, then our paging state is the index
offset of the final Pq in the response, plus the value of the final
clustering column. A query coming in with a paging state attached to it
starts the next set of queries from the provided Pq offset where
clusteringKey > the provided value.
So if you can just track partition key offset (if spanning multiple
partitions), and clustering key offset, you can construct your next query
from those instead.
On Tue, Feb 10, 2015 at 6:58 PM, Ajay <aj...@gmail.com> wrote:
> Thanks Alex.
>
> But is there any workaround possible?. I can't believe that everyone read
> & process all rows at once (without pagination).
>
> Thanks
> Ajay
> On Feb 10, 2015 11:46 PM, "Alex Popescu" <al...@datastax.com> wrote:
>
>>
>> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
>>
>>> 1) Java driver implicitly support Pagination in the ResultSet (using
>>> Iterator) which can be controlled through FetchSize. But it is limited in a
>>> way that we cannot skip or go previous. The FetchState is not exposed.
>>
>>
>> Cassandra doesn't support skipping so this is not really a limitation of
>> the driver.
>>
>>
>> --
>>
>> [:>-a)
>>
>> Alex Popescu
>> Sen. Product Manager @ DataStax
>> @al3xandru
>>
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to java-driver-user+unsubscribe@lists.datastax.com.
>>
>
Re: Pagination support on Java Driver Query API
Posted by Eric Stevens <mi...@gmail.com>.
> I can't believe that everyone read & process all rows at once (without
pagination).
Probably not too many people try to read all rows in a table as a single
rolling operation with a standard client driver. But those who do would
use token() to keep track of where they are and be able to resume with that
as well.
But it sounds like you're talking about paginating a subset of data -
larger than you want to process as a unit, but prefiltered by some other
criteria which prevents you from being able to rely on token(). For this
there is no general purpose solution, but it typically involves you
maintaining your own paging state, typically keeping track of the last
partitioning and clustering key seen, and using that to construct your next
query.
For example, we have client queries which can span several partitioning
keys. We make sure that the List of partition keys generated by a given
client query List(Pq) is deterministic, then our paging state is the index
offset of the final Pq in the response, plus the value of the final
clustering column. A query coming in with a paging state attached to it
starts the next set of queries from the provided Pq offset where
clusteringKey > the provided value.
So if you can just track partition key offset (if spanning multiple
partitions), and clustering key offset, you can construct your next query
from those instead.
On Tue, Feb 10, 2015 at 6:58 PM, Ajay <aj...@gmail.com> wrote:
> Thanks Alex.
>
> But is there any workaround possible?. I can't believe that everyone read
> & process all rows at once (without pagination).
>
> Thanks
> Ajay
> On Feb 10, 2015 11:46 PM, "Alex Popescu" <al...@datastax.com> wrote:
>
>>
>> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
>>
>>> 1) Java driver implicitly support Pagination in the ResultSet (using
>>> Iterator) which can be controlled through FetchSize. But it is limited in a
>>> way that we cannot skip or go previous. The FetchState is not exposed.
>>
>>
>> Cassandra doesn't support skipping so this is not really a limitation of
>> the driver.
>>
>>
>> --
>>
>> [:>-a)
>>
>> Alex Popescu
>> Sen. Product Manager @ DataStax
>> @al3xandru
>>
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to java-driver-user+unsubscribe@lists.datastax.com.
>>
>
Re: Pagination support on Java Driver Query API
Posted by Ajay <aj...@gmail.com>.
Thanks Alex.
But is there any workaround possible?. I can't believe that everyone read &
process all rows at once (without pagination).
Thanks
Ajay
On Feb 10, 2015 11:46 PM, "Alex Popescu" <al...@datastax.com> wrote:
>
> On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
>
>> 1) Java driver implicitly support Pagination in the ResultSet (using
>> Iterator) which can be controlled through FetchSize. But it is limited in a
>> way that we cannot skip or go previous. The FetchState is not exposed.
>
>
> Cassandra doesn't support skipping so this is not really a limitation of
> the driver.
>
>
> --
>
> [:>-a)
>
> Alex Popescu
> Sen. Product Manager @ DataStax
> @al3xandru
>
> To unsubscribe from this group and stop receiving emails from it, send an
> email to java-driver-user+unsubscribe@lists.datastax.com.
>
Re: Pagination support on Java Driver Query API
Posted by Alex Popescu <al...@datastax.com>.
On Tue, Feb 10, 2015 at 4:59 AM, Ajay <aj...@gmail.com> wrote:
> 1) Java driver implicitly support Pagination in the ResultSet (using
> Iterator) which can be controlled through FetchSize. But it is limited in a
> way that we cannot skip or go previous. The FetchState is not exposed.
Cassandra doesn't support skipping so this is not really a limitation of
the driver.
--
[:>-a)
Alex Popescu
Sen. Product Manager @ DataStax
@al3xandru