Create database and table policies for Azure Data Explorer

  • Article

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.

Prerequisites

Install packages

Authentication

To run the examples in this article, you need an Azure AD application and service principal that can access resources. If necessary, create an Azure AD 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 Azure AD 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";
// Create a confidential authentication client for Azure AD:
var authClient = ConfidentialClientApplicationBuilder.Create(clientId)
    .WithAuthority($"https://login.microsoftonline.com/{tenantId}")
    .WithClientSecret(clientSecret) // can be replaced by .WithCertificate to authenticate with an X.509 certificate
    .Build();
// Acquire application token
var result = authClient.AcquireTokenForClient(
    new[] { "https://management.core.windows.net/.default" } // Define scopes for accessing Azure management plane
).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 databasePatch = new ReadWriteDatabase(softDeletePeriod: TimeSpan.FromDays(10));
await kustoManagementClient.Databases.UpdateAsync(resourceGroupName, clusterName, databaseName, 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";
// Create a confidential authentication client for Azure AD:
var authClient = ConfidentialClientApplicationBuilder.Create(clientId)
    .WithAuthority($"https://login.microsoftonline.com/{tenantId}")
    .WithClientSecret(clientSecret) // can be replaced by .WithCertificate to authenticate with an X.509 certificate
    .Build();
// Acquire application token
var result = authClient.AcquireTokenForClient(
    new[] { "https://management.core.windows.net/.default" } // Define scopes for accessing Azure management plane
).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 databasePatch = new ReadWriteDatabase(hotCachePeriod: TimeSpan.FromDays(10));
await kustoManagementClient.Databases.UpdateAsync(resourceGroupName, clusterName, databaseName, 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.windows.net/";
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 Azure AD 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";
// Create a confidential authentication client for Azure AD:
var authClient = ConfidentialClientApplicationBuilder.Create(clientId)
    .WithAuthority($"https://login.microsoftonline.com/{tenantId}")
    .WithClientSecret(clientSecret) // can be replaced by .WithCertificate to authenticate with an X.509 certificate
    .Build();
// Acquire application token
var result = authClient.AcquireTokenForClient(
    new[] { "https://management.core.windows.net/.default" } // Define scopes for accessing Azure management plane
).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 databasePrincipalListRequest = new DatabasePrincipalListRequest
{
    Value = new List<DatabasePrincipal> { new("Admin", "<databasePrincipalName>", "App", appId: clientId, tenantName: tenantId) }
};
await kustoManagementClient.Databases.AddPrincipalsAsync(resourceGroupName, clusterName, databaseName, databasePrincipalListRequest);

Next steps