While MongoDB is useful in production, using it for complex analytical purposes is a challenge (For example, JOIN operation using $lookup is a disaster and the limitation of BSON is 16 MB).
To help our customers make use of both the power of MongoDB and SQL databases, Holistics have a built-in ETL tool to pull data from MongoDB to a SQL data warehouse. After that, users can easily query and create dashboards/reports easily using SQL.
Here is a quick 3-step process to query MongoDB data in Holistics. MongoDB reporting in Holistics requires a connection to your MongoDB database, as well as to a reporting SQL database.
If you prefer a more detailed guide, please check the video tutorial below.
#Step 1: Connect Holistics to MongoDB to begin building reports.
Please ensure that you have also connected a SQL reporting database for Holistics to import your MongoDB data into.
If your database is backed with firewall, please check our guide to setup Reverse SSH Tunnels
#Step 2: Extract - Transform MongoDB data into SQL Database
Use Holistics' Data Imports feature to configure and schedule the extract - transform - load (ETL) process from your MongoDB to any of your connected SQL databases
You can use MongoDB's dot notation to pull out specific values you want, from a column that has data stored as JSON, for example.
#Get value for column where values are arrays of key-value pairs like below:
If you have a column whose type is an array like this:
And you want to extract only the value of
status key, the syntax is:
column_name.0.status and the value returned is
#Step 3: SQL Query and Create Reports/Dashboards from MongoDB
Visualize MongoDB data with SQL queries and run joins with other data tables to build automated reports/charts/metrics/dashboards as required!
Due to the NoSQL nature of MongoDB, here are a few things to take note.
- For nested object, you can use MongoDB dot notation to navigate to the wanted value.
- When specifying where_clause, you need to specify a JSON object similar to find() method of MongoDB.
For more detailed information on incremental loads for MongoDB, please have a look at our supporting documentation on Incremental Imports for loading data from MongoDB