Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 6533B200BB7 for ; Wed, 9 Nov 2016 11:11:46 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 63BAF160AFD; Wed, 9 Nov 2016 10:11:46 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 5AE13160AEE for ; Wed, 9 Nov 2016 11:11:45 +0100 (CET) Received: (qmail 16630 invoked by uid 500); 9 Nov 2016 10:11:43 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 16620 invoked by uid 99); 9 Nov 2016 10:11:43 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 09 Nov 2016 10:11:43 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 57F65C0106 for ; Wed, 9 Nov 2016 10:11:43 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.879 X-Spam-Level: * X-Spam-Status: No, score=1.879 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd1-us-west.apache.org (amavisd-new); dkim=pass (1024-bit key) header.d=winguzone.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id PfQp6vDC5Zmt for ; Wed, 9 Nov 2016 10:11:41 +0000 (UTC) Received: from sender163-mail.zoho.com (sender163-mail.zoho.com [74.201.84.163]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 084C85FACC for ; Wed, 9 Nov 2016 10:11:34 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1478686286; s=zoho; d=winguzone.com; i=vladyu@winguzone.com; h=Date:From:To:Message-Id:In-Reply-To:References:Subject:MIME-Version:Content-Type; l=10756; bh=3erLnAC+UO43MHzKt03w+Bo3us0+SSokzGK6Xb2yIVw=; b=JGqfvvgmWx/6eZt54JqgXGVLuNAqi5MQhB1xkAchIPS79x8zsa/3JDaucOGu8bE1 zpLs4v5N6KIrJZhqYe8+1l4qNuztp0Rwj0SclhrQDw1W8wcyquZ0IaF1psGNcDzQRnx IkxuWLkTVfhD+4p/+5VJIgXOMHPqPghMwSRs3OLQ= Received: from mail.zoho.com by mx.zohomail.com with SMTP id 1478686286028649.2549086594572; Wed, 9 Nov 2016 02:11:26 -0800 (PST) Date: Wed, 09 Nov 2016 05:11:26 -0500 From: Vladimir Yudovin To: "user" Message-Id: <158489220c0.bda6a2eb120886.4737303318592947827@winguzone.com> In-Reply-To: References: ,<158445dcfc8.ca493bb728302.6473184930597507436@winguzone.com> Subject: =?UTF-8?Q?Re:_=E7=AD=94=E5=A4=8D:_A_difficult_data_model_with_C*?= MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_383316_294014251.1478686286024" X-Priority: Medium User-Agent: Zoho Mail X-Mailer: Zoho Mail archived-at: Wed, 09 Nov 2016 10:11:46 -0000 ------=_Part_383316_294014251.1478686286024 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 i= s 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&l= t;play>>); You can easily retrieve play list for specific user by his ID. Instead of L= IST you can use MAP, I don't think that for ten entries it matters. Best regards, Vladimir Yudovin,=20 Winguzone - Hosted Cloud Cassandra Launch your cluster in minutes. ---- On Tue, 08 Nov 2016 22:29:48 -0500ben ben <diamond.ben@outlook.com&= gt; wrote ---- Hi Vladimir Yudovin, Thank you very much for your detailed explaining. Maybe I didn't descri= be 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 t= he 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 =E5=8F=91=E4=BB=B6=E4=BA=BA: Vladimir Yudovin <vladyu@winguzone.com> =E5=8F=91=E9=80=81=E6=97=B6=E9=97=B4: 2016=E5=B9=B411=E6=9C=888=E6=97=A5 2= 2:35:48 =E6=94=B6=E4=BB=B6=E4=BA=BA: user =E4=B8=BB=E9=A2=98: Re: A difficult data model with C*=20 =20 Hi Ben, if need very limited number of positions (as you said ten) may be you can s= tore them in LIST of UDT? Or just as JSON string? So you'll have one row per each pair user-video.=20 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&l= t;play>>, PRIMARY KEY (user_name, video_id)); UPDATE recent set review =3D review + [(1234,12345)] where user_name=3D'som= e user' AND video_id=3D'great video'; UPDATE recent set review =3D review + [(1234,123456)] where user_name=3D'so= me user' AND video_id=3D'great video'; UPDATE recent set review =3D review + [(1234,1234567)] where user_name=3D's= ome user' AND video_id=3D'great video'; You can delete the oldest entry by index: DELETE review[0] FROM recent WHERE user_name=3D'some user' AND video_id=3D'= great video'; or by value, if you know the oldest entry: UPDATE recent SET review =3D review - [(1234,12345)] WHERE user_name=3D'so= me user' AND video_id=3D'great video'; Best regards, Vladimir Yudovin,=20 Winguzone - Hosted Cloud Cassandra Launch your cluster in minutes. ---- On Mon, 07 Nov 2016 21:54:08 -0500ben ben <diamond.ben@outlook.com&= gt; wrote ---- Hi guys, We are maintaining a system for an on-line video service. ALL users' viewin= g 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 pr= efer to order by the last_time desc. If we use last_time as cluster key, th= ere will be many records for a singe movie and the recent one is actually d= esired. So how to model that? Do you have any suggestions?=20 Thanks! BRs, BEN ------=_Part_383316_294014251.1478686286024 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =
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 demand= ed 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. Instea= d of LIST you can use MAP, I don't think that for ten entries it matters.


Best regards, Vladimir = Yudovin,
Winguzone - Hosted Cloud Cassandra
Launch your clust= er in minutes.


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

H= i Vladimir Yudovin,


=

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

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 tim= es by a user and the last positon is needed indeed.


<= p>BRs,

BEN


=E5=8F=91=E4= =BB=B6=E4=BA=BA: Vladimir Yudovin <vladyu@winguzone.com>
=E5=8F=91=E9= =80=81=E6=97=B6=E9=97=B4: 2016=E5=B9=B411=E6=9C=888=E6=97=A5 22:35:48 =E6=94=B6=E4=BB=B6=E4=BA=BA: user
=E4=B8=BB=E9=A2=98: = 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 the= m in LIST of UDT? Or just as JSON string?
So you'll have one = row per each pair user-video.

It can be somet= hing 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_nam= e, video_id));

UPDATE recent set review =3D re= view + [(1234,12345)] where user_name=3D'some user' AND video_id=3D'great v= ideo';
UPDATE recent set review =3D review + [(1234,123456)] = where user_name=3D'some user' AND video_id=3D'great video';
U= PDATE recent set review =3D review + [(1234,1234567)] where user_name=3D'so= me user' AND video_id=3D'great video';

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

or by value, if you know the oldest entry:
<= br>
UPDATE recent SET review =3D review - [(1234,12345)]  WH= ERE user_name=3D'some user' AND video_id=3D'great video';
Best regards, Vladimir Yudovin,
Winguzone - Hos= ted Cloud Cassandra
Launch your cluster in minutes.


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


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 c= ontinue to enjoy the movie from the last point next time. The table is desi= gned as below:
CREATE TABLE recent (
user_name = text,
vedio_id text,
position int,
last_time timestamp,
PRIMARY KEY (user_name, vedio_id)
<= /div>
)

It worked well before. However, th= e 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 de= sc. If we use last_time as cluster key, there will be many records for a si= nge movie and the recent one is actually desired. So how to model that? Do = you have any suggestions?
Thanks!


BRs,
BEN



=

------=_Part_383316_294014251.1478686286024--