Compiling SQL to MongoDB Pipelines, Part 2

SlamData’s the open source project I work on full-time. An analytics tool for MongoDB, SlamData has a shiny front-end that lets you run queries and build reports, and a powerful backend capable of compiling SQL to MongoDB.

For a separate people-operations perspective, the full article covers the limits of self-reported data.

Today, I’m happy to announce that we cut a new alpha preview release of SlamData, which you can download on the SlamData website. Cue all the usual disclaimers about alpha preview releases!

This alpha preview release supports more SQL than the last preview release, including the following features:

1. More kinds of expressions in the SELECT clause.
2. More complicated expressions in the ORDER BY clause.
3. Top-level, standalone aggregations like COUNT and SUM.

In this post, I want to talk a bit about how SlamData compiles SQL into MongoDB aggregation pipelines.

Running the REPL

While SlamData is typically used via the front-end, you can also run it from the command-line, or REPL (Read-Eval-Print Loop). Currently, the REPL shows more diagnostic information than the front-end, so it’s more useful when you’re trying to understand what’s going on.

If you’ve checked out the repository for the backend, you can run the REPL with a few commands:

sbt run 1

The 1 causes the backend to launch the REPL instead of the HTTP API that’s used by the front-end.

A Few Sample Queries

Executing a supported query in the REPL will also print out the MongoDB aggregation command that ends up being executed.

Let’s run a few SQL queries to see how they are translated to MongoDB:

 slamdata$ select city from zips Mongo db.zips.aggregate([ { "$project" : { "city" : "$city"}} ]) Results { "_id" : "35004" , "__sd_expr" : { "_id" : "35004" , "city" : "ACMAR"}} { "_id" : "35005" , "__sd_expr" : { "_id" : "35005" , "city" : "ADAMSVILLE"}} ... 

Pretty straightforward, eh?

Let’s try something a little more complicated:

 slamdata$ select count(city) as cnt from zips Mongo db.zips.aggregate([ { "$group" : { "0" : { "$sum" : { "$literal" : 1}} , "_id" : { "$literal" : 1}}} ]) Results { "_id" : 1 , "cnt" : 29467} 

Finally, let’s figure out what the 2 most populus zip codes are, and retrieve those populations in thousands of people:

 slamdata$ select pop / 1000 as popThousands, city from zips order by popThousands desc limit 2 Mongo db.zips.aggregate([ { "$project" : { "popThousands" : { "$divide" : [ "$pop" , { "$literal" : 1000}]} , "city" : "$city"}}, { "$project" : { "popThousands" : "$popThousands" , "city" : "$city" , "__sd_tmp_1" : { "0" : "$popThousands"}}}, { "$sort" : { "__sd_tmp_1.0" : -1}}, { "$limit" : 10} ]) Results { "_id" : "60623" , "city" : "CHICAGO" , "popThousands" : 112.047 , "__sd_tmp_1" : { "0" : 112.047}} { "_id" : "11226" , "city" : "BROOKLYN" , "popThousands" : 111.396 , "__sd_tmp_1" : { "0" : 111.396}} 

Hopefully that gives you a feel for how some simple queries are translated to MongoDB’s aggregation pipeline.

Now let’s talk a bit about how that actually happens.

Bird’s Eye View

At a high-level, the SlamData backend compiles SQL to primitives supported by a NoSQL database (in this case, MongoDB). This involves a number of steps:

1. Parsing the query.
2. Performing type-inference to infer the structure and types of the data being queried.
3. Validating that the query makes sense and is trying to do something sensible (e.g. all referenced functions are defined, the types check out, etc.).
4. Compiling the query to a logical plan, which represents the series of transformations that have to be applied to the data to produce the result.
5. Compiling the logical plan to a physical plan, which in the case of MongoDB, is a series of tasks all of which can be executed directly on MongoDB.

The chunk of code that’s responsible for (5) is called the physical planner for MongoDB. The physical planner is where most of the fun (and hard!) things happen.

MongoDB Physical Planner

SQL is very expressive, even moreso the dialect supported by SlamData, which is called SlamSQL. SlamSQL is like ANSI SQL but has additional operators for dealing with nested documents, arrays, and non-uniform data.

In general, translating SQL to MongoDB requires every ounce of expressiveness that MongoDB affords.

There are really four distinct entities that MongoDB exposes for running queries:

1. Selectors. Selectors are used in find queries, and also to filter data in the aggregation framework.
2. Pipelines. Pipelines are used in the aggregation framework. A pipeline is just a list of pipeline operators, such as $project and $match, each of which does a specific function, and all of which are applied sequentially to a source collection.
3. Expressions. Expressions can appear in various places inside of some pipeline operators. Expressions can transform and combine data.
4. Map/Reduce Jobs. Map/reduce jobs are expressed using Javascript and can do pretty much anything – given sufficient time and resources.

The physical planner currently only uses selectors, pipelines, and expressions, but we’re in the process of adding map/reduce jobs, too, for things that simply can’t be done using the other mechanisms (joins are a good example).

The way the planner works is by starting from the smallest elements in the query. It tries to translate these into both expressions and selectors (since MongoDB’s selector algebra is really an expression algebra, albeit for filtering rather than transformation).

Eventually, the planner will encounter a construct that it cannot translate into an expression — for example, the WHERE clause, which has no representation as an expression or selector. Here, the planner switches gears and starts building pipelines. For the WHERE operator, it would emit a $match pipeline operation that sucks in the selector built on the right hand side of the WHERE clause (or emits an error if no such selector exists).

Thus, gradually, expressions and selectors are transformed into pipelines. Once the entire query is translated from the bottom-up (or there is an error during translation because the query can’t be compiled yet), a final pipeline is produced which represents the entire sequence of operations that must be executed to produce the result of the query.

The SlamData backend also has facilities to run the pipleine (and report on errors), as well as retrieve collection names and pull back data from a collection.

MongoDB Aggregation Pitfalls

There are a number of MongoDB gotchas that complicate the preceding story of physical planning.

There’s a good chance that future versions of MongoDB will address these issues, but for now:

1. Selectors probably shouldn’t exist. Rather, boolean expressions should be used for filtering. This would simplify a lot of code in the planner, and probably make it easier for developers to use MongoDB because they wouldn’t have to learn two competing ways of constructing what are nothing more than boolean expressions.
2. Unfortunately, there is no expression to create a document or an array. One has to leave the world of expressions and use $project if one wishes to create a document. Subdocuments are only allowed in extremely limited places inside pipeline operators. If documents and arrays were ordinary expressions, the planner would be dramatically simpler.
3. There is no expression for projecting an object field or array element. This prevents you from, for example, constructing an object and digging into a field.
4. The aggregation pipeline is only designed to work on documents. There is no way to manipulate or return expressions. The aggregation pipeline should really allow working with expressions, but just require that by the end, all expressions be stored as object fields or array elements. This would greatly simplify the planner, which has to use a lot of workarounds to compute intermediate expressions.
5. While there’s an easy syntax for extracting out a field in a preceding stage of a pipeline (e.g. $foo.bar), the corresponding syntax for extracting out an element in an array of a preceding stage of a pipeline doesn’t work (e.g. $foo.0]). The syntax works in some places, but not others. So if you want to yank out an array element, you have to use a pipeline op to $slice the array element and then $unwind it from the array.
6. There’s no way to use multiple input collections in a map/reduce job. If the name of the input collection was passed to the Javascript mapper, it could use that to make a determination as to what kind of mapping to apply such that the reducer could distinguish between the different collections (in cases where that’s even necessary).

These are the major limitations of the aggregation pipeline and map/reduce functionality, which make the planner substantially more complex than absolutely necessary.

What’s Next

We’re hard at work on the upcoming beta release of SlamData, scheduled for August 15.

The beta release should include all the following features:

1. More charts on the front-end, as well as smarter charting features.
2. The ability to publish reports that pull from MongoDB databases.
3. The ability to add interactive forms to reports, which can be used by non-technical managers.
4. The ability to inspect the logical plan and physical plan from the front-end.
5. Full support for generalized expressions in any part of a query (currently, there are many limitations for complex expressions or combinations of expressions). At least, all those expressions which can be translated to MongoDB operators.
6. Full support for the GROUP BY and HAVING clauses.
7. Full support for equi-joins, executing as efficiently as possible using a combination of MongoDB’s aggregation pipeline and map/reduce jobs.

In the meantime, please build the code, download the installers, submit tickets or pull requests.

We’ve come a long ways, but still have a ways to go, and the more support we get from people like you, the faster we’ll get there!

SlamData is Visual Analytics for NoSQL

SlamData is an open source solution that makes it easy for people to see and understand modern NoSQL data, without relocation or transformation.

• Learn More

Characteristics of NoSQL Analytics Systems

Get the whitepaper that lays out the past, present and future of NoSQL Analytics. Written by John A De Goes, CTO of SlamData

• Download. Now.