It is necessary to bring MongoDB’s data to a relational format data warehouse like AWS Redshift to perform analytical queries. It is simple and cost-effective to efficiently analyze all your data by using a real-time data pipeline. MongoDB is document-oriented and uses JSON-like documents to store data.

MongoDB doesn’t enforce schema restrictions while storing data, the application developers can quickly change the schema, add new fields and forget about older ones that are not used anymore without worrying about tedious schema migrations. Owing to the schema-less nature of a MongoDB collection, converting data into a relational format is a non-trivial problem.

This blog talks about the two different methods you can use to set up a connection from MongoDB to Redshift in a seamless fashion: Using Custom ETL Scripts and with the help of a third-party tool, Hevo.

Introduction to MongoDB

Mongodb logo
Image Source

MongoDB distinguishes itself as a NoSQL database program. It uses JSON-like documents along with optional schemas. MongoDB is written in C++. MongoDB allows you to address a diverse set of data sets, accelerate development, and adapt quickly to change with key functionalities like horizontal scaling and automatic failover.

Introduction to Amazon Redshift

Redshift Logo
Image Source

Amazon Redshift is essentially a storage system that allows companies to store petabytes of data across easily accessible “Clusters” that you can query in parallel. Every Amazon Redshift Data Warehouse is fully managed which means that the administrative tasks like maintenance backups, configuration, and security are completely automated. 

A data practitioner can use Amazon Redshift to work with Big Data and is easily scalable due to its modular node design. It also allows users to gain more granular insight into datasets, owing to the ability of Amazon Redshift Clusters to be further divided into slices. Amazon Redshift’s multi-layered architecture allows multiple queries to be processed simultaneously thus cutting down on waiting times. Apart from these, there are a few more benefits of Amazon Redshift you can unlock with the best practices in place.

Main features of Amazon Redshift are:

  • When you submit a query, Redshift cross checks the result cache for a valid and cached copy of the query result. When it finds a match in the result cache, the query is not executed. On the other hand, it uses a cached result to reduce runtime of the query.
  • You can use the Massive Parallel Processing (MPP) feature for writing the most complicated queries when dealing with large volume of data.
  • Your data is stored in columnar format in Redshift tables. Therefore, the number of disk I/O requests to optimize analytical query performance is reduced.

Understanding the Methods to Move Data from MongoDB to Redshift

MongoDB to Redshift Integration image
Image Source

These are the methods you can use to move data from MongoDB to Redshift in a seamless fashion:

Methods to replicate data from MongoDB data to Redshift:

There are two popular methods to move data from MongoDB to Amazon Redshift.

Using Custom Scripts to Move Data from MongoDB to Redshift

This is a simple 4 step approach from exporting data using mongoexport to loading the data from S3 to Redshift. This method has considerable advantages but suffers from a few setbacks as well.

Using Hevo Data to Move Data from MongoDB to Redshift

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs

GET STARTED WITH HEVO FOR FREE

Method 1: Using Hevo Data to Move Data from MongoDB to Redshift

Hevo Logo
Image Source

Hevo Data, a No-code Data Pipeline platform helps to load data from any data source such as Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services and simplifies the ETL process. It supports 150+ data sources including MongoDB, etc., and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination.

Hevo’s fault-tolerant and scalable architecture ensures that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different Business Intelligence (BI) tools as well.

Get Started with Hevo for free

The steps involved in the process are,

  • Configure Source: Load Data from Hevo to MongoDB by entering details like Database Port, Database Host, Database User, Database Password, Pipeline Name, Connection URI, and the connection settings.
Image Source
  • Integrate Data: Load data from MongoDB to Redshift by providing your Redshift databases credentials like Database Port, Username, Password, Name, Schema, and Cluster Identifier along with the Destination Name.
Image Source

Here are a few benefits of using Hevo for ETL from MongoDB to Redshift:

  • Real-time Data Migration in Minutes: With Hevo, you can connect to MongoDB on a point and click UI. Without writing any code, you can seamlessly move data from MongoDB to Redshift in real-time. This will eliminate the need for you to run batch ETL jobs or manage servers. Hevo takes care of all the grunt work and makes your data available in real-time for analysis by using existing business intelligence tools.
  • Automatic Schema Detection and Evolution: Hevo dynamically detects schema as it pulls documents for ETL and upgrades the earlier encountered schema to incorporate new collections and fields. Hevo also creates the required tables and columns in Redshift as and when the schema changes.
  • Notifications for Schema Incompatibilities: Hevo sends notifications to the user whenever the fields with incompatible types are detected in a collection. Hevo also allows the user to take appropriate actions to handle the incompatibility right on time.
  • Dynamic expansion for Varchar Columns: Hevo expands the existing varchar columns in Redshift dynamically as and when it encounters longer string values. This ensures that your Redshift space is used wisely without you breaking a sweat.
  • Splitting Nested Documents with Transformations: Hevo lets you split the nested MongoDB documents into multiple rows in Redshift by writing simple Python transformations. This makes MongoDB file flattening a cakewalk for users.
  • Automatic Conversion to Redshift Data Types: Hevo converts all MongoDB data types to the closest compatible data type in Redshift. This eliminates the need to write custom scripts to maintain each data type, in turn, making the migration of data from MongoDB to Redshift seamless.

By following the above two steps you can seamlessly export MongoDB to Redshift.

GET STARTED WITH HEVO FOR FREE

For detailed information on how you can use the Hevo connectors for MongoDB to Redshift ETL, check out:

Method 2: Using Custom Scripts to Move Data from MongoDB to Redshift

Following are the steps to move data from MongoDB to Redshift using Custom Script:

  • Step 1: Use mongoexport to export data.
mongoexport --collection=collection_name --db=db_name --out=outputfile.csv
  • Step 2: Upload the .json file to the S3 bucket.

2.1: Since MongoDB allows for varied schema, it might be challenging to comprehend a collection and produce an Amazon Redshift table that works with it. For this reason, before uploading the file to the S3 bucket, you need to create a table structure.

2.2: Installing the AWS CLI will also allow you to upload files from your local computer to S3. File uploading to the S3 bucket is simple with the help of the AWS CLI. To upload.csv files to the S3 bucket, use the command below if you have previously installed the AWS CLI. You may use the command prompt to generate a table schema after transferring.csv files into the S3 bucket. 

AWS S3 CP D:\outputfile.csv S3://S3bucket01/outputfile.csv
  • Step 3: Create a Table schema before loading the data into Redshift.
  • Step 4: Using the COPY command load the data from S3 to Redshift.

Use the following COPY command to transfer files from the S3 bucket to Redshift if you’re following Step 2 (2.1).

COPY table_name
from 's3://S3bucket_name/table_name-csv.tbl' 
'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
csv;

Use the COPY command to transfer files from the S3 bucket to Redshift if you’re following Step 2 (2.2). Add csv to the end of your COPY command in order to load files in CSV format.

COPY db_name.table_name 
FROM ‘S3://S3bucket_name/outputfile.csv’
'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
csv;

You have successfully completed MongoDB Redshift integration. 

For the scope of this article, we have highlighted the challenges faced while migrating data from MongoDB to Amazon Redshift. Towards the end of the article, a detailed list of advantages of using approach 2 is also given. You can check out Method 1 on our other blog and know the detailed steps to migrate MongoDB to Amazon Redshift.

Limitations of using Custom Scripts to Move Data from MongoDB to Redshift

Here is a list of limitations of using the manual method of moving data from MongoDB to Redshift:

  • Schema Detection Cannot be Done Upfront: Unlike a relational database, a MongoDB collection doesn’t have a predefined schema. Hence, it is impossible to look at a collection and create a compatible table in Redshift upfront.
  • Different Documents in a Single Collection: Different documents in single collection can have a different set of fields. A document in a collection in MongoDB can have a different set of fields.
{

  "name": "John Doe",

  "age": 32,

  "gender": "Male"

}
{

  "first_name": "John",

  "last_name": "Doe",

  "age": 32,

  "gender": "Male"

}

Different documents in a single collection can have incompatible field data types. Hence, the schema of the collection cannot be determined by reading one or a few documents.

2 documents in a single MongoDB collection can have fields with values of different types.

{

  "name": "John Doe",

  "age": 32,

  "gender": "Male"

  "mobile": "(424) 226-6998"

}
{

"name": "John Doe",

"age": 32,

"gender": "Male",

"mobile": 4242266998

}

The field mobile is a string and a number in the above documents respectively. It is a completely valid state in MongoDB. In Redshift, however, both these values either will have to be converted to a string or a number before being persisted.

  • New Fields can be added to a Document at Any Point in Time: It is possible to add columns to a document in MongoDB by running a simple update to the document. In Redshift, however, the process is harder as you have to construct and run ALTER statements each time a new field is detected.
  • Character Lengths of String Columns: MongoDB doesn’t put a limit on the length of the string columns. It has a 16MB limit on the size of the entire document. However, in Redshift, it is a common practice to restrict string columns to a certain maximum length for better space utilization. Hence, each time you encounter a longer value than expected, you will have to resize the column.
  • Nested Objects and Arrays in a Document: A document can have nested objects and arrays with a dynamic structure. The most complex of MongoDB ETL problems is handling nested objects and arrays.
{

"name": "John Doe",

"age": 32,

"gender": "Male",

"address": {

"street": "1390 Market St",

"city": "San Francisco",

"state": "CA"

},

"groups": ["Sports", "Technology"]

}

MongoDB allows nesting objects and arrays to several levels. In a complex real-life scenario is may become a nightmare trying to flatten such documents into rows for a Redshift table.

  • Data Type Incompatibility between MongoDB and Redshift: Not all data types of MongoDB are compatible with Redshift. ObjectId, Regular Expression, Javascript are not supported by Redshift. While building an ETL solution to migrate data from MongoDB to Redshift from scratch, you will have to write custom code to handle these data types.
Download the Cheatsheet on How to Set Up High-performance ETL to Redshift
Download the Cheatsheet on How to Set Up High-performance ETL to Redshift
Download the Cheatsheet on How to Set Up High-performance ETL to Redshift
Learn the best practices and considerations for setting up high-performance ETL to Redshift

Conclusion

This blog talks about the 2 different methods you can use to set up a connection from MongoDB to Redshift in a seamless fashion: Using Custom ETL Scripts and with the help of a third-party tool, Hevo.

Outside of the benefits offered by Hevo, you can use Hevo to migrate data from an array of different sources – databases, cloud applications, SDKs, and more. This will provide the flexibility to instantly replicate data from any source like MongoDB to Redshift.

More related reads:

  1. Creating a table in Redshift
  2. Redshift functions

You can additionally model your data, build complex aggregates and joins to create materialized views for faster query executions on Redshift. You can define the interdependencies between various models through a drag and drop interface with Hevo’s Workflows to convert MongoDB data to Redshift.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of setting up a connection from MongoDB to Redshift in the comments section below!

mm
Founder and CTO, Hevo Data

Sourabh has more than a decade of experience building scalable real-time analytics and has worked for companies like Flipkart, tBits Global, and Unbxd. He is experienced in technologies like MySQL, Hibernate, Spring, CXF, php, ExtJS and Shell.

No-code Data Pipeline for Redshift