FAQ: The MongoDB Connector for BI
On this page
- Is there a cloud-hosted version of the BI Connector?
- How do I authenticate with the BI Connector?
- When upgrading MongoDB, what considerations should be taken for the BI Connector?
- Does the BI Connector store any data?
- How are queries processed?
- How do I skip data incompatible with my DRDL type definition?
- Can I use MongoDB views with BI Connector?
- Is there any syntax validation tool for DRDL?
- How does the BI Connector process dates?
- How do I use TLS/SSL with the BI Connector?
- Can I use the SQL
EXPLAINfunction?
Is there a cloud-hosted version of the BI Connector?
You can host the MongoDB Connector for Business Intelligence in MongoDB Atlas. To learn how to enable and connect to an Atlas-hosted BI Connector, see Connect via BI Connector for Atlas.
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.
When upgrading MongoDB, what considerations should be taken for the BI Connector?
Before upgrading your MongoDB deployment, set the
--mongo-versionCompatibility
option to the currently installed major
release series of MongoDB, for example
3.4. Once the upgrade is complete, restart mongosqld
without the
--mongo-versionCompatibility
option or set it to the newly updated major release series.
Note
If you launch the BI Connector with a DRDL schema file, you must update your schema file after the upgrade if you start using a newly introduced BSON Type.
MongoDB 3.4 introduces the decimal BSON type.
When you upgrade from MongoDB 3.2 to MongoDB 3.4 and your schema file
contains float64 values (which map to the double type in
MongoDB), you must update your DRDL file after you begin using the
new decimal type.
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 aggregation 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 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.
Can I use MongoDB views with BI Connector?
Yes. BI Connector treats a view like any other collection. For more information on using views with BI Connector, see Managing Schema with MongoDB Views.
Is there any syntax validation tool for DRDL?
DRDL files use the YAML syntax. Any YAML validator such as https://yaml-online-parser.appspot.com/ 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:
db.data.save({ 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:
db.data.save({ 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 SQL
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 \ --mongo-sslPEMKeyFile=/certs/mongodb_client.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 \ --sslPEMKeyFile=/certs/mongosql_server.pem
Can I use the SQL EXPLAIN function?
If you are using an SQL client which allows you to issue SQL
statements directly, such as the MySQL shell, you can precede any
query with EXPLAIN to get information about how the query will
be executed. EXPLAIN returns the complete aggregation
operation which BI Connector will send to MongoDB,
without running the query or returning any results. EXPLAIN is
useful if you want to know exactly how a specific SQL query translates
into MongoDB Query Language.