Skip to content

Latest commit

 

History

History
402 lines (324 loc) · 16.9 KB

connector-odata.md

File metadata and controls

402 lines (324 loc) · 16.9 KB
Raw
title titleSuffix description author ms.subservice ms.custom ms.topic ms.date ms.author
Copy data from OData sources
Azure Data Factory & Azure Synapse
Learn how to copy data from OData sources to supported sink data stores using a copy activity in an Azure Data Factory or Synapse Analytics pipeline.
jianleishen
data-movement
synapse
conceptual
01/05/2024
jianleishen

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

[!INCLUDEappliesto-adf-asa-md]

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

[!INCLUDE data-factory-v2-integration-runtime-requirements]

Get started

[!INCLUDE data-factory-v2-connector-get-started]

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.

  1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then select New:

    Azure Data Factory

    :::image type="content" source="media/doc-common-process/new-linked-service.png" alt-text="Screenshot of creating a new linked service with Azure Data Factory UI.":::

    Azure Synapse

    :::image type="content" source="media/doc-common-process/new-linked-service-synapse.png" alt-text="Screenshot of creating a new linked service with Azure Synapse UI.":::

  2. Search for OData and select the OData connector.

    :::image type="content" source="media/connector-odata/odata-connector.png" alt-text="Screenshot of the OData connector.":::

  3. Configure the service details, test the connection, and create the new linked service.

    :::image type="content" source="media/connector-odata/configure-odata-linked-service.png" alt-text="Screenshot of linked service configuration for an OData store.":::

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.

  1. 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.

    :::image type="content" source="./media/connector-odata/odata-project-online-postman-access-token-inline.png" alt-text="Screenshot of using Postman to get the access token." lightbox="./media/connector-odata/odata-project-online-postman-access-token-expanded.png":::

  2. 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.

    :::image type="content" source="./media/connector-odata/odata-project-online-linked-service.png" alt-text="Create OData linked service":::

  3. Create the OData dataset:

    1. Create the dataset with the OData linked service created in step 2.
    2. Preview data.

    :::image type="content" source="./media/connector-odata/odata-project-online-preview-data.png" alt-text="Preview data":::

Lookup activity properties

To learn details about the properties, check Lookup activity.

Related content

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