SQL to Kusto query translation

If you’re familiar with SQL and want to learn KQL, translate SQL queries into KQL by prefacing the SQL query with a comment line, --, and the keyword explain. The output shows the KQL version of the query, which can help you understand the KQL syntax and concepts.

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

Output

SQL to Kusto cheat sheet

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

| Category | SQL Query | Kusto Query | Learn more | |–|–|–| | Select data from table | SELECT * FROM dependencies | dependencies | Tabular expression statements | | – | SELECT name, resultCode FROM dependencies | dependencies | project name, resultCode | project | | – | SELECT TOP 100 * FROM dependencies | dependencies | take 100 | take | | Null evaluation | SELECT * FROM dependencies
WHERE resultCode IS NOT NULL | dependencies
| where isnotnull(resultCode) | isnotnull() | | Comparison operators (date) | SELECT * FROM dependencies
WHERE timestamp > getdate()-1 | dependencies
| where timestamp > ago(1d) | ago() | | – | SELECT * FROM dependencies
WHERE timestamp BETWEEN ... AND ... | dependencies
| where timestamp between (datetime(2016-10-01) .. datetime(2016-11-01)) | between | | Comparison operators (string) | SELECT * FROM dependencies
WHERE type = "Azure blob" | dependencies
| where type == "Azure blob" | Logical operators | | – | -- substring
SELECT * FROM dependencies
WHERE type like "%blob%" | // substring
dependencies
| where type has "blob" | has | | – | -- wildcard
SELECT * FROM dependencies
WHERE type like "Azure%" | // wildcard
dependencies
| where type startswith "Azure"
// or
dependencies
| where type matches regex "^Azure.*" | startswith
matches regex | | Comparison (boolean) | SELECT * FROM dependencies
WHERE !(success) | dependencies
| where success == False | Logical operators | | Grouping, Aggregation | SELECT name, AVG(duration) FROM dependencies
GROUP BY name | dependencies
| summarize avg(duration) by name | summarize
avg() | | Distinct | SELECT DISTINCT name, type FROM dependencies | dependencies
| summarize by name, type | summarize
distinct | | – | 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 | count()
dcount() | | Column aliases, Extending | SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies
GROUP BY name | dependencies
| summarize AvgD = avg(duration) by Name=operationName | Alias statement | | – | SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions | ConferenceSessions
| extend session=strcat(sessionid, " ", session_title)
| project conference, session | strcat()
project | | Ordering | SELECT name, timestamp FROM dependencies
ORDER BY timestamp ASC | dependencies
| project name, timestamp
| sort by timestamp asc nulls last | sort | | 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 | top | | Union | SELECT * FROM dependencies
UNION
SELECT * FROM exceptions | union dependencies, exceptions | union | | – | 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 exceptions
ON dependencies.operation_Id = exceptions.operation_Id | dependencies
| join kind = leftouter
(exceptions)
on $left.operation_Id == $right.operation_Id | join | | 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) | toscalar | | Having | SELECT COUNT(\*) FROM dependencies
GROUP BY name
HAVING COUNT(\*) > 3 | dependencies
| summarize Count = count() by name
| where Count > 3 | summarize
where |

Related content

• Use T-SQL to query data