find 运算符find operator

• 08/06/2020
• • 
  • 

在一组表中查找与谓词匹配的行。Finds rows that match a predicate across a set of tables.

find in (Table1, Table2, Table3) where Fruit=="apple"

find in (database('*').*) where Fruit == "apple"

find in (cluster('cluster_name').database('MyDB*'.*)) where Fruit == "apple"

find in (Table1, Table2, Table3) where Fruit=="apple"

语法Syntax

• find [withsource=ColumnName] [in (Table [, Table, ...])] where Predicate [project-smart | project ColumnName [:ColumnType] [, ColumnName[:ColumnType], ...][, pack(*)]] find [withsource=ColumnName] [in (Table [, Table, ...])] where Predicate [project-smart | project ColumnName [:ColumnType] [, ColumnName[:ColumnType], ...][, pack(*)]]

• find Predicate [project-smart | project ColumnName[:ColumnType] [, ColumnName[:ColumnType], ...] [, pack(*)]] find Predicate [project-smart | project ColumnName[:ColumnType] [, ColumnName[:ColumnType], ...] [, pack(*)]]

参数Arguments

返回Returns

Predicate 为 true 的 Table [, Table, ...] 中的行的转换 。Transformation of rows in Table [, Table, ...] for which Predicate is true. 行根据输出架构进行转换。The rows are transformed according to the output schema.

输出架构Output schema

source_ 列source_ column

find 运算符输出始终包含具有源表名称的 source_ 列。The find operator output will always include a source_ column with the source table name. 可以使用 withsource 参数重命名该列。The column can be renamed using the withsource parameter.

结果列results columns

将筛除不包含谓词评估所用的任何列的源表。Source tables that don't contain any column used by the predicate evaluation, will be filtered out.

使用 project-smart 时，输出中会显示的列为：When using project-smart, the columns that will appear in the output will be:

• 在谓词中显式显示的列。Columns that appear explicitly in the predicate.
• 所有筛选的表的公共列。Columns that are common to all the filtered tables.

其余列将打包为属性包，并显示在其他 pack_ 列中。The rest of the columns will be packed into a property bag and will appear in an additional pack_ column. 谓词显式引用并显示在具有多种类型（每种类型在结果架构中将具有不同的列）的多个表中的列。A column that is referenced explicitly by the predicate and appears in multiple tables with multiple types, will have a different column in the result schema for each such type. 每个列名都基于原始列名和类型构造而成，并用下划线分隔。Each of the column names will be constructed from the original column name and type, separated by an underscore.

使用 project ColumnName[:ColumnType] [, ColumnName[:ColumnType], ...][, pack(*)] 时 ：When using project ColumnName[:ColumnType] [, ColumnName[:ColumnType], ...][, pack(*)]:

• 结果表将包含列表中指定的列。The result table will include the columns specified in the list. 如果源表不包含特定列，则相应行中的值将为 NULL。If a source table doesn't contain a certain column, the values in the corresponding rows will be null.
• 使用 ColumnName 指定 ColumnType 时，“结果”中的此列将具有给定的类型，并且值将根据需要强制转换为该类型 。When specifying a ColumnType with a ColumnName, this column in the "result" will have the given type, and the values will be cast to that type if needed. 评估 Predicate 时，强制转换不会影响列类型。The casting won't have an effect on the column type when evaluating the Predicate.
• 使用 pack(*) 时，其余列将打包为属性包，并显示在其他 pack_ 列中。When pack(*) is used, the rest of the columns will be packed into a property bag and will appear in an additional pack_ column.

pack_ 列pack_ column

此列将包含一个属性包，其中包含输出架构中未显示的所有列的数据。This column will contain a property bag with the data from all the columns that doesn't appear in the output schema. 源列名将用作属性名，列值将用作属性值。The source column name will serve as the property name and the column value will serve as the property value.

谓词语法Predicate syntax

find 运算符支持 * has 术语的替代语法，并且仅使用 term 即可在所有输入列中搜索术语 。The find operator supports an alternative syntax for the * has term, and using just term, will search a term across all input columns.

有关某些筛选函数的摘要，请参阅 where 运算符。For a summary of some filtering functions, see where operator.

说明Notes

• 如果 project 子句引用了出现在多个表中且具有多种类型的列，则在 project 子句中，类型必须遵循此列引用If the project clause references a column that appears in multiple tables and has multiple types, a type must follow this column reference in the project clause
• 如果某列出现在多个表中且具有多种类型，并且正在使用 project-smart，则 find 的结果中每种类型都会有一个对应的列，如 union 中所述If a column appears in multiple tables and has multiple types and project-smart is in use, there will be a corresponding column for each type in the find's result, as described in union
• 当使用 project-smart 时，谓词、源表集或表架构中的更改可能会导致输出架构发生更改。When using project-smart, changes in the predicate, in the source tables set, or in the tables schema, may result in a change to the output schema. 如果需要常量结果架构，请改用 projectIf a constant result schema is needed, use project instead
• find 作用域不能包括函数。find scope can't include functions. 要在 find 作用域中包含函数，请使用 view 关键字定义 let 语句。To include a function in the find scope, define a let statement with view keyword.

性能提示Performance tips

• 使用表，而不是表格表达式。Use tables as opposed to tabular expressions. 如果使用表格表达式，则 find 运算符将回退到 union 查询，这可能会导致性能下降。If tabular expression, the find operator falls back to a union query that can result in degraded performance.
• 如果列出现在多个表中并具有多种类型，并且是 project 子句的一部分，则在将表传递给 find 之前，最好将 ColumnType 添加到 project 子句，而不是修改表。If a column that appears in multiple tables and has multiple types, is part of the project clause, prefer adding a ColumnType to the project clause over modifying the table before passing it to find.
• 向谓词添加基于时间的筛选器。Add time-based filters to the predicate. 使用日期/时间列值或 ingestion_time()。Use a datetime column value or ingestion_time().
• 搜索特定列而不是全文搜索。Search in specific columns rather than a full text search.
• 最好不要引用出现在多个表中且具有多个类型的列。It's better not to reference columns that appear in multiple tables and have multiple types. 如果在为多个类型解析此类列类型时谓词有效，则查询将回退到并集。If the predicate is valid when resolving such columns type for more than one type, the query will fall back to union. 例如，请参阅将 find 用作 union 的案例示例。For example, see examples of cases where find will act as a union.

示例Examples

find "Kusto"

find in (K*) where * has "Kusto"

find in (database('*').*) "Kusto"

find in (database("B*").K*) where * has "Kusto"

find in (cluster("cluster1").database("B*").K*, cluster("cluster2").database("C*".*))
where * has "Kusto"

find "Kusto"

find 输出结果的示例Examples of find output results

下面的示例演示如何针对两个表使用 find：EventsTable1 和 EventsTable2 。The following examples show how find can be used over two tables: EventsTable1 and EventsTable2. 假设我们有这两个表的以下内容：Assume we have the next content of these two tables:

EventsTable1EventsTable1

EventsTable2EventsTable2

搜索公共列、投影通用列和非通用列，然后打包其余列Search in common columns, project common and uncommon columns, and pack the rest

find in (EventsTable1, EventsTable2) 
     where Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e' and Level == 'Error' 
     project EventText, Version, EventName, pack(*)

搜索公共列和非公共列Search in common and uncommon columns

find Version == 'v1.0.0' or EventName == 'Event1' project Session_Id, EventText, Version, EventName

注意：在实践中，将使用 Version == 'v1.0.0' 谓词筛选 EventsTable1 行，使用 EventName == 'Event1' 谓词筛选 EventsTable2 行 。Note: in practice, EventsTable1 rows will be filtered with Version == 'v1.0.0' predicate and EventsTable2 rows will be filtered with EventName == 'Event1' predicate.

使用缩写表示法在当前数据库的所有表中进行搜索Use abbreviated notation to search across all tables in the current database

find Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e'

以属性包的形式返回每一行的结果Return the results from each row as a property bag

find Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e' project pack(*)

将 find 用作 union 的案例示例Examples of cases where find will act as union

使用非表格表达式作为 find 操作数Using a non-tabular expression as find operand

let PartialEventsTable1 = view() { EventsTable1 | where Level == 'Error' };
find in (PartialEventsTable1, EventsTable2) 
     where Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e'

引用出现在多个表中并具有多个类型的列Referencing a column that appears in multiple tables and has multiple types

假设我们已通过运行以下内容创建了两个表：Assume we've created two tables by running:

.create tables 
  Table1 (Level:string, Timestamp:datetime, ProcessId:string),
  Table2 (Level:string, Timestamp:datetime, ProcessId:int64)

• 下面的查询将作为 union 执行。The following query will be executed as union.

find in (Table1, Table2) where ProcessId == 1001

输出结果架构将为 (Level:string, Timestamp, ProcessId_string, ProcessId_int)。The output result schema will be (Level:string, Timestamp, ProcessId_string, ProcessId_int).

• 以下查询也将作为 union 执行，但将产生不同的结果模式。The following query will also be executed as union, but will produce a different result schema.

find in (Table1, Table2) where ProcessId == 1001 project Level, Timestamp, ProcessId:string 

输出结果架构将为 (Level:string, Timestamp, ProcessId_string)The output result schema will be (Level:string, Timestamp, ProcessId_string)