KQL Series – SQL to KQL Cheat Sheet
source link: https://hybriddbablog.com/2022/03/31/kql-series-sql-to-kql-cheat-sheet/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
KQL Series – SQL to KQL Cheat Sheet
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.
CategorySQL QueryKusto QuerySelect data from tableSELECT * FROM dependencies
dependencies
—SELECT name, resultCode FROM dependencies
dependencies | project name, resultCode
—SELECT TOP 100 * FROM dependencies
dependencies | take 100
Null evaluationSELECT * FROM dependencies
WHERE resultCode IS NOT NULLdependencies
Comparison operators (date)
| where isnotnull(resultCode)SELECT * FROM dependencies
WHERE timestamp > getdate()-1dependencies
—
| where timestamp > ago(1d)SELECT * FROM dependencies
WHERE timestamp BETWEEN ... AND ...dependencies
Comparison operators (string)
| where timestamp > datetime(2016-10-01)
and timestamp <= datetime(2016-11-01)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
Comparison (boolean)
dependencies
| where type startswith "Azure"
// or
dependencies
| where type matches regex "^Azure.*"SELECT * FROM dependencies
WHERE !(success)dependencies
Grouping, Aggregation
| where success == "False"SELECT name, AVG(duration) FROM dependencies
GROUP BY namedependencies
Distinct
| summarize avg(duration) by nameSELECT DISTINCT name, type FROM dependencies
dependencies
—
| summarize by name, typeSELECT name, COUNT(DISTINCT type)
FROM dependencies
GROUP BY namedependencies
Column aliases, Extending
| summarize by name, type | summarize count() by name
// or approximate for large sets
dependencies
| summarize dcount(type) by nameSELECT operationName as Name, AVG(duration) as AvgD FROM dependencies
GROUP BY namedependencies
—
| summarize AvgD = avg(duration) by Name=operationNameSELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions
ConferenceSessions
Ordering
| extend session=strcat(sessionid, " ", session_title)
| project conference, sessionSELECT name, timestamp FROM dependencies
ORDER BY timestamp ASCdependencies
Top n by measure
| project name, timestamp
| order by timestamp asc nulls lastSELECT TOP 100 name, COUNT(*) as Count FROM dependencies
GROUP BY name
ORDER BY Count DESCdependencies
Union
| summarize Count = count() by name
| top 100 by Count descSELECT * FROM dependencies
UNION
SELECT * FROM exceptionsunion dependencies, exceptions
—SELECT * FROM dependencies
WHERE timestamp > ...
UNION
SELECT * FROM exceptions
WHERE timestamp > ...dependencies
Join
| where timestamp > ago(1d)
| union
(exceptions
| where timestamp > ago(1d))SELECT * FROM dependencies
LEFT OUTER JOIN exception
ON dependencies.operation_Id = exceptions.operation_Iddependencies
Nested queries
| join kind = leftouter
(exceptions)
on $left.operation_Id == $right.operation_IdSELECT * FROM dependencies
WHERE resultCode ==
(SELECT TOP 1 resultCode FROM dependencies
WHERE resultId = 7
ORDER BY timestamp DESC)dependencies
Having
| where resultCode == toscalar(
dependencies
| where resultId == 7
| top 1 by timestamp desc
| project resultCode)SELECT COUNT(*) FROM dependencies
GROUP BY name
HAVING COUNT(*) > 3dependencies
| summarize Count = count() by name
| where Count > 3
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK