Connect documentdb with google looker studio for analyzing data

If you’re already using Amazon DocumentDB to store your data, you’re on the right track. But raw data on its own doesn’t offer much value unless you can actually work with it. That’s where Google Looker Studio comes in. By connecting DocumentDB with Looker Studio, you can instantly turn your live data into interactive dashboards that are easy to understand and even easier to share.

There’s no need to write complex code or wait on someone from engineering. Just log in, connect your data, and start visualizing what matters. Whether it’s customer activity, sales performance or system health, you’ll be able to track it all in real time and make decisions that move your business forward.

Why Connect DocumentDB to Looker Studio?

Your data holds valuable insights but only if you can actually see them. DocumentDB stores a lot of information, but without the right tools, it stays hidden in collections and JSON objects. Looker Studio helps unlock that value by letting you build live dashboards that update as your data changes.

With this setup, you can transform complex data into clear visuals that anyone on your team can use. Whether it’s marketing tracking campaign performance, sales looking at daily revenue or operations monitoring supply chain metrics, Looker makes it accessible.

You won’t have to rely on exports or manually run reports. Everything happens in real time, giving your team the freedom to explore, monitor and act on data without delays or bottlenecks.

What is MongoDB BI Connector?

MongoDB BI Connector is a tool that acts as a bridge between MongoDB-style databases (like Amazon DocumentDB) and applications that use SQL to access data. Since DocumentDB stores data in a flexible, JSON-like format, many analytics and reporting tools cannot connect directly. The BI Connector translates this data into a MySQL-compatible format so SQL-based tools like Google Looker Studio can query it easily.

Why Use MongoDB BI Connector?

Google Looker Studio does not have a native connector for DocumentDB or MongoDB. To connect, you use Looker’s MySQL connector, which talks to the MongoDB BI Connector. The BI Connector then translates those SQL queries into MongoDB’s query language and fetches data from DocumentDB.
This setup allows real-time data access without needing to export or duplicate your database.
Your team can use familiar BI tools like Looker Studio without learning MongoDB’s query language.
You analyze data directly where it lives, avoiding delays or extra storage costs.

In short, the MongoDB BI Connector acts as the essential link that enables real-time, SQL-based analytics on DocumentDB through tools like Google Looker Studio.

What We Needed to Do

To bridge DocumentDB and Looker Studio, we take these steps:

Launch and configure an EC2 instance to host our connector.
Download and install the MongoDB BI Connector on this instance.
Configure the BI Connector to access live DocumentDB data.
Expose the connector as a MySQL-compatible endpoint so Looker Studio can connect.
Set up security and network permissions for safe and smooth communication.

Set Up the EC2 Instance

We’ll use Ubuntu 22.04 as our server’s operating system. This is where we installed the BI Connector that acts as a bridge between DocumentDB and Looker Studio.

Install MongoDB BI Connector on Ubuntu 22

Download the necessary MongoDB BI Connector files

				
					wget https://info-mongodb-com.s3.amazonaws.com/mongodb-bi/v2/mongodb-bi-linux-x86_64-ubuntu2204-v2.14.14.tgz.sig
wget https://info-mongodb-com.s3.amazonaws.com/mongodb-bi/v2/mongodb-bi-linux-x86_64-ubuntu2204-v2.14.14.tgz

				
					curl -LO https://pgp.mongodb.com/bi-connector.asc
gpg --import bi-connector.asc
gpg --verify mongodb-bi-linux-x86_64-ubuntu2204-v2.14.14.tgz.sig mongodb-bi-linux-x86_64-ubuntu2204-v2.14.14.tgz

				
					tar -xvzf mongodb-bi-linux-x86_64-ubuntu2204-v2.14.14.tgz
cd mongodb-bi-linux-x86_64-ubuntu2204-v2.14.14/bin
sudo install -m755 mongosqld /usr/local/bin/

				
					mongosqld --version

Set up SSL Certificates for Secure Connection

Switch to root user and create directory for storing ssl certificate files

				
					sudo su
mkdir /opt/certs
cd /opt/certs

				
					openssl genrsa -out /opt/certs/mdbprivate.key -aes256 -passout pass:<password>
openssl req -x509 -new -key /opt/certs/mdbprivate.key -days 10000 -out /opt/certs/mdbca.crt -passin pass:<passowrd>

Create a key and Certificate Signing Request (CSR) for the BI Connector:

				
					openssl req -new -nodes -newkey rsa:2048 -keyout /opt/certs/bi.key -out /opt/certs/bi.csr

				
					openssl x509 -CA /opt/certs/mdbca.crt -CAkey /opt/certs/mdbprivate.key -CAcreateserial -req -days 10000 -in /opt/certs/bi.csr -out /opt/certs/bi.crt -passin pass:<passowrd>

				
					cat /opt/certs/bi.key /opt/certs/bi.crt > /opt/certs/bi.pem

Configure the BI Connector

Create a log directory for monitoring

				
					sudo mkdir -pv /var/log/mongosqld
sudo chown ubuntu:ubuntu /var/log/mongosqld

				
					sudo mkdir -pv /etc/mongosqld
sudo vim /etc/mongosqld/mongosqld.conf

add following content into mongosqld.conf file

				
					systemLog:
  logAppend: false
  path: "/var/log/mongosqld/mongosqld.log"
  verbosity: 2

security:
  enabled: true

mongodb:
  net:
    uri: <documentdb host url>
    auth:
      username: <user>
      password: <password>

net:
  bindIp: 0.0.0.0
  port: 3307
  unixDomainSocket:
    enabled: false
  ssl:
    mode: 'allowSSL'
    PEMKeyFile: '/opt/certs/bi.pem'
    CAFile: '/opt/certs/mdbca.crt'

schema:
  sample:
    namespaces: "*.*"

processManagement:
  service:
    name: mongosqld
    displayName: mongosqld
    description: "BI Connector SQL proxy server"

Start the connector and enable as a service

				
					mongosqld --config /etc/mongosqld/mongosqld.conf
sudo systemctl restart mongosqld
sudo systemctl daemon-reload
sudo systemctl enable mongosqld
sudo systemctl status mongosqld

Check the connector is listening on port 3307

				
					sudo lsof -iTCP:3307 -sTCP:LISTEN

Verify logs for connection issues

				
					sudo tail -f /var/log/mongosqld/mongosqld.log

Test the Connection with DBeaver

Before implementing to Looker Studio, it’s good practice to test using DBeaver.

Open DBeaver and create a new MySQL connection
Fill in the hostname, port 3307, and your DocumentDB credentials
Under SSL tab, select mdbca.crt as the CA certificate
Click Test Connection — it should succeed

Connect to Looker Studio

Final step, let’s integrate your DocumentDB data into Google Looker Studio..

Open Looker Studio and add a new data source
Choose MySQL as the connector
Fill in your EC2 domain or IP, port 3307, and credentials
Enable SSL and upload the mdbca.crt file
Authenticate and you’re in

You’ll now be able to see your DocumentDB collections (tables) and fields. Drag and drop to create dynamic reports and dashboards in minutes.

Final Thoughts

Connecting Amazon DocumentDB to Google Looker Studio might feel a bit technical at first, but once it’s in place, the value it delivers is huge. By setting up the MongoDB BI Connector, you’re creating a reliable bridge that turns your live, unstructured data into meaningful visual insights — accessible to your entire team in real time.

It not only eliminates the need for manual data exports but also empowers non-technical users to explore data through interactive dashboards. Your operations, marketing, finance and product teams can all work from the same live source of truth — without writing a single line of code.