As MongoDB is a NoSQL database, it does not ship with a JDBC driver. However it is still possible to connect to and report off of your data stored in Mongo, by using the MongoDB Connector for BI in conjunction with the standard MySQL driver.
1. The first thing we need to do is to ensure that the MongoDB Connector for BI has been installed with our Mongo Instance.
- Connector for BI: https://www.mongodb.com/download-center?_ga=2.2475...
2. Next we need to start the "mongodbsql" program. This will act as a simple server and will translate incoming SQL queries to Mongo's query syntax.
- Navigate to the "Connector for BI" bin folder in the mongo primary directory. Eg C:\Program Files\MongoDB\Connector for BI\2.5\bin
- Open a command prompt and run the application: .\mongosqld.exe
- Note: long-term you will want to set this up as a service
3. Once the application is started you should see something along the lines of:
2019-02-07T11:23:36.957-0700 I NETWORK [initandlisten] waiting for connections at 127.0.0.1:3307
4. Within Yellowfin, we can now connect to this as if it were a standard MySQL server. As the MongoDB connection does not support the "rollback" parameter used frequently by Yellowfin it is necessary to use the "Generic JDBC" connection and specify, useLocalTransactionState=true in the connection string:
jdbc:mysql://localhost:3307/test?useLocalTransactionState=true
Note that in the following example I am connecting to an unsecure (no user creds) Mongo instance on the localhost (port 3307)
Using Mongo Authentication
If your Mongo server has authentication enabled (typically specified with the --auth parameter on server startup), then we will need to pass authentication parameters through the BI connector.
The first step is to ensure that your Yellowfin is using a recent version of the mysql jdbc driver (> 5.1.47). If you are using a Yellowfin version newer than 8.0.1, this will automatically be included. If not we will need to update the driver in Yellowfin's lib folder.
- Download the latest v5 driver: https://dev.mysql.com/downloads/connector/j/
- Extract the driver jar (mysql-connector-java-5.1.47.jar) to the Yellowfin lib folder: <yourYFInstall>/appserver/webapps/ROOT/WEB-INF/lib/ (replacing the 5.1.25 jar)
- Download the mongo JDBC authentication jar and place this into the lib folder as well: https://docs.mongodb.com/bi-connector/current/refe...
- Restart the Yellowfin server.
Now, when we construct the JDBC connection string we will need to include an additional parameter (authenticationPlugins) for this connector:
jdbc:mysql://127.0.0.1:3307/test?useSSL=false&useLocalTransactionState=true &authenticationPlugins=org.mongodb.mongosql.auth.plugin.MongoSqlAuthenticationPlugin
Note that your user will need to have the ability to query the data structure as well as individual databases.