SQL to Kusto query translation

  • 2 minutes to read

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 Kusto 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'.

For example:

EXPLAIN 
SELECT COUNT_BIG(*) as C FROM StormEvents
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 > 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"
Distinct SELECT DISTINCT name, type FROM dependencies dependencies
| summarize by name, type
Grouping, Aggregation SELECT name, AVG(duration) FROM dependencies
GROUP BY name		 dependencies
| summarize avg(duration) by name
Column aliases, Extending SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies
GROUP BY name		 dependencies
| summarize AvgD = avg(duration) by operationName
| project Name = operationName, AvgD
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