Connecting QuerySurge to MongoDB with the CData MongoDB JDBC Driver

Versions: 8.0+

MongoDB, one of the prominent NOSQL databases, is used by many organizations as a component in Data Warehouse and Big Data ETL/LT data flows. MongoDB is therefore part of the data testing and verification effort for these organizations. For QuerySurge users working with architectures including MongoDB, CData offers a MongoDB JDBC driver. Setup details for using the CData MongoDB JDBC driver to connect to QuerySurge follow. 

Note: RTTS, the vendor of QuerySurge, partners with CData to make a broad range of JDBC drivers available to QuerySurge users. For information about our partnership, click here. See all of CData's JDBC offerings here. For questions related to ordering, contact us here.

Adding a MongoDB Data Source Connection using the CData JDBC Driver

To connect to a MongoDB data source, you'll need to use the Connection Extensibility feature of the QuerySurge Connection Wizard.

1. Download the CData MongoDB JDBC driver.
   
   Note: You'll need to deploy a copy of the driver to each Agent that you expect to issue MongoDB queries on.
   
   
2. Deploy the MongoDB JDBC driver to your Agent(s). To install the CData driver, unzip the driver download and run their driver installer (setup.jar) to install the driver on an Agent box (instructions for doing so can be found in the readme.txt file). We recommend that you install the driver in the default location. Once installed, copy the driver file(s) and the license file from the installation directory to your QuerySurge Agent jdbc/ directory (see the relevant Knowledge Base articles for deploying drivers to Agents on Windows or Agents on Linux). You'll need to deploy a driver for each Agent box on which you intend to use the driver. 
   
   Note: The deployed files are: a) cdata.jdbc.mongodb.jar and b)  cdata.jdbc.mongodb.lic. A license file is required both for trials with the driver and for fully licensed drivers. 
   
   
3. Login to QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Select Connections in the leftnav tree, and click the Add button (at the bottom left of the main panel). Leave the Advanced Mode checkbox unchecked. You'll need the following information for the next steps:

   Driver Class: cdata.jdbc.mongodb.MongoDBDriver

   Connection URL template: jdbc:cdata:mongodb:Server=<yourMongoDBServerNameOrIP>;Port=<yourMongoDBPort>;Database=<MongoDBDatabase>;

   You'll need to provide a URL with actual values for the MongoDb Server, Port (default is usually 27017) and database.
   

4. Enter a Connection Name of your choice, and select *All Other JDBC Connections (Connection Extensibility) from the Data Source dropdown menu. Click the Next button.

5. Enter the Driver Class and click the Next button.

6. Enter the Connection URL, a username/password combination (as required) and an optional Test Query (recommended). A test query need only return one row and one column. Click Next.

7. If you entered a Test Query, you can use the Test Connection (lower left) button to test whether your Connection is set up properly. Once you have verified the connection details, save the Connection.

8. You're all set! You can write and execute QueryPairs against the MongoDB data source of choice. The CData driver documentation discusses different driver configuration options, advanced settings,  supported SQL syntax and functions.