Structuring cassandra database

问题:

I don't understand one thing about Cassandra. Say, I have similar website to Facebook, where people can share, like, comment, upload images and so on.

Now, let's say, I want to get all of the things my friends did:

• Username1 liked you comment
• username 2 updated his profile picture

And so on.

So after a lot of reading, I guess I would need to do is create new Column Family for each single thing, for example: user_likes user_comments, user_shares. Basically, anything you can think off, and even after I do that, I would still need to create secondary indexes for most of the columns just so I could search for data? And even so how would I know which users are my friends? Would I need to first get all of my friends id's and then search through all of those Column Families for each user id?

EDIT Ok so i did some more reading and now i understand things a little bit better, but i still can't really figure out how to structure my tables, so i will set a bounty and i want to get a clear example of how my tables should look like if i want to store and retrieve data in this kind of order:

• All
• Likes
• Comments
• Favourites
• Downloads
• Shares
• Messages

So let's say i want to retrieve ten last uploaded files of all my friends or the people i follow, this is how it would look like:

John uploaded song AC/DC - Back in Black 10 mins ago

And every thing like comments and shares would be similar to that...

Now probably the biggest challenge would be to retrieve 10 last things of all categories together, so the list would be a mix of all the things...

Now i don't need an answer with a fully detailed tables, i just need some really clear example of how would i structure and retrieve data like i would do in mysql with joins

回答1:

With sql, you structure your tables to normalize your data, and use indexes and joins to query. With cassandra, you can't do that, so you structure your tables to serve your queries, which requires denormalization.

You want to query items which your friends uploaded, one way to do this is t have a single table per user, and write to this table whenever a friend of that user uploads something.

friendUploads { #columm family
    userid { #column 
        timestamp-upload-id : null #key : no value
    }
 }

as an example,

friendUploads {
    userA {
         12313-upload5 : null
         12512-upload6 : null
         13512-upload8 : null
    }
}

friendUploads {
    userB {
         11313-upload3 : null
         12512-upload6 : null
    }
}

Note that upload 6 is duplicated to two different columns, as whoever did upload6 is a friend of both User A and user B.

Now to query the friends upload display of a friend, do a getSlice with a limit of 10 on the userid column. This will return you the first 10 items, sorted by key.

To put newest items first, use a reverse comparator that sorts larger timestamps before smaller timestamps.

The drawback to this code is that when User A uploads a song, you have to do N writes to update the friendUploads columns, where N is the number of people who are friends of user A.

For the value associated with each timestamp-upload-id key, you can store enough information to display the results (probably in a json blob), or you can store nothing, and fetch the upload information using the uploadid.

To avoid duplicating writes, you can use a structure like,

userUploads { #columm family
    userid { #column 
        timestamp-upload-id : null #key : no value
    }
 }

This stores the uploads for a particular user. Now when want to display the uploads of User B's friends, you have to do N queries, one for each friend of User B, and merge the result in your application. This is slower to query, but faster to write.

Most likely, if users can have thousands of friends, you would use the first scheme, and do more writes rather than more queries, as you can do the writes in the background after the user uploads, but the queries have to happen while the user is waiting.

As an example of denormalization, look at how many writes twitter rainbird does when a single click occurs. Each write is used to support a single query.

回答2:

In some regards, you "can" treat noSQL as a relational store. In others, you can denormalize to make things faster. For instance, PlayOrm's @OneToMany stored the many like so

user1 -> friend.user23, friend.user25, friend.user56, friend.user87

This is the wide row approach so when you find your user, you have all the foreign keys to his friends. Each row can be different lengths. You may also have a reverse reference stored as well so the user might have references to the people that marked him as a friend but he did not mark them back(let's call it buddy) so you might have

user1 -> friend.user23, friend.user25, buddy.user29, buddy.user37

Notice that if designed right, you may NOT need to "search" for the data. That said, with PlayOrm, you can still do Scalable SQL and do joins(you just have to figure out how to partition your tables so it can scale to trillions of rows).

A row can have millions of columns in it or it could have just 10. We are actually in the process of updating alot of the documentation in PlayOrm and the noSQL patterns this month so if you keep an eye on that, you can also learn more about general noSQL there as well.

Dean

回答3:

Think of each DB query as of request to the service running on another machine. Your goal is to minimize number of these requests (because each request requires network roundtrip).

Here comes the main difference from RDBMS paradigm: In SQL you would typically use joins and secondary indexes. In cassandra joins aren't possible, since related data would reside on different servers. Things like materialized views are used in cassandra for the same purpose (to fetch all related data with single query).

I'd recommend to read this article: http://maxgrinev.com/2010/07/12/do-you-really-need-sql-to-do-it-all-in-cassandra/

And to look into twissandra sample project https://github.com/twissandra/twissandra This is nice collection of optimization technics for the kind of projects you described.