Technology stack for linear regression on (not so) large dataset

Question

While attending to the Coursera's Machine Learning Course, I figured out that I could use a database from the company I work for (~50MM records) to do some linear regression experiments.

But one of the steps involved on proposing this experiment, is to define the technology stack required for this task.

From my understanding the following tasks should be covered:

1. Read raw data and store it on an non-production database
2. Transform data to a "regression friendly" format
3. Store the transformed data on an intermediate database
4. Compute the actual regression

For #1 I can take some paths, like doing a custom .NET or Java program, or even use an ETL process (this is more to copy data to somewhere else and don't mess with production database).

On #2 the funny part begins: should I consider a specialized tool for a <100MM records database? If so, what would you suggest for transforming this data into a matrix-like representation?

I believe #3 is dependant on the #4: I see lots of samples (eg.: in R, or Matlab/Octave) based on text or csv files. Are these the standard formats for these computations? Or should I read from a database

For #4, from what I could understanding using R is the way to go, right?

Finally, should I consider a multi-gig multi-processors server, or considering it's an experiment in which spending some hours of computation is not a big issue, a 4GB machine will do the job?

I am aware that this question may be considered too broad, but I really would like to hear from you about what should I consider for it, and even if I am missing something (or going to a totally wrong path).

Regarding the data, you can consider it like the house pricing in Boston: it's a 30 features (columns) dataset, used to predict the value for one of these columns.

(question originally posted on Stack Overflow)

Answer 1

Since no one has mentioned RAM-efficient methods yet:

Instead of loading everything into RAM, you can use online/out-of-core learning.

Python's Scikit-Learn for example has the SGDClassifier class. Set its loss function to "log" and you get logistic regression. Using the partial_fit function you can feed it small batches of data that you read straight from the database (or from some CSV file,...).

Vowpal Wabbit might also be worth a try. Its made for out-of-core learning - hardly uses any RAM and you won't find anything that's much faster.

You could also use Python's Keras library to build a neural network (or in the simplest case just logistic regression), which you can also feed with small batches of data instead of loading everything into RAM. Compared to the other two recommendations a neural network could also learn non-linear dependencies.

Besides that, try to start with fewer samples - plot the learning curves with 10k, 100k, 1M samples and see if 100M samples are even necessary to get a good score.

Answer 2

It's easier if your entire dataset fits into RAM. So check how large it is in GB and get enough RAM. Multiprocessor probably won't help much. So try to get highest frequency proc with just a few cores. You could extract your data into a csv and use R or Scikit-learn for modelling.

Answer 3

You can use R or Python for 100 million records with the conventional regression libraries. You will require around 16GB of RAM according to my experience, may be more than that!! A quadcore processor will be fine while running algorithms and during pre-processing steps. It would be better to store the transformed data to an immediate database.