BigTable limitations

[smuggyuk]

Hi all,

I'm from a SQL Server background so I'm currently reading up on
BigTable to see what the differences are, and how feasible it would be
to port a site I have to App Engine. Most of it seems doable, however
there is one bit I'm struggling with.

Essentially I store recipes and those recipes can be rated. Now I get
that I have a recipe model, and a rating model, and using a
referencekey property can tell the rating entity which recipe entity
it is for. Now, conventionally to get the rating for a recipe I would
do a SQL query along the lines of SELECT AVG(rating.value) FROM
ratings WHERE ratings.recipeid = recipe.recipeid

My question is, with the lack of any kind of function such as AVG in
GQL what it the best method for determing this in App Engine, and I
hope it isn't loop around the returned rating entities calculating the
avg as you go along because that doesn't sound too great! For
exmaple, is it possible to do, on the entity results of a GQL query,
ratings.value.AVG()?

A second different issue, once you've established a datastore with
several entities of a certain kind, if you update the model for this
kind in your python code, what happens to the existing entities. I.e.
are they affected by the update to the model or not - I'm thinking
along the lines of adding a new column to a database table. If they
are not affected, how can you update the value of this new property
for those early entities? Does just setting it add it to the existing
entity?

Thanks for any advice whilst I find my feet in the approaches used for
a python / BigTable environment.

Chris

[Michael Brunton-Spall]

Hi,

I'm pretty new to it too, but as I understand it your best bet is to denormalise your table.
So your table that contains the recipe would also contain a total rating and number of ratings column.
When you insert a new rating, you increment the rating count and the total as expected for the recipe.
When you want to display a recipe's average rating you can simply get one row, the recipe row, and then do total / count to give you the average.
Of course if you are allowing people to adjust their ratings you will also want to insert a Recipe to user link, exactly as you already do, so you can find the users existing rating if they are looking at their own rating, and adjusting the rating is the case of reducing the total by the old rating, and adding the new rating to the total for the recipe.

Hope that's a clear explanation.

Michael Brunton-Spall

--
Michael Brunton-Spall

[Staz]

BigTable is a very different beast to relational databases. The data
is distributed over a large number of nodes, not centralised in a
powerful central server. This means that some things that it is
sensible to do centrally in the relational model just don't make sense
with BigTable.

Whether the BigTable code or your app code loops over the results
probably doesn't make much difference from a performance point of
view. In either case, one node is going to have to fetch data from a
bunch of others and then process it. So you might as well just get
the data and do the processing yourself.

Regarding adding new attributes to existing entities, presumably
you'll have a default value to use. In which case, just make it the
default in your Model class. New instances that have a value will use
it, old instances that don't will have the default.

[btoc]

Yes this is the way to do it. In fact this is the way to do it with a
normal SQL database. It's crazy to think you keep calculating this on
each read. Make the effort on the insert of the new rating and you are
done. So if you get 20 reads for every rating you've saved yourself 19
high cost calculations.

On Apr 10, 10:15 am, "Michael Brunton-Spall"