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.shakirzya...@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 <bulat.shakirzya...@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 <migh...@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.ga...@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" <migh...@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.ga...@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.ga...@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 <ajay.ga...@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 <ajay.ga...@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+unsubscr...@lists.datastax.com. >>>>>>> >>>>>> >>>>> >>> > > >
