我有一个警报表 . 我想在2列上使用IN运算符并在一列上使用大于运算符来查询它 . 我没试过下面的事情 . 有人可以告诉我DB设计使查询工作吗?我的环境细节:[cqlsh 5.0.1 | Cassandra 2.1.2 | CQL规范3.2.0 |原生协议v3]
在分区键中使用'type':
CREATE TABLE alerts (
serialNumber text,
time bigint,
type text,
time2 int,
status text,
parentId int,
PRIMARY KEY ((serialNumber,type), time)
) WITH CLUSTERING ORDER BY (time DESC);
cqlsh:testdb> select * from alerts WHERE serialNumber IN ( '1','2') AND type IN ( '1','2','3' ) AND time > 1;
code=2200 [Invalid query] message="Partition KEY part serialNumber cannot be restricted by IN relation (only the last part of the partition key can)"
使用聚类键中的“类型”:
CREATE TABLE alerts (
serialNumber text,
time bigint,
type text,
time2 int,
status text,
parentId int,
PRIMARY KEY (serialNumber, type, time)
) WITH CLUSTERING ORDER BY (type ASC,time DESC);
cqlsh:testdb> select * from alerts WHERE serialnumber IN ( '1','2') AND type IN ( 'a','b') and time > 1;
code=2200 [Invalid query] message="Clustering column "type" cannot be restricted by an IN relation"
索引类型:
CREATE TABLE alerts (
serialNumber text,
time bigint,
type text,
time2 int,
status text,
parentId int,
PRIMARY KEY (serialNumber, time)
) WITH CLUSTERING ORDER BY (time DESC);
CREATE INDEX alertsTypeIndex ON alerts(type);
select * from alerts WHERE serialnumber IN ( '1','2') and time > 1 AND type IN ( 'a','b');
code=2200 [Invalid query] message="IN predicates on non-primary-key columns (type) is not yet supported"
select * from alerts WHERE serialnumber IN ( '1','2') and time > 1 AND type = 'a';
code=2200 [Invalid query] message="Select on indexed columns and with IN clause for the PRIMARY KEY are not supported"
1 回答
你在哪个版本?你的第二个例子在2.2.1中为我工作:
即使你可以让它工作,我也会建议反对它 . 在分区键上使用IN关键字称为"multi-key"查询反模式 . DataStax有blurb in their documentation discussing why this is bad . 实质上,这种方法不能扩展,因为必须查询许多节点才能满足这些类型的查询 . 您应该找到另一种方法来建模警报,这样您就不需要使用IN关系 .