您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

SQL 到 Kusto 备份单SQL to Kusto cheat sheet

Kusto 支持 SQL 语言的一个子集。Kusto supports a subset of the SQL language. 有关不支持的功能的完整列表,请参阅 SQL 已知问题的列表。See the list of SQL known issues for the full list of unsupported features.

与 Kusto 交互的主要语言为 KQL(Kusto 查询语言)。The primary language to interact with Kusto is KQL (Kusto Query Language). 为了使过渡和学习体验更轻松,可以使用 Kusto 将 SQL 查询转换为 KQL。To make the transition and learning experience easier, you can use Kusto to translate SQL queries to KQL. 将 SQL 查询发送到 Kusto,并在其前面加上动词“EXPLAIN”。Send an SQL query to Kusto, prefixing it with the verb 'EXPLAIN'.

例如:For example:

EXPLAIN 
SELECT COUNT_BIG(*) as C FROM StormEvents 
查询Query
StormEventsStormEvents
| summarize C=count()| summarize C=count()
| project C| project C

SQL 到 Kusto 备份单SQL to Kusto cheat sheet

下表显示了 SQL 中的示例查询及其 KQL 等效项。The table below shows sample queries in SQL and their KQL equivalents.

类别Category SQL 查询SQL Query Kusto 查询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 评估Null evaluation SELECT * FROM dependencies
WHERE resultCode IS NOT NULL
dependencies
| where isnotnull(resultCode)
比较运算符(日期)Comparison operators (date) SELECT * FROM dependencies
WHERE timestamp > getdate()-1
dependencies
| where timestamp > ago(1d)
-- SELECT * FROM dependencies
WHERE timestamp BETWEEN ... AND ...
dependencies
| where timestamp > datetime(2016-10-01)
  and timestamp <= datetime(2016-11-01)
比较运算符(字符串)Comparison operators (string) 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
dependencies
| where type startswith "Azure"
// or
dependencies
| where type matches regex "^Azure.*"
比较(布尔值)Comparison (boolean) SELECT * FROM dependencies
WHERE !(success)
dependencies
| where success == "False"
DistinctDistinct SELECT DISTINCT name, type FROM dependencies dependencies
| summarize by name, type
分组,聚合Grouping, Aggregation SELECT name, AVG(duration) FROM dependencies
GROUP BY name
dependencies
| summarize avg(duration) by name
列别名、扩展Column aliases, Extending SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies
GROUP BY name
dependencies
| summarize AvgD = avg(duration) by Name=operationName
中间件排序Ordering SELECT name, timestamp FROM dependencies
ORDER BY timestamp ASC
dependencies
| project name, timestamp
| order by timestamp asc nulls last
按度量值排名的前 n 位Top n by measure 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
UnionUnion 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))
联接Join 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
嵌套查询Nested queries 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)
HavingHaving SELECT COUNT(*) FROM dependencies
GROUP BY name
HAVING COUNT(*) > 3
dependencies
| summarize Count = count() by name
| where Count > 3