Install MongoDB BI Connector in Ubuntu

Introduction

MongoDB BI Connector is a connector that allows you to use MongoDB as a data source for SQL-based BI (Business Intelligence) and visualization tools. It allows you to connect your BI tools, such as Tableau, to a MongoDB database and use SQL to query and visualize the data stored in the 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 you to use the power of SQL to analyze and visualize data stored in a MongoDB database, without having to learn the MongoDB query language. In this post, we will learn to install the MongoDB BI connector in Ubuntu.

Prerequisites

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

Steps to install MongoDB BI Connector in Ubuntu

1. Download MongoDB BI Connector from the following Link

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

Choose the version of you OS

2. Extract the recently downloaded file

Command:

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

3. Go to the recently extracted directory

Command:

cd mongodb-bi-linux_*

4. Install MongoDB BI Connector

To install this connector use the following script:

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

5. Copy Data from MongoDB

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

6. Start mongosqld for testing

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

7. Install MySQL Client to work smoothly.

sudo apt install mysql-client-core-5.7

8. Start mongosqld for Prod with configuration

Save config file in /etc/mongosqld.conf
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"

9. 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

10. Enable mongosqld service and your service will auto start when the system reboot

sudo systemctl enable mongosqld.service

11. Start mongosqld service if you want

sudo systemctl start mongosqld.service

Conclusion

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