“The adoption of a relational model of data, as described above, permits the development of a universal data sub-language.”– E.F. Codd, 1969

“Database research has produced a number of good results, but the relational database is not one of them.” – Henry Baker, 1991

Outside of programming language flame wars, few questions raise the hackles of hackers more than: “how should I store my data?”

I will argue here, like many such debates , the answer is:  it depends on what you’re doing.

While the rise of non-relational data stores serves a much-needed niche, the death of SQL and relational databases has been much exaggerated.  E.F. Codd may be dead, but SQL is alive and well as a simple yet powerful data query language.

3NF Crusaders vs NoSQL Rebels

While the current critique relational databases shares features of earlier debates (such as in the 1990s, when object-oriented databases were heralded as the next big thing), it has some new twists.  Thus to review the players and their positions:

On our right are the relational curmudgeons, the kind of folks who pen manifestos and crusade against NULL values. They have converted nearly all of big business to their ministry, and have billions of dollars in their coffers to show for it. They insist that data should be stored in terms of its relations, to protect its integrity and facilitate its analysis. Ideally that means third-normal form, but more liberal branches of the church exist.

On our left are the folks from the misnomered NoSQL movement, shaggy kids from the likes of Facebook and Twitter . They’ve rebelled against the shackles of relational tables (and bear the scars of MySQL scaling struggles). They believe that data should be persisted as it’s programmed: in objects. And they’ve spawned a constellation of colorfully named open-source projects – Cassandra, Voldemort, CouchDB, MongoDB, and Dynamo – to consummate their cause.

A Three-Pronged Attack on SQL:  Syntax, Schemas, and Performance

At the heart of the NoSQL movement are three distinct critiques:

1. A dislike for SQL’s syntax, which is ill-fitted to programming patterns.  It’s painful to write select statements to grab the data spread out across many tables, when all you want is a record.  Within web frameworks, the interface problem has been solved to a large degree by object-relational-mappers, such as Ruby’s ActiveRecord.
2. A rejection of the strong typing of relational schemas, which make it painfully difficult to alter one’s data model.  It also makes writing to the data store a complex process.
3. A critique of performance, which in turn relates to how concurrency and partitioning of computation is handled.  Most relational databases maintain a shared state, which strives for perfect concurrency, but complicates distributed computation over many nodes.  NoSQL architectures are built on languages and tools, like Erlang and Hadoop, that favor distributed processes which (to use two favorite catch phrases) “share nothing” but are “eventually consistent.”  The NoSQL philosophy also weighs heavily against joins.

These critical threads are mirrored in the movement and their associated projects.  One the one hand you have developers who prefer the programmatic ease of interacting with NoSQL data stores, such as Cassandra and CouchDB.  They also don’t suffer the performance penalties of scale:  unlike with relational tables, the performance of look-ups does not degrade as the stored number of objects rises.

On the other, you have Big Data analysts (like myself), who love Hadoop because it allows easy distributed computation over massive, loosely typed data sets.

Analytics:  MapReduce for Munging, SQL for Set Operations

With regard to analytics, the Hadoop ecosystem makes it easy to dump several billion records of varying formats into a data store and process them – without having to conform them to a common data model.   Thus NoSQL framework is great for massive data munging.

But if I had to access an already structured massive data set, I prefer SQL’s declarative syntax to MapReduce constructs.

I recently sat down at an SQL terminal with several hundred billion call records behind it. With a simple SQL query, I determined how many distinct people the average American telephones more than once in a given month (answer: five). In a few hundred seconds, I’d generated a report on the global state of the customer calling network.

Contrary to what the NoSQL may inveigh, it’s not that relational databases can’t scale – in fact, they can scale to petabytes, as those who know Fortune 500 enterprise computing can attest . The problem is that relational databases require lots of ETL cruft to munge fluid blobs of data into strongly typed tables.

I can’t imagine the programmer pain and suffering that went into building one, unified, global database.  But once it’s there, I’d much prefer to access it with SQL statements than MapReduce code .

And I’m not alone in feeling this way:  Jeff Hammerbacher of Cloudera recently told me that, for an enterprise deployment, usage jumped 10x when an SQL interface – HIVE (which I mention below) – was placed on the cluster.

NoSQL is a Misnomer: SQL is Innocent!

Which brings me to my defense of SQL.  I agree with two of three above critiques that embody the NoSQL philosophy, namely the need for schema-less storage and distributed architectures.  But when they go after SQL, and name the movement in opposition to it, they’ve named the wrong villain. (Your honor,) SQL is just an innocent query language!

As evidence of innocence, look no further than Google’s GQL and Hadoop’s HIVE, two SQL-style query languages for NoSQL data stores.

Why SQL in a NoSQL data store?   For one, it’s a language that both business analysts and developers already know; so the zero-th order adoption step is shorter.

But SQL lives on for a deeper reason: it is a simple yet powerful language for set operations. SQL captures the essential patterns of data manipulation, such as:

1. intersections (JOINs)
2. filters (WHEREs)
3. reductions or aggregations (GROUP BYs)

I suspect that many developers who profess a disdain for SQL have been deceived by its simplicity.  One of my favorite packages in R is sqldf, which allows SQL queries on R data frames.  SQL’s declarative expressions are frequently more readable and compact than their R programmatic equivalents.

MapReduce is Possible in SQL

Until very recently one of the more difficult operations to perform in SQL was a top-K query, for example, finding the five highest priced items in for every store in a retail database. But so-called window functions, which make such queries easy to express, have become part of the SQL standard and are now natively supported in Postgres.

Window functions are powerful because they provide a “split-apply” functionality, otherwise known as a map function. Combine these with SQL’s GROUP BY operations, which is a reduce function, and you have achieved – voila! – map-reduce in SQL. And as with all map functions, window operations are massively parallelizable (something that has not gone unnoticed by some commercial vendors.)

Verdict:  Don’t Use a Chainsaw to Cut Butter (Use the Right Tool)

Both NoSQL and SQL have their place in an analytics ecosystem.   In the Big Data workflow that I’ve advocated in the past, I view SQL as a pipe feeding data into more sophisticated modeling and visualization tools, such as R.  But it is an easy-to-use pipe, and it allows analysts to quickly pull out a subset of data — and start asking questions of that data.

The verdict in the great NoSQL debate is:  know your tools and know your goals.  In the Big Data space today, there can be an undue focus on formats or mechanics, but these are just a means to one end:  products.  Remember, Paul Graham and his team wrote Viaweb in Lisp, and it just worked.

Filed Under analytics | 8 Comments »