Copy data to or from Azure Data Explorer by using Azure Data Factory

This article describes how to use the copy activity in Azure Data Factory to copy data to or from Azure Data Explorer. It builds on the copy activity overview article, which offers a general overview of copy activity.

Tip

For Azure Data Factory and Azure Data Explorer integration in general, learn more from Integrate Azure Data Explorer with Azure Data Factory.

Supported capabilities

This Azure Data Explorer connector is supported for the following activities:

You can copy data from any supported source data store to Azure Data Explorer. You can also copy data from Azure Data Explorer to any supported sink data store. For a list of data stores that the copy activity supports as sources or sinks, see the Supported data stores table.

Note

Copying data to or from Azure Data Explorer through an on-premises data store by using self-hosted integration runtime is supported in version 3.14 and later.

With the Azure Data Explorer connector, you can do the following:

  • Copy data by using Azure Active Directory (Azure AD) application token authentication with a service principal.
  • As a source, retrieve data by using a KQL (Kusto) query.
  • As a sink, append data to a destination table.

Getting started

You can use one of the following tools or SDKs to use the copy activity with a pipeline. Select a link for step-by-step instructions:

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

Linked service properties

The Azure Data Explorer connector uses service principal authentication. Follow these steps to get a service principal and to grant permissions:

  1. Register an application entity in Azure Active Directory by following the steps in Register your application with an Azure AD tenant. Make note of the following values, which you use to define the linked service:

    • Application ID
    • Application key
    • Tenant ID
  2. Grant the service principal the correct permissions in Azure Data Explorer. See Manage Azure Data Explorer database permissions for detailed information about roles and permissions and about managing permissions. In general, you must:

    • As source, grant at least the Database viewer role to your database
    • As sink, grant at least the Database ingestor role to your database

Note

When you use the Data Factory UI to author, your login user account is used to list Azure Data Explorer clusters, databases, and tables. Manually enter the name if you don’t have permission for these operations.

The following properties are supported for the Azure Data Explorer linked service:

Property Description Required
type The type property must be set to AzureDataExplorer. Yes
endpoint Endpoint URL of the Azure Data Explorer cluster, with the format as https://<clusterName>.<regionName>.kusto.windows.net. Yes
database Name of database. Yes
tenant Specify the tenant information (domain name or tenant ID) under which your application resides. This is known as "Authority ID" in Kusto connection string. Retrieve it by hovering the mouse pointer in the upper-right corner of the Azure portal. Yes
servicePrincipalId Specify the application's client ID. This is known as "AAD application client ID" in Kusto connection string. Yes
servicePrincipalKey Specify the application's key. This is known as "AAD application key" in Kusto connection string. Mark this field as a SecureString to store it securely in Data Factory, or reference secure data stored in Azure Key Vault. Yes

Linked service properties example:

{
    "name": "AzureDataExplorerLinkedService",
    "properties": {
        "type": "AzureDataExplorer",
        "typeProperties": {
            "endpoint": "https://<clusterName>.<regionName>.kusto.windows.net ",
            "database": "<database name>",
            "tenant": "<tenant name/id e.g. microsoft.onmicrosoft.com>",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            }
        }
    }
}

Dataset properties

For a full list of sections and properties available for defining datasets, see Datasets in Azure Data Factory. This section lists properties that the Azure Data Explorer dataset supports.

To copy data to Azure Data Explorer, set the type property of the dataset to AzureDataExplorerTable.

The following properties are supported:

Property Description Required
type The type property must be set to AzureDataExplorerTable. Yes
table The name of the table that the linked service refers to. Yes for sink; No for source

Dataset properties example:

{
   "name": "AzureDataExplorerDataset",
    "properties": {
        "type": "AzureDataExplorerTable",
        "typeProperties": {
            "table": "<table name>"
        },
        "schema": [],
        "linkedServiceName": {
            "referenceName": "<Azure Data Explorer linked service name>",
            "type": "LinkedServiceReference"
        }
    }
}

Copy activity properties

For a full list of sections and properties available for defining activities, see Pipelines and activities in Azure Data Factory. This section provides a list of properties that Azure Data Explorer sources and sinks support.

Azure Data Explorer as source

To copy data from Azure Data Explorer, set the type property in the Copy activity source to AzureDataExplorerSource. 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: AzureDataExplorerSource Yes
query A read-only request given in a KQL format. Use the custom KQL query as a reference. Yes
queryTimeout The wait time before the query request times out. Default value is 10 min (00:10:00); allowed max value is 1 hour (01:00:00). No
noTruncation Indicates whether to truncate the returned result set. By default, result is truncated after 500,000 records or 64 megabytes (MB). Truncation is strongly recommended to ensure the correct behavior of the activity. No

Note

By default, Azure Data Explorer source has a size limit of 500,000 records or 64 MB. To retrieve all the records without truncation, you can specify set notruncation; at the beginning of your query. For more information, see Query limits.

Example:

"activities":[
    {
        "name": "CopyFromAzureDataExplorer",
        "type": "Copy",
        "typeProperties": {
            "source": {
                "type": "AzureDataExplorerSource",
                "query": "TestTable1 | take 10",
                "queryTimeout": "00:10:00"
            },
            "sink": {
                "type": "<sink type>"
            }
        },
        "inputs": [
            {
                "referenceName": "<Azure Data Explorer input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ]
    }
]

Azure Data Explorer as sink

To copy data to Azure Data Explorer, set the type property in the copy activity sink to AzureDataExplorerSink. 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: AzureDataExplorerSink. Yes
ingestionMappingName Name of a pre-created mapping on a Kusto table. To map the columns from source to Azure Data Explorer (which applies to all supported source stores and formats, including CSV/JSON/Avro formats), you can use the copy activity column mapping (implicitly by name or explicitly as configured) and/or Azure Data Explorer mappings. No

Example:

"activities":[
    {
        "name": "CopyToAzureDataExplorer",
        "type": "Copy",
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "AzureDataExplorerSink",
                "ingestionMappingName": "<optional Azure Data Explorer mapping name>"
            }
        },
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure Data Explorer output dataset name>",
                "type": "DatasetReference"
            }
        ]
    }
]

Lookup activity properties

For more information about the properties, see Lookup activity.

Next steps