MongoDB Gets SQL-Reporting Capability

6 Characteristics Of Data-Driven Rock Stars

One of the most successful NoSQL products, MongoDB, is detailing new enhancements on Tuesday that will allow it to work with standard relational reporting tools, such as Business Objects, Tableau, and Cognos. These upgrades are expected to hit the market this fall.

One of the criticisms of NoSQL systems is that it's difficult to know what data they contain. The programmers who wrote the applications that loaded the data know, and knowledgeable programmers can decipher one another's work to predict a comprehensive list of contents.

But what about data managers and database administrators trained to use SQL?

MongoDB engineers have built a connector that takes a standard SQL query from Business Objects, Tableau, Cognos, or other SQL-based analysis systems, and translate it into a query that MongoDB understands. MongoDB is popular as a JSON document database, capable of storing email, reports, comments, and other forms of text as objects in a database.

It has used its own query and reporting methods that in the past have been incompatible with SQL reporting systems, the ones that most data managers were familiar with, explained Kelly Stirman, vice president of strategy at MongoDB.

With the connector, anyone who can formulate the common English-like "select" query against a SQL database can name a set of data to be sought in MongoDB, and the connector will translate it into a query that brings back the correct set of results, Stirman said in an interview. The tool is good for reading data using a SQL query.

Other SQL functionality, such as joins, updates, and deletes are not executed by the connector.

[Want to learn more about MongoDB deployments? See MongoDB Eyes Bigger, Faster NoSQL Deployments.]

For reporting purposes, MongoDB will have a new "lookup" capability that can combine two different types of data that the user knows are related. For example, it can bring back order information related to a single customer at the same time that it returns customer information. The combo superficially resembles a join in a relational system, but MongoDB isn't claiming all the properties of a relational join -- hence the different terminology, lookup.

The connector makes the unstructured data in MongoDB available "to all sorts of new users, from business analysts and data scientists to business executives," said Stirman. There are lesser known SQL tools than Cognos or Tableau on millions of end-user desktops, and the connector will work with them, as well as the database administrator type tools.

Among other things, Stirman said, the MongoDB connector can be used to gather data for an Excel spreadsheet and other common data visualization and analysis tools.

The connector was built by working with joint MongoDB and Tableau customers. The connector is open source code, like other elements of MongoDB, he noted.

MongoDB cofounder and CTO Eliot Horowitz said use of unstructured data has become so extensive that it's imperative that data administrators be able to tap into the rich ecosystem of SQL-based tools to view and analyze this data.

"This is a lightweight connector with heavyweight capabilities," he claimed in the June 2 announcement. It was demonstrated at the second day of the MongoDB World show in New York, attended by about 2,000 users. Flight data from Tableau, derived from the US Federal Aviation Administration (FAA), was combined with conference attendee data in MongoDB to predict how likely the attendees would make it home on time.

CTO Eliot Horowitz

The connector will be new code in the 3.2 release of MongoDB, due out this fall in either September or October, according to Stirman.

At that time, MongoDB will gain the ability to encrypt valuable data in the database engine before it's sent to storage, avoiding any in-transit exposures. It can likewise import data from storage and decrypt it inside the database system.

Additional Features

In addition, MongoDB is being giving a capability of enforcing more data governance rules. Some unstructured data may be important enough that it needs to contain the same elements as related information, such as a correct email address or zip code for customers. Those rules may be added to a customer information document, and an error sent back to the data administrator if a data set arrives without those elements.

Strict interpretation of data types and data validation thus far has been a characteristic of relational database systems. Such rules don't need to be applied to all the unstructured data flowing into MongoDB, but they can be used when the data administrator chooses to apply them.

The 3.2 release will also include a new graphical interface to make it easier for developers and data administrators to use.

The additions to MongoDB are aimed at making the system easier to write applications for, and useful to a broader set of users. The new reporting capabilities can tell customers what type of data they have inside the system and how frequently high-value data occurs. For example, it can answer the question, For how many customers do we have an accurate phone number, or other specialized queries? Stirman said.

The 3.2 version will go into a public beta release over the summer in advance of becoming generally available in the fall. The new features make it "a better fit for how you run your business," he claimed.