1

i need to select 'N'th row from cassandra table based on the particular number i'm getting from my logic. i.e: if logic output is 23 means, i need to get 23rd row details. since there is no auto increment in cassandra,i cant able to go with ID key match. In SQL they getting it using OFFSET and LIMIT. i dont know how to achieve this feet in Cassandra. Can we achieve this by using any UDF concept??? Someone reply me the solution.Thanks in advance.

Table Schema :

CREATE TABLE new_card ( 
    customer_id bigint, 
    card_number text, 
    active tinyint, 
    auto_pay int, 
    available_credit_limit double, 
    average_card_spend_half_yearly double, 
    average_card_spend_monthly double, 
    average_card_spend_quarterly double, 
    average_card_spend_yearly double, 
    avg_half_yearly_spend_mcc double,
    PRIMARY KEY (customer_id, card_number) 
);
Mahesh Kumar
  • 25
  • 5

2 Answers2

1

If you are using Java driver, refer Paging

Note, Cassandra does not support direct offsets, pages are read sequentially. If you have to use offsets to be used in your queries, you might want to revisit your data model. You could have created a composite partition key including the row number as an additional column on top of you existing partition key column.

Gautam
  • 564
  • 2
  • 12
  • In our project ,we are using crud repository concept. so, cant go with Cluster and Statement concept, that we normally using for JDBC connection. i'm on a confused loop now. – Mahesh Kumar Aug 28 '17 at 08:50
  • Well, bottom line is that you cannot jump pages with Cassandra. You'll need to go one page at a time. Now spring-data as a whole supports Pagination using Pageable and Sort, but inherently spring data cassandra must still be scrolling through pages, just that it is hidden from client side developer. Check for Pageable and Sort in spring data cassandra documentation. Still, you should really consider revisiting your data model. – Gautam Aug 28 '17 at 08:56
  • I done it using paging concept as you replied, Thank you. – Mahesh Kumar Oct 06 '17 at 09:35
1

You simply can't select N row from table, because Cassandra table is made from partitions, and you can order your rows within partition, but not the partitions. Going with paging will go throw all tables, but there's will be no chronological order of the rows selected using suck approach (disregarding the fact that the partitions can change while you doing your go-throw-pages stuff).

If you want to select row number N from Cassandra, you need to implement auto increment field on the application level and use it as key.

There's ways to do it with Cassandra, using lightweight transactions for example, but it have high cost from performance perceptive. See several solutions here:

How to create auto increment IDs in Cassandra

nevsv
  • 2,448
  • 1
  • 14
  • 21
  • Since its master table, i cant change table structure wisely. – Mahesh Kumar Aug 28 '17 at 13:25
  • So use auto increment and id, but that is a bad practice, or implement it on application level. Anyway, if you are moving table from relational DB to Cassandra without any changes, expect things to stop working, it isn't designed this way. If it is a must have feature, you should maybe try using another DB. If you can't change not the code, and not the table structure, it will just not work. Cassandra can do many things, but it is for sure not a drop-in replacement for relational DB. – nevsv Aug 28 '17 at 14:16