如何使用cassandra的时间戳列作为WHERE条件执行查询 [英] How to perform query with cassandra's timestamp column as WHERE condition

问题描述

我有以下Cassandra表格

I have the following Cassandra table

cqlsh:mydb> describe table events;

CREATE TABLE mydb.events (
    id uuid PRIMARY KEY,
    country text,
    insert_timestamp 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 country_index ON mydb.events (country);
CREATE INDEX insert_timestamp_index ON mydb.events (insert_timestamp);

正如你所看到的，index已经创建在 insert_timestamp 栏。

As you can see, index is already created on insert_timestamp column.

我浏览过 http：// stackoverflow。 com / a / 18698386/3238864

我虽然以下是正确的查询：

I though the following is the correct query

cqlsh:mydb> select * from events where insert_timestamp >= '2016-03-01 08:27:22+0000';
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'insert_timestamp >= <value>'"

cqlsh:mydb> select * from events where insert_timestamp >= '2016-03-01 08:27:22+0000' ALLOW FILTERING;
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'insert_timestamp >= <value>'"

但是，使用 country 列查询 WHERE

cqlsh:mydb> select * from events where country = 'my';

id                                   | country | insert_timestamp
--------------------------------------+---------+--------------------------
53167d6a-e125-46ff-bacf-f5b267de0258 |      my | 2016-03-01 08:27:22+0000

任何想法为什么用时间戳查询作为条件工作？

Any idea why query with timestamp as condition doesn't work? Is there anything wrong with my query syntax?

推荐答案

对二级索引的直接查询只支持= 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.

一旦您添加 ALLOW FILTERING 即可。

select * from events where insert_timestamp >= '2016-03-01 08:27:22+0000' ALLOW FILTERING;

您在问题中提及的链接的时间戳列为集群键。因此它在那里工作。

The link that you have mentioned in your question has timestamp column as clustering key. Hence it is working there.

根据注释 RangeQuery on secondary index is not alllowed up to 2.2.x version

FYI：
当Cassandra必须执行辅助索引查询时，它将联系所有节点以检查位于每个节点上的辅助索引的部分。
因此，它被认为是cassandra中的反模式，在高基数列（如时间戳）上有索引。
您应该考虑更改您的数据模型以适合您的查询。

FYI: When Cassandra must perform a secondary index query, it will contact all the nodes to check the part of the secondary index located on each node. Hence it is considered as anti-pattern in cassandra to have index on high cardinality column like timestamp. You Should consider changing your data model to suit your queries.

这篇关于如何使用cassandra的时间戳列作为WHERE条件执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！