Copy data from an OData source by using Azure Data Factory or Synapse Analytics

Alt
01/05/2024

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 Copy Activity in an Azure Data Factory or Synapse Analytics pipeline to copy data from an OData source. The article builds on Copy Activity, which presents a general overview of Copy Activity.

Supported capabilities

This OData connector is supported for the following capabilities:

Supported capabilities	IR
Copy activity (source/-)	① ②
Lookup activity	① ②

① Azure integration runtime ② Self-hosted integration runtime

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

Specifically, this OData connector supports:

OData version 2.0, 3.0, and 4.0.
Copying data by using one of the following authentications: Anonymous, Basic, Windows, and Microsoft Entra service principal.

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.

Get started

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

Create a linked service to an OData store using UI

Use the following steps to create a linked service to an OData store in the Azure portal UI.

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

Connector configuration details

The following sections provide details about properties you can use to define Data Factory entities that are specific to an OData connector.

Linked service properties

The following properties are supported for an OData linked service:

Property	Description	Required
type	The type property must be set to OData.	Yes
url	The root URL of the OData service.	Yes
authenticationType	The type of authentication used to connect to the OData source. Allowed values are Anonymous, Basic, Windows, and AadServicePrincipal. User-based OAuth isn't supported. You can additionally configure authentication headers in `authHeader` property.	Yes
authHeaders	Additional HTTP request headers for authentication. For example, to use API key authentication, you can select authentication type as “Anonymous” and specify API key in the header.	No
userName	Specify userName if you use Basic or Windows authentication.	No
password	Specify password for the user account you specified for userName. Mark this field as a SecureString type to store it securely. You also can reference a secret stored in Azure Key Vault.	No
servicePrincipalId	Specify the Microsoft Entra application's client ID.	No
aadServicePrincipalCredentialType	Specify the credential type to use for service principal authentication. Allowed values are: `ServicePrincipalKey` or `ServicePrincipalCert`.	No
servicePrincipalKey	Specify the Microsoft Entra application's key. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault.	No
servicePrincipalEmbeddedCert	Specify the base64 encoded certificate of your application registered in Microsoft Entra ID, and ensure the certificate content type is PKCS #12. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault.	No
servicePrincipalEmbeddedCertPassword	Specify the password of your certificate if your certificate is secured with a password. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault.	No
tenant	Specify the tenant information (domain name or tenant ID) under which your application resides. Retrieve it by hovering the mouse in the top-right corner of the Azure portal.	No
aadResourceId	Specify the Microsoft Entra resource you are requesting for authorization.	No
azureCloudType	For service principal authentication, specify the type of Azure cloud environment to which your Microsoft Entra application is registered. Allowed values are AzurePublic, AzureChina, AzureUsGovernment, and AzureGermany. By default, the service's cloud environment is used.	No
connectVia	The Integration Runtime to use to connect to the data store. Learn more from Prerequisites section. If not specified, the default Azure Integration Runtime is used.	No

Example 1: Using Anonymous authentication

{
    "name": "ODataLinkedService",
    "properties": {
        "type": "OData",
        "typeProperties": {
            "url": "https://services.odata.org/OData/OData.svc",
            "authenticationType": "Anonymous"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example 2: Using Basic authentication

{
    "name": "ODataLinkedService",
    "properties": {
        "type": "OData",
        "typeProperties": {
            "url": "<endpoint of OData source>",
            "authenticationType": "Basic",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example 3: Using Windows authentication

{
    "name": "ODataLinkedService",
    "properties": {
        "type": "OData",
        "typeProperties": {
            "url": "<endpoint of OData source>",
            "authenticationType": "Windows",
            "userName": "<domain>\\<user>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example 4: Using service principal key authentication

{
    "name": "ODataLinkedService",
    "properties": {
        "type": "OData",
        "typeProperties": {
            "url": "<endpoint of OData source>",
            "authenticationType": "AadServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "aadServicePrincipalCredentialType": "ServicePrincipalKey",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>",
            "aadResourceId": "<AAD resource URL>"
        }
    },
    "connectVia": {
        "referenceName": "<name of Integration Runtime>",
        "type": "IntegrationRuntimeReference"
    }
}

Example 5: Using service principal cert authentication

{
    "name": "ODataLinkedService",
    "properties": {
        "type": "OData",
        "typeProperties": {
            "url": "<endpoint of OData source>",
            "authenticationType": "AadServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "aadServicePrincipalCredentialType": "ServicePrincipalCert",
            "servicePrincipalEmbeddedCert": { 
                "type": "SecureString", 
                "value": "<base64 encoded string of (.pfx) certificate data>"
            },
            "servicePrincipalEmbeddedCertPassword": { 
                "type": "SecureString", 
                "value": "<password of your certificate>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>",
            "aadResourceId": "<AAD resource e.g. https://tenant.sharepoint.com>"
        }
    },
    "connectVia": {
        "referenceName": "<name of Integration Runtime>",
        "type": "IntegrationRuntimeReference"
    }
}

Example 6: Using API key authentication

{
    "name": "ODataLinkedService",
    "properties": {
        "type": "OData",
        "typeProperties": {
            "url": "<endpoint of OData source>",
            "authenticationType": "Anonymous",
            "authHeader": {
                "APIKey": {
                    "type": "SecureString",
                    "value": "<API key>"
                }
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dataset properties

This section provides a list of properties that the OData dataset supports.

For a full list of sections and properties that are available for defining datasets, see Datasets and linked services.

To copy data from OData, set the type property of the dataset to ODataResource. The following properties are supported:

Property	Description	Required
type	The type property of the dataset must be set to ODataResource.	Yes
path	The path to the OData resource.	Yes

Example

{
    "name": "ODataDataset",
    "properties":
    {
        "type": "ODataResource",
        "schema": [],
        "linkedServiceName": {
            "referenceName": "<OData linked service name>",
            "type": "LinkedServiceReference"
        },
        "typeProperties":
        {
            "path": "Products"
        }
    }
}

Copy Activity properties

This section provides a list of properties that the OData source supports.

For a full list of sections and properties that are available for defining activities, see Pipelines.

OData as source

To copy data from OData, 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 ODataSource.	Yes
query	OData query options for filtering data. Example: `"$select=Name,Description&$top=5"`. Note: The OData connector copies data from the combined URL: `[URL specified in linked service]/[path specified in dataset]?[query specified in copy activity source]`. For more information, see OData URL components.	No
httpRequestTimeout	The timeout (the TimeSpan value) for the HTTP request to get a response. This value is the timeout to get a response, not the timeout to read response data. If not specified, the default value is 00:30:00 (30 minutes).	No

Example

"activities":[
    {
        "name": "CopyFromOData",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<OData input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "ODataSource",
                "query": "$select=Name,Description&$top=5"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

If you were using RelationalSource typed source, it is still supported as-is, while you are suggested to use the new one going forward.

Data type mapping for OData

When you copy data from OData, the following mappings are used between OData data types and interim data types used within the service internally. To learn how Copy Activity maps the source schema and data type to the sink, see Schema and data type mappings.

OData data type	Interim service data type
Edm.Binary	Byte[]
Edm.Boolean	Bool
Edm.Byte	Byte[]
Edm.DateTime	DateTime
Edm.Decimal	Decimal
Edm.Double	Double
Edm.Single	Single
Edm.Guid	Guid
Edm.Int16	Int16
Edm.Int32	Int32
Edm.Int64	Int64
Edm.SByte	Int16
Edm.String	String
Edm.Time	TimeSpan
Edm.DateTimeOffset	DateTimeOffset

Note

OData complex data types (such as Object) aren't supported.

Copy data from Project Online

Project Online requires user-based OAuth, which is not supported by Azure Data Factory. To copy data from Project Online, you can use the OData connector and an access token obtained from tools like Postman.

Caution

The access token expires in 1 hour by default, you need to get a new access token when it expires.

Use Postman to get the access token:

Note

Postman is a used by some developers for testing remote web APIs. However, there are some security and privacy risks associated with its usage. This article does not endorse the use of Postman for production environments. Please use it at your own risk.
1. Navigate to Authorization tab on the Postman Website.
2. In the Type box, select OAuth 2.0, and in the Add authorization data to box, select Request Headers.
3. Fill the following information in the Configure New Token page to get a new access token:
  - Grant type: Select Authorization Code.
  - Callback URL: Enter https://www.localhost.com/.
  - Auth URL: Enter https://login.microsoftonline.com/common/oauth2/authorize?resource=https://<your tenant name>.sharepoint.com. Replace <your tenant name> with your own tenant name.
  - Access Token URL: Enter https://login.microsoftonline.com/common/oauth2/token.
  - Client ID: Enter your Microsoft Entra service principal ID.
  - Client Secret: Enter your service principal secret.
  - Client Authentication: Select Send as Basic Auth header.
4. You will be asked to sign in with your username and password.
5. Once you get your access token, please copy and save it for the next step.
Create the OData linked service:
- Service URL: Enter https://<your tenant name>.sharepoint.com/sites/pwa/_api/Projectdata. Replace <your tenant name> with your own tenant name.
- Authentication type: Select Anonymous.
- Auth headers:
  - Property name: Choose Authorization.
  - Value: Enter Bearer <access token from step 1>.
- Test the linked service.
Create the OData dataset:
1. Create the dataset with the OData linked service created in step 2.
2. Preview data.

Lookup activity properties

To learn details about the properties, check Lookup activity.

For a list of data stores that Copy Activity supports as sources and sinks, see Supported data stores and formats.

Comhroinn trí

Copy data from an OData source by using Azure Data Factory or Synapse Analytics

Supported capabilities

Prerequisites

Get started

Create a linked service to an OData store using UI

Connector configuration details

Linked service properties

Dataset properties

Copy Activity properties

OData as source

Data type mapping for OData

Copy data from Project Online

Lookup activity properties

Aiseolas

Acmhainní breise

Comhroinn trí

Copy data from an OData source by using Azure Data Factory or Synapse Analytics

Supported capabilities

Prerequisites

Get started

Create a linked service to an OData store using UI

Connector configuration details

Linked service properties

Dataset properties

Copy Activity properties

OData as source

Data type mapping for OData

Copy data from Project Online

Lookup activity properties

Related content

Aiseolas

Acmhainní breise