首页 文章

Cassandra CQL选择查询不返回时间戳为clusterkey的记录

提问于
浏览 次
2

Cassandra CQL：使用组合键和集群键创建的表 . 当我尝试从分区键执行select *然后我能够检索所有数据，它也适用于关系运算符（<或>） . 但是当我使用具有适当值的等于（=）运算符查询特定簇密钥时，它返回0行 .

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: - 这里不起作用

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行 .
cassandra cassandra-2.0 cql3 nosql

1 回答

  • 3

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

    解决此问题的最佳方法是将时间存储为 timeuuid s（注意：我将 received_date 留作时间戳仅用于示例目的） . 然后在 SELECT 时使用 dateOf ，并使用 minTimeuuid() 函数作为 WHERE 子句：

    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() 函数被设计用于查询数据，而不是存储数据 . 这是一篇博客文章，描述了（更详细）如何使这项工作：

    Time series based queries in Cassandra 1.2+ and CQL3

    回复于

相关问题