KQL Basics: A Beginner's Guide to Mastering Data Queries

Are you new to Kusto Query Language (KQL) and curious about its potential? You've come to the right place! In this post, we’ll cover the foundational aspects of KQL, explore its syntax, and introduce some essential commands to help you get started on your data exploration journey.

Kusto documentation - Kusto | Microsoft Learn

What is Kusto Query Language?

KQL (Kusto Query Language) is a powerful language designed for analyzing large datasets with ease and efficiency. It’s primarily used in tools like Azure Data Explorer and Log Analytics, making it invaluable for data professionals looking to:

• Query and analyze vast amounts of data quickly.

• Create readable, maintainable, and scalable queries.

The Syntax of Kusto Query Language

Basics of KQL Syntax and Structure

KQL uses a pipeline structure, where commands are chained together using the | operator. Each command builds upon the result of the previous one, allowing for concise and intuitive queries.

Here are three essential commands to start with:

1. project: Select specific columns to display.

2. summarize: Perform aggregations like counts, sums, or averages.

3. where: Filter data rows based on specific conditions.

Basic Structure

KQL queries follow a pipeline structure where commands are chained with the | operator:

TableName
| Command1
| Command2
| Command3

1. Filtering Data with where

Use where command to filter rows based on conditions.

Examples:

• Filter rows where State is "California":

StormEvents 
| where State == "California"

• Combine conditions with logical operators:

StormEvents 
| where DamageProperty > 100000 and EventType == "Hail"

2. Selecting Columns with project

Pick specific columns to display using project.

Example:

• Show only EventType, State, and DamageProperty:

StormEvents 
| project EventType, State, DamageProperty

3. Creating Calculated Columns with extend

Use extend to create new columns with calculated values.

Example:

• Add a new column TotalDamage as the sum of DamageProperty and DamageCrop:

StormEvents 
| extend TotalDamage = DamageProperty + DamageCrop

4. Aggregating Data with summarize

Summarize data by grouping and calculating metrics.

Examples:

• Count total events by EventType:

StormEvents 
| summarize TotalEvents = count() by EventType

• Find average damage by state:

StormEvents 
| summarize AvgDamage = avg(DamageProperty) by State

5. Working with Multiple Datasets

KQL provides powerful commands to combine and deduplicate datasets.

Commands:

• join: Combine rows from two datasets based on matching values.

• union: Merge rows from multiple datasets.

• distinct: Remove duplicate rows.

Examples:

• Join two tables (Customers and Orders):

Customers 
| join kind=inner (Orders) on CustomerID

• Merge two tables:

Dataset1 
| union Dataset2

• Deduplicate rows:

Dataset 
| distinct ColumnName

Pro Tips

Use Comments: Add comments to your queries with // for clarity.

StormEvents 
| summarize TotalEvents = count() by EventType // Counts events by type

Experiment and Explore: Use the take command to preview a sample of rows:

StormEvents 
| take 10

KQL (Kusto Query Language) is case-sensitive

KQL (Kusto Query Language) is case-sensitive when it comes to:

• Column names.

• Table names.

• Literal strings (e.g., "California" is different from "california").

However, commands and operators (like project, summarize, where) are not case-sensitive. For example, you can write WHERE, Where, or where—and they’ll all work the same.

If you're working with case-sensitive data, you can use text functions like:

• tolower(): Converts text to lowercase for consistency.

• toupper(): Converts text to uppercase.

  
  

Example: Normalize a column to lowercase before filtering:

StormEvents
| where tolower(State) == "california"

Summary

Review Key Concepts:

• Basic Queries:

  • project: Select specific columns.

  • where: Filter rows based on conditions.

  • summarize: Perform aggregations like count, sum, and avg.

• Filtering Data:

  • Use logical operators: and, or, not.

• Column Transformation:

  • extend: Create calculated columns.

  • project: Select transformed and specific columns.

• Aggregations:

  • summarize: Aggregate data with grouping by columns.

• Data Transformation:

  • join: Combine data from multiple tables.

  • union: Merge datasets.

  • distinct: Find unique values in a column.

  
  

StormEvents
// Filtering rows based on conditions
| where EventType == "Flood" or EventType == "Tornado"
// Creating a calculated column
| extend TotalDamage = DamageProperty + DamageCrop
// Aggregating data by State
| summarize TotalDamageByState = sum(TotalDamage) by State
// Joining with another table
| join kind=inner (PopulationData | project State, Population) on State
// Creating another calculated column
| extend DamagePerCapita = TotalDamageByState / Population
// Adding a filter condition
| where DamagePerCapita > 1000
// Selecting specific columns
| project State, DamagePerCapita, Population
// Ensuring unique rows based on State
| distinct State