Integrate Locale with Self-hosted MongoDB BI-Connector

In this article, we'll walk you through everything you need to know to set up a mongosqld proxy on an AWS EC2 instance with a schema generated using the mongodrdl utility. This will allow you to query your MongoDB data using in SQL using Locale.

Step 1: Launch an AWS EC2 Instance

The first step is to launch an AWS EC2 instance. You can choose any instance type that meets your requirements.

We recommend using the Amazon Linux 2 x86 as it comes with some dependencies pre-installed

Note: Amazon Linux 2 x86 (2023) is not compatible, use older version of Amazon Linux 2 x86

Step 2: Install the MongoDB Enterprise Tools on the EC2 Instance

After launching the EC2 instance, you need to install the MongoDB BI-connector Tools on it. These tools include the mongodrdl and mongosqld utilities.

To install the tools, connect to the EC2 instance using SSH and run the following command:

wget <https://storage.googleapis.com/locale_datafiles/mongo_install.sh>

sudo sh mongo_install.sh

This will download and install the MongoDB BI-connector Tools on the EC2 instance. This script by locale will set up the entire environment automatically.

You can manually install MongoDB BI-Connector from the website in case of any difficulty running the script.

Step 3: Update the config file

Next, you need to fill in the details of your MongoDB connection in the config file that was created by running mongo_install.sh script.

A locale-mongodb.config file will be created in the current directory. If it's not created, create a file with the same name and add the following contents:

mongodb:
  net:
    uri: mongodb://<hostname>:<port>
    ssl:
      enabled: true
    auth:
      username: <username>
      password: <password>

systemLog:
  logAppend: true
  path: /home/<user>/mongo.log # Path for log file

security:
  enabled: true

net:
  bindIp: '0.0.0.0'
  port: 3307 # port for mongosqld proxy

schema:
  refreshIntervalSecs: 30
  stored:
    mode: "auto" # automatically create schema
    source: "mongosqld_schema" # database where schemas will be stored

# Service is used to keep running monosqld in background
processManagement:
  service:
    name: "mongosql"
    displayName: "MongoSQL Service"
    description: "MongoSQL accesses MongoDB data with SQL"

Replace <hostname>, <port>, <username>, and <password>with your specific MongoDB connection details in the uri, auth.username, and auth.password fields. Also, update the systemLog.path field with the appropriate path for your log file.

Create user with ReadAndWrite Access.

Step 4: Start the Mongosqld Proxy

Now it's time to start the mongosql proxy service. This tool provides a SQL interface to your MongoDB data.

To start the proxy, run the following command:

sudo systemctl start mongosql

This will start the mongosql background service which is installed by the install.sh script. Logs can be found in a file referenced by systemLog.path in config file.

Logs can be found in a file referenced by systemLog.path in config file.

<aside>

💡 mongosqld proxy will be running on port 3307

Step 5: Whitelist Locale’s IP in the VM

Whitelist Locale’s IP address 35.185.77.86 for inbound traffic in the virtual machine. The default port for mongsqld is 3307.

Step 6: Setting Up Connection

Integrating your MongoDB with BI connector to Locale is easy with just a few clicks.

If you are an admin user then head over to

Settings → Data Connections → New Data Connection

Select MongoDB from the existing connections and enter your database details.

Name the data source → A unique name to identify the data source. You could have multiple MongoDB databases/clusters connecting to Locale so you can use this name to uniquely differentiate between each of them within Locale’s platform.
Host Address → Public IP of the EC2/GCE machine, where mongosqld is running.
Port → The port on which the database accepts connections (The default value for self-hosted MongoDB BI connector is 3307).
Database Name → Enter the database name to which the connection needs to be made. As mentioned already, you can create new connections for different databases within the same cluster/new cluster.
Username → Database user.
Password → Password for the mentioned database user.
Use SSL → Check this option if you want the connection to be encrypted over SSL.