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. Kusto に SQL クエリを送信します。これにプレフィックスとして動詞 ' 説明 ' を付けます。Send an SQL query to Kusto, prefixing it with the verb 'EXPLAIN'.

次に例を示します。For example:

EXPLAIN 
SELECT COUNT_BIG(*) as C FROM StormEvents 
クエリQuery
StormEventsStormEvents
C の集計 = count ()summarize C=count()
プロジェクト Cproject 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)
比較演算子 (date)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)
比較演算子 (string)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 operationName
| project Name = operationName, AvgD
順序Ordering SELECT name, timestamp FROM dependencies
ORDER BY timestamp ASC
dependencies
| project name, timestamp
| order by timestamp asc nulls last
メジャー別の上位 nTop 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))
JoinJoin 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)
保持Having SELECT COUNT(*) FROM dependencies
GROUP BY name
HAVING COUNT(*) > 3
dependencies
| summarize Count = count() by name
| where Count > 3