更新策略命令update policy commands

更新策略是一个表级别策略对象,它自动运行查询,然后在将数据引入到另一个表时引入结果。The update policy is a table-level policy object that automatically runs a query and then ingests the results when data is ingested into another table.

显示更新策略Show update policy

如果将 * 用作表名称,此命令将返回指定表或默认数据库中所有表的更新策略。This command returns the update policy of the specified table, or all tables in the default database if * is used as a table name.

语法Syntax

  • .show table TableName policy update.show table TableName policy update
  • .show table DatabaseName.TableName policy update .show table DatabaseName.TableName policy update
  • .show table * policy update.show table * policy update

返回Returns

此命令返回每个表都有一条记录的表。This command returns a table that has one record per table.

Column 类型Type 说明Description
EntityNameEntityName string 对其定义更新策略的实体的名称The name of the entity the update policy is defined on
策略Policies string 一个 JSON 数组,指示为该实体定义的所有更新策略,格式设置为更新策略对象A JSON array indicating all update policies defined for the entity, formatted as update policy object

示例Example

.show table DerivedTableX policy update 
EntityNameEntityName 策略Policies
[TestDB].[DerivedTableX][TestDB].[DerivedTableX] [{"IsEnabled": true, "Source":"MyTableX","Query":"MyUpdateFunction()","IsTransactional": false,"PropagateIngestionProperties": false}][{"IsEnabled": true, "Source": "MyTableX","Query": "MyUpdateFunction()","IsTransactional": false,"PropagateIngestionProperties": false}]

更改更新策略Alter update policy

此命令设置指定表的更新策略。This command sets the update policy of the specified table.

语法Syntax

  • .alter table TableName policy update ArrayOfUpdatePolicyObjects .alter table TableName policy update ArrayOfUpdatePolicyObjects
  • .alter table DatabaseName.TableName policy update ArrayOfUpdatePolicyObjects .alter table DatabaseName.TableName policy update ArrayOfUpdatePolicyObjects

ArrayOfUpdatePolicyObjects 是一个定义了零个或多个更新策略对象的 JSON 数组。ArrayOfUpdatePolicyObjects is a JSON array that has zero or more update policy objects defined.

备注

  • 对更新策略对象的 Query 属性使用存储的函数。Use a stored function for the Query property of the update policy object. 只需修改函数定义,而不是整个策略对象。You will only need to modify the function definition, instead of the entire policy object.
  • 如果 IsEnabled 设置为 true,则在设置更新策略时对其执行以下验证:If IsEnabled is set to true, then the following validations are performed on the update policy as it's being set:
    • Source - 检查目标数据库中是否存在该表。Source - Checks that the table exists in the target database.
    • Query
      • 检查架构定义的架构是否与目标表中的架构匹配Checks that the schema defined by the schema matches the one of the target table
      • 检查查询是否引用更新策略的 source 表。Checks that the query references the source table of the update policy. 可以通过在 with 属性中设置 AllowUnreferencedSourceTable=true 来定义不引用源的更新策略查询(请参见下面的示例),但是由于性能问题,不建议使用此策略。Defining an update policy query which does not reference the source is possible by setting AllowUnreferencedSourceTable=true in the with properties (see example below), but isn't recommended due to performance issues. 每次将数据引入到源表时,更新策略执行起来会考虑不同表中的所有记录。For every ingestion to the source table, all records in a different table are considered for the update policy execution.
      • 检查策略是否不会导致在目标数据库中的更新策略链中创建循环。Checks that the policy doesn't result in a cycle being created in the chain of update policies in the target database.
  • 如果 IsTransactional 设置为 true,则检查 TableAdmin 权限是否也针对 Source(源表)进行了验证。If IsTransactional is set to true, then checks that the TableAdmin permissions are also verified against Source (the source table).
  • 测试更新策略或函数的性能,然后应用它以在源表每次引入时运行。Test your update policy or function for performance, before applying it to run on each ingestion to the source table. 有关详细信息,请参阅测试更新策略的性能影响For more information, see testing an update policy's performance impact.

返回Returns

此命令设置表的更新策略对象,替代所有当前策略,然后返回相应的 .show table update policy 命令的输出。The command sets the table's update policy object, overriding any current policy, and then returns the output of the corresponding .show table update policy command.

示例Example

// Create a function that will be used for update
.create function 
MyUpdateFunction()
{
    MyTableX
    | where ColumnA == 'some-string'
    | summarize MyCount=count() by ColumnB, Key=ColumnC
    | join (OtherTable | project OtherColumnZ, Key=OtherColumnC) on Key
    | project ColumnB, ColumnZ=OtherColumnZ, Key, MyCount
}

// Create the target table (if it doesn't already exist)
.set-or-append DerivedTableX <| MyUpdateFunction() | limit 0

// Use update policy on table DerivedTableX
.alter table DerivedTableX policy update
@'[{"IsEnabled": true, "Source": "MyTableX", "Query": "MyUpdateFunction()", "IsTransactional": false, "PropagateIngestionProperties": false}]'
  • 当发生对源表(在本例中为 MyTableX)的引入时,在该表中创建一个或多个盘区(数据分片)When an ingestion to the source table occurs, in this case, MyTableX, one or more extents (data shards) are created in that table
  • 在更新策略对象(在本例中为 MyUpdateFunction())中定义的 Query 将仅在这些区上运行,而不会在整个表上运行。The Query that is defined in the update policy object, in this case MyUpdateFunction(), will only run on those extents, and won't run on the entire table.
    • “范围界定”是在内部自动完成的,在定义 Query 时不应进行处理。"Scoping" is done internally and automatically, and shouldn't be handled when defining the Query.
    • 当引入到 DerivedTableX 派生表时,仅考虑每个引入操作中不同的新引入记录。Only newly ingested records, that are different in each ingestion operation, will be taken into consideration when ingesting to the DerivedTableX derived table.
// The following example will throw an error for not referencing the source table in the update policy query
// The policy's source table is MyTableX, whereas the query only references MyOtherTable. 
.alter table DerivedTableX policy update
@'[{"IsEnabled": true, "Source": "MyTableX", "Query": "MyOtherTable", "IsTransactional": false, "PropagateIngestionProperties": false}]'

// Adding the following properties will suppress the error (but is not recommended)
.alter table DerivedTableX policy update with (AllowUnreferencedSourceTable=true)
@'[{"IsEnabled": true, "Source": "MyTableX", "Query": "MyOtherTable", "IsTransactional": false, "PropagateIngestionProperties": false}]'

.alter-merge table TableName policy update.alter-merge table TableName policy update

此命令修改指定表的更新策略。This command modifies the update policy of the specified table.

语法Syntax

  • .alter-merge table TableName policy update ArrayOfUpdatePolicyObjects .alter-merge table TableName policy update ArrayOfUpdatePolicyObjects
  • .alter-merge table DatabaseName.TableName policy update ArrayOfUpdatePolicyObjects .alter-merge table DatabaseName.TableName policy update ArrayOfUpdatePolicyObjects

ArrayOfUpdatePolicyObjects 是一个定义了零个或多个更新策略对象的 JSON 数组。ArrayOfUpdatePolicyObjects is a JSON array that has zero or more update policy objects defined.

备注

  • 对批量实现更新策略对象的查询属性使用存储的函数。Use stored functions for the bulk implementation of the query property of the update policy object. 只需修改函数定义,不需修改整个策略对象。You'll only need to modify the function definition instead of the entire policy object.
  • 该验证与对 alter 命令执行的验证相同。The validations are the same as those done on an alter command.

返回Returns

此命令追加到表的更新策略对象,替代所有当前策略,然后返回相应的 .show table TableName update policy 命令的输出。The command appends to the table's update policy object, overriding any current policy, and then returns the output of the corresponding .show table TableName update policy command.

示例Example

.alter-merge table DerivedTableX policy update 
@'[{"IsEnabled": true, "Source": "MyTableY", "Query": "MyUpdateFunction()", "IsTransactional": false}]'  

.delete table TableName policy update.delete table TableName policy update

删除指定表的更新策略。Deletes the update policy of the specified table.

语法Syntax

  • .delete table TableName policy update.delete table TableName policy update
  • .delete table DatabaseName.TableName policy update .delete table DatabaseName.TableName policy update

返回Returns

此命令删除表的更新策略对象,然后返回相应的 .show table TableName update policy 命令的输出。The command deletes the table's update policy object and then returns the output of the corresponding .show table TableName update policy command.

示例Example

.delete table DerivedTableX policy update