How do you deal with very large datasets in Mathematica?

Question

This question is in two related parts. The first is about dataset size, the second data wrangling.

Part 1: I've built a naive Bayesian classifier on Mathematica. It uses KernelMixtureDistribution to train on a large (2mm row) dataset. About 200 fields, of which 30 are used. Two separate classes are trained.

Unfortunately, when I try to load 2mm rows into Mathematica I get a Java out of heap space error. This is not the end of the world, I can load each column individually, compute the distribution and save it. Then load up all the distributions separately without the training data later.

The problem comes in, with the prediction part of the process. I have a 300Mb (400K rows) CSV file, and can't load it. In more traditional environments, I'd either read a row at at time (slow), do a prediction and write it out to a separate file. Or read in some block of rows at a time. I can't see a way to do this in Mathematica. Import seems to only import whole files, and runs out of memory. (4Gig)

How are people working with large datasets that won't fit into memory?

Part2: I'm dealing with data that mostly has the same columns, but can be shifted about a bit, sometimes have extra columns added. Mathematica, doesn't seem to have a record or structure type, only lists and matrices. How are people dealing with this? I've taken to loading my data into an SQL database, but even this is suboptimal - for my classifier I have to make sure I select the same fields, in the same order, in the same offset in a list.

Edit: It looks like Mathematica supports a sort of cursor called result sets. This looks to be slow (400K records, one at a time), but computer time is cheap, person time isn't.

I'm going to attempt to train my classifier per normal, then use result sets to individually classify each sample row. If this works out, I'll post an answer to this question detail how I did it.

I am making one huge presumption here, that MMA/Java database link will free up memory after each row is no longer needed. As long as that's the case, this approach might stand a chance of working.

http://reference.wolfram.com/mathematica/DatabaseLink/ref/SQLResultSetRead.html

Edit2:

After some playing around, I found Mathematica's result sets acts the same as a standard select would - it loads the entire dataset into JDBC and Mathematica's memory. So that approach runs out of memory just like a standard Import or Select would.

I hit on another approach relying on my database (PostgresQL) to do the lifting for me using cursors. This approach only works if you can process your data sequentially. For me, that's an acceptable limitation.

First I declared a cursor predRows, and made sure it would persist outside of a transaction (The hold part):

SQLExecute[dbconn, "declare predRows cursor with hold for select * from myTable;"]

Next, I fetch a row from the cursor - this will iterate over all my ~400K rows one at a time for each request. I can also ask for more rows say 1000 at a time if I'd like.

 (* Fetch one row at a time *)
 SQLExecute[dbconn, "fetch 1 from predRows;"]

 (* Fetch 1000 rows at a ttime *)
 SQLExecute[dbconn, "fetch 1000 from predRows;"]

This does mean I have to program in a imperative manner, but I'll learn to live with myself.

Postgres documentation on cursors: http://www.postgresql.org/docs/current/interactive/sql-declare.html

Answer 1

You can read lines from an InputStream strm (opened with OpenRead) in batches:

lines=ReadList[strm, "String", 4000]

You can vary the chunk size based on your application, 4000 is a number I found to work well for reading web server logs with lines that aren't crazy-long.

You can also reposition for random access on startup. Version 9 improves the use of streams here: streams now support 64-bit stream positions, even on 32-bit operating systems.

When reading from an InputStream strm,

StreamPosition[strm]

returns a stream position pos. You could store positions in an index data structure for fast random access on startup via

SetStreamPosition[strm, pos]

Answer 2

When I work on datamining projects it's usually not necessary to have each and every row in the kernel. You can submit Unix tools and perl to the command line and read only the resulting extraction into the kernel.

ReadList["!perl ...",...]

ReadList["!grep ...",...]

ReadList["!sed ...",...]

ReadList["!cut ...",...]

As mentioned at several places in this group, ReadList and streams (OpenRead, OpenAppend) are very fast and efficient, and OpenRead/OpenAppend use native methods.

You can probably throw out unused rows (use grep or egrep), unused columns (use sed, cut and their siblings). Hardly ever is all data needed, usually preprocessing and extraction can help reduce the size. You can even put simple statistics functions in the perl script and have it create smaller "sub" files, and then you read in the smaller files that are already providing a basic clustering of your data.