5
$\begingroup$

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?

$\endgroup$
3
  • 2
    $\begingroup$ PutAppend? or LocalObject maybe? $\endgroup$
    – Kuba
    Oct 26, 2018 at 2:10
  • 1
    $\begingroup$ Don’t have time to handle it now, but the DataDropClient code (that’s probably the paclet name) is likely accessible via Mathematica and you may be able to trick it into using local storage $\endgroup$
    – b3m2a1
    Oct 26, 2018 at 3:42
  • $\begingroup$ @b3m2a1 Tricking DataDrop would be very useful. I'm not experienced working with internal code such as paclets, but if I figure it out I will definitely post it. $\endgroup$
    – Ghersic
    Nov 1, 2018 at 19:28

1 Answer 1

6
$\begingroup$

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.

$\endgroup$
5
  • $\begingroup$ Thank you very much for showing how straight-forward this is. I will accept the answer this weekend after implementing it, ideally incorporating the hardware (sensors, etc.) as well. $\endgroup$
    – Ghersic
    Oct 31, 2018 at 19:24
  • $\begingroup$ @Ghersic Did it work out? $\endgroup$
    – C. E.
    Nov 30, 2018 at 9:06
  • $\begingroup$ Yes, apologies for the delay. This works exactly as you described on my Windows PC, so I've marked it as accepted. However, it returns a cryptic error of JDBC::error: org.sqlite.core.NativeDB._open(Ljava/lang/String;I)V after the third line ("conn = ...") trying to open a user-made SQL database on the Raspberry Pi version of Mathematica. I've contacted them, but they simply said they don't provide support for the Pi, so perhaps I should simply report it as a bug. A note for those working on the Pi. This should still work on other systems though. $\endgroup$
    – Ghersic
    Dec 5, 2018 at 3:57
  • $\begingroup$ I was able to write something that simply records readings to a CSV file, though, using tricks like Kuba highlighted. Thank you again for your help. $\endgroup$
    – Ghersic
    Dec 5, 2018 at 3:59
  • $\begingroup$ @Ghersic that's disappointing... there are other database drivers one could use, but SQLite is a simple and very nice database. Good that you solved it with CSV at least. $\endgroup$
    – C. E.
    Dec 5, 2018 at 6:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.