/kql

Advanced KQL Patterns

Reference for specialized KQL features: graph queries, vector similarity, geospatial operations, time series, and external data. Consult this when a task requires these capabilities.

Try it yourself: Examples marked with // try it! can be run on the public help cluster: https://help.kusto.windows.net, database Samples.

Table of Contents

1. Graph Queries
2. Vector Similarity
3. Geospatial Operations
4. Time Series Analysis
5. External Data
6. Stored Functions
7. Materialized Views & Caching
8. Good Query Habits

1. Graph Queries

KQL supports two approaches for building and querying graphs. The graph-match syntax is the same for both — only how the graph is constructed differs.

• Transient graphs — built inline with the make-graph operator. Useful for verifying an approach before committing to persistent graph building, or for smaller graphs (~1M entities).
• Persistent (snapshot) graphs — defined as a graph model and queried with graph("ModelName"). Best for large graphs and repeated queries where rebuilding the graph each time is too expensive.

Querying a persistent graph model

Persistent graph models are defined once and queried repeatedly with graph("ModelName"). The function picks up the latest snapshot automatically. You can also target a specific snapshot with graph("ModelName", "SnapshotName").

// try it! — query the persistent "Simple" graph model on the help cluster
graph("Simple")
| graph-match (src)-[e]->(dst)
  where src.name == "Alice"
  project src.name, dst.name, e.lbl

Creating a persistent graph model

Use .create-or-alter graph_model to define the schema and how nodes/edges are built from tables:

// Management command — define the graph model
.create-or-alter graph_model YaccNetwork ```
{
  "Schema": {
    "Nodes": {
      "Application": {"AppName": "string", "HostingIp": "string"}
    },
    "Edges": {
      "Connection": {"Protocol": "string", "Port": "int"}
    }
  },
  "Definition": {
    "Steps": [
      {
        "Kind": "AddNodes",
        "Query": "YaccApplications | project NodeId = AppId, AppName, HostingIp",
        "NodeIdColumn": "NodeId",
        "Labels": ["Application"]
      },
      {
        "Kind": "AddEdges",
        "Query": "YaccConnections | project SourceId = SourceAppId, TargetId = TargetAppId, Protocol, Port",
        "SourceColumn": "SourceId",
        "TargetColumn": "TargetId",
        "Labels": ["Connection"]
      }
    ]
  }
}
```

Ad-hoc graphs with make-graph

For one-time analysis, use the make-graph operator to build a transient graph inline:

// try it! — uses SimpleGraph_Nodes and SimpleGraph_Edges from help cluster
SimpleGraph_Edges
| make-graph source --> target with SimpleGraph_Nodes on id
| graph-match (src)-[e]->(dst)
  where src.name == "Alice"
  project src.name, dst.name, e.lbl

Variable-length traversal

// try it! — find all paths up to 3 hops from Alice on persistent model
graph("Simple")
| graph-match (start)-[path*1..3]->(target)
  where start.name == "Alice"
  project start.name, target.name, hops = array_length(path)
| take 10

Pre-filtering edges (the key pattern)

Edge properties are not accessible on variable-length paths during graph-match. Use all() / any() for label-based filtering, or pre-filter edges before building the graph.

// ❌ WRONG — can't access properties on variable-length edge
graph-match (src)-[path*1..3]->(dst)
  where path.IsVulnerable == true

// ✅ RIGHT (persistent) — filter in graph model definition
// In the AddEdges step, set Query to: "Edges | where IsVulnerable == true | project ..."

// ✅ RIGHT (ad-hoc) — pre-filter edges before make-graph
Edges
| where IsVulnerable == true
| make-graph SourceId --> TargetId with Nodes on NodeId
| graph-match (src)-[path*1..3]->(dst)
  project src.Name, dst.Name

// ✅ RIGHT (label-based) — use all() on variable-length paths
graph("MyGraph")
| graph-match (src)-[path*1..3]->(dst)
  where all(path, labels() has "Vulnerable")
  project src.Name, dst.Name

Graph snapshots

For persistent models, create materialized snapshots for faster queries:

// Create a snapshot (management command, async — not runnable on help cluster)
.make graph_snapshot Simple_20240115
  with (source = graph_model("Simple"))

// try it! — query picks up latest snapshot automatically
graph("Simple")
| graph-match (src)-[e]->(dst)
  where labels(src) has "Person"
  project src.name, dst.name, e.lbl
| take 10

Post-processing graph results

After graph-match with a project clause, results are tabular and can be piped to any KQL operator:

// try it! — count relationships per person
graph("Simple")
| graph-match (src)-[e]->(dst)
  project src.name, dst.name, e.lbl
| summarize ConnectionCount = count() by src_name
| top 10 by ConnectionCount desc

Use the graph-to-table operator when you need to export all nodes and edges from a graph as raw tables (without pattern matching):

graph("Simple")
| graph-to-table nodes as N, edges as E;
N | take 5

2. Vector Similarity

KQL has native vector operations — don't export to Python for cosine similarity.

series_cosine_similarity

The most common vector operation.

// try it! — find Iris flowers most similar to a target using feature vectors
let target_vec = pack_array(5.1, 3.5, 1.4, 0.2);
Iris
| extend Vec = pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth)
| extend similarity = series_cosine_similarity(Vec, target_vec)
| top 5 by similarity desc
| project Class, SepalLength, SepalWidth, similarity

Combining vectors

// try it! — weighted vector combination from Iris feature vectors
let v1 = toscalar(Iris | where Class == "Iris-setosa" | take 1 | project pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth));
let v2 = toscalar(Iris | where Class == "Iris-versicolor" | take 1 | project pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth));
let v3 = toscalar(Iris | where Class == "Iris-virginica" | take 1 | project pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth));
let combined = series_add(
    series_add(
        series_multiply(v1, repeat(0.5, array_length(v1))),
        series_multiply(v2, repeat(0.3, array_length(v2)))
    ),
    series_multiply(v3, repeat(0.2, array_length(v3)))
);
print combined

Performance consideration

Pairwise cosine similarity on 1536-dim vectors is expensive (~20s for large tables). Strategies:

1. Pre-filter — narrow candidates before computing similarity
2. Round-and-join — bucket vector dimensions to integers, join on buckets for approximate matching
3. Project away vectors — never include vector columns in results unless needed

// Round-and-join for approximate nearest neighbor
let target_rounded = toscalar(
    Vecs | where Word == "test"
    | project r = array_sort_asc(series_multiply(Vec, repeat(100, array_length(Vec))))
);
Vecs
| extend rounded = array_sort_asc(series_multiply(Vec, repeat(100, array_length(Vec))))
| where rounded[0] between (target_rounded[0]-10 .. target_rounded[0]+10)
| extend sim = series_cosine_similarity(Vec, toscalar(Vecs | where Word == "test" | project Vec))
| top 10 by sim desc

Other series functions

3. Geospatial Operations

Point-in-polygon

// try it! — check if storm events occurred within a polygon around NYC
StormEvents
| where geo_point_in_polygon(BeginLon, BeginLat,
    dynamic({"type":"Polygon","coordinates":[[[-74.05,40.65],[-73.85,40.65],[-73.85,40.85],[-74.05,40.85],[-74.05,40.65]]]}))
| summarize count() by EventType

Note: geo_point_in_polygon is a scalar function, not a plugin. It works on free clusters. Don't try to evaluate it as a plugin — use it directly in | where or | extend.

Distance calculations

// try it! — distance between start and end points of storm events (meters)
StormEvents
| where isnotempty(BeginLat) and isnotempty(EndLat)
| extend distance_m = geo_distance_2points(BeginLon, BeginLat, EndLon, EndLat)
| project EventType, State, distance_m
| top 5 by distance_m desc

// Filter by distance — storms within 50km of Houston (-95.37, 29.76)
StormEvents
| where geo_distance_2points(BeginLon, BeginLat, -95.37, 29.76) < 50000
| summarize count() by EventType

Spatial bucketing for joins

KQL joins are equality-only, so spatial proximity joins need pre-bucketing:

// try it! — S2 cells on storm events
StormEvents
| extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 8)
| summarize count() by cell
| top 10 by count_ desc

// Then join on cell IDs (e.g., storm events near taxi pickups)
StormEvents
| extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 5)
| join kind=inner (nyc_taxi | extend cell = geo_point_to_s2cell(pickup_longitude, pickup_latitude, 5)) on cell
| take 10

IP geolocation

// try it! — check if IPs are in a range (using inline test data)
datatable(ClientIP:string) ["10.1.2.3", "172.16.5.6", "192.168.1.1", "8.8.8.8"]
| where ipv4_is_in_any_range(ClientIP, dynamic(["10.0.0.0/8", "172.16.0.0/12"]))

// Check if an IP is in a specific subnet
print is_private = ipv4_is_in_range("10.1.2.3", "10.0.0.0/8")

4. Time Series Analysis

Creating time series

// try it! — basic time series from storm events
StormEvents
| make-series count() default=0 on StartTime step 1d
| render timechart

// Multi-series (one per event type)
StormEvents
| make-series count() default=0 on StartTime step 1d by EventType
| take 5

Anomaly detection

// try it! — decompose and find anomalies in daily storm counts
StormEvents
| make-series count() default=0 on StartTime step 1d
| extend (anomalies, score, baseline) = series_decompose_anomalies(count_)
| mv-expand StartTime, count_, anomalies, score, baseline
| where anomalies != 0
| take 10

Period detection

// try it! — find periodic patterns in occupancy sensor data
OccupancyDetection
| make-series avg_temp = avg(Temperature) default=0 on Timestamp step 10m
| extend (periods, scores) = series_periods_detect(avg_temp, 4.0, 1440.0, 2)
| project periods, scores

Sessionization

// try it! — group trace logs into sessions with 5-minute gap
cluster("help").database("SampleLogs").TraceLogs
| order by Source, Timestamp asc
| extend SessionId = row_window_session(Timestamp, 5m, 1h, Source != prev(Source))
| summarize EventCount = count(), SessionStart = min(Timestamp) by Source, SessionId
| take 5

Sliding window statistics

// try it! — rolling average on occupancy temperature
OccupancyDetection
| make-series avg_temp = avg(Temperature) on Timestamp step 1h
| extend rolling_avg = series_fir(avg_temp, repeat(1.0/7, 7), false)
| take 1

5. External Data

Load small reference tables (up to ~100 MB) directly into KQL without ingestion using the externaldata operator:

// try it! — load Iris dataset from a public CSV on GitHub
externaldata(SepalLength:real, SepalWidth:real, PetalLength:real, PetalWidth:real, Class:string)
  [h@"https://raw.githubusercontent.com/uiuc-cse/data-fa14/gh-pages/data/iris.csv"]
  with (format="csv", ignoreFirstRecord=true)
| summarize count() by Class

Notes:

• externaldata supports all ingestion data formats (CSV, TSV, JSON, Parquet, Avro, ORC, etc.). For hierarchical formats, specify ingestionMapping.
• Not designed for large data volumes — for larger datasets, use external tables (see below) or ingest the data into a table.
• For non-tabular content (images, PDFs, JavaScript), use Python or the browser.

External tables for larger datasets

For data too large for externaldata or queried repeatedly, define a persistent external table. External tables have a stored schema and point to data in Azure Blob Storage, ADLS, Delta Lake, or SQL databases (SQL Server, MySQL, PostgreSQL, Cosmos DB).

// Create an external table (management command)
.create external table ExternalLogs (Timestamp:datetime, Level:string, Message:string)
  kind=storage
  dataformat=csv
  (h@'https://storage.blob.core.windows.net/logs/2024/;managed_identity=system')
  with (ignoreFirstRecord=true)

// try it! — query the TaxiRides external table on the help cluster
external_table("TaxiRides")
| where pickup_datetime between (datetime(2016-01-01) .. datetime(2016-01-02))
| summarize count() by payment_type

// try it! — discover available external tables
.show external tables

When to use which:

6. Stored Functions

Some databases include pre-built stored functions. The help cluster has several in the Samples database.

Discovering stored functions

// try it!
.show functions

Invoking stored functions

// try it! — call a stored function with a parameter
MyFunction2(5)

// try it! — use a stored function to filter a query pipeline
StormEvents
| where State in (InterestingStates())
| summarize count() by State

Common pitfalls with stored functions

1. String escaping: KQL supports both single ('...') and double ("...") quotes for string literals. If a string contains special characters or backslashes, use verbatim syntax @"..." or @'...':

   MyCalc(1.0, 2.5, 3.7)

2. Argument types: Ensure you pass the right type. If the function expects string and you have dynamic, cast with tostring().
3. Function not found: Check the database — functions are database-scoped. Use .show functions to list available ones.

7. Materialized Views & Caching

materialize() for subquery reuse

When a subquery is referenced multiple times, materialize() computes it once:

// try it! — compute once, use twice
let top_states = materialize(
    StormEvents
    | summarize EventCount = count() by State
    | where EventCount > 1000
);
// Use it twice without recomputing
top_states | summarize TotalEvents = sum(EventCount)
| union (top_states | top 3 by EventCount desc)

toscalar() for single-value subqueries

// try it! — extract a single value to use as a constant
let avg_damage = toscalar(StormEvents | summarize avg(DamageProperty));
StormEvents | where DamageProperty > avg_damage | summarize count()

datatable for inline test data

// Create test data without touching the database
let test_data = datatable(Name:string, Score:long) [
    "Alice", 95,
    "Bob", 87,
    "Charlie", 92
];
test_data | where Score > 90

8. Good Query Habits

Patterns that prevent wasted time, runaway queries, and unnecessary retries.

Test transformations on small data first

Use datatable or print to validate logic before running on full tables. This catches syntax errors and logic bugs without touching real data.

// Test a regex extraction before running on millions of rows
let sample = datatable(Msg:string) [
    "User 'alice' sent 1024 bytes",
    "User 'bob' sent 512 bytes",
    "Invalid line with no match"
];
sample
| parse Msg with * "User '" Username "' sent " ByteCount " bytes"
| where isnotempty(Username)

// Test a datetime format with print
print result = format_datetime(datetime(2024-03-15T10:30:00Z), "yyyy-MM")

Start with count, then sample, then full query

// Step 1: How big is this table?
StormEvents | count

// Step 2: What does the data look like?
StormEvents | take 10

// Step 3: Now write the real query with filters
StormEvents
| where StartTime between (datetime(2007-06-01) .. datetime(2007-06-30T23:59:59))
| summarize count() by EventType
| top 20 by count_ desc

Use materialize() to avoid redundant computation

When referencing the same subquery more than once, wrap it in materialize() (see Section 7).

Project early, project often

Drop columns you don't need as early as possible — especially wide columns like vectors, JSON blobs, or free-text fields. This reduces memory pressure and speeds up downstream operators.

// ❌ Carries all columns through the pipeline
StormEvents | where State == "TEXAS" | summarize count() by EventType

// ✅ Drop unneeded columns immediately
StormEvents | where State == "TEXAS" | project State, EventType | summarize count() by EventType

Order predicates for maximum pruning

Kusto has efficient indexes on datetime and string term columns. Order your where predicates to exploit this:

1. datetime filters first — enables shard pruning (entire data extents skipped)
2. string/dynamic term filters next — has, ==, in use the term index
3. Numeric filters — less selective but still cheap
4. Substring scans last — contains, matches regex must scan column data

// ❌ Substring scan runs first on all data
StormEvents
| where EventNarrative contains "power line"
| where StartTime between (datetime(2007-06-01) .. datetime(2007-06-30T23:59:59))

// ✅ Datetime prunes shards, then term filter, then substring
StormEvents
| where StartTime between (datetime(2007-06-01) .. datetime(2007-06-30T23:59:59))
| where EventType has "Wind"
| where EventNarrative contains "power line"

Join performance hints

// hint.shufflekey — for high-cardinality join keys (>1M distinct values)
StormEvents
| join hint.shufflekey=EventId kind=inner (StormEvents | project EventId, State) on EventId

// hint.strategy=broadcast — when left side is small (<100 MB)
PopulationData
| join hint.strategy=broadcast kind=inner (StormEvents) on State

// Use lookup instead of join when right side is small
StormEvents
| lookup kind=leftouter (PopulationData) on State

Use in instead of left semi join

For filtering by a single column, in is simpler and often faster:

// ❌ Verbose
StormEvents
| join kind=leftsemi (StormEvents | where State == "TEXAS" | project State) on State

// ✅ Simpler and often faster
StormEvents
| where State in (InterestingStates())

Pre-filter dynamic columns with has

Parsing dynamic/JSON columns is expensive. Use has to filter out non-matching rows before parsing:

// ❌ Parses JSON for every row
StormEvents
| where StormSummary.Details.Description has "tornado"

// ✅ Term filter first, then parse — skips JSON parsing on non-matching rows
StormEvents
| where StormSummary has "tornado"
| where StormSummary.Details.Description has "tornado"

Summarize with shuffle strategy

When summarize has high-cardinality group keys, use hint.strategy=shuffle to parallelize across nodes:

// ❌ Single-node bottleneck with millions of distinct keys
StormEvents | summarize count() by EventId

// ✅ Shuffle distributes work across all nodes
StormEvents | summarize hint.strategy=shuffle count() by EventId

Query materialized views efficiently

Use the materialized_view() function to query only the pre-aggregated part (faster), rather than the full view which may include un-materialized delta records:

// try it! — queries only the materialized (pre-computed) part — fastest
materialized_view("DailyCovid19") | take 5

// Queries materialized + delta records — complete but slower
DailyCovid19 | take 5