MongoDB is classified as a NoSQL database. It is cross-platform document-oriented , and supports JSON-like data structures for ease of integration with JSON-based applications. In this post, I will attempt to build a sandbox MongoDB, and load it with some sample relational data sets from MySQL for use with the visualization tools Tibco Spotfire Desktop 7 (free), MicroStrategy Desktop 10 (free), and Tableau 10 (15-day evaluation).
List of Tools
1. Host OS Windows 7 Pro 64bit
2. Oracle VirtualBox 5.1 from https://www.virtualbox.org/
3. Ubuntu 16.04 64-bit
4. MongoDB 3.2.10
5. MongoDB BI Connector 2.0.2
6. Java JDK 8
7. Test Data files from http://www.transtats.bts.gov
8. MongoChef 4.5.2
9. Talend Open Studio for Big Data 6.3
10. Microstrategy Desktop 10
11. TibcoSpotfire Desktop 7.0
12. Tableau Professional 10
Building the MongoDB Sandbox Virtual Machine (VM)
1. Download Ubuntu 16.04.1 LTS from https://www.ubuntu.com/download/desktop
2. Download Oracle VirtualBox 5.1 from https://www.virtualbox.org/
3. Build a new VM from the download Ubuntu image (iso file)
Once you have successfully built the VM, you should be able to log in and see your Ubuntu desktop as shown below
4. Log into your Ubuntu VM, and run FireFox to download MongoDB from https://www.mongodb.com/download-center – Version used in this post is Ubuntu 16.04 Linux 64-bit x64
5. Install MongoDB using instructions from https://docs.mongodb.com/manual/tutorial/install-mongodb-on-linux/
6. Download MongoDB BI Connector from https://www.mongodb.com/download-center
7. Intall MongoDB BI Connector using instructions from https://docs.mongodb.com/bi-connector/master/installation/
8. Assuming that you are using the default data directory /data/db, start MongoDB from Terminal Shell as follows
However, if you have configured a different data directory for MongoDB, use the dbpath as shown below
9. Return to your Windows desktop, and download Talend Open Studio for Big Data 6.3 from https://www.talend.com/download/talend-open-studio/
10. Install Talend Open Studio for Big Data
11. Download MongoChef from http://3t.io/mongochef/download/core/
12. Install MongoChef
13. Run MongoChef to connect to your MongoDB, and create a new test database
14. Download some test data from http://www.transtats.bts.gov, or use this sample dataset carrier_movement_Year2000
Notes. The above sample dataset is for one calendar year 2000 only. The following screenshots were from loading the carrier movement data between 2000 and 2015.
15. Run Talend Open Studio and create a new workflow to load the sample data file into MongoDB as shown below.
16. Once the above workflow is executed successfully, switch to MongoChef to see your new collection such as shown below
17. Switch to the Ubuntu VM, and run mongodrdl to generate a SQL schema for the collection carrier_movement as shown below (also see https://docs.mongodb.com/bi-connector/master/schema-configuration/ for more information)
mongodrdl -d air_carrier -c carrier_movement -o carrier_movement.drdl
Notes. The above command generates a drdl file for the carrier_movement collection in the air_carrier database.
18. Start mongosqld listener for the carrier_movement collection
Notes. The above command specifies the listening address 192.168.1.12 on port number 3307. Modify the command with your specific address and port.
The carrier movement data in MongoDB is now accessible by Tibco Spotfire via its MySQL database connection, and MicroStrategy via its native MongoDB connection as shown below
View JSON collections in MongoDB using MongoChef
View Tibco Spotfire Dashboard
View MicroStrategy Dashboard
View Tableau Dashboard
Have fun with MongoDB – Watch out for my next post to connect MongoDB to MapR-DB.