Since several years ago I've being using R and some packages such as data.table to analyze data.
Now I'm working with a large dataset (200GB) that doesn't fit on memory.
Even if I can split the data and work with smaller chunks R is not the proper tool to work with.
I would like to move to a "real" database system instead and do most operations on it, and only use external tools such as R when needed. I didn't do it before because I wasn't aware of any DB able to perform all the operations I need.
What database or technology could I use?
It needs to be able to...
... work with terabyte-sized data on computers with 8GB RAM.
... reshape data from wide to long format, and from long to wide. (1)
... work on Windows 10. And ideally also on Linux.
... produce basic statistical descriptives.
... do statistical computations such as regressions, or be able to use R (or Python or Julia or any scientific platform, maybe Spark) to do so. (2)
... do basic string manipulation.
... work with dates.
... be fast both to write and read. (3)
... be free.
... be safe.
Being able to work distributed would be a bonus.
The easier to use the better.
I don't mind if it's NoSQL or a traditional database.
It's going to store data (integers, doubles, logical, small strings, dates) in columns or rows.
Sometimes I could modify its structure, adding rows or columns or reshaping it.
I'm not going to store graphs nor documents.
I prefer something not based on Java.
(1) As reshape or melt/dcast in R. With thousands of columns at once.
(2) I will use subsets of the data to perform mixed effects analysis, survival analysis and bayesian analysis (maybe with Stan).
(3) 98% reads / 2% writes. Sometimes by column, sometimes by rows, sometimes single elements.
My most important requirement is the ability to reshape the whole database from long to wide and viceversa. Many common scientific tools can do it (R, SPSS, Stata, Tableau...), but they don't use databases and they are not able to perform this kind of operations with data larger than memory.
Or this example from http://www.princeton.edu/
Getting the long format table is useful when you want to fit a model using the newly created variable.