Delivering a Near Real-Time Single View into Operations with a Federated Database

So the data within your organization spans across multiple databases, database platforms, and even storage types, but you need to bring it together and make sense of the data that’s dispersed. This is referred to as a Single View application and it is a common need for many organizations, so you’re not alone!

With MongoDB Data Federation, you can seamlessly query, transform, and aggregate your data from one or more locations, such as within a MongoDB database, AWS S3 buckets, and even HTTP API endpoints. In other words, with Data Federation, you can use the MongoDB Query API to work with your data even if it doesn’t exist within MongoDB.

What’s a scenario where this might make sense?

Let’s say you’re in the automotive or supply chain industries. You have customer data that might exist within MongoDB, but your parts vendors run their own businesses external to yours. However, there’s a need to pair the parts data with transactions for any particular customer. In this scenario, you might want to be able to create queries or views that bring each of these pieces together.

In this tutorial, we’re going to see how quick and easy it is to work with MongoDB Data Federation to create custom views that might aid your sales and marketing teams.

The prerequisites

To be successful with this tutorial, you should have the following or at least an understanding of the following:

• A MongoDB Atlas instance, M0 or better.
• An external data source, accessible within an AWS S3 bucket or an HTTP endpoint.
• Node.js 18+.

While you could have data ready to go for this tutorial, we’re going to assume you need a little bit of help. With Node.js, we can get a package that will allow us to generate fake data. This fake data will act as our customer data within MongoDB Atlas. The external data source will contain our vendor data, something we need to access, but ultimately don’t own.

To get down to the specifics, we’ll be referencing Carvana data because it is available as a dataset on AWS. If you want to follow along exactly, load that dataset into your AWS S3 bucket. You can either expose the S3 bucket to the public, or configure access specific for MongoDB. For this example, we’ll just be exposing the bucket to the public so we can use HTTP.

Understanding the Carvana dataset within AWS S3

If you choose to play around with the Carvana dataset that is available within the AWS marketplace, you’ll notice that you’re left with a CSV that looks like the following:

• vechicle_id
• stock_number
• year
• make
• model
• miles
• trim
• sold_price
• discounted_sold_price
• partnered_dealership
• delivery_fee
• earliest_delivery_date
• sold_date

Since this example is supposed to get you started, much of the data isn’t too important to us, but the theme is. The most important data to us will be the vehicle_id because it should be a unique representation for any particular vehicle. The vehicle_id will be how we connect a customer to a particular vehicle.

With the Carvana data in mind, we can continue towards generating fake customer data.

Generate fake customer data for MongoDB

While we could connect the Carvana data to a MongoDB federated database and perform queries, the example isn’t particularly exciting until we add a different data source.

To populate MongoDB with fake data that makes sense and isn’t completely random, we’re going to use a tool titled mgeneratejs which can be installed with NPM.

If you don’t already have it installed, execute the following from a command prompt:

npm install -g mgeneratejs

With the generator installed, we’re going to need to draft a template of how the data should look. You can do this directly in the command line, but it might be easier just to create a shell file for it.

Create a generate_data.sh file and include the following:

mgeneratejs '{ 
	"_id": "$oid",
    "name": "$name",
    "location": {
        "address": "$address",
        "city": {
            "$choose": {
                "from": [ "Tracy", "Palo Alto", "San Francsico", "Los Angeles" ]
            }
        },
        "state": "CA"
    },
    "payment_preference": {
        "$choose": {
            "from": ["Credit Card", "Banking", "Cash", "Bitcoin" ]
        }
    },
    "transaction_history": {
        "$array": {
            "of": {
                "$choose": {
                    "from": ["2270123", "2298228", "2463098", "2488480", "2183400", "2401599", "2479412", "2477865", "2296988", "2415845", "2406021", "2471438", "2284073", "2328898", "2442162", "2467207", "2388202", "2258139", "2373216", "2285237", "2383902", "2245879", "2491062", "2481293", "2410976", "2496821", "2479193", "2129703", "2434249", "2459973", "2468197", "2451166", "2451181", "2276549", "2472323", "2436171", "2475436", "2351149", "2451184", "2470487", "2475571", "2412684", "2406871", "2458189", "2450423", "2493361", "2431145", "2314101", "2229869", "2298756", "2394023", "2501380", "2431582", "2490094", "2388993", "2489033", "2506533", "2411642", "2429795", "2441783", "2377402", "2327280", "2361260", "2505412", "2253805", "2451233", "2461674", "2466434", "2287125", "2505418", "2478740", "2366998", "2171300", "2431678", "2359605", "2164278", "2366343", "2449257", "2435175", "2413261", "2368558", "2088504", "2406398", "2362833", "2393989", "2178198", "2478544", "2290107", "2441142", "2287235", "2090225", "2463293", "2458539", "2328519", "2400013", "2506801", "2454632", "2386676", "2487915", "2495358", "2353712", "2421438", "2465682", "2483923", "2449799", "2492327", "2484972", "2042273", "2446226", "2163978", "2496932", "2136162", "2449304", "2149687", "2502682", "2380738", "2493539", "2235360", "2423807", "2403760", "2483944", "2253657", "2318369", "2468266", "2435881", "2510356", "2434007", "2030813", "2478191", "2508884", "2383725", "2324734", "2477641", "2439767", "2294898", "2022930", "2129990", "2448650", "2438041", "2261312", "2418766", "2495220", "2403300", "2323337", "2417618", "2451496", "2482895", "2356295", "2189971", "2253113", "2444116", "2378270", "2431210", "2470691", "2460896", "2426935", "2503476", "2475952", "2332775", "2453908", "2432284", "2456026", "2209392", "2457841", "2066544", "2450290", "2427091", "2426772", "2312503", "2402615", "2452975", "2382964", "2396979", "2391773", "2457692", "2158784", "2434491", "2237533", "2474056", "2474203", "2450595", "2393747", "2497077", "2459487", "2494952"]
                }
            },
            "number": {
                "$integer": {
                    "min": 1,
                    "max": 3
                }
            },
            "unique": true
        }
    }
}
' -n 50 

So what’s happening in the above template?

It might be easier to have a look at a completed document based on the above template:

{
    "_id": ObjectId("64062d2db97b8ab3a8f20f8d"),
    "name": "Amanda Vega",
    "location": {
        "address": "1509 Fuvzu Circle",
        "city": "Tracy",
        "state": "CA"
    },
    "payment_preference": "Credit Card",
    "transaction_history": [
        "2323337"
    ]
}

The script will create 50 documents. Many of the fields will be randomly generated with the exception of the city, payment_preference, and transaction_history fields. While these fields will be somewhat random, we’re sandboxing them to a particular set of options.

Customers need to be linked to actual vehicles found in the Carvana data. The script adds one to three actual id values to each document. To narrow the scope, we’ll imagine that the customers are locked to certain regions.

Import the output into MongoDB. You might consider creating a carvana database and a customers collection within MongoDB for this data to live.

Create a multiple datasource federated database within MongoDB Atlas

It’s time for the fun part! We need to create a federated database to combine both customer data that already lives within MongoDB and the Carvana data that lives on AWS S3.

Within MongoDB Atlas, click the Data Federation Tab.

Create a new federated database and add both data sources.

Whether the Carvana data source comes directly from an AWS S3 integration or a public HTTP endpoint, it is up to you. The end result will be the same.

With the data sources available, create a database within your federated instance. Since the theme of this example is Carvana, it might make sense to create a carvana database and give each data source a proper collection name. The data living on AWS S3 might be called sales or transactions and the customer data might have a customers name.

What you name everything is up to you. When connecting to this federated instance, you’ll only ever see the federated database name and federated collection names. Looking in, you won’t notice any difference from connecting to any other MongoDB instance.

You can connect to your federated instance using the connection string it provides. It will look similar to a standard MongoDB Atlas connection string.

The above image was captured with MongoDB Compass. Notice the sales collection is the Carvana data on AWS S3 and it looks like any other MongoDB document?

Create a single view report with a MongoDB aggregation pipeline

Having all the data sources accessible from one location with Data Federation is great, but we can do better by providing users a single view that might make sense for their reporting needs.

A little imagination will need to be used for this example, but let’s say we want a report that shows the amount of car types sold for every city. For this, we’re going to need data from both the customers collection as well as the carvana collection.

Let’s take a look at the following aggregation pipeline:

[
    {
        "$lookup": {
            "from": "sales",
            "localField": "transaction_history",
            "foreignField": "vehicle_id",
            "as": "transaction_history"
        }
    },
    {
        "$unwind": {
            "path": "$transaction_history"
        }
    },
    {
        "$group": {
            "_id": {
                "city": "$location.city",
                "vehicle": "$transaction_history.make"
            },
            "total_transactions": {
                "$sum": 1
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "city": "$_id.city",
            "vehicle": "$_id.vehicle",
            "total_transactions": 1
        }
    }
]

There are four stages in the above pipeline.

In the first stage, we want to expand the vehicle id values that are found in customers documents. Reference values are not particularly useful to us standalone so we do a join operation using the $lookup operator between collections. This leaves us with all the details for every vehicle alongside the customer information.

The next stage flattens the array of vehicle information using the $unwind operation. By the end of this, all results are flat and we’re no longer working with arrays.

In the third stage we group the data. In this example, we are grouping the data based on the city and vehicle type and counting how many of those transactions occurred. By the end of this stage, the results might look like the following:

{
    "_id": {
        "city": "Tracy",
        "vehicle": "Honda"
    },
    "total_transactions": 4
}

In the final stage, we format the data into something a little more attractive using a $project operation. This leaves us with data that looks like the following:

[
    {
        "city": "Tracy",
        "vehicle": "Honda",
        "total_transactions": 4
    },
    {
        "city": "Tracy",
        "vehicle": "Toyota",
        "total_transactions": 12
    }
]

The data can be manipulated any way we want, but for someone running a report of what city sells the most of a certain type of vehicle, this might be useful.

The aggregation pipeline above can be used in MongoDB Compass and would be nearly identical using several of the MongoDB drivers such as Node.js and Python. To get an idea of what it would look like in another language, here is an example of Java:

Arrays.asList(new Document("$lookup", 
    new Document("from", "sales")
            .append("localField", "transaction_history")
            .append("foreignField", "vehicle_id")
            .append("as", "transaction_history")), 
    new Document("$unwind", "$transaction_history"), 
    new Document("$group", 
    new Document("_id", 
    new Document("city", "$location.city")
                .append("vehicle", "$transaction_history.make"))
            .append("total_transactions", 
    new Document("$sum", 1L))), 
    new Document("$project", 
    new Document("_id", 0L)
            .append("city", "$_id.city")
            .append("vehicle", "$_id.vehicle")
            .append("total_transactions", 1L)))

When using MongoDB Compass, aggregation pipelines can be output automatically to any supported driver language you want.

The person generating the report probably won’t want to deal with aggregation pipelines or application code. Instead, they’ll want to look at a view that is always up to date in near real-time.

Within the MongoDB Atlas dashboard, go back to the configuration area for your federated instance. You’ll want to create a view, similar to how you created a federated database and federated collection.

Give the view a name and paste the aggregation pipeline into the box when prompted.

Refresh MongoDB Compass or whatever tool you’re using and you should see the view. When you load the view, it should show your data as if you ran a pipeline — however, this time without running anything.

In other words, you’d be interacting with the view like you would any other collection — no queries or aggregations to constantly run or keep track of.

The view is automatically kept up to date behind the scenes using the pipeline you used to create it.

Conclusion

With MongoDB Data Federation, you can combine data from numerous data sources and interact with it using standard MongoDB queries and aggregation pipelines. This allows you to create views and run reports in near real-time regardless where your data might live.

Have a question about Data Federation or aggregations? Check out the MongoDB Community Forums and learn how others are using them.

This content first appeared on MongoDB.

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in C#, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Unity. Nic writes about his development experiences related to making web and mobile development easier to understand.