Hypertable database design

Question

Could someone point me to post/pdf/explan here where I can learn more about hypertable database design, I have searched all over google but nothing useful comes up

Main problem I have is how do I store data that you "cant" represent as key value. For example lets say I want to track user reviews, I may simply imitate relational databases, generate new review key store all data I need, and then for for product X store keys to all reviews in json format to x.reviews

The other thing I came up with is simply have x.reviewKeys and then for each generated review push new key as a new version of that cell, assuming that I can store infinite number of versions of the same cell.

Second approach seems much more appropriate to me as I leave to database all the work I would implement in code.

Does anyone know how to handle this problem, or even better how does google handles this with their bigtable

Answer 1

This "few hours" of searching for anything that might result in understanding Hypertable database design is definitely not enough but here is starting point if someone runs to this over google

Now far as I understood it so far is that you can define column family say, reviews, and each column family can have infinite number of keys, for example if you try to store user data you would probably write something like this

userKey:info and have multiple properties like, name, address, email... and you would be able to access that info simply by defining key to that entry so for example to get user address you would use userKey:info:address as key

One thing that makes column families different (if I understood it well) is that you can get all that keys in column family so for example, if you specify userKey:info you would get userKey:info:name, userKey:info:address, userKey:info:email as result which means that to store all the reviews I mentioned in my question you would create new column family and store keys to all reviews and you would end up with something like this key:reviews:<review_ID> which would return IDs (if you ask it to return key:reviews) of all reviews and than you would simply use that ID to get details about specific review.

so basically just like you have properties name, address, email in column family info, here you would use review ID as column property.

Now to be at least partly sure I got It right I also checked out riak database design (key/value database unlike hypertable which is column database?) and seems like they have bucket as alternative to column family, so this at lest seems to be right implementation

I dont know how they implemented this internally to make it efficient, so I'll have to check it out, and as I always create new account on SO I wont be able to update this post, but if someone has more info post replay/edit this post

Answer 2

So if i understood correctly, you have a table with Products and another one with Reviews, and they have a 1:n relationship (there can be several reviews for each product)?

One way to do that is using column qualifiers.

Your product table has a row key (i.e. the product id), some columns for title, description, manufacturer etc. And one column "reviews":

create table Products (title, description, manufacturer, reviews);

For the reviews you could create a unique key, i.e. a GUID for the row key (or a sufficiently large random number or just a unique timestamp; i am going to use a GUID i my examples):

create table Reviews (author, rating, text);

To create the 1:n relationship, insert the row key of the review into the "reviews" column family as a qualifier (the actual cell value is not needed and can be empty):

INSERT INTO Reviews VALUES ("product-id", "reviews:1273-1234-234-123a", "");

And to retrieve all reviews of product 123:

SELECT reviews FROM Products WHERE ROW = "123";

You can even create a qualifier index on "reviews" to speed up some of the queries.