从查询引入(.set、.append、.set-or-append、.set-or-replace)Ingest from query (.set, .append, .set-or-append, .set-or-replace)

这些命令执行查询或控制命令,并将查询结果引入表中。These commands execute a query or a control 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.

命令Command 如果表存在If table exists 如果表不存在If table doesn't exist
.set 命令将失败The command fails 将创建表并引入数据The table is created and data is ingested
.append 数据将追加到表Data is appended to the table 命令将失败The command fails
.set-or-append 数据将追加到表Data is appended to the table 将创建表并引入数据The table is created and data is ingested
.set-or-replace 数据将替换表中的数据Data replaces the data in the table 将创建表并引入数据The table is created and data is ingested

语法Syntax

.set [async] TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand.set [async] TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand

.append [async] TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand.append [async] TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand

.set-or-append [async] TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand.set-or-append [async] TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand

.set-or-replace [async] TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand.set-or-replace [async] TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand

参数Arguments

  • async:如果指定了此项,命令会立即返回,并继续在后台执行引入操作。async: If specified, the command will immediately return and continue ingestion in the background. 命令的结果将包含一个 OperationId 值,该值随后可以与 .show operations 命令一起使用,以检索引入完成状态和结果。The results of the command will include an OperationId value that can then be used with the .show operations command, to retrieve the ingestion completion status and results.
  • TableName:要将数据引入到其中的表的名称。TableName: The name of the table to ingest data to. 表名称始终与上下文中的数据库相关。The table name is always related to the database in context.
  • PropertyName、PropertyValue:影响引入过程的任意数量的引入属性。PropertyName, PropertyValue: Any number of ingestion properties that affect the ingestion process.

受支持的引入属性。Ingestion properties that are supported.

属性Property 说明Description
creationTime 日期时间值,格式为 ISO8601 字符串,在创建引入的数据盘区时使用。The datetime value, formatted as an ISO8601 string, to use at the creation time of the ingested data extents. 如果未指定,将使用当前值 (now())If unspecified, the current value (now()) will be used
extend_schema 一个布尔值,如果指定了此项,将指示命令扩展表的架构。A Boolean value that, if specified, instructs the command to extend the schema of the table. 默认值为“false”。Default is "false". 此选项仅适用于 .append.set-or-appendset-or-replace 命令。This option applies only to .append, .set-or-append, and set-or-replace commands. 进行架构扩展时,只允许在表的末尾添加其他列The only permitted schema extensions have additional columns added to the table at the end
recreate_schema 一个布尔值,A Boolean value that. 如果指定了此项,它将指示此命令是否可以重新创建表的架构。If specified, describes if the command may recreate the schema of the table. 默认值为“false”。Default is "false". 此选项仅适用于 set-or-replace 命令。This option applies only to the set-or-replace command. 在同时设置的情况下,此选项优先于 extend_schema 属性This option takes precedence over the extend_schema property if both are set
folder 要分配给表的文件夹。The folder to assign to the table. 如果表已存在,则此属性会替代表的文件夹。If the table already exists, this property will overwrite the table's folder.
ingestIfNotExists 一个字符串值,A string value that. 如果指定了此项,则当表的数据已通过一个具有相同值的 ingest-by: 标记进行了标记时,引入操作会失败If specified, prevents ingestion from succeeding if the table already has data tagged with an ingest-by: tag with the same value
policy_ingestiontime 一个布尔值。A Boolean value. 当指定了此项时,它将指示是否在通过此命令创建的表上启用引入时间策略If specified, describes if to enable the Ingestion Time Policy on a table that is created by this command. 默认值为“true”The default is "true"
tags 一个 JSON 字符串,指示在引入期间要运行哪些验证A JSON string that indicates which validations to run during ingestion
docstring 一个用来记录表的字符串A string documenting the table

用于控制命令行为的属性。Property that controls the behavior of the command.

属性Property 类型Type 说明Description
distributed bool 指示此命令以并行方式从执行查询的所有节点引入。Indicates that the command ingests from all nodes executing the query in parallel. 默认值为“false”。Default is "false". 请参阅下面的备注。See remarks below.
  • QueryOrCommand:其结果将用作要引入的数据的查询或控制命令的文本。QueryOrCommand: The text of a query or a control command whose results will be used as data to ingest.

备注

仅支持 .show 控制命令。Only .show control commands are supported.

备注Remarks

  • .set-or-replace 将替换表的数据(如果存在)。.set-or-replace replaces the data of the table if it exists. 它会删除现有的数据分片或创建目标表(如果不存在)。It drops the existing data shards or creates the target table, if doesn't already exist. 除非将 extend_schemarecreate_schema 引入属性之一设置为“true”,否则将保留表架构。The table schema will be preserved unless one of extend_schema or recreate_schema ingestion properties is set to "true". 如果对架构进行了修改,则这是在实际引入数据之前在其自己的事务中进行的。If the schema is modified, it happens before the actual data ingestion in its own transaction. 因此,引入数据失败并不意味着未修改架构。A failure to ingest the data doesn't mean the schema wasn't modified.
  • .set-or-append.append 命令会保留架构,除非将 extend_schema 引入属性设置为“true”。.set-or-append and .append commands will preserve the schema unless the extend_schema ingestion property is set to "true". 如果对架构进行了修改,则这是在实际引入数据之前在其自己的事务中进行的。If the schema is modified, it happens before the actual data ingestion in its own transaction. 因此,引入数据失败并不意味着未修改架构。A failure to ingest the data doesn't mean the schema wasn't modified.
  • 建议将数据引入量限制为每个引入操作少于 1 GB。We recommended that you limit the data for ingestion to less than 1 GB per ingestion operation. 可以根据需要使用多个引入命令。Multiple ingestion commands may be used, if necessary.
  • 数据引入是一项占用大量资源的操作,可能会影响群集上的并发活动(包括正在运行的查询)。Data ingestion is a resource-intensive operation that might affect concurrent activities on the cluster, including running queries. 请避免同时运行过多的此类命令。Avoid running too many such commands at the same time.
  • 将结果集架构与目标表架构匹配时,比较是基于列类型进行的。When matching the result set schema to that of the target table, the comparison 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. 否则,数据将引入到错误的列中。Else data will be ingested into the wrong column.
  • 如果查询生成的数据量很大,超过 1 GB,并且查询不需要序列化,则可将 distributed 标志设置为“true”,使多个节点可以并行产生输出。Setting the distributed flag to "true" is useful when the amount of data being produced by the query is large, exceeds 1GB, and the query doesn't require serialization, so that multiple nodes can produce output in parallel. 如果查询结果较小,请不要使用此标志,因为它可能会不必要地生成很多小数据分片。When the query results are small, don't use this flag, since it might needlessly generate many small data shards.

示例Examples

在数据库中创建一个名为“RecentErrors”的新表,使该表具有与“LogsTable”相同的架构并保存过去一小时的所有错误记录。Create a new table called "RecentErrors" in the database that has the same schema as "LogsTable" and holds all the error records of the last hour.

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

在数据库中创建一个名为“OldExtents”的新表,使该表包含单个列(即“ExtentId”),并保存数据库中已创建 30 天以上的所有盘区的盘区 ID。Create a new table called "OldExtents" in the database that has a single column, "ExtentId", and holds the extent IDs of all extents in the database that has been created more than 30 days earlier. 数据库有一个名为“MyExtents”的现有表。The database has an existing table named "MyExtents".

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

在当前数据库中将数据追加到名为“OldExtents”的现有表,该表包含单个列(即“ExtentId”),并保存数据库中已创建 30 天以上的所有盘区的盘区 ID。Append data to an existing table called "OldExtents" in the current database that has a single column, "ExtentId", and holds the extent IDs of all extents in the database that have been created more than 30 days earlier. 基于名为“MyExtents”的现有表,用标记 tagAtagB 来标记新盘区。Mark the new extent with tags tagA and tagB, based on an existing table named "MyExtents".

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

在当前数据库中将数据追加到“OldExtents”表,或者创建该表(如果不存在)。Append data to the "OldExtents" table in the current database, or create the table if it doesn't already exist. 使用 ingest-by:myTag 标记新盘区。Tag the new extent with ingest-by:myTag. 只有在表尚未包含使用 ingest-by:myTag 标记的盘区的情况下,才应当基于名为“MyExtents”的现有表来进行标记。Do so only if the table doesn't already contain an extent tagged with ingest-by:myTag, based on an existing table named "MyExtents".

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

在当前数据库中替换“OldExtents”表中的数据,或者创建该表(如果不存在)。Replace the data in the "OldExtents" table in the current database, or create the table if it doesn't already exist. 使用 ingest-by:myTag 标记新盘区。Tag the new extent with ingest-by:myTag.

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

在当前数据库中将数据追加到“OldExtents”表,并将已创建盘区的创建时间设置为过去的某个具体日期/时间。Append data to the "OldExtents" table in the current database, while setting the created extent(s) creation time to a specific datetime in the past.

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

返回输出Return output

返回通过 .set.append 命令创建的盘区的信息。Returns information on the extents created because of the .set or .append command.

示例输出Example output

ExtentIdExtentId OriginalSizeOriginalSize ExtentSizeExtentSize CompressedSizeCompressedSize IndexSizeIndexSize RowCountRowCount
23a05ed6-376d-4119-b1fc-6493bcb0556323a05ed6-376d-4119-b1fc-6493bcb05563 12911291 58825882 15681568 43144314 10 个10