Note that it seems to work when you structure your schema in this example
below, BUT this is a problem because all of my data will wind up hitting a
single node in my cassandra cluster because the partitioning key is "counter"
and that isn't unique enough. I was hoping that I wasn't going to need to
build up my own "sharding" scheme as this blog talks about
(http://www.datastax.com/dev/blog/advanced-time-series-with-cassandra) because
this becomes much harder for other clients to integrate with because they now
need to know how my data is structured in order to get it out.
CREATE TABLE count5 (
counter text,
ts timeuuid,
key1 text,
value int,
PRIMARY KEY (counter, ts)
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=864000 AND
read_repair_chance=0.100000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'SnappyCompressor'};
cqlsh:Test> select counter,dateof(ts),key1,value from count5 where counter =
'test' and ts > minTimeuuid('2013-06-17 22:36:16') and ts <
minTimeuuid('2013-06-18 22:44:02');
counter | dateof(ts) | key1 | value
---------+--------------------------+------+-------
test | 2013-06-18 22:43:53-0400 | 1 | 1
test | 2013-06-18 22:43:54-0400 | 1 | 1
test | 2013-06-18 22:43:55-0400 | 1 | 1
test | 2013-06-18 22:43:56-0400 | 1 | 1
test | 2013-06-18 22:43:58-0400 | 1 | 1
test | 2013-06-18 22:43:58-0400 | 1 | 1
test | 2013-06-18 22:43:59-0400 | 1 | 1
test | 2013-06-18 22:44:00-0400 | 1 | 1
test | 2013-06-18 22:44:01-0400 | 1 | 1
cqlsh:Test> select counter,dateof(ts),key1,value from count5 where counter =
'test' and ts > minTimeuuid('2013-06-17 22:36:16') and ts <
minTimeuuid('2013-06-20 22:44:02');
counter | dateof(ts) | key1 | value
---------+--------------------------+------+-------
test | 2013-06-18 22:43:53-0400 | 1 | 1
test | 2013-06-18 22:43:54-0400 | 1 | 1
test | 2013-06-18 22:43:55-0400 | 1 | 1
test | 2013-06-18 22:43:56-0400 | 1 | 1
test | 2013-06-18 22:43:58-0400 | 1 | 1
test | 2013-06-18 22:43:58-0400 | 1 | 1
test | 2013-06-18 22:43:59-0400 | 1 | 1
test | 2013-06-18 22:44:00-0400 | 1 | 1
test | 2013-06-18 22:44:01-0400 | 1 | 1
test | 2013-06-18 22:44:02-0400 | 1 | 1
test | 2013-06-18 22:44:02-0400 | 1 | 1
test | 2013-06-18 22:44:03-0400 | 1 | 1
test | 2013-06-18 22:44:04-0400 | 1 | 1
test | 2013-06-18 22:44:05-0400 | 1 | 1
test | 2013-06-18 22:44:06-0400 | 1 | 1
From: <Ryan>, Brent Ryan <br...@cvent.com<mailto:br...@cvent.com>>
Reply-To: "user@cassandra.apache.org<mailto:user@cassandra.apache.org>"
<user@cassandra.apache.org<mailto:user@cassandra.apache.org>>
Date: Wednesday, June 19, 2013 12:56 PM
To: "user@cassandra.apache.org<mailto:user@cassandra.apache.org>"
<user@cassandra.apache.org<mailto:user@cassandra.apache.org>>
Subject: Re: timeuuid and cql3 query
Here's an example of that not working:
cqlsh:Test> desc table count4;
CREATE TABLE count4 (
ts timeuuid,
counter text,
key1 text,
value int,
PRIMARY KEY (ts, counter)
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=864000 AND
read_repair_chance=0.100000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'SnappyCompressor'};
cqlsh:Test> select counter,dateof(ts),key1,value from count4;
counter | dateof(ts) | key1 | value
---------+--------------------------+------+-------
test | 2013-06-18 22:36:16-0400 | 1 | 1
test | 2013-06-18 22:36:18-0400 | 1 | 1
test | 2013-06-18 22:36:18-0400 | 1 | 1
test | 2013-06-18 22:36:18-0400 | 1 | 1
test | 2013-06-18 22:36:19-0400 | 1 | 1
test | 2013-06-18 22:36:19-0400 | 1 | 1
test | 2013-06-18 22:36:20-0400 | 1 | 1
test | 2013-06-18 22:36:20-0400 | 1 | 1
test | 2013-06-18 22:36:21-0400 | 1 | 1
test | 2013-06-18 22:36:21-0400 | 1 | 1
test | 2013-06-18 22:36:22-0400 | 1 | 1
test | 2013-06-18 22:36:22-0400 | 1 | 1
test | 2013-06-18 22:36:23-0400 | 1 | 1
test | 2013-06-18 22:36:23-0400 | 1 | 1
test | 2013-06-18 22:36:25-0400 | 1 | 1
test | 2013-06-18 22:36:27-0400 | 1 | 1
test | 2013-06-18 22:36:28-0400 | 1 | 1
cqlsh:Statistics> select counter,dateof(ts),key1,value from count4 where ts >
minTimeuuid('2013-06-17 22:36:16') and ts < minTimeuuid('2013-06-19 22:36:20');
Bad Request: 2 Start key must sort before (or equal to) finish key in your
partitioner!
Any ideas? Seems like a bug to me, right?
Brent
From: <Ryan>, Brent Ryan <br...@cvent.com<mailto:br...@cvent.com>>
Reply-To: "user@cassandra.apache.org<mailto:user@cassandra.apache.org>"
<user@cassandra.apache.org<mailto:user@cassandra.apache.org>>
Date: Wednesday, June 19, 2013 12:47 PM
To: "user@cassandra.apache.org<mailto:user@cassandra.apache.org>"
<user@cassandra.apache.org<mailto:user@cassandra.apache.org>>
Subject: Re: timeuuid and cql3 query
Tyler,
You're recommending this schema instead, correct?
CREATE TABLE count3 (
counter text,
ts timeuuid,
key1 text,
value int,
PRIMARY KEY (ts, counter)
)
I believe I tried this as well and ran into similar problems but I'll try it
again. I'm using the "ByteOrderedPartitioner" if that helps with the latest
version of DSE community edition which I believe is Cassandra 1.2.3.
Thanks,
Brent
From: Tyler Hobbs <ty...@datastax.com<mailto:ty...@datastax.com>>
Reply-To: "user@cassandra.apache.org<mailto:user@cassandra.apache.org>"
<user@cassandra.apache.org<mailto:user@cassandra.apache.org>>
Date: Wednesday, June 19, 2013 11:00 AM
To: "user@cassandra.apache.org<mailto:user@cassandra.apache.org>"
<user@cassandra.apache.org<mailto:user@cassandra.apache.org>>
Subject: Re: timeuuid and cql3 query
On Wed, Jun 19, 2013 at 8:08 AM, Ryan, Brent
<br...@cvent.com<mailto:br...@cvent.com>> wrote:
CREATE TABLE count3 (
counter text,
ts timeuuid,
key1 text,
value int,
PRIMARY KEY ((counter, ts))
)
Instead of doing a composite partition key, remove a set of parens and let ts
be your clustering key. That will cause cql rows to be stored in sorted order
by the ts column (for a given value of "counter") and allow you to do the kind
of query you're looking for.
--
Tyler Hobbs
DataStax<http://datastax.com/>
