Take the 2-minute tour ×

I have a requirement where I have large sets of incoming data into a system I own.

A single unit of data in this set has a set of immutable attributes + state attached to it. The state is dynamic and can change at any time.

The requirements are as follows -

  1. Large sets of data can experience state changes. Updates need to be fast.
  2. I should be able to aggregate data pivoted on various attributes.
  3. Ideally - there should be a way to correlate individual data units to an aggregated results i.e. I want to drill down into the specific transactions that produced a certain aggregation. (I am aware of the race conditions here, like the state of a data unit changing after an aggregation is performed ; but this is expected).
  4. All aggregations are time based - i.e. sum of x on pivot y over a day, 2 days, week, month etc.

I am evaluating different technologies to meet these use cases, and would like to hear your suggestions. I have taken a look at Hive/Pig which fit the analytics/aggregation use case. However, I am concerned about the large bursts of updates that can come into the system at any time. I am not sure how this performs on HDFS files when compared to an indexed database (sql or nosql).

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

You'll probably arrive at the optimal solution only by stress testing actual scenarios in your environment, but here are some suggestions. First, if write speed is a bottleneck, it might make sense to write the changing state to an append-only store, separate from the immutable data, then join the data again for queries. Append-only writing (e.g., like log files) will be faster than updating existing records, primarily because it minimizes disk seeks. This strategy can also help with the problem of data changing underneath you during queries. You can query against a "snapshot" in time. For example, HBase keeps several timestamped updates to a record. (The number is configurable.)

This is a special case of the persistence strategy called Multiversion Concurrency Control - MVCC. Based on your description, MVCC is probably the most important underlying strategy for you to perform queries for a moment in time and get consistent state information returned, even while updates are happening simultaneously.

Of course, doing joins over split data like this will slow down query performance. So, if query performance is more important, then consider writing whole records where the immutable data is repeated along with the changing state. That will consume more space, as a tradeoff.

share|improve this answer
Thanks Dean. For systems like Hadoop for example, I want to be able to define MapReduce queries to perform analysis on such data sets. However, updating records in files in HDFS does not seem possible. Constantly transferring large amounts of data into HDFS files does not seem efficient either. For Hadoop, is HBase the only answer ? –  user699324 Apr 13 '11 at 22:47
add comment

You might consider looking at Flexviews. It supports creating incrementally refreshable materialized views for MySQL. A materialized view is like a snapshot of a query that is updated periodically with the data which has changed. You can use materialized views to summarize on multiple attributes in different summary tables and keep these views transactionally consistent with each other. You can find some slides describing the functionality on slideshare.net

There is also Shard-Query which can be used in combination with InnoDB and MySQL partitioning, as well as supporting spreading data over many machines. This will satisfy both high update rates and will provide query parallelism for fast aggregation.

Of course, you can combine the two together.

share|improve this answer
add comment

Your Answer


