Game API, ranking

[stijnbe]

Hi bigdata gurus,

For Ketnet I have to develop a game API on top of Apache Cassandra.
Creating a leaderboard and defining a model for storing high scores
wasn't that difficult. Now I also want to get the position of a user
in a leaderboard. I saw that with Redis it is possible to get the Rank
(http://redis.io/commands/zrank). How could I solve this problem with
apache cassandra?

Thanks

Stijn

[Davy Suvee]

Hi Stijn,

It probably depends a bit on how you defined your model. I would second-guess that you defined a ColumnFamily user, row key being the user id containing multiple columns, one being highscore.

In that model, put a secondary index on the highscore column. And use a IndexedSlicesQuery (hector-api: http://rantav.github.com/hector/source/content/API/core/1.0-1/me/prettyprint/cassandra/model/IndexedSlicesQuery.html) where you perform addGteExpression on highscore (i.e. finding all the users who have a bigger highscore). Counting the results will give you the position of the user in the leaderboard. (As far as I can see, no seperate count method exists. Performance would depend a bit on the number of users in the system I guess.)

Another trick would be to store an inverted view of your highscores. Create a ColumnFamily highscores with a single row where each column contains a highscore. The highscores column family would hence be a sorted collection of scores. Using a RangeSlicesCounterQuery  (http://rantav.github.com/hector/source/content/API/core/1.0-1/me/prettyprint/hector/api/query/RangeSlicesCounterQuery.html), range being the highest possible score and the highscore of the user, would retrieve the position of the specific highscore in the leaderboard. Now, this solution has 2 possible problems:

- Highscores will probably not be unique. My proposed solution would be to wrap the score as a TimeUUID. In this case, each highscore will be unique, while they remain sorted. When querying, you would first wrap the specific highscore as a TimeUUID.

- This single row would contains millions of columns. Although not really a problem, it could get crowded :-). A trick would be to set the Time to Live (http://www.datastax.com/dev/blog/whats-new-cassandra-07-expiring-columns) on each highscore. As a result, once a highscore is for instance older than 30 days, it would be automatically removed (if that of course would fit your usecase).

Davy

[stijnbe]

Hi Davy

Thanks for your answer and the solution for my problem. We are already
using an inverted view of the highscores where each column stores a
highscore as TimeUUID. I didn't know it was possible to do a get_count
on a range of columns.
I hope this will be performant enough as Apache Cassandra takes all
columns from disk to calculate the count.

Stijn

On Jan 17, 9:39 am, Davy Suvee <i...@datablend.be> wrote:
> Hi Stijn,
>
> It probably depends a bit on how you defined your model. I would
> second-guess that you defined a ColumnFamily user, row key being the user
> id containing multiple columns, one being highscore.
> In that model, put a secondary index on the highscore column. And use

> a *IndexedSlicesQuery
> *(hector-api:http://rantav.github.com/hector/source/content/API/core/1.0-1/me/pret...)
> where you perform *addGteExpression *on highscore (i.e. finding all the

> users who have a bigger highscore). Counting the results will give you the
> position of the user in the leaderboard. (As far as I can see, no seperate
> count method exists. Performance would depend a bit on the number of users
> in the system I guess.)
>
> Another trick would be to store an inverted view of your highscores. Create
> a ColumnFamily highscores with a single row where each column contains a
> highscore. The highscores column family would hence be a sorted collection

> of scores. Using a *RangeSlicesCounterQuery  *(http://rantav.github.com/hector/source/content/API/core/1.0-1/me/pret...),