cassandra，通过非主键选择 [英] cassandra, select via a non primary key

问题描述

我是cassandra的新人，我遇到了一个问题。我创建了一个keyspace demodb和一个表用户。这个表有3列：id（int和主键），firstname（varchar），name（varchar）。
这个请求给我很好的结果：

I'm new with cassandra and I met a problem. I created a keyspace demodb and a table users. This table got 3 columns: id (int and primary key), firstname (varchar), name (varchar). this request send me the good result:

SELECT * FROM demodb.users WHERE id = 3;

但这一个：

SELECT * FROM demodb.users WHERE firstname = 'francois';

无法使用，我收到以下错误讯息：

doesn't work and I get the following error message:

InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: "

此请求也无效：

SELECT * FROM users WHERE firstname  = 'francois'  ORDER BY id DESC LIMIT 5;
InvalidRequest: code=2200 [Invalid query] message="ORDER BY with 2ndary indexes is not supported."

提前感谢。

推荐答案

此请求也无效：

This request also doesn't work:

误解了Cassandra中排序顺序的工作原理。而不是在 firstname 上使用辅助索引，请为此查询创建一个表，如下所示：

That's because you are mis-understanding how sort order works in Cassandra. Instead of using a secondary index on firstname, create a table specifically for this query, like this:

CREATE TABLE usersByFirstName (
  id int,
  firstname text,
  lastname text,
  PRIMARY KEY (firstname,id));

此查询现在应该可以工作：

This query should now work:

SELECT * FROM usersByFirstName WHERE firstname='francois'
ORDER BY id DESC LIMIT 5;

< c $ c>和 id 。这将分区您的数据 firstname （允许您通过它查询），同时也通过 id 聚集您的数据。默认情况下，您的数据将按 id 按升序聚类。要改变此行为，您可以在表创建语句中指定 CLUSTERING ORDER ：

Note, that I have created a compound primary key on firstname and id. This will partition your data on firstname (allowing you to query by it), while also clustering your data by id. By default, your data will be clustered by id in ascending order. To alter this behavior, you can specify a CLUSTERING ORDER in your table creation statement:

WITH CLUSTERING ORDER BY (id DESC)

...甚至需要一个 ORDER BY 子句。

...and then you won't even need an ORDER BY clause.

我最近写了一篇关于如何在Cassandra href =http://planetcassandra.org/blog/we-shall-have-order/>我们有秩序）。它解释了这一点，并涵盖了一些排序策略。

I recently wrote an article on how clustering order works in Cassandra (We Shall Have Order). It explains this, and covers some ordering strategies as well.

这篇关于cassandra，通过非主键选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！