Partager via


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

S’applique à : ✅Microsoft Fabric✅Azure Data ExplorerAzure MonitorMicrosoft Sentinel

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

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

Sortie

Requête
StormEvents<br> | résumer C=count()<br>| projet C

Aide-mémoire SQL vers Kusto

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

Catégorie 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"
a
-- -- wildcard
SELECT * FROM dependencies
WHERE type like "Azure%"
// wildcard
dependencies
| where type startswith "Azure"
// or
dependencies
| where type matches regex "^Azure.*"
startswith
matches 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
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()
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
Instruction Alias
-- SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions ConferenceSessions
| extend session=strcat(sessionid, " ", session_title)
| project conference, session
strcat()
project
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))
Participer 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
Possession SELECT COUNT(\*) FROM dependencies
GROUP BY name
HAVING COUNT(\*) > 3
dependencies
| summarize Count = count() by name
| where Count > 3
résumer
  • Utiliser T-SQL pour interroger des données