Edit

SQL to Kusto cheat sheet

  • Article
  • 2 minutes to read

If you're familiar with SQL and want to learn KQL, you can use Azure Data Explorer to translate SQL queries into KQL.

To translate an SQL query, preface the SQL query with a comment line, --, and the keyword explain. The output will show the KQL version of the query, which can help you understand the KQL syntax and concepts.

Run the query

--
explain
SELECT COUNT_BIG(*) as C FROM StormEvents

Output

Query
StormEvents
| summarize C=count()
| project C

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 between (datetime(2016-10-01) .. 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

Next steps

  • Use T-SQL to query data