Venkatesh Shanbhag

Partner Solution Architect

Diana Annie Jenosh

Senior Partner Solutions Architect

Many enterprise customers using MongoDB Atlas as their core operational database also use BigQuery for their Batch and AI/ML based analytics, making it pivotal for seamless transfer of data between these entities. Since the announcement of the [Dataflow templates](https://cloud.google.com/blog/products/data-analytics/mongodb-atlas-and-bigquery-dataflow-templates) (in October Of 2022) on moving data between MongoDB and BigQuery, we have seen a lot of interest from customers as it made it effortless for an append-only, one-to-one migration of data. Though the three Dataflow templates provided cater to most of the common use cases, there was also a demand to be able to do transformations as part of these templates.

We are excited to announce the addition of the ability to write your own user-defined functions (UDFs) in these Dataflow pipelines! This new feature allows you to use UDFs in JavaScript to transform and analyze data within BigQuery. With UDFs, you can define custom logic and business rules that can be applied to your data as it is being processed by Dataflow. This allows you to perform complex transformations like transforming fields, concatenating fields, deleting fields, converting embedded documents to separate documents, etc. These UDFs take unprocessed documents as input parameters and return the processed documents as output.

To use UDFs with BigQuery Dataflow, simply write your JavaScript function and store it in the Google cloud storage bucket. Use the Dataflow templates’ optional parameter to read these UDFs while running the templates. The function will be executed on the data as it is being processed, allowing you to apply custom logic and transformations to your data during the transfer.

## How to set it up
Let’s have a quick look at how to set up a sample UDF to process (transform a field, flatten an embedded document, and delete a field) from an input document before writing the processed data to BigQuery.

### Set up MongoDB 

1. MongoDB Atlas setup through [registration](https://mongodb.prakticum-team.ru/cloud/atlas/register).
2. MongoDB Atlas setup through [GCP Marketplace](https://console.cloud.google.com/marketplace/product/mongodb/mdb-atlas-self-service?utm_source=mongodb_devrel&utm_medium=static_site&utm_campaign=devrel&utm_id=mongodb). (MongoDB Atlas is available pay as you go in the GC marketplace).
3. Create your MongoDB [cluster](https://mongodb.prakticum-team.ru/docs/atlas/tutorial/create-new-cluster/).
4. Click on **Browse collections** and click on **+Create Database**.

![MongoDB Atlas cluster landing page](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/mongodb_atlas_cluster_landing_page_ad369de42d.png)

5: Name your database **Sample_Company** and collection **Sample_Employee**.
![MongoDB Atlas create collection step](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/create_database_30d4e827a2.png)
6: Click on **INSERT DOCUMENT**.
![Insert document to MongoDB collection from Atlas UI](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/insert_document_2e7d4f16ef.png)
Copy and paste the below document and click on **Insert**.
```
{
  "Name":"Venkatesh",
  "Address":{"Phone":{"$numberLong":"123455"},"City":"Honnavar"},
  "Department":"Solutions Consulting",
  "Direct_reporting": "PS"
}
```
7: To have authenticated access on the MongoDB Sandbox cluster from Google console, we need to create database users.

Click on the **Database Access** from the left pane on the Atlas Dashboard. 

Choose to **Add New User** using the green button on the left. Enter the username `appUser` and password `appUser123`. We will use built-in roles; click **Add Default Privileges** and in the **Default Privileges** section, add the roles readWriteAnyDatabase. Then press the green **Add User** button to create the user.

![Create a database user on MongoDB Atlas](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/create_database_user_774995251a.png)

8: Whitelist the IPs.

For the purpose of this demo, we will allow access from any ip, i.e 0.0.0.0/0. However, this is not recommended for a production setup, where the recommendation will be to use VPC Peering and private IPs.

![IP whitelisting on MongoDB Atlas to allow external access](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/ip_whitelisting_f5bc31c2bb.png)

![Whitelist 0.0.0.0/0](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/whitelisting_c0f1df1e03.png)

### Set up Google Cloud

1. Create a cloud storage bucket.
2. On your local machine, create a Javascript file **transform.js** and add below sample code.

```
function transform(inputDoc) {
   var outputDoc = new Object();
   inputDoc["City"] = inputDoc["Address"]["City"];
   delete doc.Address;
   outputDoc = doc;
   return returnObj;
}
```

This function will read the document read from MongoDB using the Apache beam MongoDB IO connector. Flatten the embedded document Address/City to City. Delete the Address field and return the updated document.

3: [Upload](https://cloud.google.com/storage/docs/uploading-objects) the javascript file to the Google Cloud storage bucket.

4: [Create](https://cloud.google.com/bigquery/docs/datasets#create-dataset) a BigQuery Dataset in your project in the region close to your physical location.

5: Create a [Dataflow pipeline](https://console.cloud.google.com/dataflow).

a. Click on the **Create Job from the template** button at the top.

b. Job Name: **mongodb-udf**.

c. Region: Same as your BigQuery dataset region.

d. MongoDB connection URI: Copy the connection URI for connecting applications from MongoDB Atlas.

e. MongoDB Database: **Sample_Company**.

f. MongoDB Collection: **Sample_Employee**.

g. BigQuery Destination Table: Copy the destination table link from the BigQuery

h. Dataset details page in format: bigquery-project:**sample_dataset.sample_company**.

i. User Option: **FLATTEN**.

j. Click on **show optional parameters**.

k. Cloud storage location of your Javascript UDF: Browse your UDF file loaded to bucket location. This is the new feature that allows running the UDF and applies the transformations before inserting into BigQuery.

l. Name of your Javascript function: **transform**.

![Create Dataflow Job with UDF on Google Cloud console](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/create_dataflow_job_cfdbd2357e.png)

6: Click on **RUN JOB** to start running the pipeline. Once the pipeline finishes running,  your graph should show **Succeeded** on each stage as shown below.

![Dataflow Job Graph view on Google Cloud console](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/dataflow_job_graph_ff48458c96.png)

7: After completion of the job, you will be able to see the transformed document inserted into BigQuery.

![Document read from MongoDB flattened and loaded to BigQuery](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/document_read_ed7e24b805.png)

## Conclusion
In this blog, we introduced UDFs to MongoDB to BigQuery Dataflow templates and their capabilities to transform the documents read from MongoDB using custom user defined Javascript functions stored on Google Cloud storage buckets. This blog also includes a simple tutorial on how to set up MongoDB Atlas, Google Cloud, and the UDFs.

### Further reading

* A data pipeline for [MongoDB Atlas and BigQuery using Dataflow](https://cloud.google.com/blog/products/data-analytics/mongodb-atlas-and-bigquery-dataflow-templates).
* A data pipeline for [MongoDB Atlas and BigQuery using the Confluent](https://mongodb.prakticum-team.ru/developer/products/atlas/mongodb-bigquery-pipeline-using-confluent/) connector.
* Run analytics using BigQuery using [BigQuery ML](https://cloud.google.com/bigquery-ml/docs).
* [Set up your first MongoDB cluster](https://mongodb.prakticum-team.ru/docs/atlas/billing/gcp-self-serve-marketplace/) using Google Marketplace.


Learn how to transform the MongoDB Documents using user-defined JavaScript functions in Dataflow templates.

UDF Announcement for MongoDB to BigQuery Dataflow Templates

To win in this competitive digital economy, enterprises are striving to create smarter intelligent apps. These apps provide a superior customer experience and can derive insights and predictions in real-time. 

Smarter apps use data — in fact, lots of data, AI and analytics together. [MongoDB Atlas](https://mongodb.prakticum-team.ru/cloud/atlas/lp/try4) stores valuable operational data and has capabilities to support operational analytics and AI based applications. This blog details MongoDB Atlas’ seamless integration with [Microsoft Fabric](https://www.microsoft.com/en-us/microsoft-fabric) to run large scale AI/ML and varied analytics and BI reports across the enterprise data estate, reshaping how teams work with data by bringing everyone together on a single, AI-powered platform built for the era of AI. Customers can leverage MongoDB Atlas with Microsoft Fabric as the foundation to build smart and intelligent applications.

## Better together 

MongoDB was showcased as a key partner at Microsoft Ignite, highlighting the collaboration to build seamless integrations and joint solutions complementing capabilities to address diverse use cases. 

![Satya Nadella at Microsoft Ignite][1]

During the first [Keynote at Ignite](https://ignite.microsoft.com/en-US/sessions/bff678f5-f533-4ad0-9905-6ac35c9704ad?source=sessions), Satya Nadella, Chairman and Chief Executive Officer of Microsoft, announced that Microsoft Fabric is now generally available for purchase. Satya addressed the strategic plan to enable MongoDB Atlas mirroring in Microsoft Fabric to enable our customers to use mirroring to access their data in OneLake. 

MongoDB Atlas’ flexible data model, versatile query engine, integration with LLM frameworks, and inbuilt [Vector Search](https://mongodb.prakticum-team.ru/products/platform/atlas-vector-search), [analytical nodes](https://mongodb.prakticum-team.ru/docs/atlas/cluster-config/multi-cloud-distribution/#analytics-nodes-for-workload-isolation), [aggregation framework](https://mongodb.prakticum-team.ru/docs/manual/aggregation/), [Atlas Data Lake](https://mongodb.prakticum-team.ru/atlas/data-lake), [Atlas Data Federation](https://mongodb.prakticum-team.ru/cloud/atlas/lp/data-federation), [Charts](https://mongodb.prakticum-team.ru/products/charts), etc. enables operational analytics and application-driven intelligence from the source of the data itself. However, the analytics and AI needs of an enterprise span across their data estate and require them to combine multiple data sources and run multiple types of analytics like big data, Spark, SQL, or KQL-based ones at a large-scale. They bring data from sources like MongoDB Atlas to one uniform format in [OneLake](https://learn.microsoft.com/en-us/fabric/onelake/onelake-overview) in Microsoft Fabric to enable them to run Batch Spark analytics and AI/ML of petabyte scale and use data warehousing abilities, big data analytics, and real-time analytics across the delta tables populated from disparate sources. 

![MongoDB Atlas Developer Data Platform][2]

Let's discuss the integration mechanisms between MongoDB Atlas and Microsoft Fabric for both batch and real-time scenarios. 

## MongoDB integration mechanisms for Microsoft Fabric

MongoDB Atlas serves as the operational data layer (ODL) of many enterprise applications. Atlas as an ODL stores data from internal applications, customer-facing services, and third-party APIs from multiple channels. By using Microsoft Fabric pipelines, you can combine MongoDB Atlas data with relational data from other traditional applications and unstructured data from sources like logs, clickstreams, etc.

### Batch/microbatches

There are multiple options to bring MongoDB Atlas data into Microsoft Fabric in batch mode one time or bring it in micro batches which runs on a specified frequency. In this section, we will discuss the out-of-the-box (OOTB) approaches that can be applied to fetch data from MongoDB to Microsoft Fabric in batch mode. 

#### MongoDB Atlas SQL Dataflow Gen2 connector

The [MongoDB Atlas SQL connector ](https://learn.microsoft.com/en-us/power-query/connectors/mongodb-atlas-sql-interface)is a Microsoft-certified connector which can be accessed from the “[Dataflow Gen2](https://learn.microsoft.com/en-us/fabric/data-factory/create-first-dataflow-gen2)” feature from “[Data Factory](https://learn.microsoft.com/en-us/fabric/data-factory/data-factory-overview)” in Microsoft Fabric.

Dataflow Gen2 selection takes us to the familiar Power Query interface of Microsoft Power BI. To bring data from MongoDB Atlas collections, search the MongoDB Atlas SQL connector from the “Get Data” option on the menu.

![Dataflow Gen2][3]

Providing the connection URI and authentication details, we can set up a connection to MongoDB Atlas. Note that the connection string is not that of a normal connection but that of Atlas SQL or a federated database. Learn more about [connecting using the Atlas SQL](https://mongodb.prakticum-team.ru/docs/atlas/data-federation/query/sql/powerbi/connect/) or [set up an Atlas federated database](https://mongodb.prakticum-team.ru/docs/atlas/data-federation/tutorial/getting-started/) and get a connection string for the same. Also, note that the connector needs a Gateway set up to communicate from Fabric and schedule refreshes. Get more details on [Gateway setup](https://powerbi.microsoft.com/en-in/gateway/).

Once data is retrieved from MongoDB Atlas into Power Query, the magic of Power Query can be used to transform the data, including flattening object data into separate columns, unwinding array data into separate rows, or changing data types. These are typically required when converting MongoDB data in JSON format to the relational format in Power BI. Additionally, the blank query option can be used for a quick query execution. Below is a sample query to start with:

```
let
  Source = MongoDBAtlasODBC.Query("<Atlas SQL connection string>", “<DB name>", "select * from <collection name>", null)
in
  Source
```

####  MongoDB Data Pipeline connector (preview)

The [announcement at Microsoft Ignite](https://www.microsoft.com/en-us/microsoft-fabric/blog/) of the Data Pipeline connector being released for MongoDB Atlas in Microsoft Fabric is definitely good news for MongoDB customers. The connector provides a quick and similar experience as the [MongoDB connector in Data Factory and Synapse Pipelines](https://learn.microsoft.com/en-us/azure/data-factory/connector-mongodb?tabs=data-factory). 

The connector is accessed from the “[Data Pipelines](https://learn.microsoft.com/en-us/fabric/data-factory/create-first-pipeline-with-sample-data)” feature from “[Data Factory](https://learn.microsoft.com/en-us/fabric/data-factory/data-factory-overview)” in Fabric. Choose the “Copy data” activity to use the MongoDB connector to get data from MongoDB or to push data to MongoDB. To get data from MongoDB, add MongoDB in Source. Select the MongoDB connector and create a linked service by providing the **connection string** and the **database** to connect to in MongoDB Atlas.

![MongoDB Data Pipeline Connector][4]

We can run the pipeline by setting up Lakehouse in Microsoft Fabric as the Sink to receive the data from MongoDB Atlas.

![Microsoft Fabric Data Details][5]

Thus, the connector provides an easy mechanism to copy data between MongoDB Atlas and Microsoft Fabric. Similar to the MongoDB connector in Microsoft Azure Data Factory, it is available for both on-prem and self-hosted MongoDB instances and MongoDB Atlas. As this is the first release of the connector, it has few limitations when compared to the MongoDB connector in Azure Data Factory or Synapse Pipelines. 

#### MongoDB mirroring in Microsoft Fabric

As mentioned earlier, Satya Nadella, CEO of Microsoft, announced the strategic plan to enable MongoDB Atlas mirroring in Microsoft Fabric. This will provide the easiest and fastest approach to replicate the data from MongoDB Atlas to OneLake in Microsoft Fabric. However, this will provide the fewest options to do data transformations while replicating the data. The availability of this feature is expected to be coming in CY24.

![Microsoft Fabric Compute Engines][6]

### Real-time sync

Bringing data using the PULL mechanism of batch mode serves multiple use-cases, including the near real-time ones. However, in certain cases, we may need a real-time sync mechanism, which will replicate the data changes from MongoDB Atlas into Microsoft Fabric in real-time. This has to be a Change Data Capture method, a PUSH mechanism from MongoDB Atlas to Microsoft Fabric. In this section, we will detail two such custom approaches that can be leveraged for these CDC-based, real-time sync use cases.

#### Atlas triggers and functions

Real-time sync from MongoDB Atlas to Microsoft Fabric can be achieved by using [MongoDB Atlas Triggers](https://mongodb.prakticum-team.ru/docs/atlas/app-services/triggers/database-triggers/) to capture the change events in a MongoDB collection and using an [Atlas function](https://mongodb.prakticum-team.ru/docs/atlas/app-services/functions/) to trigger an Azure function. The Azure function can directly write to the Lake House in Microsoft Fabric or to ADLS Gen2 storage using [ADLS Gen2 APIs](https://learn.microsoft.com/en-us/rest/api/storageservices/data-lake-storage-gen2). ADLS Gen2 storage accounts can be referenced in Microsoft Fabric using [shortcuts](https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts), eliminating the need for an ETL process to move data from ADLS Gen2 to OneLake. Data in Microsoft Fabric can be accessed using the existing ADLS Gen2 APIs but there are some changes and constraints which can be referred to in the [Microsoft Fabric documentation](https://learn.microsoft.com/en-us/fabric/onelake/onelake-access-api). 

![Atlas Triggers and Functions with Azure][7]

#### MongoDB Spark streaming

MongoDB’s [Spark connector v10.1](https://mongodb.prakticum-team.ru/blog/post/introducing-mongodb-spark-connector-version-10-1) provides [streaming capabilities](https://mongodb.prakticum-team.ru/docs/spark-connector/current/structured-streaming/) which allows structured streaming of changes from MongoDB or to MongoDB in both continuous and micro-batch modes. Using the connector, we just need a simple code that reads a stream of changes from the MongoDB collection and writes the stream to the Lakehouse in Microsoft Fabric or to ADLS Gen2 storage which can be referenced in Microsoft Fabric using shortcuts. MongoDB Atlas can be set up as a source for structured streaming by referring to the [MongoDB documentation](https://mongodb.prakticum-team.ru/docs/spark-connector/current/structured-streaming/#configuring-a-read-stream-from-mongodb). Refer to the [Microsoft Fabric documentation](https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-streaming-data) on setting up Lakehouse as Sink for structured streaming. 

![Microsoft Fabric Data Pipeline with MongoDB Atlas][8]

Watch a full demo of MongoDB Atlas integration with Microsoft Fabric:

:youtube[]{vid=0-jTG9Y6nyQ}

## Conclusion

AI is set to reshape businesses and provide innovative solutions across industries. Smart applications can be built with the right database solution combined with the right analytics and AI solution. These applications can thrive, delivering accurate, context-aware, dynamic, data-driven user experiences that meet the growing demands of today's fast-paced digital landscape. MongoDB Atlas data and its operational analytics, when combined with the entire enterprise data estate in Microsoft Fabric to run multiple analytics and reporting at a large scale, is a powerful combination to build such smart applications.

Learn more about [MongoDB Atlas on Azure](https://azure.microsoft.com/en-us/solutions/mongodb/) and get started for free today on [Azure Marketplace](https://azuremarketplace.microsoft.com/en-us/marketplace/apps/mongodb.mongodb_atlas_self_serve_prod_2022?ocid=msft_ignite_fabricblog_FY24). 


  [1]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltc783c7ca51ffc321/655678560e64b945e26edeb7/Fabric_Keynote.png
  [2]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt11079cade4dbe467/6553ef5253e8ec0e05c46baa/image2.png
  [3]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt133326ec100a6ccd/6553ef7a9984b8c9045a9fc6/image5.png
  [4]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltd2c2a8c4741c1849/6553efa09984b8a1685a9fca/image6.png
  [5]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltc1faa0c6b3e2a93d/6553f00253e8ecacacc46bb4/image3.png
  [6]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt4789acc89ff7d1ef/6553f021647c28121d4e84f6/image7.png
  [7]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt58b3b258cfeb5614/6553f0410e64b9dbad6ece06/image1.png
  [8]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltba161d80a4442dd0/6553f06ac2479d218b7822e0/image4.png

Learn how you can use Microsoft Fabric with MongoDB Atlas for intelligent analytics for your data. 

Diana Annie Jenosh

Content by Diana Annie Jenosh

UDF Announcement for MongoDB to BigQuery Dataflow Templates

Unlock the Value of Data in MongoDB Atlas with the Intelligent Analytics of Microsoft Fabric