MongoDB

Find out how to connect MongoDB to your data warehouse effortlessly.

Before you begin

Make sure you have access to your MongoDB connection string. It can be in one of the following formats:

Standard format:

mongodb://username:password@localhost:27017/myDatabase

SRV format (used with MongoDB Atlas):

mongodb+srv://username:[e-mail]/myDatabase

How to Fill Out the Connection Form in Erathos

Use the details from your connection string to complete the form as follows:

Field	Value
DB	myDatabase
Host	cluster0.mongodb.net or localhost
Port	27017
User	username
Password	password

When using CDC, the following values may be used in cdc_snapshot_mode:

• initial: performs an initial snapshot, after it completes, streaming following changes from the oplog.

• no_data: does not perform any snapshot, only streams changes that are currently available in the oplog.

Don't forget to click on Save and close to advance to the next steps of the integration.

With this connection created you can seemlessly move your MongoDB data to your BigQuery, Redshift or Postgres data-warehouse.

CDC setup

CDC reads changes from MongoDB change streams. In order to enable CDC in your MongoDB connection you need to confirm the following settings in your database.

Replica set or sharded cluster

Change streams are only available on replica sets and sharded clusters. A standalone MongoDB server does not support CDC. MongoDB Atlas clusters are always replica sets, so no change is needed there.

// On a replica set, returns the replica set name; on a standalone server, returns nothing
db.hello().setName

If your self-hosted server is a standalone, it can be converted into a single-member replica set by starting mongod with the --replSet option and running rs.initiate().

User permissions

The user used for the connection must be able to read the captured databases, list databases and open change streams on them. The built-in readAnyDatabase role covers all of these:

db.getSiblingDB("admin").createUser({
    user: "erathos",
    pwd: "<strong-password>",
    roles: ["readAnyDatabase"]
});

// Or grant it to an existing user
db.getSiblingDB("admin").grantRolesToUser("your_existing_user", ["readAnyDatabase"]);

On MongoDB Atlas, the same permission is available as the Only read any database built-in role under Database Access.

Oplog retention

Change streams read from the replica set's oplog, which has a limited size and only keeps changes for a certain amount of time (the oplog window). The following command shows the current window:

// "log length start to end" is the oplog window
rs.printReplicationInfo()

// If necessary, keep at least 24 hours of changes (MongoDB 4.4+):
db.adminCommand({ replSetResizeOplog: 1, minRetentionHours: 24 })

On MongoDB Atlas, the minimum oplog window can be set under the cluster's Additional Settings > More Configuration Options > Set Minimum Oplog Window.

Important: If changes that CDC still needs to read are removed from the oplog before it does so, the connection will need to perform a fresh initial snapshot to recover. Keep the oplog window comfortably longer than the interval between your CDC syncs.

Deleted documents (optional)

By default, a deleted document only carries its _id, so the other columns of a deleted row are empty. On MongoDB 6.0 or later, you can enable pre-images on a collection so that deleted rows contain the full document as it was before being deleted:

db.runCommand({
    collMod: "your_collection",
    changeStreamPreAndPostImages: { enabled: true }
});

Pre-images are stored by MongoDB in addition to your data, so enable them only on the collections where you need the full content of deleted documents.

How documents are loaded

MongoDB doesn't enforce a fixed structure, so each document field becomes a column in your data warehouse:

• When all fields are selected, new fields are added as new columns as soon as a document containing them is synced. Otherwise, only the selected fields are loaded.

• Documents that don't have a given field are loaded with an empty (NULL) value in that column.

• Nested documents and arrays are loaded as text.