Configure a database using a Kusto Query Language script

You can run a Kusto Query Language script to configure your database during Azure Resource Management (ARM) template deployment. A script is a list of one or more management commands, each separated by one line break, and is created as a resource that is accessed with the ARM template.

The script can only run database-level management commands that start with the following verbs:

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

In general, we recommended using the idempotent version of commands so that if they're called more than once with the same input parameters, they have no additional effect. In other words, running the command multiple times has the same effect as running it once. For example, where possible, we recommend using the idempotent command .create-or-alter over the regular .create command.

There are various methods you can use to configure a database with scripts. In this article, we focus on the following methods using ARM template deployments:

1. Inline script: The script is provided inline as a parameter to a JSON ARM template.
2. Bicep script: The script is provided as a separate file used by a Bicep ARM template.
3. Storage Account: The script is created as a blob in an Azure storage account and its details (URL and shared access signatures (SaS) provided as parameters to the ARM template.

Example script with management commands

The following example is a script with commands that create two tables: MyTable and MyTable2.

.create-merge table MyTable (Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int32)

.create-merge table MyTable2 (Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int32)

Notice the two commands are idempotent. When first run, they create the tables, on subsequent runs they have no effect.

Prerequisites

• An Azure subscription. Create a Azure account.
• An Azure Data Explorer cluster and database. Create a cluster and database.

Security

The principal, such as a user or service principal, used to deploy a script must have the following security roles:

• Contributor role on the cluster
• Admin role on the database

Inline script

Use this method to create an ARM template with the script defined as an inline parameter. If your script has one or more management commands, separate the commands by at least one line break.

Run inline script using an ARM template

The following template shows how to run the script using a JSON Azure Resource Manager template.

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "kqlScript": {
            "defaultValue": ".create-merge table MyTable (Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int32)\n\n.create-merge table MyTable2 (Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int32)",
            "type": "String"
        },
        "forceUpdateTag": {
            "defaultValue": "[utcNow()]",
            "type": "String"
        },
        "continueOnErrors": {
            "defaultValue": false,
            "type": "bool"
        },
        "clusterName": {
            "type": "String"
        },
        "databaseName": {
            "type": "String"
        },
        "scriptName": {
            "type": "String"
        }
    },
    "variables": {
    },
    "resources": [
        {
            "type": "Microsoft.Kusto/Clusters/Databases/Scripts",
            "apiVersion": "2022-02-01",
            "name": "[concat(parameters('clusterName'), '/', parameters('databaseName'), '/', parameters('scriptName'))]",
            "properties": {
                "scriptContent": "[parameters('kqlScript')]",
                "continueOnErrors": "[parameters('continueOnErrors')]",
                "forceUpdateTag": "[parameters('forceUpdateTag')]"
            }
        }
    ],
    "outputs": {
    }
}

Use the following settings:

Setting	Description
kqlScript	The inline Kusto Query Language script. Use \n to add new line characters.
forceUpdateTag	A unique string. If changed, the script is applied again.
continueOnErrors	A flag indicating whether to continue if one of the commands fails. Default value: false.
clusterName	The name of the cluster where the script runs.
databaseName	The name of the database under which the script runs.
scriptName	The name of the script when using an external file to supply the script. This is the name of the actual ARM template resource of type script.

Omit update tag

Running a KQL script at every ARM template deployment isn't recommended as it consumes cluster resources. You can prevent the running of the script in consecutive deployments using the following methods:

• Specify the forceUpdateTag property and keep the same value between deployments.
• Omit the forceUpdateTag property, or leave it empty, and use the same script between deployments.

The best practice is to omit the forceUpdateTag property so that any script changes are run the next time the template is deployed. Only use the forceUpdateTag property if you need to force the script to run.

Bicep script

Passing a script as a parameter to a template can be cumbersome. Bicep Azure Resource Manager template enables you to keep and maintain the script in a separate file and load it into the template using the loadTextContent Bicep function.

Assuming the script is stored in a file script.kql located in the same folder as the Bicep file, the following template produces the same result as the previous example:

param forceUpdateTag string = utcNow()
param continueOnErrors bool = false
param clusterName string
param databaseName string
param scriptName string

resource cluster 'Microsoft.Kusto/clusters@2022-02-01' existing = {
    name: clusterName
}

resource db 'Microsoft.Kusto/clusters/databases@2022-02-01' existing = {
    name: databaseName
    parent: cluster
}

resource perfTestDbs 'Microsoft.Kusto/clusters/databases/scripts@2022-02-01' = {
    name: scriptName
    parent: db
    properties: {
        scriptContent: loadTextContent('script.kql')
        continueOnErrors: continueOnErrors
        forceUpdateTag: forceUpdateTag
    }
}

Use the following settings:

Setting	Description
forceUpdateTag	A unique string. If changed, the script is applied again.
continueOnErrors	A flag indicating to continue if one of the commands fails. Default value: false.
clusterName	The name of the cluster where the script runs.
databaseName	The name of the database under which the script runs.
scriptName	The name of the script when using an external file to supply the script.

The Bicep template can be deployed using similar tools as the JSON ARM template. For example, you can use the following Azure CLI commands to deploy the template:

az deployment group create -n "deploy-$(uuidgen)" -g "MyResourceGroup" --template-file "json-sample.json" --parameters clusterName=MyCluster databaseName=MyDb

Bicep templates are transpiled into JSON ARM template before deployment. In the example, the script file is embedded inline in the JSON ARM template. For more information, see Bicep overview.

Storage account script

This method assumes that you already have a blob in an Azure Storage account and you provide its details (URL and shared access signatures (SaS)) directly in the ARM template.

Create the script resource

The first step is to create a script and upload it to a storage account.

1. Create a script containing the management commands you want to use to create the table in your database.

2. Upload your script to your Azure Storage account. You can create your storage account using the Azure portal, PowerShell, or Azure CLI.

3. Provide access to this file using shared access signatures (SaS). You can provide access using PowerShell, Azure CLI, or .NET.

Run the script using an ARM template

In this section, you learn how to run a script stored in Azure Storage with an Azure Resource Manager template.

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "scriptUrl": {
      "type": "String"
    },
    "scriptUrlSastoken": {
      "type": "SecureString"
    },
    "forceUpdateTag": {
      "defaultValue": "[utcNow()]",
      "type": "String"
    },
    "continueOnErrors": {
      "defaultValue": false,
      "type": "bool"
    },
    "clusterName": {
      "type": "String"
    },
    "databaseName": {
      "type": "String"
    },
    "scriptName": {
      "type": "String"
    }
  },
  "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')]"
      }
    }
  ],
  "outputs": {
  }
}

Use the following settings:

Setting	Description
scriptUrl	The URL of the blob. For example, 'https://myaccount.blob.core.chinacloudapi.cn/mycontainer/myblob'.
scriptUrlSastoken	A string with the shared access signatures (SaS).
forceUpdateTag	A unique string. If changed, the script is applied again.
continueOnErrors	A flag indicating whether to continue if one of the commands fails. Default value: false.
clusterName	The name of the cluster where the script runs.
databaseName	The name of the database under which the script runs.
scriptName	The name of the script when using an external file to supply the script.

Limitations

• Scripts are only supported in Azure Data Explorer; Scripts aren't supported in Synapse Data Explorer pools.
• Two scripts can't be added, modified, or removed in parallel on the same cluster. If this occurs, the following error: Code="ServiceIsInMaintenance" is raised. You can work around the issue by placing a dependency between the two scripts so that they're created or updated sequentially.
• To create functions with cross-cluster queries using scripts, you must set the skipvalidation property to true in the .create function command.

Troubleshooting

Commands run by a script resource don't appear in the results of the .show commands-and-queries command. You can trace the script execution using the .show journal command.

Related content

• Management commands overview