Analytics & Reporting on Compose: Use SQL to Understand Your MongoDB Data

Slamdata Logo

This is a guest article by John A. De Goes, CTO of SlamData. SlamData is the commercial company behind the open source project of the same name. John is the original author and an active contributor to the SlamData project. You can follow him on Twitter at @jdegoes, or on Github at jdegoes.

SlamData is a relatively new open source project lets you write and execute SQL queries against a MongoDB cluster. Unlike similar projects, SlamData queries always execute 100% in­database, because they’re aggressively compiled down to the find(), aggregate(), and mapReduce() APIs.

In this post, I’ll talk about how you can use SlamData, and then show you how to connect your Compose account to SlamData and write your first query.

SlamData is a perfect fit for Compose, because SlamData never streams back any data for post­processing. All queries are executed entirely inside of your hosted MongoDB databases.

Note: SlamData only supports MongoDB 2.6 and higher, so if you’re on an earlier version of MongoDB, you’ll need to upgrade before you can use SlamData. Compose makes this extremely simple.

What SlamData’s Good For

SlamData is targeted at two distinct use cases:

  1. Application Migration. Migrating applications (or even part of their workflows) to MongoDB isn’t always the easiest sell to management, even when it’s clearly the right technical decision. The reason is that companies often rely on using SQL for business access to the data, including reporting workflows. SlamData makes it easy to migrate those applications to MongoDB and preserve existing analytical SQL skill sets and assets.
  2. Application Intelligence. For existing applications already built on MongoDB, one of the key business problems is understanding exactly what is stored in MongoDB, and figuring out how that data can be used to troubleshoot customer issues, improve the product, understand usage, and so forth. SlamData is the only MongoDB­native solution for data discovery and ad hoc analytics, and it’s easy enough to use you can just hand it off to a data analyst.

Of course, it’s possible to solve these problems without SlamData. In fact, many developers are currently using MoSQL for these use cases. MoSQL helps you copy data from MongoDB into PostgreSQL, where you can use SQL to query the data or hook it up to a BI tool for others to query.

MoSQL is still useful, but there are a few advantages that SlamData has over MoSQL:

As an open source project released under the same license as MongoDB, SlamData is 100% free for commercial or non­commercial use. You can download pre­built installers from the SlamData website, or build the project yourself from the code available on Github.

Next, I’m going to walk you through connecting SlamData to your Compose account, and then we’ll take a look at how to query your Compose account through the SlamData interface.

Using SlamData with Compose

Using SlamData with Compose is very easy. The first step after logging into your Compose account is choosing the MongoDB deployment you want to use with SlamData:

slamdata1

After you've selected the right deployment, you'll be presented with a screen similar to the following:

slamdata2

On this screen, you should click the Admin tab, and then you'll see a MongoDB Connection URI, with blanks for the username and password. You'll need to modify this URI to include the username and password, and then copy it to the clipboard.

Next, download the official installers from the SlamData website. After you run the installer and choose where you want to install SlamData, you can launch SlamData by double­clicking the launcher.

You'll then see a screen similar to the one shown below:

slamdata3

Click on the + button to add a new MongoDB database to the SlamData file system. You’ll see the following screen:

slamdata4

From this screen, you can select Paste from Clipboard to paste your MongoDB Connection URI into the application.
You can then tweak settings if you like, and save the information to close the dialog.

Once you add a database and close the configuration screen, you’ll see the primary SlamData interface, shown below:

slamdata5

The interface shows you which databases are mounted to the SlamData file system, has a simple text editor that lets you run or compile queries, and has a log window at the bottom for diagnosing issues.

Querying Data on Compose

You can enter SQL queries in the editor, and hit Ctrl + Enter (Windows / Linux) or Cmd + Enter (Mac) to run the queries (or click on the Run button).

The results appear in a table below, and from there you can choose to copy them to the clipboard, or export them into a CSV file.

SlamData has broad (if not yet complete) support for SQL, including SELECT, WHERE, ORDER BY, LIMIT, OFFSET, GROUP BY, HAVING, CROSS, JOIN (all flavors), and numerous functions and operators. You can find more information on supported syntax and functions on the SlamData documentation page.

Note: SlamData allows you to mount different databases to different paths in the file system. So when you write a query, you must reference the full path to the data. If you mount a database at the root (/), then you must reference the collections in the database as “/[collection­name]”, including the quotes. For example, if you have a collection named zips, you can query from it using SELECT * FROM “/zips”.

Contributing to SlamData

As an open source project, SlamData’s pretty early, and we’re hard at work on 2.0, which will include improved query support, reporting capabilities, and data exploration and search features.

If you like what you see, please consider supporting the project in various ways:

  1. Watching, forking, and starring the repositories.
  2. Submitting pull requests and bug reports.
  3. Telling your family, friends, and colleagues about SlamData (Twitter, Reddit, etc.).
  4. Ask your company to sponsor any features you’d like to see in SlamData.

We also have a newsletter you can sign up for if you want to be notified when 2.0 comes out.

The future of analytics and reporting on MongoDB looks very bright!