Local Storage of Periodically Collected Data (without using DataDrop)

Question

I would like to take periodic measurements (e.g. noise levels and temperature in my office from a Raspberry Pi, etc.), say, every minute and have a long-reaching record of this similar to Stephen's Personal Analytics blogpost. The free components of DataDrop allow for reasonably sized databins, but these are wiped every 30 days and I want to avoid subscription costs and just use my local storage. Clearly, the best would be if Wolfram simply allowed you to store a databin locally, but this is not included as far as I can tell - maybe to promote usage of their cloud service.

Can anyone conceive of a user-friendly means of doing this? Is there a simpler way than using ScheduledTask, and a basic combination of AppendTo, large lists of {DateObject[], myQuantity}, and a periodic Export using CSV or DumpSave using *.m files? (Along with possibly SessionSubmit or something similar.)

I feel as if there must be a simpler approach to this than the combination of these I am envisioning, perhaps using database linking and SQL or something along these lines. I realize this is a largely conceptual and opinion-based question, but is DatabaseLink overkill for simple storage of time-series data analogous to what one would do with DataDrop, or is it superior to my basic proposed method above?

Answer 1

I will show an example of how easy it is to set up a database, sometimes things seem complicated or overkill because one hasn't seen a good example of how simple it can be.

Start by connecting to a database:

Needs["DatabaseLink`"];
JDBCDrivers["SQLite"]

conn = OpenSQLConnection[
   JDBC["SQLite", "/path/to/database.db"]
   ];

If database.db does not exist then it will be created when you call this, e.g. you don't need to create the database in any manual way, it will be created for you like this.

Create a table to log water level measurements into:

SQLCreateTable[conn, "measurements", {
   SQLColumn["timestamp", "DataTypeName" -> "INTEGER"],
   SQLColumn["water_level", "DataTypeName" -> "FLOAT"]
   }];

This table now exists, this is all the setup that is necessary. Now we can start to input values:

Do[
  SQLInsert[
   conn,
   "measurements",
   {"timestamp", "water_level"},
   {UnixTime[], RandomReal[]}
   ],
  {10}
  ];

Here, we inserted a Unix timestamp, which is a good way to store a date and time in a database, as well as a measurement, ten times.

Later, we want to retrieve this data:

SQLSelect[conn, "measurements"]

(* Out: {{1540527269, 0.39035}, {1540527269, 0.921092}, {1540527269, 
  0.73255}, {1540527269, 0.44437}, {1540527269, 
  0.841312}, {1540527269, 0.242435}, {1540527269, 
  0.567367}, {1540527269, 0.0463256}, {1540527269, 
  0.490409}, {1540527269, 0.162797}} *)

It is also possible to retrieve a range of values by timestamp, averages per day, maximum values, and more, directly in SQL.

As you can see, this is very simple and lightweight. It's not overkill at all.

SQLite is good for your purpose because it is simple to use and it's simple to create as we have seen. It doesn't need a database server like some other databases. If you want to move the database to another computer or back it up, then you can simply copy the .db file.

As for how to log something periodically, you could use scheduled tasks in Mathematica, but maybe it would also be worth investigating how well it would work to create a Wolfram Language script and have that be run once a minute using the operating system's cron jobs. One would have to check how quickly it evaluates: if it takes several seconds to start the kernel just to run the script, then that would be less good. It might be like this, in which case it's better to have a kernel running. Such a kernel can probably, I say with some level of confidence but without having done it, be started via the command line so that it runs as a background task. This is worth looking into, in order to make the logging as unintrusive as possible.