在 cassandra 中使用 Order by 子句 [英] Use of Order by clause in cassandra

问题描述

在 cassandra 中创建表时，我们可以按如下顺序给出聚类键.

When creating table in cassandra, we can give clustering keys with ordering like below.

Create table user(partitionkey int, id int, name varchar, age int, address text, 
   insrt_ts timestamp,
 Primary key(partitionkey, name, insrt_ts, id)
 with clustering order by (name asc, insrt_ts desc, id asc);

当我们将数据插入到该表中时，根据 cassandra 文档记录根据聚类键进行排序.

when we insert data into that table, As per cassandra documentation records are sorted based on clustering keys.

当我使用 CQL1 和 CQL2 检索记录时，我的排序顺序相同.

When i retrieve records with CQL1 and CQL2, I am getting in the same sorted order.

CQL1:

Select * from user where partitionkey=101;

CQL2:

Select * from user where partitionkey=101 order by name, insrt desc, id;

CQL1 和 CQL2 有什么区别?

what is the difference between CQL1 and CQL2?

推荐答案

DataStax 推荐了一种面向查询的数据建模方法，其中结果按照查询时所需的顺序存储.此顺序由 [复合] 主键的分区键和第一个集群列确定. 上的 DataStax 文档ORDER BY 子句 解释了这一点:

DataStax recommends a query-oriented data modeling approach, where the results are stored in the order in which they are required at query-time. This order is determined by both the partition key and first clustering column of a [compound] primary key. The DataStax doc on the ORDER BY clause explains this:

查询复合主键和排序结果 ORDER BY 子句只能选择单列.该列必须是第二个复合 PRIMARY KEY 中的列.这也适用于带有主键中有两个以上的列组件.订购可以按升序或降序完成，默认升序，以及使用 ASC 或 DESC 关键字指定.

Querying compound primary keys and sorting results ORDER BY clauses can select a single column only. That column has to be the second column in a compound PRIMARY KEY. This also applies to tables with more than two column components in the primary key. Ordering can be done in ascending or descending order, default ascending, and specified with the ASC or DESC keywords.

在 ORDER BY 子句中，参考到使用实际名称的列，而不是别名.

In the ORDER BY clause, refer to a column using the actual name, not the aliases.

例如设置在使用复合主键的播放列表表中插入示例数据，并使用此查询获取有关特定数据的信息播放列表，按song_order 排序.从 Cassandra 1.2 开始，您不需要在选择表达式中包含 ORDER BY 列.

For example, set up the playlists table, which uses a compound primary key, insert the example data, and use this query to get information about a particular playlist, ordered by song_order. As of Cassandra 1.2, you do not need to include the ORDER BY column in the select expression.

所以基本上你只能通过要求 ASCending 或 DESCending 顺序来改变它.

So basically you can really only alter it by asking for ASCending or DESCending order.

您可能还想在 DataStax Ac*ademy 上查看使用 Apache Cassandra 在线课程的(免费)Java 开发一>.我相信会话 3 提供了一个关于 ORDER BY 子句的模块.

You also might want to check out the (free) Java development with Apache Cassandra online class at DataStax Ac*ademy. I believe session 3 offers a module on the ORDER BY clause.

编辑 2019

对于所有未来的 Google 员工，我在这篇博文大约一年后写了一篇关于这个主题的文章.它现在更旧了，但仍然与 Cassandra 的结果集排序相关:https://www.datastax.com/dev/blog/we-shall-have-order

For all future Googlers, I wrote an article on this topic about a year after this post. It's older now, but still relevant to result set ordering with Cassandra: https://www.datastax.com/dev/blog/we-shall-have-order

这篇关于在 cassandra 中使用 Order by 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！