Copy data from or to MongoDB Atlas using Azure Data Factory or Synapse Analytics

Artikkeli
09/20/2023

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

This article outlines how to use the Copy Activity in an Azure Data Factory or Synapse Analytics pipeline to copy data from and to a MongoDB Atlas database. It builds on the copy activity overview article that presents a general overview of copy activity.

Supported capabilities

This MongoDB Atlas connector is supported for the following capabilities:

Supported capabilities	IR
Copy activity (source/sink)	① ②

① Azure integration runtime ② Self-hosted integration runtime

For a list of data stores that are supported as sources/sinks, see the Supported data stores table.

Prerequisites

If your data store is located inside an on-premises network, an Azure virtual network, or Amazon Virtual Private Cloud, you need to configure a self-hosted integration runtime to connect to it.

If your data store is a managed cloud data service, you can use the Azure Integration Runtime. If the access is restricted to IPs that are approved in the firewall rules, you can add Azure Integration Runtime IPs to the allow list.

You can also use the managed virtual network integration runtime feature in Azure Data Factory to access the on-premises network without installing and configuring a self-hosted integration runtime.

For more information about the network security mechanisms and options supported by Data Factory, see Data access strategies.

Getting started

To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

Create a linked service to MongoDB Atlas using UI

Use the following steps to create a linked service to MongoDB Atlas in the Azure portal UI.

Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:
- Azure Data Factory
- Azure Synapse
Search for MongoDB Atlas and select the MongoDB Atlas connector.
Configure the service details, test the connection, and create the new linked service.

Connector configuration details

The following sections provide details about properties that are used to define Data Factory entities specific to MongoDB Atlas connector.

Linked service properties

The following properties are supported for MongoDB Atlas linked service:

Property	Description	Required
type	The type property must be set to: MongoDbAtlas	Yes
connectionString	Specify the MongoDB Atlas connection string e.g. `mongodb+srv://<username>:<password>@<clustername>.<randomString>.<hostName>/<dbname>?<otherProperties>`. You can also put a connection string in Azure Key Vault. Refer to Store credentials in Azure Key Vault with more details.	Yes
database	Name of the database that you want to access.	Yes
driverVersion	Specify the driver version to v2 which supports MongoDB version 3.6 and higher. For more information, go to this article.	No
connectVia	The Integration Runtime to be used to connect to the data store. Learn more from Prerequisites section. If not specified, it uses the default Azure Integration Runtime.	No

Example:

{
    "name": "MongoDbAtlasLinkedService",
    "properties": {
        "type": "MongoDbAtlas",
        "typeProperties": {
            "connectionString": "mongodb+srv://<username>:<password>@<clustername>.<randomString>.<hostName>/<dbname>?<otherProperties>",
            "database": "myDatabase",
            "driverVersion": "<driver version>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dataset properties

For a full list of sections and properties that are available for defining datasets, see Datasets and linked services. The following properties are supported for MongoDB Atlas dataset:

Property	Description	Required
type	The type property of the dataset must be set to: MongoDbAtlasCollection	Yes
collectionName	Name of the collection in MongoDB Atlas database.	Yes

Example:

{
    "name": "MongoDbAtlasDataset",
    "properties": {
        "type": "MongoDbAtlasCollection",
        "typeProperties": {
            "collectionName": "<Collection name>"
        },
        "schema": [],
        "linkedServiceName": {
            "referenceName": "<MongoDB Atlas linked service name>",
            "type": "LinkedServiceReference"
        }
    }
}

Copy activity properties

For a full list of sections and properties available for defining activities, see the Pipelines article. This section provides a list of properties supported by MongoDB Atlas source and sink.

MongoDB Atlas as source

The following properties are supported in the copy activity source section:

Property	Description	Required
type	The type property of the copy activity source must be set to: MongoDbAtlasSource	Yes
filter	Specifies selection filter using query operators. To return all documents in a collection, omit this parameter or pass an empty document ({}).	No
cursorMethods.project	Specifies the fields to return in the documents for projection. To return all fields in the matching documents, omit this parameter.	No
cursorMethods.sort	Specifies the order in which the query returns matching documents. Refer to cursor.sort().	No
cursorMethods.limit	Specifies the maximum number of documents the server returns. Refer to cursor.limit().	No
cursorMethods.skip	Specifies the number of documents to skip and from where MongoDB Atlas begins to return results. Refer to cursor.skip().	No
batchSize	Specifies the number of documents to return in each batch of the response from MongoDB Atlas instance. In most cases, modifying the batch size will not affect the user or the application. Azure Cosmos DB limits each batch cannot exceed 40MB in size, which is the sum of the batchSize number of documents' size, so decrease this value if your document size being large.	No (the default is 100)

Tip

The service supports consuming BSON document in Strict mode. Make sure your filter query is in Strict mode instead of Shell mode. More description can be found at MongoDB manual.

Example:

"activities":[
    {
        "name": "CopyFromMongoDbAtlas",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<MongoDB Atlas input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "MongoDbAtlasSource",
                "filter": "{datetimeData: {$gte: ISODate(\"2018-12-11T00:00:00.000Z\"),$lt: ISODate(\"2018-12-12T00:00:00.000Z\")}, _id: ObjectId(\"5acd7c3d0000000000000000\") }",
                "cursorMethods": {
                    "project": "{ _id : 1, name : 1, age: 1, datetimeData: 1 }",
                    "sort": "{ age : 1 }",
                    "skip": 3,
                    "limit": 3
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

MongoDB Atlas as sink

The following properties are supported in the Copy Activity sink section:

Property	Description	Required
type	The type property of the Copy Activity sink must be set to MongoDbAtlasSink.	Yes
writeBehavior	Describes how to write data to MongoDB Atlas. Allowed values: insert and upsert. The behavior of upsert is to replace the document if a document with the same `_id` already exists; otherwise, insert the document. Note: The service automatically generates an `_id` for a document if an `_id` isn't specified either in the original document or by column mapping. This means that you must ensure that, for upsert to work as expected, your document has an ID.	No (the default is insert)
writeBatchSize	The writeBatchSize property controls the size of documents to write in each batch. You can try increasing the value for writeBatchSize to improve performance and decreasing the value if your document size being large.	No (the default is 10,000)
writeBatchTimeout	The wait time for the batch insert operation to finish before it times out. The allowed value is timespan.	No (the default is 00:30:00 - 30 minutes)

Tip

To import JSON documents as-is, refer to Import or export JSON documents section; to copy from tabular-shaped data, refer to Schema mapping.

Example

"activities":[
    {
        "name": "CopyToMongoDBAtlas",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Document DB output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "MongoDbAtlasSink",
                "writeBehavior": "upsert"
            }
        }
    }
]

Import and Export JSON documents

You can use this MongoDB Atlas connector to easily:

Copy documents between two MongoDB Atlas collections as-is.
Import JSON documents from various sources to MongoDB Atlas, including from Azure Cosmos DB, Azure Blob storage, Azure Data Lake Store, and other supported file-based stores.
Export JSON documents from a MongoDB Atlas collection to various file-based stores.

To achieve such schema-agnostic copy, skip the "structure" (also called schema) section in dataset and schema mapping in copy activity.

Schema mapping

To copy data from MongoDB Atlas to tabular sink or reversed, refer to schema mapping.

For a list of data stores supported as sources and sinks by the copy activity, see supported data stores.

Jaa

Copy data from or to MongoDB Atlas using Azure Data Factory or Synapse Analytics

Supported capabilities

Prerequisites

Getting started

Create a linked service to MongoDB Atlas using UI

Connector configuration details

Linked service properties

Dataset properties

Copy activity properties

MongoDB Atlas as source

MongoDB Atlas as sink

Import and Export JSON documents

Schema mapping

Palaute

Lisäresursseja

Jaa

Copy data from or to MongoDB Atlas using Azure Data Factory or Synapse Analytics

Supported capabilities

Prerequisites

Getting started

Create a linked service to MongoDB Atlas using UI

Connector configuration details

Linked service properties

Dataset properties

Copy activity properties

MongoDB Atlas as source

MongoDB Atlas as sink

Import and Export JSON documents

Schema mapping

Related content

Palaute

Lisäresursseja