Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I don't care about the general differences between SQL and NoSQL (or their traditional differences).

I am currently looking at altering the storage of our internal time series. They all contain financial data from a number of different sources. Currently, we are storing our data in a proprietary database. It's very much NoSQL, that has its own query language.

I'm interested in the community input: How would you store the data in a SQL database? What merits are there for using SQL over a NoSQL, specifically for time series? Am I insane for considering storing this in SQL?

Our data set consist of millions of time series, with around 10% of these containing millions of records each. The time series are organised hierarchically: /Market/Instrument/Value/Frequency, where:

  • Market is a securities exchange, etc, basically a collection of instruments, usually similar instruments.
  • Instrument is an instrument. This could be an indicator (Brent Crude), an equity (GOOG), etc
  • Value is one of multiple types of data for an instrument. This could be a close, high, low, etc
  • Frequency is the frequency of a particular time series values. Weekly, daily, monthly, tick, arbitrary, etc.

How would the data be stored in a SQL db? One big table (maybe partitioned by something), one table per market or instrument, one table per time series.

Thank you in advance.

share|improve this question
1  
Do all the time series contain the same metadata (ie columns)? –  Jack Douglas Nov 7 '11 at 10:55
1  
Sounds like a data warehouse... See this on SO: stackoverflow.com/q/2684462/27535 –  gbn Nov 7 '11 at 10:55
    
@jack-douglas: Are you asking that to suggest a column-oriented data store? –  Nicolas Nov 7 '11 at 10:59
3  
@Nicolas No my expectation is that a traditional SQL RDBMS would be well suited to your data because a) it would be easier to query, b) volumes don't sound impractically large (billions of rows?) c) date partitioning sounds natural and/or standard OLAP features. I was asking about the metadata to determine how many tables you need. If each time series has unique metadata you need millions of tables which doesn't sound like a good idea on a regular RDBMS, but I don't think you need that, do you? –  Jack Douglas Nov 7 '11 at 11:13
2  
@Nicolas have you looked into the new Hadoop connector for SQL Server. On the surface, your scenario looks to fit. –  Mark Storey-Smith Nov 9 '11 at 9:32

2 Answers 2

up vote 12 down vote accepted

In general, for such a structured dataset I suspect you could write a custom data format which was faster for most daily operations (i.e. small data pulls from an arbitrary time). The benefit of moving to a standard DB tool is likely in some of the extras, for example ad hoc queries, multiple access, replication, availability etc. It's also easier to hire help to maintain a standards based data store.

If I were asked to set up a database to store that data, I would do the following:

Proposed schema

(1) Core data is placed into numerous (1000's) of individual tables, each containing two columns:

  1. time: either a SQL DATETIME data type or a numeric type from some epoch (this is the primary key)
  2. value: typed as appropriate for your data. I would default to single precision float, however a fixed-point data type may be more appropriate for financial transactions. This is probably unindexed.

These tables will get quite large, and you may want to manually partition them by (for example) year. But you'll have to check system performance and tune as appropriate.

These tables need unique names, and there are a couple of options. They could be human readable (e.g. nyse_goog_dailyhighs_2010) or (my preference) random. Either way a set of metadata tables is required, and random table names prevent developers from inferring anything into the name that was not meant to be inferred.

(2) Meta data is stored in separate tables, as required by the application:

An additional table or set of tables is required to keep track of the metadata. These tables will contain data about exchange, instrument, value, frequency, date ranges, provenance (where did the data come from), plus anything else you need. These are mapped to data table names.

If there is enough data, this lookup could actually provide a table name and database name, allowing a sort of self-implemented data sharding (if that is the correct use of the term). But I would hold that in reserve.

Then at the application layer I would query the metadata tables to determine where my data was located, and then perform relatively simple queries on the big data tables to get my data.

Advantages:

  • My (relatively limited) experience is that databases can generally handle a large number of small tables easier than a smaller number of large tables. This approach also enables easier maintenance (e.g. purging old data, rebuilding a corrupt table, creating/reloading from backups, adding a new entity). This completely decouples the different kinds of data, if (for example) you have data at different rates, or requiring different data types.

  • This skinny table concept should also allow fast disk access for what I suspect is the most common query, a contiguous range of data from a single entity. Most data applications are disk I/O limited, so this is worth considering. As a commenter has already implied, this my be an ideal application for a column-oriented database, but I have yet to find a column oriented product that is mainstream enough for me to bet my career on. This schema gets pretty close.

Disadvantages:

  • About half of your disk space is dedicated to storing time stamps, when quite frankly 100's or 1000's of the tables will have the exact same data in the timestamp column. (In fact this is a requirement if you want to perform easy table joins).

  • Storing table names and performing the dynamic lookup requires a lot of application complexity and string operations, which kind of makes me cringe. But it still seems better than the alternatives (discussed below).

Considerations:

  • Be careful of rounding in your time field. You want your values round enough to enable joins (if appropriate), but precise enough to be unambiguous.

  • Be careful of time-zones and daylight savings time. These are hard to test. I would enforce a UTC requirement on the data store (which may make me unpopular) and handle conversions in the application.

Variations:

Some variations that I have considered are:

Data folding: If the timeseries is equally spaced, then use one timestamp column and (for example) 10 data columns. The timestamp now refers to the time of the first data column, and the othe data columns are assumed equally spaced between that timestamp and the next one. This saves a lot of storage that was previously used to store timestamps, at a cost of significant query and/or application complexity. Contiguous range, single entity queries now require less disk access.

Multi-plexing: If multiple time series are known to use the same time series, then use one timestamp and (for example) 10 data columns as described above. But now each column represents a different time series. This requires an update to the metadata table, which is not a lookup into table and column name. Storage space is reduced. Queries remain simple. However contiguous range, single entity queries now require significantly more disk access.

Mega-table: Take the "multi-plexing" concept to the extreme, and put all data into a single table, once time series per column. This requires large amounts of disk access for contiguous range, single entity queries, and is a maintenance nightmare. For example adding a new entity now requires a MODIFY TABLE command on a many TB table.

For additional discussion on this format, see the various answers in: Too many columns in MySQL

Fully normalized table: Instead of using many 2-column tables, you could use one, three-column table, where the columns are time, dataid, and value. Now your metadata tables only need to lookup ID values, rather than tablenames or column names, which enables pushing more logic into the SQL queries, rather than the application layer.

Approximately 2/3 of Storage is now consumed with the normalizing columns, so this will use a lot of disk space.

You can use a primary key order of (dataid, timestamp) for fast contiguous, single entity queries. Or, you can use a primary key order of (timestamp. dataid) for faster inserts.

However, even after considering these variations, my plan for my next development is lots of tables, two-columns each. That, or the method soon to be posted by someone wiser than I :).

share|improve this answer
    
Thank you very much for your answer. You've raised some very valid points. I completely agree with storing in UTC. I'm enforcing the idea that all data is delivered to the frontends (web, desktop & mobile) in UTC. We have multinational customers, and the OS should be responsible for doing the time conversion. I have a DBA company working on our entire data set, and wondered what others would come up with. Thanks again. –  Nicolas Nov 8 '11 at 8:01
    
While the DBA consultants work on targeting a beefy SQL Server installation, I will go ahead with testing with a BigData setup. –  Nicolas Nov 8 '11 at 8:02

Use MongoDB, you can create collections on-the-fly very quickly. Look at arranging your data into separate databases, and collections within those databases. Consider how much memory you would need to try to keep each shard within system memory - if you need fast retrieval. Silly to stick with an in-house solution, if there is something fresher out there that will evolve along the lines you need. Sounds like a good initiative.

share|improve this answer
1  
How would you store the time series in Mongo? Each document is a time serie? or the value of a specific timestamp? –  RockScience Mar 7 '12 at 9:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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