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 51D11D955 for ; Thu, 20 Dec 2012 21:18:40 +0000 (UTC) Received: (qmail 31582 invoked by uid 500); 20 Dec 2012 21:18:38 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 31534 invoked by uid 500); 20 Dec 2012 21:18:37 -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 31526 invoked by uid 99); 20 Dec 2012 21:18:37 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 20 Dec 2012 21:18:37 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of aventurella@gmail.com designates 209.85.214.172 as permitted sender) Received: from [209.85.214.172] (HELO mail-ob0-f172.google.com) (209.85.214.172) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 20 Dec 2012 21:18:31 +0000 Received: by mail-ob0-f172.google.com with SMTP id za17so3856005obc.17 for ; Thu, 20 Dec 2012 13:18:10 -0800 (PST) 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=36tY9YZ0qFVh2YtbpN2/S3FqB2/i0KrZtYbxZogD01w=; b=IMviXabgKffq73tcqfxbPdWIOYe21RAOfr8jJvnqJ5swrCvbBfinUBil/6n9jGxz1W /K5XGffBVpQmr3fCPEMtoQ9v68+exHJXLdGhKq2oE3bP4PfQnafxcFjcL+f0Rlv1UQco iYLi+YTcL47wVllslzSqe/ELIUU8T5mlNPlDJvyjQARfL0ysf3wxqEfbG9fc464S1s1C d93i34Ml7o0ir1iEKAEq9twP1WSF6QC5nPdD6r3rGqJhYPjouP7UhV8FvB56vdKXAzya sSSMSKF6/AA1YawEfrCPpz5MbBiUUlzrxptKHK9GQbouUKNaByAF9dRT3n63pjTjAWzW xlvw== MIME-Version: 1.0 Received: by 10.182.159.5 with SMTP id wy5mr8656093obb.31.1356038290352; Thu, 20 Dec 2012 13:18:10 -0800 (PST) Received: by 10.76.34.40 with HTTP; Thu, 20 Dec 2012 13:18:10 -0800 (PST) In-Reply-To: <3DA1CBD1-5082-427C-AC3E-07926C773605@thelastpickle.com> References: <3DA1CBD1-5082-427C-AC3E-07926C773605@thelastpickle.com> Date: Thu, 20 Dec 2012 13:18:10 -0800 Message-ID: Subject: Re: Data Model Review From: Adam Venturella To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=14dae9399bd9992a6b04d14f44ff X-Virus-Checked: Checked by ClamAV on apache.org --14dae9399bd9992a6b04d14f44ff Content-Type: text/plain; charset=ISO-8859-1 In the case without CQL3, where I would use composite columns, I see how this sort of lines up with what CQL3 is doing. I don't have the ability to use CQL3 as I am using pycassa for my client, so that leaves me with CompositeColumns Under composite columns, I would have 1 row, which would be stored on 1 node with a lot of columns. Basically this single node would be hit frequently and the other nodes would be ignored. Assuming I have it correct that a row lives on a single node. I can then get a slice of columns using the composite though (username,), and have the comparator be reverse for the photo_seq which would give me my proper order. As I understand it, that would give me the same data result as using the primary key, but it would be looking at 1 row on 1 node, unlike the PK solution, so I would have a hotspot. The PRIMARY KEY solution allows creates multiple rows, but they effectively act as 1 wide row, but they have the benefit of being distributed across the nodes as they are independent rows (using username as the partition key), instead of living on 1 node in 1 row. If my assumptions are correct above, the PK solution is clearly better than the single row solution. In doing some reading, I have come across a solution where you manually partition the row keys so you spread the load more evenly. The cassandra docs here talk about this approach under "High Throughput Timelines" : http://www.datastax.com/dev/blog/advanced-time-series-with-cassandra Would you advise the manual partition example? My other option is to store all of the photos based on their id, or generate them my own canonical id based on their id and some other factors, into rows and then have kind of a hybrid index row for usernames that not only would reference the photo_id row but potentially contain some more information to render a result set. On Tue, Dec 18, 2012 at 8:13 PM, aaron morton wrote: > I have heard it best to try and avoid the use of super columns for now. > > Yup. > > Your model makes sense. If you are creating the CF using the cassandra-cli > you will probably want to reverse order the column names see > http://thelastpickle.com/2011/10/03/Reverse-Comparators/ > > If you want to use CQL 3 you could do something like this: > > CREATE TABLE InstagramPhotos ( > > user_name str, > photo_seq timestamp, > meta_1 str, > meta_2 str > PRIMARY KEY (user_name, phot_seq) > ); > > That's pretty much the same. user_name is the row key, and photo_seq will > be used as part of a composite column name internally. > (You can do the same thing without CQL, just look up composite columns) > > You can do something similar for the annotations. > > Depending on your use case I would use UNIX epoch time if possible rather > than a time uuid. > > Hope that helps. > > ----------------- > Aaron Morton > Freelance Cassandra Developer > New Zealand > > @aaronmorton > http://www.thelastpickle.com > > On 18/12/2012, at 4:35 AM, Adam Venturella wrote: > > My use case is capturing some information about Instagram photos from the > API. I have 2 use cases. One, I need to capture all of the media data for > an account and two I need to be able to privately annotate that data. There > is some nuance in this, multiple http queries for example, but ignoring > that, and assuming I have obtained all of the data surrounding an accounts > photos here is how I was thinking of storing that information for use case > 1. > > ColumnFamily: InstagramPhotos > > Row Key: > > Columns: > Coulmn Name: > Coulumn Value: JSON representing the data for the individual photo > (filter, comments, likes etc, not the binary photo data). > > > > So the idea would be to keep adding columns to the row that contain that > serialized data (in JSON) with their timestamps as the name. Timestamps as > the column names, I figure, should help help to perform range queries, > where I make the 1st column inserted the earliest timestamp and the last > column inserted the most recent. I could probably also use TimeUUIDs here > as well since I will have things ordered prior to inserting. > > The question here, does this approach make sense? Is it common to store > JSON in columns like this? I know there are super columns as well, so I > could use those I suppose instead of JSON. The extra level of indexing > would probably be useful to query specific photos for use case 2. I have > heard it best to try and avoid the use of super columns for now. I have no > information to back that claim up other than some time spent in the IRC. So > feel free to debunk that statement if it is false. > > So that is use case one, use case two covers the private annotations. > > I figured here: > > ColumnFamily: InstagramAnnotations > row key: Canonical Media Id > > Column Name: TimeUUID > Column Value: JSON representing an annotation/internal comment > > > Writing out the above I can actually see where I might need to tighten > some things up around how I store the photos. I am clearly missing an > obvious connection between the InstagramPhotos and the > InstagramAnnotations, maybe super columns would help with the photos > instead of JSON? Otherwise I would need to build an index row where I tie > the the canonical photo id to a timestamp (column name) in the > InstagramPhotos. I could also try to figure out how to make a TimeUUID of > my own that can double as the media's canonical id or further look at > Instagram's canonical id for photos and see if it already counts up. In > which case I could use that in place of a timestamp. > > Anyway, I figured I would see if anyone might help flush out other > potential pitfalls in the above. I am definitely new to cassandra and I am > using this project as a way to learn some more about assembling systems > using it. > > > > > > > --14dae9399bd9992a6b04d14f44ff Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
In the case without CQL3, where I would use composite colu= mns, I see how this sort of lines up with what CQL3 is doing.

I don't have the ability to use CQL3 as I am using pycassa = for my client, so that leaves me with CompositeColumns

Under composite columns, I would have 1 row,= which would be stored on 1 node with a lot of columns. Basically this sing= le node would be hit frequently and the other nodes would be ignored. Assum= ing I have it correct that a row lives on a single node.=A0

I can then get a slice of columns using the= composite though (username,), =A0and have the comparator be reverse for th= e photo_seq which would give me my proper order. As I understand it, that w= ould give me the same data result as using the primary key, but it would be= looking at 1 row on 1 node, unlike the PK solution, so I would have a hots= pot.

The PRIMARY KEY solution allows creates mul= tiple rows, but they effectively act as 1 wide row, but they have the benef= it of being distributed across the nodes as they are independent rows (usin= g username as the partition key), instead of living on 1 node in 1 row.

If my assumptions are correct above, = the PK solution is clearly better than the single row solution. In doing so= me reading, I have come across a solution where you manually partition the = row keys so you spread the load more evenly. The cassandra docs here talk a= bout this approach under "High Throughput Timelines" : http= ://www.datastax.com/dev/blog/advanced-time-series-with-cassandra

Would you advise the manual partition examp= le?

My other option is to store all of= the photos based on their id, or generate them my own canonical id based o= n their id and some other factors, into rows and then have kind of a hybrid= index row for usernames that not only would reference the photo_id row but= potentially contain some more information to render a result set.


=

On Tue, Dec 18, 2012 at 8:13 PM, aaron m= orton <aaron@thelastpickle.com> wrote:
I have heard it best to try and avoid t= he use of super columns for now.=A0
Yup.=A0

Your model makes sense. If you are creatin= g the CF using the cassandra-cli you will probably want to reverse order th= e column names see=A0http://thelastpickle.com/2011/10/03/Revers= e-Comparators/

If you want to use CQL 3 you could do something like th= is:

CREATE=A0TABLE=A0InstagramPhotos=A0(
<= br>
user_name=A0str,=
photo_seq=A0timestamp,
meta_1 str,=A0
meta_2 str
PRIMARY=A0KEY=A0(user_name,=A0phot_seq) );

That's pretty much the same. user_name is t= he row key, and photo_seq will be used as part of a composite column name i= nternally.=A0
(You can do the same thing without CQL, just look u= p composite columns)

You can do something similar for the annotations.=A0

Depending on your use case I would use UNIX epoch ti= me if possible rather than a time uuid.

Hope that = helps.=A0

-----------------
Aaron Morton
Freelance Cassandra= Developer
New Zealand


On 18/12/2012, at 4:35 AM, Adam Venturella <aventurella@gmail.com> = wrote:

My use case is capturing som= e information about Instagram photos from the API. I have 2 use cases. One,= I need to capture all of the media data for an account and two I need to b= e able to privately annotate that data. There is some nuance in this, multi= ple http queries for example, but ignoring that, and assuming I have obtain= ed all of the data surrounding an accounts photos here is how I was thinkin= g of storing that information for use case 1.=A0

ColumnFamily: InstagramPhotos

Row Key: <account_username>

Columns: = =A0=A0
Coulmn Name: <date_posted_timestamp>
Coulu= mn Value: JSON representing the data for the individual photo (filter, comm= ents, likes etc, not the binary photo data).



So the idea would be to k= eep adding columns to the row that contain that serialized data (in JSON) w= ith their timestamps as the name. =A0Timestamps as the column names, I figu= re, should help help to perform range queries, where I make the 1st column = inserted the earliest timestamp and the last column inserted the most recen= t. I could probably also use TimeUUIDs here as well since I will have thing= s ordered prior to inserting.

The question here, does this approach make sense? Is it= common to store JSON in columns like this? I know there are super columns = as well, so I could use those I suppose instead of JSON. The extra level of= indexing would probably be useful to query specific photos for use case 2.= I have heard it best to try and avoid the use of super columns for now. I = have no information to back that claim up other than some time spent in the= IRC. So feel free to debunk that statement if it is false.

So that is use case one, use case two covers the privat= e annotations.

I figured here:

ColumnFamily: InstagramAnnotations
row key: =A0Canonical Me= dia Id

Column Name: TimeUUID
Column Value: JSON repr= esenting an annotation/internal comment


=
Writing out the above I can actually see where I might need to tighten= some things up around how I store the photos. I am clearly missing an obvi= ous connection between the=A0InstagramPhotos=A0and the InstagramAnnotations= , maybe super columns would help with the photos instead of JSON? Otherwise= I would need to build an index row where I tie the the canonical photo id = to a timestamp (column name) in the InstagramPhotos. I could also try to fi= gure out how to make a TimeUUID of my own that can double as the media'= s canonical id or further look at Instagram's canonical id for photos a= nd see if it already counts up. In which case I could use that in place of = a timestamp.

Anyway, I figured I would see if anyone might hel= p flush out other potential pitfalls in the above. I am definitely new to c= assandra and I am using this project as a way to learn some more about asse= mbling systems using it.







--14dae9399bd9992a6b04d14f44ff--