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
<marc.zianideferra...@sirca.org.au>
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
