使用复合键进行查询，而不是使用Cassandra中的行键

I want to query data filtering by composite keys other than Row Key in CQL3. These are my queries:

我想通过CQL3中的行键以外的组合键来查询数据过滤。这些是我的查询:

CREATE TABLE grades (id int,
  date timestamp,
  subject text,
  status text,
  PRIMARY KEY (id, subject, status, date)
);

When I try and access the data,

当我尝试访问数据时，

SELECT * FROM grades where id = 1098; //works fine
SELECT * FROM grades where subject = 'English' ALLOW FILTERING; //works fine
SELECT * FROM grades where status = 'Active' ALLOW FILTERING; //gives an error

Bad Request: PRIMARY KEY part status cannot be restricted (preceding part subject is either not restricted or by a non-EQ relation)

错误请求:主键部分状态不能被限制(前面的部分主体要么不受限制，要么不受非eq关系的限制)

Just to experiment, I shuffled the keys around keeping 'id' as my Primary Row Key always. I am always ONLY able to query using either the Primary Row key or the second key, considering above example, if I swap subjects and status in Primary Key list, I can then query with status but I get similar error if I try to do by subject or by time.

为了进行实验，我一直把关键字“id”作为我的主行关键字。我总是只能查询使用主行键或第二个关键,考虑上面的例子中,如果我在主键交换对象和状态列表,然后我可以查询状态,但我得到类似的错误,如果我做主题或时间。

Am I doing something wrong? Can I not query data using any other composite key in CQL3? I'm using Cassandra 1.2.6 and CQL3.

我做错什么了吗?我可以不使用CQL3中的任何其他组合键查询数据吗?我用的是Cassandra 1.2.6和CQL3。

1 个解决方案

#1

That looks all normal behavior according to Cassandra Composite Key model (http://www.datastax.com/docs/1.2/cql_cli/cql/SELECT). Cassandra data model aims (and this is a general NoSQL way of thinking) at granting that queries are performant, that comes to the expense of "restrictions" on the way you store and index your data, and then how you query it, namely you "always need to restrict the preceding part of subject" on the primary key.

根据Cassandra组合键模型(http://www.datastax.com/docs/1.2/cql_cli/cql/SELECT)，这看起来是正常的行为。卡桑德拉数据模型的目标(这是一般的NoSQL的思维方式)在假定查询性能,到“限制”为代价的方式存储和索引你的数据,然后你怎么查询,即你”总是需要限制主题”的前部分的主键。

You cannot swap elements on the primary key list on the queries (that is more a SQL way of thinking). You always need to "Constraint"/"Restrict" the previous element of the primary key if you are to use multiple elements of the composite key. This means that if you have composite key = (id, subject, status, date) and want to query "status", you will need to restrict "id" and/or "subject" ("or" is possible in case you use "allow filtering", i.e., you can restrict only "subject" and do not need to restrict "id"). So, if you want to query on "status" you will b able to query in two different ways:

您不能在查询的主键列表上交换元素(这是一种SQL思维方式)。如果要使用组合键的多个元素，则始终需要“约束”/“限制”主键的前一个元素。这意味着如果您有组合键= (id、subject、status、date)，并且想要查询“status”，那么您将需要限制“id”和/或“subject”(“或者”在您使用“允许过滤”的情况下是可能的，即“允许过滤”)。，您可以只限制“subject”，不需要限制“id”)。所以，如果你想查询“状态”，你可以用两种不同的方式进行查询:

select * from grades where id = '1093' and subject = 'English' and status = 'Active';

从id = '1093'和subject = 'English' and status = 'Active'的年级中选择*;

或

select * from grades where subject = 'English' and status = 'Active' allow filtering;

从受试者= 'English'和状态= 'Active'允许过滤的年级中选择*;

The first is for a specific "student", the second for all the "students" on the subject in status = "Active".

第一种是针对某一特定的“学生”，第二种是针对某一主题的所有“学生”，状态=“活动”。

#1