MongoDB is a powerful Open Source and NoSQL database management system. However, MongoDB is a NoSQL database, which makes querying MongoDB less straightforward than SQL databases.
In this tutorial, you will learn how to query your MongoDB with SQL and use it for different purposes such as Analytics, Embedded Analytics, Machine Learning, Statistics etc., through these 4 steps:
- Prepare a SQL Database
- Connect to Your MongoDB
- Import MongoDB into SQL Database
- Start Querying
1. Prepare a SQL Database and Connect to Holistics
To use SQL for querying MongoDB data, first, you need to create a SQL Database (PostgreSQL, BigQuery, MySQL...) to store your MongoDB data.
If you don't know how to create a SQL database, follow this video guide to create your own SQL database in seconds. Feel free to skip the video if you already have one.
For example, we use our database details to connect Holistics to our demo PostgreSQL database.
2. Connect Holistics to Your MongoDB
Now you need to connect your MongoDB to Holistics as well. Similarly, provide your MongoDB database details to connect your MongoDB to Holistics.
Now that you have both your SQL PostgreSQL database and MongoDB connected to Holistics, you are ready to set up a Data Import pipeline, to move data from MongoDB to PostgreSQL to start querying your data.
3. Move Data From MongoDB into Your SQL Database
We use Holistics' Data Imports feature to import MongoDB into SQL databases through 3 quick steps
3.1. Set up Import Source
Create a Data Import job, and set your import source as your MongoDB. Choose the table you want to import. Here we choose a restaurant table
3.2. Set up Import Destination
Choose your destination source as your SQL database, select a Schema Name if needed, and choose or create a new table to import your MongoDB data into. We'll call our new table mongo_restaurant.
3.3. Transform and Extract your MongoDB columns to SQL columns
It takes a few seconds to move data from MongoDB to a SQL database, by selecting the columns you want to import and setting their destination types.
You can also transform your data in these configurations before importing, such as pulling out specific values from JSON data, using dot notation. For example, we extract the zipcode from the address JSON as address.zipcode, like the image below
4. Query Your MongoDB Data With SQL
Now you have the mongo_restaurant table in your SQL Database, you can start querying your MongoDB using SQL.
Extract anything you want from your MongoDB tables in your SQL query. Whatever you are able do with a SQL database, can now be done easily with your MongoDB data.
5. Create Dashboards, Drag-&-Drop with your data, and more
Now not only can you query your MongoDB data using SQL, but you can also create beautiful Dashboards using Drag-&-Drop.
Feel free to reach me with any feedback or questions at firstname.lastname@example.org!
Subscribe to Holistics Blog
Get the latest posts delivered right to your inbox
From SQL Queries To Beautiful Charts
Connect to your database and build beautiful charts with Holistics BILearn More
"Holistics is the solution to the increasingly many and complex data requests from the operational teams"
Tang Yee Jie
Senior Data Analyst, Grab