Return-Path: X-Original-To: apmail-cassandra-user-archive@www.apache.org Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 1C6A7107AE for ; Wed, 26 Feb 2014 15:34:11 +0000 (UTC) Received: (qmail 8380 invoked by uid 500); 26 Feb 2014 15:34:06 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 7723 invoked by uid 500); 26 Feb 2014 15:33:54 -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 7714 invoked by uid 99); 26 Feb 2014 15:33:52 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 26 Feb 2014 15:33:52 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_NONE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [15.201.208.54] (HELO g4t3426.houston.hp.com) (15.201.208.54) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 26 Feb 2014 15:33:46 +0000 Received: from G9W0364.americas.hpqcorp.net (g9w0364.houston.hp.com [16.216.193.45]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by g4t3426.houston.hp.com (Postfix) with ESMTPS id 0DE0632F for ; Wed, 26 Feb 2014 15:33:26 +0000 (UTC) Received: from G4W6303.americas.hpqcorp.net (16.210.26.228) by G9W0364.americas.hpqcorp.net (16.216.193.45) with Microsoft SMTP Server (TLS) id 14.3.123.3; Wed, 26 Feb 2014 15:32:33 +0000 Received: from G4W3292.americas.hpqcorp.net ([169.254.1.181]) by G4W6303.americas.hpqcorp.net ([16.210.26.228]) with mapi id 14.03.0123.003; Wed, 26 Feb 2014 15:32:33 +0000 From: "Green, John M (HP Education)" To: "user@cassandra.apache.org" Subject: RE: Naive question about orphan rows Thread-Topic: Naive question about orphan rows Thread-Index: Ac8vGpPY63rtFOW4Qmaj1QpkLHZ+GgD39gGAAAJN57A= Date: Wed, 26 Feb 2014 15:32:32 +0000 Message-ID: <9FD9C2860A087F4398EB3FB01F19AA5F6934B2BF@G4W3292.americas.hpqcorp.net> References: <9FD9C2860A087F4398EB3FB01F19AA5F6933E40F@G9W0751.americas.hpqcorp.net> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [16.210.48.25] Content-Type: multipart/alternative; boundary="_000_9FD9C2860A087F4398EB3FB01F19AA5F6934B2BFG4W3292americas_" MIME-Version: 1.0 X-Virus-Checked: Checked by ClamAV on apache.org --_000_9FD9C2860A087F4398EB3FB01F19AA5F6934B2BFG4W3292americas_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Edward, Thanks for your insight. One other thought I had was to store a reference count with the "song". Wh= en the last "playlist" referencing the "song" is deleted the "song" will al= so be deleted because the reference count decrements to zero. However, th= is would create some nastiness when it comes to reliably maintaining refere= nce counts. I'm not sure if it would help to split the reference count in= to two monotonically increasing counters (number of references added, and n= umber of references deleted). In my case, users cannot browse a repository of "songs" to build a playlist= from scratch. They can only import "songs" themselves or create reference= s to "songs" other users have explicitly made available to them. Once a "s= ong" is not referred to by any "playlist" it will never be re-discovered so= it should be deleted. This could be done in some sort of background data= maintenance job that runs periodically. Even if it is a low-priority bac= kground job it look like it will create a lot overhead (scanning and produc= ing counts). John From: Edward Capriolo [mailto:edlinuxguru@gmail.com] Sent: Wednesday, February 26, 2014 5:56 AM To: user@cassandra.apache.org Subject: Re: Naive question about orphan rows It is probably ok to have redundant songs in playlists, cassandra is about = denormalization. Dealing with this issue is going to be hard since the only way to dwal with= this would be scanning through the firsr cf and procing counts then using = that information to delete in the second table. However that information ca= n change rapidly and then will fall out of sink fast. The only ways yo handle this are 1) never delete songs 2) store copies of songs ib playlist On Friday, February 21, 2014, Green, John M (HP Education) > wrote: > I'm very much a newbie so this may be a silly question but ... > > > > I have a situation similar to the music service example (http://www.datas= tax.com/documentation/cql/3.1/cql/ddl/ddl_music_service_c.html) of songs an= d playlists. However, in my case, the "songs" would be considered orphans = that should be deleted when no "playlists" refer to them. Relational datab= ases have mechanisms to manage this relationship so that a "song" could be = deleted as soon as the last "playlist" referencing it is deleted. While = I do NOT need to manage this as an atomic transaction, I'm wondering what i= s the best way to delete orphaned rows (i.e., "songs" not referenced by any= "playlists") using Cassandra. > > > > I guess an alternative approach would be to store "songs" directly in the= "playlists" but this could lead to many redundant copies of the same "song= " which is something I'm hoping to avoid. I'm my case the "playlists" coul= d have thousands of entries and the "songs" might be blobs of 10s of Mbytes= . Maybe I'm just having a hard time abandoning my relational roots? > > > > John -- Sorry this was sent from mobile. Will do less grammar and spell check than = usual. --_000_9FD9C2860A087F4398EB3FB01F19AA5F6934B2BFG4W3292americas_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Edward,=


Thanks for your insight. 

 <= /p>

One other thought I had w= as to store a reference count with the “song”.  When the l= ast “playlist” referencing the “song” is deleted th= e “song” will also be deleted because the reference count decrements to zero.   Howeve= r, this would create some nastiness when it comes to reliably maintaining r= eference counts.   I’m not sure if it would help to split t= he reference count into two monotonically increasing counters (number of references added, and number of references deleted).  = ;

 <= /p>

In my case, users cannot = browse a repository of “songs” to build a playlist from scratch= .  They can only import “songs” themselves or create refer= ences to “songs” other users have explicitly made available to them.  Once a “so= ng” is not referred to by any “playlist” it will never be= re-discovered so it should be deleted.   This could be done in s= ome sort of background data maintenance job that runs periodically. &n= bsp; Even if it is a low-priority background job it look like it will create a lot o= verhead (scanning and producing counts).

 <= /p>

John

From: Edward C= apriolo [mailto:edlinuxguru@gmail.com]
Sent: Wednesday, February 26, 2014 5:56 AM
To: user@cassandra.apache.org
Subject: Re: Naive question about orphan rows

 

It is probably ok to have redundant songs in playlis= ts, cassandra is about denormalization.

Dealing with this issue is going to be hard since the only way to dwal with= this would be scanning through the firsr cf and procing counts then using = that information to delete in the second table. However that information ca= n change rapidly and then will fall out of sink fast.

The only ways yo handle this are

1) never delete songs
2) store copies of songs ib playlist

On Friday, February 21, 2014, Green, John M (HP Education) <john.green@hp.com> wrote:
> I’m very much a newbie so this may be a silly question but ̷= 0;
>
>  
>
> I have a situation similar to the music service example (http://www.datastax.com/documentation/cql/3.1/cql/ddl/ddl_music_service_= c.html) of songs and playlists.  However, in my case, the “songs” would be considered orphans t= hat should be deleted when no “playlists” refer to them.  = Relational databases have mechanisms to manage this relationship so that a = “song” could be deleted as soon as the last “playlistR= 21; referencing it is deleted.    While I do NOT need to manage this as an = atomic transaction, I’m wondering what is the best way to delete orph= aned rows (i.e., “songs” not referenced by any “playlists= ”)  using Cassandra.    
>
>  
>
> I guess an alternative approach would be to store “songs” = directly in the “playlists” but this could lead to many redunda= nt copies of the same “song” which is something I’m hopin= g to avoid.  I’m my case the “playlists” could have = thousands of entries and the “songs” might be blobs of 10s of Mbytes.    = ;Maybe I’m just having a hard time abandoning my relational roots? >
>  
>
> John

--
Sorry this was sent from mobile. Will do less grammar and spell check than = usual.

--_000_9FD9C2860A087F4398EB3FB01F19AA5F6934B2BFG4W3292americas_--