Configuring Connections: MongoDB Connector for BI

Versions: 8.0+

Versions:7.2+

MongoDB, one of the prominent NOSQL databases, is increasingly part of Data Warehouse and Big Data ETL/LT data flows. MongoDB is therefore increasingly part of data testing and verification for many organizations. Due to interest in accessing MongoDB via SQL from BI tools, MongoDB offers a plugin supporting this use case. QuerySurge is able to utilize this plugin to execute SQL queries against a MongoDB database for data testing purposes.

A few of caveats are important to consider if you are planning to setup SQL access to MongoDB:

• The MongoDB Connector for BI is only compatible with MongoDB server version 3.2 or greater
• The MongoDB Connector for Business Intelligence for Atlas (BI Connector) is only available for M10 and larger clusters.
• If you are using MongoDB Atlas, the BI Connector is already enabled – no additional downloads are required
• Connector for BI uses the MySQL Driver that already comes bundled with QuerySurge
• Connector for BI requires an additional plugin (the “JDBC Authentication Plugin") for use with a MongoDB instance running with authentication. MongoDB provides the JDBC Authentication Plugin, which can be downloaded here (note the supported authentication methods and compatibility with MySQL Server or Connector Versions): 

Install the BI Connector

Follow instructions for your operating system to install the BI Connector from here.

Note:  If you are using MongoDB Atlas, the BI Connector is already installed, it just needs to be enabled. Instructions for how to enable and connect to MongoDB Connector for BI with MongoDB Atlas can be found here.

Generate a schema (Recommended)

A schema is used by the BI Connector to map your MongoDB collections and data types into common SQL tables and data types, and to describe how your non-relational MongoDB Collections should be treated as tables, rows, and columns.

Note:  There are several options for creating a schema for your MongoDB Data; they are discussed in the MongoDB documentation here. 

Create a configuration file (Recommended)

A configuration file contains important settings for startup of the BI Connector. A configuration file is useful for defining options and settings so that you don't have to do so with command line options every time the BI Connector is started. You can include settings such as authentication information, network options, and schema options. 

Note: It is recommended that you create a configuration file with the settings necessary for startup of the BI Connector. If you choose not to do this, these settings can be configured with command line options when you launch the BI Connector.

A sample configuration file is included with your install of the BI Connector, called "example-mongosqld-config.yml" You can use this as a template. Settings you can include are found here. 

Note:  If your MongoDB instance uses Authentication, your BI Connector instance must also use authentication. The user that connects to MongoDB via the mongosqld program must have permission to read from all the namespaces you wish to sample data from. You can specify authentication information in the configuration file.

Launch the BI Connector

Using the mongosqld tool that installs with the BI Connector (instructions for which can be found here), Launch the BI Connector. More details on mongosqld can be found here.

 Note: Depending on how you decide to go about creating a schema and Configuration file, the command line options for mongosqld can vary. We recommend generating a configuration file beforehand with all the important settings such as Authentication and schema path. This way, you only need to include a path to the configuration file on startup.

Deploy the JDBC Authentication Plugin (necessary if MongoDB is running with authentication)

1. Download the .jar file for the plugin from here.
2. Stop the QuerySurge agent service. Instructions to do this can be found here.
3. Copy the .jar file for the plugin to the QuerySurge agent's JDBC folder. Instructions to do so can be found here for Windows, and here for Linux.
4. Start the QuerySurge agent service.

Add a QuerySurge Connection to your MongoDB Connector for BI

  Note: The BI Connector process is accessed using the MySQL JDBC Driver, which comes bundled with QuerySurge. However because the Connection URL requires special parameters, a standard MySQL Connection should not be used; instead, use the QuerySurge Connection Wizard's Connection Extensibility option.

1. Navigate to the Connection Wizard by clicking the Administration icon on the and choosing Add Connection in the dropdown menu
2. Enter a name for your Connection
3. Choose All Other JDBC Connections (Connection Extensibility) from the Data Source dropdown menu
4. For the Driver Class, enter:

   com.mysql.jdbc.Driver

5. Use the following template to construct your Connection URL. Detailed information on constructing a MySQL Connection URL, and additional properties you can include can be found here.

   jdbc:mysql://<host><:port>/<database>[?properties]

     Note: If your MongoDB process is running with authentication, you will need to add the class for the JDBC Authentication Plugin to this URL as a property. The URL will now look like this:

   jdbc:mysql://<host><:port>/<database>?authenticationPlugins=org.mongodb.mongosql.auth.plugin.MongoSqlAuthenticationPlugin[&other optional properties]

6. Enter the Username and Password, if necessary. This can also be done in the connection URL as properties.
7. (Optional) Enter a Test Query
8. Click Next
9. (Optional) If you entered a Test Query, click "Test Connection"
10. Click Save

Once the Connection has made successfully, you will be able to write supported MySQL queries against you MondoDB process. A List of MySQL functions and operators supported by MongoDB Connector for BI can be found here.