find 运算符find operator
在一组表中查找与谓词匹配的行。Finds rows that match a predicate across a set of tables.
find
的作用域也可以跨数据库或跨群集。The scope of the find
can also be cross-database or cross-cluster.
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
withsource=
ColumnName:可选。withsource=
ColumnName: Optional. 默认情况下,输出将包括一个名为 source_ 的列,其值指示哪个源表提供了每一行。By default, the output will include a column called source_ whose values indicate which source table has contributed each row. 如果指定,将使用 ColumnName,而不是 source_ 。If specified, ColumnName will be used instead of source_. 通配符匹配后,如果查询引用了多个数据库(包括默认数据库)中的表,则此列的值将具有使用数据库进行限定的表名。After wildcard matching, if the query references tables from more than one database (including the default database), 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.- 谓词:针对输入表 Table [
,
Table, ...] 的列的boolean
表达式 。对每个输入表中的每一行执行计算。Predicate: Aboolean
expression over the columns of the input tables Table [,
Table, ...]. It's evaluated for each row in each input table. 有关详细信息,请参阅谓词语法详细信息。For more information, see predicate-syntax details. Table
:可选。Table
: Optional. 默认情况下,find 将在当前数据库的所有表中查找:By default, find will look in all the tables in the current database, for:- 表的名称,如
Events
The name of a table, such asEvents
- 查询表达式,如
(Events | where id==42)
A query expression, 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 withE
.
- 表的名称,如
project-smart
|project
:如果未指定,则默认情况下将使用project-smart
。project-smart
|project
: If not specified,project-smart
will be used by default. 有关详细信息,请参阅输出架构详细信息。For more information, see output-schema details.
withsource=
ColumnName:可选。withsource=
ColumnName: Optional. 默认情况下,输出将包括一个名为 source_ 的列,其值指示哪个源表提供了每一行。By default, the output will include a column called source_ whose values indicate which source table contributed each row. 如果指定,将使用 ColumnName,而不是 source_ 。If specified, ColumnName will be used instead of source_.- 谓词:针对输入表 Table [
,
Table, ...] 的列的boolean
表达式 。对每个输入表中的每一行执行计算。Predicate: Aboolean
expression over the columns of the input tables Table [,
Table, ...]. It's evaluated for each row in each input table. 有关详细信息,请参阅谓词语法详细信息。For more information, see predicate-syntax details. Table
:可选。Table
: Optional. 默认情况下 find 将在所有表中搜索:By default find will search all tables for:- 表的名称,如
Events
The name of a table, such asEvents
- 查询表达式,如
(Events | where id==42)
A query expression, 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 whose names begin withE
.
- 表的名称,如
project-smart
|project
:如果未指定,则默认情况下将使用project-smart
。project-smart
|project
: If not specifiedproject-smart
will be used by default. 有关详细信息,请参阅输出架构详细信息。For more information, see output-schema details.
返回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_
列中。Whenpack(*)
is used, the rest of the columns will be packed into a property bag and will appear in an additionalpack_
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 theproject
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 andproject-smart
is in use, there will be a corresponding column for each type in thefind
'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 aunion
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 tofind
. - 向谓词添加基于时间的筛选器。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
在当前数据库的所有表中进行字词查找Term lookup across all tables in current database
以下查询从当前数据库的所有表中查找其中的任意列包含单词 Kusto
的所有行。The query finds all rows from all tables in the current database in which any column includes the word Kusto
.
生成的记录根据输出架构进行转换。The resulting records are transformed according to the output schema.
find "Kusto"
对当前数据库中与名称模式匹配的所有表进行字词查找Term lookup across all tables matching a name pattern in the current database
以下查询从当前数据库中名称以 K
开头的所有表中,查找其中的任意列包含单词 Kusto
的所有行。The query finds all rows from all tables in the current database whose name starts with K
, and in which any column includes the word Kusto
.
生成的记录根据输出架构进行转换。The resulting records are transformed according to the output schema.
find in (K*) where * has "Kusto"
对群集中所有数据库的所有表进行字词查找Term lookup across all tables in all databases in the cluster
以下查询从所有数据库的所有表中查找其中的任意列包含单词 Kusto
的所有行。The query finds all rows from all tables in all databases in which any column includes the word Kusto
.
此查询是一个跨数据库查询。This query is a cross-database query.
生成的记录根据输出架构进行转换。The resulting records are transformed according to the output schema.
find in (database('*').*) "Kusto"
对群集中与名称模式匹配的所有表和数据库进行字词查找Term lookup across all tables and databases matching a name pattern in the cluster
以下查询从所有名称以 K
开头的表中和所有名称以 B
开头的数据库中,查找其中的任意列包含单词 Kusto
的所有行。The query finds all rows from all tables whose name starts with K
in all databases whose name start with B
and in which any column includes the word Kusto
.
生成的记录根据输出架构进行转换。The resulting records are transformed according to the output schema.
find in (database("B*").K*) where * has "Kusto"
在多个群集中进行字词查找Term lookup in several clusters
以下查询从所有名称以 K
开头的表中和所有名称以 B
开头的数据库中,查找其中的任意列包含单词 Kusto
的所有行。The query finds all rows from all tables whose name starts with K
in all databases whose name start with B
and in which any column includes the word Kusto
.
生成的记录根据输出架构进行转换。The resulting records are transformed according to the output schema.
find in (cluster("cluster1").database("B*").K*, cluster("cluster2").database("C*".*))
where * has "Kusto"
跨所有表进行字词查找Term lookup across all tables
以下查询从所有表中查找其中的任意列包含单词 Kusto
的所有行。The query finds all rows from all tables in which any column includes the word Kusto
.
生成的记录根据输出架构进行转换。The resulting records are transformed according to the output schema.
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
Session_IdSession_Id | 级别Level | EventTextEventText | 版本Version |
---|---|---|---|
acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | 信息Information | Some Text1Some Text1 | v1.0.0v1.0.0 |
acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | 错误Error | Some Text2Some Text2 | v1.0.0v1.0.0 |
28b8e46e-3c31-43cf-83cb-48921c3986fc28b8e46e-3c31-43cf-83cb-48921c3986fc | 错误Error | Some Text3Some Text3 | v1.0.1v1.0.1 |
8f057b11-3281-45c3-a856-05ebb18a3c598f057b11-3281-45c3-a856-05ebb18a3c59 | 信息Information | Some Text4Some Text4 | v1.1.0v1.1.0 |
EventsTable2EventsTable2
Session_IdSession_Id | 级别Level | EventTextEventText | EventNameEventName |
---|---|---|---|
f7d5f95f-f580-4ea6-830b-5776c8d64fddf7d5f95f-f580-4ea6-830b-5776c8d64fdd | 信息Information | Some Other Text1Some Other Text1 | Event1Event1 |
acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | 信息Information | Some Other Text2Some Other Text2 | Event2Event2 |
acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | 错误Error | Some Other Text3Some Other Text3 | Event3Event3 |
15eaeab5-8576-4b58-8fc6-478f75d8fee415eaeab5-8576-4b58-8fc6-478f75d8fee4 | 错误Error | Some Other Text4Some Other Text4 | Event4Event4 |
搜索公共列、投影通用列和非通用列,然后打包其余列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(*)
source_source_ | EventTextEventText | 版本Version | EventNameEventName | pack_pack_ |
---|---|---|---|---|
EventsTable1EventsTable1 | Some Text2Some Text2 | v1.0.0v1.0.0 | {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error"} | |
EventsTable2EventsTable2 | Some Other Text3Some Other Text3 | Event3Event3 | {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error"} |
搜索公共列和非公共列Search in common and uncommon columns
find Version == 'v1.0.0' or EventName == 'Event1' project Session_Id, EventText, Version, EventName
source_source_ | Session_IdSession_Id | EventTextEventText | 版本Version | EventNameEventName |
---|---|---|---|---|
EventsTable1EventsTable1 | acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | Some Text1Some Text1 | v1.0.0v1.0.0 | |
EventsTable1EventsTable1 | acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | Some Text2Some Text2 | v1.0.0v1.0.0 | |
EventsTable2EventsTable2 | f7d5f95f-f580-4ea6-830b-5776c8d64fddf7d5f95f-f580-4ea6-830b-5776c8d64fdd | Some Other Text1Some Other Text1 | Event1Event1 |
注意:在实践中,将使用 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'
source_source_ | Session_IdSession_Id | 级别Level | EventTextEventText | pack_pack_ |
---|---|---|---|---|
EventsTable1EventsTable1 | acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | 信息Information | Some Text1Some Text1 | {"Version":"v1.0.0"}{"Version":"v1.0.0"} |
EventsTable1EventsTable1 | acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | 错误Error | Some Text2Some Text2 | {"Version":"v1.0.0"}{"Version":"v1.0.0"} |
EventsTable2EventsTable2 | acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | 信息Information | Some Other Text2Some Other Text2 | {"EventName":"Event2"}{"EventName":"Event2"} |
EventsTable2EventsTable2 | acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e | 错误Error | Some Other Text3Some Other Text3 | {"EventName":"Event3"}{"EventName":"Event3"} |
以属性包的形式返回每一行的结果Return the results from each row as a property bag
find Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e' project pack(*)
source_source_ | pack_pack_ |
---|---|
EventsTable1EventsTable1 | {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Text1", "Version":"v1.0.0"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Text1", "Version":"v1.0.0"} |
EventsTable1EventsTable1 | {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Text2", "Version":"v1.0.0"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Text2", "Version":"v1.0.0"} |
EventsTable2EventsTable2 | {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Other Text2", "EventName":"Event2"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Other Text2", "EventName":"Event2"} |
EventsTable2EventsTable2 | {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Other Text3", "EventName":"Event3"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Other Text3", "EventName":"Event3"} |
将 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 asunion
.
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 asunion
, 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)