Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (athena.apache.org: domain of jlacefield@datastax.com
 designates 209.85.128.169 as permitted sender)
References: 
 <CACz-ikvL+8PPS4GsZOScEboVtS2HE8=ee_4o5rkh4SL-d8yguA@mail.gmail.com>
From: Jonathan Lacefield <jlacefield@datastax.com>
Mime-Version: 1.0 (1.0)
In-Reply-To: 
 <CACz-ikvL+8PPS4GsZOScEboVtS2HE8=ee_4o5rkh4SL-d8yguA@mail.gmail.com>
Date: Tue, 25 Feb 2014 19:51:45 -0500
Message-ID: <4219401695378628553@unknownmsgid>
Subject: Re: Getting the most-recent version from time-series data
To: "user@cassandra.apache.org" <user@cassandra.apache.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Clint

   One approach would be to create a copy of this table and switch the
clustering columns around so version precedes family.  This way you
could easily grab the 1st, 2nd, N version rows.  Would this help you
in your situation?

Jonathan

> On Feb 25, 2014, at 7:49 PM, Clint Kelly <clint.kelly@gmail.com> wrote:
>
> Hi everyone,
>
> Let's say that I have a table that looks like the following:
>
> CREATE TABLE time_series_stuff (
>   key text,
>   family text,
>   version int,
>   val text,
>   PRIMARY KEY (key, family, version)
> ) WITH CLUSTERING ORDER BY (family ASC, version DESC) AND
>   bloom_filter_fp_chance=3D0.010000 AND
>   caching=3D'KEYS_ONLY' AND
>   comment=3D'' AND
>   dclocal_read_repair_chance=3D0.000000 AND
>   gc_grace_seconds=3D864000 AND
>   index_interval=3D128 AND
>   read_repair_chance=3D0.100000 AND
>   replicate_on_write=3D'true' AND
>   populate_io_cache_on_flush=3D'false' AND
>   default_time_to_live=3D0 AND
>   speculative_retry=3D'99.0PERCENTILE' AND
>   memtable_flush_period_in_ms=3D0 AND
>   compaction=3D{'class': 'SizeTieredCompactionStrategy'} AND
>   compression=3D{'sstable_compression': 'LZ4Compressor'};
>
> cqlsh:fiddle> select * from time_series_stuff ;
>
>  key    | family  | version | val
> --------+---------+---------+--------
>  monday | revenue |       3 | $$$$$$
>  monday | revenue |       2 |    $$$
>  monday | revenue |       1 |     $$
>  monday | revenue |       0 |      $
>  monday | traffic |       2 | medium
>  monday | traffic |       1 |  light
>  monday | traffic |       0 |  heavy
>
> (7 rows)
>
> Now let's say that I'd like to perform a query that gets me the most rece=
nt N versions of "revenue" and "traffic."
>
> Is there a CQL query to do this?  Let's say that N=3D1.  Then I know that=
 I can do:
>
> cqlsh:fiddle> select * from time_series_stuff where key=3D'monday' and fa=
mily=3D'revenue' limit 1;
>
>  key    | family  | version | val
> --------+---------+---------+--------
>  monday | revenue |       3 | $$$$$$
>
> (1 rows)
>
> cqlsh:fiddle> select * from time_series_stuff where key=3D'monday' and fa=
mily=3D'traffic' limit 1;
>
>  key    | family  | version | val
> --------+---------+---------+--------
>  monday | traffic |       2 | medium
>
> (1 rows)
>
> But what if I have lots of "families" and I want to get the most recent N=
 versions of all of them in a single CQL statement.  Is that possible?  Unf=
ortunately I am working on something where the family names and the number =
of most-recent versions are not known a priori (I am porting some code that=
 was designed for HBase).
>
> Best regards,
> Clint