Quick tip: Replicating a MongoDB Atlas database to SingleStoreDB Cloud using Hevo Data
Tags: singlestoredb, mongodbatlas, mongodb, elt
Published: 2022-11-09 16:09:33

## Abstract
A range of open-source and commercial tools can provide Extract, Load and Transform (ELT) capabilities. These tools provide source and destination connectors and can use automatic data format conversion. This article will use a commercial ELT tool to replicate a MongoDB Atlas database to SingleStoreDB Cloud.

## Introduction
In a [previous article](https://dev.to/veryfatboy/testing-airbyte-with-singlestoredb-and-apache-pulsar-4a64), we used open-source Airbyte to create an ELT pipeline between SingleStoreDB and Apache Pulsar. We have also seen in [another article](https://dev.to/veryfatboy/quick-tip-moving-mongodb-json-data-to-singlestoredb-2hgh) several methods to ingest MongoDB JSON data into SingleStoreDB. In this article, we'll evaluate a commercial ELT tool called [Hevo Data](https://hevodata.com/) to create a pipeline between MongoDB Atlas and SingleStoreDB Cloud. Switching to SingleStoreDB has many benefits, as described in the Captain Metrics Customer Case Study and [webinar](https://www.singlestore.com/resources/webinar-captain-metrics-why-we-ditched-mongodb-2022-03/).

## SingleStoreDB configuration
A [previous article](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **Hevo Demo Group** as our Workspace Group Name and **hevo-demo** as our Workspace Name. We'll make a note of our **password** and **host** name. Finally, we'll create a new database using the **SQL Editor**:

```sql
CREATE DATABASE hevo_demo;
```

## MongoDB configuration
We'll use the MongoDB Atlas shared cluster deployment on AWS. This will give us a 3-node cluster (one primary and two secondary). Once the cluster is deployed, we'll load the sample dataset. We'll also create a user called `hevo` and assign `readAnyDatabase` privileges to this user for our initial tests.

## Hevo Data configuration

### 1. Configure Source

We'll search and choose **MongoDB Atlas** as the source.

We'll fill in the **Configure your MongoDB Atlas Source** form, as follows:

- **Pipeline Name:** MongoDB Atlas Source
- **General Connection Settings:**
  - Select **Paste Connection String**
  - **Connection URI:** `mongodb+srv://hevo:<password>@<cluster>`
- **Select an Ingestion Mode:** Change Streams
- **Advanced Settings:** Disable **Load All Databases** and select `sample_restaurants` from the list

We'll replace the `<password>` and `<cluster>` with the values from MongoDB Atlas.

Several Hevo Data IP addresses are also listed, and these should be added to the **IP Access List** in MongoDB Atlas.

We'll use the **TEST CONNECTION** button, and the connection should be successful.

Next, we'll click **TEST & CONTINUE**.

### 2. Select Objects
On the next page, we'll check (✔) **All objects selected** and click **CONTINUE**.

### 3. Configure Destination
We'll search and choose **MySQL** as the destination.

We'll fill in the **Configure your MySQL Destination** form, as follows:

- **Destination Name:** SingleStoreDB Destination
- **Database Host:** &lt;host&gt;
- **Database Port:** 3306
- **Database User:** admin
- **Database Password:** &lt;password&gt;
- **Database Name:** hevo_demo

We'll replace the `<host>` and `<password>` with the values from our SingleStoreDB Cloud account.

Several Hevo Data IP addresses are also listed, and these should be added to the **Inbound IP Allowlist** in the SingleStoreDB Cloud Firewall.

We'll use the **TEST CONNECTION** button, and the connection should be successful.

Next, we'll click **SAVE & CONTINUE**.

### 4. Final Settings
We'll use **Auto Mapping** and **Replicate JSON fields to JSON columns**.

Next, we'll click **CONTINUE**.

The pipeline should start running shortly afterwards, as shown in Figure 1.

![Figure 1. Hevo Data Pipeline.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h57fqxgwvw3qsl91gzuf.png)

<center>Figure 1. Hevo Data Pipeline.</center>

It will take a little time for the pipeline to complete.

In SingleStoreDB, two tables should be created, `restaurants` and `neighborhoods`, to match the two collections in MongoDB.

We can quickly check each table in SingleStoreDB to view what the Hevo Data pipeline has generated. Here is an example from the `restaurants` table:

```
*************************** 1. row ***************************
                       _id: 5eb3d669b31de5d588f4754d
                   address: {"building":"7015","coord":[-74.026271,40.634328],"street":"3 Avenue","zipcode":"11209"}
                   borough: Brooklyn
                   cuisine: Ice Cream, Gelato, Yogurt, Ices
                    grades: [{"date":1401494400000,"grade":"A","score":10}]
                      name: Uncle Mikes Ices
             restaurant_id: 50001556
     __hevo__database_name: sample_restaurants
                 __hevo_id: ef392c2550f086e4e22680e2b9753383ff6e9ae0747c5634b1c4188978815eaf
       __hevo__ingested_at: 1667930442414
    __hevo__marked_deleted: 0
__hevo__source_modified_at: NULL
```

and here is a truncated example from the `neighborhoods` table:

```
*************************** 1. row ***************************
                       _id: 55cb9c666c522cafdb053ab3
                  geometry: {"coordinates":[[[-73.74016951762782,40.73848189750691],[-73.74144628592867,40.736938746807425],[-73.74151636839602,40.73686036104047],
...
[-73.74055585492765,40.73858718262385],[-73.74029081629884,40.73851667014021],[-73.74016951762782,40.73848189750691]]],"type":"Polygon"}
                      name: Oakland Gardens
     __hevo__database_name: sample_restaurants
                 __hevo_id: b75f4ff0eddfff963f8a983274c856cf1d433806a536fddf36bfe1bb8c9def76
       __hevo__ingested_at: 1667930426142
    __hevo__marked_deleted: NULL
__hevo__source_modified_at: NULL
```

Eventually, there should be 25,359 rows in the `restaurants` table and 195 rows in the `neighborhoods` table. These numbers match the numbers from MongoDB Atlas.

## Summary
This short article has shown one example of a database automatically replicated to SingleStoreDB using a commercial ELT platform. Overall, the results have been very promising. Auto Mapping worked successfully, and all the data were copied from MongoDB Atlas to SingleStoreDB Cloud.