MongoDB is one of the most popular of a new breed of “NoSQL” databases. NoSQL databases are essentially the opposite of relational databases such as Oracle, SQL Server, MySQL, PostgreSQL, etc. They allow for the storage of unstructured and semi-structured data as well as the ability to maintain flexible schemas.
Here are a few key things to know about MongoDB:
- Focuses on the storage of “documents” (as opposed to graph databases or other types of NoSQL databases).
- Data is stored in JSON format (technically, they store data in a binary representation of JSON they call BSON).
- Built with developers in mind, so it has lots of tools, APIs, and drivers to meet the needs of virtually any developer.
- Because it does not require the creation of rigidly-defined schemas, it provides developers with lots of flexibility. The focus can be shifted from “schema-on-write” to “schema-on-read.” This creates much more agility for developers.
- Built with a distributed architecture so it is highly available, scalable, durable, and reliable out-of-the-box.
- It is open source, though MongoDB also sells licensed enterprise versions (more on that later). More details Tableau Training
Because of the above, MongoDB is a fantastic generalized database for any type of data from unstructured and structured data (and everything in between). It makes a great platform for small to medium data lakes. And it is one of the top choices of database for many modern developers.
Connecting Tableau to MongoDB
With the continued growth and popularity of the MongoDB platform, we as analytics professionals, will likely cross paths with it at some point and will need to connect Tableau (or other BI tools) to it.
The important thing to remember here is that Tableau expects data to come in a relational format—tables with columns and rows that are related to other similarly structured tables.
But MongoDB is not relational, so that immediately creates some challenges. Some restructuring of the data will inevitably be necessary for it to be consumable in Tableau. Thus, connecting to MongoDB is a bit trickier than connecting to more commonly used relational databases. Get more skills from Tableau Server Training
In this blog, I’m going to provide you with 4 options for connecting Tableau to your MongoDB data. For my examples, I’ll be using an instance of MongoDB Atlas, Mongo’s cloud database-as-a-service offering. You can connect to it using the following connection details:
Cluster Name: Mongo-shard-0/mongo-shard-00-00-pw3el.mongodb.net:27017,mongo-shard-00-01-pw3el.mongodb.net:27017,mongo-shard-00-02-pw3el.mongodb.net:27017
Username: nosql
Password: nosql
The cluster has a number of sample databases installed already, one of which is similar to Tableau’s Superstore, called sample_supplies; it contains a single collection called sales, which I’ll be using in the examples below.
Note: What we call Tables in relational databases are called Collections in MongoDB. Rows or Recordsare known as Documents.
Option 1: Export Data as JSON
The first option is to export data out of MongoDB into JSON files, then leverage’s Tableau’s native JSON connector. To do this, we’ll need to do the following:
1) On the computer running MongoDB, open a command line. If you cannot connect to the computer, as in the case with our Atlas instance, you’ll need to install the MongoDB utilities on your computer. To do this, download MongoDB and choose the custom install option. Deselect all options except for the Import/Export tools.
2) Open a command line and navigate to the directory in which the utilities were installed (on Windows, it should be something like this: C:\Program Files\MongoDB\Server\4.0\bin). Learn more skills from Tableau Advanced Training
3) Run mongoexport using the following syntax (red text indicates that we need to plug in our own values here):
mongoexport –host <Cluster or Host Name> –ssl –username <Username> –password <Password> –authenticationDatabase admin –db <Database Name> –collection <Collection Name> –type json –out <Output JSON File Name>
To export the sales collection from the sample_supplies database in our Atlas cluster, use the following command:
mongoexport --host Mongo-shard-0/mongo-shard-00-00-pw3el.mongodb.net:27017,
mongo-shard-00-01-pw3el.mongodb.net:27017,
mongo-shard-00-02-pw3el.mongodb.net:27017 --ssl --username nosql --password nosql --authenticationDatabase admin --db sample_supplies
--collection sales --type json --out c:\sales.json
4) Use Tableau’s JSON file connector and select the json file. Since the JSON file is not structured in a relational format, Tableau will prompt you to select which “schemas” you wish to include.
To get in-depth knowledge, enroll for a live free demo on Tableau Online Training
Mulesoft Online Learning 