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.

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


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'


try:
   with open(ssh_private_key_path, 'r') as f:
       print("Private key file is accessible.")
except FileNotFoundError:
   raise FileNotFoundError(f"Private key file not found at {ssh_private_key_path}")
except PermissionError:
   raise PermissionError(f"Permission denied for private key file at {ssh_private_key_path}")


with SSHTunnelForwarder(
   (ssh_host, ssh_port),
   ssh_username=ssh_user,
   ssh_pkey=ssh_private_key_path,
   remote_bind_address=(mongo_host, mongo_port)
) as tunnel:
   client = MongoClient(f'mongodb://{mongo_user}:{mongo_password}@127.0.0.1:{tunnel.local_bind_port}/{mongo_db}?compressors=disabled&gssapiServiceName=mongodb')
   
  
   db = client[mongo_db]
   collection = db[collection_name]
   document = collection.find()
   df = pd.DataFrame(document)
   df = df.astype({"_id": str})
   client.close()
   pipeline.run(df, table_name="yourtablename")

• 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