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:
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 | dependencies |
| Comparison operators (date) | SELECT * FROM dependencies | dependencies |
| — | SELECT * FROM dependencies | dependencies |
| Comparison operators (string) | SELECT * FROM dependencies | dependencies |
| — | -- substring | // substring |
| — | -- wildcard | // wildcard |
| Comparison (boolean) | SELECT * FROM dependencies | dependencies |
| Grouping, Aggregation | SELECT name, AVG(duration) FROM dependencies | dependencies |
| Distinct | SELECT DISTINCT name, type FROM dependencies | dependencies |
| — | SELECT name, COUNT(DISTINCT type) | dependencies |
| Column aliases, Extending | SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies | dependencies |
| — | SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions | ConferenceSessions |
| Ordering | SELECT name, timestamp FROM dependencies | dependencies |
| Top n by measure | SELECT TOP 100 name, COUNT(*) as Count FROM dependencies | dependencies |
| Union | SELECT * FROM dependencies | union dependencies, exceptions |
| — | SELECT * FROM dependencies | dependencies |
| Join | SELECT * FROM dependencies | dependencies |
| Nested queries | SELECT * FROM dependencies | dependencies |
| Having | SELECT COUNT(*) FROM dependencies | dependencies |
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.