> Hey guys, I need to retrieve a list of distinct users based on their activity
> datetime. How can I model a table to store that kind of information?
If it’s for an arbitrary time slice it will be tricky, if you can use pre set
time slices something like this would work:
CREATE TABLE (
timeslice_start timestamp,
timeslice_size int,
user text,
PRIMARY KEY ( (timeslice_start, timeslice_size), user)
);
That would give you the unique users in a time slice, e.g. unique for a 4 hour
window.
Cheers
-----------------
Aaron Morton
New Zealand
@aaronmorton
Co-Founder & Principal Consultant
Apache Cassandra Consulting
http://www.thelastpickle.com
On 9/11/2013, at 12:56 am, Franc Carter <franc.car...@sirca.org.au> wrote:
>
> How about something like using a time-range as the key (e.g an hour depending
> on your update rate) and a composite (time:user) as the column name
>
> cheers
>
>
>
> On Fri, Nov 8, 2013 at 10:45 PM, Laing, Michael <michael.la...@nytimes.com>
> wrote:
> You could try this:
>
> CREATE TABLE user_activity (shard text, user text, ts timeuuid, primary key
> (shard, ts));
>
> select user, ts from user_activity where shard in ('00', '01', ...) order by
> ts desc;
>
> Grab each user and ts the first time you see that user.
>
> Use as many shards as you think you need to control row size and spread the
> load.
>
> Set ttls to expire user_activity entries when you are no longer interested in
> them.
>
> ml
>
>
> On Fri, Nov 8, 2013 at 6:10 AM, pavli...@gmail.com <pavli...@gmail.com> wrote:
> Hey guys, I need to retrieve a list of distinct users based on their activity
> datetime. How can I model a table to store that kind of information?
>
> The straightforward decision was this:
>
> CREATE TABLE user_activity (user text primary key, ts timeuuid);
>
> but it turned out it is impossible to do a select like this:
>
> select * from user_activity order by ts;
>
> as it fails with "ORDER BY is only supported when the partition key is
> restricted by an EQ or an IN".
>
> How would you model the thing? Just need to have a list of users based on
> their last activity timestamp...
>
> Thanks!
>
>
>
>
>
> --
> Franc Carter | Systems architect | Sirca Ltd
> franc.car...@sirca.org.au | www.sirca.org.au
> Tel: +61 2 8355 2514
> Level 4, 55 Harrington St, The Rocks NSW 2000
> PO Box H58, Australia Square, Sydney NSW 1215
>
