Connecting to MongoDB
Applicable to Sisense on Linux and Microsoft Windows
Sisense 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 with Sisense, note that MongoDB is an unstructured database, and therefore tables may be flattened with additional tables being created for nested items.
Note: MongoDB for Linux is supported from Sisense v8.0.3.
To connect to MongoDB:
- Open Sisense. For a non-local installation, open Sisense on the hosted cloud environment.
- In the Data page, open an ElastiCube or create a new ElastiCube.
- In the Model Editor, click . The Add Data dialog box is displayed.
- Click MongoDB to open the MongoDB settings.
- In the MongoDB settings area, enter the following information:
Location: Enter IP address of the host where your MongoDB instance is running.
Port: Enter the number of the TCP port that the server uses to listen for client connections.
Database: Enter the name of the database that you want to access.
Note: The name of the database is case-sensitive.
Connect to Replica Set: If you are connecting to a replica set in your MongoDB implementation, select the Connect to Replica Set checkbox 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.
Authentication Mechanism: The authentication mechanism to use, No authentication, LDAP, or User and password.
Username: Enter your username to access your MongoDB.
Password: Enter your password to access your MongoDB.
Authentification Source: To use a database other than the admin database to check your credentials, enter the name of the database.
Enable SSL: Select to enable SSL.
Trust Store: Enter the full path of the TrustStore.
Trust Store Password: Enter the password property to your password for accessing the TrustStore.
Metadata Mechanism: This option specifies where the driver looks for the schema definition.
Database: The driver loads the schema definition from the MongoDB database.
Metadata File: The driver loads the schema definition from the JSON file specified in the Local File field or the LocalMetadataFile key
Sampling Method: This property specifies whether the driver starts sampling data from the first or last record when generating a temporary schema definition.
Forward: The driver samples data starting from the first record in the database, then samples the next record, and so on.
Backwards: The driver samples data starting from the last record in the database, then samples the preceding record, and so on.
Sampling Count: Enter the maximum number of records that the driver can sample to generate a temporary schema definition. When this property is set to 0, the driver samples every document in the database.
Sampling Interval: Enter the interval at which the driver samples records when scanning through the database to generate a temporary schema definition. For example, if you set this property to 2, then the driver samples every second record in the database.
Local Metadata File: Enter the full path of a local JSON file containing the schema definition that you want the driver to use when connecting to MongoDB.
Additional Parameters: Enter one or more JDBC connection parameters in the following format: <param1>=<value>&<param2>=<value>&<param3>=<value>
You must provide the JDBC parameters as ampersand-separated key-value pairs.
- Click Next. A list of tables in the database is displayed. All tables and views associated with the database will appear in a new window.
- From the Tables list, select the relevant table or view you want to work with. You can click next to the relevant table or click Preview to see a preview of the data inside it.
- (Optional) Click + to customize the data you want to import with SQL. See Importing Data with Custom Queries for more information.
- After you have selected all the relevant tables, click Done. The tables are added to your data model.
Connecting to MongoDB with a Schema Editor
If you have a large or complex MongoDB implementation, Sisense recommends connecting to your MongoDB via the Schema editor.
This procedure comprises two main steps:
- Creating a schema definition JSON file.
- Connecting the schema definition to Sisense.
Creating a Schema Definition JSON File
Use a Schema Editor to create a schema definition file in JSON format for your MongoDB.
To create the schema:
- Download the attached SchemaEditor.jar file from here.
- On your computer, in the folder containing the SchemaEditor.jar file, open a command prompt.
- Run the following command:
java -jar SchemaEditor.jar
- In the Schema Editor window, under “Create a new Schema Definition”, click Create New.
- In the Connection URL field, enter the connection string, and click Connect.
- Select the collections you want to use and click Sample.
- Add and remove columns as required.
- From the File menu, click Save to save the JSON file.
Connecting the Schema Definition JSON File to Sisense
Having created the schema definition JSON file, you can now connect it to Sisense. Doing so ensures that when you connect your MongoDB data source, the correct tables and columns are imported.
To connect the schema definition JSON file to Sisense:
- Open Sisense and, in your ElastiCube, from the Data page, click + Data.
- Add the MongoDB connector.
- In the Metadata Mechanism field, select Metadata file.
- In the Local Metadata File field, enter the path of the generated JSON file:
- In Sisense for Windows, this can be any location on your computer, such as
- In Sisense for Linux, enter the following path:
Connecting to MongoDB Atlas
MongoDB Atlas is a MongoDB cloud service. Sisense supports connections to MongoDB Atlas through the following methods:
- Generic JDBC connector
- MongoDB connector
Both methods are described below.
MongoDB Atlas has the following prerequisites:
- You must whitelist the server IP in Network Access
- You must know the name of the catalog you're accessing (we can only access one per connection)
- You must have a user and password for accessing the catalog
- You must know the Atlas nodes' hosts and ports, which must be open
For more information, see https://docs.atlas.mongodb.com/connect-to-cluster/.
MongoDB Atlas Example Settings
The following is an example of a three node cluster in MongoDB Atlas. This example is used to describe what information you need to provide in Sisense to establish a connection with MongoDB Atlas in Connecting to MongoDB Atlas with the MongoDB Connector.
This example has three nodes:
The username and password are: admin/********.
The database to be accessed in this example is sample_airbnb.
Using the example above, enter the connection details as follows in the MongoDB Connector settings:
Enter the hosts separated by comma. The port is taken from the Port field using it as a default port. Spaces are ignored.
cluster0-shard-00-00-l2zsn.mongodb.net, cluster0-shard-00-01-l2zsn.mongodb.net, cluster0-shard-00-02-l2zsn.mongodb.net
Enter the hosts with its port. The Port field is ignored. Spaces are ignored.
cluster0-shard-00-00-l2zsn.mongodb.net:27017, cluster0-shard-00-01-l2zsn.mongodb.net:27017, cluster0-shard-00-02-l2zsn.mongodb.net:27017
Port: Ignored if specified in the address, otherwise used as a default for every host without a port, 27017.
Authentication mechanism: Username and password
Enable SSL: Leave this checkbox empty as SSL is used for Atlas automatically.
After entering your credentials, you should then see your database.
- You can not connect to MongoDB with SSL when adding the certificate outside of connectors folder in Sisense Linux V8.0.3
- When connecting to MongoDB, your credentials are cached when SSL is enabled
- When creating a union from the Data Wizard while working with the MongoDB connector, no preview is displayed. You can import both tables individually and then perform a build. The next time you open the Data Wizard the preview is displayed as expected
Troubleshooting MongoDB Connections
- If you are having problems with timeouts when connecting to MongoDB, try reducing the sample size to 1
- Use the Schema Editor to selectively sample your database and collections of choice and use the generated schema map for your queries. The driver is intended to sample all the databases it has authentication rights to.