5
$\begingroup$

I am curating a small number of databases-actually I am not sure if they qualify as such; they are more like collections of-loosely-related data points; the problem is that they are growing over time; also some of these collections undergo revisions and maintaining them is starting to be an issue; they are scattered, at the moment, in different formats (mainly, collections of Excel worksheets, plain csv's and Eviews databases).

I am considering the idea of consolidating all the different formats into a single one that would be easy/efficient to work with Mathematica because up to now, importing and working with my data has been a less than streamlined operation (huge understatement).

I would appreciate any suggestions/pointers/solutions regarding a more or less unified approach for dealing with modest amounts of data from different sources under Mathematica.

If it is at all possible, I would be interested in approaches that have proven themselves overtime-if you have successfully maintained a small database over time I'd be interested to read about your approach; I am more in need of practically oriented solutions rather than theoretical considerations (I don't mind reading up; I don't like rabitholes)

PS. I was kinda hoping I would come to embrace Association's and Dataset's, but up 'till now they have been cumbersome to use. Also, there is the issue of the physical format for data storage.

$\endgroup$
  • $\begingroup$ If you've got object-oriented data I've found EntityStore quite useful. It plays pretty well with Dataset and Association, too. (so you know I'm serious I wrote a full-fledged database manager using it). $\endgroup$ – b3m2a1 Jan 18 '18 at 9:14
  • 2
    $\begingroup$ I think you probably should let us know a bit more about the nature of your data. Is the content of these xls and csv files entirely unrelated or are they variations with a common structure? What about the eview databases, what do they contain? What is it that you need to do with the data, how large is it in total? Unlike b3m2a1 I would as a first candidate think of a common storage format (probably something like hdf5 or sqlite which both are supported) so you could not access the data only with Mathematica... $\endgroup$ – Albert Retey Jan 18 '18 at 10:44
  • $\begingroup$ @AlbertRetey my data are (predominantly) time series data; I am not sure how to report the size of the collections as different file types store information differently; also some of the raw data have been enriched with meta-information; with those considerations in mind, I'd say that the combined size of my data collection right now is as low as 1GB and possibly as high as x2 that figure (x3 is less probable but not unlikely; I expect it grow relatively fast); most frequent operations are related to time series analysis (recall series, plot them, conduct regression analysis, tabulate results) $\endgroup$ – user42582 Jan 18 '18 at 12:23
  • $\begingroup$ so you would need to often read them, and read speed might be an issue although size is probably not a real issue, it probably would even be possible (although not optimal) to hold everything in memory these days. Would you need to update existing time-series, e.g. when additional data is available? How would you find/select a specific time series? Do you have a naming convention for them? What about the meta-data, what is it and how would you use it? $\endgroup$ – Albert Retey Jan 18 '18 at 14:27
  • $\begingroup$ in-memory solutions seem appalling because I use my data on multiple devices some of which are relatively old with low memory altogether; yes I need to update my collections at least four times a year and at most weekly; lookup so far is done manually-I have compiled indexes for every set of data that carry meta-info on the series; naming conventions are mixed because the data come from different sources; one reason for considering a unified solution under Mathematica is consolidation of separate sources of data into a unified bundle; meta-data are info on the data source, date, version etc $\endgroup$ – user42582 Jan 18 '18 at 17:12
3
$\begingroup$

After asking so many question I though I owe you an answer. You have another suggestion which is all valid and despite the information you gave I don't feel I understand your specific situation good enough to make a clear suggestion. I also see many other possibilities that should work and might fit well. But something that I have made very good experience with is to use an sqlite-database via DatabaseLink` to store data which I think is quite similar to what you report about yours.

Pros and Cons

Here are some reasons why I think that might be a good fit for your problem:

  • you can store your data in one (or a few) file(s). That makes data handling (e.g. backups, transfers etc.) much easier than having hundreds or thousands of single files in one or more directories.

  • loading and selecting many time series at once will be quite efficient

  • you are relying on proven technology which was made for the purpose (storing data).

  • using SQLSelect or raw SQL you have a powerful setup to search/select the data you need without having to load everything into memory.

  • you can easily extend your database and update existing data as needed. If you dig deeper into sqlite functionality you will find several ways to optimize disk usage and access speed.

  • depending on how you decide to store your data you can keep it in a format which will be accessable with other software/programming languages as well.

there are of course also drawbacks which have to be taken into account:

  • first time startoff of DatabaseLink` connections is kind of slow.

  • it is possible but probably suboptimal to store more than about 500MB or 1GB of data in one sqlite-file. If there is a "natural" way to split your data into junks of no more than that you probably should do that.

  • for huge datasets reading into memory is not as fast as it could be (extra overhead and copies. Note that this is true for most other import formats as well).

  • you shouldn't store (that is access) sqlite files on network drives: while sqlite can handle concurrent access by several processes/users to one file, it relies on file locks for that which usually can not be guaranteed to work for network filesystems.

I could also imagine that a combination of an index in a sqlite-file (to store metadata and references in a searchable way) and storing the actual data in extra files (e.g. as .mx files for maximal read-speed) could also be a good solution.

Example

Finally here is code for a minimal example. First open a database file and create a table to store the data:

filename = FileNameJoin[{$HomeDirectory, "Desktop", "data.sqlite"}];

Needs["DatabaseLink`"]

conn = OpenSQLConnection[JDBC["SQLite", filename]]

SQLDropTable[conn, SQLTable["Data"]]

SQLCreateTable[conn, SQLTable["Data"], {
  SQLColumn["Name", "DataTypeName" -> "TEXT", "PrimaryKey" -> True],
  SQLColumn["CompressedData", "DataTypeName" -> "TEXT"],
  SQLColumn["Source", "DataTypeName" -> "TEXT", "Nullable" -> True],
  SQLColumn["Date", "DataTypeName" -> "REAL", "Nullable" -> True],
  SQLColumn["Version", "DataTypeName" -> "INTEGER", "Nullable" -> True]
}]

then create data and insert into the table:

timeseriesdata = Table[{t, Sin[t]}, {t, 0, 20, 0.25}];

SQLInsert[conn, "Data",
 {"Name", "Source", "Date", "Version", "CompressedData"},
 {
   {"Dataset-One", "the internet", AbsoluteTime[], 3, 
    Compress[timeseriesdata]},
 {"Dataset-Two", "the internet", AbsoluteTime[], 1, 
    Compress[timeseriesdata]},
 {"Something-Else", "self generated", AbsoluteTime[], 3, 
    Compress[timeseriesdata]}
 }
]

here is an example how to read all data at once and convert it to a Dataset

Dataset@Apply[
  <|"Name" -> #1, "Source" -> #2, "Date" -> DateObject[#3], 
    "Version" -> #4, "Data" -> Uncompress@#5|> &,
  SQLSelect[conn, 
   "Data", {"Name", "Source", "Date", "Version", "CompressedData"}],
  {1}
  ]

This will only load the timeseries-data for those entries with names starting with "Dataset-":

Uncompress[#] & @@@ SQLSelect[conn, "Data", "CompressedData", 
  SQLStringMatchQ[SQLColumn["Name"], "Dataset-%"]]

Once you are done, don't forget to close the connection (that is basically a file-close, will happen automatically on Kernel quit)

CloseSQLConnection[conn]

There are some details about the above code:

  • I used Compress and Uncompress to store arbitrary data in a text column (you could also have used SQLExpr which I think does almost the same thing). That will usually work well, but of course that data is then only accessable with Mathematica. If you want to avoid that, you can convert to other formats and store as either text (I have e.g. used JSON strings to store Associations) or blobs (e.g. arrays of 32bit floats).

  • SQLite has a quite special way to handle data types (compared to other database engines/libraries). I think that might be the reason why the normal way to store Dates via DatabaseLink` does not work as expected. Storing AbsoluteTime values will work, but if you want to have the dates work with other software you will have to convert it (SQLite uses another offset for numeric timestamps)

$\endgroup$

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.