“坏请求：PRIMARY KEY部分to_id不能被限制”当尝试选择使用where条件时 [英] "Bad Request: PRIMARY KEY part to_id cannot be restricted" when trying to select using where condition

问题描述

这是我的聊天类应用的cassandra表：

Here is my cassandra table for chat kind of application:

CREATE TABLE tax_keyspace_dev.chat_messages (
  message text,
  when timestamp,
  from_id text,
  to_id text,
  read boolean,
  participants text,
  PRIMARY KEY(participants, when, to_id)
);

此查询工作：

select * from tax_keyspace_dev.chat_messages where participants='[e-mail][e-mail]' order by when;

但以下查询无效：

select * from tax_keyspace_dev.chat_messages where to_id='[e-mail]' order by when; 

错误是错误的请求：PRIMARY KEY part to_id不能被限制不限制或非EQ关系）

update tax_keyspace_dev.chat_messages set read=true where participants = '[e-mail][e-mail]' and when = '2014-04-10 17:44:22+0530'; 

错误是错误请求：缺少必备的PRIMARY KEY零件to_id

如果我从复合键中删除to_id，并创建如下的单独的索引：

If I remove "to_id" from composite key and create separate index like this:

CREATE TABLE tax_keyspace_dev.chat_messages (
 message text,
 when timestamp,
 from_id text,
 to_id text,
 read boolean,
 participants text,
 PRIMARY KEY(participants, when)
);
CREATE INDEX idx_chat_messages_to ON tax_keyspace_dev.chat_messages (to_id);

那么其他查询工作，但这一个失败：

then other queries work but this one fails:

select * from tax_keyspace_dev.chat_messages where to_id='[e-mail]' order by when;

错误为错误的请求：不支持ORDER BY与二进制索引。

如何设计表格，以便所有这些用例都可以工作？

How do I design my table so that all these use cases can work?

select * from tax_keyspace_dev.chat_messages where participants='[e-mail][e-mail]' order by when;
update tax_keyspace_dev.chat_messages set read=true where participants = '[e-mail][e-mail]' and when = '2014-04-10 17:44:22+0530';
select * from tax_keyspace_dev.chat_messages where to_id='[e-mail]' order by when;

推荐答案

使用cassandra时，您的第一部分主键成为分区键。因此，要转到特定分区以检索行，您需要始终使用equals constraint指定主键。

When using cassandra , your first part of the primary key becomes the partition key. Hence , to go to a particular partition for retrieving the row, you need to specify the primary key with equals constraint always.

select * from tax_keyspace_dev.chat_messages where participants='[e-mail][e-mail]' order by when;

以下查询建议您到达名为participants的行分区，使用ASC的默认顺序。此顺序可能不需要，因为您的列默认按升序排序。

The following query suggests that you arrive at your row partition named "participants" and then you order by when using default ordering of ASC.This order by may not be needed too as your columns are by default ordered in ascending order.

select * from tax_keyspace_dev.chat_messages where to_id='[e-mail]' order by when; 

select * from tax_keyspace_dev.chat_messages where to_id='[e-mail]' order by when;

以下查询不工作，因为您不提供行分区以定位值。行分区键用于标识包含数据的SSTables。所以，默认情况下，cassandra不支持这种昂贵的操作。

Following query doesnt work as you are not providing the row partition to locate the value.By default, the row partition keys are used to identify the SSTables that contain the data . So, by default , cassandra doesnt support this costly operation.

会发生什么是简单。如果你错过这个行分区键，cassandra必须扫描所有的SSTables，并得到它的数据。这可以通过使用允许过滤来完成，但是您的查询会变得昂贵，因为它不会使用bloom过滤器。

What happens is simple. If you miss this row partition key, cassandra has to scan through all SSTables and get the data out of it. That can be done by using ALLOW FILTERING but your query becomes expensive as it will not be using bloom filter.

update tax_keyspace_dev.chat_messages set read=true where participants = '[e-mail][e-mail]' and when = '2014-04-10 17:44:22+0530'; 

如果是cassandra的更新，只考虑使用地图操作的情况。您正在尝试修改值，但您没有地图的完整键。在内部，cassandra存储的值是participants_when_to_id：值。

In case of updates on cassandra, its not different from inserts. Just consider a case of operating with maps. You are trying to modify a value but you dont have the complete key for the map. Internally, cassandra stores the values are "participants_when_to_id": value.

这篇关于“坏请求：PRIMARY KEY部分to_id不能被限制”当尝试选择使用where条件时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！