MongoDB is a non-relational, document oriented database that is extremely popular with developers (see for instance this Hacker news poll). Toad for cloud databases allows you to work with non-relational data using SQL by normalizing the data structures and converting SQL to the non-relational calls.
I wanted to get started by creating some MongoDB collections with familiar data. So I wrote a Java program that takes data out of the Oracle sample schema, and loads it into Mongo as documents. The program is here.
The key parts of the code are shown here:
Here’s how it works:
|1-4||We loop through each customer, retrieving the key customer details|
|7-10||We create a basic MongoDB document that contains the customer details|
|12||We create another MongoDB document that will contain all the product sales for the customer|
|16-21||Fetching the data for an individual sale for that customer from Oracle|
|23-27||We create a document for that single sale|
|29||Add the sale to the document containing all the sales|
|36||Add all the sales to the customer|
|39||Add the customer document to the collection|
The MongoDB API is very straight forward; much easier than similar APIs for HBase or Cassandra.
When we run the program, we create JSON documents in Mongo DB that look like this:
Toad for Cloud “renormalizes” the documents so that they resemble something that we might use in a more conventional database. So in this case, Toad creates two tables from the Mongo collection, one for customers, and one which contains the sales for a customer. You can rename the auto-generated foreign keys and the sub-table name to make this a bit clearer, as in the example below:
We can more clearly see the relationships in the .NET client by using Toad’s visual query builder (or we could have used the database diagram tool):
MongoDB has a pretty rich query language, but it’s fairly mysterious to those of us are used to SQL, and it’s certainly not as rich as the SQL language. Using Toad for Cloud, you can issue ANSI standard SQL against your MongoDB tables and quickly browse or perform complex queries. Later this year, this Mongo support will emerge in some of our commercial data management tools such as Toad for Data Analysts and our soon to be announced BI tools.