union 运算符union operator

获取两个或多个表，并返回表中的所有行。Takes two or more tables and returns the rows of all of them.

Table1 | union Table2, Table3

语法Syntax

T | union [UnionParameters] [kind= inner|outer] [withsource=ColumnName] [isfuzzy= true|false] Table [, Table]...T | union [UnionParameters] [kind= inner|outer] [withsource=ColumnName] [isfuzzy= true|false] Table [, Table]...

不带管道输入的替代形式：Alternative form with no piped input:

union [UnionParameters] [kind= inner|outer] [withsource=ColumnName] [isfuzzy= true|false] Table [, Table]...union [UnionParameters] [kind= inner|outer] [withsource=ColumnName] [isfuzzy= true|false] Table [, Table]...

参数Arguments

  • Table:Table:

    • 表的名称，例如 Events；或The name of a table, such as Events; or
    • 必须用圆括号括起来的查询表达式，（例如 (Events | where id==42) 或 (cluster("https://help.kusto.chinacloudapi.cn:443").database("Samples").table("*"))）；或A query expression that must be enclosed with parenthesis, such as (Events | where id==42) or (cluster("https://help.kusto.chinacloudapi.cn:443").database("Samples").table("*")); or
    • 使用通配符指定的一组表。A set of tables specified with a wildcard. 例如，E* 将构成数据库中所有名称以 E 开头的表的并集。For example, E* would form the union of all the tables in the database whose names begin E.

  • kind:kind:

    • inner - 此结果包含所有输入表所共有列的子集。inner - The result has the subset of columns that are common to all of the input tables.
    • outer -（默认值）。outer - (default). 结果包含所有输入中出现的所有列。The result has all the columns that occur in any of the inputs. 未由输入行定义的单元格设置为 null。Cells that weren't defined by an input row are set to null.

  • withsource=ColumnName：如果指定了此项，则输出将包括一个名为 ColumnName 的列，其值指示哪个源表提供了每一行。withsource=ColumnName: If specified, the output will include a column called ColumnName whose value indicates which source table has contributed each row. 如果查询（在通配符匹配后）有效地引用多个数据库（默认数据库总计在内）中的表，则此列的值将具有使用数据库进行限定的表名。If the query effectively (after wildcard matching) references tables from more than one database (default database always counts) the value of this column will have a table name qualified with the database. 同样，如果引用了多个群集，则值中将存在群集和数据库限定条件。Similarly cluster and database qualifications will be present in the value if more than one cluster is referenced.

  • isfuzzy= true | false：如果 isfuzzy 设置为 true - 则允许对 union 支线进行模糊解析。isfuzzy= true | false: If isfuzzy is set to true - allows fuzzy resolution of union legs. Fuzzy 适用于 union 源集。Fuzzy applies to the set of union sources. 这意味着，在分析查询和准备执行的过程中，union 源的集合被缩减为当时存在并且可供访问的一组表引用。It means that while analyzing the query and preparing for execution, the set of union sources is reduced to the set of table references that exist and are accessible at the time. 如果至少找到了一个这样的表，则任何解析失败都将在查询状态结果中产生警告（每个缺少的引用都会产生一个），但不会阻止查询执行；如果没有任何解析成功，则查询会返回错误。If at least one such table was found, any resolution failure will yield a warning in the query status results (one for each missing reference), but will not prevent the query execution; if no resolutions were successful - the query will return an error. 默认为 isfuzzy= false。The default is isfuzzy= false.

  • UnionParameters：零个或零个以上（以空格分隔）以 Name = Value 形式表示的参数，用于控制行匹配操作和执行计划的行为 。UnionParameters: Zero or more (space-separated) parameters in the form of Name = Value that control the behavior of the row-match operation and execution plan. 支持以下参数：The following parameters are supported:

    名称Name 值Values 说明Description
    hint.concurrency 数字Number 提示系统应并行执行 union 运算符的多少个并发子查询。Hints the system how many concurrent subqueries of the union operator should be executed in parallel. 默认：群集的单个节点上的 CPU 核心数（2 到 16 个）。Default: Amount of CPU cores on the single node of the cluster (2 to 16).
    hint.spread 数字Number 提示系统并发的 union 子查询执行应使用多少个节点。Hints the system how many nodes should be used by the concurrent union subqueries execution. 默认：1.Default: 1.
  • Table:Table:
    • 表的名称，例如 EventsThe name of a table, such as Events
    • 必须用圆括号括起来的查询表达式，例如 (Events | where id==42)A query expression that must be enclosed with parenthesis, such as (Events | where id==42)
    • 使用通配符指定的一组表。A set of tables specified with a wildcard. 例如，E* 将构成数据库中所有名称以 E 开头的表的并集。For example, E* would form the union of all the tables in the database whose names begin E.
  • kind:kind:
    • inner - 此结果包含所有输入表所共有列的子集。inner - The result has the subset of columns that are common to all of the input tables.
    • outer -（默认值）。outer - (default). 结果包含所有输入中出现的所有列。The result has all the columns that occur in any of the inputs. 未由输入行定义的单元格设置为 null。Cells that weren't defined by an input row are set to null.
  • withsource=ColumnName：如果指定了此项，则输出将包括一个名为 ColumnName 的列，其值指示哪个源表提供了每一行。withsource=ColumnName: If specified, the output will include a column called ColumnName whose value indicates which source table contributed each row. 如果查询（在通配符匹配后）有效地引用多个数据库（默认数据库总计在内）中的表，则此列的值将具有使用数据库进行限定的表名。If the query effectively (after wildcard matching) references tables from more than one database (default database always counts) the value of this column will have a table name qualified with the database. 同样，如果引用了多个群集，则值中将存在群集和数据库限定条件。Similarly, the cluster and database qualifications will be present in the value if more than one cluster is referenced.
  • isfuzzy= true | false：如果 isfuzzy 设置为 true - 则允许对 union 支线进行模糊解析。isfuzzy= true | false: If isfuzzy is set to true - allows fuzzy resolution of union legs. Fuzzy 适用于 union 源集。Fuzzy applies to the set of union sources. 这意味着，在分析查询和准备执行的过程中，union 源的集合被缩减为当时存在并且可供访问的一组表引用。It means that while analyzing the query and preparing for execution, the set of union sources is reduced to the set of table references that exist and are accessible at the time. 如果至少找到了一个这样的表，则任何解析失败都将在查询状态结果中产生警告（每个缺少的引用都会产生一个），但不会阻止查询执行；如果没有任何解析成功，则查询会返回错误。If at least one such table was found, any resolution failure will yield a warning in the query status results (one for each missing reference), but will not prevent the query execution; if no resolutions were successful - the query will return an error. 默认为 isfuzzy=false。The default is isfuzzy=false.

返回Returns

一个表，其中的行数与所有输入表中的行数相同。A table with as many rows as there are in all the input tables.

备注Notes

  1. union 作用域可以包括 let 语句（如果这些语句通过 view 关键字进行了特性化）union scope can include let statements if those are attributed with view keyword
  2. union 作用域将不包括函数。union scope will not include functions. 若要在 union 作用域中包括某个函数，请定义带 view 关键字的 let 语句To include a function in the union scope, define a let statement with view keyword
  3. 如果 union 输入是表（与表格表达式相对），并且 union 后跟一个 where 运算符，则为获得更好的性能，请考虑使用 find 来替代两者。If the union input is tables (as oppose to tabular expressions), and the union is followed by a where operator, for better performance, consider replacing both with find. 请注意 find 运算符生成的不同输出架构。Note the different output schema produced by the find operator.
  4. isfuzzy=true 仅适用于 union 源解析阶段。isfuzzy=true only applies to the union sources resolution phase. 确定源表的集合后，不会隐藏可能的其他查询失败。Once the set of source tables is determined, possible additional query failures will not be suppressed.
  5. 使用 outer union 时，结果将包含出现在任何输入中的所有列，每个名称和类型实例各占一列。When using outer union, the result has all the columns that occur in any of the inputs, one column for each name and type occurrences. 这意味着，如果某个列出现在多个表中且具有多个类型，则在 union 的结果中，对于每个类型，它都有一个对应的列。This means that if a column appears in multiple tables and has multiple types, it will have a corresponding column for each type in the union's result. 此列名称将以“”作为后缀，后跟源列类型。This column name will be suffixed with a '' followed by the origin column type.
  1. union 作用域可以包括 let 语句（如果这些语句通过 view 关键字进行了特性化）union scope can include let statements if those are attributed with view keyword
  2. union 作用域将不包括函数。union scope will not include functions. 若要在 union 作用域中包括函数，请定义带 view 关键字的 let 语句To include function in the union scope - define a let statement with view keyword
  3. 如果 union 输入是表（与表格表达式相对），并且 union 后跟一个 where 运算符，则为获得更好的性能，请考虑使用 find 来替代两者。If the union input is tables (as oppose to tabular expressions), and the union is followed by a where operator, consider replacing both with find for better performance. 请注意 find 运算符生成的不同输出架构。Please note the different output schema produced by the find operator.
  4. isfuzzy= true 仅适用于 union 源解析的阶段。isfuzzy= true applies only to the phase of the union sources resolution. 确定源表的集合后，不会隐藏可能的其他查询失败。Once the set of source tables was determined, possible additional query failures will not be suppressed.
  5. 使用 outer union 时，结果将包含出现在任何输入中的所有列，每个名称和类型实例各占一列。When using outer union, the result has all the columns that occur in any of the inputs, one column for each name and type occurrences. 这意味着，如果某个列出现在多个表中且具有多个类型，则在 union 的结果中，对于每个类型，它都有一个对应的列。This means that if a column appears in multiple tables and has multiple types, it will have a corresponding column for each type in the union's result. 此列名称将以“”作为后缀，后跟源列类型。This column name will be suffixed with a '' followed by the origin column type.

示例Example

union K* | where * has "Kusto"

数据库中名称以 K 开头的所有表中有任意列包括单词 Kusto 的行。Rows from all tables in the database whose name starts with K, and in which any column includes the word Kusto.

示例Example

union withsource=SourceTable kind=outer Query, Command
| where Timestamp > ago(1d)
| summarize dcount(UserId)

过去一天已生成 Query 事件或 Command 事件的不同用户数。The number of distinct users that have produced either a Query event or a Command event over the past day. 在结果中，“SourceTable”列会指示“Query”或“Command”。In the result, the 'SourceTable' column will indicate either "Query" or "Command".

Query
| where Timestamp > ago(1d)
| union withsource=SourceTable kind=outer 
   (Command | where Timestamp > ago(1d))
| summarize dcount(UserId)

此效率更高的版本可生成相同结果。This more efficient version produces the same result. 它可在创建联合之前筛选每个表。It filters each table before creating the union.

示例：使用 isfuzzy=trueExample: Using isfuzzy=true

// Using union isfuzzy=true to access non-existing view:                                     
let View_1 = view () { print x=1 };
let View_2 = view () { print x=1 };
let OtherView_1 = view () { print x=1 };
union isfuzzy=true
(View_1 | where x > 0), 
(View_2 | where x > 0),
(View_3 | where x > 0)
| count
计数Count
22

观察查询状态 - 返回以下警告：Failed to resolve entity 'View_3'Observing Query Status - the following warning returned: Failed to resolve entity 'View_3'

// Using union isfuzzy=true and wildcard access:
let View_1 = view () { print x=1 };
let View_2 = view () { print x=1 };
let OtherView_1 = view () { print x=1 };
union isfuzzy=true View*, SomeView*, OtherView*
| count
计数Count
33

观察查询状态 - 返回以下警告：Failed to resolve entity 'SomeView*'Observing Query Status - the following warning returned: Failed to resolve entity 'SomeView*'

示例：源列类型不匹配Example: source columns types mismatch

let View_1 = view () { print x=1 };
let View_2 = view () { print x=toint(2) };
union withsource=TableName View_1, View_2
TableNameTableName x_longx_long x_intx_int
View_1View_1 11
View_2View_2 22 
let View_1 = view () { print x=1 };
let View_2 = view () { print x=toint(2) };
let View_3 = view () { print x_long=3 };
union withsource=TableName View_1, View_2, View_3
TableNameTableName x_long1x_long1 x_intx_int x_longx_long
View_1View_1 11
View_2View_2 22
View_3View_3 33

来自 View_1 的列 x 收到了后缀 _long，并且因为结果架构中已存在名为 x_long 的列，所以对列名称执行了删除重复数据的操作，生成了新列 - x_long1Column x from View_1 received the suffix _long, and as a column named x_long already exists in the result schema, the column names were de-duplicated, producing a new column- x_long1