ORDER BY reloaded，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。 我如何实现这一点？（列'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.)

非常感谢，Tamas

Thanks a lot, Tamas

推荐答案

在分区键中定义，在您的情况下（lastName + userId）。因此cassandra将存储结果以（lastName + userId）组合的排序顺序。这是为什么要给出两个检索的目的。如果你想将表中的所有数据排序为lastname，因为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);

这里，如果u对所有用户记录提供buket值为1，那么所有用户都将进入同一个桶并且hense它将按照姓氏的排序顺序检索所有行。 （不是说这是一个好的设计，只是给你一个想法）。

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 reloaded，cassandra的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！