Aide-mémoire SQL pour Langage de requête Kusto

  • Article

Si vous êtes familiarisé avec SQL et que vous souhaitez apprendre KQL, convertissez les requêtes SQL en KQL en préfaçant la requête SQL avec une ligne de commentaire, --et le mot clé explain. La sortie montre la version KQL de la requête, ce qui peut vous aider à comprendre la syntaxe et les concepts KQL.

Exécuter la requête

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

Sortie

Requête
StormEvents<br>| summarize C=count()<br>| project C

Aide-mémoire SQL vers Kusto

Le tableau suivant présente des exemples de requêtes dans SQL et leurs équivalents KQL.

Category Requête SQL Requête Kusto En savoir plus
Sélectionnez les données d’une table SELECT * FROM dependencies dependencies Instructions d’expression tabulaire
-- SELECT name, resultCode FROM dependencies dependencies | project name, resultCode project
-- SELECT TOP 100 * FROM dependencies dependencies | take 100 take
Évaluation de la valeur null SELECT * FROM dependencies
WHERE resultCode IS NOT NULL		 dependencies
| where isnotnull(resultCode)		 isnotnull()
Opérateurs de comparaison (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
Opérateurs de comparaison (chaîne) SELECT * FROM dependencies
WHERE type = "Azure blob"		 dependencies
| where type == "Azure blob"		 Opérateurs logiques
-- -- 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
correspond à regex
Comparaison (booléenne) SELECT * FROM dependencies
WHERE !(success)		 dependencies
| where success == False		 Opérateurs logiques
Regroupement, agrégation SELECT name, AVG(duration) FROM dependencies
GROUP BY name		 dependencies
| summarize avg(duration) by name		 Résumer
avg()
Distinct SELECT DISTINCT name, type FROM dependencies dependencies
| summarize by name, type		 Résumer
Distinctes
-- 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()
Alias de colonne, étendre SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies
GROUP BY name		 dependencies
| summarize AvgD = avg(duration) by Name=operationName		 Alias, instruction
-- SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions ConferenceSessions
| extend session=strcat(sessionid, " ", session_title)
| project conference, session		 strcat()
Projet
Classement SELECT name, timestamp FROM dependencies
ORDER BY timestamp ASC		 dependencies
| project name, timestamp
| sort by timestamp asc nulls last		 sort
N premiers par mesure 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
Requêtes imbriquées 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		 Résumer
Où

Contenu connexe

  • Utiliser T-SQL pour interroger des données