Connect MongoDB with SSH tunneling (passwordless) using Bold ETL

This article provides guidance on retrieving data from a MongoDB database using SSH tunneling (password) utilizing Bold ETL. This process can be accomplished through the execution of a Python script.

Prerequisite:
PyMongo and sshtunnel is required. In upcoming releases of Bold BI, it will be included as default.

Here is the sample Python code for fetching selected fields from MongoDB.

from sshtunnel import SSHTunnelForwarder
from pymongo import MongoClient
import pandas as pd

# SSH and MongoDB configuration
ssh_host = 'SSHHOST'  # SSH host (MongoDB server IP)
ssh_port = 22  # Default SSH port
ssh_username = 'SSHUSER'  # SSH username
ssh_private_key = "PRIVATEKEYPATH"  # Path to your private SSH key file

# MongoDB configuration
mongo_host = 'localhost'  # MongoDB host (localhost when using SSH tunnel)
mongo_port = 27017  # MongoDB default port
mongo_db = 'DBNAME'  # MongoDB database name
mongo_user = 'USERNAME'
mongo_password ='PASSWORD'
collection_name = 'users'


# Set up the SSH tunnel using SSHTunnelForwarder
with SSHTunnelForwarder(
   (ssh_host, ssh_port),
   ssh_username=ssh_username,
   ssh_pkey=ssh_private_key,
   remote_bind_address=(ssh_host, mongo_port),  # MongoDB server address and port
   local_bind_address=('localhost', mongo_port)  # Local forwarding address and port
) as tunnel:
   print("SSH Tunnel established...")

   # Connect to MongoDB through the tunnel
   client = MongoClient(f'mongodb://{mongo_user}:{mongo_password}@127.0.0.1:{tunnel.local_bind_port}/{mongo_db}')
   #client = MongoClient(f'mongodb://127.0.0.1:{tunnel.local_bind_port}/{mongo_db}')
   db = client[mongo_db]
   collection = db[collection_name]
   document = collection.find()
   df = pd.DataFrame(document)
   df = df.astype({"_id": str})
   client.close()
   print(df.head())
   pipeline.run(df, table_name="yourtablename")

• If you do not have a MongoDB username and server, please replace line number 33 as follows:

client = MongoClient(f'mongodb://127.0.0.1:{tunnel.local_bind_port}/{mongo_db}') 

• Replace your credentials in below properties.

ssh_host = 'sshhost'
ssh_port = 22
ssh_user = 'sshuser'
ssh_private_key_path = 'privatekey'
mongo_host = 'mongodbserver'
mongo_port = 27017
mongo_user = 'user'
mongo_password = 'password'
mongo_db = 'db'
collection_name = 'collection_name' 

• yourtablename - Replace yourtablename with the desired table name in your destination database.

Please follow the instructions in order to run the Python script within the Bold ETL platform.
Python DataFrame into Bold BI