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 08C2F10D1C for ; Wed, 4 Sep 2013 14:24:15 +0000 (UTC) Received: (qmail 48850 invoked by uid 500); 4 Sep 2013 14:24:11 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 48545 invoked by uid 500); 4 Sep 2013 14:24:11 -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 48535 invoked by uid 99); 4 Sep 2013 14:24:10 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 04 Sep 2013 14:24:10 +0000 X-ASF-Spam-Status: No, hits=2.5 required=5.0 tests=FREEMAIL_REPLY,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of muhammed.dawood@gmail.com designates 209.85.128.170 as permitted sender) Received: from [209.85.128.170] (HELO mail-ve0-f170.google.com) (209.85.128.170) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 04 Sep 2013 14:24:04 +0000 Received: by mail-ve0-f170.google.com with SMTP id 15so272278vea.1 for ; Wed, 04 Sep 2013 07:23:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=s+3HINIaDGhetLw/TLFcuVHvP17TZUTBueE6u9yd5Sw=; b=w7f/u4fLbyg48BdhW7N0uCQAdpiP6PJmSoe0zypMIA4LAfYltkxobKjjr7Qa1hxHQM U+DUT0YtvFaCMU1/98bIQb9KyCkM0/kVeCvLsOZ+obIEJ14QcO3lEhrQdd+tmSbwYgvS PRERQTeZ8/0FEqzCkBFE8NNR4YPw5p+iNndwZ90NRUxjkwdKjQoQtJ6stfoARZZ1142a faH506nchfLydilL3tlVE7b6SAV2YLcIe65uNxVRZPaAiiJks1ihl8Dt5opFY0biSb3i VbCtB/qHT+nFdJkYM/onTgJod+vBkaE8ZTaVgopO5lshFBuDMe/8ol/56wzwpCWszJHe +2HA== MIME-Version: 1.0 X-Received: by 10.220.145.75 with SMTP id c11mr319410vcv.30.1378304623124; Wed, 04 Sep 2013 07:23:43 -0700 (PDT) Received: by 10.52.180.67 with HTTP; Wed, 4 Sep 2013 07:23:43 -0700 (PDT) In-Reply-To: References: <8A136D81-BC95-4605-B0C6-C48659876598@nordsc.com> Date: Wed, 4 Sep 2013 19:53:43 +0530 Message-ID: Subject: Re: Versioning in cassandra From: dawood abdullah To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=047d7b3439707425e904e58f8d41 X-Virus-Checked: Checked by ClamAV on apache.org --047d7b3439707425e904e58f8d41 Content-Type: text/plain; charset=ISO-8859-1 Thanks for the quick response Michael, looks like I have to go with the solution you have given of maps, as performance is pretty critical for our application and we do not have enough time to test. Appreciate your help. Regards, Dawood On Wed, Sep 4, 2013 at 7:33 PM, Laing, Michael wrote: > Dawood, > > In general that will work. However it does mean that you 1) read the old > version 2) update the new version and 3) write the archive version. > > Step 2 is a problem: what if someone else has updated the old version > after step 1? and there are 3 atomic operations required, at least. > > However, these considerations may be mitigated using Cassandra 2 light > transactions; and it is not a problem if you have only one updater. > > But another problem may be performance. You must test. The solution I > proposed does not require a read before write and does an atomic append, > even if multiple maps are being updated. It also defers deletions via ttl's > and a separate, manageable queue for 'cleanup' of large maps. > > I think the most important word in my reply is: 'test'. > > Cheers, > > Michael > > > On Wed, Sep 4, 2013 at 9:05 AM, dawood abdullah > wrote: > >> Michael, >> >> Your approach solves the problem, thanks for the solution. I was thinking >> of another approach as well where in I would create another column family >> say file_archive, so whenever an update is made to the File table, I will >> create a new version in the File and move the old version to the new >> file_archive table. Please let me know if the second approach is fine. >> >> Regards, >> Dawood >> >> >> On Wed, Sep 4, 2013 at 2:47 AM, Laing, Michael > > wrote: >> >>> I use the technique described in my previous message to handle millions >>> of messages and their versions. >>> >>> Actually, I use timeuuid's instead of timestamps, as they have more >>> 'uniqueness'. Also I index my maps by a timeuuid that is the complement >>> (based on a future date) of a current timeuuid. Since maps are kept sorted >>> by key, this means I can just pop off the first one to get the most recent. >>> >>> The downside of this approach is that you get more stuff returned to you >>> from Cassandra than you need. To mitigate that I queue a job to examine and >>> correct the situation if, upon doing a read, the number of versions for a >>> particular key is higher than some threshold, e.g. 50. There are many ways >>> to approach this problem. >>> >>> Our actual implementation proceeds to another level, as we also have >>> replicas of versions. This happens because we process important >>> transactions in parallel and can expect up to 9 replicas of each version. >>> We journal them all and use them for reporting latencies in our processing >>> pipelines as well as for replay when we need to recover application state. >>> >>> Regards, >>> >>> Michael >>> >>> >>> On Tue, Sep 3, 2013 at 3:15 PM, Laing, Michael < >>> michael.laing@nytimes.com> wrote: >>> >>>> try the following. -ml >>>> >>>> -- put this in and run using 'cqlsh -f >>>> >>>> DROP KEYSPACE latest; >>>> >>>> CREATE KEYSPACE latest WITH replication = { >>>> 'class': 'SimpleStrategy', >>>> 'replication_factor' : 1 >>>> }; >>>> >>>> USE latest; >>>> >>>> CREATE TABLE file ( >>>> parentid text, -- row_key, same for each version >>>> id text, -- column_key, same for each version >>>> contenttype map, -- differs by version, version is >>>> the key to the map >>>> PRIMARY KEY (parentid, id) >>>> ); >>>> >>>> update file set contenttype = contenttype + {'2011-03-04':'pdf1'} where >>>> parentid = 'd1' and id = 'f1'; >>>> update file set contenttype = contenttype + {'2011-03-05':'pdf2'} where >>>> parentid = 'd1' and id = 'f1'; >>>> update file set contenttype = contenttype + {'2011-03-04':'pdf3'} where >>>> parentid = 'd1' and id = 'f2'; >>>> update file set contenttype = contenttype + {'2011-03-05':'pdf4'} where >>>> parentid = 'd1' and id = 'f2'; >>>> >>>> select * from file where parentid = 'd1'; >>>> >>>> -- returns: >>>> >>>> -- parentid | id | contenttype >>>> >>>> ------------+----+-------------------------------------------------------------------------- >>>> -- d1 | f1 | {'2011-03-04 00:00:00-0500': 'pdf1', '2011-03-05 >>>> 00:00:00-0500': 'pdf2'} >>>> -- d1 | f2 | {'2011-03-04 00:00:00-0500': 'pdf3', '2011-03-05 >>>> 00:00:00-0500': 'pdf4'} >>>> >>>> -- use an app to pop off the latest version from the map >>>> >>>> -- map other varying fields using the same technique as used for >>>> contenttype >>>> >>>> >>>> >>>> On Tue, Sep 3, 2013 at 2:31 PM, Vivek Mishra wrote: >>>> >>>>> create table file(id text , parentid text,contenttype text,version >>>>> timestamp, descr text, name text, PRIMARY KEY(id,version) ) WITH CLUSTERING >>>>> ORDER BY (version DESC); >>>>> >>>>> insert into file (id, parentid, version, contenttype, descr, name) >>>>> values ('f2', 'd1', '2011-03-06', 'pdf', 'f2 file', 'file1'); >>>>> insert into file (id, parentid, version, contenttype, descr, name) >>>>> values ('f2', 'd1', '2011-03-05', 'pdf', 'f2 file', 'file1'); >>>>> insert into file (id, parentid, version, contenttype, descr, name) >>>>> values ('f1', 'd1', '2011-03-05', 'pdf', 'f1 file', 'file1'); >>>>> insert into file (id, parentid, version, contenttype, descr, name) >>>>> values ('f1', 'd1', '2011-03-04', 'pdf', 'f1 file', 'file1'); >>>>> create index on file(parentid); >>>>> >>>>> >>>>> select * from file where id='f1' and parentid='d1' limit 1; >>>>> >>>>> select * from file where parentid='d1' limit 1; >>>>> >>>>> >>>>> Will it work for you? >>>>> >>>>> -Vivek >>>>> >>>>> >>>>> >>>>> >>>>> On Tue, Sep 3, 2013 at 11:29 PM, Vivek Mishra wrote: >>>>> >>>>>> My bad. I did miss out to read "latest version" part. >>>>>> >>>>>> -Vivek >>>>>> >>>>>> >>>>>> On Tue, Sep 3, 2013 at 11:20 PM, dawood abdullah < >>>>>> muhammed.dawood@gmail.com> wrote: >>>>>> >>>>>>> I have tried with both the options creating secondary index and also >>>>>>> tried adding parentid to primary key, but I am getting all the files with >>>>>>> parentid 'yyy', what I want is the latest version of file with the >>>>>>> combination of parentid, fileid. Say below are the records inserted in the >>>>>>> file table: >>>>>>> >>>>>>> insert into file (id, parentid, version, contenttype, description, >>>>>>> name) values ('f1', 'd1', '2011-03-04', 'pdf', 'f1 file', 'file1'); >>>>>>> insert into file (id, parentid, version, contenttype, description, >>>>>>> name) values ('f1', 'd1', '2011-03-05', 'pdf', 'f1 file', 'file1'); >>>>>>> insert into file (id, parentid, version, contenttype, description, >>>>>>> name) values ('f2', 'd1', '2011-03-05', 'pdf', 'f1 file', 'file1'); >>>>>>> insert into file (id, parentid, version, contenttype, description, >>>>>>> name) values ('f2', 'd1', '2011-03-06', 'pdf', 'f1 file', 'file1'); >>>>>>> >>>>>>> I want to write a query which returns me second and last record and >>>>>>> not the first and third record, because for the first and third record >>>>>>> there exists a latest version, for the combination of id and parentid. >>>>>>> >>>>>>> I am confused If at all this is achievable, please suggest. >>>>>>> >>>>>>> Dawood >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Tue, Sep 3, 2013 at 10:58 PM, Vivek Mishra >>>>>> > wrote: >>>>>>> >>>>>>>> create secondary index over parentid. >>>>>>>> OR >>>>>>>> make it part of clustering key >>>>>>>> >>>>>>>> -Vivek >>>>>>>> >>>>>>>> >>>>>>>> On Tue, Sep 3, 2013 at 10:42 PM, dawood abdullah < >>>>>>>> muhammed.dawood@gmail.com> wrote: >>>>>>>> >>>>>>>>> Jan, >>>>>>>>> >>>>>>>>> The solution you gave works spot on, but there is one more >>>>>>>>> requirement I forgot to mention. Following is my table structure >>>>>>>>> >>>>>>>>> CREATE TABLE file ( >>>>>>>>> id text, >>>>>>>>> contenttype text, >>>>>>>>> createdby text, >>>>>>>>> createdtime timestamp, >>>>>>>>> description text, >>>>>>>>> name text, >>>>>>>>> parentid text, >>>>>>>>> version timestamp, >>>>>>>>> PRIMARY KEY (id, version) >>>>>>>>> >>>>>>>>> ) WITH CLUSTERING ORDER BY (version DESC); >>>>>>>>> >>>>>>>>> >>>>>>>>> The query (select * from file where id = 'xxx' limit 1;) provided >>>>>>>>> solves the problem of finding the latest version file. But I have one more >>>>>>>>> requirement of finding all the latest version files having parentid say >>>>>>>>> 'yyy'. >>>>>>>>> >>>>>>>>> Please suggest how can this query be achieved. >>>>>>>>> >>>>>>>>> Dawood >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> On Tue, Sep 3, 2013 at 12:43 AM, dawood abdullah < >>>>>>>>> muhammed.dawood@gmail.com> wrote: >>>>>>>>> >>>>>>>>>> In my case version can be timestamp as well. What do you suggest >>>>>>>>>> version number to be, do you see any problems if I keep version as counter >>>>>>>>>> / timestamp ? >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Tue, Sep 3, 2013 at 12:22 AM, Jan Algermissen < >>>>>>>>>> jan.algermissen@nordsc.com> wrote: >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On 02.09.2013, at 20:44, dawood abdullah < >>>>>>>>>>> muhammed.dawood@gmail.com> wrote: >>>>>>>>>>> >>>>>>>>>>> > Requirement is like I have a column family say File >>>>>>>>>>> > >>>>>>>>>>> > create table file(id text primary key, fname text, version >>>>>>>>>>> int, mimetype text, content text); >>>>>>>>>>> > >>>>>>>>>>> > Say, I have few records inserted, when I modify an existing >>>>>>>>>>> record (content is modified) a new version needs to be created. As I need >>>>>>>>>>> to have provision to revert to back any old version whenever required. >>>>>>>>>>> > >>>>>>>>>>> >>>>>>>>>>> So, can version be a timestamp? Or does it need to be an integer? >>>>>>>>>>> >>>>>>>>>>> In the former case, make use of C*'s ordering like so: >>>>>>>>>>> >>>>>>>>>>> CREATE TABLE file ( >>>>>>>>>>> file_id text, >>>>>>>>>>> version timestamp, >>>>>>>>>>> fname text, >>>>>>>>>>> .... >>>>>>>>>>> PRIMARY KEY (file_id,version) >>>>>>>>>>> ) WITH CLUSTERING ORDER BY (version DESC); >>>>>>>>>>> >>>>>>>>>>> Get the latest file version with >>>>>>>>>>> >>>>>>>>>>> select * from file where file_id = 'xxx' limit 1; >>>>>>>>>>> >>>>>>>>>>> If it has to be an integer, use counter columns. >>>>>>>>>>> >>>>>>>>>>> Jan >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> > Regards, >>>>>>>>>>> > Dawood >>>>>>>>>>> > >>>>>>>>>>> > >>>>>>>>>>> > On Mon, Sep 2, 2013 at 10:47 PM, Jan Algermissen < >>>>>>>>>>> jan.algermissen@nordsc.com> wrote: >>>>>>>>>>> > Hi Dawood, >>>>>>>>>>> > >>>>>>>>>>> > On 02.09.2013, at 16:36, dawood abdullah < >>>>>>>>>>> muhammed.dawood@gmail.com> wrote: >>>>>>>>>>> > >>>>>>>>>>> > > Hi >>>>>>>>>>> > > I have a requirement of versioning to be done in Cassandra. >>>>>>>>>>> > > >>>>>>>>>>> > > Following is my column family definition >>>>>>>>>>> > > >>>>>>>>>>> > > create table file_details(id text primary key, fname text, >>>>>>>>>>> version int, mimetype text); >>>>>>>>>>> > > >>>>>>>>>>> > > I have a secondary index created on fname column. >>>>>>>>>>> > > >>>>>>>>>>> > > Whenever I do an insert for the same 'fname', the version >>>>>>>>>>> should be incremented. And when I retrieve a row with fname it should >>>>>>>>>>> return me the latest version row. >>>>>>>>>>> > > >>>>>>>>>>> > > Is there a better way to do in Cassandra? Please suggest >>>>>>>>>>> what approach needs to be taken. >>>>>>>>>>> > >>>>>>>>>>> > Can you explain more about your use case? >>>>>>>>>>> > >>>>>>>>>>> > If the version need not be a small number, but could be a >>>>>>>>>>> timestamp, you could make use of C*'s ordering feature , have the database >>>>>>>>>>> set the new version as a timestamp and retrieve the latest one with a >>>>>>>>>>> simple LIMIT 1 query. (I'll explain more when this is an option for you). >>>>>>>>>>> > >>>>>>>>>>> > Jan >>>>>>>>>>> > >>>>>>>>>>> > P.S. Me being a REST/HTTP head, an alarm rings when I see >>>>>>>>>>> 'version' next to 'mimetype' :-) What exactly are you versioning here? >>>>>>>>>>> Maybe we can even change the situation from a functional POV? >>>>>>>>>>> > >>>>>>>>>>> > >>>>>>>>>>> > > >>>>>>>>>>> > > Regards, >>>>>>>>>>> > > >>>>>>>>>>> > > Dawood >>>>>>>>>>> > > >>>>>>>>>>> > > >>>>>>>>>>> > > >>>>>>>>>>> > > >>>>>>>>>>> > >>>>>>>>>>> > >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> > --047d7b3439707425e904e58f8d41 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Thanks for the quick response Michael, looks like I h= ave to go with the solution you have given of maps, as performance is prett= y critical for our application and we do not have enough time to test. Appr= eciate your help.

Regards,
Dawood


On Wed, Sep 4, 2013 at 7:33 PM, Laing, Michael <michael.laing@nytimes.com> wrote:
Dawood,

= In general that will work. However it does mean that you 1) read the old ve= rsion 2) update the new version and 3) write the archive version.

Step 2 is a problem: what if someone else has updated t= he old version after step 1? and there are 3 atomic operations required, at= least.

However, these considerations may be mitigated using Ca= ssandra 2 light transactions; and it is not a problem if you have only one = updater.

But another problem may be performance. Y= ou must test. The solution I proposed does not require a read before write = and does an atomic append, even if multiple maps are being updated. It also= defers deletions via ttl's and a separate, manageable queue for 'c= leanup' of large maps.

I think the most important word in my reply is: 'te= st'.

Cheers,

Michael<= /div>


On Wed, Sep 4, 2013 at 9:05 AM, dawood a= bdullah <muhammed.dawood@gmail.com> wrote:
Michael,

Y= our approach solves the problem, thanks for the solution. I was thinking of= another approach as well where in I would create another column family say= file_archive, so whenever an update is made to the File table, I will crea= te a new version in the File and move the old version to the new file_archi= ve table. Please let me know if the second approach is fine.

Regards,
Dawood


On Wed, Sep 4, 2013 at 2:47 AM, La= ing, Michael <michael.laing@nytimes.com> wrote:
I use the technique describ= ed in my previous message to handle millions of messages and their versions= .

Actually, I use timeuuid's instead of timestamps, as the= y have more 'uniqueness'. Also I index my maps by a timeuuid that i= s the complement (based on a future date) of a current timeuuid. Since maps= are kept sorted by key, this means I can just pop off the first one to get= the most recent.

The downside of this approach is that you get more stuf= f returned to you from Cassandra than you need. To mitigate that I queue a = job to examine and correct the situation if, upon doing a read, the number = of versions for a particular key is higher than some threshold, e.g. 50. Th= ere are many ways to approach this problem.

Our actual implementation proceeds to another level, as= we also have replicas of versions. This happens because we process importa= nt transactions in parallel and can expect up to 9 replicas of each version= . We journal them all and use them for reporting latencies in our processin= g pipelines as well as for replay when we need to recover application state= .

Regards,

Michael


On Tu= e, Sep 3, 2013 at 3:15 PM, Laing, Michael <michael.laing@nytimes.c= om> wrote:
try the following. -ml
=
-- put this in <file> and run using 'cqlsh -f= <file>

DROP KEYSPACE latest;

CREATE K= EYSPACE latest WITH replication =3D {
=A0 =A0 'class': 'SimpleStrategy',=A0
=A0 = =A0 'replication_factor' : 1
};

= USE latest;

CREATE TABLE file (
=A0 =A0 = parentid text, -- row_key, same for each version
=A0 =A0 id text, -- column_key, same for each version
=A0 = =A0 contenttype map<timestamp, text>, -- differs by version, version = is the key to the map
=A0 =A0 PRIMARY KEY (parentid, id)
);

update file set contenttype =3D contenttype + {'201= 1-03-04':'pdf1'} where parentid =3D 'd1' and id =3D = 9;f1';
update file set contenttype =3D contenttype + {'20= 11-03-05':'pdf2'} where parentid =3D 'd1' and id =3D &#= 39;f1';
update file set contenttype =3D contenttype + {'2011-03-04':&#= 39;pdf3'} where parentid =3D 'd1' and id =3D 'f2';
update file set contenttype =3D contenttype + {'2011-03-05':&= #39;pdf4'} where parentid =3D 'd1' and id =3D 'f2';

select * from file where parentid =3D 'd1';

-- returns:

-- parentid | id= | contenttype
------------+----+--------------------------------= ------------------------------------------
-- =A0 =A0 =A0 d1 | f1 | {'2011-03-04 00:00:00-0500': 'pdf= 1', '2011-03-05 00:00:00-0500': 'pdf2'}
-- = =A0 =A0 =A0 d1 | f2 | {'2011-03-04 00:00:00-0500': 'pdf3', = '2011-03-05 00:00:00-0500': 'pdf4'}

-- use an app to pop off the latest version from the ma= p

-- map other varying fields using the same techn= ique as used for contenttype



On Tue, Sep 3, 2013 at 2:31 PM, Vivek Mi= shra <mishra.vivs@gmail.com> wrote:
create table file(id text , parentid text,contenttype= text,version timestamp, descr text, name text, PRIMARY KEY(id,version) ) W= ITH CLUSTERING ORDER BY (version DESC);

inser= t into file (id, parentid, version, contenttype, descr, name) values ('= f2', 'd1', '2011-03-06', 'pdf', 'f2 file= 9;, 'file1');
insert into file (id, parentid, version, contenttype, descr, name) val= ues ('f2', 'd1', '2011-03-05', 'pdf', '= f2 file', 'file1');
insert into file (id, parentid, v= ersion, contenttype, descr, name) values ('f1', 'd1', '= 2011-03-05', 'pdf', 'f1 file', 'file1');
insert into file (id, parentid, version, contenttype, descr, name) val= ues ('f1', 'd1', '2011-03-04', 'pdf', '= f1 file', 'file1');
create index on file(parentid);


select * from file where id= =3D'f1' and parentid=3D'd1' limit 1;

select * from file where parentid=3D'd1' limit 1;


Will it work for you?

-Vivek




On Tue, Sep 3= , 2013 at 11:29 PM, Vivek Mishra <mishra.vivs@gmail.com>= wrote:
My bad. I did miss out to r= ead "latest version" part.

-Vivek


On Tue, Sep 3, 2013 at 11:20 PM= , dawood abdullah <muhammed.dawood@gmail.com> wrote:=
I have tried= with both the options creating secondary index and also tried adding paren= tid to primary key, but I am getting all the files with parentid 'yyy&#= 39;, what I want is the latest version of file with the combination of pare= ntid, fileid. Say below are the records inserted in the file table:

insert into file (id, parentid, version, contenttype, description, name= ) values ('f1', 'd1', '2011-03-04', 'pdf', = 'f1 file', 'file1');
insert into file (id, parentid, ver= sion, contenttype, description, name) values ('f1', 'd1', &= #39;2011-03-05', 'pdf', 'f1 file', 'file1'); insert into file (id, parentid, version, contenttype, description, name) va= lues ('f2', 'd1', '2011-03-05', 'pdf', '= ;f1 file', 'file1');
insert into file (id, parentid, version= , contenttype, description, name) values ('f2', 'd1', '2011-03-06', 'pdf', &= #39;f1 file', 'file1');

I want to write a query wh= ich returns me second and last record and not the first and third record, b= ecause for the first and third record there exists a latest version, for th= e combination of id and parentid.

I am confused If at all this is achievable, please suggest.=

Dawood

=

On Tue, Sep 3, 2013 at 10:58 PM, Vivek M= ishra <mishra.vivs@gmail.com> wrote:
create secondary index over= parentid.
OR
make it part of clustering key

-Vivek


On Tue, Sep 3, 2013 at 10:42 PM, dawood abdullah <muhammed.dawood@= gmail.com> wrote:
Jan,

The solution you gav= e works spot on, but there is one more requirement I forgot to mention. Fol= lowing is my table structure

CREATE TABLE file (
=A0 id text,
=A0 contenttype text,
=A0 createdby text,
=A0 createdtime timestamp,
=A0 description text,<= br>=A0 name text,
=A0 parentid text,
=A0 version timestamp,
=A0 PR= IMARY KEY (id, version)

) WITH CLUSTERING ORDER BY (version DESC);<= br>

The query (select * from file where id =3D 'xxx' limit 1;) provided= solves the problem of finding the latest version file. But I have one more= requirement of finding all the latest version files having parentid say &#= 39;yyy'.

Please suggest how can this query be achieved.

Dawo= od



On = Tue, Sep 3, 2013 at 12:43 AM, dawood abdullah <muhammed.dawood@gma= il.com> wrote:
In my case version can be t= imestamp as well. What do you suggest version number to be, do you see any = problems if I keep version as counter / timestamp ?


On Tue, Sep 3, 2013 at 12:22 AM, Jan Algermissen <jan.algermissen= @nordsc.com> wrote:

On 02.09.2013, at 20:44, dawood abdullah <muhammed.dawood@gmail.com> wrote:
> Requirement is like I have a column family say File
>
> create table file(id text primary key, fname text, version int, mimety= pe text, content text);
>
> Say, I have few records inserted, when I modify an existing record (co= ntent is modified) a new version needs to be created. As I need to have pro= vision to revert to back any old version whenever required.
>

So, can version be a timestamp? Or does it need to be an integer?

In the former case, make use of C*'s ordering like so:

CREATE TABLE file (
=A0 =A0file_id text,
=A0 =A0version timestamp,
=A0 =A0fname text,
=A0 =A0....
=A0 =A0PRIMARY KEY (file_id,version)
) WITH CLUSTERING ORDER BY (version DESC);

Get the latest file version with

select * from file where file_id =3D 'xxx' limit 1;

If it has to be an integer, use counter columns.

Jan


> Regards,
> Dawood
>
>
> On Mon, Sep 2, 2013 at 10:47 PM, Jan Algermissen <jan.algermissen@nordsc.com> wrote:
> Hi Dawood,
>
> On 02.09.2013, at 16:36, dawood abdullah <muhammed.dawood@gmail.com> wro= te:
>
> > Hi
> > I have a requirement of versioning to be done in Cassandra.
> >
> > Following is my column family definition
> >
> > create table file_details(id text primary key, fname text, versio= n int, mimetype text);
> >
> > I have a secondary index created on fname column.
> >
> > Whenever I do an insert for the same 'fname', the version= should be incremented. And when I retrieve a row with fname it should retu= rn me the latest version row.
> >
> > Is there a better way to do in Cassandra? Please suggest what app= roach needs to be taken.
>
> Can you explain more about your use case?
>
> If the version need not be a small number, but could be a timestamp, y= ou could make use of C*'s ordering feature , have the database set the = new version as a timestamp and retrieve the latest one with a simple LIMIT = 1 query. (I'll explain more when this is an option for you).
>
> Jan
>
> P.S. Me being a REST/HTTP head, an alarm rings when I see 'version= ' next to 'mimetype' :-) What exactly are you versioning here? = Maybe we can even change the situation from a functional POV?
>
>
> >
> > Regards,
> >
> > Dawood
> >
> >
> >
> >
>
>












--047d7b3439707425e904e58f8d41--