Message Boards Message Boards

Best practices for saving TemporalData and Datasets to an external database

Posted 10 years ago

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.

POSTED BY: Michael Stern
3 Replies

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"
|>}]

A sample dataset loosely following the schema indicated in the above post

SQLInsert[myconnection, "MYTABLE", {"date", "portfolio", 
  "percentMaturedEarly"}, {SQLDateTime[
     DateList[#[["date"]]]], #[["portfolio"]], #[["percentMaturedEarly"]]} & /@ Normal[dataset]]
POSTED BY: Jesse Friedman
Posted 10 years ago

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.

POSTED BY: Michael Stern

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.

POSTED BY: Jesse Friedman
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract