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屋!