Yes. The problem is that I can't use "counter" as the partition key otherwise I'd wind up with hot spots in my cluster where majority of the data is being written to single node in the cluster. The only real way around this problem with Cassandra is to follow along with what this blog does:
http://www.datastax.com/dev/blog/advanced-time-series-with-cassandra From: Eric Stevens <migh...@gmail.com<mailto:migh...@gmail.com>> Reply-To: "user@cassandra.apache.org<mailto:user@cassandra.apache.org>" <user@cassandra.apache.org<mailto:user@cassandra.apache.org>> Date: Friday, June 21, 2013 8:38 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 It's my understanding that if cardinality of the first part of the primary key has low cardinality, you will struggle with cluster balance as (unless you use WITH COMPACT STORAGE) the first entry of the primary key equates to the row key from the traditional interface, thus all entries related to a single value for the "counter" column will map to the same partition. So consider the cardinality of this field, if cardinality is low, you might need to remodel with PRIMARY KEY (counter, ts, key1) then tack on WITH COMPACT STORAGE (then the entire primary key becomes the row key, but you can only have one column which is not part of the primary key) If cardinality of "counter" is high, then you have nothing to worry about. On Wed, Jun 19, 2013 at 3:16 PM, Francisco Andrades Grassi <bigjoc...@gmail.com<mailto:bigjoc...@gmail.com>> wrote: Hi, I believe what he's recommending is: CREATE TABLE count3 ( counter text, ts timeuuid, key1 text, value int, PRIMARY KEY (counter, ts) ) That way counter will be your partitioning key, and all the rows that have the same counter value will be clustered (stored as a single wide row sorted by the ts value). In this scenario the query: where counter = 'test' and ts > minTimeuuid('2013-06-18 16:23:00') and ts < minTimeuuid('2013-06-18 16:24:00'); would actually be a sequential read on a wide row on a single node. -- Francisco Andrades Grassi www.bigjocker.com<http://www.bigjocker.com/> @bigjocker On Jun 19, 2013, at 12:17 PM, "Ryan, Brent" <br...@cvent.com<mailto:br...@cvent.com>> wrote: 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/>
