How do you deal with very large datasets in Mathematica?

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.

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

-
Here and here it describes lower-level (than Import) functions for input-output. –  acl Dec 10 '12 at 18:08
This fantastic answer by by @LeonidShifrin might be useful –  ssch Dec 10 '12 at 18:08
Well, this is embarrassing, but how many rows are "2mm rows"? –  Mark McClure Dec 10 '12 at 21:59
mark: Sorry, finance style. ->2mm=2 million. Joel. You're probably right, I'll think about the phrasing and post a separate question. –  Steven Dec 10 '12 at 23:02
While this applies only to images (not the problem you have at the moment), it's worth mentioning that Mathematica 9 introduced some functions to process images without loading them into memory. Check ?ImageFile*. –  Szabolcs Dec 10 '12 at 23:26

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]

-
This stream approach looks very promising. Is there any way to read a line of a CSV file in as a list? If so, I can hack up something that'll work quite easily. I see RecordSeparators, but apparently those are three fixed CR/LF Types. It also gets confused by CSV field quoting. –  Steven Dec 10 '12 at 23:55
ImportString["1,2,3","CSV"] works for a single row, and should do all the same CSV field quoting that Import does, but I don't know anything about its performance. –  Joel Klein Dec 11 '12 at 0:34

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 ...",...]



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.

-
I can throw out unused columns, but have to load up the entire row first. Which is where my trouble lies. I think my best approach is just to do predictions in chunks of say 5000 rows at a time. Which will easily fit in memory. –  Steven Dec 10 '12 at 22:45
@Steven: If I understand you right, you need to read an entire line, just to find out if a certain element is needed (to be able to decide if a "column" is needed). This is a perfect example of the type of preprocessing I am talking about. Write a perl script that goes through it, line by line, and when it's needed, write it in one file, and when it's not needed, write it in another file. Now you have two smaller files (that has reduced your data size problem already), and when the item is needed, it's there in one file, and when not, it's missing in the other file. –  Andreas Lauschke Dec 10 '12 at 23:49
Well, I know which columns are needed or not needed ahead of time. I get those when I'm training my classifier. But you still have to load a file or a row before discarding it in mathematica. So I could write some code to go through, read a column I want, skip the next few values, and read the next column I want. But that seems like the wrong way to do it. Particularly next to just taking the pain of some wasted memory, and doing it in chunks of 5K rows. –  Steven Dec 11 '12 at 1:23
@Steven: part 1: everything you said in your comment can be done with a simple perl/ruby/python/other script. Use it as a preprocessor/parser, that creates new, smaller files. Memory in the kernel is precious. You should always get only that data into the kernel that is essential. If it's not, leave it outside the kernel. A line-by-line parsing of a text file should be done with perl/ruby/phython/other, that way you split your big file into smaller files of only essential data. Never discard data in the kernel. It shouldn't be there in the first place if it's discardable. –  Andreas Lauschke Dec 11 '12 at 4:45
@Steven: part2: perl/python/ruby/other are also JIT-compiled, so you get the speed of C-compiled code, much faster than processing huge data in the kernel. So it's not just a memory saver, it's also a speed saver. perl/ruby/python are incredibly fast on text files. I'd do all my raw data handling outside of the kernel (speed, memory, ...), and then use M only for high-level analysis. Never have useless data in the kernel. –  Andreas Lauschke Dec 11 '12 at 4:49