Management

• 1: Advanced data management
• 1.1: .clear cluster cache external-artifacts command
• 1.2: Data purge
• 1.3: Delete data
• 1.4: Follower commands
• 1.5: Data soft delete
• 1.5.1: Data soft delete
• 1.5.2: Data soft delete command
• 1.6: Extents (data shards)
• 1.6.1: Extent tags
• 1.6.2: Extents (data shards)
• 1.6.3: Extents commands
• 2: Cross-cluster schema
• 3: Continuous data export
• 3.1: .export to SQL
• 3.2: .export to storage
• 3.3: .export to table
• 3.4: Data export
• 3.5: Continuous data export
• 3.5.1: .create or alter continuous-export
• 3.5.2: .drop continuous-export
• 3.5.3: .show continuous data-export failures
• 3.5.4: .show continuous-export
• 3.5.5: .show continuous-export exported-artifacts
• 3.5.6: Continuous data export
• 3.5.7: Enable or disable continuous data export
• 3.5.8: Use a managed identity to run a continuous export job
• 4: Data ingestion
• 4.1: .ingest inline command (push)
• 4.2: .show data operations
• 4.3: Data formats supported for ingestion
• 4.4: Data ingestion properties
• 4.5: Ingest from query
• 4.5.1: .cancel operation command
• 4.5.2: Kusto query ingestion (set, append, replace)
• 4.6: Kusto.ingest into command (pull data from storage)
• 4.7: Streaming ingestion
• 4.7.1: Clearing cached schema for streaming ingestion
• 4.7.2: Streaming ingestion and schema changes
• 5: Database cursors
• 5.1: Database cursors
• 6: Plugin commands
• 7: Policies
• 7.1: Policies overview
• 7.2: Auto delete
• 7.2.1: Auto delete policy
• 7.3: Caching
• 7.3.1: Caching policy (hot and cold cache)
• 7.4: Callout
• 7.4.1: Callout policy
• 7.5: Capacity
• 7.5.1: Capacity policy
• 7.6: Encoding policy
• 7.6.1: Encoding policy
• 7.7: Extent tags policy
• 7.7.1: Extent tags retention policy
• 7.8: Ingestion batching
• 7.8.1: IngestionBatching policy
• 7.9: Ingestion time
• 7.9.1: IngestionTime policy
• 7.10: Managed identity
• 7.10.1: Kusto ManagedIdentity policy
• 7.11: Merge policy
• 7.11.1: Extents merge policy
• 7.12: Mirroring policy
• 7.12.1: Mirroring policy
• 7.13: Partitioning policy
• 7.13.1: Partitioning policy
• 7.14: Query acceleration policy
• 7.14.1: Query acceleration policy (preview)
• 7.15: Query week consistency policy
• 7.15.1: Query weak consistency policy
• 7.16: Restricted view access
• 7.16.1: Restricted view access policy
• 7.17: Retention policy
• 7.17.1: Retention policy
• 7.18: Row level security policy
• 7.18.1: Row level security policy
• 7.19: Row order policy
• 7.19.1: Row order policy
• 7.20: Sandbox policy
• 7.20.1: Sandbox policy
• 7.20.2: Sandboxes
• 7.21: Sharding policy
• 7.21.1: Data sharding policy
• 7.22: Streaming ingestion policy
• 7.22.1: Streaming ingestion policy
• 7.23: Update policy
• 7.23.1: Common scenarios for using table update policies
• 7.23.2: Run an update policy with a managed identity
• 7.23.3: Update policy overview
• 8: Query results cache
• 8.1: Query results cache commands
• 9: Schema
• 9.1: Avrotize k2a tool
• 9.2: Best practices for schema management
• 9.3: Columns
• 9.3.1: Change column type without data loss
• 9.3.2: Columns management
• 9.4: Databases
• 9.5: External tables
• 9.5.1: Azure SQL external tables
• 9.5.1.1: Create and alter Azure SQL external tables
• 9.5.1.2: Query SQL external tables
• 9.5.1.3: Use row-level security with Azure SQL external tables
• 9.5.2: Azure Storage external tables
• 9.5.2.1: Create and alter Azure Storage delta external tables
• 9.5.2.2: Create and alter Azure Storage external tables
• 9.6: Functions
• 9.6.1: Stored functions management overview
• 9.7: Ingestion mappings
• 9.7.1: AVRO Mapping
• 9.7.2: CSV Mapping
• 9.7.3: Ingestion mappings
• 9.7.4: JSON Mapping
• 9.7.5: ORC Mapping
• 9.7.6: Parquet Mapping
• 9.7.7: W3CLOGFILE Mapping
• 9.8: Manage external table mappings
• 9.9: Materialized views
• 9.9.1: Materialized views
• 9.9.2: Materialized views data purge
• 9.9.3: Materialized views limitations
• 9.9.4: Materialized views policies
• 9.9.5: Materialized views use cases
• 9.9.6: Monitor materialized views
• 9.10: Stored query results
• 9.10.1: Stored query results
• 9.11: Tables
• 9.11.1: Tables management
• 10: Security roles
• 10.1: Manage database security roles
• 10.2: Manage external table roles
• 10.3: Manage function roles
• 10.4: Manage materialized view roles
• 10.5: Referencing security principals
• 10.6: Security roles
• 10.7: Access control
• 10.7.1: Access Control Overview
• 10.7.2: Microsoft Entra application registration
• 10.7.3: Role-based access control
• 10.8: Manage table roles
• 10.8.1: Manage table security roles
• 10.8.2: Manage view access to tables
• 11: Operations
• 11.1: Estimate table size
• 11.2: Journal management
• 11.3: System information
• 11.4: Operations
• 11.5: Queries and commands
• 11.6: Statistics
• 12: Workload groups
• 12.1: Query consistency policy
• 12.2: Request limits policy
• 12.3: Request queuing policy
• 12.4: Request rate limit policy
• 12.5: Request rate limits enforcement policy
• 12.6: Workload groups
• 12.7: Request classification policy
• 12.7.1: Request classification policy
• 12.8: Workload group commands
• 13: Management commands overview

1 - Advanced data management

1.1 - .clear cluster cache external-artifacts command

Clears cached external-artifacts of language plugins.

This command is useful when you update external-artifact files stored in external storage, as the cache may retain the previous versions. In such scenarios, executing this command will clear the cache entries and ensure that subsequent queries run with the latest version of the artifacts.

Permissions

You must have at least Database Admin permissions to run this command.

Syntax

.clear cluster cache external-artifacts ( ArtifactURI [, … ] )

Parameters

Returns

This command returns a table with the following columns:

Example

.clear cluster cache external-artifacts ("https://kustoscriptsamples.blob.core.windows.net/samples/R/sample_script.r", "https://kustoscriptsamples.blob.core.windows.net/samples/python/sample_script.py")

Related content

• Manage language extensions in your Azure Data Explorer cluster
• Python plugin
• R plugin

1.2 - Data purge

The data platform supports the ability to delete individual records, by using Kusto .purge and related commands. You can also purge an entire table or purge records in a materialized view.

Purge guidelines

Carefully design your data schema and investigate relevant policies before storing personal data.

1. In a best-case scenario, the retention period on this data is sufficiently short and data is automatically deleted.
2. If retention period usage isn’t possible, isolate all data that is subject to privacy rules in a few tables. Optimally, use just one table and link to it from all other tables. This isolation allows you to run the data purge process on a few tables holding sensitive data, and avoid all other tables.
3. The caller should make every attempt to batch the execution of .purge commands to 1-2 commands per table per day. Don’t issue multiple commands with unique user identity predicates. Instead, send a single command whose predicate includes all user identities that require purging.

Purge process

The process of selectively purging data happens in the following steps:

1. Phase 1: Give an input with a table name and a per-record predicate, indicating which records to delete. Kusto scans the table looking to identify data extents that would participate in the data purge. The extents identified are those having one or more records for which the predicate returns true.

2. Phase 2: (Soft Delete) Replace each data extent in the table (identified in step (1)) with a reingested version. The reingested version shouldn’t have the records for which the predicate returns true. If new data isn’t being ingested into the table, then by the end of this phase, queries will no longer return data for which the predicate returns true. The duration of the purge soft delete phase depends on the following parameters:

   • The number of records that must be purged
   • Record distribution across the data extents in the cluster
   • The number of nodes in the cluster
   • The spare capacity it has for purge operations
   • Several other factors

   The duration of phase 2 can vary between a few seconds to many hours.

3. Phase 3: (Hard Delete) Work back all storage artifacts that may have the “poison” data, and delete them from storage. This phase is done at least five days after the completion of the previous phase, but no longer than 30 days after the initial command. These timelines are set to follow data privacy requirements.

Issuing a .purge command triggers this process, which takes a few days to complete. If the density of records for which the predicate applies is sufficiently large, the process will effectively reingest all the data in the table. This reingestion has a significant impact on performance and COGS (cost of goods sold).

Purge limitations and considerations

• The purge process is final and irreversible. It isn’t possible to undo this process or recover data that has been purged. Commands such as undo table drop can’t recover purged data. Rollback of the data to a previous version can’t go to before the latest purge command.

• Before running the purge, verify the predicate by running a query and checking that the results match the expected outcome. You can also use the two-step process that returns the expected number of records that will be purged.

• The .purge command is executed against the Data Management endpoint: https://ingest-[YourClusterName].[region].kusto.windows.net. The command requires database admin permissions on the relevant databases.

• Due to the purge process performance impact, and to guarantee that purge guidelines have been followed, the caller is expected to modify the data schema so that minimal tables include relevant data, and batch commands per table to reduce the significant COGS impact of the purge process.

• The predicate parameter of the .purge command is used to specify which records to purge. Predicate size is limited to 1 MB. When constructing the predicate:

  • Use the ‘in’ operator, for example, where [ColumnName] in ('Id1', 'Id2', .. , 'Id1000').
  • Note the limits of the ‘in’ operator (list can contain up to 1,000,000 values).
  • If the query size is large, use externaldata operator, for example where UserId in (externaldata(UserId:string) ["https://...blob.core.windows.net/path/to/file?..."]). The file stores the list of IDs to purge.
  • The total query size, after expanding all externaldata blobs (total size of all blobs), can’t exceed 64 MB.

Purge performance

Only one purge request can be executed on the cluster, at any given time. All other requests are queued in Scheduled state. Monitor the purge request queue size, and keep within adequate limits to match the requirements applicable for your data.

To reduce purge execution time:

• Follow the purge guidelines to decrease the amount of purged data.

• Adjust the caching policy since purge takes longer on cold data.

• Scale out the cluster

• Increase cluster purge capacity, after careful consideration, as detailed in Extents purge rebuild capacity.

Trigger the purge process

Purge table TableName records command

Purge command may be invoked in two ways for differing usage scenarios:

• Programmatic invocation: A single step that is intended to be invoked by applications. Calling this command directly triggers purge execution sequence.

  Syntax

  // Connect to the Data Management service
  #connect "https://ingest-[YourClusterName].[region].kusto.windows.net"
  
  // To purge table records
  .purge table [TableName] records in database [DatabaseName] with (noregrets='true') <| [Predicate]
  
   // To purge materialized view records
  .purge materialized-view [MaterializedViewName] records in database [DatabaseName] with (noregrets='true') <| [Predicate]
  

• Human invocation: A two-step process that requires an explicit confirmation as a separate step. First invocation of the command returns a verification token, which should be provided to run the actual purge. This sequence reduces the risk of inadvertently deleting incorrect data.

[!NOTE] The first step in the two-step invocation requires running a query on the entire dataset, to identify records to be purged. This query may time-out or fail on large tables, especially with significant amount of cold cache data. In case of failures, validate the predicate yourself and after verifying correctness use the single-step purge with the noregrets option.

Syntax

// Connect to the Data Management service - this command only works in Kusto.Explorer
#connect "https://ingest-[YourClusterName].[region].kusto.windows.net"

// Step #1 - retrieve a verification token (no records will be purged until step #2 is executed)
.purge table [TableName] records in database [DatabaseName] <| [Predicate]

// Step #2 - input the verification token to execute purge
.purge table [TableName] records in database [DatabaseName] with (verificationtoken=h'<verification token from step #1>') <| [Predicate]

To purge a materialized view, replace the table keyword with materialized-view, and replace TableName with the MaterializedViewName.

Purge predicate limitations

• The predicate must be a simple selection (for example, where [ColumnName] == ‘X’ / where [ColumnName] in (‘X’, ‘Y’, ‘Z’) and [OtherColumn] == ‘A’).
• Multiple filters must be combined with an ‘and’, rather than separate where clauses (for example, where [ColumnName] == 'X' and OtherColumn] == 'Y' and not where [ColumnName] == 'X' | where [OtherColumn] == 'Y').
• The predicate can’t reference tables other than the table being purged (TableName). The predicate can only include the selection statement (where). It can’t project specific columns from the table (output schema when running ‘table | Predicate’ must match table schema).
• System functions (such as, ingestion_time(), extent_id()) aren’t supported.

Example: Two-step purge

To start purge in a two-step activation scenario, run step #1 of the command:

   // Connect to the Data Management service
   #connect "https://ingest-[YourClusterName].[region].kusto.windows.net"

   .purge table MyTable records in database MyDatabase <| where CustomerId in ('X', 'Y')

   .purge materialized-view MyView records in database MyDatabase <| where CustomerId in ('X', 'Y')

Output

Then, validate the NumRecordsToPurge before running step #2.

To complete a purge in a two-step activation scenario, use the verification token returned from step #1 to run step #2:

.purge table MyTable records in database MyDatabase
 with(verificationtoken=h'e43c7....')
<| where CustomerId in ('X', 'Y')

.purge materialized-view MyView records in database MyDatabase
 with(verificationtoken=h'e43c7....')
<| where CustomerId in ('X', 'Y')

Output

Example: Single-step purge

To trigger a purge in a single-step activation scenario, run the following command:

// Connect to the Data Management service
 #connect "https://ingest-[YourClusterName].[region].kusto.windows.net"

.purge table MyTable records in database MyDatabase with (noregrets='true') <| where CustomerId in ('X', 'Y')

.purge materialized-view MyView records in database MyDatabase with (noregrets='true') <| where CustomerId in ('X', 'Y')

Output

Cancel purge operation command

If needed, you can cancel pending purge requests.

Syntax

 // Cancel of a single purge operation
 .cancel purge <OperationId>

  // Cancel of all pending purge requests in a database
 .cancel all purges in database <DatabaseName>

 // Cancel of all pending purge requests, for all databases
 .cancel all purges

Example: Cancel a single purge operation

 .cancel purge aa894210-1c60-4657-9d21-adb2887993e1

Output

The output of this command is the same as the ‘show purges OperationId’ command output, showing the updated status of the purge operation being canceled. If the attempt is successful, the operation state is updated to Canceled. Otherwise, the operation state isn’t changed.

Example: Cancel all pending purge operations in a database

 .cancel all purges in database MyDatabase

Output

The output of this command is the same as the show purges command output, showing all operations in the database with their updated status. Operations that were canceled successfully will have their status updated to Canceled. Otherwise, the operation state isn’t changed.

Track purge operation status

Status = ‘Completed’ indicates successful completion of the first phase of the purge operation, that is records are soft-deleted and are no longer available for querying. Customers aren’t expected to track and verify the second phase (hard-delete) completion. This phase is monitored internally.

Show purges command

Show purges command shows purge operation status by specifying the operation ID within the requested time period.

.show purges <OperationId>
.show purges [in database <DatabaseName>]
.show purges from '<StartDate>' [in database <DatabaseName>]
.show purges from '<StartDate>' to '<EndDate>' [in database <DatabaseName>]

Examples

.show purges
.show purges c9651d74-3b80-4183-90bb-bbe9e42eadc4
.show purges from '2018-01-30 12:00'
.show purges from '2018-01-30 12:00' to '2018-02-25 12:00'
.show purges from '2018-01-30 12:00' to '2018-02-25 12:00' in database MyDatabase

Output

• OperationId - the DM operation ID returned when executing purge.
• DatabaseName** - database name (case sensitive).
• TableName - table name (case sensitive).
• ScheduledTime - time of executing purge command to the DM service.
• Duration - total duration of the purge operation, including the execution DM queue wait time.
• EngineOperationId - the operation ID of the actual purge executing in the engine.
• State - purge state, can be one of the following values:
  • Scheduled - purge operation is scheduled for execution. If job remains Scheduled, there’s probably a backlog of purge operations. See purge performance to clear this backlog. If a purge operation fails on a transient error, it will be retried by the DM and set to Scheduled again (so you may see an operation transition from Scheduled to InProgress and back to Scheduled).
  • InProgress - the purge operation is in-progress in the engine.
  • Completed - purge completed successfully.
  • BadInput - purge failed on bad input and won’t be retried. This failure may be due to various issues such as a syntax error in the predicate, an illegal predicate for purge commands, a query that exceeds limits (for example, over 1M entities in an externaldata operator or over 64 MB of total expanded query size), and 404 or 403 errors for externaldata blobs.
  • Failed - purge failed and won’t be retried. This failure may happen if the operation was waiting in the queue for too long (over 14 days), due to a backlog of other purge operations or a number of failures that exceed the retry limit. The latter will raise an internal monitoring alert and will be investigated by the team.
• StateDetails - a description of the State.
• EngineStartTime - the time the command was issued to the engine. If there’s a large difference between this time and ScheduledTime, there’s usually a significant backlog of purge operations and the cluster isn’t keeping up with the pace.
• EngineDuration - time of actual purge execution in the engine. If purge was retried several times, it’s the sum of all the execution durations.
• Retries - number of times the operation was retried by the DM service due to a transient error.
• ClientRequestId - client activity ID of the DM purge request.
• Principal - identity of the purge command issuer.

Purging an entire table

Purging a table includes dropping the table, and marking it as purged so that the hard delete process described in Purge process runs on it. Dropping a table without purging it doesn’t delete all its storage artifacts. These artifacts are deleted according to the hard retention policy initially set on the table. The purge table allrecords command is quick and efficient and is preferable to the purge records process, if applicable for your scenario.

Purge table TableName allrecords command

Similar to ‘.purge table records ’ command, this command can be invoked in a programmatic (single-step) or in a manual (two-step) mode.

1. Programmatic invocation (single-step):

   Syntax

   // Connect to the Data Management service
   #connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"
   
   .purge table [TableName] in database [DatabaseName] allrecords with (noregrets='true')
   

2. Human invocation (two-steps):

   Syntax

   
   // Connect to the Data Management service
   #connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"
   
   // Step #1 - retrieve a verification token (the table will not be purged until step #2 is executed)
   
   .purge table [TableName] in database [DatabaseName] allrecords
   
   // Step #2 - input the verification token to execute purge
   .purge table [TableName] in database [DatabaseName] allrecords with (verificationtoken=h'<verification token from step #1>')
   

   Parameters	Description
   DatabaseName	Name of the database.
   TableName	Name of the table.
   noregrets	If set, triggers a single-step activation.
   verificationtoken	In two-step activation scenario (noregrets isn’t set), this token can be used to execute the second step and commit the action. If verificationtoken isn’t specified, it will trigger the command’s first step. In this step, a token is returned to pass back to the command and do step #2.

Example: Two-step purge

1. To start purge in a two-step activation scenario, run step #1 of the command:

   // Connect to the Data Management service
    #connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"
   
   .purge table MyTable in database MyDatabase allrecords
   

   Output

   VerificationToken
   e43c7184ed22f4f23c7a9d7b124d196be2e570096987e5baadf65057fa65736b

2. To complete a purge in a two-step activation scenario, use the verification token returned from step #1 to run step #2:

   .purge table MyTable in database MyDatabase allrecords
   with (verificationtoken=h'eyJT.....')
   

   The output is the same as the ‘.show tables’ command output (returned without the purged table).

   Output

   TableName	DatabaseName	Folder	DocString
   OtherTable	MyDatabase	—	—

Example: Single-step purge

To trigger a purge in a single-step activation scenario, run the following command:

// Connect to the Data Management service
#connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"

.purge table MyTable in database MyDatabase allrecords with (noregrets='true')

The output is the same as the ‘.show tables’ command output (returned without the purged table).

Output

Related content

• Enable data purge on your Azure Data Explorer cluster

1.3 - Delete data

Delete data from a table is supported in several ways. Use the following information to help you choose which deletion method is best for your use case.

The following sections describe the different deletion methods.

Delete all data in a table

To delete all data in a table, use the .clear table data command. This command is the most efficient way to remove all data from a table.

Syntax:

.clear table <TableName> data

Delete data using a retention policy

Automatically delete data based on a retention policy. You can set the retention policy at the database or table level. There’s no guarantee as to when the deletion occurs, but it will not be deleted before the retention period. This is an efficient and convenient way to remove old data.

Consider a database or table that is set for 90 days of retention. If only 60 days of data are needed, delete the older data as follows:

.alter-merge database <DatabaseName> policy retention softdelete = 60d

.alter-merge table <TableName> policy retention softdelete = 60d

Delete data by dropping extents

Extent (data shard) is the internal structure where data is stored. Each extent can hold up to millions of records. Extents can be deleted individually or as a group using drop extent(s) commands.

Examples

You can delete all rows in a table or just a specific extent.

• Delete all rows in a table:

  .drop extents from TestTable
  

• Delete a specific extent:

  .drop extent e9fac0d2-b6d5-4ce3-bdb4-dea052d13b42
  

Delete individual rows

Both purge and soft delete can be used for deleting individual rows. Soft delete doesn’t necessarily delete the storage artifacts that contain records to delete, and purge does delete all such storage artifacts.

Both methods prevent deleted records from being recovered, regardless of any retention or recoverability settings. The deletion process is final and irreversible.

Soft delete

With soft delete, data isn’t necessarily deleted from storage artifacts. This method marks all matching records as deleted, so that they’ll be filtered out in queries, and doesn’t require significant system resources.

Purge

With purge, extents that have one or more records to be deleted, are replaced with new extents in which those records don’t exist. This deletion process isn’t immediate, requires significant system resources, and can take a whole day to complete.

Soft delete can be used for deleting individual rows. Data isn’t necessarily deleted from storage artifacts. Soft delete prevent deleted records from being recovered, regardless of any retention or recoverability settings. The deletion process is final and irreversible. This method marks all matching records as deleted, so that they’ll be filtered out in queries, and doesn’t require significant system resources.

1.4 - Follower commands

Management commands for managing your follower configuration. These commands run synchronously but are applied on the next periodic schema refresh, which may result in a short delay until the new configuration is applied.

The follower commands include database level commands and table level commands.

Permissions

You must have at least Database Admin permissions to run this command.

Database policy overrides

A leader database can override the following database-level policies in the follower cluster: Caching policy and Authorized principals.

Caching policy

The default caching policy for the follower cluster uses the leader cluster database and table-level caching policies.

Authorized principals

Table and materialized views policy overrides

By default, tables and materialized views in a database that is being followed by a follower cluster keep the source entity’s caching policy. However, table and materialized view caching policies can be overridden in the follower cluster. Use the replace option to override the source entity’s caching policy.

Database level commands

.show follower database

Shows a database (or databases) followed from other leader cluster, which have one or more database-level overrides configured.

Syntax

.show follower database DatabaseName

.show follower databases (DatabaseName1,…,DatabaseNameN)

Output

.alter follower database policy caching

Alters a follower database caching policy, to override the one set on the source database in the leader cluster.

Notes

• The default modification kind for caching policies is union. To change the modification kind, use the .alter follower database caching-policies-modification-kind command.
• Viewing the policy or effective policies after the change can be done using the .show commands:
  • .show database policy retention
  • .show database details
  • .show table details
• Viewing the override settings on the follower database after the change is made can be done using .show follower database

Syntax

.alter follower database DatabaseName policy caching hot = HotDataSpan

Example

.alter follower database MyDb policy caching hot = 7d

.delete follower database policy caching

Deletes a follower database override caching policy. This deletion causes the policy set on the source database in the leader cluster the effective one.

Notes

• Viewing the policy or effective policies after the change can be done using the .show commands:
  • .show database policy retention
  • .show database details
  • .show table details
• Viewing the override settings on the follower database after the change can be done using .show follower database

Syntax

.delete follower database DatabaseName policy caching

Example

.delete follower database MyDB policy caching

.add follower database principals

Adds authorized principal(s) to the follower database collection of override authorized principals. Notes

• The default modification kind for such authorized principals is none. To change the modification kind use alter follower database principals-modification-kind.
• Viewing the effective collection of principals after the change can be done using the .show commands:
  • .show database principals
  • .show database details
• Viewing the override settings on the follower database after the change can be done using .show follower database

Syntax

.add follower database DatabaseName (admins | users | viewers | monitors) Role (principal1,…,principalN) ['notes']

Example

.add follower database MyDB viewers ('aadgroup=mygroup@microsoft.com') 'My Group'

.drop follower database principals

Drops authorized principal(s) from the follower database collection of override authorized principals.

Syntax

.drop follower database DatabaseName (admins | users | viewers | monitors) (principal1,…,principalN)

Example

.drop follower database MyDB viewers ('aadgroup=mygroup@microsoft.com')

.alter follower database principals-modification-kind

Alters the follower database authorized principals modification kind.

Syntax

.alter follower database DatabaseName principals-modification-kind = (none | union | replace)

Example

.alter follower database MyDB principals-modification-kind = union

.alter follower database caching-policies-modification-kind

Alters the caching policies modification kind for the follower database, table, and materialized views.

Syntax

.alter follower database DatabaseName caching-policies-modification-kind = (none | union | replace)

Example

.alter follower database MyDB caching-policies-modification-kind = union

.alter follower database prefetch-extents

The follower cluster can wait for new data to be fetched from the underlying storage to the nodes’ SSD (cache) before making this data queryable.

The following command alters the follower database configuration of pre-fetching new extents upon each schema refresh.

Syntax

.alter follower database DatabaseName prefetch-extents = (true | false)

Example

.alter follower database MyDB prefetch-extents = false

Tables and materialized views commands

Alter follower table or materialized view caching policy

Alters a table’s or a materialized view’s caching policy on the follower database, to override the policy set on the source database in the leader cluster.

Syntax

.alter follower database DatabaseName table TableName policy caching hot = HotDataSpan

.alter follower database DatabaseName tables (TableName1,…,TableNameN) policy caching hot = HotDataSpan

.alter follower database DatabaseName materialized-view ViewName policy caching hot = HotDataSpan

.alter follower database DatabaseName materialized-views (ViewName1,…,ViewNameN) policy caching hot = HotDataSpan

Examples

.alter follower database MyDb tables (Table1, Table2) policy caching hot = 7d

.alter follower database MyDb materialized-views (View1, View2) policy caching hot = 7d

Delete follower table or materialized view caching policy

Deletes an override for a table’s or a materialized-view’s caching policy on the follower database. The policy set on the source database in the leader cluster will now be the effective policy.

Syntax

.delete follower database DatabaseName table TableName policy caching

.delete follower database DatabaseName tables (TableName1,…,TableNameN) policy caching

.delete follower database DatabaseName materialized-view ViewName policy caching

.delete follower database DatabaseName materialized-views (ViewName1,…,ViewNameN) policy caching

Example

.delete follower database MyDB tables (Table1, Table2) policy caching

.delete follower database MyDB materialized-views (View1, View2) policy caching

Sample configuration

The following are sample steps to configure a follower database.

In this example:

• Our follower cluster, MyFollowerCluster will be following database MyDatabase from the leader cluster, MyLeaderCluster.

  • MyDatabase has N tables: MyTable1, MyTable2, MyTable3, … MyTableN (N > 3).
  • On MyLeaderCluster:

  MyTable1 caching policy	MyTable2 caching policy	MyTable3…MyTableN caching policy	MyDatabase Authorized principals
  hot data span = 7d	hot data span = 30d	hot data span = 365d	Viewers = aadgroup=scubadivers@contoso.com; Admins = aaduser=jack@contoso.com

  • On MyFollowerCluster we want:

  MyTable1 caching policy	MyTable2 caching policy	MyTable3…MyTableN caching policy	MyDatabase Authorized principals
  hot data span = 1d	hot data span = 3d	hot data span = 0d (nothing is cached)	Admins = aaduser=jack@contoso.com, Viewers = aaduser=jill@contoso.com

Steps to execute

Prerequisite: Set up cluster MyFollowerCluster to follow database MyDatabase from cluster MyLeaderCluster.

Show the current configuration

See the current configuration according to which MyDatabase is being followed on MyFollowerCluster:

.show follower database MyDatabase
| evaluate narrow() // just for presentation purposes

Override authorized principals

Replace the collection of authorized principals for MyDatabase on MyFollowerCluster with a collection that includes only one Microsoft Entra user as the database admin, and one Microsoft Entra user as a database viewer:

.add follower database MyDatabase admins ('aaduser=jack@contoso.com')

.add follower database MyDatabase viewers ('aaduser=jill@contoso.com')

.alter follower database MyDatabase principals-modification-kind = replace

Only those two specific principals are authorized to access MyDatabase on MyFollowerCluster

.show database MyDatabase principals

.show follower database MyDatabase
| mv-expand parse_json(AuthorizedPrincipalsOverride)
| project AuthorizedPrincipalsOverride.Principal.FullyQualifiedName

Override Caching policies

Replace the collection of database and table-level caching policies for MyDatabase on MyFollowerCluster by setting all tables to not have their data cached, excluding two specific tables - MyTable1, MyTable2 - that will have their data cached for periods of 1d and 3d, respectively:

.alter follower database MyDatabase policy caching hot = 0d

.alter follower database MyDatabase table MyTable1 policy caching hot = 1d

.alter follower database MyDatabase table MyTable2 policy caching hot = 3d

.alter follower database MyDatabase caching-policies-modification-kind = replace

Only those two specific tables have data cached, and the rest of the tables have a hot data period of 0d:

.show tables details
| summarize TableNames = make_list(TableName) by CachingPolicy

.show follower database MyDatabase
| mv-expand parse_json(TableMetadataOverrides)
| project TableMetadataOverrides

Summary

See the current configuration where MyDatabase is being followed on MyFollowerCluster:

.show follower database MyDatabase
| evaluate narrow() // just for presentation purposes

1.5 - Data soft delete

1.5.1 - Data soft delete

The ability to delete individual records is supported. Record deletion is commonly achieved using one of the following methods:

• To delete records with a system guarantee that the storage artifacts containing these records are deleted as well, use .purge
• To delete records without such a guarantee, use .delete as described in this article - this command marks records as deleted but doesn’t necessarily delete the data from storage artifacts. This deletion method is faster than purge.

For information on how to use the command, see Syntax

Use cases

This deletion method should only be used for the unplanned deletion of individual records. For example, if you discover that an IoT device is reporting corrupt telemetry for some time, you should consider using this method to delete the corrupt data.

If you need to frequently delete records for deduplication or updates, we recommend using materialized views. See choose between materialized views and soft delete for data deduplication.

Deletion process

The soft delete process is performed using the following steps:

1. Run predicate query: The table is scanned to identify data extents that contain records to be deleted. The extents identified are those with one or more records returned by the predicate query.
2. Extents replacement: The identified extents are replaced with new extents that point to the original data blobs, and also have a new hidden column of type bool that indicates per record whether it was deleted or not. Once completed, if no new data is ingested, the predicate query won’t return any records if run again.

Limitations and considerations

• The deletion process is final and irreversible. It isn’t possible to undo this process or recover data that has been deleted, even though the storage artifacts aren’t necessarily deleted following the operation.

• Soft delete is supported for native tables and materialized views. It isn’t supported for external tables.

• Before running soft delete, verify the predicate by running a query and checking that the results match the expected outcome. You can also run the command in whatif mode, which returns the number of records that are expected to be deleted.

• Don’t run multiple parallel soft delete operations on the same table, as this may result in failures of some or all the commands. However, it’s possible to run multiple parallel soft delete operations on different tables.

• Don’t run soft delete and purge commands on the same table in parallel. First wait for one command to complete and only then run the other command.

• Soft delete is executed against your cluster URI: https://[YourClusterName].[region].kusto.windows.net. The command requires database admin permissions on the relevant database.

• Deleting records from a table that is a source table of a materialized view, can have an impact on the materialized view. If records being deleted were not yet processed by the materialization cycle, these records will be missing in the view, since they will never be processed. Similarly, the deletion will not have an impact on the materialized view if the records have already been processed.

• Limitations on the predicate:

  • It must contain at least one where operator.
  • It can only reference the table from which records are to be deleted.
  • Only the following operators are allowed: extend, order, project, take and where. Within toscalar(), the summarize operator is also allowed.

Deletion performance

The main considerations that can impact the deletion process performance are:

• Run predicate query: The performance of this step is very similar to the performance of the predicate itself. It might be slightly faster or slower depending on the predicate, but the difference is expected to be insignificant.
• Extents replacement: The performance of this step depends on the following:
  • Record distribution across the data extents in the cluster
  • The number of nodes in the cluster

Unlike .purge, the .delete command doesn’t reingest the data. It just marks records that are returned by the predicate query as deleted and is therefore much faster.

Query performance after deletion

Query performance isn’t expected to noticeably change following the deletion of records.

Performance degradation isn’t expected because the filter that is automatically added on all queries that filter out records that were deleted is efficient.

However, query performance is also not guaranteed to improve. While performance improvement may happen for some types of queries, it may not happen for some others. In order to improve query performance, extents in which most of the records are deleted are periodically compacted by replacing them with new extents that only contain the records that haven’t been deleted.

Impact on COGS (cost of goods sold)

In most cases, the deletion of records won’t result in a change of COGS.

• There will be no decrease, because no records are actually deleted. Records are only marked as deleted using a hidden column of type bool, the size of which is negligible.
• In most cases, there will be no increase because the .delete operation doesn’t require the provisioning of extra resources.
• In some cases, extents in which the majority of the records are deleted are periodically compacted by replacing them with new extents that only contain the records that haven’t been deleted. This causes the deletion of the old storage artifacts that contain a large number of deleted records. The new extents are smaller and therefore consume less space in both the Storage account and in the hot cache. However, in most cases, the effect of this on COGS is negligible.

1.5.2 - Data soft delete command

To soft delete individual records without a system guarantee that the storage artifacts containing these records are deleted as well, use the following command. This command marks records as deleted but doesn’t necessarily delete the data from storage artifacts. For more information, see Soft delete.

To delete individual records with a system guarantee that the storage artifacts containing these records are deleted as well, see Data purge.

Syntax

.delete [async] table TableName records [with ( propertyName = propertyValue [, …])] <| Predicate

Parameters

Supported properties

Returns

The output of the command contains information about which extents were replaced.

Example: delete records of a given user

To delete all the records that contain data of a given user:

.delete table MyTable records <| MyTable | where UserId == 'X'

Example: check how many records would be deleted from a table

To determine the number of records that would be deleted by the operation without actually deleting them, check the value in the RecordsMatchPredicate column when running the command in whatif mode:

.delete table MyTable records with (whatif=true) <| MyTable | where UserId == 'X'

.delete materialized-view records - soft delete command

When soft delete is executed on materialized views, the same concepts and limitations apply.

Syntax - materialized views

.delete [async] materialized-view MaterializedViewName records [with ( propertyName = propertyValue [, …])] <| Predicate

Parameters - materialized views

Supported properties - materialized views

Example - materialized views

To delete all the materialized view records that contain data of a given user:

.delete materialized-view MyMaterializedView records <| MyMaterializedView | where UserId == 'X'

Example: check how many records would be deleted from a materialized view

To determine the number of records that would be deleted by the operation without actually deleting them, check the value in the RecordsMatchPredicate column while running the command in whatif mode:

.delete materialized-view MyMaterializedView records with (whatif=true) <| MyMaterializedView | where UserId == 'X'

Related content

• Soft delete
• Materialized views

1.6 - Extents (data shards)

1.6.1 - Extent tags

An extent tag is a string that describes properties common to all data in an extent. For example, during data ingestion, you can append an extent tag to signify the source of the ingested data. Then, you can use this tag for analysis.

Extents can hold multiple tags as part of their metadata. When extents merge, their tags also merge, ensuring consistent metadata representation.

To see the tags associated with an extent, use the .show extents command. For a granular view of tags associated with records within an extent, use the extent-tags() function.

drop-by extent tags

Tags that start with a drop-by: prefix can be used to control which other extents to merge with. Extents that have the same set of drop-by: tags can be merged together, but they won’t be merged with other extents if they have a different set of drop-by: tags.

Examples

Determine which extents can be merged together

If:

• Extent 1 has the following tags: drop-by:blue, drop-by:red, green.
• Extent 2 has the following tags: drop-by:red, yellow.
• Extent 3 has the following tags: purple, drop-by:red, drop-by:blue.

Then:

• Extents 1 and 2 won’t be merged together, as they have a different set of drop-by tags.
• Extents 2 and 3 won’t be merged together, as they have a different set of drop-by tags.
• Extents 1 and 3 can be merged together, as they have the same set of drop-by tags.

Use drop-by tags as part of extent-level operations

The following query issues a command to drop extents according to their drop-by: tag.

.ingest ... with @'{"tags":"[\"drop-by:2016-02-17\"]"}'

.drop extents <| .show table MyTable extents where tags has "drop-by:2016-02-17" 

ingest-by extent tags

Tags with the prefix ingest-by: can be used together with the ingestIfNotExists property to ensure that data is ingested only once.

The ingestIfNotExists property prevents duplicate ingestion by checking if an extent with the specified ingest-by: tag already exists. Typically, an ingest command contains an ingest-by: tag and the ingestIfNotExists property with the same value.

Examples

Add a tag on ingestion

The following command ingests the data and adds the tag ingest-by:2016-02-17.

.ingest ... with (tags = '["ingest-by:2016-02-17"]')

Prevent duplicate ingestion

The following command ingests the data so long as no extent in the table has the ingest-by:2016-02-17 tag.

.ingest ... with (ingestIfNotExists = '["2016-02-17"]')

Prevent duplicate ingestion and add a tag to any new data

The following command ingests the data so long as no extent in the table has the ingest-by:2016-02-17 tag. Any newly ingested data gets the ingest-by:2016-02-17 tag.

.ingest ... with (ingestIfNotExists = '["2016-02-17"]', tags = '["ingest-by:2016-02-17"]')

Limitations

• Extent tags can only be applied to records within an extent. Consequently, tags can’t be set on streaming ingestion data before it is stored in extents.
• Extent tags can’t be stored on data in external tables or materialized views.

Related content

• Extents (data shards) overview
• Extent tags retention policy
• .drop table extent tags

1.6.2 - Extents (data shards)

Tables are partitioned into extents, or data shards. Each extent is a horizontal segment of the table that contains data and metadata such as its creation time and optional tags. The union of all these extents contains the entire dataset of the table. Extents are evenly distributed across nodes in the cluster, and they’re cached in both local SSD and memory for optimized performance.

Extents are immutable, meaning they can be queried, reassigned to a different node, or dropped out of the table but never modified. Data modification happens by creating new extents and transactionally swapping old extents with the new ones. The immutability of extents provides benefits such as increased robustness and easy reversion to previous snapshots.

Extents hold a collection of records that are physically arranged in columns, enabling efficient encoding and compression of the data. To maintain query efficiency, smaller extents are merged into larger extents according to the configured merge policy and sharding policy. Merging extents reduces management overhead and leads to index optimization and improved compression.

The common extent lifecycle is as follows:

1. The extent is created by an ingestion operation.
2. The extent is merged with other extents.
3. The merged extent (possibly one that tracks its lineage to other extents) is eventually dropped because of a retention policy.

Extent creation time

Two datetime values are tracked per extent: MinCreatedOn and MaxCreatedOn. These values are initially the same but may change when the extent is merged with other extents. When the extent is merged with other extents, the new values are according to the original minimum and maximum values of the merged extents.

The creation time of an extent is used for the following purposes:

• Retention: Extents created earlier are dropped earlier.
• Caching: Extents created recently are kept in hot cache.
• Sampling: Recent extents are preferred when using query operations such as take.

To overwrite the creation time of an extent, provide an alternate creationTime in the data ingestion properties. This can be useful for retention purposes, such as if you want to reingest data but don’t want it to appear as if it arrived late.

Related content

• Extent tags
• Merge policy
• Partitioning policy

1.6.3 - Extents commands

2 - Cross-cluster schema

3 - Continuous data export

3.1 - .export to SQL

Export data to SQL allows you to run a query and have its results sent to a table in an SQL database, such as an SQL database hosted by the Azure SQL Database service.

Permissions

You must have at least Table Admin permissions to run this command.

Syntax

.export [async] to sql sqlTableName sqlConnectionString [with (propertyName = propertyValue [, …])] <| query

Parameters

Supported properties

Authentication and authorization

The authentication method is based on the connection string provided, and the permissions required to access the SQL database vary depending on the authentication method.

The supported authentication methods for exporting data to SQL are Microsoft Entra integrated (impersonation) authentication and username/password authentication. For impersonation authentication, be sure that the principal has the following permissions on the database:

• Existing table: table UPDATE and INSERT
• New table: CREATE, UPDATE, and INSERT

Limitations and restrictions

There are some limitations and restrictions when exporting data to an SQL database:

1. Kusto is a cloud service, so the connection string must point to a database that is accessible from the cloud. (In particular, one can’t export to an on-premises database since it’s not accessible from the public cloud.)

2. Kusto supports Active Directory Integrated authentication when the calling principal is a Microsoft Entra principal (aaduser= or aadapp=). Alternatively, Kusto also supports providing the credentials for the SQL database as part of the connection string. Other methods of authentication aren’t supported. The identity being presented to the SQL database always emanates from the command caller not the Kusto service identity itself.

3. If the target table in the SQL database exists, it must match the query result schema. In some cases, such as Azure SQL Database, this means that the table has one column marked as an identity column.

4. Exporting large volumes of data might take a long time. It’s recommended that the target SQL table is set for minimal logging during bulk import. See SQL Server Database Engine > … > Database Features > Bulk Import and Export of Data.

5. Data export is performed using SQL bulk copy and provides no transactional guarantees on the target SQL database. See Transaction and Bulk Copy Operations.

6. The SQL table name is restricted to a name consisting of letters, digits, spaces, underscores (_), dots (.) and hyphens (-).

7. The SQL connection string is restricted as follows: Persist Security Info is explicitly set to false, Encrypt is set to true, and Trust Server Certificate is set to false.

8. The primary key property on the column can be specified when creating a new SQL table. If the column is of type string, then SQL might refuse to create the table due to other limitations on the primary key column. The workaround is to manually create the table in SQL before exporting the data. This limitation exists because primary key columns in SQL can’t be of unlimited size, but Kusto table columns don’t have declared size limitations.

Azure database Microsoft Entra integrated authentication Documentation

• Use Microsoft Entra authentication for authentication with SQL Database

Examples

Asynchronous export to SQL table

In the following example, Kusto runs the query and then exports the first record set produced by the query to the MySqlTable table in the MyDatabase database in server myserver.

.export async to sql MySqlTable
    h@"Server=tcp:myserver.database.windows.net,1433;Authentication=Active Directory Integrated;Initial Catalog=MyDatabase;Connection Timeout=30;"
    <| print Id="d3b68d12-cbd3-428b-807f-2c740f561989", Name="YSO4", DateOfBirth=datetime(2017-10-15)

Export to SQL table if it doesn’t exist

In the following example, Kusto runs the query and then exports the first record set produced by the query to the MySqlTable table in the MyDatabase database in server myserver. The target table is created if it doesn’t exist in the target database.

.export async to sql ['dbo.MySqlTable']
    h@"Server=tcp:myserver.database.windows.net,1433;Authentication=Active Directory Integrated;Initial Catalog=MyDatabase;Connection Timeout=30;"
    with (createifnotexists="true", primarykey="Id")
    <| print Message = "Hello World!", Timestamp = now(), Id=12345678

Related content

• Ingest from query
• Management commands overview
• .export to table
• .export to storage
• Create and alter Azure SQL external tables

3.2 - .export to storage

Executes a query and writes the first result set to an external cloud storage, specified by a storage connection string.

Permissions

You must have at least Database Viewer permissions to run this command.

Syntax

.export [async] [compressed] to OutputDataFormat ( StorageConnectionString [, …] ) [with ( PropertyName = PropertyValue [, …] )] <| Query

Parameters

Supported properties

Authentication and authorization

The authentication method is based on the connection string provided, and the permissions required vary depending on the authentication method.

The following table lists the supported authentication methods and the permissions needed for exporting data to external storage by storage type.

Returns

The commands return a table that describes the generated storage artifacts. Each record describes a single artifact and includes the storage path to the artifact and how many records it holds.

Asynchronous mode

If the async flag is specified, the command executes in asynchronous mode. In this mode, the command returns immediately with an operation ID, and data export continues in the background until completion. The operation ID returned by the command can be used to track its progress and ultimately its results via the following commands:

• .show operations: Track progress.
• .show operation details: Get completion results.

For example, after a successful completion, you can retrieve the results using:

.show operation f008dc1e-2710-47d8-8d34-0d562f5f8615 details

Examples

In this example, Kusto runs the query and then exports the first recordset produced by the query to one or more compressed CSV blobs, up to 1 GB before compression. Column name labels are added as the first row for each blob.

.export
  async compressed
  to csv (
    h@"https://storage1.blob.core.windows.net/containerName;secretKey",
    h@"https://storage1.blob.core.windows.net/containerName2;secretKey"
  ) with (
    sizeLimit=1000000000,
    namePrefix="export",
    includeHeaders="all",
    encoding="UTF8NoBOM"
  )
  <| 
  Logs | where id == "1234" 

Failures during export commands

Export commands can transiently fail during execution. Continuous export automatically retries the command. Regular export commands (export to storage, export to external table) don’t perform any retries.

• When the export command fails, artifacts already written to storage aren’t deleted. These artifacts remain in storage. If the command fails, assume the export is incomplete, even if some artifacts were written.
• The best way to track both completion of the command and the artifacts exported upon successful completion is by using the .show operations and .show operation details commands.

Storage failures

By default, export commands are distributed such that there might be many concurrent writes to storage. The level of distribution depends on the type of export command:

• The default distribution for regular .export command is per_shard, which means all extents that contain data to export write to storage concurrently.

• The default distribution for export to external table commands is per_node, which means the concurrency is the number of nodes.

When the number of extents/nodes is large, this might lead to high load on storage that results in storage throttling, or transient storage errors. The following suggestions might overcome these errors (by order of priority):

• Increase the number of storage accounts provided to the export command or to the external table definition. The load is evenly distributed between the accounts.

• Reduce the concurrency by setting the distribution hint to per_node (see command properties).

• Reduce concurrency of number of nodes exporting by setting the client request property query_fanout_nodes_percent to the desired concurrency (percent of nodes). The property can be set as part of the export query. For example, the following command limits the number of nodes writing to storage concurrently to 50% of the nodes:

  .export async  to csv
      ( h@"https://storage1.blob.core.windows.net/containerName;secretKey" ) 
      with
      (
          distribution="per_node"
      ) 
      <| 
      set query_fanout_nodes_percent = 50;
      ExportQuery
  

• Reduce concurrency of number of threads exporting in each node when using per shard export, by setting the client request property query_fanout_threads_percent to the desired concurrency (percent of threads). The property can be set as part of the export query. For example, the following command limits the number of threads writing to storage concurrently to 50% on each of the nodes:

  .export async  to csv
      ( h@"https://storage1.blob.core.windows.net/containerName;secretKey" ) 
      with
      (
          distribution="per_shard"
      ) 
      <| 
      set query_fanout_threads_percent = 50;
      ExportQuery
  

• If exporting to a partitioned external table, setting the spread/concurrency properties can reduce concurrency (see details in the command properties.

• If neither of the previous recommendations work, you can completely disable distribution by setting the distributed property to false. However, we don’t recommend doing so, as it might significantly affect the command performance.

Authorization failures

Authentication or authorization failures during export commands can occur when the credentials provided in the storage connection string aren’t permitted to write to storage. If you’re using impersonate or a user-delegated SAS token for the export command, the Storage Blob Data Contributor role is required to write to the storage account. For more information, see Storage connection strings.

Data types mapping

Parquet data types mapping

On export, Kusto data types are mapped to Parquet data types using the following rules:

Related content

• Management commands overview
• .continuous data export
• .ingest into
• .export to table
• .export to SQL

3.3 - .export to table

You can export data by defining an external table and exporting data to it. The table properties are specified when creating the external table. The export command references the external table by name.

Permissions

You must have at least Table Admin permissions to run this command.

Syntax

.export [async] to table externalTableName
[with (propertyName = propertyValue [, …])] <| query

Parameters

Supported properties

The following properties are supported as part of the export to external table command.

Distribution settings

The distribution of an export to external table operation indicates the number of nodes and threads that are writing to storage concurrently. The default distribution depends on the external table partitioning:

Change the default distribution settings

Changing the default distribution settings can be useful in the following cases:

Authentication and authorization

In order to export to an external table, you must set up write permissions. For more information, see the Write permissions for Azure Storage external table or SQL Server external table.

Output

Notes

• The export query output schema must match the schema of the external table, including all columns defined by the partitions. For example, if the table is partitioned by DateTime, the query output schema must have a Timestamp column matching the TimestampColumnName. This column name is defined in the external table partitioning definition.

• It isn’t possible to override the external table properties using the export command. For example, you can’t export data in Parquet format to an external table whose data format is CSV.

• If the external table is partitioned, exported artifacts are written to their respective directories according to the partition definitions. For an example, see partitioned external table example.

  • If a partition value is null/empty or is an invalid directory value, per the definitions of the target storage, the partition value is replaced with a default value of __DEFAULT_PARTITION__.

• For suggestions to overcome storage errors during export commands, see failures during export commands.

• External table columns are mapped to suitable target format data types, according to data types mapping rules.

• Parquet native export is a more performant, resource light export mechanism. An exported datetime column is currently unsupported by Synapse SQL COPY.

Number of files

The number of files written per partition depends on the distribution settings of the export operation:

• If the external table includes datetime partitions only, or no partitions at all, the number of files written for each partition that exists, should be similar to the number of nodes (or more, if sizeLimit is reached). When the export operation is distributed, all nodes export concurrently. To disable distribution, so that only a single node does the writes, set distributed to false. This process creates fewer files, but reduces the export performance.

• If the external table includes a partition by a string column, the number of exported files should be a single file per partition (or more, if sizeLimit is reached). All nodes still participate in the export (operation is distributed), but each partition is assigned to a specific node. Setting distributed to false, causes only a single node to do the export, but behavior remains the same (a single file written per partition).

Examples

Non-partitioned external table example

The following example exports data from table T to the ExternalBlob table. ExternalBlob is a non-partitioned external table.

.export to table ExternalBlob <| T

Output

Partitioned external table example

The following example first creates a partitioned external table, PartitionedExternalBlob with a specified blob storage location. The data is stored in CSV format with a path format which organizes the data by customer name and date.

.create external table PartitionedExternalBlob (Timestamp:datetime, CustomerName:string) 
kind=blob
partition by (CustomerName:string=CustomerName, Date:datetime=startofday(Timestamp))   
pathformat = ("CustomerName=" CustomerName "/" datetime_pattern("yyyy/MM/dd", Date))   
dataformat=csv
( 
   h@'http://storageaccount.blob.core.windows.net/container1;secretKey'
)

It then exports data from table T to the PartitionedExternalBlob external table.

.export to table PartitionedExternalBlob <| T

Output

If the command is executed asynchronously by using the async keyword, the output is available using the show operation details command.

Related content

• Continuous data export
• Management commands overview
• External tables
• .export to SQL
• .export to storage

3.4 - Data export

Data export involves executing a Kusto query and saving its results. This process can be carried out either on the client side or the service side.

For examples on data export, see Related content.

Client-side export

Client-side export gives you control over saving query results either to the local file system or pushing them to a preferred storage location. This flexibility is facilitated by using Kusto client libraries. You can create an app to run queries, read the desired data, and implement an export process tailored to your requirements.

Alternatively, you can use a client tool like the Azure Data Explorer web UI to export data from your Kusto cluster. For more information, see Share queries.

Service-side export (pull)

Use the ingest from query commands to pull query results into a table in the same or different database. See the performance tips before using these commands.

Service-side export (push)

For scalable data export, the service offers various .export management commands to push query results to cloud storage, an external table, or an SQL table. This approach enhances scalability by avoiding the bottleneck of streaming through a single network connection.

Continuous data export is supported for export to external tables.

Related content

• Export to cloud storage
• Export to an external table
• Export to a SQL table
• Continuous data export overview

3.5 - Continuous data export

3.5.1 - .create or alter continuous-export

Creates or alters a continuous export job.

Permissions

You must have at least Database Admin permissions to run this command.

Syntax

.create-or-alter continuous-export continuousExportName [over (T1, T2 )] to table externalTableName [with (propertyName = propertyValue [, …])] <| query

Parameters

Supported properties

Example

The following example creates or alters a continuous export MyExport that exports data from the T table to ExternalBlob. The data exports occur every hour, and have a defined forced latency and size limit per storage artifact.

.create-or-alter continuous-export MyExport
over (T)
to table ExternalBlob
with
(intervalBetweenRuns=1h, 
 forcedLatency=10m, 
 sizeLimit=104857600)
<| T

Related content

• Continuous export overview
• External tables
• Disable or enable continuous export
• .show continuous-exports
• .show continuous-export
• .drop continuous-export
• .show continuous-export failures

3.5.2 - .drop continuous-export

Drops a continuous-export job.

Permissions

You must have at least Database Admin permissions to run this command.

Syntax

.drop continuous-export ContinuousExportName

Parameters

Returns

The remaining continuous exports in the database (post deletion). Output schema as in the show continuous export command.

Related content

• Continuous export overview
• External tables
• .create or alter continuous-export
• .disable or enable continuous-export
• .show continuous-export
• .show continuous-export failures

3.5.3 - .show continuous data-export failures

Returns all failures logged as part of the continuous export within the past 14 days. To view only a specific time range, filter the results by the Timestamp column.

The command doesn’t return any results if executed on a follower database, it must be executed against the leader database.

The command doesn’t return any results if executed on a database shortcut, it must be executed against the leader database.

Permissions

You must have at least Database Monitor or Database Admin permissions to run this command. For more information, see role-based access control.

Syntax

.show continuous-export ContinuousExportName failures

Parameters

Returns

Example

The following example shows failures from the continuous export MyExport.

.show continuous-export MyExport failures 

Output

Related content

• Continuous data export overview
• External tables
• .create or alter continuous-export
• .disable or enable continuous-export
• .show continuous-export
• .drop continuous-export

3.5.4 - .show continuous-export

Returns the properties of a specified continuous export or all continuous exports in the database.

Permissions

You must have at least Database User, Database Viewer, or Database Monitor permissions to run this command. For more information, see role-based access control.

Syntax

.show continuous-export ContinuousExportName

.show continuous-exports

Parameters

Returns

Related content

• Continuous data export overview
• External tables
• .create or alter continuous-export
• .disable or enable continuous-export
• .drop continuous-export
• .show continuous-export failures

3.5.5 - .show continuous-export exported-artifacts

Returns all artifacts exported by the continuous-export in all runs. Filter the results by the Timestamp column in the command to view only records of interest. The history of exported artifacts is retained for 14 days.

The command doesn’t return any results if executed on a follower database, it must be executed against the leader database.

The command doesn’t return any results if executed on a database shortcut, it must be executed against the leader database.

Permissions

You must have at least Database Monitor or Database Admin permissions to run this command. For more information, see role-based access control.

Syntax

.show continuous-export ContinuousExportName exported-artifacts

Parameters

Returns

Example

The following example shows retrieved artifacts from the continuous export MyExport that were exported within the last hour.

.show continuous-export MyExport exported-artifacts | where Timestamp > ago(1h)

Output

Related content

• Continuous data export overview
• External tables
• .show continuous-export failures

3.5.6 - Continuous data export

This article describes continuous export of data from Kusto to an external table with a periodically run query. The results are stored in the external table, which defines the destination, such as Azure Blob Storage, and the schema of the exported data. This process guarantees that all records are exported “exactly once”, with some exceptions.

By default, continuous export runs in a distributed mode, where all nodes export concurrently, so the number of artifacts depends on the number of nodes. Continuous export isn’t designed for low-latency streaming data.

To enable continuous data export, create an external table and then create a continuous export definition pointing to the external table.

In some cases, you must use a managed identity to successfully configure a continuous export job. For more information, see Use a managed identity to run a continuous export job.

Permissions

All continuous export commands require at least Database Admin permissions.

Continuous export guidelines

• Output schema:

  • The output schema of the export query must match the schema of the external table to which you export.

• Frequency:

  • Continuous export runs according to the time period configured for it in the intervalBetweenRuns property. The recommended value for this interval is at least several minutes, depending on the latencies you’re willing to accept. The time interval can be as low as one minute, if the ingestion rate is high.

    [!NOTE] The intervalBetweenRuns serves as a recommendation only, and isn’t guaranteed to be precise. Continuous export isn’t suitable for exporting periodic aggregations. For example, a configuration of intervalBetweenRuns=1h with an hourly aggregation (T | summarize by bin(Timestamp, 1h)) won’t work as expected, since the continuous export won’t run exactly on-the-hour. Therefore, each hourly bin will receive multiple entries in the exported data.

• Number of files:

  • The number of files exported in each continuous export iteration depends on how the external table is partitioned. For more information, see export to external table command. Each continuous export iteration always writes to new files, and never appends to existing ones. As a result, the number of exported files also depends on the frequency in which the continuous export runs. The frequency parameter is intervalBetweenRuns.

• External table storage accounts:

  • For best performance, the database and the storage accounts should be colocated in the same Azure region.
  • Continuous export works in a distributed manner, such that all nodes are exporting concurrently. On large databases, and if the exported data volume is large, this might lead to storage throttling. The recommendation is to configure multiple storage accounts for the external table. For more information, see storage failures during export commands.

Exactly once export

To guarantee “exactly once” export, continuous export uses database cursors. The continuous export query shouldn’t include a timestamp filter - the database cursors mechanism ensures that records aren’t processed more than once. Adding a timestamp filter in the query can lead to missing data in exported data.

IngestionTime policy must be enabled on all tables referenced in the query that should be processed “exactly once” in the export. The policy is enabled by default on all newly created tables.

The guarantee for “exactly once” export is only for files reported in the show exported artifacts command. Continuous export doesn’t guarantee that each record is written only once to the external table. If a failure occurs after export begins and some of the artifacts were already written to the external table, the external table might contain duplicates. If a write operation was aborted before completion, the external table might contain corrupted files. In such cases, artifacts aren’t deleted from the external table, but they aren’t reported in the show exported artifacts command. Consuming the exported files using the show exported artifacts command guarantees no duplications and no corruptions.

Export from fact and dimension tables

By default, all tables referenced in the export query are assumed to be fact tables. As such, they’re scoped to the database cursor. The syntax explicitly declares which tables are scoped (fact) and which aren’t scoped (dimension). See the over parameter in the create command for details.

The export query includes only the records that joined since the previous export execution. The export query might contain dimension tables in which all records of the dimension table are included in all export queries. When using joins between fact and dimension tables in continuous-export, keep in mind that records in the fact table are only processed once. If the export runs while records in the dimension tables are missing for some keys, records for the respective keys are either missed or include null values for the dimension columns in the exported files. Returning missed or null records depends on whether the query uses inner or outer join. The forcedLatency property in the continuous-export definition can be useful in such cases, where the fact and dimensions tables are ingested during the same time for matching records.

Monitor continuous export

Monitor the health of your continuous export jobs using the following export metrics:

• Continuous export max lateness - Max lateness (in minutes) of continuous exports in the database. This is the time between now and the min ExportedTo time of all continuous export jobs in database. For more information, see .show continuous export command.
• Continuous export result - Success/failure result of each continuous export execution. This metric can be split by the continuous export name.

Use the .show continuous export failures command to see the specific failures of a continuous export job.

Resource consumption

• The impact of the continuous export on the database depends on the query the continuous export is running. Most resources, such as CPU and memory, are consumed by the query execution.
• The number of export operations that can run concurrently is limited by the database’s data export capacity. For more information, see Management commands throttling. If the database doesn’t have sufficient capacity to handle all continuous exports, some start lagging behind.
• The show commands-and-queries command can be used to estimate the resources consumption.
  • Filter on | where ClientActivityId startswith "RunContinuousExports" to view the commands and queries associated with continuous export.

Export historical data

Continuous export starts exporting data only from the point of its creation. Records ingested before that time should be exported separately using the non-continuous export command. Historical data might be too large to be exported in a single export command. If needed, partition the query into several smaller batches.

To avoid duplicates with data exported by continuous export, use StartCursor returned by the show continuous export command and export only records where cursor_before_or_at the cursor value. For example:

.show continuous-export MyExport | project StartCursor

Followed by:

.export async to table ExternalBlob
<| T | where cursor_before_or_at("636751928823156645")

Continuous export from a table with Row Level Security

To create a continuous export job with a query that references a table with Row Level Security policy, you must:

• Provide a managed identity as part of the continuous export configuration. For more information, see Use a managed identity to run a continuous export job.
• Use impersonation authentication for the external table to which the data is exported.

Continuous export to delta table - Preview

Continuous export to a delta table is currently in preview.

To define continuous export to a delta table, do the following steps:

1. Create an external delta table, as described in Create and alter delta external tables on Azure Storage.

   [!NOTE] If the schema isn’t provided, Kusto will try infer it automatically if there is already a delta table defined in the target storage container.
   Delta table partitioning isn’t supported.

2. Define continuous export to this table using the commands described in Create or alter continuous export.

   [!IMPORTANT] The schema of the delta table must be in sync with the continuous export query. If the underlying delta table changes, the export might start failing with unexpected behavior.

Limitations

General:

• The following formats are allowed on target tables: CSV, TSV, JSON, and Parquet.
• Continuous export isn’t designed to work over materialized views, since a materialized view might be updated, while data exported to storage is always appended and never updated.
• Continuous export can’t be created on follower databases since follower databases are read-only and continuous export requires write operations.
• Records in source table must be ingested to the table directly, using an update policy, or ingest from query commands. If records are moved into the table using .move extents or using .rename table, continuous export might not process these records. See the limitations described in the Database Cursors page.
• If the artifacts used by continuous export are intended to trigger Event Grid notifications, see the known issues section in the Event Grid documentation.

Cross-database and cross-cluster:

• Continuous export doesn’t support cross-cluster calls.
• Continuous export supports cross-database calls only for dimension tables. All fact tables must reside in the local database. See more details in Export from fact and dimension tables.
• If the continuous export includes cross-database calls, it must be configured with a managed identity.

Cross-database and cross-Eventhouse:

• Continuous export doesn’t support cross-Eventhouse calls.
• Continuous export supports cross-database calls only for dimension tables. All fact tables must reside in the local database. See more details in Export from fact and dimension tables.

Policies:

• Continuous export can’t be enabled on a table with Row Level Security policy unless specific conditions are met. For more information, see Continuous export from a table with Row Level Security.
• Continuous export can’t be configured on a table with restricted view access policy.

Related content

• .create or alter continuous-export

• External tables

• .create or alter continuous-export

• External tables

• Use a managed identity to run a continuous export job

3.5.7 - Enable or disable continuous data export

Disables or enables the continuous-export job. A disabled continuous export isn’t executed, but its current state is persisted and can be resumed when the continuous export is enabled.

When enabling a continuous export that was disabled for a long time, exporting continues from where it last stopped when the exporting was disabled. This continuation might result in a long running export, blocking other exports from running, if there isn’t sufficient database capacity to serve all processes. Continuous exports are executed by last run time in ascending order so the oldest export runs first, until catch up is complete.

Permissions

You must have at least Database Admin permissions to run these commands.

Syntax

.enable continuous-export ContinuousExportName

.disable continuous-export ContinuousExportName

Parameters

Returns

The result of the show continuous export command of the altered continuous export.

Related content

• Continuous export overview
• External tables
• .create or alter continuous-export
• .drop continuous-export
• .show continuous-export
• .show continuous-export failures

3.5.8 - Use a managed identity to run a continuous export job

A continuous export job exports data to an external table with a periodically run query.

The continuous export job should be configured with a managed identity in the following scenarios:

• When the external table uses impersonation authentication
• When the query references tables in other databases
• When the query references tables with an enabled row level security policy

A continuous export job configured with a managed identity is performed on behalf of the managed identity.

In this article, you learn how to configure a system-assigned or user-assigned managed identity and create a continuous export job using that identity.

Prerequisites

• A cluster and database. Create a cluster and database.
• All Databases Admin permissions on the database.

Configure a managed identity

There are two types of managed identities:

• System-assigned: A system-assigned identity is connected to your cluster and is removed when the cluster is removed. Only one system-assigned identity is allowed per cluster.

• User-assigned: A user-assigned managed identity is a standalone Azure resource. Multiple user-assigned identities can be assigned to your cluster.

Select one of the following tabs to set up your preferred managed identity type.

User-assigned

1. Follow the steps to Add a user-assigned identity.

2. In the Azure portal, in the left menu of your managed identity resource, select Properties. Copy and save the Tenant Id and Principal Id for use in the following steps.

   

3. Run the following .alter-merge policy managed_identity command, replacing <objectId> with the managed identity object ID from the previous step. This command sets a managed identity policy on the cluster that allows the managed identity to be used with continuous export.

   .alter-merge cluster policy managed_identity ```[
       {
         "ObjectId": "<objectId>",
         "AllowedUsages": "AutomatedFlows"
       }
   ]```
   

   [!NOTE] To set the policy on a specific database, use database <DatabaseName> instead of cluster.

4. Run the following command to grant the managed identity Database Viewer permissions over all databases used for the continuous export, such as the database that contains the external table.

   .add database <DatabaseName> viewers ('aadapp=<objectId>;<tenantId>')
   

   Replace <DatabaseName> with the relevant database, <objectId> with the managed identity Principal Id from step 2, and <tenantId> with the Microsoft Entra ID Tenant Id from step 2.

System-assigned

1. Follow the steps to Add a system-assigned identity.

2. Copy and save the Object (principal) ID for use in a later step.

3. Run the following .alter-merge policy managed_identity command. This command sets a managed identity policy on the cluster that allows the managed identity to be used with continuous export.

   .alter-merge cluster policy managed_identity ```[
       {
         "ObjectId": "system",
         "AllowedUsages": "AutomatedFlows"
       }
   ]```
   

   [!NOTE] To set the policy on a specific database, use database <DatabaseName> instead of cluster.

4. Run the following command to grant the managed identity Database Viewer permissions over all databases used for the continuous export, such as the database that contains the external table.

   .add database <DatabaseName> viewers ('aadapp=<objectId>')
   

   Replace <DatabaseName> with the relevant database and <objectId> with the managed identity Object (principal) ID from step 2.

Set up an external table

External tables refer to data located in Azure Storage, such as Azure Blob Storage, Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, or SQL Server.

Select one of the following tabs to set up an Azure Storage or SQL Server external table.

Azure Storage

1. Create a connection string based on the storage connection string templates. This string indicates the resource to access and its authentication information. For continuous export flows, we recommend impersonation authentication.

2. Run the .create or .alter external table command to create the table. Use the connection string from the previous step as the storageConnectionString argument.

   For example, the following command creates MyExternalTable that refers to CSV-formatted data in mycontainer of mystorageaccount in Azure Blob Storage. The table has two columns, one for an integer x and one for a string s. The connection string ends with ;impersonate, which indicates to use impersonation authentication to access the data store.

   .create external table MyExternalTable (x:int, s:string) kind=storage dataformat=csv 
   ( 
       h@'https://mystorageaccount.blob.core.windows.net/mycontainer;impersonate' 
   )
   

3. Grant the managed identity write permissions over the relevant external data store. The managed identity needs write permissions because the continuous export job exports data to the data store on behalf of the managed identity.

   External data store	Required permissions	Grant the permissions
   Azure Blob Storage	Storage Blob Data Contributor	Assign an Azure role
   Data Lake Storage Gen2	Storage Blob Data Contributor	Assign an Azure role
   Data Lake Storage Gen1	Contributor	Assign an Azure role

SQL Server

1. Create a SQL Server connection string. This string indicates the resource to access and its authentication information. For continuous export flows, we recommend Microsoft Entra integrated authentication, which is impersonation authentication.

2. Run the .create or .alter external table command to create the table. Use the connection string from the previous step as the sqlServerConnectionString argument.

   For example, the following command creates MySqlExternalTable that refers to MySqlTable table in MyDatabase of SQL Server. The table has two columns, one for an integer x and one for a string s. The connection string contains ;Authentication=Active Directory Integrated, which indicates to use impersonation authentication to access the table.

   .create external table MySqlExternalTable (x:int, s:string) kind=sql table=MySqlTable
   ( 
      h@'Server=tcp:myserver.database.windows.net,1433;Authentication=Active Directory Integrated;Initial Catalog=MyDatabase;'
   )
   

3. Grant the managed identity CREATE, UPDATE, and INSERT permissions over the SQL Server database. The managed identity needs write permissions because the continuous export job exports data to the database on behalf of the managed identity. To learn more, see Permissions.

Create a continuous export job

Select one of the following tabs to create a continuous export job that runs on behalf of a user-assigned or system-assigned managed identity.

User-assigned

Run the .create-or-alter continuous-export command with the managedIdentity property set to the managed identity object ID.

For example, the following command creates a continuous export job named MyExport to export the data in MyTable to MyExternalTable on behalf of a user-assigned managed identity. <objectId> should be a managed identity object ID.

.create-or-alter continuous-export MyExport over (MyTable) to table MyExternalTable with (managedIdentity=<objectId>, intervalBetweenRuns=5m) <| MyTable

System-assigned

Run the .create-or-alter continuous-export command with the managedIdentity property set to system.

For example, the following command creates a continuous export job named MyExport to export the data in MyTable to MyExternalTable on behalf of your system-assigned managed identity.

.create-or-alter continuous-export MyExport over (MyTable) to table MyExternalTable with (managedIdentity="system", intervalBetweenRuns=5m) <| MyTable

Related content

• Continuous export overview
• .show continuous-exports
• Managed identities

4 - Data ingestion

4.1 - .ingest inline command (push)

This command inserts data into a table by pushing the data included within the command to the table.

Permissions

You must have at least Table Ingestor permissions to run this command.

Syntax

.ingest inline into table TableName [with ( IngestionPropertyName = IngestionPropertyValue [, …] )] <| Data

.ingest inline into table TableName [with ( IngestionPropertyName = IngestionPropertyValue [, …] )] [ Data ]

Parameters

Returns

The result is a table with as many records as the number of generated data shards (“extents”). If no data shards are generated, a single record is returned with an empty (zero-valued) extent ID.

Examples

Ingest with <| syntax

The following command ingests data into a table Purchases with two columns: SKU (of type string) and Quantity (of type long).

.ingest inline into table Purchases <|
    Shoes,1000
    Wide Shoes,50
    "Coats black",20
    "Coats with ""quotes""",5

Ingest with bracket syntax

The following command ingests data into a table Logs with two columns: Date (of type datetime) and EventDetails (of type dynamic).

.ingest inline into table Logs
    [2015-01-01,"{""EventType"":""Read"", ""Count"":""12""}"]
    [2015-01-01,"{""EventType"":""Write"", ""EventValue"":""84""}"]

Related content

• Data formats supported for ingestion
• .ingest into (pull data from storage)
• Ingest from query

4.2 - .show data operations

Returns a table with data operations that reached a final state. Data operations are available for 30 days from when they ran.

Any operation that results in new extents (data shards) added to the system is considered a data operation.

Permissions

You must have Database Admin or Database Monitor permissions to see any data operations invoked on your database.

Any user can see their own data operations.

For more information, see Kusto role-based access control.

Syntax

.show data operations

Returns

This command returns a table with the following columns:

Example

The following example returns information about UpdatePolicy, BatchIngest, and SetOrAppend operations.

.show data operations

Output

4.3 - Data formats supported for ingestion

Data ingestion is the process by which data is added to a table and is made available for query. For all ingestion methods, other than ingest-from-query, the data must be in one of the supported formats. The following table lists and describes the formats that is supported for data ingestion.

For more information about why ingestion might fail, see Ingestion failures and Ingestion error codes in Azure Data Explorer.

For more info on ingesting data using json or multijson formats, see ingest json formats.

Supported data compression formats

Blobs and files can be compressed through any of the following compression algorithms:

Indicate compression by appending the extension to the name of the blob or file.

For example:

• MyData.csv.zip indicates a blob or a file formatted as CSV, compressed with zip (archive or a single file)
• MyData.json.gz indicates a blob or a file formatted as JSON, compressed with gzip.

Blob or file names that don’t include the format extensions but just compression (for example, MyData.zip) is also supported. In this case, the file format must be specified as an ingestion property because it cannot be inferred.

Related content

• Learn more about supported data formats
• Learn more about Data ingestion properties
• Learn more about data ingestion

4.4 - Data ingestion properties

Data ingestion is the process by which data is added to a table and is made available for query. You add properties to the ingestion command after the with keyword.

Related content

• Learn more about supported data formats
• Learn more about data ingestion

4.5 - Ingest from query

4.5.1 - .cancel operation command

This command cancels a long-running ingest from query operation. This command is useful when the operation is taking too long and you would like to abort it while running.

The cancel operation command isn’t guaranteed to succeed. The output of the .cancel operation command indicates whether or not cancellation was successful.

Syntax

.cancel operation OperationId [with ( reason = ReasonPhrase )]

Parameters

Returns

Example

.cancel operation 078b2641-f10d-4694-96f8-1ee2b75dda48 with(Reason="Command canceled by me")

4.5.2 - Kusto query ingestion (set, append, replace)

These commands execute a query or a management command and ingest the results of the query into a table. The difference between these commands is how they treat existing or nonexistent tables and data.

To cancel an ingest from query command, see cancel operation.

Permissions

To perform different actions on a table, you need specific permissions:

• To add rows to an existing table using the .append command, you need a minimum of Table Ingestor permissions.
• To create a new table using the various .set commands, you need a minimum of Database User permissions.
• To replace rows in an existing table using the .set-or-replace command, you need a minimum of Table Admin permissions.

For more information on permissions, see Kusto role-based access control.

Syntax

(.set | .append | .set-or-append | .set-or-replace) [async] tableName [with (propertyName = propertyValue [, …])] <| queryOrCommand

Parameters

Performance tips

• Set the distributed property to true if the amount of data produced by the query is large, exceeds one gigabyte (GB), and doesn’t require serialization. Then, multiple nodes can produce output in parallel. Don’t use this flag when query results are small, since it might needlessly generate many small data shards.
• Data ingestion is a resource-intensive operation that might affect concurrent activities on the database, including running queries. Avoid running too many ingestion commands at the same time.
• Limit the data for ingestion to less than one GB per ingestion operation. If necessary, use multiple ingestion commands.

Supported ingestion properties

Schema considerations

• .set-or-replace preserves the schema unless one of extend_schema or recreate_schema ingestion properties is set to true.
• .set-or-append and .append commands preserve the schema unless the extend_schema ingestion property is set to true.
• Matching the result set schema to that of the target table is based on the column types. There’s no matching of column names. Make sure that the query result schema columns are in the same order as the table, otherwise data is ingested into the wrong columns.

Character limitation

The command fails if the query generates an entity name with the $ character. The entity names must comply with the naming rules, so the $ character must be removed for the ingest command to succeed.

For example, in the following query, the search operator generates a column $table. To store the query results, use project-rename to rename the column.

.set Texas <| search State has 'Texas' | project-rename tableName=$table

Returns

Returns information on the extents created because of the .set or .append command.

Examples

Create and update table from query source

The following query creates the :::no-loc text=“RecentErrors”::: table with the same schema as :::no-loc text=“LogsTable”:::. It updates :::no-loc text=“RecentErrors”::: with all error logs from :::no-loc text=“LogsTable”::: over the last hour.

.set RecentErrors <|
   LogsTable
   | where Level == "Error" and Timestamp > now() - time(1h)

Create and update table from query source using the distributed flag

The following example creates a new table called OldExtents in the database, asynchronously. The dataset is expected to be bigger than one GB (more than ~one million rows) so the distributed flag is used. It updates OldExtents with ExtentId entries from the MyExtents table that were created more than 30 days ago.

.set async OldExtents with(distributed=true) <|
   MyExtents 
   | where CreatedOn < now() - time(30d)
   | project ExtentId

Append data to table

The following example filters ExtentId entries in the MyExtents table that were created more than 30 days ago and appends the entries to the OldExtents table with associated tags.

.append OldExtents with(tags='["TagA","TagB"]') <| 
   MyExtents 
   | where CreatedOn < now() - time(30d) 
   | project ExtentId

Create or append a table with possibly existing tagged data

The following example either appends to or creates the OldExtents table asynchronously. It filters ExtentId entries in the MyExtents table that were created more than 30 days ago and specifies the tags to append to the new extents with ingest-by:myTag. The ingestIfNotExists parameter ensures that the ingestion only occurs if the data doesn’t already exist in the table with the specified tag.

.set-or-append async OldExtents with(tags='["ingest-by:myTag"]', ingestIfNotExists='["myTag"]') <|
   MyExtents
   | where CreatedOn < now() - time(30d)
   | project ExtentId

Create table or replace data with associated data

The following query replaces the data in the OldExtents table, or creates the table if it doesn’t already exist, with ExtentId entries in the MyExtents table that were created more than 30 days ago. Tag the new extent with ingest-by:myTag if the data doesn’t already exist in the table with the specified tag.

.set-or-replace async OldExtents with(tags='["ingest-by:myTag"]', ingestIfNotExists='["myTag"]') <| 
   MyExtents 
   | where CreatedOn < now() - time(30d) 
   | project ExtentId

Append data with associated data

The following example appends data to the OldExtents table asynchronously, using ExtentId entries from the MyExtents table that were created more than 30 days ago. It sets a specific creation time for the new extents.

.append async OldExtents with(creationTime='2017-02-13T11:09:36.7992775Z') <| 
   MyExtents 
   | where CreatedOn < now() - time(30d) 
   | project ExtentId     

Sample output

The following is a sample of the type of output you may see from your queries.

Related content

• Data formats supported for ingestion
• Inline ingestion
• Ingest from storage

4.6 - Kusto.ingest into command (pull data from storage)

The .ingest into command ingests data into a table by “pulling” the data from one or more cloud storage files. For example, the command can retrieve 1,000 CSV-formatted blobs from Azure Blob Storage, parse them, and ingest them together into a single target table. Data is appended to the table without affecting existing records, and without modifying the table’s schema.

Permissions

You must have at least Table Ingestor permissions to run this command.

Syntax

.ingest [async] into table TableName SourceDataLocator [with ( IngestionPropertyName = IngestionPropertyValue [, …] )]

Parameters

Authentication and authorization

Each storage connection string indicates the authorization method to use for access to the storage. Depending on the authorization method, the principal might need to be granted permissions on the external storage to perform the ingestion.

The following table lists the supported authentication methods and the permissions needed for ingesting data from external storage.

Returns

The result of the command is a table with as many records as there are data shards (“extents”) generated by the command. If no data shards were generated, a single record is returned with an empty (zero-valued) extent ID.

Examples

Azure Blob Storage with shared access signature

The following example instructs your database to read two blobs from Azure Blob Storage as CSV files, and ingest their contents into table T. The ... represents an Azure Storage shared access signature (SAS) which gives read access to each blob. Obfuscated strings (the h in front of the string values) are used to ensure that the SAS is never recorded.

.ingest into table T (
    h'https://contoso.blob.core.windows.net/container/file1.csv?...',
    h'https://contoso.blob.core.windows.net/container/file2.csv?...'
)

Azure Blob Storage with managed identity

The following example shows how to read a CSV file from Azure Blob Storage and ingest its contents into table T using managed identity authentication. Authentication uses the managed identity ID (object ID) assigned to the Azure Blob Storage in Azure. For more information, see Create a managed identity for storage containers.

.ingest into table T ('https://StorageAccount.blob.core.windows.net/Container/file.csv;managed_identity=802bada6-4d21-44b2-9d15-e66b29e4d63e')

Azure Data Lake Storage Gen 2

The following example is for ingesting data from Azure Data Lake Storage Gen 2 (ADLSv2). The credentials used here (...) are the storage account credentials (shared key), and we use string obfuscation only for the secret part of the connection string.

.ingest into table T (
  'abfss://myfilesystem@contoso.dfs.core.windows.net/path/to/file1.csv;...'
)

Azure Data Lake Storage

The following example ingests a single file from Azure Data Lake Storage (ADLS). It uses the user’s credentials to access ADLS (so there’s no need to treat the storage URI as containing a secret). It also shows how to specify ingestion properties.

.ingest into table T ('adl://contoso.azuredatalakestore.net/Path/To/File/file1.ext;impersonate')
  with (format='csv')

Amazon S3 with an access key

The following example ingests a single file from Amazon S3 using an access key ID and a secret access key.

.ingest into table T ('https://bucketname.s3.us-east-1.amazonaws.com/path/to/file.csv;AwsCredentials=AKIAIOSFODNN7EXAMPLE,wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY')
  with (format='csv')

Amazon S3 with a presigned URL

The following example ingests a single file from Amazon S3 using a preSigned URL.

  with (format='csv')

Related content

• Data formats supported for ingestion
• Inline ingestion
• Ingest from query (.set, .append, .set-or-append, .set-or-replace)
• .show ingestion failures command
• .show ingestion mapping

4.7 - Streaming ingestion

4.7.1 - Clearing cached schema for streaming ingestion

Nodes cache schema of the databases that receive data via streaming ingestion. This process optimizes performance and utilization of resources, but can cause propagation delays when the schema change.

Clear the cache to guarantee that subsequent streaming ingestion requests incorporate database or table schema changes. For more information, see Streaming ingestion and schema changes.

Permissions

You must have at least Database Ingestor permissions to run this command.

Syntax

.clear table TableName cache streamingingestion schema

.clear database cache streamingingestion schema

Parameters

Returns

This command returns a table with the following columns:

Example

.clear database cache streamingingestion schema

.clear table T1 cache streamingingestion schema

4.7.2 - Streaming ingestion and schema changes

Cluster nodes cache the schema of databases that get data through streaming ingestion, boosting performance and resource use. However, when there are schema changes, it can lead to delays in updates.

Eventhouse nodes cache the schema of databases that get data through streaming ingestion, boosting performance and resource use. However, when there are schema changes, it can lead to delays in updates.

If schema changes and streaming ingestion aren’t synchronized, you can encounter failures like schema-related errors or incomplete and distorted data in the table.

This article outlines typical schema changes and provides guidance on avoiding problems with streaming ingestion during these changes.

Schema changes

The following list covers key examples of schema changes:

• Creation of tables
• Deletion of tables
• Adding a column to a table
• Removing a column from a table
• Retyping the columns of a table
• Renaming the columns of a table
• Adding precreated ingestion mappings
• Removing precreated ingestion mappings
• Adding, removing, or altering policies

Coordinate schema changes with streaming ingestion

The schema cache is kept while the database is online. If there are schema changes, the system automatically refreshes the cache, but this refresh can take several minutes. If you rely on the automatic refresh, you can experience uncoordinated ingestion failures.

You can reduce the effects of propagation delay by explicitly clearing the schema cache on the nodes. If the streaming ingestion flow and schema changes are coordinated, you can completely eliminate failures and their associated data distortion.

To coordinate the streaming ingestion flow with schema changes:

1. Suspend streaming ingestion.
2. Wait until all outstanding streaming ingestion requests are complete.
3. Do schema changes.
4. Issue one or several .clear cache streaming ingestion schema commands.
   • Repeat until successful and all rows in the command output indicate success
5. Resume streaming ingestion.

5 - Database cursors

5.1 - Database cursors

A database cursor is a database-level object that lets you query a database multiple times. You get consistent results even if there are data-append or data-retention operations happening in parallel with the queries.

Database cursors are designed to address two important scenarios:

• The ability to repeat the same query multiple times and get the same results, as long as the query indicates “same data set”.

• The ability to make an “exactly once” query. This query only “sees” the data that a previous query didn’t see, because the data wasn’t available then. The query lets you iterate, for example, through all the newly arrived data in a table without fear of processing the same record twice or skipping records by mistake.

The database cursor is represented in the query language as a scalar value of type string. The actual value should be considered opaque and there’s no support for any operation other than to save its value or use the cursor functions noted below.

Cursor functions

Kusto provides three functions to help implement the two above scenarios:

• cursor_current(): Use this function to retrieve the current value of the database cursor. You can use this value as an argument to the two other functions.

• cursor_after(rhs:string): This special function can be used on table records that have the IngestionTime policy enabled. It returns a scalar value of type bool indicating whether the record’s ingestion_time() database cursor value comes after the rhs database cursor value.

• cursor_before_or_at(rhs:string): This special function can be used on the table records that have the IngestionTime policy enabled. It returns a scalar value of type bool indicating whether the record’s ingestion_time() database cursor value comes before or at the rhs database cursor value.

The two special functions (cursor_after and cursor_before_or_at) also have a side-effect: When they’re used, Kusto will emit the current value of the database cursor to the @ExtendedProperties result set of the query. The property name for the cursor is Cursor, and its value is a single string.

For example:

{"Cursor" : "636040929866477946"}

Restrictions

Database cursors can only be used with tables for which the IngestionTime policy has been enabled. Each record in such a table is associated with the value of the database cursor that was in effect when the record was ingested. As such, the ingestion_time() function can be used.

The database cursor object holds no meaningful value unless the database has at least one table that has an IngestionTime policy defined. This value is guaranteed to update, as-needed by the ingestion history, into such tables and the queries run, that reference such tables. It might, or might not, be updated in other cases.

The ingestion process first commits the data, so that it’s available for querying, and only then assigns an actual cursor value to each record. If you attempt to query for data immediately following the ingestion completion using a database cursor, the results might not yet incorporate the last records added, because they haven’t yet been assigned the cursor value. Also, retrieving the current database cursor value repeatedly might return the same value, even if ingestion was done in between, because only a cursor commit can update its value.

Querying a table based on database cursors is only guaranteed to “work” (providing exactly-once guarantees) if the records are ingested directly into that table. If you’re using extents commands, such as move extents/.replace extents to move data into the table, or if you’re using .rename table, then querying this table using database cursors isn’t guaranteed to not miss any data. This is because the ingestion time of the records is assigned when initially ingested, and doesn’t change during the move extents operation. Therefore, when the extents are moved into the target table, it’s possible that the cursor value assigned to the records in these extents was already processed (and next query by database cursor will miss the new records).

Example: Processing records exactly once

For a table Employees with schema [Name, Salary], to continuously process new records as they’re ingested into the table, use the following process:

// [Once] Enable the IngestionTime policy on table Employees
.set table Employees policy ingestiontime true

// [Once] Get all the data that the Employees table currently holds 
Employees | where cursor_after('')

// The query above will return the database cursor value in
// the @ExtendedProperties result set. Lets assume that it returns
// the value '636040929866477946'

// [Many] Get all the data that was added to the Employees table
// since the previous query was run using the previously-returned
// database cursor 

6 - Plugin commands

7 - Policies

7.1 - Policies overview

The following table provides an overview of the policies for managing your environment:

7.2 - Auto delete

7.2.1 - Auto delete policy

An auto delete policy on a table sets an expiry date for the table. The table is automatically deleted at this expiry time. Unlike the retention policy, which determines when data (extents) are removed from a table, the auto delete policy drops the entire table.

The auto delete policy can be useful for temporary staging tables. Temporary staging tables are used for data preparation, until the data is moved to its permanent location. We recommend explicitly dropping temporary tables when they’re no longer needed. Only use the auto delete policy as a fallback mechanism in case the explicit deletion doesn’t occur.

Policy object

An auto delete policy includes the following properties:

• ExpiryDate:

  • Date and time value indicating when the table should be deleted.
  • The deletion time is imprecise, and could occur few hours later than the time specified in the ExpiryDate property.
  • The value specified can’t be null and it must be greater than current time.

• DeleteIfNotEmpty:

  • Boolean value indicating whether table should be dropped even if there are still extents in it.
  • Defaults to false.

Related content

For more information, see auto delete policy commands.

7.3 - Caching

7.3.1 - Caching policy (hot and cold cache)

To ensure fast query performance, a multi-tiered data cache system is used. Data is stored in reliable storage but parts of it are cached on processing nodes, SSD, or even in RAM for faster access.

The caching policy allows you to choose which data should be cached. You can differentiate between hot data cache and cold data cache by setting a caching policy on hot data. Hot data is kept in local SSD storage for faster query performance, while cold data is stored in reliable storage, which is cheaper but slower to access.

The cache uses 95% of the local SSD disk for hot data. If there isn’t enough space, the most recent data is preferentially kept in the cache. The remaining 5% is used for data that isn’t categorized as hot. This design ensures that queries loading lots of cold data won’t evict hot data from the cache.

The best query performance is achieved when all ingested data is cached. However, certain data might not warrant the expense of being kept in the hot cache. For instance, infrequently accessed old log records might be considered less crucial. In such cases, teams often opt for lower querying performance over paying to keep the data warm.

Use management commands to alter the caching policy at the database, table, or materialized view level.

Use management commands to alter the caching policy at the cluster, database, table, or materialized view level.

How caching policy is applied

When data is ingested, the system keeps track of the date and time of the ingestion, and of the extent that was created. The extent’s ingestion date and time value (or maximum value, if an extent was built from multiple preexisting extents), is used to evaluate the caching policy.

By default, the effective policy is null, which means that all the data is considered hot. A null policy at the table level means that the policy is inherited from the database. A non-null table-level policy overrides a database-level policy.

Scoping queries to hot cache

When running queries, you can limit the scope to only query data in hot cache.

There are several query possibilities:

• Add a client request property called query_datascope to the query. Possible values: default, all, and hotcache.
• Use a set statement in the query text: set query_datascope='...'. Possible values are the same as for the client request property.
• Add a datascope=... text immediately after a table reference in the query body. Possible values are all and hotcache.

The default value indicates use of the default settings, which determine that the query should cover all data.

If there’s a discrepancy between the different methods, then set takes precedence over the client request property. Specifying a value for a table reference takes precedence over both.

For example, in the following query, all table references use hot cache data only, except for the second reference to “T” that is scoped to all the data:

set query_datascope="hotcache";
T | union U | join (T datascope=all | where Timestamp < ago(365d)) on X

Caching policy vs retention policy

Caching policy is independent of retention policy:

• Caching policy defines how to prioritize resources. Queries for important data are faster.
• Retention policy defines the extent of the queryable data in a table/database (specifically, SoftDeletePeriod).

Configure this policy to achieve the optimal balance between cost and performance, based on the expected query pattern.

Example:

• SoftDeletePeriod = 56d
• hot cache policy = 28d

In the example, the last 28 days of data is stored on the SSD and the additional 28 days of data is stored in Azure blob storage. You can run queries on the full 56 days of data.

Related content

• Hot windows for infrequent queries over cold data

7.4 - Callout

7.4.1 - Callout policy

Your cluster can communicate with external services in many different scenarios. Cluster administrators can manage the authorized domains for external calls by updating the cluster’s callout policy.

Supported properties of a callout

A callout policy is composed of the following properties:

Types of callout

Callout policies are managed at cluster-level and are classified into the following types:

Predefined callout policies

The following table shows a set of predefined callout policies that are preconfigured on your cluster to enable callouts to selected services:

More predefined policies on your cluster may be observed with next query:

.show cluster policy callout 
| where EntityType == 'Cluster immutable policy'
| project Policy

Remarks

If an external resource of a given type matches more than one policy defined for such type, and at least one of the matched policies has their CanCall property set to false, access to the resource is denied.

Related content

• .show cluster policy callout command
• .alter cluster policy callout command
• .alter-merge cluster policy callout command
• .delete cluster policy callout command

7.5 - Capacity

7.5.1 - Capacity policy

A capacity policy is used for controlling the compute resources of data management operations on the cluster.

The capacity policy object

The capacity policy is made of the following components:

• IngestionCapacity
• ExtentsMergeCapacity
• ExtentsPurgeRebuildCapacity
• ExportCapacity
• ExtentsPartitionCapacity
• MaterializedViewsCapacity
• StoredQueryResultsCapacity
• StreamingIngestionPostProcessingCapacity
• PurgeStorageArtifactsCleanupCapacity
• PeriodicStorageArtifactsCleanupCapacity
• QueryAccelerationCapacity

To view the capacity of your cluster, use the .show capacity command.

Ingestion capacity

Formula

The .show capacity command returns the cluster’s ingestion capacity based on the following formula:

Minimum(ClusterMaximumConcurrentOperations , Number of nodes in cluster * Maximum(1, Core count per node * CoreUtilizationCoefficient))

Extents merge capacity

Formula

The .show capacity command returns the cluster’s extents merge capacity based on the following formula:

Minimum(Number of nodes in cluster * Concurrent operations per node, ClusterMaximumConcurrentOperations)

The effective value for Concurrent operations per node is automatically adjusted by the system in the range [MinimumConcurrentOperationsPerNode,MaximumConcurrentOperationsPerNode], as long as the success rate of the merge operations is 90% or higher.

Extents purge rebuild capacity

Formula

The .show capacity command returns the cluster’s extents purge rebuild capacity based on the following formula:

Number of nodes in cluster x MaximumConcurrentOperationsPerNode

Export capacity

Formula

The .show capacity command returns the cluster’s export capacity based on the following formula:

Minimum(ClusterMaximumConcurrentOperations , Number of nodes in cluster * Maximum(1, Core count per node * CoreUtilizationCoefficient))

Extents partition capacity

The effective value for Concurrent operations is automatically adjusted by the system in the range [ClusterMinimumConcurrentOperations,ClusterMaximumConcurrentOperations], as long as the success rate of the partitioning operations is 90% or higher.

Materialized views capacity policy

The policy can be used to change concurrency settings for materialized views. Changing the materialized views capacity policy can be useful when there’s more than a single materialized view defined on a cluster.

By default, only a single materialization runs concurrently (see how materialized views work). The system adjusts the current concurrency in the range [ClusterMinimumConcurrentOperations,ClusterMaximumConcurrentOperations], based on the number of materialized views in the cluster and the cluster’s CPU. You can increase/decrease concurrency by altering this policy. For example, if the cluster has 10 materialized views, setting the ClusterMinimumConcurrentOperations to five ensures that at least five of them can materialize concurrently. You can view the effective value for the current concurrency using the .show capacity command

Stored query results capacity

Formula

The .show capacity command returns the cluster’s stored query results creation capacity based on the following formula:

Minimum(MaximumConcurrentOperationsPerDbAdmin , Number of nodes in cluster * Maximum(1, Core count per node * CoreUtilizationCoefficient))

Streaming ingestion post processing capacity

Formula

The .show capacity command returns the cluster’s streaming ingestion post processing capacity based on the following formula:

Number of nodes in cluster x MaximumConcurrentOperationsPerNode

Purge storage artifacts cleanup capacity

Formula

The .show capacity command returns the cluster’s purge storage artifacts cleanup capacity based on the following formula:

MaximumConcurrentOperationsPerCluster

Periodic storage artifacts cleanup capacity

Formula

The .show capacity command returns the cluster’s periodic storage artifacts cleanup capacity based on the following formula:

MaximumConcurrentOperationsPerCluster

Query Acceleration capacity

Formula

The .show capacity command returns the cluster’s query acceleration caching capacity based on the following formula:

Minimum(ClusterMaximumConcurrentOperations , Number of nodes in cluster * Maximum(1, Core count per node * CoreUtilizationCoefficient))

Defaults

The default capacity policy has the following JSON representation:

{
  "IngestionCapacity": {
    "ClusterMaximumConcurrentOperations": 512,
    "CoreUtilizationCoefficient": 0.75
  },
  "ExtentsMergeCapacity": {
    "MinimumConcurrentOperationsPerNode": 1,
    "MaximumConcurrentOperationsPerNode": 3
  },
  "ExtentsPurgeRebuildCapacity": {
    "MaximumConcurrentOperationsPerNode": 1
  },
  "ExportCapacity": {
    "ClusterMaximumConcurrentOperations": 100,
    "CoreUtilizationCoefficient": 0.25
  },
  "ExtentsPartitionCapacity": {
    "ClusterMinimumConcurrentOperations": 1,
    "ClusterMaximumConcurrentOperations": 32
  },
  "MaterializedViewsCapacity": {
    "ClusterMaximumConcurrentOperations": 1,
    "ExtentsRebuildCapacity": {
      "ClusterMaximumConcurrentOperations": 50,
      "MaximumConcurrentOperationsPerNode": 5
    }
  },
  "StoredQueryResultsCapacity": {
    "MaximumConcurrentOperationsPerDbAdmin": 250,
    "CoreUtilizationCoefficient": 0.75
  },
  "StreamingIngestionPostProcessingCapacity": {
    "MaximumConcurrentOperationsPerNode": 4
  },
  "PurgeStorageArtifactsCleanupCapacity": {
    "MaximumConcurrentOperationsPerCluster": 2
  },
  "PeriodicStorageArtifactsCleanupCapacity": {
    "MaximumConcurrentOperationsPerCluster": 2
  },
  "QueryAccelerationCapacity": {
    "ClusterMaximumConcurrentOperations": 100,
    "CoreUtilizationCoefficient": 0.5
  }
}

Management commands

• Use .show cluster policy capacity to show the current capacity policy of the cluster.
• Use .alter-merge cluster policy capacity to alter the capacity policy of the cluster.

Management commands throttling

Kusto limits the number of concurrent requests for the following user-initiated commands:

• Ingestions
  • This category includes commands that ingest from storage, ingest from a query, and ingest inline.
  • The ingestion capacity defines the limit.
• Purges
  • The global limit is currently fixed at one per cluster.
  • The purge rebuild capacity is used internally to determine the number of concurrent rebuild operations during purge commands. Purge commands aren’t blocked or throttled because of this process, but completes faster or slower depending on the purge rebuild capacity.
• Exports
  • The limit is as defined in the export capacity.
• Query Acceleration
  • The limit is as defined in the query acceleration capacity.

When the cluster detects that an operation exceeded the limit on concurrent requests:

• The command’s state, as presented by System information commands, is Throttled.
• The error message includes the command type, the origin of the throttling and the capacity that exceeded. For example:
  • For example: The management command was aborted due to throttling. Retrying after some backoff might succeed. CommandType: 'TableSetOrAppend', Capacity: 18, Origin: 'CapacityPolicy/Ingestion'.
• The HTTP response code is 429. The subcode is TooManyRequests.
• The exception type is ControlCommandThrottledException.

Related content

• .show capacity

7.6 - Encoding policy

7.6.1 - Encoding policy

The encoding policy defines how data is encoded, compressed, and indexed. This policy applies to all columns of stored data. A default encoding policy is applied based on the column’s data type, and a background process adjusts the encoding policy automatically if necessary.

Scenarios

We recommend the default policy be maintained except for specific scenarios. It can be useful to modify the default column’s encoding policy to fine tune control over the performance/COGS trade-off. For example:

• The default indexing applied to string columns is built for term searches. If you only query for specific values in the column, COGS might be reduced if the index is simplified using the encoding profile Identifier. For more information, see the string data type.
• Fields that are never queried on or don’t need fast searches can disable indexing. You can use profile BigObject to turn off the indexes and increase maximal value size in dynamic or string columns. For example, use this profile to store HLL values returned by hll() function.

How it works

Encoding policy changes do not affect data that has already been ingested. Only new ingestion operations will be performed according to the new policy. The encoding policy applies to individual columns in a table, but can be set at the column level, table level (affecting all columns of the table), or database level.

Related content

• To view the encoding policy, see .show encoding policy.
• To alter the encoding policy, see .alter encoding policy.

7.7 - Extent tags policy

7.7.1 - Extent tags retention policy

The extent tags retention policy controls the mechanism that automatically removes extent tags from tables, based on the age of the extents.

It’s recommended to remove any tags that are no longer helpful, or were used temporarily as part of an ingestion pipeline, and may limit the system from reaching optimal performance. For example: old drop-by: tags, which prevent merging extents together.

The policy can be set at the table-level, or at the database-level. A database-level policy applies to all tables in the database that don’t override the policy.

The policy object

The extent tags retention policy is an array of policy objects. Each object includes the following properties:

Example

The following policy will have any drop-by: tags older than three days and any ingest-by: tags older than two hours automatically dropped:

[
    {
        "TagPrefix": "drop-by:",
        "RetentionPeriod": "3.00:00:00"
    },
    {
        "TagPrefix": "ingest-by:",
        "RetentionPeriod": "02:00:00"
    }
]

Defaults

By default, when the policy isn’t defined, extent tags of any kind are retained as long as the extent isn’t dropped.

Management commands

The following management commands can be used to manage the extent tags retention policy:

• .show extent tags retention policy
• .alter extent tags retention policy
• .delete extent tags retention policy

7.8 - Ingestion batching

7.8.1 - IngestionBatching policy

Overview

During the queued ingestion process, the service optimizes for throughput by batching small ingress data chunks together before ingestion. Batching reduces the resources consumed by the queued ingestion process and doesn’t require post-ingestion resources to optimize the small data shards produced by non-batched ingestion.

The downside to doing batching before ingestion is the forced delay. Therefore, the end-to-end time from requesting the data ingestion until the data ready for query is larger.

When you define the IngestionBatching policy, you’ll need to find a balance between optimizing for throughput and time delay. This policy applies to queued ingestion. It defines the maximum forced delay allowed when batching small blobs together. To learn more about using batching policy commands, and optimizing for throughput, see:

• Ingestion batching policy command reference
• Ingestion best practices - optimizing for throughput

Sealing a batch

There’s an optimal size of about 1 GB of uncompressed data for bulk ingestion. Ingestion of blobs with much less data is suboptimal, so in queued ingestion the service will batch small blobs together.

The following list shows the basic batching policy triggers to seal a batch. A batch is sealed and ingested when the first condition is met:

• Size: Batch size limit reached or exceeded
• Count: Batch file number limit reached
• Time: Batching time has expired

The IngestionBatching policy can be set on databases or tables. Default values are as follows: 5 minutes maximum delay time, 500 items, total size of 1 GB.

The following list shows conditions to seal batches related to single blob ingestion. A batch is sealed and ingested when the conditions are met:

• SingleBlob_FlushImmediately: Ingest a single blob because ‘FlushImmediately’ was set
• SingleBlob_IngestIfNotExists: Ingest a single blob because ‘IngestIfNotExists’ was set
• SingleBlob_IngestByTag: Ingest a single blob because ‘ingest-by’ was set
• SingleBlob_SizeUnknown: Ingest a single blob because blob size is unknown

If the SystemFlush condition is set, a batch will be sealed when a system flush is triggered. With the SystemFlush parameter set, the system flushes the data, for example due to database scaling or internal reset of system components.

Defaults and limits

The most effective way of controlling the end-to-end latency using ingestion batching policy is to alter its time boundary at table or database level, according to the higher bound of latency requirements. A database level policy affects all tables in that database that don’t have the table-level policy defined, and any newly created table.

Batch data size

The batching policy data size is set for uncompressed data. For Parquet, AVRO, and ORC files, an estimation is calculated based on file size. For compressed data, the uncompressed data size is evaluated as follows in descending order of accuracy:

1. If the uncompressed size is provided in the ingestion source options, that value is used.
2. When ingesting local files using SDKs, zip archives and gzip streams are inspected to assess their raw size.
3. If previous options don’t provide a data size, a factor is applied to the compressed data size to estimate the uncompressed data size.

Batching latencies

Latencies can result from many causes that can be addressed using batching policy settings.

7.9 - Ingestion time

7.9.1 - IngestionTime policy

The IngestionTime policy is an optional policy that can be set (enabled) on tables.

When enabled, Kusto adds a hidden datetime column to the table, called $IngestionTime. Now, whenever new data is ingested, the time of ingestion is recorded in the hidden column. That time is measured just before the data is committed.

Since the ingestion time column is hidden, you can’t directly query for its value. Instead, a special function called ingestion_time() retrieves that value. If there’s no datetime column in the table, or the IngestionTime policy wasn’t enabled when a record was ingested, a null value is returned.

The IngestionTime policy is designed for two main scenarios:

• To allow users to estimate the latency in ingesting data. Many tables with log data have a timestamp column. The timestamp value gets filled by the source and indicates the time when the record was produced. By comparing that column’s value with the ingestion time column, you can estimate the latency for getting the data in.

  [!NOTE] The calculated value is only an estimate, because the source and Kusto don’t necessarily have their clocks synchronized.

• To support Database Cursors that let users issue consecutive queries, the query is limited to the data that was ingested since the previous query.

For more information. see the management commands for managing the IngestionTime policy.

For more information. see the management commands for managing the IngestionTime policy.

7.10 - Managed identity

7.10.1 - Kusto ManagedIdentity policy

ManagedIdentity is a policy that controls which managed identities can be used for what purposes. For example, you can configure a policy that allows a specific managed identity to be used for accessing a storage account for ingestion purposes.

This policy can be enabled at the cluster and database levels. The policy is additive, meaning that for every operation that involves a managed identity, the operation will be permitted if the usage is allowed at either the cluster or database level.

Permissions

Creating or altering a managed identity policy requires AllDatabasesAdmin permissions.

The ManagedIdentity policy object

A cluster or database may have zero or more ManagedIdentity policy objects associated with it. Each ManagedIdentity policy object has the following user-definable properties: DisplayName and AllowedUsages. Other properties are automatically populated from the managed identity associated with the specified ObjectId and displayed for convenience.

The following table describes the properties of the ManagedIdentity policy object:

The following is an example of a ManagedIdentity policy object:

{
  "ObjectId": "<objectID>",
  "ClientId": "<clientID>",
  "TenantId": "<tenantID",
  "DisplayName": "myManagedIdentity",
  "IsSystem": false,
  "AllowedUsages": "NativeIngestion, ExternalTable"
}

Managed identity usages

The following values specify authentication to a usage using the configured managed identity:

7.11 - Merge policy

7.11.1 - Extents merge policy

The merge policy defines if and how Extents (data shards) should get merged.

There are two types of merge operations: Merge, which rebuilds indexes, and Rebuild, which completely reingests the data.

Both operation types result in a single extent that replaces the source extents.

By default, Rebuild operations are preferred. If there are extents that don’t fit the criteria for being rebuilt, then an attempt will be made to merge them.

Merge policy properties

The merge policy contains the following properties:

• RowCountUpperBoundForMerge:
  • Defaults to 16,000,000.
  • Maximum allowed row count of the merged extent.
  • Applies to Merge operations, not Rebuild.
• OriginalSizeMBUpperBoundForMerge:
  • Defaults to 30,000.
  • Maximum allowed original size (in MBs) of the merged extent.
  • Applies to Merge operations, not Rebuild.
• MaxExtentsToMerge:
  • Defaults to 100.
  • Maximum allowed number of extents to be merged in a single operation.
  • Applies to Merge operations.
  • This value shouldn’t be changed.
• AllowRebuild:
  • Defaults to ’true'.
  • Defines whether Rebuild operations are enabled (in which case, they’re preferred over Merge operations).
• AllowMerge:
  • Defaults to ’true'.
  • Defines whether Merge operations are enabled, in which case, they’re less preferred than Rebuild operations.
• MaxRangeInHours:
  • Defaults to 24.
  • The maximum allowed difference, in hours, between any two different extents’ creation times, so that they can still be merged.
  • Timestamps are of extent creation, and don’t relate to the actual data contained in the extents.
  • Applies to both Merge and Rebuild operations.
  • In materialized views: defaults to 336 (14 days), unless recoverability is disabled in the materialized view’s effective retention policy.
  • This value should be set according to the effective retention policy SoftDeletePeriod, or cache policy DataHotSpan values. Take the lower value of SoftDeletePeriod and DataHotSpan. Set the MaxRangeInHours value to between 2-3% of it. See the examples .
• Lookback:
  • Defines the timespan during which extents are considered for rebuild/merge.
  • Supported values:
    • Default - The system-managed default. This is the recommended and default value, whose period is currently set to 14 days.
    • All - All extents, hot and cold, are included.
    • HotCache - Only hot extents are included.
    • Custom - Only extents whose age is under the provided CustomPeriod are included. CustomPeriod is a timespan value in the format dd.hh:mm.

Default policy example

The following example shows the default policy:

{
  "RowCountUpperBoundForMerge": 16000000,
  "OriginalSizeMBUpperBoundForMerge": 30000,
  "MaxExtentsToMerge": 100,,
  "MaxRangeInHours": 24,
  "AllowRebuild": true,
  "AllowMerge": true,
  "Lookback": {
    "Kind": "Default",
    "CustomPeriod": null
  }
}

MaxRangeInHours examples

When a database is created, it’s set with the default merge policy values mentioned above. The policy is by default inherited by all tables created in the database, unless their policies are explicitly overridden at table-level.

For more information, see management commands that allow you to manage merge policies for databases or tables.

7.12 - Mirroring policy

7.12.1 - Mirroring policy

The mirroring policy commands allow you to view, change, partition, and delete your table mirroring policy. They also provide a way to check the mirroring latency by reviewing the operations mirroring status.

Management commands

• Use .show table policy mirroring command to show the current mirroring policy of the table.
• Use .alter-merge table policy mirroring command to change the current mirroring policy.
• Use .delete table policy mirroring command to soft-delete the current mirroring policy.
• Use .show table mirroring operations command to check operations mirroring status.
• Use .show table mirroring operations exported artifacts command to check operations exported artifacts status.
• Use .show table mirroring operations failures to check operations mirroring failure status.

The policy object

The mirroring policy includes the following properties:

Data types mapping

To ensure compatibility and optimize queries, ensure that your data types are properly mapped to the parquet data types.

Event house to Delta parquet data types mapping

Event house data types are mapped to Delta Parquet data types using the following rules:

For more information on Event house data types, see Scalar data types.

Example policy

{
  "Format": "parquet",
  "IsEnabled": true,
  "Partitions": null,
}

7.13 - Partitioning policy

7.13.1 - Partitioning policy

The partitioning policy defines if and how extents (data shards) should be partitioned for a specific table or a materialized view.

The policy triggers an additional background process that takes place after the creation of extents, following data ingestion. This process includes reingesting data from the source extents and producing homogeneous extents, in which all values of the column designated as the partition key reside within a single partition.

The primary objective of the partitioning policy is to enhance query performance in specific supported scenarios.

Supported scenarios

The following are the only scenarios in which setting a data partitioning policy is recommended. In all other scenarios, setting the policy isn’t advised.

• Frequent filters on a medium or high cardinality string or guid column:
  • For example: multitenant solutions, or a metrics table where most or all queries filter on a column of type string or guid, such as the TenantId or the MetricId.
  • Medium cardinality is at least 10,000 distinct values.
  • Set the hash partition key to be the string or guid column, and set the PartitionAssignmentMode property to uniform.
• Frequent aggregations or joins on a high cardinality string or guid column:
  • For example, IoT information from many different sensors, or academic records of many different students.
  • High cardinality is at least 1,000,000 distinct values, where the distribution of values in the column is approximately even.
  • In this case, set the hash partition key to be the column frequently grouped-by or joined-on, and set the PartitionAssignmentMode property to ByPartition.
• Out-of-order data ingestion:
  • Data ingested into a table might not be ordered and partitioned into extents (shards) according to a specific datetime column that represents the data creation time and is commonly used to filter data. This could be due to a backfill from heterogeneous source files that include datetime values over a large time span.
  • In this case, set the uniform range datetime partition key to be the datetime column.
  • If you need retention and caching policies to align with the datetime values in the column, instead of aligning with the time of ingestion, set the OverrideCreationTime property to true.

Partition keys

The following kinds of partition keys are supported.

Hash partition key

If the policy includes a hash partition key, all homogeneous extents that belong to the same partition will be assigned to the same data node.

• A hash-modulo function is used to partition the data.
• Data in homogeneous (partitioned) extents is ordered by the hash partition key.
  • You don’t need to include the hash partition key in the row order policy, if one is defined on the table.
• Queries that use the shuffle strategy, and in which the shuffle key used in join, summarize or make-series is the table’s hash partition key, are expected to perform better because the amount of data required to move across nodes is reduced.

Partition properties

Hash partition key example

A hash partition key over a string-typed column named tenant_id. It uses the XxHash64 hash function, with MaxPartitionCount set to the recommended value 128, and the default Seed of 1.

{
  "ColumnName": "tenant_id",
  "Kind": "Hash",
  "Properties": {
    "Function": "XxHash64",
    "MaxPartitionCount": 128,
    "Seed": 1,
    "PartitionAssignmentMode": "Uniform"
  }
}

Uniform range datetime partition key

In these cases, you can reshuffle the data between extents so that each extent includes records from a limited time range. This process results in filters on the datetime column being more effective at query time.

The partition function used is bin_at() and isn’t customizable.

Partition properties

Uniform range datetime partition example

The snippet shows a uniform datetime range partition key over a datetime typed column named timestamp. It uses datetime(2021-01-01) as its reference point, with a size of 7d for each partition, and doesn’t override the extents’ creation times.

{
  "ColumnName": "timestamp",
  "Kind": "UniformRange",
  "Properties": {
    "Reference": "2021-01-01T00:00:00",
    "RangeSize": "7.00:00:00",
    "OverrideCreationTime": false
  }
}

The policy object

By default, a table’s data partitioning policy is null, in which case data in the table won’t be repartitioned after it’s ingested.

The data partitioning policy has the following main properties:

• PartitionKeys:

  • A collection of partition keys that define how to partition the data in the table.
  • A table can have up to 2 partition keys, with one of the following options:
    • One hash partition key.
    • One uniform range datetime partition key.
    • One hash partition key and one uniform range datetime partition key.
  • Each partition key has the following properties:
    • ColumnName: string - The name of the column according to which the data will be partitioned.
    • Kind: string - The data partitioning kind to apply (Hash or UniformRange).
    • Properties: property bag - Defines parameters according to which partitioning is done.

• EffectiveDateTime:

  • The UTC datetime from which the policy is effective.
  • This property is optional. If it isn’t specified, the policy will take effect for data ingested after the policy was applied.

Data partitioning example

Data partitioning policy object with two partition keys.

1. A hash partition key over a string-typed column named tenant_id.
   • It uses the XxHash64 hash function, with MaxPartitionCount set to the recommended value 128, and the default Seed of 1.
2. A uniform datetime range partition key over a datetime type column named timestamp.
   • It uses datetime(2021-01-01) as its reference point, with a size of 7d for each partition.

{
  "PartitionKeys": [
    {
      "ColumnName": "tenant_id",
      "Kind": "Hash",
      "Properties": {
        "Function": "XxHash64",
        "MaxPartitionCount": 128,
        "Seed": 1,
        "PartitionAssignmentMode": "Uniform"
      }
    },
    {
      "ColumnName": "timestamp",
      "Kind": "UniformRange",
      "Properties": {
        "Reference": "2021-01-01T00:00:00",
        "RangeSize": "7.00:00:00",
        "OverrideCreationTime": false
      }
    }
  ]
}

Additional properties

The following properties can be defined as part of the policy. These properties are optional and we recommend not changing them.

The data partitioning process

• Data partitioning runs as a post-ingestion background process.
  • A table that is continuously ingested into is expected to always have a “tail” of data that is yet to be partitioned (nonhomogeneous extents).
• Data partitioning runs only on hot extents, regardless of the value of the EffectiveDateTime property in the policy.
  • If partitioning cold extents is required, you need to temporarily adjust the caching policy.

You can monitor the partitioning status of tables with defined policies in a database by using the .show database extents partitioning statistics command and partitioning metrics.

Partitioning capacity

• The data partitioning process results in the creation of more extents. The extents merge capacity may gradually increase, so that the process of merging extents can keep up.

• If there’s a high ingestion throughput, or a large enough number of tables that have a partitioning policy defined, then the Extents partition capacity may gradually increase, so that the process of partitioning extents can keep up.

• To avoid consuming too many resources, these dynamic increases are capped. You may be required to gradually and linearly increase them beyond the cap, if they’re used up entirely.

  • If increasing the capacities causes a significant increase in the use of the cluster’s resources, you can scale the cluster up/out, either manually, or by enabling autoscale.

Limitations

• Attempts to partition data in a database that already has more than 5,000,000 extents will be throttled.
  • In such cases, the EffectiveDateTime property of partitioning policies of tables in the database will be automatically delayed by several hours, so that you can reevaluate your configuration and policies.

Outliers in partitioned columns

• The following situations can contribute to imbalanced distribution of data across nodes, and degrade query performance:
  • If a hash partition key includes values that are much more prevalent than others, for example, an empty string, or a generic value (such as null or N/A).
  • The values represent an entity (such as tenant_id) that is more prevalent in the dataset.
• If a uniform range datetime partition key has a large enough percentage of values that are “far” from the majority of the values in the column, the overhead of the data partitioning process is increased and may lead to many small extents to keep track of. An example of such a situation is datetime values from the distant past or future.

In both of these cases, either “fix” the data, or filter out any irrelevant records in the data before or at ingestion time, to reduce the overhead of the data partitioning. For example, use an update policy.

Related content

• Partitioning policy management commands

7.14 - Query acceleration policy

7.14.1 - Query acceleration policy (preview)

An external table is a schema entity that references data stored external to a Kusto database. Queries run over external tables can be less performant than on data that is ingested due to various factors such as network calls to fetch data from storage, the absence of indexes, and more. Query acceleration allows specifying a policy on top of external delta tables. This policy defines a number of days to accelerate data for high-performance queries.

Query acceleration is supported in Azure Data Explorer over Azure Data Lake Store Gen2 or Azure blob storage external tables.

Query acceleration is supported in Eventhouse over OneLake, Azure Data Lake Store Gen2, or Azure blob storage external tables.

To enable query acceleration in the Fabric UI, see Query acceleration over OneLake shortcuts.

Limitations

• The number of columns in the external table can’t exceed 900.
• Delta tables with checkpoint V2 are not supported.
• Query performance over accelerated external delta tables which have partitions may not be optimal during preview.
• The feature assumes delta tables with static advanced features, for example column mapping doesn’t change, partitions don’t change, and so on. To change advanced features, first disable the policy, and once the change is made, re-enable the policy.
• Schema changes on the delta table must also be followed with the respective .alter external delta table schema, which might result in acceleration starting from scratch if there was breaking schema change.
• Index-based pruning isn’t supported for partitions.
• Parquet files larger than 1 GB won’t be cached.
• Query acceleration isn’t supported for external tables with impersonation authentication.

Known issues

• Data in the external delta table that is optimized with the OPTIMIZE function will need to be reaccelearted.
• If you run frequent MERGE/UPDATE/DELETE operations in delta, the underlying parquet files may be rewritten with changes and Kusto will skip accelerating such files, causing retrieval during query time.
• The system assumes that all artifacts under the delta table directory have the same access level to the selected users. Different files having different access permissions under the delta table directory might result with unexpected behavior.

Commands for query acceleration

• .alter query acceleration policy command
• .delete query acceleration policy command
• .show query acceleration policy command
• .show external table operations query_acceleration statistics

7.15 - Query week consistency policy

7.15.1 - Query weak consistency policy

The query weak consistency policy is a cluster-level policy object that configures the weak consistency service.

Management commands

• Use .show cluster policy query_weak_consistency to show the current query weak consistency policy of the cluster.
• Use .alter cluster policy query_weak_consistency to change the current query weak consistency policy of the cluster.

The policy object

The query weak consistency policy includes the following properties:

Default policy

The default policy is:

{
  "PercentageOfNodes": -1,
  "MinimumNumberOfNodes": -1,
  "MaximumNumberOfNodes": -1,
  "SuperSlackerNumberOfNodesThreshold": -1,
  "EnableMetadataPrefetch": false,
  "MaximumLagAllowedInMinutes": -1,
  "RefreshPeriodInSeconds": -1
}

7.16 - Restricted view access

7.16.1 - Restricted view access policy

The restricted view access policy is an optional security feature that governs view permissions on a table. By default, the policy is disabled. When enabled, the policy adds an extra layer of permission requirements for principals to access and view the table.

For a table with an enabled restricted view access policy, only principals assigned the UnrestrictedViewer role have the necessary permissions to view the table. Even principals with roles like Table Admin or Database Admin are restricted unless granted the UnrestrictedViewer role.

While the restricted view access policy is specific to individual tables, the UnrestrictedViewer role operates at the database level. Thereby, a principal with the UnrestrictedViewer role has view permissions for all tables within the database. For more detailed information on managing table view access, see Manage view access to tables.

Limitations

• The restricted view access policy can’t be configured on a table on which a Row Level Security policy is enabled.
• A table with the restricted view access policy enabled can’t be used as the source of a materialized view. For more information, see materialized views limitations and known issues.

Related content

• Role-based access control
• Manage database security roles
• .show restricted_view_access policy
• .alter restricted_view_access policy
• .delete restricted_view_access policy

7.17 - Retention policy

7.17.1 - Retention policy

The retention policy controls the mechanism that automatically removes data from tables or materialized views. It’s useful to remove data that continuously flows into a table, and whose relevance is age-based. For example, the policy can be used for a table that holds diagnostics events that may become uninteresting after two weeks.

The retention policy can be configured for a specific table or materialized view, or for an entire database. The policy then applies to all tables in the database that don’t override it. When the policy is configured both at the database and table level, the retention policy in the table takes precedence over the database policy.

Setting up a retention policy is important when continuously ingesting data, which will limit costs.

Data that is “outside” the retention policy is eligible for removal. There’s no specific guarantee when removal occurs. Data may “linger” even if the retention policy is triggered.

The retention policy is most commonly set to limit the age of the data since ingestion. For more information, see SoftDeletePeriod.

deleted before the limit is exceeded, but deletion isn’t immediate following that point.

The policy object

A retention policy includes the following properties:

• SoftDeletePeriod:
  • Time span for which it’s guaranteed that the data is kept available to query. The period is measured starting from the time the data was ingested.
  • Defaults to 1,000 years.
  • When altering the soft-delete period of a table or database, the new value applies to both existing and new data.
• Recoverability:
  • Data recoverability (Enabled/Disabled) after the data was deleted.
  • Defaults to Enabled.
  • If set to Enabled, the data will be recoverable for 14 days after it’s been soft-deleted.
  • It is not possible to configure the recoverability period.

Management commands

• Use .show policy retention to show the current retention policy for a database, table, or materialized view.
• Use .alter policy retention to change current retention policy of a database, table, or materialized view.