Install MongoDB BI Connector on Ubuntu

MongoDB BI Connector is a connector that allows us to use MongoDB as a data source for SQL-based BI (Business Intelligence) and visualization tools. It allows us to connect our BI tools, such as Tableau, to a MongoDB database and use SQL to query and visualize the data stored in the database. In this post, we will learn to install MongoDB BI connector on Ubuntu.

How BI connector helps to connect database?

The BI Connector translates SQL queries into MongoDB queries and returns the results in a format that can be easily consumed by the BI tool. This enables us to use the power of SQL to analyze and visualize data stored in a MongoDB database, without having to learn the MongoDB query language.

Prerequisites

1. MongoDB installed and Running
2. User must have role read/write if auth is enabled in MongoDB
3. Install MySQL ODBC Driver. (It Requires) 

Steps to install MongoDB BI Connector on Ubuntu

Download MongoDB BI Connector from the following Link

https://www.mongodb.com/download-center/bi-connector

Choose the version of your OS

Extract the recently downloaded file

Command:

tar -xvzf mongodb-bi-linux_<your version of os and other info>.tgz

Go to the recently extracted directory

Command:

cd mongodb-bi-linux_*

Install MongoDB BI Connector

To install this connector use the following script:

sudo install -m755 bin/mongo* /usr/local/bin/

Copy Data from MongoDB

mongodrdl --host mongo.myhost.com -d mydatabase -o /opdirectory/schema-<mydatabase>.drdl

Start mongosqld for testing

mongosqld --schema /opdirectory/schema.drdl --mongo-uri mongo.myhost.com

Install MySQL Client to work smoothly.

sudo apt install mysql-client-core-5.7

Start mongosqld for Prod with configuration

1. Save config file in /etc/mongosqld.conf
2. Config file sample

systemLog:
  quiet: false
  logAppend: false
  path: "/var/log/mongosqld/mongosqld.log"
  verbosity: 1
  logRotate: "rename" # "rename"|"reopen"
security:
  enabled: true
#  datasource: "datasource_name"
mongodb:
  net:
    uri: "mongo.myhost.com:27017"
    auth:
      username: "mongodb_username"
      password: "mongodb_password"
net:
  bindIp: 0.0.0.0
  port: 3307
#  ssl:
#    mode: "allowSSL"
#    PEMKeyFile: "/vagrant/certificates/mongosqld-server.pem"
#    CAFile: "/vagrant/certificates/ca.crt"
schema:
  path: "/etc/mongosqld/schema/"
processManagement:
  service:
    name: mongosqld
    displayName: mongosqld
    description: "BI Connector SQL proxy server"

Install mongosqld as a service with the above configuration

sudo mongosqld install --config /etc/mongosqld.conf

Note: You may not be able to install or start the service if the directory for log is not created already. We have to create it manually. Like: sudo mkdir logdir

Enable mongosqld service and our service will auto start when the system reboot

sudo systemctl enable mongosqld.service

Start mongosqld service

sudo systemctl start mongosqld.service

Conclusion

By following the above steps we are successful to install MongoDB BI connector on Ubuntu server.