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.