MongoDB to Redshift ETL: 2 Easy Methods

If you are looking to move data from MongoDB to Redshift, I reckon that you are trying to upgrade your analytics set up to a modern data stack. Great move!

Kudos to you for taking up this mammoth of a task! In this blog, I have tried to share my two cents on how to make the data migration from MongoDB to Redshift easier for you.

Before we jump to the details, I feel it is important to understand a little bit on the nuances of how MongoDB and Redshift operate. This will ensure you understand the technical nuances that might be involved in MongoDB to Redshift ETL. In case you are already an expert at this, feel free to skim through these sections or skip them entirely.

Why perform MongoDB to Redshift ETL?

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 for you.

In my experience in helping customers set up their modern data stack, I have seen MongoDB be a particularly tricky database to run analytics on. Hence, I have also suggested an easier / alternative approach that can help make your journey simpler.

In this blog, I will talk 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.

What is MongoDB?

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.

MondoDB is a best RDBMS when you have a huge data volume of structured and unstructured data. It’s features make scaling and flexibility smooth. These are available for data integration, load balancing, ad-hoc queries, sharding, indexing, etc.

Another advantage is that MongoDB also supports all common operating systems (Linux, macOS, and Windows). It also supports C, C++, Go, Node.js, Python, and PHP. 

What is Amazon Redshift?

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. 

Suppose, you are a data practitioner who wants to use Amazon Redshift to work with Big Data. It will make your work easily scalable due to its modular node design. It also us you 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

• 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.

What Are the Methods to Move Data from MongoDB to Redshift?

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

• Method 1: Using Custom Scripts to Move Data from MongoDB to Redshift
• Method 2: Using an Automated Data Pipeline Platform to Move Data from MongoDB to Redshift

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

Following are the steps we can use 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;

We 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.

Method 2: Using Third Pary ETL Tools to Move Data from MongoDB to Redshift

You can use an automated data pipeline platform to carry out the integration. Hevo Data is one such platform, a No-code Data Pipeline platform that can help 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

Here are the steps involved in the process for you:

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.

• 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.

So,what are the 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.
• 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:

• MongoDB Source Connector
• Redshift Destination Connector

Conclusion

In this blog, I have talked 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!