Data Model Review

[Adam Venturella <av...@gmail.com>]

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: <account_username>

Columns:
Coulmn Name: <date_posted_timestamp>
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.

Re: Data Model Review

[Adam Venturella <av...@gmail.com>]

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 <aa...@thelastpickle.com>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 <av...@gmail.com> 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: <account_username>
>
> Columns:
> Coulmn Name: <date_posted_timestamp>
> 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.
>
>
>
>
>
>
>

Re: Data Model Review

[aaron morton <aa...@thelastpickle.com>]

> 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 <av...@gmail.com> 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: <account_username>
> 
> Columns:   
> Coulmn Name: <date_posted_timestamp>
> 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.
> 
> 
> 
> 
>