Generic data store - what to use?
Reply With QuoteI'm probably in a similar boat to you - I collect data from one wire sensors, wireless (weather station, electricity monitor, homeeasy), solar controller, and web scraping.
I used to use rrdtool, but got fed up with having to recreate the rrd every time I added a new sensor, and it wasn't easy (or quick) to extract data in the way I wanted.
I now store my data in a mysql database. I have two tables, sensor_names which contains the human readable name of the sensor with its auto-generated unique id. So, that contains 20 odd sensors at the mo. The big table is the data table. Each entry in this table contains a unique id associated with that table entry, a timestamp for when the sensor value was taken, the id of the sensor (matching the sensor_names table entry), and the actual value of the sensor.
This table layout minimises the amount of space taken by each entry and you don't need to redo the schema every time you add a sensor, and you can handle sensors with varying update rates, with the disadvantage that you end up with a lot of entries in each table. As it grows, I suspect it will get slow, so I'll have to decide whether to start purging or archiving data, or do something with indexes (if it's possible or helps). Anyway, here's some sql to give you an idea:
mysql> show tables;
+-----------------------+
| Tables_in_environment |
+-----------------------+
| sensor_names |
| sensor_values |
+-----------------------+
2 rows in set (0.00 sec)
mysql> describe sensor_names;
+-------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+---------+----------------+
| sensor_id | int(5) unsigned | NO | PRI | NULL | auto_increment |
| sensor_name | varchar(64) | NO | PRI | NULL | |
+-------------+-----------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> select * from sensor_names;
+-----------+-----------------------------------+
| sensor_id | sensor_name |
+-----------+-----------------------------------+
| 1 | Oregon:Lounge:Humidity |
| 2 | Oregon:Lounge:Temperature |
| 5 | OWL:Instant |
| 6 | OWL:Total |
| 7 | Oregon:Patio:Humidity |
| 8 | Oregon:Patio:Temperature |
| 10 | Oregon:Rain:Rate |
| 11 | Oregon:Rain:Total |
| 12 | Oregon:Wind:Average |
| 13 | Oregon:Wind:Gust |
| 14 | Oregon:Windirection |
| 17 | Temperatures:OWFS:Garage |
| 34 | Temperatures:BBC:Lymington |
mysql> describe sensor_values;
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| value_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| value_time | int(11) | NO | | NULL | |
| sensor_id | int(5) | NO | MUL | NULL | |
| sensor_value | float | NO | | NULL | |
+--------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from sensor_values where sensor_id=2 order by value_time desc limit 10;
+----------+------------+-----------+--------------+
| value_id | value_time | sensor_id | sensor_value |
+----------+------------+-----------+--------------+
| 10368200 | 1382355001 | 2 | 20.2 |
| 10368174 | 1382354701 | 2 | 20.2 |
| 10368146 | 1382354401 | 2 | 20.2 |
| 10368118 | 1382354101 | 2 | 20.2 |
| 10368090 | 1382353801 | 2 | 20.2 |
| 10368062 | 1382353501 | 2 | 20.2 |
| 10368033 | 1382353202 | 2 | 20.2 |
| 10368007 | 1382352901 | 2 | 20.2 |
| 10367979 | 1382352602 | 2 | 20.2 |
| 10367951 | 1382352302 | 2 | 20.2 |
+----------+------------+-----------+--------------+
10 rows in set (0.08 sec)
mysql> select * from sensor_values order by value_time desc limit 10;
+----------+------------+-----------+--------------+
| value_id | value_time | sensor_id | sensor_value |
+----------+------------+-----------+--------------+
| 10368252 | 1382355308 | 42 | 45.5 |
| 10368251 | 1382355308 | 45 | 51.37 |
| 10368250 | 1382355306 | 35 | 21 |
| 10368249 | 1382355305 | 41 | 20.87 |
| 10368248 | 1382355305 | 43 | 16.31 |
| 10368247 | 1382355305 | 40 | 18.62 |
| 10368246 | 1382355304 | 39 | 0.1 |
| 10368245 | 1382355303 | 34 | 16 |
| 10368244 | 1382355302 | 38 | 0.4 |
| 10368243 | 1382355302 | 17 | 19.25 |
+----------+------------+-----------+--------------+
10 rows in set (0.44 sec)
mysql> select sensor_name, from_unixtime(value_time), sensor_value from sensor_values sv join sensor_names sn on sv.sensor_id = sn.sensor_id order by value_time desc limit 10;
+-----------------------------------+---------------------------+--------------+
| sensor_name | from_unixtime(value_time) | sensor_value |
+-----------------------------------+---------------------------+--------------+
| Temperatures:Quasar:Boiler Output | 2013-10-21 12:35:08 | 45.5 |
| Temperatures:Quasar:Hot Water | 2013-10-21 12:35:08 | 51.37 |
| Temperatures:Quasar:Bedroom 1 | 2013-10-21 12:35:05 | 20.87 |
| Temperatures:Quasar:External | 2013-10-21 12:35:05 | 16.31 |
| Temperatures:Quasar:Attic | 2013-10-21 12:35:05 | 18.62 |
| Weather:River:Meerut Road | 2013-10-21 12:35:04 | 0.1 |
| Temperatures:BBC:Lymington | 2013-10-21 12:35:03 | 16 |
| Weather:River:Brockenhurst | 2013-10-21 12:35:02 | 0.4 |
| Temperatures:OWFS:Garage | 2013-10-21 12:35:02 | 19.25 |
+-----------------------------------+---------------------------+--------------+
10 rows in set (0.71 sec)
For displaying the data, I use jpgraph, which is definitely a bit more work than rrdtool due to having to write the php, but I think the end results are easily as good.
