How can a big table be treated as a database?

Question

I often work with big tables that I want to treat kind of like a database. Here's an example table.

theTable = {
 {"id","color","size","flavor"},
 {1,"blue",5,"cherry"},
 {2,"green",5,"piquant"},
 {3,"blue",20,"peppermint"}
}

In a database I would ask

SELECT * FROM `theTable` WHERE `color` = 'blue' AND `size` > 10

and get effectively

{{3, "blue", 20, "peppermint"}}

in response. In Mathematica, I need to determine the "column number" for color and size, then use Cases with And to do the same thing.

Cases[theTable[[2 ;;]], a_ /; And[a[[2]] == "blue", a[[3]] > 10]]

This is operationally much clumsier than the database way. Unfortunately setting up a database for every such table is too much extra work, particularly since I want the data to end up in mma anyway.

How can this approach be improved? Specifically, how can I more easily use the column names directly, instead of their part numbers? And how can I avoid the ugly a_/;f[a] pattern?

Answer 1

A way of getting around the a_/;test[a] syntax is to write out the tests in string form, and use replace to insert the values. For this to work you need to build rules from your table. Here is a simple implementation:

 SetAttributes[queryCriteria, HoldAll]
 queryCriteria[theTable_, query_] := Function[{entry}, 
 Unevaluated[query] /. (Rule @@@ Transpose[{theTable[[1]], entry}]), HoldAll]

 Select[theTable, queryCriteria[theTable, "color" == "blue" && "size" > 10]]

Personally I would prefer not having to give theTable as an argument to the query function constructor, since conceptually you shouldn't need a table to define a query, however it's needed during the construction because you have the field names listed in the first row. A way to nicely work around this is to consider a query an indpependent entitiy, which doesn't use the table until it's used in Select. This can be defined by setting an Upvalue pattern for Select, to resemble your included example, I use where as a name for the query:

 SetAttributes[where, HoldAll]
 Select[table_, where[query_]] ^:= Select[table, queryCriteria[table, query]]

So that the query can be written:

 Select[theTable, where["color" == "blue" && "size" > 10]]

This is all just ways of doing a similar thing with different syntax however. I would expect that performance issues become more important with big Databases.

Answer 2

Don't reinvent the wheel: If you need a database you should be aware of the SQLite access readily built into Mathematica, though unfortunately undocumented:

db = Database`OpenDatabase[FileNameJoin[{$TemporaryDirectory, "mma-temp-db.sqlite"}]];

Database`QueryDatabase[db, 
    "CREATE TABLE stuff(id INTEGER PRIMARY KEY,color TEXT,size REAL,flavor TEXT)"
];

Database`QueryDatabase[db, "BEGIN"];

Scan[
  Database`QueryDatabase[db, 
    ToString@StringForm[
      "INSERT into stuff(color,size,flavor) VALUES ('`1`',`2`,'`3`')",
      Sequence @@ #
  ]] &,
  theTable[[2 ;; -1, 2 ;; 4]]
];

Database`QueryDatabase[db, "END"];

Database`QueryDatabase[db,"SELECT * FROM stuff WHERE color = 'blue' AND size > 10"]

Database`CloseDatabase[db]

and in case you are more into speed than persistency:

Database`OpenDatabase[":memory:"]

for details just look for documentation about sqlite, there is tons of good documentation around for it...

EDIT: as murta mentioned in his comment it is also possible to make use of SQLite with the officially supported and documented DatabaseLink`. In version 10 a corresponding driver is included, for earlier versions a SQLite JDBC-driver has to be installed manually. As far as I can tell using the Database`* functions is a very lightweight approach most probably making direct use of the sqlite libraries while DatabaseLink` makes use of Java/JLink/JDBC which is kind of heavyweight but of course also has its advantages. Also from murta is the above example using DatabaseLink:

Needs["DatabaseLink`"]
conn=OpenSQLConnection[JDBC["SQLite",$TemporaryDirectory<>"testBase.sqlite"]];
SQLExecute[conn,"CREATE TABLE stuff(id INTEGER PRIMARY KEY,color TEXT,size REAL,flavor TEXT)"];
SQLInsert[conn,"stuff",{"color","size","flavor"},theTable[[2;;-1,2;;4]]];
SQLExecute[conn,"SELECT * FROM stuff WHERE color = 'blue' AND size > 10"]
CloseSQLConnection[conn]

For in memory version use: conn=OpenSQLConnection[JDBC["SQLite(Memory)","jdbc:sqlite::memory:"]];

Just for completeness: there are also drivers for HSQL included in all versions of DatabaseLink that I can remember of which provide similar functionality as SQLite, since version 10 there are also drivers for H2 and Derby included which also claim similar functionality.

EDIT since version 11.1 the Database` functions have been removed. So for any version newer than 11.0 one has to use the DatabaseLink` approach, but as they come with the SQLite driver you still can access SQLite databases in those versions out of the box.

Answer 3

I had forgot about this question, or the answer linked by @Leonid, or @jVincent's etc, and last week I was under the same "need".

I'll just post what I used since it's no extra work, in case it still helps someone.

Speed wasn't a concern, so I have no clue how much time this wastes

LabeledMatrix[cs_, mat_][cols : {__String}, funQ_] := 
    LabeledMatrix[cs, mat][cols, funQ, cols];

Normal[LabeledMatrix[_, mat_, ___]] ^:= mat

(lm : LabeledMatrix[cs_, mat_?MatrixQ])[cols : {__String}, funQ_, showCols : {___String}] :=
    Extract[mat[[All, label2Position[lm, showCols]]], 
      Position[LabeledMatrix[cs, mat][cols], {i___} /; funQ[i], {1}]];

LabeledMatrix[cs_, mat_?MatrixQ][cols : {__String}, All] := 
    LabeledMatrix[cs, mat][cols];
(lm : LabeledMatrix[cs_, mat_?MatrixQ])[cols : {__String}] := 
    mat[[All, label2Position[lm, cols]]];

SetAttributes[label2Position, Listable];
label2Position[LabeledMatrix[cols_List, ___], lab_] := 
    First@Flatten@Position[cols, lab, {1}, 1];

There's basically no error checking, formatting rules, etc.

Usage

LabeledMatrix is a wrapper. It takes, as a first argument, the names of the columns, and as a second, the data matrix.

lm = LabeledMatrix[
   {"ID", "Person", "Age"},
   {{4, "Peter", 23}, {5, "Mary", 33}, {55, "John", 23}}];

Say you want the "Person" and "Age", column

lm[{"Person", "Age"}]

(* {{"Peter", 23}, {"Mary", 33}, {"John", 23}} *)

The first argument, (unless you use the 3 argument form), is a list of the columns you want as output.

If you give a second argument, then that second argument is a predicate function to filter rows. The arguments taken by that function are those supplied in the first argument. Example

lm[{"Age", "ID"}, #2 > #1 &]

(* {{23, 55}} *)

If you supply a third argument, it's the list of columns returned. The first argument still works as the input to the predicate. So, say you want the IDs of the people aged under 30

lm[{"Age"}, # < 30 &, {"ID"}]

(* {{4}, {55}} *)

A second argument of All is the same as nothing. Normal gives the data matrix. First, or some other convenience function you want to create, the names of the columns.

Answer 4

1. Keep your data in external .csv files. Data is stored/queried in/from "tables" in a db anyway.

2. query=ReadList["!grep ...", String]

3. Process query, use ToExpression where appropriate, etc.

This has the following advantages:

• you don't actually keep your data in the kernel, you keep it in files, outside of the M system. That's where data storage SHOULD be: outside of the kernel. You shouldn't use the kernel for data storage, only for data processing.

• now the kernel will contain and process only your extraction / query result / result set. MUCH more memory-efficient. You shouldn't have in the kernel what you're not interested in anyway.

• grep and egrep are extremely fast. You're actually outsourcing the query to operating system speed.

• you don't need the overhead of a real d/b system (CPU, memory, installation, drive space, etc.) You only consume the drive space needed for your .csv files

What I've described above is actually how I do store large data amounts of data that I want to be available for queries. I don't consider this a workaround, it's a solution. Instead of installing a d/b application, I ALWAYS ask if I can simply do my tables (which I'd have to set up in a d/b system anyway) as well-designed .csv files (conforming to certain d/b standards, such as normalization, etc.), and then use the speed of grep, o/s methods, and ReadList["!grep ...",...], and then string-based processing and possible type conversions. Should be extremely hard to beat that speed, you don't need external d/b applications, you don't have external links (as you may be aware of, M's DatabaseLink package uses JLink internally), and you have additional flexibilities with this approach. You could, for example, save the .csv files from spreadsheet programs or other generators, and you can zip them when you want to archive them (text files can be zipped down to 7%), and you could even include zip/unzip steps in your M program to "set up" your d/b. A collection of properly formatted .csv files is actually a relational database! A simple d/b is nothing but a collection of well-organized tables, so you can do it yourself with text files (I recommend .csv).

Answer 5

This is my way. I'm used to name my columns as:

cId=1;cColor=2;cSize=3;cFlavor=4;

I append c because it's easy to use with the autocomplete, and prevent variables mix. And instead of cases, I prefer to use select as below:

r=Select[Rest@theTable, #[[cColor]] == "blue" && #[[cSize]] > 10 &]

If you want to take just some columns you can make for example:

r[[All,{cID,cColor}]]

Sometimes I have a lot os columns, so is boring to make the first step so I created this function that is in my tool bag:

sequenceVarList[list_] := Module[{varList},
    varList = StringReplace[list, {"_" -> "", " " -> ""}];
    Clear@@varList // Quiet;
    MapIndexed[(Evaluate[Symbol[#1]] = #2[[1]]) &, varList];
    TableForm[MapIndexed[{#2[[1]], #1} &, varList]]
]

So I can use it as:

sequenceVarList[{"cId","cColor","cSize","cFlavor"}]

and the first step is done easily.

I think that the big advantage of this solution is that you can use your column names in another calculations like GatherBy, SortBy and so on.

Update

If you are dealing with many tables, you can use the function sequenceVarList as:

Unprotect[Dot];
SetAttributes[Dot, HoldAll];
Protect[Dot];

mrtSeqVarList[varList_,symbol_Symbol] := Module[{},
    Clear[UpValues[symbol]];
    MapIndexed[(symbol/:symbol.#1=First@#2)&, varList];
]

So now you can do:

sequenceVarList[{cId,cColor,cSize,cFlavor},tab1]
r=Select[Rest@theTable, #[[tab1.cColor]] == "blue" && #[[tab1.cSize]] > 10 &]