Copy data to or from Azure Data Explorer using Azure Data Factory or Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article describes how to use the copy activity in Azure Data Factory and Synapse Analytics pipelines 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.

Supported capabilities

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

① Azure integration runtime ② Self-hosted integration runtime

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.

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

• Copy data by using Microsoft Entra 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

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

• The Copy Data tool
• The Azure portal
• The .NET SDK
• The Python SDK
• Azure PowerShell
• The REST API
• The Azure Resource Manager template

Create a linked service to Azure Data Explorer using UI

Use the following steps to create a linked service to Azure Data Explorer in the Azure portal UI.

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

   

2. Search for Explorer and select the Azure Data Explorer (Kusto) connector.

   

3. 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 entities specific to Azure Data Explorer connector.

Linked service properties

The Azure Data Explorer connector supports the following authentication types. See the corresponding sections for details:

• Service principal authentication
• System-assigned managed identity authentication
• User-assigned managed identity authentication

Service principal authentication

To use service principal authentication, follow these steps to get a service principal and to grant permissions:

1. Register an application with the Microsoft identity platform. To learn how, see Quickstart: Register an application with the Microsoft identity platform. Make note of these 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 user role to your database

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

Example: using service principal key authentication

{
    "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>"
            }
        }
    }
}

System-assigned managed identity authentication

To learn more about managed identities for Azure resources, see Managed identities for Azure resources.

To use system-assigned managed identity authentication, follow these steps to grant permissions:

1. Retrieve the managed identity information by copying the value of the managed identity object ID generated along with your factory or Synapse workspace.

2. Grant the managed identity 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 the Database viewer role to your database.
   • As sink, grant the Database ingestor and Database viewer roles to your database.

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

Example: using system-assigned managed identity authentication

{
    "name": "AzureDataExplorerLinkedService",
    "properties": {
        "type": "AzureDataExplorer",
        "typeProperties": {
            "endpoint": "https://<clusterName>.<regionName>.kusto.windows.net ",
            "database": "<database name>",
        }
    }
}

User-assigned managed identity authentication

To learn more about managed identities for Azure resources, see Managed identities for Azure resources

To use user-assigned managed identity authentication, follow these steps:

1. Create one or multiple user-assigned managed identities and grant permission 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

2. Assign one or multiple user-assigned managed identities to your data factory or Synapse workspace, and create credentials for each user-assigned managed identity.

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

Example: using user-assigned managed identity authentication

{
    "name": "AzureDataExplorerLinkedService",
    "properties": {
        "type": "AzureDataExplorer",
        "typeProperties": {
            "endpoint": "https://<clusterName>.<regionName>.kusto.windows.net ",
            "database": "<database name>",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        }
    }
}

Dataset properties

For a full list of sections and properties available for defining datasets, see Datasets. 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:

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

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:

Example:

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

Mapping data flow properties

When transforming data in mapping data flow, you can read from and write to tables in Azure Data Explorer. For more information, see the source transformation and sink transformation in mapping data flows. You can choose to use an Azure Data Explorer dataset or an inline dataset as source and sink type.

Source transformation

The below table lists the properties supported by Azure Data Explorer source. You can edit these properties in the Source options tab.

Azure Data Explorer source script examples

When you use Azure Data Explorer dataset as source type, the associated data flow script is:

source(allowSchemaDrift: true,
	validateSchema: false,
	query: 'table | take 10',
	format: 'query') ~> AzureDataExplorerSource

If you use inline dataset, the associated data flow script is:

source(allowSchemaDrift: true,
    validateSchema: false,
    format: 'query',
    query: 'table | take 10',
    store: 'azuredataexplorer') ~> AzureDataExplorerSource

Sink transformation

The below table lists the properties supported by Azure Data Explorer sink. You can edit these properties in the Settings tab. When using inline dataset, you will see additional settings, which are the same as the properties described in dataset properties section.

Azure Data Explorer sink script examples

When you use Azure Data Explorer dataset as sink type, the associated data flow script is:

IncomingStream sink(allowSchemaDrift: true,
	validateSchema: false,
	format: 'table',
	preSQLs:['pre SQL scripts'],
	postSQLs:['post SQL script'],
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true) ~> AzureDataExplorerSink

If you use inline dataset, the associated data flow script is:

IncomingStream sink(allowSchemaDrift: true,
    validateSchema: false,
    format: 'table',
    store: 'azuredataexplorer',
    skipDuplicateMapInputs: true,
    skipDuplicateMapOutputs: true) ~> AzureDataExplorerSink

Lookup activity properties

For more information about the properties, see Lookup activity.

Related content

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

• Learn more about how to copy data from Azure Data Factory and Synapse Analytics to Azure Data Explorer.