37
$\begingroup$

I am wondering if anyone has used NoSQL (mongodb, cassandra, etc.) to store and analyze data. I tried searching the web but was not able to see if the financial firms had gotten in to using nosql storage.

So far even on this site i have only seen talks of using SQL DBs. I would imagine that NoSQL would be much faster.

Would someone shed light on the subject of NoSQL solutions in the finance world. have you seen/heard anyone use it? if so why are they using. if not using then why not.

Improve this question
$\endgroup$

7 Answers 7

Reset to default
22
$\begingroup$

Specialised NoSQL database systems are used a lot for time series storage, particularly for tick data:

  • Kx / Kdb is one prominent solution; from Arthur Whitney et al who did A+ at Morgan Stanley
  • Onetick is another, newer entrant, which has traces back to Goldman Sachs
  • Voltdb is something by serial database inventer Michael Stonebreaker
  • SciDB is another recent Stonebreaker project, trying 'commercial open source' but more for science apps (at least for now?)

The commercial offerings tend to be rather expensive and I have not heard of use of standard Web 2.0 NoSQL databases in trading environments. Some may of course do rather than talk ...

Improve this answer
$\endgroup$
7
  • $\begingroup$ thank you this is very helpful. i have used nosql and love it (used to only serve data for search engines for enterprises). the data is still stored in DB in the backend. $\endgroup$
    – user697697
    Commented Jul 1, 2011 at 20:35
  • 1
    $\begingroup$ Kdb+ and OneTick are column-oriented stores, which is a frequent topic here. Stonebreaker's own column-oriented DB is Vertica, which was recently bought by HP. (VoltDB is row-oriented and intended for OLTP applications, whereas the column-oriented products here are intended for OLAP.) $\endgroup$
    – chrisaycock
    Commented Jul 2, 2011 at 3:26
  • $\begingroup$ Chris, thanks for expanding the answer.. Besides Vertica, one could also mentions Sybase's offering in the area. I do think SciDB is column-oriented too, though, and could become a dark horse. They talk R support right from the start. $\endgroup$
    – Dirk Eddelbuettel
    Commented Jul 2, 2011 at 7:40
  • $\begingroup$ @Dirk Yes, Sybase IQ (now part of SAP) is also in this space. There's Vhayu (part of Thomson Reuters) too, though I haven't heard much from them in a while. I'm still confused as to what SciDB actually does. $\endgroup$
    – chrisaycock
    Commented Jul 2, 2011 at 15:21
  • $\begingroup$ Note that the link for SciDB should be www.scidb.org, not .com. $\endgroup$
    – joshayers
    Commented Dec 13, 2011 at 21:20
16
$\begingroup$

The reason that "traditional" NoSql databases will not get much up take in finance is that they are designed to solve a different problem. Most NoSql databases from the web world are designed with two central design parameters. First key lookups should be very fast. Second is that operations should be atomic at the row level and should not span records. This allows the database to be sharded very effectively since no operation should span multiple machines and in turn this allows them to build scale out architectures. These databases are built for very write heavy workloads and to be able to have fast key lookups to render webpages. While it is possible to use them to store tick data its not really what they are good at.

Financial data tends to be small relative to large web companies data. So scale out in finance is less important. Also financial data tends to partition well (at the day, symbol, or almost any other key) so replica placement tends to be more explicit.

Map reduce is the typical way to build analytical applications for these data stores either using hadoop (Cassandra) or internal map reduce (MongoDB has a java script MR API). This is typically not the ideal paradigm for analyzing time series data.

Traditional column oriented stores with integrated time series analytics tools or custom built platforms will continue to be the preferred way to store and process time series data. I don't think that there will (or should) be much convergence between the databases built for running very high scale websites and the databases built to store and analyze time series information.

All that being said we use mongodb extensively for metadata storage and as a sort of very large cache. But we don't use it to store or analyze financial data.

Improve this answer
$\endgroup$
9
  • 5
    $\begingroup$ "Financial data tends to be small relative to large web companies data"... yeah tell that to the 60 nasdaq TAQ dvds sitting on my desk right now (2010 data only). All compressed about 95%.Tick data and even 1 minute data on a large number of firms can be quite data expensive. Also if you are doing various calculations on various periods, the standardized columns of mysql might be overbearing, when you can simply add any fields you need to a NoSql document (Mongodb specifically). $\endgroup$
    – NoviceCoding
    Commented Jan 31, 2012 at 3:37
  • 3
    $\begingroup$ ITCH, and OpenBook ULTRA, PITCH, etc... would not be considered big by web company standards. OPRA is a big feed. 60 DVDs is just under 300 GB compressed which is tiny. There are plenty of reasons to use NoSQL for the reason you mention which is no schema. However it is unlikely that we will see (or should see) large scale (thousands of machines in an instance) NoSql deployments in finance because the data simply isn't that big. $\endgroup$
    – Steve Severance
    Commented Jan 31, 2012 at 17:50
  • $\begingroup$ But would you say thats because of the fact that support/employees for NoSql are small? Or is it a NoSql infrastructural issue? For example mongodb has one of the easiest (from what I've read) sharding and replication mechanisms which make it very easy useful for multi machine expansion (one of the most mentioned resions to switch away from SQL to MongoDb). But I am saying this with only experience of starting a project I am working on in mongo (college student, no legit experience). $\endgroup$
    – NoviceCoding
    Commented Jan 31, 2012 at 18:50
  • 2
    $\begingroup$ What I am saying is that many of the problems that the NoSQL data bases from the web world (Mongo, BigTable, Dynamo, their clones, etc...) are trying to solve are not necessarily the same as you would find in the financial world. There are another set of stores mentioned in Dirk's answer that are probably a better fit for quant problems. Multi machine expansion is not free and is easier in a NoSQL database because most make less strict guarantees. Mongo is very fast at key lookup. It does not have the type of time series operators that it would need to be truly useful. $\endgroup$
    – Steve Severance
    Commented Jan 31, 2012 at 19:40
  • 1
    $\begingroup$ Schema-less store is useful for storing client data. I've seen some tables with 148 columns, many of them null. For tick data, column oriented is the best model I've seen, and kdb+ the best product (primarily because of the language). $\endgroup$
    – Yike Lu
    Commented Sep 19, 2013 at 13:26
6
$\begingroup$

In the finance world, most data (especially given this forum is for Quant monkeys) is transactional and subject to regulatory reporting.

NoSQL isn't generally transactional and given the shape of, say, risk data, there is no overpowering reason to throw away ACID and RDBMS.

There are more mundane reasons: there are 1000s of IT guys who know RDBMS. When your NoSQL folk leave, where is your continuity and support?

I'm biased here: I'm an RDBMS database specialist who manages risk/pricing/trading systems

Improve this answer
$\endgroup$
7
  • 2
    $\begingroup$ what about the times when you simply want to store and perform analysis on data. i'd argue that in this case going to RDBMS is slower. by the way i am not biased towards one or another. $\endgroup$
    – user697697
    Commented Jul 1, 2011 at 19:28
  • 2
    $\begingroup$ In the finance world, most quants use column-oriented storage. This is particularly true for time-series data, like tick history. $\endgroup$
    – chrisaycock
    Commented Jul 2, 2011 at 3:38
  • $\begingroup$ @chrisaycock: based on my experience of Derivatives IT, they're all on conventional RDBMS. Or Excel. $\endgroup$
    – user1041
    Commented Jul 2, 2011 at 9:09
  • 1
    $\begingroup$ based on my experience, the difference seems to be within people who make money with data, who uses advanced systems, and people who use client for data, who use good'ol technology.. $\endgroup$
    – nicolas
    Commented Jul 9, 2011 at 17:31
  • 1
    $\begingroup$ For those who advocate RDBMS for finance data, i wonder if we might consider what i believe to be a common calculation in backtesting. Can someone please explain me how to efficiently calculate, for example, a moving 24 hour standard deviation? Even if we calculate it at each minute end, it's my understanding that this turns out to be very expensive in RDBMS. Is NoSQL better for this? $\endgroup$
    – Yugmorf
    Commented Aug 21, 2013 at 3:09
5
$\begingroup$

Cassandra is the obvious choice. With MongoDB or any RDBMS, you will hold all ticks in a table (collection in Mongo-speak) and index by ticker. This means that when you want to retrieve data for a ticker, the data will not be contiguously stored, and you will have a massive usage of index and random reads. Even with SSDs this is slow. For 500k ticks into Python from Mongo it takes me well over 200 seconds on a single 16GB SSD-equipped I7. Yes you can cluster, but the starting point is poor. Imagine if you need to bring in 100 tickers?

With Cassandra, everything is stored in column families. Big dicts of dicts, basically. You get entirely sequential storage of each ticker, meaning you can go back to spinning HDs if you want, but with SSDs it positively screams for data retrieval. I'm talking under 15 seconds for the same as the above. I'm not even going to go into the ease with which adding nodes to Cassandra speeds this up. The 15-second average is on a single computer. Lots of the "reasons for Cassandra" on the web talk about its easy scale-out using multiple nodes, and it's true this will make it even faster / safer, but for me this sequential columnar storage is what makes it perfect for finance. It starts out geared for timeseries, wherease Mongo does not. The design gives you at least an order of magnitude out-of-the-box before you even start messing with clusters and/or optimization. If you know Python, the analogy is simple. Cassandra is to Mongo (or an RDBMS) as Numpy is to Python lists. Chained pointers to scattered datapoints as opposed to a big chunk of contiguous data.

The converse of course is also true. If your use case is single (few) record(s) amongst millions (typical in web applications), Mongo makes this much easier than Cassandra. Cassandra is for big data, whereas Mongo/RDBMS map better to website-userID style workloads. Parodoxically, while SQL for example is considered quite rigid compared with "NoSQL", in my opinion, Cassandra is even more rigid than relational databases. But boy do you get performance if you accept its structures.

BTW Hbase is another columnar choice but I have no experience with it. The main message is, forget about RDBMS or "traditional" NoSQL. These are bad choices for multiple time series. What you want is a column database, of which Cassandra is an excellent example.

Improve this answer
$\endgroup$
3
  • $\begingroup$ I a bit confused with calling Cassandra a "column database" while you also say it's also basically a "dict of dicts". Using Python analogies, wouldn't a proper "column database" be store data more or like how a pandas DataFrame does? $\endgroup$
    – Gustavo Bezerra
    Commented Apr 11, 2016 at 7:52
  • $\begingroup$ @Gustavo Bezerra: no because Cassanda's rows need not be all the same length. So it's more like a dict of dicts in data modelling analogy, and unlike a numpy array (which is what Pandas is underneath) which must always be rectangular. However it still stores each row contiguously - that's the very essential point here, so that range queries are ultra fast. $\endgroup$
    – Thomas Browne
    Commented Apr 11, 2016 at 9:14
  • $\begingroup$ Is there somewhere where you can buy tick data? $\endgroup$
    – Nikos
    Commented Mar 31, 2017 at 13:43
4
$\begingroup$

mongodb seems to be a good opensource solution to store historical tick-by-tick. I am using it here and it seems to be very simple to use.

Improve this answer
$\endgroup$
6
  • 1
    $\begingroup$ How about the analytics side of things? Any successes or gotchas you can report? $\endgroup$
    – chrisaycock
    Commented Dec 13, 2011 at 17:04
  • $\begingroup$ what can I say ! its well developed using boost and a modern c++ approach and yes ! It does what they said on they site. it is scalable and high-performance. even the transport protocol is fast and it is JSON based so the maintenance of the database is quite simple. And I am not doing merchandise but the guys did a good work. And even better you can use it to implement the publish–subscribe pattern +.+ $\endgroup$
    – Clebson Derivan
    Commented Feb 27, 2013 at 21:51
  • 4
    $\begingroup$ Am I the only one whose gut feeling is storing tick data in a document database is insane? How well does MongoDB compress taq data? Is there any implicit ordering you can rely on for time series queries? $\endgroup$
    – Chuu
    Commented Feb 6, 2014 at 14:29
  • $\begingroup$ I love mongoDB for a lot of things, use it daily. Tick data, at least any sizable dataset, wouldn't be something I'd throw at it. $\endgroup$
    – Zeb
    Commented Dec 7, 2014 at 19:17
  • 2
    $\begingroup$ MongoDB is a poor choice for tick data. You will have to index by ticker name and then retrieving large amounts (anything > 10-20000 ticks) will have to query the index for all the atomized bits of your data. Cassandra, which is a column store, is infinitely better suited. It stores data in a column family so all the ticks for a ticker on sequentially stored on disk. I have moved from Mongo to Cassandra for precisely this reason and have seen my range queries on tick data (usually 500k ticks approximately) increase in speed by 20x. $\endgroup$
    – Thomas Browne
    Commented Aug 24, 2015 at 19:16
3
$\begingroup$

I am wondering if anyone has used NoSQL ... to store and analyze data.

Yes. Have a look at arctic on github. This is an open-source API built on top of MongoDB, that is in production use by one of the largest hedge funds in the world, for storing time-series data.

I would imagine that NoSQL would be much faster.

In the github wiki you'll find links to presentations where they show performance metrics vs their existing, commercial DB. This is achieved partly by storing the data in compressed format, and sending over the wire as-such (this is a feature of the API; not MongoDB).

Other cool features include versioning, quotas, acls.

Improve this answer
$\endgroup$
0
$\begingroup$

In regards to the above answers, for tick data or time series , you could probably use a combination of redis (in memory data-store) & mongodb, or use Hbase with bus events.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.