Create an IoT Hub data connection for Azure Data Explorer

1. In the left menu of your Azure Data Explorer cluster, select Databases then select the database that contains your target table.

   

2. Select Data connections and Add data connection. From the dropdown, select IoT Hub.

   

3. Fill out the form with the following information.

   Setting	Field description
   Data connection name	The name of the connection you want to create in Azure Data Explorer
   Subscription	The subscription ID where the Event Hubs resource is located.
   IoT Hub	IoT Hub name
   Shared access policy	The name of the shared access policy. Must have read permissions
   Consumer group	The consumer group defined in the IoT Hub built-in endpoint
   Event system properties	The IoT Hub event system properties. When adding system properties, create or update table schema and mapping to include the selected properties.

   

   Note

   • Event system properties are supported for single-record events.
   • For CSV mapping, properties are added at the beginning of the record. For JSON mapping, properties are added according to the name that appears in the drop-down list.

4. Depending on your use case, you may want to turn on multi-database routing. For more information about database routing, see Events routing.

   

5. Fill out the following routing settings:

   Setting	Suggested value	Field description
   Table name	TestTable	The table you created in testdb.
   Data format	JSON	Supported formats are AVRO, CSV, JSON, ORC, PARQUET, PSV, SCSV, SOHSV, TSV, TXT, TSVE, APACHE AVRO, and W3CLOG.
   Mapping	TestMapping	The mapping you created in testdb, which maps incoming data to the column names and data types of testdb. If not specified, an identity data mapping derived from the table's schema is used.

   

   Note

   • JSON data is parsed as multijson by default. Select Ignore format errors to ingest the data in strict JSON format.
   • If you selected Event system properties, you must include system properties in the table schema and mapping.

6. Select Create.

1. Install the Microsoft.Azure.Management.Kusto NuGet package.

2. Create an Azure AD application principal to use for authentication. You'll need the directory (tenant) ID, application ID, and client secret.

3. Run the following code.

   var tenantId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; //Directory (tenant) ID
   var clientId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; //Application ID
   var clientSecret = "PlaceholderClientSecret"; //Client Secret
   var subscriptionId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx";
   var authClient = ConfidentialClientApplicationBuilder.Create(clientId)
       .WithAuthority($"https://login.microsoftonline.com/{tenantId}")
       .WithClientSecret(clientSecret)
       .Build();
   var result = authClient.AcquireTokenForClient(new[] { "https://management.core.windows.net/" }).ExecuteAsync().Result;
   var credentials = new TokenCredentials(result.AccessToken, result.TokenType);
   var kustoManagementClient = new KustoManagementClient(credentials) { SubscriptionId = subscriptionId };
   var resourceGroupName = "testrg";
   //The cluster and database that are created as part of the Prerequisites
   var clusterName = "mykustocluster";
   var databaseName = "mykustodatabase";
   var iotHubConnectionName = "myiothubconnect";
   //The IoT hub that is created as part of the Prerequisites
   var iotHubResourceId = "/subscriptions/<iotHubSubscriptionId>/resourceGroups/<iotHubResourceGroupName>/providers/Microsoft.Devices/IotHubs/<iotHubName>";
   var sharedAccessPolicyName = "iothubforread";
   var consumerGroup = "$Default";
   var location = "Central US";
   //The table and column mapping are created as part of the Prerequisites
   var tableName = "StormEvents";
   var mappingRuleName = "StormEvents_CSV_Mapping";
   var dataFormat = DataFormat.CSV;
   var databaseRouting = "Multi";
   var iotHubConnectionData = new IotHubDataConnection(
       iotHubResourceId, consumerGroup, sharedAccessPolicyName,
       tableName: tableName, location: location, mappingRuleName: mappingRuleName,
       dataFormat: dataFormat, databaseRouting: databaseRouting
   );
   await kustoManagementClient.DataConnections.CreateOrUpdateAsync(resourceGroupName, clusterName, databaseName, iotHubConnectionName, iotHubConnectionData);
   

   Setting	Suggested value	Field description
   tenantId	xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx	Your tenant ID. Also known as directory ID.
   subscriptionId	xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx	The subscription ID that you use for resource creation.
   clientId	xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx	The client ID of the application that can access resources in your tenant.
   clientSecret	PlaceholderClientSecret	The client secret of the application that can access resources in your tenant.
   resourceGroupName	testrg	The name of the resource group containing your cluster.
   clusterName	mykustocluster	The name of your cluster.
   databaseName	mykustodatabase	The name of the target database in your cluster.
   iotHubConnectionName	myiothubconnect	The desired name of your data connection.
   tableName	StormEvents	The name of the target table in the target database.
   mappingRuleName	StormEvents_CSV_Mapping	The name of your column mapping related to the target table.
   dataFormat	csv	The data format of the message.
   iotHubResourceId	Resource ID	The resource ID of your IoT hub that holds the data for ingestion.
   sharedAccessPolicyName	iothubforread	The name of the shared access policy that defines the permissions for devices and services to connect to IoT Hub.
   consumerGroup	$Default	The consumer group of your event hub.
   location	Central US	The location of the data connection resource.
   databaseRouting	Multi or Single	The database routing for the connection. If you set the value to Single, the data connection is routed to a single database in the cluster as specified in the databaseName setting. If you set the value to Multi, you can override the default target database using the Database ingestion property. For more information, see Events routing.

1. Install the required libraries.

   pip install azure-common
   pip install azure-mgmt-kusto
   

2. Create an Azure AD application principal to use for authentication. You'll need the directory (tenant) ID, application ID, and client secret.

3. Run the following code.

   from azure.mgmt.kusto import KustoManagementClient
   from azure.mgmt.kusto.models import IotHubDataConnection
   from azure.common.credentials import ServicePrincipalCredentials
   
   #Directory (tenant) ID
   tenant_id = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"
   #Application ID
   client_id = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"
   #Client Secret
   client_secret = "xxxxxxxxxxxxxx"
   subscription_id = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"
   credentials = ServicePrincipalCredentials(
           client_id=client_id,
           secret=client_secret,
           tenant=tenant_id
       )
   kusto_management_client = KustoManagementClient(credentials, subscription_id)
   
   resource_group_name = "testrg"
   #The cluster and database that are created as part of the Prerequisites
   cluster_name = "mykustocluster"
   database_name = "mykustodatabase"
   data_connection_name = "myeventhubconnect"
   #The IoT hub that is created as part of the Prerequisites
   iot_hub_resource_id = "/subscriptions/xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/xxxxxx/providers/Microsoft.Devices/IotHubs/xxxxxx";
   shared_access_policy_name = "iothubforread"
   consumer_group = "$Default"
   location = "Central US"
   #The table and column mapping that are created as part of the Prerequisites
   table_name = "StormEvents"
   mapping_rule_name = "StormEvents_CSV_Mapping"
   data_format = "csv"
   database_routing = "Multi"
   
   #Returns an instance of LROPoller, check https://video2.skills-academy.com/python/api/msrest/msrest.polling.lropoller?view=azure-python
   poller = kusto_management_client.data_connections.create_or_update(resource_group_name=resource_group_name, cluster_name=cluster_name, database_name=database_name, data_connection_name=data_connection_name,
                                               parameters=IotHubDataConnection(iot_hub_resource_id=iot_hub_resource_id, shared_access_policy_name=shared_access_policy_name,
                                                                                   consumer_group=consumer_group, table_name=table_name, location=location, mapping_rule_name=mapping_rule_name, data_format=data_format, database_routing=database_routing))
   

   Setting	Suggested value	Field description
   tenant_id	xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx	Your tenant ID. Also known as directory ID.
   subscriptionId	xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx	The subscription ID that you use for resource creation.
   client_id	xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx	The client ID of the application that can access resources in your tenant.
   client_secret	xxxxxxxxxxxxxx	The client secret of the application that can access resources in your tenant.
   resource_group_name	testrg	The name of the resource group containing your cluster.
   cluster_name	mykustocluster	The name of your cluster.
   database_name	mykustodatabase	The name of the target database in your cluster.
   data_connection_name	myeventhubconnect	The desired name of your data connection.
   table_name	StormEvents	The name of the target table in the target database.
   mapping_rule_name	StormEvents_CSV_Mapping	The name of your column mapping related to the target table.
   data_format	csv	The data format of the message.
   iot_hub_resource_id	Resource ID	The resource ID of your IoT hub that holds the data for ingestion.
   shared_access_policy_name	iothubforread	The name of the shared access policy that defines the permissions for devices and services to connect to IoT Hub.
   consumer_group	$Default	The consumer group of your event hub.
   location	Central US	The location of the data connection resource.
   database_routing	Multi or Single	The database routing for the connection. If you set the value to Single, the data connection is routed to a single database in the cluster as specified in the databaseName setting. If you set the value to Multi, you can override the default target database using the Database ingestion property. For more information, see Events routing.

The following example shows an Azure Resource Manager template for adding an IoT Hub data connection. You can edit and deploy the template in the Azure portal by using the form.

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "IotHubs_iothubdemo_name": {
            "type": "string",
            "defaultValue": "iothubdemo",
            "metadata": {
                "description": "Specifies the IoT Hub name."
            }
        },
        "iothubpolices_iothubowner_name": {
            "type": "string",
            "defaultValue": "iothubowner",
            "metadata": {
                "description": "Specifies the shared access policy name."
            }
        },
        "consumergroup_default_name": {
            "type": "string",
            "defaultValue": "$Default",
            "metadata": {
                "description": "Specifies the consumer group of the IoT Hub."
            }
        },
        "Clusters_kustocluster_name": {
            "type": "string",
            "defaultValue": "kustocluster",
            "metadata": {
                "description": "Specifies the name of the cluster"
            }
        },
        "databases_kustodb_name": {
            "type": "string",
            "defaultValue": "kustodb",
            "metadata": {
                "description": "Specifies the name of the database"
            }
        },
        "tables_kustotable_name": {
            "type": "string",
            "defaultValue": "kustotable",
            "metadata": {
                "description": "Specifies the name of the table"
            }
        },
        "mapping_kustomapping_name": {
            "type": "string",
            "defaultValue": "kustomapping",
            "metadata": {
                "description": "Specifies the name of the mapping rule"
            }
        },
        "dataformat_type": {
            "type": "string",
            "defaultValue": "csv",
            "metadata": {
                "description": "Specifies the data format"
            }
        },
           "databaseRouting_type": {
            "type": "string",
            "defaultValue": "Single",
            "metadata": {
                "description": "The database routing for the connection. If you set the value to **Single**, the data connection will be routed to a single database in the cluster as specified in the *databaseName* setting. If you set the value to **Multi**, you can override the default target database using the *Database* EventData property."
            }
        },
        "dataconnections_kustodc_name": {
            "type": "string",
            "defaultValue": "kustodc",
            "metadata": {
                "description": "Name of the data connection to create"
            }
        },
        "subscriptionId": {
            "type": "string",
            "defaultValue": "[subscription().subscriptionId]",
            "metadata": {
                "description": "Specifies the subscriptionId of the IoT Hub"
            }
        },
        "resourceGroup": {
            "type": "string",
            "defaultValue": "[resourceGroup().name]",
            "metadata": {
                "description": "Specifies the resourceGroup of the IoT Hub"
            }
        },
        "location": {
            "type": "string",
            "defaultValue": "[resourceGroup().location]",
            "metadata": {
                "description": "Location for all resources."
            }
        }
    },
    "variables": {
    },
    "resources": [{
            "type": "Microsoft.Kusto/Clusters/Databases/DataConnections",
            "apiVersion": "2019-09-07",
            "name": "[concat(parameters('Clusters_kustocluster_name'), '/', parameters('databases_kustodb_name'), '/', parameters('dataconnections_kustodc_name'))]",
            "location": "[parameters('location')]",
            "kind": "IotHub",
            "properties": {
                "iotHubResourceId": "[resourceId(parameters('subscriptionId'), parameters('resourceGroup'), 'Microsoft.Devices/IotHubs', parameters('IotHubs_iothubdemo_name'))]",
                "consumerGroup": "[parameters('consumergroup_default_name')]",
                "sharedAccessPolicyName": "[parameters('iothubpolices_iothubowner_name')]",
                "tableName": "[parameters('tables_kustotable_name')]",
                "mappingRuleName": "[parameters('mapping_kustomapping_name')]",
                "dataFormat": "[parameters('dataformat_type')]",
                 "databaseRouting": "[parameters('databaseRouting_type')]"
            }
        }
    ]
}

To remove the IoT Hub connection from the Azure portal, do the following:

1. Go to your cluster. From the left menu, select Databases. Then, select the database that contains the target table.
2. From the left menu, select Data connections. Then, select the checkbox next to the relevant IoT Hub data connection.
3. From the top menu bar, select Delete.

To remove the IoT Hub connection, run the following command:

kustoManagementClient.DataConnections.Delete(resourceGroupName, clusterName, databaseName, dataConnectionName);

To remove the IoT Hub connection, run the following command:

kusto_management_client.data_connections.delete(resource_group_name=resource_group_name, cluster_name=kusto_cluster_name, database_name=kusto_database_name, data_connection_name=kusto_data_connection_name)