This is a guest blog post by Jay Gopalakrishnan, Founder of Cloud9 Charts. Cloud9 Charts is a Reporting-as-a-Service focused on native analytics from NoSQL databases alongside traditional databases and other sources. Follow them at @cloud9charts.
Got data in MongoDB? How do you make sense of the data in it? Do you:
- Replicate the data into a relational database for reporting purposes?
- Have business users waiting for you to create manual queries to export data?
- Write head-scratching mapreduce functions to derive insights you need?
If so, read on. We’ll take a look at how to generate visual insights quickly using a live MongoDB database running on Compose.io, using Cloud9 Charts.
We’ll cover the following:
- Data discovery: Connect and discover collections & fields.
- Auto-query generation: Automatically generate MongoDB queries and aggregations using a visual interface.
- Visualization/Dashboards: Generate shareable, embeddable dashboards.
- Combining MongoDB data with PostgreSQL data: We’ll use PostgreSQL, the latest addition to Compose.io’s stable, to combine it with MongoDB data on the same dashboard.
To get started, visit our MongoDB Instant Analytics page. If your database is hosted on Compose.io, there’s nothing to install: You can quickly connect, discover and visualize the data from that page directly. To make things easier, we’ve pre-populated it with our own data demo database running on Compose.io.
Connect to the database
The first step, once you are on the MongoDB Instant Analytics page, is to connect to the database.
If you are using your own connection parameters, it will auto-connect to derive accessible collections for that account into the collections dropdown.
Note: Within Compose.io, your MongoDB connection parameters can be found under Deployments → Databases → Admin.
Choose a collection
Once connected, the next step is to choose a collection.
This will trigger a field discovery process to determine a set of fields associated to the collection (based on the most recent 100 documents).
Select your metrics
With the fields discovered, you can click to select, from the Metrics list, the fields you want to generate insights for (or type it in).
To specify an aggregation on a field, click on a selected field. This will open an aggregation option window.
Dimensions enable you to bucket metrics. You can select any optional dimensions in the Dimensions/Group By field. For date based fields, click on the field for additional bucket options.
Create optional filters and searches
Create any optional filter/search criteria. Filters are defined in the Filter field. Note that the queries will be auto-generated as you add/change any of the fields.
You can also plug in queries directly. The queries may be standalone Mongo queries, or, a mix of Mongo Query complemented with Cloud9QL in cases where MongoDB query comes up short, such as Date based bucketing for example).
Cloud9QL is an powerful SQL-like syntax to easily manipulate and pipeline data returned from MongoDB. See our Cloud9QL explorer for more details.
See the results
Click on "Show Me" to see the results. This executes the query and then auto-visualizes the results.
As you can see, in a few simple steps, we have connected, auto-generated queries and visualized data from MongoDB.
Dashboards once created, can be easily customized, shared & embedded, with options to auto-update it.
Sign up to be able to create and save your own dashboards from MongoDB and other sources.
PostgreSQL and other databases
Now let’s create a dashboard with data from both MongoDB and a PostgreSQL instance. You'll need to sign up to do this.
- Select PostgreSQL from the list of datasources on the settings page after sign up. PostgreSQL is one of more than 15 NoSQL/SQL and file based data sources we currently have support for.
- Set up the connectivity parameters to PostgreSQL (or use the default).
- Plug in SQL query - For our default database use: select opened, sent, date from demo_data.
- Click on Preview to instantly visualize it.
- To add the new widget to the dashboard: Click Save → ‘Back to Dashboards’ link → Open ‘Widgets’ section → Drag and drop widget into the dashboard.
As you can see, in a few simple steps, we are able to go from live MongoDB data to business insights quickly, in addition to combining it with PostgreSQL data into the same dashboard.
Going beyond the basics, contact us to learn more about the more complex use cases such as queries against multiple MongoDB instances, read preferences, simple joins, index checks, date tokens, results data warehousing etc.
Many thanks to the Compose.io team for hosting us!