SQL 到 Kusto 备份单

  • 项目

Kusto 支持 SQL 语言的一个子集。 有关不支持的功能的完整列表，请参阅 SQL 已知问题的列表。

与 Kusto 交互的主要语言为 KQL（Kusto 查询语言）。 为了使过渡和学习体验更轻松，可以使用 Kusto 将 SQL 查询转换为 KQL。 将 SQL 查询发送到 Kusto，并在其前面加上动词“EXPLAIN”。

例如：

EXPLAIN 
SELECT COUNT_BIG(*) as C FROM StormEvents

输出

查询
StormEvents
| summarize C=count()
| project C

SQL 到 Kusto 备份单

下表显示了 SQL 中的示例查询及其 KQL 等效项。

类别 SQL 查询 Kusto 查询
从表中选择数据 SELECT * FROM dependencies dependencies
-- SELECT name, resultCode FROM dependencies dependencies | project name, resultCode
-- SELECT TOP 100 * FROM dependencies dependencies | take 100
Null 评估 SELECT * FROM dependencies
WHERE resultCode IS NOT NULL		 dependencies
| where isnotnull(resultCode)
比较运算符（日期） SELECT * FROM dependencies
WHERE timestamp > getdate()-1		 dependencies
| where timestamp > ago(1d)
-- SELECT * FROM dependencies
WHERE timestamp BETWEEN ... AND ...		 dependencies
| where timestamp between (datetime(2016-10-01) .. 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
依赖项
| where type contains "blob"
-- -- wildcard
SELECT * FROM dependencies
WHERE type like "Azure%"		 // wildcard
依赖项
| where type startswith "Azure"
// or
依赖项
| where type matches regex "^Azure.*"
比较（布尔值） SELECT * FROM dependencies
WHERE !(success)		 dependencies
| where success == "False"
分组，聚合 SELECT name, AVG(duration) FROM dependencies
GROUP BY name		 dependencies
| summarize avg(duration) by name
Distinct SELECT DISTINCT name, type FROM dependencies dependencies
| summarize by name, type
-- SELECT name, COUNT(DISTINCT type)
FROM dependencies
GROUP BY name		 依赖关系
| summarize by name, type | summarize count() by name
// or approximate for large sets
依赖项
| summarize dcount(type) by name
列别名、扩展 SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies
GROUP BY name		 dependencies
| summarize AvgD = avg(duration) by Name=operationName
-- SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions ConferenceSessions
| extend session=strcat(sessionid, " ", session_title)
| project conference, session
中间件排序 SELECT name, timestamp FROM dependencies
ORDER BY timestamp ASC		 dependencies
| project name, timestamp
| order by timestamp asc nulls last
按度量值排名的前 n 位 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
Union SELECT * FROM dependencies
UNION
SELECT * FROM exceptions		 union dependencies, exceptions
-- SELECT * FROM dependencies
WHERE timestamp > ...
UNION
SELECT * FROM exceptions
WHERE timestamp > ...		 dependencies
| where timestamp > ago(1d)
| union
  (exceptions
  | where timestamp > ago(1d))
联接 SELECT * FROM dependencies
LEFT OUTER JOIN exception
ON dependencies.operation_Id = exceptions.operation_Id		 dependencies
| join kind = leftouter
  (exceptions)
on $left.operation_Id == $right.operation_Id
嵌套查询 SELECT * FROM dependencies
WHERE resultCode ==
(SELECT TOP 1 resultCode FROM dependencies
WHERE resultId = 7
ORDER BY timestamp DESC)		 dependencies
| where resultCode == toscalar(
  依赖项
  | where resultId == 7
  | top 1 by timestamp desc
  | project resultCode)
Having SELECT COUNT(*) FROM dependencies
GROUP BY name
HAVING COUNT(*) > 3		 dependencies
| summarize Count = count() by name
| where Count > 3