Understanding Tabular Expression Statements in Kusto Query Language (KQL)

Essential for querying and manipulating tabular data

Kusto Query Language (KQL) is a powerful tool used in various Microsoft services like Azure Data Explorer, Azure Monitor, Microsoft Sentinel, and Microsoft Fabric. One of the core components of KQL is the tabular expression statement, which is essential for querying and manipulating tabular data.

What is a Tabular Expression Statement?

A tabular expression statement is typically what people refer to when they talk about queries in KQL. It is composed of tabular data sources, tabular data operators, and optional rendering operators. The statement follows a regular form represented by the pipe character (|), visually depicting the flow of data from left to right.

Syntax and Parameters

The syntax of a tabular expression statement is straightforward:

Source | Operator1 | Operator2 | RenderInstruction

• Source: A tabular data source, such as a table reference, a function invocation that returns a table, or a table literal.

• Operator: Tabular data operators like filters and projections.

• RenderInstruction: Optional rendering operators or instructions.

Tabular Data Sources

Tabular data sources produce sets of records that are processed by tabular data operators. Supported sources include:

• Table references

• Tabular range operator

• Print operator

• Function invocation returning a table

• Table literal ("datatable")

Examples

Filtering Rows by Condition

This example counts the number of records in the StormEvents table where the State column has the value "FLORIDA":

StormEvents | where State == "FLORIDA" | count

Output:

Count
1042

Combining Data from Two Tables

In this example, the join operator combines records from the StormEvents table and the PopulationData table based on the State column:

StormEvents | where InjuriesDirect + InjuriesIndirect > 50 | join (PopulationData) on State | project State, Population, TotalInjuries = InjuriesDirect + InjuriesIndirect

Output:

State      Population   TotalInjuries
ALABAMA    4918690      60
CALIFORNIA 39562900     61
KANSAS     2915270      63
MISSOURI   6153230      422
OKLAHOMA   3973710      200
TENNESSEE  6886720      187
TEXAS      29363100     137

TLDR

Tabular expression statements are a fundamental aspect of KQL, enabling users to efficiently query and manipulate tabular data. Understanding the syntax, parameters, and practical examples can significantly enhance your ability to work with KQL in various Microsoft services.