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% indatabase, 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 postprocessing. 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:
- 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.
- 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 MongoDBnative 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:
- Zero Data Relocation. Every query executes 100% inside MongoDB (optionally in a replica set). This is unique to SlamData, as MoSQL and other solutions require streaming data from MongoDB or bulk relocating it to another data store.
- Native NoSQL Data Model. Arrays and nested objects are given first class status. You can dig into them using SQL and do all the sorts of things you’d expect. You can also query heterogeneous data. MoSQL can only handle simple nested documents, and doesn’t work with arrays or nonuniform schemas.
- Point and Query. You can literally point SlamData to your MongoDB cluster, and begin querying right away. No need to define any data models, perform any kind of mapping, or setup other software and hardware infrastructure (all of which you have to do with MoSQL).
As an open source project released under the same license as MongoDB, SlamData is 100% free for commercial or noncommercial use. You can download prebuilt 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:
After you've selected the right deployment, you'll be presented with a screen similar to the following:
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 doubleclicking the launcher.
You'll then see a screen similar to the one shown below:
Click on the + button to add a new MongoDB database to the SlamData file system. You’ll see the following screen:
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:
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 “/[collectionname]”, 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:
- Watching, forking, and starring the repositories.
- Submitting pull requests and bug reports.
- Telling your family, friends, and colleagues about SlamData (Twitter, Reddit, etc.).
- 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!