SQL から Kusto のチート シート

Kusto では、SQL 言語のサブセットがサポートされています。 サポートされていない機能の完全な一覧については、SQL の既知の問題に関するページの一覧を参照してください。

Kusto と対話するための主な言語は KQL (Kusto クエリ言語) です。 移行と学習を容易にするため、Kusto を使用して SQL クエリを KQL に変換できます。 前に "EXPLAIN" という動詞を付けた SQL クエリを、Kusto に送信します。

次に例を示します。

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 > datetime(2016-10-01)
  and timestamp <= 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
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.*"
比較 (ブール値) 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
dependencies
| summarize by name, type | summarize count() by name
// or approximate for large sets
dependencies
| 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 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
和集合 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
[入れ子になったクエリ] 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