Ingest data from Azure Stream Analytics into Azure Data Explorer

Important

This connector can be used in Real-Time Intelligence in Microsoft Fabric. Use the instructions in this article with the following exceptions:

• If required, create databases using the instructions in Create a KQL database.
• If required, create tables using the instructions in Create an empty table.
• Get query or ingestion URIs using the instructions in Copy URI.
• Run queries in a KQL queryset.

Azure Data Explorer supports data ingestion from Azure Stream Analytics. Azure Stream Analytics is a real-time analytics and complex event-processing engine that's designed to process high volumes of fast streaming data from multiple sources simultaneously.

An Azure Stream Analytics job consists of an input source, a transformation query, and an output connection. You can create, edit, and test Stream Analytics jobs using the Azure portal, Azure Resource Manager (ARM) templates, Azure PowerShell, .NET API, REST API, Visual Studio, and the Stream Analytics no code editor.

In this article, you'll learn how to use a Stream Analytics job to collect data from an event hub and send it to your Azure Data Explorer cluster using the Azure portal or an ARM template.

Prerequisites

• Create a cluster and database and a table.

• Create an event hub using the following sections of the Azure Stream Analytics tutorial:

  • Create an event hub
  • Grant access to the event hub and get a connection string

  Tip

  For testing, we recommend that you download the phone call event generator app from the Microsoft Download Center or get the source code from GitHub. When setting up the Azure Stream Analytics job, you'll configure it to pull data from the event hub and pass it to the Azure Data Explorer output connector.

Create an Azure Data Explorer output connection

Use the following steps to create an Azure Data Explorer output for a Stream Analytics job using the Azure portal or using an ARM template. The connection is used by the Stream Analytics job to send data to a specified Azure Data Explorer table. Once created and job is running, data that flows into the job is ingested into the specified target table.

Important

• The Azure Data Explorer output connector only supports Managed Identity authentication. As part of creating the connector, database monitor and database ingestor permissions are granted to the Azure Stream Analytics job managed identity.
• When setting up the Azure Data Explorer output connector, you specify the target cluster, database, and table name. For ingestion to succeed, all of the columns defined in the Azure Stream Analytics query must match the column names and types in the Azure Data Explorer table. Column names are case-sensitive and can be in any order. If there are columns in the Azure Stream Analytics query that don't map to columns in the Azure Data Explorer table, an error is raised.

Note

• All Azure Stream Analytics inputs are supported. The connector transforms the inputs to CSV format and then imports the data into the specified Azure Data Explorer table.
• Azure Data Explorer has an aggregation (batching) policy for data ingestion, designed to optimize the ingestion process. By default, the policy is configured to 5 minutes, 1000 items or 1 GB of data by default, so you may experience a latency. For information about configuring the aggregation options, see batching policy.

Azure portal

Before you begin, make sure you have an existing Stream Analytics job or create a new one, and then use the following steps to create your Azure Data Explorer connection.

1. Sign in to the Azure portal.

2. From the Azure portal, open All resources, and select your Stream Analytics job.

3. Under Job topology, select the Outputs.

4. Select Add > Azure Data Explorer.

   

5. Fill the output form using the following information and then select Save.

   Note

   You can use the following options to specify your cluster and database:

   • Subscription: Select Select Azure Data Explorer from your subscriptions, select your subscription, and then choose your cluster and database.
   • Manually: Select Provide Azure Data Explorer settings manually, specify the cluster URI and database.

   Property name	Description
   Output alias	A friendly name used in queries to direct the query output to this database.
   Subscription	Select the Azure subscription where your cluster resides.
   Cluster	The unique name that identifies your cluster. The domain name [region].kusto.windows.net is appended to the cluster name you provide. The name can contain only lowercase letters and numbers. It must contain from 4 to 22 characters.
   Cluster URI	The data ingestion URI of your cluster. You can specify the URI for the Azure Data Explorer or Azure Synapse Data Explorer data ingestion endpoints.
   Database	The name of the database where you're sending your output. The database name must be unique within the cluster.
   Authentication	A Microsoft Entra managed identity that allows your cluster to easily access other Microsoft Entra protected resources. The identity is managed by the Azure platform and doesn't require you to provision or rotate any secrets. Managed identity configuration enables you to use customer-managed keys for your cluster.
   Table	The name of the table where you're sending your output. The column names and data types in the Azure Stream Analytics output must match the schema of the Azure Data Explorer table.

   

Azure Resource Manager template

The following example shows an Azure Resource Manager template for adding an Azure Data Explorer output connector. You can edit and deploy the template in the Azure portal to create your connector.

{
    "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "streamingJobName": {
            "type": "string",
            "defaultValue": "streamingjob"
        },
        "eventHubNamespace": {
            "type": "string"
        },
        "eventHubKey": {
            "type": "securestring"
        },
        "eventHubName": {
            "type": "string"
        },
        "eventHubConsumerGroupName": {
            "type": "string"
        },
        "streamingUnits": {
            "type": "int",
            "defaultValue": 3
        },
        "ADXClusterUri": {
            "type": "string"
        },
        "ADXDatabaseName": {
            "type": "string"
        },
        "ADXTableName": {
            "type": "string"
        }
    },
    "resources": [
        {
            "apiVersion": "2017-04-01-preview",
            "name": "[parameters('streamingJobName')]",
            "location": "[resourceGroup().location]",
            "type": "Microsoft.StreamAnalytics/StreamingJobs",
            "identity": {
                "type": "systemAssigned"
            },
            "properties": {
                "sku": {
                    "name": "standard"
                },
                "eventsOutOfOrderPolicy": "drop",
                "eventsOutOfOrderMaxDelayInSeconds": 10,
                "compatibilityLevel": "1.2",
                "outputStartMode": "JobStartTime",
                "inputs": [
                    {
                        "name": "inputEventHub",
                        "properties": {
                            "type": "stream",
                            "serialization": {
                                "type": "JSON",
                                "properties": {
                                    "encoding": "UTF8"
                                }
                            },
                            "datasource": {
                                "type": "Microsoft.ServiceBus/EventHub",
                                "properties": {
                                    "serviceBusNamespace": "[parameters('eventHubNamespace')]",
                                    "sharedAccessPolicyName": "RootManageSharedAccessKey",
                                    "sharedAccessPolicyKey": "[parameters('eventHubKey')]",
                                    "eventHubName": "[parameters('eventHubName')]",
                                    "consumerGroupName": "[parameters('eventHubConsumerGroupName')]"
                                }
                            }
                        }
                    }
                ],
                "transformation": {
                    "name": "ProcessSampleData",
                    "properties": {
                        "streamingUnits": "[int(parameters('streamingUnits'))]",
                        "query": "select * from inputEventHub"
                    }
                },
                "outputs": [
                    {
                        "name": "output",
                        "properties": {
                            "serialization": {
                                "type": "CSV",
                                "properties": {
                                    "encoding": "UTF8"
                                }
                            },
                            "datasource": {
                                "type": "Microsoft.Kusto/clusters/databases",
                                "properties": {
                                    "cluster": "[parameters('ADXClusterUri')]",
                                    "table": "[parameters('ADXTableName')]",
                                    "database": "[parameters('ADXDatabaseName')]",
                                    "authenticationMode": "Msi"
                                }
                            }
                        }
                    }
                ]
            }
        }
    ]
}

Note

When creating an Azure Data Explorer output connection using an ARM template, the Streaming Analytics job managed identity permissions to the Azure Data Explorer database must be granted manually.

Related content

• Query data in Azure Data Explorer
• Run Azure Functions from Azure Stream Analytics jobs