Suitability of Amazon SimpleDB for large temporal data sets eminating from thousands of separate devices

Question

I'm trying to establish whether Amazon SimpleDB is suitable for a subset of data I have.

I have thousands of deployed autonomous sensor devices recording data.

Each sensor device essentially reports a couple of values four times an hour each day, over months and years. I need to keep all of this data for historic statistical analysis. Generally, it is write once, read many times. Server-based applications run regularly to query the data to infer other information.

The rows of data today, in SQL look something like this:

• (id, device_id, utc_timestamp, value1, value2)

Our existing MySQL solution is not going to scale up much further, with tens of millions of rows. We query things like "tell me the sum of all the value1 yesterday" or "show me the average of value2 in the last 8 hours". We do this in SQL but can happily change to doing it in code. SimpleDBs "eventual consistency" appears fine for our puposes.

I'm reading up all I can and am about to start experimenting with our AWS account, but it's not clear to me how the various SimpleDB concepts (items, domains, attributes, etc.) relate to our domain.

Is SimpleDB an appropriate vehicle for this and what would a generalised approach be?

PS: We mostly use Python, but this shouldn't matter when considering this at a high level. I'm aware of the boto library at this point.

Edit:

Continuing to search on solutions for this I did come across Stack Overflow question What is the best open source solution for storing time series data? which was useful.

Answer 1

Just following up on this one many months later...

I did actually have the opportunity to speak to Amazon directly about this last summer, and eventually got access to the beta programme for what eventually became DynamoDB, but was not able to talk about it.

I would recommend it for this sort of scenario, where you need a primary key and what might be described as a secondary index/range - eg timestamps. This allows you much greater confidence in search, ie "show me all the data for device X between monday and friday"

We haven't actually moved to this yet for various reasons but do still plan to.

http://aws.amazon.com/dynamodb/

Answer 2

I my opinon, Amazon SimpleDb as well as Microsoft Azure Tables is a fine solution as long as your queries are quite simple. As soon as you trying to do stuff that's absolutely a non-issue on relational databases like aggregates you begin to run into trouble. So if you are going to do some heavy reporting stuff it might get messy.

Answer 3

It sounds like your problem may be best handled by a round-robin database (RRD). An RRD stores time variable data in such a way so that the file size never grows beyond its initial setting. It's extremely cool and very useful for generating graphs and time series information.

Answer 4

I agree with Oliver Weichhold that a cloud based database solution will handle the usecase you described. You can spread your data across multiple SimpleDB domains (like partitions) and stored your data in a way that most of your queries can be executed from a single domain without having to traverse the entire database. Defining your partition strategy will be key to the success of moving towards a cloud based DB. Data set partitioning is talked about here