This question is in two related parts. The first is about dataset size, the second data wrangling.
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.
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