MongoDB BI Connector and SQL Access

MongoDB BI Connector and Atlas SQL Interface

Overview

Two distinct approaches for SQL-based BI tool integration with MongoDB:

  1. Legacy BI Connector (mongosqld): EOL September 2026. Self-hosted translation layer.
  2. Atlas SQL Interface (current): Managed service via Atlas Data Federation. Certified connectors for Tableau and Power BI.

Legacy BI Connector (mongosqld) — EOL September 2026

What It Is

A self-hosted binary (mongosqld) that acts as a MySQL-protocol SQL translation layer in front of MongoDB. BI tools connect via ODBC/JDBC as if connecting to a MySQL database.

Architecture

BI Tool (Tableau/Power BI) → ODBC/JDBC → mongosqld (localhost/server) → MongoDB cluster

DRDL Schema Files

mongosqld uses DRDL (Document Relational Definition Language) files to define the SQL schema mapping:

schema:
  - db: mydb
    tables:
    - table: orders
      collection: orders
      pipeline: []
      columns:
      - Name: _id
        MongoType: bson.ObjectId
        SqlName: _id
        SqlType: varchar
      - Name: customerId
        MongoType: string
        SqlName: customerId
        SqlType: varchar
      - Name: amount
        MongoType: float64
        SqlName: amount
        SqlType: float

Authentication

Auth Method mongosqld Config
SCRAM-SHA-256 --auth-username + --auth-password
LDAP --auth-mechanism PLAIN + --auth-source=$external
Kerberos (Windows) --auth-mechanism GSSAPI
X.509 --ssl-clientPEM + --ssl-CA

EOL Timeline

Atlas SQL Interface (Current Standard)

Architecture

Atlas SQL Interface uses Atlas Data Federation (FDI) as its query engine. No self-hosted binary needed.

BI Tool → JDBC/ODBC → Atlas SQL Interface → Atlas Data Federation → Atlas Cluster

Certified Connectors

Tableau:

Power BI:

Excel:

JDBC Connection

# Download JDBC driver from MongoDB Downloads Center
# Connection string format:
jdbc:mongodb://atlas-sql-<fdi-id>.mongodb.net:27017/<database>?ssl=true&authSource=admin

Connection setup in Tableau:

  1. Select “Other Databases (JDBC)” connector
  2. URL: jdbc:mongodb://atlas-sql-<fdi-id>.mongodb.net:27017/<database>?ssl=true
  3. Class name: com.mongodb.mongosql.MongoSQLDriver
  4. Username/password

ODBC Connection (Power BI / Excel)

  1. Download MongoDB ODBC Driver 2.0+
  2. In Windows ODBC Data Source Administrator: Add new DSN
  3. Server: atlas-sql-<fdi-id>.mongodb.net
  4. Port: 27017
  5. Database: <virtual database name>
  6. Authentication: username/password

Schema Management

// mongosh against the FDI endpoint
// Auto-infer schema from sample
db.runCommand({ sqlSetSchema: "collectionName" });

// Get current schema
db.runCommand({ sqlGetSchema: "collectionName" });

// Set explicit schema
db.runCommand({
  sqlSetSchema: "collectionName",
  schema: {
    version: 1,
    jsonSchema: {
      bsonType: "object",
      properties: {
        orderId: { bsonType: "string" },
        amount: { bsonType: "decimal" },
        createdAt: { bsonType: "date" }
      }
    }
  }
});

DirectQuery vs Import Mode (Power BI)

Mode How it works Use when
DirectQuery Every report view sends live query to Atlas Data changes frequently; large datasets
Import Copies data into Power BI dataset Report must be fast; data is relatively static

DirectQuery performance depends on Atlas query performance. Index optimization applies here too — slow MQL = slow DirectQuery.

Atlas Cluster Requirements

Cost

Atlas SQL Interface incurs Data Federation query processing costs:

Migrating from BI Connector to Atlas SQL Interface

Pre-migration checklist

  1. Verify cluster is on MongoDB 5.0+
  2. Enable Atlas Data Federation on the project
  3. Map DRDL schema columns to Atlas SQL Interface schema (jsonSchema format)
  4. Update BI tool connection: switch from mongosqld host to FDI hostname

Schema Migration

DRDL columns → jsonSchema properties:

# DRDL column
- Name: amount
  MongoType: float64
  SqlType: float
// Atlas SQL Interface
{ "amount": { "bsonType": "double" } }

Connection String Migration

# Before (mongosqld, MySQL protocol)
jdbc:mysql://localhost:3307/mydb?useSSL=false

# After (Atlas SQL Interface, MongoDB JDBC/ODBC)
jdbc:mongodb://atlas-sql-<fdi-id>.mongodb.net:27017/mydb?ssl=true

Anti-Patterns

References