SQL to Kusto query translation

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

Primary language to interact with Kusto is KQL (Kusto Query Language), and in order to make transition and learning experience easier, you can use Kusto service to translate SQL queries to KQL. This can be achieved by sending SQL query to Kusto services prefixing it with 'EXPLAIN' verb.

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 thier KQL equivalients.

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 operatots (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