incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Laing, Michael" <michael.la...@nytimes.com>
Subject Re: CQL and counters
Date Sat, 23 Nov 2013 03:01:48 GMT
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 <tyler@datastax.com> 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 <bryce.godfrey@avanade.com>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/>
>

Mime
View raw message