KQL Series – SQL to KQL Cheat Sheet

This blog post is about how to quickly learn KQL.

Kusto supports a subset of the SQL language. See the list of SQL known issues for the full list of unsupported features.

The primary language to interact with the Kusto Engine is KQL (Kusto Query Language). To make the transition and learning experience easier, you can use Kusto to translate SQL queries to KQL. Send an SQL query to Kusto, prefixing it with the verb ‘EXPLAIN’.

So let’s write some SQL here:

EXPLAIN SELECT COUNT_BIG(*) as C FROM StormEvents

and we get:

StormEvents | summarize C=count() | project C

Reproduced from here:

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/sqlcheatsheet#sql-to-kusto-cheat-sheet-1

SQL to Kusto cheat sheet

The table below shows sample queries in SQL and their KQL equivalents.

Category	SQL Query	Kusto Query
Select data from table	`SELECT * FROM dependencies`	`dependencies`
—	`SELECT name, resultCode FROM dependencies`	`dependencies \| project name, resultCode`
—	`SELECT TOP 100 * FROM dependencies`	`dependencies \| take 100`
Null evaluation	`SELECT * FROM dependencies WHERE resultCode IS NOT NULL`	`dependencies \| where isnotnull(resultCode)`
Comparison operators (date)	`SELECT * FROM dependencies WHERE timestamp > getdate()-1`	`dependencies \| where timestamp > ago(1d)`
—	`SELECT * FROM dependencies WHERE timestamp BETWEEN ... AND ...`	`dependencies \| where timestamp > datetime(2016-10-01) and timestamp <= datetime(2016-11-01)`
Comparison operators (string)	`SELECT * FROM dependencies WHERE type = "Azure blob"`	`dependencies \| where type == "Azure blob"`
—	`-- substring SELECT * FROM dependencies WHERE type like "%blob%"`	`// substring dependencies \| where type contains "blob"`
—	`-- wildcard SELECT * FROM dependencies WHERE type like "Azure%"`	`// wildcard dependencies \| where type startswith "Azure" // or dependencies \| where type matches regex "^Azure.*"`
Comparison (boolean)	`SELECT * FROM dependencies WHERE !(success)`	`dependencies \| where success == "False"`
Grouping, Aggregation	`SELECT name, AVG(duration) FROM dependencies GROUP BY name`	`dependencies \| summarize avg(duration) by name`
Distinct	`SELECT DISTINCT name, type FROM dependencies`	`dependencies \| summarize by name, type`
—	`SELECT name, COUNT(DISTINCT type) FROM dependencies GROUP BY name`	`dependencies \| summarize by name, type \| summarize count() by name // or approximate for large sets dependencies \| summarize dcount(type) by name`
Column aliases, Extending	`SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies GROUP BY name`	`dependencies \| summarize AvgD = avg(duration) by Name=operationName`
—	`SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions`	`ConferenceSessions \| extend session=strcat(sessionid, " ", session_title) \| project conference, session`
Ordering	`SELECT name, timestamp FROM dependencies ORDER BY timestamp ASC`	`dependencies \| project name, timestamp \| order by timestamp asc nulls last`
Top n by measure	`SELECT TOP 100 name, COUNT(*) as Count FROM dependencies GROUP BY name ORDER BY Count DESC`	`dependencies \| summarize Count = count() by name \| top 100 by Count desc`
Union	`SELECT * FROM dependencies UNION SELECT * FROM exceptions`	`union dependencies, exceptions`
—	`SELECT * FROM dependencies WHERE timestamp > ... UNION SELECT * FROM exceptions WHERE timestamp > ...`	`dependencies \| where timestamp > ago(1d) \| union (exceptions \| where timestamp > ago(1d))`
Join	`SELECT * FROM dependencies LEFT OUTER JOIN exception ON dependencies.operation_Id = exceptions.operation_Id`	`dependencies \| join kind = leftouter (exceptions) on $left.operation_Id == $right.operation_Id`
Nested queries	`SELECT * FROM dependencies WHERE resultCode == (SELECT TOP 1 resultCode FROM dependencies WHERE resultId = 7 ORDER BY timestamp DESC)`	`dependencies \| where resultCode == toscalar( dependencies \| where resultId == 7 \| top 1 by timestamp desc \| project resultCode)`
Having	`SELECT COUNT() FROM dependencies GROUP BY name HAVING COUNT() > 3`	`dependencies \| summarize Count = count() by name \| where Count > 3`

The part I love the most is being able to use EXPLAIN to translate what I’ve written in for 20+ years into KQL.

Nice one Microsoft!!

#Yip.

KQL Series – SQL to KQL Cheat Sheet

SQL to Kusto cheat sheet

Rate

Share

Share

Rate