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, wheremongosqld
is running.Port
→ The port on which the database accepts connections (The default value for self-hosted MongoDB BI connector is3307
).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.