CODEWITHSUNDEEP
azurepowershellazure-powershellazure-rm-templateazure-data-explorer
Markus S.
Markus S.

Reputation: 2812

How to create Table and Ingestion Mapping in Azure Data Explorer via PowerShell or ARM Template

We want to automate the creation and setup of our Azure Data Explorer Cluster.

Provisioning the Cluster and creating a database is no problem via ARM Templates.

Creating a data connection to ingest data streams via event hub is also working via the New-AzKustoDataConnection PowerShell command.

However, this command requires a table and ingestion mapping to be present in the database.

Upvotes: 1

Views: 2689

Answers (2)

dariuszdbr
dariuszdbr

Reputation: 23

You can run a Kusto Query Language script to configure your database during ARM template deployment. A Kusto Query Language script is a list of one or more control commands, each separated by exactly one line break, and is created as a resource that will be accessed with the ARM template. The script can only run control commands that start with the following verbs:

  • .create
  • .create-or-alter
  • .create-merge
  • .alter
  • .alter-merge

The overall flow look like this:

  1. Create the script containing the control commands. For example, the code below is a Kusto Query Language script that create two tables, MyTable and MyTable2:
.create table MyTable (Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int32)
.create table MyTable2 (Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int32)

  1. Upload your Kusto Query Language script to an Azure storage account.

  2. Provide access to this file using shared access signatures (SaS). You can do this with PowerShell, CLI, or .NET.

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": { ... },
    "variables": { },
    "resources": [
        {
            "type": "Microsoft.Kusto/Clusters/Databases/Scripts",
            "apiVersion": "2021-01-01",
            "name": "[concat(concat(parameters('clusterName'), '/'), concat(parameters('databaseName'), '/'), parameters('scriptName'))]",
            "properties": {
                "scriptUrl": "[parameters('scriptUrl')]",
                "scriptUrlSasToken": "[parameters('scriptUrlSasToken')]",
                "continueOnErrors": "[parameters('continueOnErrors')]",
                "forceUpdateTag": "[parameters('forceUpdateTag')]"
            }
        }
    ]
}

More info can be found in the documentation.

Upvotes: 2

Avnera
Avnera

Reputation: 7618

Recently the ability to run a custom script containing KQL commands in the context of the database was added to the Kusto management PowerShell API as well as other languages SDK (such as C#). You can use it to create tables and tables' mappings.

Upvotes: 3

Related Questions