Login
Start Trial
Request Demo

MongoDB JDBC (SQL) - Knowi Integration

MongoDB JDBC lets you query MongoDB using SQL instead of the MongoDB query language. Knowi connects through MongoDB's official Atlas SQL JDBC driver to an Atlas SQL endpoint, where MongoDB's Data Federation service translates your SQL into MongoDB operations.

This requires Atlas SQL to be enabled on your Atlas deployment. Connecting straight to a cluster address is a different setup with extra prerequisites, covered at the end of this page.

This is a separate datasource from MongoDB. Use whichever fits the job:

MongoDB MongoDB JDBC
Query language MongoDB query language and aggregation pipelines SQL
Access Read and write Read only
Best for Nested documents, aggregation pipelines, MongoDB-specific operators, write-back SQL-familiar analysts, reusing SQL written for other databases, BI-tool parity

Overview

  1. Connect to your MongoDB deployment using one of the following options:

    a. Through our UI to connect directly, if your MongoDB deployment is accessible from the cloud.

    b. Using our Cloud9Agent for datasources inside your network.

  2. Write SQL queries against your collections, then visualize and automate your reporting.

Connecting

Getting the connection string from Atlas

  1. In Atlas, open your deployment and click Connect.

  2. Choose Atlas SQL.

  3. Select the JDBC driver option.

  4. Copy the connection string shown.

The string Atlas gives you is prefixed with jdbc:mongodb://. Leave that prefix off when you paste into Knowi - it is added for you. Leave the credential placeholders out as well; the username and password go in the User and Password fields instead.

Setting up the datasource

  1. Log in to Knowi and select Queries from the left sidebar.

  2. Click on New Datasource + button and select MongoDB JDBC.

  3. Fill in the connection fields as described below:

Field Required Description
Datasource Name Yes A unique name to identify this datasource within Knowi.
Host Name Yes The Atlas SQL federated host, in the form atlas-sql-xxxxx.a.query.mongodb.net. Take it from the connection string on the Atlas SQL tab in Atlas. A full connection string can be pasted here instead of the bare host.
Port No Your database port number. The default port is 27017.
Database Yes Your database name. Required, the driver has no default database.
User No Username to connect with.
Password No Password to connect with.
Database Properties No Additional connection properties appended to the URL. Atlas SQL requires ssl=true&authSource=admin here unless you paste a full connection string that already contains them.
  1. Click Test Connection, then Save once the test succeeds.

Connecting to Atlas SQL

Atlas gives you a connection string that looks like this:

mongodb://atlas-sql-5cdb506ec56c98544d3013a0-rccxf.a.query.mongodb.net/mydb?ssl=true&authSource=admin

You can either paste that whole string into Host Name, or split it across the fields:

Field Value from the example above
Host Name atlas-sql-5cdb506ec56c98544d3013a0-rccxf.a.query.mongodb.net
Port Your port number
Database mydb
Database Properties ssl=true&authSource=admin
User / Password Your Atlas database user credentials

If you split the fields, the ssl=true&authSource=admin part has to go into Database Properties. Atlas SQL will not authenticate without it, and leaving it out is the most common cause of a failed connection test.

If you paste a full connection string

You can put a whole connection string in Host Name rather than splitting it across fields, as long as the jdbc:mongodb:// prefix is removed first. In that case the Port, Database and Database Properties fields are ignored, so the database name and the ssl and authSource parameters all have to be present in the string itself.

Splitting the values across the fields is the simpler option and is what the rest of this page assumes.

Connecting directly to a cluster

You may also have a standard cluster connection string, in the form mongodb+srv://yourcluster.mongodb.net/yourdb. This points at the same data as your Atlas SQL endpoint, but it bypasses Data Federation, and it will not work without extra setup on the MongoDB side.

Atlas SQL maintains the SQL schema the driver needs to describe your columns and types. A cluster address has no such schema, because nothing has sampled the collections to build one. Connecting and authenticating will succeed, then the query fails with:

Unable to get Fields: ResultSetMetaData json schema must be object with properties

To use a cluster address you have to generate schemas yourself by running MongoDB's sqlGenerateSchema command against each collection you want to query, and re-run it whenever a collection's shape changes materially. See MongoDB's Atlas SQL documentation for that command.

Use the Atlas SQL federated host instead wherever you can. It reaches the same data, and the schema is maintained for you.

Writing Queries

Queries are written in SQL against your collections. Each collection appears as a table:

select customer, status, count(*) as orders
from orders
where status = 'Complete'
group by customer, status

Knowi's date tokens and query parameters work as they do on other SQL datasources:

select * from orders where order_date >= $c9_today-30d

Cloud9QL can be applied on top of the results for further transformation, the same as any other SQL datasource.

Notes and Limitations

  • Read only. The driver does not support inserts, updates or deletes, and has no transaction support. Use the MongoDB datasource for write-back.

  • Databases map to catalogs. JDBC expects catalogs, schemas and tables; MongoDB has only databases and collections. The driver reports your database as the catalog and leaves the schema empty. This is normal and needs no configuration.

  • BSON values come back as text. Values with no SQL equivalent (embedded documents, arrays, ObjectIds, regular expressions, and columns holding mixed types) are returned as extended JSON strings. Use Cloud9QL to parse them further if needed. Binary fields are returned as binary data.

  • Schema depends on your setup. With Atlas SQL, the schema comes from your Data Federation configuration. With a direct cluster connection, the driver samples documents to infer one. If a field is missing from results, check the schema on the MongoDB side first.

  • MONGODB-OIDC authentication is not supported. That flow requires opening a browser on the host, which is not possible for a server-side connection. Use username and password, or an Atlas SQL connection string.

  • Query parameters are inlined. The driver does not accept bound parameters, so Knowi substitutes values directly into the SQL before running it. This is handled automatically and requires nothing from you.

  • For SQL syntax supported by Atlas SQL, see the MongoDB Atlas SQL documentation.