question

mysub99 avatar image
mysub99 asked Erick Ramirez edited

How do I page through results of multiple queries?

I have a scenario where i need to achieve pagination from backend service. If my table has 40000 records then i need to fetch only 1000 record at a time and not all record in one go. when user clicks next button then only next 1000 records needs to be fetched.

Workflow:

Currently i have 2 tables as shown below:

First i fetch list of uuid from given datetime range from below table

CREATE TABLE employee_attaindance_by_uid_index (
    id int,
    hour timestamp,
    timestamp timestamp,
    uid uuid,
    PRIMARY KEY ((id, hour), timestamp, uid)

then i query multiple cql to get list of records based on uid,

eg: for(String uuid in list) {

select * from employee_details_by_uid where uid = xyz1

select * from employee_details_by_uid where uid = xyz1

select * from employee_details_by_uid where uid = xyz3

}

CREATE TABLE employee_details_by_uid (
    uid uuid PRIMARY KEY,
    android_id text,
          '
          '
          '
          '
          '

here employee_details_by_uid table has single key i.e uid

Now till now i checked how pagination is achieved by using tokens, fetch size and offsets etc.

But in my case my final result is coming from list of different result set show above and not in single query result set.

How can i achieve pagination in this scenario?

java driverspring-bootjoin
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered Erick Ramirez edited

It looks to me like you're doing a JOIN of two tables on the client-side using some UUID as the foreign key which is why it isn't working.

You need to design one table for each application query so you don't need to do foreign key lookups. This is called denormalisation in Cassandra data modelling. It's the complete opposite of normalised tables in traditional RDBMS.

For example, if the app query is:

Get a list of employees (and their details) who attended an event on a given day

then the table will be partitioned by date with each partition containing a list of employees:

CREATE TABLE employees_attendance_by_date (
    attended date,
    emp_id uuid,
    emp_name text,
    emp_phone text,
    PRIMARY KEY (attended, emp_id)
)

and you can query the table with:

SELECT ... FROM employees_attendance_by_date WHERE attended = ?

Note that you cannot do a range query on the partition key because this is an analytics type of query, not OLTP. You will need to use another software like Apache Spark in order to perform analytics on your data since regular CQL will not work. Cheers!

Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.