Redigera

Dela via

.replace extents command

  • Artikel

Applies to: ✅ Azure Data Explorer

This command drops and moves extents in a single transaction within the context of a specific database. The command drops any specified extents from the destination table and moves specified extents from source tables to the destination table.

Note

Data shards are called extents, and all commands use "extent" or "extents" as a synonym. For more information on extents, see Extents (data shards) overview.

Permissions

You must have at least Table Admin permissions for the source and destination tables.

Restrictions

  • Both source and destination tables must be in the context database.
  • All extents specified by the ExtentsToDropQuery are expected to belong to the destination table.
  • All columns in the source tables are expected to exist in the destination table with the same name and data type.
  • If the destination table is a source table of a materialized view, the command might fail since the materialized view can't process the records in the moved extents. See more details in the materialized views limitations page. You can work around this error by setting a new ingestion time during the move command. See setNewIngestionTime in supported properties.

Syntax

.replace [async] extents in table DestinationTableName [ with (PropertyName = PropertyValue [, ...])] <| {ExtentsToDropQuery},{ExtentsToMoveQuery}

Learn more about syntax conventions.

Parameters

Name Type Required Description
async string If specified, the command runs asynchronously.
DestinationTableName string ✔️ The name of the table to which to move the extents.
FromDate datetime The query window start date.
ToDate datetime The query window end date.
PropertyName, PropertyValue string One or more Supported properties.
ExtentsToDropQuery string ✔️ The results of this query must include the ExtentId column, which contains the extent IDs to be removed from the destination table.
ExtentsToMoveQuery string ✔️ The results of this query must include the ExtentId and TableName columns, which contains the source tables and extent IDs to be moved to the destination table.

Supported properties

Property name Type Required Description
setNewIngestionTime bool If set to true, a new ingestion time is assigned to all records in extents being moved. This is useful when records are processed by workloads that depend on database cursors, such as materialized views and continuous data export.
extentCreatedOnFrom datetime ✔️ Apply on extents created after this point in time.
extentCreatedOnTo datetime ✔️ Apply on extents created before this point in time.

Note

For better performance, set extentCreatedOnFrom and extentCreatedOnTo parameters to the smallest possible range.

Returns

When the command is run synchronously, a table with the following schema is returned.

Output parameter Type Description
OriginalExtentId string A unique identifier (GUID) for the original extent in the source table that was moved to the destination table, or the extent in the destination table that was dropped.
ResultExtentId string A unique identifier (GUID) for the result extent moved from the source table to the destination table. Empty, if the extent was dropped from the destination table. Upon failure: "Failed."
Details string Includes the failure details if the operation fails.

When the command is run asynchronously, an operation ID (GUID) is returned. Monitor the operation's status with the .show operations command, and retrieve the results of a successful execution with the .show operation details command.

Note

The command will fail if extents returned by the ExtentsToDropQuery query don't exist in the destination table. This may happen if the extents were merged before the replace command was executed. To make sure the command fails on missing extents, check that the query returns the expected ExtentIds. The first example will fail if the extent to drop doesn't exist in table MyOtherTable. The second example, however, will succeed even though the extent to drop doesn't exist, since the query to drop didn't return any extent IDs.

Examples

Move all extents in a specified creation time range from two tables

Move all extents from two specific tables (MyTable1, MyTable2) in a specified creation time range to table MyOtherTable, and drop all extents in MyOtherTable tagged with drop-by:MyTag:

.replace extents in table MyOtherTable with (extentCreatedOnFrom=datetime(2023-03-10), extentCreatedOnTo=datetime(2023-03-12)) <|
    {
        .show table MyOtherTable extents where tags has 'drop-by:MyTag'
    },
    {
        .show tables (MyTable1,MyTable2) extents
    }

Sample output

OriginalExtentId ResultExtentId Details
e133f050-a1e2-4dad-8552-1f5cf47cab69 0d96ab2d-9dd2-4d2c-a45e-b24c65aa6687
cdbeb35b-87ea-499f-b545-defbae091b57 a90a303c-8a14-4207-8f35-d8ea94ca45be
4fcb4598-9a31-4614-903c-0c67c286da8c 97aafea1-59ff-4312-b06b-08f42187872f
2dfdef64-62a3-4950-a130-96b5b1083b5a 0fb7f3da-5e28-4f09-a000-e62eb41592df

Move all extents in a specified creation time range from one table to another, drop specific extent

Move all extents in a specified creation time range from one specific table (MyTable1) to table MyOtherTable, and drop a specific extent in MyOtherTable, by its ID:

.replace extents in table MyOtherTable with (extentCreatedOnFrom=datetime(2023-03-10), extentCreatedOnTo=datetime(2023-03-12)) <|
    {
        print ExtentId = "2cca5844-8f0d-454e-bdad-299e978be5df"
    },
    {
        .show table MyTable1 extents 
    }

.replace extents in table MyOtherTable with (extentCreatedOnFrom=datetime(2023-03-10), extentCreatedOnTo=datetime(2023-03-12)) <|
    {
        .show table MyOtherTable extents
        | where ExtentId == guid(2cca5844-8f0d-454e-bdad-299e978be5df) 
    },
    {
        .show table MyTable1 extents 
    }

Implement an idempotent logic

Implement an idempotent logic so that Kusto drops extents from table t_dest only if there are extents to move from table t_source to table t_dest:

.replace async extents in table t_dest with (extentCreatedOnFrom=datetime(2023-03-10), extentCreatedOnTo=datetime(2023-03-12)) <|
{
    let any_extents_to_move = toscalar( 
        t_source
        | where extent_tags() has 'drop-by:blue'
        | summarize count() > 0
    );
    let extents_to_drop =
        t_dest
        | where any_extents_to_move and extent_tags() has 'drop-by:blue'
        | summarize by ExtentId = extent_id()
    ;
    extents_to_drop
},
{
    let extents_to_move = 
        t_source
        | where extent_tags() has 'drop-by:blue'
        | summarize by ExtentId = extent_id(), TableName = 't_source'
    ;
    extents_to_move
}