On Apr 1, 2014, at 6:56 PM, Peter Stensmyr peter.stens...@gmail.com wrote:
I posted this question on Stack Overflow a few days ago, and got some
response but nothing that really solves my problem. I'm hoping that I can get
some more input here. The initial recommendation was to keep all the data in
two tables (one meta and one data table), but this might become unwieldy with
the number of rows (I estimate it will be about 6 billion rows in total).
I have many (~2000) locations with time series data. Each time series has
millions of rows. I would like to store these in a Postgres database. My
current approach is to have a table for each location time series, and a meta
table which stores information about each location (coordinates, elevation
etc). I am using SQLAlchemy to create and query the tables. I would like to
have a relationship between the meta table and each time series table to do
queries like select all locations that have data between date A and date B
and select all data for date A and export a csv with coordinates. What is
the best way to create many tables with the same structure (only the name is
different) and have a relationship with a meta table? Or should I use a
different database design?
This approach has some problems, most notably that if I create more than one
TimeSeries object the foreign key doesn't work. Previously I've used some
work-arounds (such as not defining a foreign key), but it all seems like a
big hack and I feel that there must be a better way of doing this. How should
I organise and access my data?
so the idea of making lots of tables and using a function to generate
declarative classes with different table names, that's not unheard of. Such an
approach is the basis for recipes like the entity name recipe which you can
see at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName.
Then the idea of each of your TimeSeries reaching out to Location, and having
TimeSeries.location, also fine.
But then the whole thing goes horribly wrong when you try to claim that there
will be a timeseries backref on Location.
Think about it. You have 2000 individual Python classes all called TimeSeries,
all referring to a different table. This is the question you have to answer
(and which I think is going to wind you up back at one table):
1. which one of those 2000 TimeSeries classes do I refer to when I say
Location.timeseries ?
2. what SQL would a query like query(Location).join(timeseries) produce? Are
you looking for an enormous UNION of 2000 tables? that's not going to work.
Think about this in terms of SQL. If you want to query across *all*
timeseries at once, having them all in 2000 tables is not going to be possible.
If you did have that setup, the approach would be to write a map/reduce query
that hits each TimeSeries table separately and figures it out at that level,
just like if you were running a noSQL database like CouchDB or something like
that.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
