19
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ From some leftovers in the documentation I guess that the long term goal for datasets is (was?) to support SQL databases as a kind of backend. Of course that only makes sense / will work (well) for certain kinds of datasets. As for TemporalData-like data the common knowledge is that SQL databases are in general not well fitted to hold data which is purly numeric arrays. There are projects to extend SQL databases to have special support for arrays but the current best practice is to store such data in other ways, and I think hdf5 has become a quasi standard for such data in recent years... $\endgroup$ – Albert Retey Aug 21 '15 at 8:54
  • $\begingroup$ You've heard of NoSQL? Free your mind and your data like marklogic. In our studies we manage ~GB scale data, mostly temporal, stored in ~1k CSV files or Google gsheets. By letting the filesystem (eg Google Drive) do the work, a 1 line Query, the directory tree can be efficiently reconstructed as nested Associations. We routinely use Mathematica, R, and Python to access and analyze this without any DBMS. $\endgroup$ – alancalvitti Aug 21 '15 at 17:15
15
$\begingroup$

Preamble

I will try to illustrate several possibilities. Some of the following will be purely "manual" manipulations based on some top-level code of the type one would write.

Test data for SQL and HDF methods, and a tabular format

For SQL and HDF methods, I will use a simple tabular format for the data:

{columnNames:{__String}, data:{__List}}

which is, a list of column names, that is followed by a list of rows of data. Here is a sample data I will use:

stocksTab = {
  {"Name", "Date", "Open"}, 
  {{"HPQ", {2012, 1, 3}, 24.3}, {"HPQ", {2012, 1, 4}, 24.48}, 
   {"HPQ", {2012, 1, 5}, 24.3}, {"HPQ", {2012, 1, 6}, 24.53}, 
   {"HPQ", {2012, 1, 9}, 24.36}, {"HPQ", {2012, 1, 10}, 24.64}, 
   {"HPQ", {2012, 1, 11}, 24.46}, {"HPQ", {2012, 1, 12}, 24.68}, 
   {"HPQ", {2012, 1, 13}, 24.66}, {"HPQ", {2012, 1, 17}, 24.68}, 
   {"IBM", {2012, 1, 3}, 171.91}, {"IBM", {2012, 1, 4}, 170.84}, 
   {"IBM", {2012, 1, 5}, 170.14}, {"IBM", {2012, 1, 6}, 169.75}, 
   {"IBM", {2012, 1, 9}, 167.74}, {"IBM", {2012, 1, 10}, 168.69}, 
   {"IBM", {2012, 1, 11}, 166.38}, {"IBM", {2012, 1, 12}, 167.43}, 
   {"IBM", {2012, 1, 13}, 165.23}, {"IBM", {2012, 1, 17}, 166.04}, 
   {"INTC", {2012, 1, 3}, 21.68}, {"INTC", {2012, 1, 4}, 21.64}, 
   {"INTC", {2012, 1, 5}, 22.07}, {"INTC", {2012, 1, 6}, 22.2}, 
   {"INTC", {2012, 1, 9}, 22.37}, {"INTC", {2012, 1, 10}, 22.64}, 
   {"INTC", {2012, 1, 11}, 22.39}, {"INTC", {2012, 1, 12}, 22.76}, 
   {"INTC", {2012, 1, 13}, 22.62}, {"INTC", {2012, 1, 17},22.17}}
};

Depending on how you define your Dataset, you may need a few helper functions to convert from such tabular structure and back. If you use the format based on a list Associations, like

{
 <|"Name" -> "HPQ", "Date" -> {2012, 1, 3}, "Open" -> 24.3|>, 
 <|"Name" -> "HPQ", "Date" -> {2012, 1, 4}, "Open" -> 24.48|>,
 ...
}

then the following functions could perform the conversion:

ClearAll[tableToDataset, datasetToTable]
tableToDataset[table : {cols : {__String}, data_List}] :=
  Dataset @ Map[
    AssociationThread[cols, #] &,
    Replace[data, Null -> Missing["Not available"], {2}]
  ];

datasetToTable[ds_Dataset] :=
  With[{dt = Normal@ds},
    With[{keys = DeleteDuplicates@Flatten@Map[Keys, dt]},
      {keys, Lookup[dt, keys]}
    ]
  ];

You can test these by creating a Dataset out of the sample data given above,

stockDs = tableToDataset[stocksTab];

and converting back to tabular form:

datasetToTable @ stockDs

SQL

To my knowledge, in this direction there isn't yet any automation layer for Dataset, so this will be some work (not much, though). First, load DatabaseLink`:

<< DatabaseLink`

Here is a helper function that would dress on date lists in your data, in SQLDateTime symbolic wrapper - this in needed to write to an SQL table via DatabaseLink:

ClearAll[sqlDateDress];
sqlDateDress[table : {cols:{__String}, data_List}, types : {__Rule}]:=
  Module[{dateCols, sqldatedress},
    sqldatedress[d_SQLDateTime] := d;
    sqldatedress[d_] := SQLDateTime[d];
    dateCols = Flatten @ Position[types, _ -> "DATE", {1}];
    If[dateCols === {},
      table,
      (* else *)
      MapAt[sqldatedress, table, {2, All, dateCols}]
    ]
  ];

You may try running

sqlDateDress[stocksTab, {"Name" -> "VARCHAR", "Date" -> "DATE", "Open" -> "DOUBLE"}]

to see how this works. Note that this function is rather primitive, so if, for example, you also use DateObjects in your data, you may want to modify the internal sqldatedress function accordingly, to teach it what to do in that case.

Here are then possible (very simple) implementations for the functions to create an SQL table from your tabular data, and to read your data back from an SQL table:

ClearAll[createSQLTable];
createSQLTable[conn_SQLConnection, tableName_String, table : {{__String}, _List}, types : {__Rule}] :=
  Module[{sqlCols, created, data, cols},
    {cols, data} = sqlDateDress[table, types];
    data = Replace[data, _Missing -> Null, {2}];
    sqlCols = 
      SQLColumn[#, "DataTypeName" -> (# /. types), "Nullable" -> True] & /@ cols;
    created = SQLCreateTable[conn, tableName, sqlCols];
    If[created === 0, SQLInsert[conn, tableName, cols, data]]
  ]

Note again that the implementation is very simple, in particular I was setting all columns as nullable. You could implement a more complex syntax here, for example allow the user to specify which columns are nullable, in the types parameter. Note also, that if the table already exists, calling createSQLTable will result in JDBC error, which I made no attempt to handle.

Here is the counterpart function to read from the table:

ClearAll[readTableFromDB];
Options[readTableFromDB] = {
   RemoveSQLDates -> True
};
readTableFromDB[conn_SQLConnection, table_String, opts : OptionsPattern[]] :=
  With[{rules = If[TrueQ[OptionValue[RemoveSQLDates]], {SQLDateTime[d_] :> d}, {}]},
    {First@#, Rest@#} & @
      SQLSelect[conn, table, "ShowColumnHeadings" -> True] /. rules
  ];

It has an option RemoveSQLDates set to True by default, which instructs to strip the SQLDateTime wrappers from date data during the import. Again, one may want more complex behavior here, like using DateObject instead, etc.

To illustrate how this works, I will use an existing connection, which is used in one of the standard documentation examples:

conn = OpenSQLConnection["publisher"]

Now, here is how we construct a table:

SQLDropTable[conn, "STOCKOPEN"];
createSQLTable[
  conn,
  "STOCKOPEN",
  stocksTab,
  {"Name" -> "VARCHAR", "Date" -> "DATE", "Open" -> "DOUBLE"}
];

Note that I used SQLDropTable to delete the table if it already existed. To read the data back, we use:

readTableFromDB[conn, "STOCKOPEN"]

(* {{"NAME", "DATE", "OPEN"}, {{"HPQ", {2012, 1, 3}, 24.3}, ... }} *)

If you want to convert to and from Dataset, you can use the helper functions defined above, tableToDataset and datasetToTable.

HDF5

Here are the possible (also rather naive) implementations for saving tabular data to HDF5 format and reading data back from it:

ClearAll[tableToHDF5];
tableToHDF5[table : {colnames : {__String}, data_List}, path_] :=
  With[{dt = Transpose[data] /. SQLDateTime[d_] :> d},
    Export[path,
      dt,
      {"Datasets", colnames},
      "DataEncoding" -> Table[None, {Length[colnames]}],
      "DataFormat" ->
        Replace[First[data], {
          _String -> "String",
          _?(ArrayQ[#, _, IntegerQ] &) :> "Integer64",
          _?(ArrayQ[#, _, MatchQ[#, _Real]] &) :> "Real64"
        }, 1],
      "Dimensions" -> Dimensions /@ dt
    ]
  ];

and

ClearAll[hdf5ToTable];
hdf5ToTable[path_String] :=
  Module[{importF, colnames, data},
    importF = 
      If[# === $Failed, Throw[$Failed, hdf5ToTable], #] &@
         Quiet @ Import[path, #] &;
    colnames = StringReplace[importF["Datasets"], "/" ~~ x__ :> x];
    data = importF["Data"];
    {colnames, Transpose[data]}
  ];

Note that you can indicate dates either as strings, or as lists of date components. Here is how this can be used:

hdfFile = FileNameJoin[{$TemporaryDirectory, "stockhdf.h5"}];

Save data to hdf file:

tableToHDF5[stocksTab, hdfFile]

(* /var/folders/8r/lhqmmmj93hjgxbsx08g_5nhw0000gn/T/stockhdf.h5 *)

Read data from hdf file:

hdf5ToTable[hdfFile]

(* {{"Date", "Name", "Open"}, {{{2012, 1, 3}, "HPQ", 24.3}, ... }} *)

If you want to convert to and from Dataset, you can use the helper functions defined above, tableToDataset and datasetToTable, just as in the SQL case. One difference here is that the above simple implementations for hdf don't allow missing values.

Using .mx format

In my original post, I suggested to use some undocumented features. However, it just crossed my mind that I was over-complicating things, and that as long as you are comfortable to work with all data in-memory, you can just use the .mx format.

You will have to use

Export["your-file.mx", your-data-set]

to save the data set, and

Import["your-file.mx"] 

to import it back.

Since .mx is a very fast binary format, this may be your best option, so long as you only want to work with your data in Mathematica.

Summary

I tried to illustrate various methods one can use to persist tabular data, including

  • storing in SQL tables
  • storing as HDF files
  • storing in MX binary format
$\endgroup$
  • 2
    $\begingroup$ One important advantage that hdf5 in principle would support (though not the current Import implementation) would be to write and access only parts of huge numeric arrays at a time, e.g. read/write only some columns or rows of a 100000x100000 double array. That would make it possible to work with data even if it does not fit (or hardly fits) into memory and I think that would be an important feature for a system like Mathematica which is not specifically memory efficient. Am I correctly guessing that the new Dataset/Streaming stuff will allow such functionality as well? $\endgroup$ – Albert Retey Aug 21 '15 at 15:25
  • 1
    $\begingroup$ @LeonidShifrin, "JSON is similar to other data formats like XML - if you need to transmit more data, you just send more data. There's no inherent size limitation to the overall JSON request itself. " (stackoverflow.com/questions/1262376/…). JSON is as or more relevant in the web age the formats you selected. $\endgroup$ – alancalvitti Aug 21 '15 at 19:20
  • 2
    $\begingroup$ @alancalvitti Once again, JSON is relevant as a communication format, not storage. Your link just confirms it. No one in their right mind would store large datasets (particularly of inherently binary data) in plain JSON. Neither would people transmit Gigabytes of data over http requests on a regular basis. I don't exclude that people may use some JSON-like memory efficient binary formats, but that's not what you mentioned. There is no meaning to the world "relevant" without the context of what is the size and type of data, and how data is supposed to be used. $\endgroup$ – Leonid Shifrin Aug 21 '15 at 20:50
  • 1
    $\begingroup$ @MichaelStern One of the huge advantages of MX format is that you can store arbitrary expressions using it. So, you don't have to decompose your nested data sets, you should be able to save them directly to MX format, and then load from it. Re: speed - MX is a very fast binary format, so if anything, I am surprised that the speed difference is what you said and not larger. One other advantage of MX is that it doesn't unpack packed arrays. It seems to be fastest when you store your data in packed arrays and avoid associations. $\endgroup$ – Leonid Shifrin Aug 21 '15 at 20:58
  • 1
    $\begingroup$ @LeonidShifrin, see also Google Cloud Storage JSON API googlecloudplatform.blogspot.com/2014/05/… $\endgroup$ – alancalvitti Aug 21 '15 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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