ORDER BY 重装上阵，cassandra [英] ORDER BY reloaded, cassandra

问题描述

我想对给定的列族进行排序，为此我试图创建一个带有选项 CLUSTERING ORDER BY 的表.我总是遇到以下错误:

A given column family I would like to sort and to this I am trying to create a table with the option CLUSTERING ORDER BY. I always encounter the following errors:

1.) 变体 A 导致错误请求:缺少列用户 ID 的 CLUSTERING ORDER声明:

1.) Variant A resulting in Bad Request: Missing CLUSTERING ORDER for column userid Statement:

CREATE TABLE test.user (
  userID timeuuid,
  firstname varchar,
  lastname varchar,
  PRIMARY KEY (lastname, userID)
)WITH CLUSTERING ORDER BY (lastname desc);

2.) 变体 B 导致错误请求:CLUSTERING ORDER 指令中只能定义集群键列声明:

2.) Variant B resulting in Bad Request: Only clustering key columns can be defined in CLUSTERING ORDER directive Statement:

CREATE TABLE test.user (
  userID timeuuid,
  firstname varchar,
  lastname varchar,
  PRIMARY KEY (lastname, userID)
)WITH CLUSTERING ORDER BY (lastname desc, userID asc);

就我在手册中看到的而言，这是创建表的正确语法，我想为其运行查询SELECT .... FROM user WHERE ... ORDER BY lastname".我怎样才能做到这一点?(我希望将姓氏"列保留为主键的第一部分，以便我可以在带有 WHERE 子句的删除语句中使用它.)

As far as I can see in the manual this is the correct syntax for creating a table for which I would like to run queries as "SELECT .... FROM user WHERE ... ORDER BY lastname". How could I achieve this? (The column 'lastname' I would like to keep as the first part of the primary key, so that I could use it in delete statements with the WHERE-clause.)

非常感谢，塔马斯

推荐答案

聚类将仅限于分区键中定义的内容，在您的情况下 (lastName + userId).因此，cassandra 将按 (lastName+userId) 组合的排序顺序存储结果.这就是为什么你不能为了检索目的而同时提供两者.如果您想将表中的所有数据按姓氏排序，因为 userId 是唯一的 (timeuuid)，那么它仍然没有用，因此聚类键没有用.

Clustering would be limited to whats defined in partitioning key, in your case (lastName + userId). So cassandra would store result in sorted order whose (lastName+userId) combination. Thats why u nned to give both for retrieval purpose. Its still not useful schema if you want to sort all data in table as last name as userId is unique(timeuuid) so clustering key would be of no use.

CREATE TABLE test.user (
  userID timeuuid,
  firstname varchar,
  lastname varchar,
  bucket int,
  PRIMARY KEY (bucket)
)WITH CLUSTERING ORDER BY (lastname desc);

在这里，如果您为所有用户记录提供 buket 值，然后说 1，则所有用户都将进入同一个存储桶，然后它将按照姓氏的排序顺序检索所有行.(这绝不是一个好的设计，只是给你一个想法).

Here if u provide buket value say 1 for all user records then , all user would go in same bucket and hense it would retrieve all rows in sorted order of last name. (By no mean this is a good design, just to give you an idea).

修订:

CREATE TABLE user1 (
  userID uuid,
  firstname varchar,
  lastname varchar,
  bucket int,
  PRIMARY KEY ((bucket), lastname,userID)
)WITH CLUSTERING ORDER BY (lastname desc);

这篇关于ORDER BY 重装上阵，cassandra的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！