Best practices for saving TemporalData and Datasets to an external database

Questions
, ,
1

I have been working with a simulation tool that generates large volumes of data, and it’s time to start saving this to a proper database. Is there an established mechanism for saving TemporalData objects and/or Datasets into a SQL database, and for extracting data from the database back into TemporalData and/or Dataset format?

I appreciate it can be done by hand, but this feels like the sort of problem that somebody else may already have solved.

2

I don’t really use SQL myself, but depending on the format of your data, you should be able to use DatabaseLink and SQLInsert. If you’re making use of semantic data or entities, you could store them stringified and then use ToExpression to read them back in.

3

Jesse, the goal of putting the simulation results in a database would be to allow the use of structured queries to select results. “Show all simulation results for portfolio 1044 on or after September 1, 2015 where at least 20% of the portfolio matured early,” for (pseudocode) example. This doesn’t work if the data has been stringified. If it’s been stringified, you might as well just export it to a .m file.

4

A .m file would actually allow you to store native semantic data, but I can see why you’d want to use a SQL database. For the example you provided, it should be easy to use a few customized DatabaseLink commands to export the data into your schema. I suggested stringification because I didn’t know if you wanted to store Wolfram Language-specific objects such as GeoObjects or entities, although you could actually use SQLExpr for that.

Here’s an example:

dataset = 
 Dataset[{
    <|"date" -> DateObject["Aug 5 2015"],
    "portfolio" -> 1044,
    "percentMaturedEarly" -> "30"|>, 
    <|"date" -> DateObject["Aug 6 2015"],
    "portfolio" -> 2775,
    "percentMaturedEarly" -> "25"
|>}]

sqldataset.png
sqldataset.png310×106 5.92 KB

SQLInsert[myconnection, "MYTABLE", {"date", "portfolio", 
  "percentMaturedEarly"}, {SQLDateTime[
     DateList[#[["date"]]]], #[["portfolio"]], #[["percentMaturedEarly"]]} & /@ Normal[dataset]]