Create database and table policies for Azure Data Explorer

  • Article
  • 09/09/2024

Azure Data Explorer is a fast and highly scalable data exploration service for log and telemetry data. In this article, you'll create database and table policies for Azure Data Explorer by using C# or Python.

For code samples based on previous SDK versions, see the archived article.

Prerequisites

Install packages

Authentication

To run the examples in this article, you need a Microsoft Entra application and service principal that can access resources. If necessary, create a Microsoft Entra application and grant it appropriate role assignments on the subscription. Save the Directory (tenant) ID, Application ID, and Client Secret.

You may need to add the new Microsoft Entra application as a principal in the database. For more information, see Manage Azure Data Explorer database permissions.

Alter database retention policy

The following example sets a retention policy with a 10-day soft-delete period.

var tenantId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; // Azure AD Directory (tenant) ID
var clientId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; // Application ID
var clientSecret = "PlaceholderClientSecret"; // Application secret
var subscriptionId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx";
var credentials = new ClientSecretCredential(tenantId, clientId, clientSecret);
var resourceManagementClient = new ArmClient(credentials, subscriptionId);
var resourceGroupName = "testrg";
// The cluster and database that are created as part of the prerequisites
var clusterName = "mykustocluster";
var databaseName = "mykustodatabase";
var subscription = await resourceManagementClient.GetDefaultSubscriptionAsync();
var resourceGroup = (await subscription.GetResourceGroupAsync(resourceGroupName)).Value;
var cluster = (await resourceGroup.GetKustoClusterAsync(clusterName)).Value;
var database = (await cluster.GetKustoDatabaseAsync(databaseName)).Value;
var databasePatch = new KustoReadWriteDatabase { SoftDeletePeriod = TimeSpan.FromDays(10) };
await database.UpdateAsync(WaitUntil.Completed, databasePatch);

Alter database cache policy

The following example sets a cache policy for the database. The previous five days of data will be on the cluster SSD.

var tenantId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; // Azure AD Directory (tenant) ID
var clientId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; // Application ID
var clientSecret = "PlaceholderClientSecret"; // Application secret
var subscriptionId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx";
var credentials = new ClientSecretCredential(tenantId, clientId, clientSecret);
var resourceManagementClient = new ArmClient(credentials, subscriptionId);
var resourceGroupName = "testrg";
// The cluster and database that are created as part of the prerequisites
var clusterName = "mykustocluster";
var databaseName = "mykustodatabase";
var subscription = await resourceManagementClient.GetDefaultSubscriptionAsync();
var resourceGroup = (await subscription.GetResourceGroupAsync(resourceGroupName)).Value;
var cluster = (await resourceGroup.GetKustoClusterAsync(clusterName)).Value;
var database = (await cluster.GetKustoDatabaseAsync(databaseName)).Value;
var databasePatch = new KustoReadWriteDatabase { HotCachePeriod = TimeSpan.FromDays(5) };
await database.UpdateAsync(WaitUntil.Completed, databasePatch);

Alter table cache policy

The following example sets a cache policy for the table using Kusto Data SDK. This snippet configures the hot cache of the cluster (local SSDs) to hold the most recent five days worth of data.

var kustoUri = "https://<clusterName>.<region>.kusto.chinacloudapi.cn/";
var tenantId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; // Azure AD Directory (tenant) ID
var clientId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; // Application ID
var clientSecret = "PlaceholderClientSecret"; // Application secret
var kustoConnectionStringBuilder = new KustoConnectionStringBuilder(kustoUri)
    .WithAadApplicationKeyAuthentication(clientId, clientSecret, tenantId);
using var kustoClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder);
var command = CslCommandGenerator.GenerateAlterTableCachingPolicyCommand(
    "<tableName>", hotSpan: TimeSpan.FromDays(5)
);
await kustoClient.ExecuteControlCommandAsync("<databaseName>", command);

Add a new principal for the database

The following example adds a new Microsoft Entra application as admin principal for the database.

var tenantId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; // Azure AD Directory (tenant) ID
var clientId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx"; // Application ID
var clientSecret = "PlaceholderClientSecret"; // Application secret
var subscriptionId = "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxx";
var credentials = new ClientSecretCredential(tenantId, clientId, clientSecret);
var resourceManagementClient = new ArmClient(credentials, subscriptionId);
var resourceGroupName = "testrg";
// The cluster and database that are created as part of the prerequisites
var clusterName = "mykustocluster";
var databaseName = "mykustodatabase";
var subscription = await resourceManagementClient.GetDefaultSubscriptionAsync();
var resourceGroup = (await subscription.GetResourceGroupAsync(resourceGroupName)).Value;
var cluster = (await resourceGroup.GetKustoClusterAsync(clusterName)).Value;
var database = (await cluster.GetKustoDatabaseAsync(databaseName)).Value;
var databasePrincipalList = new DatabasePrincipalList();
databasePrincipalList.Value.Add(
    new KustoDatabasePrincipal(
        KustoDatabasePrincipalRole.Admin, "<databasePrincipleName>", KustoDatabasePrincipalType.App
    ) { AppId = clientId }
);
database.AddPrincipals(databasePrincipalList);

Related content