复合键的Cassandra CQL分页 [英] Cassandra CQL paging for Composite key

问题描述

是否有任何简单的方法来查询宽行通过分页。我的架构是，

Is there is any easy way to query wide row by paging. My schema is,

CREATE TABLE USER_LIKED_ITEMS (
user_id text,
event_id text,
item_id text,
description text,
PRIMARY KEY(user_id, event_id, item_id)
);

我想获取给定user_id的所有行（考虑有 500000行我试过的查询是 select * from USER_LIKED_ITEMS where user_id ='101'LIMIT 25; 一旦结果到达，则第25条记录具有值

I want to get all the rows for the given user_id (consider there are 500000 rows for a given user_id with various combination of event_id and item_id) through paging with a count 25. The query I have tried is select * from USER_LIKED_ITEMS where user_id = '101' LIMIT 25; Once results is arrived then 25th record has a value

user_id = 101,
event_id = 25,
item_id = 25,
description = XXXXX

我的下一个查询是 select * from USER_LIKED_ITEMS where user_id ='101'and event_id ='25'and item_id> '25'LIMIT 25; 检索具有最后记录值的10条记录的结果，例如

My next query is, select * from USER_LIKED_ITEMS where user_id = '101' and event_id = '25' and item_id > '25' LIMIT 25; Retrieves the results of 10 records having the last record value like,

user_id = 101,
event_id = 25,
item_id = 35,
description = XXXXX

b $ b

因此，我需要再次构造 select * from USER_LIKED_ITEMS where user_id ='101'and event_id> '25'。因此，通过构造不同的查询以检索所有结果，检索所有结果是相当棘手和困难的。有没有任何简单的方法获取给定的user_id的所有记录与分页计数25？我使用cassandra-1.2.15和cassandra-driver-core-1.0.3.jar。

So again I need to frame select * from USER_LIKED_ITEMS where user_id = '101' and event_id > '25'. So It is quite tricky and difficult to retrieve all the results by framing different query to retrieve all the results. Is there is any easy way to get all the record for the given user_id with pagination count of 25? I am using cassandra-1.2.15 and cassandra-driver-core-1.0.3.jar. Cassandra2.0 has paging support, but I am not allowed to upgrade cassandra version.

推荐答案

有一种方法可以在1.2到<通过您的结果使用令牌（）创建一个href =http://www.datastax.com/documentation/cql/3.0/cql/cql_using/paging_c.html =nofollow> ）函数，并基于您的分区键（对于 user_id ）。但我怀疑是否有任何价值（给你）通过 user_id 查询你的结果，你会这样做。

There is a way in 1.2 to "page" through your results with the token() function and based on your partitioning key (which for you is user_id). But I suspect if there was any value (to you) to query your results by just user_id, you would be doing it.

一个可能对您有所帮助的解决方案是将您的数据复制到查询表中以解决此问题：

One solution that might be of some benefit to you, is to duplicate your data into a query table to solve this:

CREATE TABLE USER_LIKED_ITEMS_BY_PAGENO (
user_id text,
event_id text,
item_id text,
description text,
pageno bigint,
PRIMARY KEY(user_id, pageno, event_id, item_id)
);

当然，这是假设你在插入时知道哪些行属于哪个页面 （也许每插入25个，您需要增加 pageno 或类似的东西）。但是如果你这样做了，你可以把它们放在这样的表中，然后 SELECT 就像这样：

Of course this is assuming that you know at insert-time which rows belong on which page (maybe for every 25 inserted, you increment pageno, or something similar). But if you did, you could put them into a table like that, and then SELECT from it like this:

select * from USER_LIKED_ITEMS_BY_PAGENO 
where user_id = '101' and pageno = 4;

这篇关于复合键的Cassandra CQL分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！