Cassandra CQL查询检查多个值

问题描述

如何通过Cassandra CQL查询检查非主键字段的值是 A还是 B？ （我正在使用Cassandra 2.0.1）

How can I check if a non-primary key field's value is either 'A' or 'B' with a Cassandra CQL query? (I'm using Cassandra 2.0.1)

这是表定义：

CREATE TABLE my_table (
  my_field text,
  my_field2 text,
  PRIMARY KEY (my_field)
);

我尝试过：

1> SELECT * FROM my_table WHERE my_field2 IN ('A', 'B');

2> SELECT * FROM my_table WHERE my_field2 = 'A' OR my_field = 'B' ;

第一个因此消息失败：

Bad Request: IN predicates on non-primary-key columns (my_field2) is not yet supported

第二个失败，因为Cassandra CQL不支持OR关键字

The second one failed because Cassandra CQL doesn't support OR keyword

我无法使这个简单的查询正常工作（直截了当的方式）。总的来说，我对处理CQL查询感到非常沮丧。是因为卡桑德拉（Cassandra）不够成熟，对查询的支持真的很差，还是我必须改变思维方式？

I couldn't get this simple query working (with a pretty straight forward way). I'm pretty frustrated dealing with CQL queries in general. Is it because Cassandra is not mature enough and has really poor support with queries, or is it me who must change the way of thinking?

推荐答案

这是cassandra的故意功能。您不能在非以下列的列上使用WHERE子句查询

This is the intentional functionality of cassandra. You cannot query using a WHERE clause on columns that are not

• 分区键


• 组合键

这是因为您的数据围绕一堆cassandra节点进行了分区。您希望避免要求整个响铃返回答案的查询。理想情况下，您希望能够从环中的单个节点检索数据

This is because your data is partitioned around a ring of cassandra nodes. You want to avoid having to ask the entire ring to return the answer to your query. Ideally you want to be able to retrieve your data from a single node in the ring

通常在cassandra中，您希望结构化表以匹配查询，而不是关系归一化。因此，您可以选择一些方法来解决这个问题。

Generally in cassandra you want to structure your table to match your queries as opposed to relational normalization. So you have a few options to deal with this.

1）将数据写入多个表以支持各种查询。在您的情况下，您可能要创建第二个表，

1) write your data to multiple tables to support various queries. In your case you may want to create a second table as

CREATE TABLE my_table (
  my_field2 text,
  my_field text,
  PRIMARY KEY (my_field2)
);

然后您的第一个查询将正确返回

Then your first query will return correctly

2）使用组合键创建表

2) Create your table with a composite key as

CREATE TABLE my_table (
  my_field text,
  my_field2 text,
  PRIMARY KEY (my_field, my_field2)
);

使用此方法，如果您未为my_field指定查询值，则需要附加您的用限定词查询以告诉cassandra您确实要查询整个环

With this method, if you do not specify a query value for my_field then you will need to append your query with a qualifier to tell cassandra that you really want to query the entire ring

SELECT * FROM my_table WHERE my_field2 IN ('A', 'B') ALLOW FILTERING;

-edit-

您不能使用二级索引以搜索多个值。每个CQL文档

you cannot use a secondary index to search for multiple values. Per CQL documentation

索引是一种数据结构，可以快速，高效地查找与给定条件匹配的数据。

"An index is a data structure that allows for fast, efficient lookup of data matching a given condition."

因此，您必须给它一个值，并且只给一个值。

So, you must give it one and only one value.

这篇关于Cassandra CQL查询检查多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！