对cassandra中的二级索引进行范围查询 [英] Range query on secondary index in cassandra

问题描述

我使用cassandra 2.1.10。
所以首先我会清楚，我知道二级索引是反模式在cassandra.But为测试目的我试图遵循：

I am using cassandra 2.1.10. So First I will clear that I know secondary index are anti-pattern in cassandra.But for testing purpose I was trying following:

CREATE TABLE test_topology1.tt (
    a text PRIMARY KEY,
    b timestamp
) WITH bloom_filter_fp_chance = 0.01
    AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}'
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99.0PERCENTILE';
CREATE INDEX idx_tt ON test_topology1.tt (b);

当我运行以下查询时，它会给我错误。

When I run following query it gives me error.

cqlsh:test_topology1> Select * from tt where b>='2016-04-29 18:00:00' ALLOW FILTERING;
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'b >= <value>'"

-where-clauserel =nofollow>博客说允许过滤可以用于查询二级索引。
Cassandra安装在Windows机器上。

while this Blog says that allow filtering can be used to query secondary index. Cassandra is installed on windows machine.

推荐答案

Cassandra中不允许对二级索引列进行范围查询包括2.2.x.但是，作为后 A深入了解CQL WHERE子句 指出，如果过滤为全部，则允许在非索引列上：

Range queries on secondary index columns are not allowed in Cassandra up to and including 2.2.x. However, as the post A deep look at the CQL WHERE clause points out, they are allowed on non-indexed columns, if filtering is allwed:

对次要索引的直接查询只支持=，CONTAINS或
CONTAINS KEY限制。

Direct queries on secondary indices support only =, CONTAINS or CONTAINS KEY restrictions.

[..]

二级索引查询允许您使用=，>，> =，< =和< CONTAINS和CONTAINS KEY限制来限制返回结果

Secondary index queries allow you to restrict the returned results using the =, >, >=, <= and <, CONTAINS and CONTAINS KEY restrictions on non-indexed columns using filtering.

因此，给定表结构和索引

So, given the table structure and index

CREATE TABLE test_secondary_index (
     a text PRIMARY KEY,
     b timestamp,
     c timestamp 
);
CREATE INDEX idx_inequality_test ON test_secondary_index (b);

以下查询失败，因为对索引列执行不相等测试：

the following query fails because the inequality test is done on the indexed column:

SELECT * FROM  test_secondary_index WHERE b >= '2016-04-29 18:00:00' ALLOW FILTERING ;
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'b >= <value>'"

但是下面的工作原理是因为不相等的测试是在非索引列上完成的：

But the following works because the inequality test is done on a non-indexed column:

SELECT * FROM  test_secondary_index WHERE b = '2016-04-29 18:00:00' AND c >= '2016-04-29 18:00:00' ALLOW FILTERING ;

 a | b | c
---+---+---

(0 rows)

如果在列 c 上添加另一个索引，但仍然需要 ALLOW FILTERING term，对我来说意味着在这种情况下不使用列c上的索引。

This still works if you add another index on column c, but also still requires the ALLOW FILTERING term, which to me means that the index on column c is not used in this scenario.

这篇关于对cassandra中的二级索引进行范围查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！