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.
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
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.
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
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