Here's another example that may help:
-- put this in <file> AND run using 'cqlsh -f <file>
DROP KEYSPACE bryce_test;
CREATE KEYSPACE bryce_test WITH replication = {
'class': 'SimpleStrategy',
'replication_factor' : 1
};
USE bryce_test;
CREATE TABLE samples (
name text,
bucket text,
count counter,
total counter,
PRIMARY KEY (name, bucket)
);
UPDATE samples SET count = count + 1, total = total + 1 WHERE name='test'
AND bucket='2013-11-22T19:00';
UPDATE samples SET count = count + 1, total = total + 2 WHERE name='test'
AND bucket='2013-11-22T19:00';
UPDATE samples SET count = count + 1, total = total + 3 WHERE name='test'
AND bucket='2013-11-22T19:15';
UPDATE samples SET count = count + 1, total = total + 4 WHERE name='test'
AND bucket='2013-11-22T19:30';
UPDATE samples SET count = count + 1, total = total + 5 WHERE name='test'
AND bucket='2013-11-22T19:30';
SELECT * FROM samples;
SELECT * FROM samples
WHERE
name = 'test'
AND bucket >= '2013-11-22T19:30'
AND bucket <= '2013-11-22T19:45';
-- returns:
-- name | bucket | count | total
--------+------------------+-------+-------
-- test | 2013-11-22T19:00 | 2 | 3
-- test | 2013-11-22T19:15 | 1 | 3
-- test | 2013-11-22T19:30 | 2 | 9
--(3 rows)
-- name | bucket | count | total
--------+------------------+-------+-------
-- test | 2013-11-22T19:30 | 2 | 9
--(1 rows)
On Fri, Nov 22, 2013 at 7:21 PM, Tyler Hobbs <[email protected]> wrote:
> Something like this would work:
>
> CREATE TABLE foo (
> interface text,
> property text,
> bucket timestamp,
> count counter,
> PRIMARY KEY ((interface, property), bucket)
> )
>
> interface is 'NIC1' and property is 'Total' or 'Count'.
>
> To query over a date range, you'd run a query like:
>
> SELECT bucket, count FROM foo WHERE interface='NIC1' AND property='total'
> AND bucket > '2013-11-22 10:00:00' AND bucket < '2013-11-22 12:00:00';
>
>
> On Fri, Nov 22, 2013 at 4:48 PM, Bryce Godfrey
> <[email protected]>wrote:
>
>> I’m looking for some guidance on how to model some stat tracking over
>> time, bucketed to some type of interval (15 min, hour, etc).
>>
>>
>>
>> As an example, let’s say I would like to track network traffic throughput
>> and bucket it to 15 minute intervals. In our old model, using thrift I
>> would create a column family set to counter, and use a timestamp ticks for
>> the column name for a “total” and “count” column. And as data was sampled,
>> we would increment count by one, and increment the total with the sampled
>> value for that time bucket. The column name would give us the datetime for
>> the values, as well as provide me with a convenient row slice query to get
>> a date range for any given statistic.
>>
>>
>>
>> Key | 1215 | 1230 | 1245
>>
>> NIC1:Total | 100 | 56 | 872
>>
>> NIC1:Count | 15 | 15 | 15
>>
>>
>>
>> Then given the total/count I can show an average over time.
>>
>>
>>
>> In CQL it seems like I can’t do new counter columns at runtime unless
>> they are defined in the schema first or run an ALTER statement, which may
>> not be the correct way to go. So is there a better way to model this type
>> of data with the new CQL world? Nor do I know how to query that type of
>> data, similar to the row slice by column name.
>>
>>
>>
>> Thanks,
>>
>> Bryce
>>
>
>
>
> --
> Tyler Hobbs
> DataStax <http://datastax.com/>
>
