Cassandra CQL选择查询不返回具有时间戳为clusterkey的记录 [英] Cassandra CQL select query not returning records which have timestamp as clusterkey
问题描述
Cassandra CQL:使用复合键和集群键创建的表。当我尝试从分区键执行select *时,我能够检索所有数据,它也适用于关系运算符(<或>)。
但是当我查询特定的集群键使用等于(=)运算符与适当的值,它返回0行。
表格:
CREATE TABLE entity_data(
received_date timestamp,
entity text,
received_time timestamp,
node int,
primary key((received_date,entity),received_time)
资料(选择*来自实体):
$ b
received_date |实体| 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
type仍然以毫秒存储(由于您的dateOf()
),您只能看不到它。
最好的办法是将你的时间存储为
$ b> $ <$ p> $timeuuid
(注意:我留下received_date
作为仅用于示例目的的时间戳)。然后在SELECT
时使用dateOf
,并使用minTimeuuid()
子句
CREATE TABLE entity_data2
received_date timestamp,
实体文本,
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 );
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 |实体| received_time |节点
-------------------------- + -------- + --------- ----------------------------- + ------
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 |实体| dateof(received_time)|节点
-------------------------- + -------- + --------- ----------------- + ------
2014-09-23 15:00:00-0500 | NA | 2014-10-08 08:13:53-0500 | 0
(1 rows)
基本上
dateOf()
函数旨在用于查询数据,而不是存储它。这是一个博客文章,描述(更详细地说明)如何使这项工作:
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.
Table:
CREATE TABLE entity_data ( received_date timestamp, entity text, received_time timestamp, node int, primary key ((received_date ,entity),received_time));
Data ( select * from entity):
received_date | entity | received_time | node_id 2014-09-24 00:00:00+0400 | NA | 2014-09-24 18:56:55+0400 | 0 |
with Conditional Query: -- here it does not work
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)
-- it returns 0 rows.
解决方案I see what is going on. You are using
now()
to generate a time-UUID. But when you convert that to atimestamp
usingdateOf()
you are truncating the milliseconds off of it. Therefore querying for areceived_time
equal to 2014-09-24 18:56:55+0400 will yield nothing, as thetimestamp
type is still stored with the milliseconds (you just can't see it due to yourdateOf()
).The best way to go about this, is to store your times as
timeuuid
s (NOTE: I leftreceived_date
as a timestamp just for purposes of the example). Then use thedateOf
when youSELECT
, and use theminTimeuuid()
function for yourWHERE
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)
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:Time series based queries in Cassandra 1.2+ and CQL3
这篇关于Cassandra CQL选择查询不返回具有时间戳为clusterkey的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!