Cassandra CQL选择查询不返回时间戳作为clusterkey的记录 [英] Cassandra CQL select query not returning records which have timestamp as clusterkey

问题描述

Cassandra CQL:使用复合键和集群键创建的表.当我尝试从分区键执行 select * 时，我能够检索所有数据并且它也适用于关系运算符( < 或 > ).但是，当我使用具有正确值的等于(=)运算符查询特定集群键时，它返回 0 行.

Cassandra CQL: Table created with composite key and cluster key. When I try to execute select * from partition key then I able to retrieve all data and it works for relational operator ( < or > ) too . But when I queried for particular cluster key using equal-to(=) operator with proper value it returns 0 rows.

表格:

CREATE TABLE entity_data (
received_date timestamp,
entity text,
received_time timestamp,
node int,
primary key ((received_date ,entity),received_time));

数据(从实体中选择 *):

received_date              | entity | received_time            | node_id
2014-09-24 00:00:00+0400   |     NA | 2014-09-24 18:56:55+0400 |       0  | 

使用条件查询: -- 这里不起作用

select * from entity_data 
where received_date = '2014-09-24 00:00:00+0400' and entity = 'NA' 
and received_time='2014-09-24 18:56:55+0400';
(0 rows)

-- 返回 0 行.

推荐答案

我知道发生了什么.您正在使用 now() 生成时间 UUID.但是，当您使用 dateOf() 将其转换为 timestamp 时，您正在截断它的毫秒数.因此查询 received_time 等于 2014-09-24 18:56:55+0400 将不会产生任何结果，因为 timestamp 类型仍以毫秒为单位存储(您只需由于您的 dateOf()) 而看不到它.

I see what is going on. You are using now() to generate a time-UUID. But when you convert that to a timestamp using dateOf() you are truncating the milliseconds off of it. Therefore querying for a received_time equal to 2014-09-24 18:56:55+0400 will yield nothing, as the timestamp type is still stored with the milliseconds (you just can't see it due to your dateOf()).

解决此问题的最佳方法是将您的时间存储为 timeuuids(注意:我将 received_date 保留为时间戳，仅出于示例目的).然后在 SELECT 时使用 dateOf，并在 WHERE 子句中使用 minTimeuuid() 函数:

The best way to go about this, is to store your times as timeuuids (NOTE: I left received_date as a timestamp just for purposes of the example). Then use the dateOf when you SELECT, and use the minTimeuuid() function for your WHERE clause:

CREATE TABLE entity_data2 (
    received_date timestamp,
    entity text,
    received_time timeuuid,
    node int,
PRIMARY KEY ((received_date, entity), received_time));

INSERT INTO entity_data2 (received_date, entity, received_time , node) 
VALUES ('2014-09-24 00:00:00+0400','NA',now(),0);

aploetz@cqlsh:stackoverflow> SELECT * FROM entity_data2 
    WHERE received_date = '2014-09-24 00:00:00+0400' AND entity = 'NA'  
    AND received_time>minTimeuuid('2014-10-08 08:13:53-0500') 
    AND received_time<minTimeuuid('2014-10-08 08:13:54-0500');

 received_date            | entity | received_time                        | node
--------------------------+--------+--------------------------------------+------
 2014-09-23 15:00:00-0500 |     NA | f3b548b0-4eec-11e4-9d05-7991a041665c |    0

(1 rows)

aploetz@cqlsh:stackoverflow> SELECT received_date, entity, dateof(received_time), node 
    FROM entity_data2 WHERE received_date = '2014-09-24 00:00:00+0400' AND entity = 'NA'
    AND received_time>minTimeuuid('2014-10-08 08:13:53-0500') 
    AND received_time<minTimeuuid('2014-10-08 08:13:54-0500');

 received_date            | entity | dateof(received_time)    | node
--------------------------+--------+--------------------------+------
 2014-09-23 15:00:00-0500 |     NA | 2014-10-08 08:13:53-0500 |    0

(1 rows)

基本上，dateOf() 函数被设计用于查询数据，而不是存储数据.这是一篇博客文章，描述了(更详细地)如何进行这项工作:

Basically the dateOf() function was designed to be used for querying data, not storing it. Here is a blog posting that describes (in more detail) how to make this work:

Cassandra 中基于时间序列的查询1.2+ 和 CQL3

这篇关于Cassandra CQL选择查询不返回时间戳作为clusterkey的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！