MongoDB to Redshift

This page provides you with instructions on how to extract data from MongoDB and load it into Redshift. (If this manual process sounds onerous, check out Stitch, which can do all the heavy lifting for you in just a few clicks.)

What is MongoDB?

MongoDB, or just Mongo, is an open source NoSQL database that stores data in JSON format. It uses a document-oriented data model, and data fields can vary by document. MongoDB isn't tied to any specified data structure, meaning that there's no particular format or schema for data in a Mongo database.

Getting data out of MongoDB

The process of pulling data out of MongoDB depends on how you've loaded data into MongoDB. In some cases, it may be impossible to extract all of your data, because NoSQL databases don't require structure (i.e. specific columns). Relational databases, such as those used for data warehouses, use a more traditional, rigid structure. You'll need to defined a structure in the relational database into which you can insert MongoDB data.

Don't stress about the confusing data structure. Lots of the data that's loaded into MongoDB is created by a computer, so it probably has a pretty predictable structure. If you can find specific fields that exist for every record, you're well on your way. Make sure these fields appear in the records of each collection you'd like to replicate from MongoDB. There are many ways to do this. The most popular method to get data from MongoDB is to use the find() command.

Sample MongoDB data

MongoDB stores and returns JSON-formatted data. Here's an example of what a response might look like to a query against the products collection.

db.products.find( { qty: { $gt: 25 } }, { _id: 0, qty: 0 } )

{ "item" : "pencil", "type" : "no.2" }
{ "item" : "bottle", "type" : "blue" }
{ "item" : "paper" }

Loading data into Redshift

Once you've identified all the columns you want to insert, you can use the CREATE TABLE statement in Reshift to set up a table to receive your data.

With the table built, you might think that the easiest way to migrate your data (especially if there isn't much of it) would be to build INSERT statements to add data to your Redshift table row by row. Think again! Redshift isn't optimized for inserting data one row at a time. If you have a high volume of data to be inserted, we suggest moving the data into Amazon S3 and then using the COPY command to load it into Redshift.

Keeping MongoDB data up to date

Fine job! You are the proud developer of a script that moves data from MongoDB to your data warehouse. This works as a one-shot deal. It's good to think about what will happen when there is new and updated data in MongoDB.

One option that works would be to load the entire MongoDB dataset all over again. That would certainly update the data, but it's not very efficient and can also cause terribly latency.

The smartest way to get data updated from MongoDB would be to identify keys that can be used as bookmarks to store where you script left off on the last run. Fields like updated_at, modified_at, or other auto-incrementing data are useful here. With that done, you can set up your script as a cron job or continuous loop to identify new data as it appears.

Other data warehouse options

Redshift is great, but sometimes you need to optimize for different things when you're choosing a data warehouse. Some folks choose to go with Google BigQuery, PostgreSQL, or Snowflake, which are RDBMSes that use similar SQL syntax, or Panoply, which works with Redshift instances. If you're interested in seeing the relevant steps for loading data into one of these platforms, check out To BigQuery, To Postgres, To Snowflake, and To Panoply.

Easier and faster alternatives

If all this sounds a bit overwhelming, don’t be alarmed. If you have all the skills necessary to go through this process, chances are building and maintaining a script like this isn’t a very high-leverage use of your time.

Thankfully, products like Stitch were built to solve this problem automatically. With just a few clicks, Stitch starts extracting your MongoDB data via the API, structuring it in a way that is optimized for analysis, and inserting that data into your Redshift data warehouse.