Choosing Storage for Tick Data Analyzed in R

# R: How feasible is it to store -- and work with -- tick data in a database connected to R?


# R: How feasible is it to store -- and work with -- tick data in a database connected to R?












I'm looking to convert some tickdata .csv files into a database on a local disk and then use R to call the data and do my various analytics and modelling.

What are some best practices / implementation techniques that could be recommended to minimize any headaches?

Noted that packages such as `mmap` help enormously, but would like to try and find a more 'permanent' solution.

Which is the best db to play with? MySQL seems to be suboptimal as it's relational (rather than column-oriented). q/KDB seem to have a trial version to play with but has a very steep learning curve. Which is the best db to handle and serve up tick requests?

Any help much appreciated. Platform agnostic, but I suppose Linux is my preferred platform.

## Answer by Christoph Glur (score 16, accepted)

https://quant.stackexchange.com/a/2874

There are many specialised products for HF tick data. In addition to KDB which you mentioned, there is OneTick, Vertica, Infobright, and some open-source ones like MonetDB etc. (see http://en.wikipedia.org/wiki/Column-oriented_DBMS).

My experience is that Column Oriented Databases are overrated when it comes to tick data, because very often you request the entire tick or bar record (as opposed to just one column of a record - i.e. what Column-Oriented DBs are optimised for). In my experience, the key to speed is much more that you use a clustered index for your database, thus defining the order in which the data is stored on the harddisk. If you primarily query the timeseries of a given instrument (as opposed to the latest prices of a group of instruments), then you want to cluster by (Instrument, TickTimestamp), making queries extremely fast even for huge table sizes.

Then there is also the school of thought that plays around with new alternatives out of the NoSQL corner, such as BigTable, MongoDB, etc. It's an interesting area, but my personal believe is that they are made primarily for flexible datamodels, which is not our core requirement. You can make them work, and they'll work very fast, but this comes at the cost of more archaic tool support, steeper learning curves, etc.

I have been using many different databases (Oracle, MySQL, SQLServer, MongoDB, MonetDB) over the years, and my conclusion is that most of them work pretty decently for storing financial timeseries data if you understand them and design them accordingly. Currently, I'm using primarily SQLServer, which is somewhat faster than MySQL, free for smaller datasets, and does most of the things I want. Support for R (and Matlab and many other environments) is very decent through the ODBC R package.

## Answer by Darren Cook (score 6)

https://quant.stackexchange.com/a/2926

Using MySQL for financial data is not unreasonable. But for tick data are you ever going to do anything except a query on a date range? For analyzing tick data in R I generally keep it in a disk file, one tick file per day, and load the files in as I need them. Using .RData files instead of csv files is quicker.

I've also used custom C++ classes before, to really get quick, but if the data is then to be analyzed in R, there is not much point.

## Answer by nxstock-trader (score 1)

https://quant.stackexchange.com/a/2869

I have had success using MySQL to store both OHLCV, Options data and metadata such as earnings dates in MySQL and accessing both for reads and writes from R.

For me this works very nicely and is performant for daily data, if you are doing HFT you may want to consider a specialized tickdb, but at daily scales (252 returns per year per ticker - MySQL is plenty fast). Also, it's much better than flat files because you'll find at some point that you do want to issue relational queries to find time series that match certain aggregate patterns.

Examples that I typically do are to JOIN OHLC data with Options data for a given ticker and merge by date, sure R's XTS::merge can help here as well but it's often more performant to issue SQL queries direcltly and use R's dataframes/XTS for 'fine tuning' once you narrow down from Gigs of data (which R is not great at) to a few MB.

If there is interest - I could consider sharing my .R modules for interfacing with MySQL for both daily downloading and storing of data (e.g. from Yahoo for OHLC of course), as well as querying.

My advice is don't overdesign it - if you don't need the perf of a specialized tick DB, and for daily scales I don't think you do - go with what's proven and simple. MySQL works.