FAQ: The MongoDB Connector for BI

Is there a cloud-hosted version of the BI Connector?

A cloud-hosted MongoDB Connector for BI is now available in MongoDB Atlas. For more information on connecting to an Atlas-hosted BI Connector, see Connect via Atlas BI Connector.

How do I authenticate with the BI Connector?

Changed in version 2.0: Prior to version 2.0, the BI Connector stored its own separate set of credentials.

If you are using an older release of the MongoDB Connector for BI, you should upgrade to 2.0 by following the steps in Install BI Connector On Premises.

When connecting to a MongoDB deployment using authentication, you can authenticate as the users and roles configured in that deployment.

See Authentication for details on how to specify your authentication source and mechanism.

Does the BI Connector store any data?

The BI Connector instance only transforms SQL queries into MongoDB queries. It does not store any data itself.

When starting mongosqld, you provide it a DRDL file describing the schema of your data.

How are queries processed?

The BI Connector constructs an aggregation pipeline that allows the MongoDB server to execute many SQL expressions.

mongosqld always enables the aggregate allowDiskUse option.

The BI Connector cannot map some supported SQL constructs into an aggregation equivalent. In this case, the BI Connector will execute those constructs in memory.

How do I skip data incompatible with my DRDL type definition?

Using MongoDB 3.4 Views

MongoDB 3.4 introduces Read-Only Views that you can use to filter incompatible data.

For example, you can create a view in the test database that contains only documents containing a number in the grade field of a grades collection:

db.runCommand( { create: "numericGrades", viewOn: "grades", pipeline: [ { "$match": { "grade": { "$type": "number" } } } ] } )

You can then use mongodrdl to generate a schema from this view as you would a collection:

mongodrdl -d test -c numericGrades

Using a DRDL Filter

If documents in a collection contain different data types for a field, you may filter for a specific data type. To accomplish this, you can include a $match stage at the beginning of the pipeline in your DRDL table definition.

For example, to match only documents containing a number in the grade field, use the following pipeline stage:

"$match": { "grade": { "$type": "number" } }

If you are unwinding an array field that contains different data types, then to filter the array for a specific data type, put the $match stage after the $unwind.

Is there any syntax validation tool for DRDL?

DRDL files use the YAML syntax. Any YAML validator such as can help you check your DRDL files.

How does the BI Connector process dates?

The BI Connector will correctly process BSON date data by mapping it to the SQL datetime type. For example:{ date: new Date() })

If you store date data as a string, the BI Connector treats it as a string rather than as a date. For example, BI Connector treats the following as a string:{ date: '32-FEB-2015' })

How do I use TLS/SSL with the BI Connector?

The connection between mongosqld and your MongoDB deployment has TLS/SSL configured separately from the connection between your MySQL client and mongosqld.

Connecting mongosqld to MongoDB

If the MongoDB instance you are connecting to uses TLS/SSL, provide the --mongo-ssl option to mongosqld.

For example:

mongosqld --schema=schema.drdl --mongo-ssl

To specify a TLS/SSL CA root certificate, use the --mongo-sslCAFile option. To specify a client certificate, use the --mongo-sslPEMKeyFile option. For example:

mongosqld --schema=schema.drdl \
          --mongo-ssl \
          --mongo-sslCAFile=/certs/ca.pem \

Connecting a client to mongosqld

To specify a TLS/SSL CA root certificate, use the --sslCAFile option. To specify a client certificate, use the --sslPEMKeyFile option. For example:

mongosqld --schema=schema.drdl \
          --sslCAFile=/certs/ca.pem \