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

问题描述

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

创建表测试(test_date bigint，test_id bigint，标题文字，PRIMARY KEY(test_date,test_id));select * from test where test_date = 2022015 and test_id IN (1,2);

但是如果我在上面添加 标签集 那么它会给出错误

创建表 test1 (test_date bigint，test_id bigint，标签集<text>,标题文字，PRIMARY KEY(test_date,test_id));select * from test1 where test_date = 2022015 and test_id IN (1,2);

<块引用>

code=2200 [Invalid query] message="不能限制列test_id"查询选择作为集合的 IN 关系"

解决方案

我认为您看到此错误是由于 Cassandra 的底层存储模型.当我在 CQLSH 中查询您的 test1 表(使用我自己的测试数据)时，我看到的是:

aploetz@cqlsh:stackoverflow>SELECT * FROM test1;测试日期 |test_id |标题 |标签-----------+---------+-----------+-------------------------2022015 |1 |等等等等|{'一'，'三'，'二'}2022015 |2 |等等等等|{'一'，'三'，'二'}(2 行)

此视图对数据的实际存储方式做出了误导性解释.这是我从 cassandra-cli 中查询同一张表时的样子:

[default@stackoverflow] list test1;使用默认限制 100使用默认单元格限制 100-------------------行键:2022015=>(名称=1:，值=，时间戳=1422895168730184)=>(名称=1:标题，值=626c616820626c6168，时间戳=1422895168730184)=>(名称=1:标签:6f6e65，值=，时间戳=1422895168730184)=>(名称=1:标签:7468726565，值=，时间戳=1422895168730184)=>(名称=1:标签:74776f，值=，时间戳=1422895168730184)=>(名称=2:，值=，时间戳=1422895161891116)=>(名称=2:标题，值=626c616820626c6168，时间戳=1422895161891116)=>(名称=2:标签:6f6e65，值=，时间戳=1422895161891116)=>(名称=2:标签:7468726565，值=，时间戳=1422895161891116)=>(名称=2:标签:74776f，值=，时间戳=1422895161891116)1 行返回.

这表明集合(集合)值存储为额外的列键.使用 IN 关系的一个限制是它必须对主键的最后一个键(分区或集群)进行操作.所以我猜这是基于 Cassandra 如何在幕后"存储收集数据的限制.

这只是一个警告，但不建议将 IN 用于生产级查询.有些人甚至将其列入 Cassandra 反模式列表.我对这个问题的回答(是 IN 关系在 Cassandra 中对查询不利?) 解释了为什么 IN 查询不是最佳的.

编辑

只是为了看看，我用列表而不是集合尝试了你的模式，看看这是否有什么不同.它仍然没有工作，但是从 cassandra-cli 中它似乎向键添加了一个额外的 UUID 标识符，并将实际值存储为列值.这与处理集合的方式不同……这一定是集合被限制为唯一值的方式.

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 [Invalid query] message="Cannot restrict column "test_id" by IN relation as a collection is selected by the query"

解决方案

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)

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.

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."

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.

EDIT

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