cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Diamond ben <diamond....@outlook.com>
Subject 答复: 答复: A difficult data model with C*
Date Thu, 10 Nov 2016 01:47:05 GMT
The solution maybe work. However, the play list will grow over time and somebody maybe has
ten thousands that will slow down the query and sort . Do you mean the oldest one should be
removed when a new play is added?

BTW, the version is 2.1.16 in our live system.


BRs,

BEN

________________________________
发件人: Vladimir Yudovin <vladyu@winguzone.com>
发送时间: 2016年11月9日 18:11:26
收件人: user
主题: Re: 答复: A difficult data model with C*

You are welcome! )

>recent ten movies watched by the user within 30 days.
In this case you can't use PRIMARY KEY (user_name, video_id), as video_id is demanded to fetch
row, so all this stuff may be
CREATE TYPE play (video_id text, position int, last_time timestamp);
CREATE TABLE recent (user_name text PRIMARY KEY, play_list LIST<frozen<play>>);
You can easily retrieve play list for specific user by his ID. Instead of LIST you can use
MAP, I don't think that for ten entries it matters.


Best regards, Vladimir Yudovin,
Winguzone<https://winguzone.com?from=list> - Hosted Cloud Cassandra
Launch your cluster in minutes.


---- On Tue, 08 Nov 2016 22:29:48 -0500ben ben <diamond.ben@outlook.com> wrote ----


Hi Vladimir Yudovin,


    Thank you very much for your detailed explaining. Maybe I didn't describe the requirement
clearly. The use cases should be:

1. a user login our app.

2. show the recent ten movies watched by the user within 30 days.

3. the user can click any one of the ten movie and continue to watch from the last position
she/he did. BTW, a movie can be watched several times by a user and the last positon is needed
indeed.


BRs,

BEN

________________________________

发件人: Vladimir Yudovin <vladyu@winguzone.com<mailto:vladyu@winguzone.com>>
发送时间: 2016年11月8日 22:35:48
收件人: user
主题: Re: A difficult data model with C*

Hi Ben,

if need very limited number of positions (as you said ten) may be you can store them in LIST
of UDT? Or just as JSON string?
So you'll have one row per each pair user-video.

It can be something like this:

CREATE TYPE play (position int, last_time timestamp);
CREATE TABLE recent (user_name text, video_id text, review LIST<frozen<play>>,
PRIMARY KEY (user_name, video_id));

UPDATE recent set review = review + [(1234,12345)] where user_name='some user' AND video_id='great
video';
UPDATE recent set review = review + [(1234,123456)] where user_name='some user' AND video_id='great
video';
UPDATE recent set review = review + [(1234,1234567)] where user_name='some user' AND video_id='great
video';

You can delete the oldest entry by index:
DELETE review[0] FROM recent WHERE user_name='some user' AND video_id='great video';

or by value, if you know the oldest entry:

UPDATE recent SET review = review - [(1234,12345)]  WHERE user_name='some user' AND video_id='great
video';

Best regards, Vladimir Yudovin,
Winguzone<https://winguzone.com?from=list> - Hosted Cloud Cassandra
Launch your cluster in minutes.


---- On Mon, 07 Nov 2016 21:54:08 -0500ben ben <diamond.ben@outlook.com<mailto:diamond.ben@outlook.com>>
wrote ----



Hi guys,

We are maintaining a system for an on-line video service. ALL users' viewing records of every
movie are stored in C*. So she/he can continue to enjoy the movie from the last point next
time. The table is designed as below:
CREATE TABLE recent (
user_name text,
vedio_id text,
position int,
last_time timestamp,
PRIMARY KEY (user_name, vedio_id)
)

It worked well before. However, the records increase every day and the last ten items may
be adequate for the business. The current model use vedio_id as cluster key to keep a row
for a movie, but as you know, the business prefer to order by the last_time desc. If we use
last_time as cluster key, there will be many records for a singe movie and the recent one
is actually desired. So how to model that? Do you have any suggestions?
Thanks!


BRs,
BEN



Mime
View raw message