如果表具有SET类型列，则Cassandra IN查询不起作用 [英] Cassandra IN query not working if table has SET type column

问题描述

我是Cassandra的新手。我在 CQL IN查询中遇到问题，如果表具有SET类型的列，则它起作用。

I am new to Cassandra. I got a issue in CQL IN query ,if table has SET type column it works.

CREATE TABLE test (
    test_date bigint, 
    test_id bigint, 
    caption text,
    PRIMARY KEY(test_date,test_id)
);

select * from test where test_date = 2022015 and test_id IN (1,2);

但是如果我在上面添加标签集，则会出现错误

but if I add tags set in above then it gives error

CREATE TABLE test1 (
    test_date bigint, 
    test_id bigint, 
    tags set<text>,
    caption text,
    PRIMARY KEY(test_date,test_id)
);

select * from test1 where test_date = 2022015 and test_id IN (1,2);

code = 2200 [无效查询] message =无法限制列 test_id通过
IN关系作为查询选择的集合

code=2200 [Invalid query] message="Cannot restrict column "test_id" by IN relation as a collection is selected by the query"

推荐答案

我认为由于Cassandra的基础存储模型，您会看到此错误。当我在CQLSH中查询您的 test1 表（带有我自己的测试数据）时，我看到的是：

I think you are seeing this error due to Cassandra's underlying storage model. When I query your test1 table within CQLSH (with my own test data), this is what I see:

aploetz@cqlsh:stackoverflow> SELECT * FROM test1;

 test_date | test_id | caption   | tags
-----------+---------+-----------+-------------------------
   2022015 |       1 | blah blah | {'one', 'three', 'two'}
   2022015 |       2 | blah blah | {'one', 'three', 'two'}

(2 rows)

此视图对数据的实际存储方式提供了误导性解释。这是从cassandra-cli中查询同一表时的样子：

This view gives a misleading interpretation of how the data is actually stored. This is what it looks like when I query the same table from within cassandra-cli:

[default@stackoverflow] list test1;
Using default limit of 100
Using default cell limit of 100
-------------------
RowKey: 2022015
=> (name=1:, value=, timestamp=1422895168730184)
=> (name=1:caption, value=626c616820626c6168, timestamp=1422895168730184)
=> (name=1:tags:6f6e65, value=, timestamp=1422895168730184)
=> (name=1:tags:7468726565, value=, timestamp=1422895168730184)
=> (name=1:tags:74776f, value=, timestamp=1422895168730184)
=> (name=2:, value=, timestamp=1422895161891116)
=> (name=2:caption, value=626c616820626c6168, timestamp=1422895161891116)
=> (name=2:tags:6f6e65, value=, timestamp=1422895161891116)
=> (name=2:tags:7468726565, value=, timestamp=1422895161891116)
=> (name=2:tags:74776f, value=, timestamp=1422895161891116)

1 Row Returned.

这建议将集合（集合）值存储为其他列键。使用 IN 关系的限制是，它必须在主键的最后一个键（分区或群集）上运行。因此，我想这是基于Cassandra如何在后台存储收集数据的限制。

This suggests that collection (set) values are stored as additional column keys. A restriction on using the IN relation, is that it must operate on the last key (partitioning or clustering) of a primary key. So I would guess that this is a limitation based on how Cassandra stores the collection data "under the hood."

这只是一个警告，但使用 IN 。有些人甚至将其列入卡桑德拉反模式清单。我对这个问题的回答（是IN关系）解释了为什么 IN 查询不是最佳查询的原因。

And just a warning, but using IN for production-level queries is not recommended. Some have even gone as far as to put it on the list of Cassandra anti-patterns. My answer to this question (Is the IN relation in Cassandra bad for queries?) explains why IN queries are not optimal.

编辑

我只是想看看，我尝试使用列表而不是列表来查看您的架构是否有所作为。它仍然没有用，但是似乎在cassandra-cli内部，向密钥添加了一个额外的UUID标识符，并将实际值存储为列值。这与对待集合的方式不同...这必须是如何将集合限制为唯一值。

Just to see, I tried your schema with a list instead of a set to see if that made any difference. It still didn't work, but from within the cassandra-cli it appeared to add an additional UUID identifier to the key, and stored the actual value as the column value. Which is different from how a set was treated...this must be how sets are restricted to unique values.

这篇关于如果表具有SET类型列，则Cassandra IN查询不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！