Connecting to MongoDB

The ElastiCube Manager enables easy and quick access to databases, tables and views contained within MongoDB databases.

Sisense provides a MongoDB connector for the ElastiCube.

Note: Before connecting to MongoDB database withSisense, please note that MongoDB is an unstructured database, and therefore tables may be flattened with additional tables being created for nested items.

To connect and define MongoDB, follow these steps:

Upgrading to MongoDB v2.0

The latest version of MongoDB provides enhanced security and improved handling for complex MongoDB models.

The latest version of MongoDB makes significant breaking changes, if you use a version of MongoDB prior to version 2.0, and you are satisfied with your service, you can continue without upgrading.

When to Upgrade?

In certain circumstances it is recommended or necessary that you upgrade your driver for MongoDB version 2.0, for example:

  1. You are new to Sisense and MongoDB
  2. You want to connect with the Sisense Native REST Connector
  3. You experience security-related issues in MongoDB v1.0
  4. You have a really complex MongoDB model that v1.0 can’t handle

Before Upgrading

When upgrading to MongoDB v2.0, your current DSN names configured locally may be deleted. As a workaround, you can export the ODBC registry entry and then import after completing the upgrade.

To migrate your ODBC Data Source registry file:

  1. In Windows, open the Run box with by pressing the Windows key + r.
  2. In the Run line, enter regedit.
  3. Click OK.
  4. Navigate to the ODBC Data Source registry entry at the following location:
  5. HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

  6. Right click the entry and select Export.
  7. Save the registry file on your machine.
  8. After installing MongoDB v2, click the exported registry file. The Register Editor warning message is displayed.
  9. Click Yes to import the entry.

After Upgrading

After upgrading to MongoDB v2.0, note the following:

  1. The new ODBC schema creation is different than v1.0. MongoDB v2.0 will not create virtual tables (_vt_ tables) in the ElastiCube Manager. As a result, your current ElastiCube schema will not match schemas created before upgrading. Create a new schema using the new v2.0 DSN.
  2. The MongoDB v2.0 driver saves a JSON format of the extracted schema, as opposed to the v1.0 driver which uses XML.
  3. The MongoDB v2.0 driver stores the schema extracted in MongoDB by default. This allows multiple users to work with the same extracted schema from different servers without extracting a new schema individually each time. However, any changes to the schema affects the schema for everyone else.
  4. Storing the schema in the MongoDB means your DB is not ReadOnly. Administrators should be aware of this and protect their database with authentication and access control, or communicate this store action and its effects to all users.

Downloading and Connecting to the MongoDB ODBC Driver

To connect to MongoDB, complete the following procedures:

  1. Download and install the MongoDB ODBC Driver.
  2. Install the MongoDB driver. Note: If you have previously installed an earlier version of the MongoDB driver, you need to uninstall it first, and then install this version.
  3. In the ElastiCube Manager, click Add Data, and then Generic ODBC Driver.
  4. Select DSN. If a DSN file has already been created, look for it in the dropdown list, and click Test Connection.
  5. If you need to add a DSN, see the next section. If your connection has been set up, you can click OK and review the tables generated by the MongoDB ODBC Driver.

Adding a DSN

If a DSN has not yet been created, you will need to add it as follows:

  1. In Windows, search for and open the application, ODBC Data Sources.
  2. Under the System DSN tab, click Add DSN.
  3. Select the previously installed Sisense MongoDB ODBC Driver, and click Next, and then Finish.
  4. Enter the following information:
    FieldDescription
    Data Source NameEnter your name for the DSN that is to be displayed in the DSN dropdown list.
    Description(Optional) Enter your description of the DSN.
    ServerEnter the name or IP address of the host where your MongoDB instance is running.
    PortEnter the number of the TCP port that the server uses to listen for client connections.
    DatabaseEnter the name of the database that you want to access.Note: The name of the database is case sensitive.
    Connect to Replica SetIf you are connecting to a replica set in your MongoDB implementation, select the Connect to Replica Set check box and then do the following:Replica Set Name: Enter the name of the replica set (this is a required field).Secondary Servers: Enter a comma-separated list of the servers in the replica set. You can indicate the TCP port that each server is using to listen for client connections by appending a colon (:) and the port number to the server name or IP address.Read Preference: Select the appropriate option to specify how the driver routes read operations to the members of a replica set.
  5. (Optional) If the database that you are connecting to requires authentication, then use the options in the Authentication area to configure authentication as needed. For more information, see Authentication Options.
  6. (Optional) To configure advanced driver options, click Advanced Options. For more information, see Advanced Options.
  7. (Optional) To configure logging behavior for the driver, click Logging Options. For more information, see Logging Options.
  8. Click Test. A pop window is displayed that indicates the connection was successful.
  9. Click OK.
  10. In the ODBC Connectivity Properties window, click Test Connection.
  11. Click OK to save and close the ODBC Data Source Administrator.

DSN Schema Definition

  1. Open the MongoDB ODBC Driver from the ODBC Data Source Administrator.
  2. Click Schema Definition to review the schema and make changes if needed:
  3. When done, click Test Connection, and OK.

Selecting Tables in the ElastiCube Manager

After setting up the DSN, you can preview and customize the query (similar to other ODBC connections) of the imported MongoDB table.

In the ElastiCube ManagerYou can view the SQL syntax in the Query Preview section. Click Edit to customize it.

Select the tables created by the Sisense MongoDB ODBC Driver (tables including MAINTABLE_vt_FIELD), as opposed to the table that includes all the raw data (with suffix main).

Connecting to Sisense MongoDB ODBC Driver using a Connection String

If you have a driver already defined, then you can connect to Sisense MongoDB ODBC Driver using strings (without using a DSN).

  1. Select Connection String (DSN-Less).
  2. Type in the connection details as strings, as follows:
  3. Example with minimum required attributes

    Driver=Sisense MongoDB ODBC Driver;

    Host=localhost;

    Port=27017;

    Database=aff;

    Example with with advanced options:

    Driver=Sisense MongoDB ODBC Driver

    Host=192.168.100.100;

    Port=27017;

    Database=MyDatabase;

    UID=MyUsername;

    PWD=MyPassword;

    RowsFetchedPerBlock=4096;

    DefaultStringColumnLength=255;

    DefaultContainerColumnLength=511;

    UseSqlWVarchar=0;

    CacheMetadata=1; VirtualTableDetection=1

  4. When done, click Test Connection, and OK.

Authentication Options

Some MongoDB databases require authentication. You can configure the MongoDB ODBC Driver to provide your credentials and authenticate the connection to the database using one of the following methods:

Using SCRAM-SHA-1

You can configure the driver to use the SCRAM-SHA-1 protocol to authenticate the connection. SCRAM-SHA-1 is the default authentication protocol used by MongoDB.

To configure SCRAM-SHA-1 authentication:

  1. To access authentication options, open the ODBC Data Source Administrator where you created the DSN, select the DSN, and then click Configure.
  2. In the Mechanism list, select MongoDB User Name and Password.
  3. To use a database other than the admin database to check your credentials, type the name of the database in the Authentication Source field.
  4. In the Username field, type an appropriate user name for accessing the MongoDB database.
  5. In the Password field, type the password corresponding to the user name you typed above.
  6. Encrypt your credentials by doing one of the following:
  7. To save your settings and close the dialog box, click OK.

Using Kerberos

You can configure the driver to use the Kerberos protocol to authenticate the connection.

Kerberos must be installed and configured before you can use this authentication mechanism. For information about how to install and configure Kerberos, see the MIT Kerberos Documentation: http://web.mit.edu/kerberos/krb5-latest/doc/.

To configure Kerberos authentication:

  1. To access authentication options, open the ODBC Data Source Administrator where you created the DSN, select the DSN, and then click Configure.
  2. In the Mechanism list, select Kerberos.
  3. In the Service Name field, type the service name of the MongoDB server.
  4. To save your settings and close the dialog box, click OK.

Using LDAP

You can configure the driver to use the LDAP protocol to authenticate the connection.

To configure LDAP authentication:

  1. To access authentication options, open the ODBC Data Source Administrator where you created the DSN, select the DSN, and then click Configure.
  2. In the Mechanism list, select LDAP.
  3. In the Username field, type an appropriate user name for accessing the MongoDB database.
  4. In the Password field, type the password corresponding to the user name you typed above.
  5. Encrypt your credentials by doing one of the following:
  6. To save your settings and close the dialog box, click OK.

DSN Advanced Options

You can configure advanced options to modify the behavior of the driver.

To configure advanced options:

  1. To access advanced options, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click Advanced Options.
  2. To retrieve data using double-buffering instead of single-buffering, select the Enable Double-Buffering check box. You can configure the buffer size using the Documents to fetch per block field.
  3. In the Documents to fetch per block field, type the maximum number of documents that a query returns at a time. This setting also determines the buffer size used when double-buffering is enabled.
  4. To return MongoDB String data as SQL_WVARCHAR instead of SQL_VARCHAR, select the Expose Strings as SQL_WVARCHAR check box.
  5. In the String Column Size field, type the maximum data length for String columns.
  6. To return MongoDB Binary data as SQL_LONGVARBINARY instead of SQL_VARBINARY, select the Expose Binary as SQL_LONGVARBINARY check box.
  7. In the Binary Column Size field, type the maximum data length for Binary columns.
  8. To configure the driver to optimize joins between virtual tables and pass filtering and aggregation optimizations to the MongoDB database for handling, select the Enable Passdown check box.
  9. Use the options in the Metadata area to specify the schema definition to use when connecting to the database:
  10. Use the options in the Sampling area to configure how the driver samples data to generate temporary schema definitions:
  11. In the Step Size field, type the interval at which the driver samples a record when scanning through the database. For example, if you set this option to 2, then the driver samples every second record in the database starting from the first record.
  12. To configure write-back behavior in the driver, click Writeback Options.
  13. To save your settings and close the Advanced Options dialog box, click OK.
  14. To close the MongoDB ODBC Driver DSN Setup dialog box, click OK.

Logging Options

To help troubleshoot issues, you can enable logging. In addition to functionality provided in the MongoDB ODBC Driver, the ODBC Data Source Administrator provides tracing functionality.

To enable driver logging:

  1. To access logging options, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click Logging Options.
  2. From the Log Level list, select the logging level corresponding to the amount of information that you want to include in log files:
  3. Field Description
    Log Level

    Select the Log Level. There are seven possible options:

    LOG_OFF: Disables all logging.

    LOG_FATAL: Logs very severe error events that lead the driver to abort.

    LOG_ERROR: Logs error events that might still allow the driver to continue running.

    LOG_WARNING:Logs potentially harmful situations.

    LOG_INFO: Logs general information that describes the progress of the driver.

    LOG_DEBUG: Logs detailed information that is useful for debugging the driver.

    LOG_TRACE: Logs more detailed information than the DEBUG level.

    Log Path

    Enter the full path to the folder where you want to save log files.

    OR

    Click Browse and select the folder where you want to save log files.

    Log Rotation

    Enter the maximum number of log files to keep in the Max Number Files field.

    Note: After the maximum number of log files is reached, each time an additional file is created, the driver deletes the oldest log file.

    Enter the maximum size of each log file in megabytes (MB) in the Max File Size field.

    Note: After the maximum file size is reached, the driver creates a new file and continues logging.

  4. In the Log Path field, specify the full path to the folder where you want to save log files. You can type the path into the field, or click Browse and then browse to select the folder.
  5. In the Max Number Files field, type the maximum number of log files to keep.
  6. In the Max File Size field, type the maximum size of each log file in megabytes (MB).
  7. Click OK.
  8. Restart your ODBC application to make sure that the new settings take effect.

The MongoDB ODBC Driver produces a log file named mongodbodbc_driver.log at the location that you specify in the Log Path field.

SSL Options

If you are connecting to a MongoDB server that has Secure Sockets Layer (SSL) enabled, then you can configure the driver to connect to an SSL-enabled socket. When connecting to a server over SSL, the driver supports identity verification between the client and the server.

Configuring an SSL Connection without Identity Verification

You can configure a connection that uses SSL but does not verify the identity of the client or the server.

To configure an SSL connection without verification:

  1. To access the SSL options for a DSN, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click SSL Options.
  2. Select the Enable SSL check box.
  3. Select the Allow Self-Signed Certificates check box.
  4. To save your settings and close the dialog box, click OK.

Configuring One-way SSL Verification

You can configure one-way verification so that the client verifies the identity of the MongoDB server.

To configure one-way SSL verification:

  1. To access the SSL options for a DSN, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click SSL Options.
  2. Select the Enable SSL check box.
  3. Choose one:
  4. In the Certificate Revocation List File field, specify the full path of the PEM file containing the list of revoked certificates.
  5. To save your settings and close the dialog box, click OK.

Configuring Two-way SSL Verification

You can configure two-way SSL verification so that the client and the MongoDB server verify each other.

To configure two-way SSL verification:

  1. To access the SSL options for a DSN, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click SSL Options.
  2. Select the Enable SSL check box.
  3. In the PEM Key File field, specify the full path of the PEM file containing the certificate for verifying the client.
  4. If the client certificate is protected with a password, type the password in the PEM Key Password field.
  5. Choose one:
  6. In the Certificate Revocation List File field, specify the full path of the PEM file containing the list of revoked certificates.
  7. To save your settings and close the dialog box, click OK.

Versions

Version Release Date Improvements
6.6.1.2 May 19, 2017 Support added for MongoDB 3.4For more information, see the completeMongoDB Release Notes