如何在cassandra中同时使用和订购 [英] how to use in and order by at the same time in cassandra

问题描述

我看到了官方文档： https：// docs .datastax.com / en / cql / 3.1 / cql / cql_using / useColumnsSort.html

可以使用 SELECT * FROM users where userID IN（102,104）按年龄ASC排序；

can use "SELECT * FROM users WHERE userID IN (102,104) ORDER BY age ASC;"

如何定义用户表或实例化视图？

how to define the users table or materialized view?

分页是一个

这是我当前的视图：

CREATE MATERIALIZED VIEW navigation_by_id_time AS
       SELECT * FROM navigation
       WHERE competition_id IS NOT NULL AND event_type_id IS NOT NULL AND event_id IS NOT NULL AND market_id IS NOT NULL AND market_start_time IS NOT NULL AND market_suspend_time IS NOT NULL
       PRIMARY KEY (event_type_id, market_start_time, event_id, market_id, market_suspend_time);

此查询无效

select * from navigation_by_id_time where event_type_id in(1,2,3) order by market_start_time;

message =不能同时使用ORDER BY和分区键IN限制的页面查询；您必须要么删除ORDER BY或IN并在客户端进行排序，要么禁用此查询的分页

message="Cannot page queries with both ORDER BY and a IN restriction on the partition key; you must either remove the ORDER BY or the IN and sort client side, or disable paging for this query"

谢谢

推荐答案

使用bth IN和ORDER BY时，Cassandra不支持分页。在datastax文档中也有说明-使用IN关键字进行检索。

Cassandra does not support paging when bth IN and ORDER BY are used. It is stated also in datastax documentation - Retrieval using IN keyword.

此外，如果您检查代码，您还将获得更多说明。

Also, if you inspect the code, you will also get some more explanations.

该检查已添加到 JIRA-6722 中，并且该行为的解释为：

The check was added in JIRA-6722 and the explanations of the behaviour is:

然而，在实践中并不是那么容易。如果您查询每个分区的整页
，而IN中有很多分区，则会在内存中加载
吨的数据，这在很大程度上破坏了分页的目标。
如果查询的大小小于每个分区的页面大小，则
现在可能需要重新查询某些分区，具体取决于
在第一页上的合并排序结果。 / p>

It is however not all that easy in practice. If you query a full page of each partition and there is many partitions in the IN, you'll load tons of data in memory, defeating in large parts the goal of paging. If you instead query less than the page size of each partition, you now may need to re-query some of the partitions depending on what the merge sort yield on those first pages.

作为结论：您需要关闭分页。如果您使用的是Java客户端，请这可能会有所帮助（还介绍了更多内容）。

As a conclusion: you need to turn off paging in cqlsh. If you are using a Java client, this might help (also explains some more stuff).

这篇关于如何在cassandra中同时使用和订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！