Help needed with pagination in Cassandra - java

[hemabs]

Hi All,

I am trying to implement pagination for the select queries using Cassandra in Java.

As of now I have completed the below and stuck at this point.

   Select selectSt=QueryBuilder.select().from("person");

   selectSt.setFetchSize(2);

   ResultSet results = cassandraOps.query(selectSt);

   ListenableFuture<Void> future = null;

   if(!results.isFullyFetched())

      future = results.fetchMoreResults();

I have no clue how to proceed from here. Can anyone provide me with inputs.

Appreciate the help.

Thanks

hbs

[Olivier Michallat]

Hi,

Calling `fetchMoreResults` manually is not required, it will be done automatically if you simply iterate over the ResultSet:

    for (Row row : results) {

        // do something with row

    }

The only reason to use it is if you want to prefetch rows in advance. One use case that comes to mind is if you displayed the rows in some kind of scrolling UI, and wanted to avoid "freezing" when the driver loads the next page; prefetching would allow you to always keep a constant buffer of rows. See the example in the Javadoc: http://www.datastax.com/drivers/java/2.1/com/datastax/driver/core/ResultSet.html#fetchMoreResults()

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Olivier Michallat

Drivers & Tools Engineer, DataStax

[hemabs]

Hi Olivier,

Thanks for the reply. But given my scenario say there are 100 records in a table; and I want to retrieve from 31st record through 40th record without iterating rows from 1 to 30 (with fetchsize = 10).

Is that possible to do using only the page iterator like I iterate the first three pages-(1to10, 11 to 20 and 21 to 30)? And then only iterate through the rows when in page 4.

Appreciate the help.

Thanks

Hema

[Olivier Michallat]

Unfortunately, offset queries are not supported. You can use the token function, providing the last key of the previous page as the first key for the next query, but there is no easy way to jump directly to page N.

We might also expose the fetching state of the result set in a future version (see JAVA-323).

[hemabs]

Thanks again. Now that I am clear with limitation. Does this mean there is no page iterator in Cassandra Java? The only option we have is to iterate row using rowIterator which starts from row1 to the row of our choice.

Can you please provide some inputs to write this program in java: (Handling Paged Results with Callbacks). I have no clue about Executor, calling Event() and wait like methods. I could find below page in python driver but not for Java driver

http://datastax.github.io/python-driver/query_paging.html

Appreciate the help.

Thanks

hbs

[hemabs]

Also want to say that token way of doing will not work for my project.

[hemabs]

Is there more information available on this? Thanks

[hemabs]

Just trying to see if there are updates on this question

[hemabs]

Can anyone who has more knowledge in this domain share their inputs or point me to a right forum.

Appreciate the help.

Thanks

hbs

[Levi Page]

The token is better than nothing but it is a huge hassle to support it. It basically forces you to send the last id of the result set to the client or have the client pull it out of the last row. Then when requesting more results, the client has to pass that token id to the server instead of a page number so your UI is now pretty much tied to Cassandra. Then to page backwards, you have to know what the last token id was of the previous page which forces you to store in this state somewhere. Again you UI is tried to Cassandra and you have to manage clearing out that previous page token and ensure it's unique for each data grid on your page. 

[Kevin Gallardo]

Persons interested in such kind of features might want to take a look at this http://datastax.github.io/java-driver/2.0.10/features/paging/#manual-paging .

It has been introduced by the Java Driver v2.0.10 and will be on v2.1.6.

Regards.

[Levi Page]

Thanks for this.I am using the C# driver, but even in Java I see no way to page backwards. The fact that I can't change the paging object make it unusable for almost all paging scenarios. In C# they only provide a byte array so I am not sure if the PagingState object (since it's not present) is editable in java but considering the notes that the query string can't be changed, I am assuming it's not going to let me change page=3 to page=2 when they hit back. Am I wrong?

[Kevin Gallardo]

Hi Levi Page, 

Yes indeed there's no clear way to get something like "give me the page before the current one". But you can maybe find a workaround for this considering these statements : 1) gathering the paging state will only give the ability to start with the next page of results. 2) the paging state is not a simple "index number", we say that it cannot be changed because it is combination of multiple ByteBuffers on the C* side and changing its content would be inconsistent for the C* node. 3) by saving a paging state each time you iterate into a new page of results, you could after that set the paging state in a statement and go to every page on which you have saved the paging state.

Then, assuming you have saved the paging state of "next page = page2" and you are on page=3 (or whatever the page you are), you can execute a new statement and setting paging state = "paging state saved of next page is page2" to go backward, and get the results of page2.

And also, the paging state API is available on the C# Driver, not sure, but that should work the same way.

Hope that it helps.. 

[Levi Page]

Thanks Kevin. I tested out the paging example but seems it does not retain the page size even when you restore the previous paging state. Not a big deal since it can be set explicitly every time but thought I would mention. I also noticed when using 'where solr_query=' the paging state comes back null. One idea for a future update would be to automatically include the previous page information in the paging state or provide a way to set it. So if they hit previous page one could say SetPagingState(mypagingstate.previousState). One could certainly create their own object and store it that way, but just a thought. Thanks again for the help! DataStax is a great company.

[Olivier Michallat]

Hi Levi,

Then when requesting more results, the client has to pass that token id to the server instead of a page number

Going to a specific page is an offset query. Cassandra made the choice of not implementing those (see CASSANDRA-6511). There's no magic way to jump to the nth result for an arbitrary query, if this was supported it would restart from the beginning an skip the first n results each time you run the query.

The paging state is basically the partition key + where we are in that partition, it's easy to get after we've fetched the current page, but not something that can be computed in advance.

--

Olivier Michallat

Driver & tools engineer, DataStax

[stevesun21]

I just faced the same issue. I implemented a solution for this.

Here is the code https://gist.github.com/stevesun21/df3fa5141bd01a4f83fc

I choose to skip rows by using page state rather than iterator rows one by one.

[Olivier Michallat]

Your approach works, but, just to be clear, it has linear performance: if the page size is 20

* page 1 will cost you 1 query and 20 fetched rows,

* page 2 will cost you 2 queries and 40 fetched rows,

* etc.

Now that might be acceptable for your use case: maybe you're optimistic that the user will rarely go past page 5 and you accept the performance hit. Also, it's probably a good idea to:

* have a bigger page size internally and do some filtering on the client side. You'll have to experiment to find the perfect balance between number of queries vs. number of fetched rows

* have a hard limit on the biggest page a user can ever ask (e.g. 100)

--

Olivier Michallat

Driver & tools engineer, DataStax

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

[Steve S]

Yes, the linear performance is kind of pain in butt for me. 

According to this article http://www.datastax.com/dev/blog/client-side-improvements-in-cassandra-2-0 and this http://datastax.github.io/java-driver/2.0.10/features/paging/, I'm trying to implement a pagination fashion to allow me to cache the page state for the next paging rather than skip from head.

[Olivier Michallat]

If you only allow the user to jump from one page to the next, you can remember the paging state from the previous query (for example include it in the "next page" link) and avoid the performance hit. That's what's described in http://datastax.github.io/java-driver/2.0.10/features/paging/.

If you allow the user to jump to any random page, you'll have to deal with linear performance.

--

Olivier Michallat

Driver & tools engineer, DataStax

[Arun Chaitanya]

Hello All,

I understand the approach mentioned here.

http://datastax.github.io/java-driver/2.0.10/features/paging/.

Given that I traverse one page after another, is it possible to get the previous page rows without storing the state?

Thanks,

[Olivier Michallat]

No, that doesn't work backwards, you'll need to have saved the state before.

--

Olivier Michallat

Driver & tools engineer, DataStax