Kusto 王への道 (1) - 基本

私の仕事では、とても kusto クエリが重要です。華麗にカッコいいクエリが書ける人はおそらくモテメンになるのは間違いありません。少なくとも一部の地域で。
さて、私は「なんとなく」kusto クエリを使ってきたので、ここらでちゃんと理解して、凝ったクエリーを一撃で書けるようになりたいので、ブログに少しづつ書き溜めることにしました。

参考資料はこちらです。

* [Overview](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/)
* [Tutorial](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/tutorial?pivots=azuredataexplorer) 

内容的には、上記のチュートリアルが非常によくできていますので、自分用に解説を加えていきます。あと、多少自分の経験した、「どうやったらええんやろ的なものは踏み込んでみたいと思います）

# Kusto クエリとは
Kusto クエリは、読み込み専用のリクエストで、データを処理して結果を返すものです。リクエストは、スキーマは、クラスタ、データベース、テーブル、そしてカラムといった形式で構造化されています。クエリは、複数のクエリからなりたっています。`;`でデリミタされています。　[tabular expression statement](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/tabularexpressionstatements)という形式で記述されています。

> source1 | operator 1 | operator2 | renderInstruction

という形式になっています。データソース、つまりテーブルを最初に書いて、そのデータのオペレーションをパイプでつないでいくことで、さまざまなデータ処理をすることができます。最後の `renderInstruction` は結果をどのようにレンダリングするかのオプションです。
次の例を見てみましょう。最初の `StormEvents` がテーブルです。そのテーブルを、`where` 句でクエリーをして、該当する日時のデータのみにフィルタしています。最後に、`State` つまり州がフロリダのもののみ抽出して、件数をカウントする関数である `count` を使って件数数えています。

```sql
StormEvents 
| where StartTime >= datetime(2007-11-01) and StartTime < datetime(2007-12-01)
| where State == "FLORIDA"  
| count
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/5d048db3-69d4-ae59-70fa-4b5f4b911fed.png)

これから様々なクエリの文法を見ていきましょう。

# Kusto クエリの文法

## 検索範囲の設定と、表示列の設定　where と project

[where](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/whereoperator) オペレータは検索の範囲を狭めるためのものです。`where` 句で使える論理演算としては、[Query best practices](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/best-practices)などを参考にされると良いでしょう。ほかにも [Samples](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/samples)が充実しています。[project](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/projectoperator)オペレータは、最後表示する列を限定することができます。カラムが多いテーブルだとたくさんカラムが出てくるとうっとおしいことが多いので、`project` で数を制限すると見やすくなります。

```sql
StormEvents
| where StartTime > datetime(2007-02-01) and StartTime < datetime(2007-03-01)
| where EventType == 'Flood' and State == 'CALIFORNIA'
| project StartTime, EndTime , State , EventType , EpisodeNarrative
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/2c7acc02-2cec-ea76-1803-d86c63b0978b.png)

## 取得レコードの制限 take

[take](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/takeoperator)は `limit` のシノニムで、取得する件数を制限します。件数が多すぎるとどうせ見れないので、通常`take` を使って 1000 件程度に制限します。

```sql
StormEvents
| take 5
| project  StartTime, EndTime, EventType, State, EventNarrative
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/3a3c7a98-d2de-7375-2e76-667d8b6b0d1a.png)

## ソート top, asc, desc

[top](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/topoperator) 最初のNレコードを返却します。`desc` で降順、`asc` で昇順です。`by` 句によって、どのカラムに対してソートをかけるのかを指定します。ほかに `null first`, `null last` というオプションを最後につけても構いません。`null` の順番の指定です。

```sql
StormEvents
| top 5 by StartTime desc
| project  StartTime, EndTime, EventType, State, EventNarrative
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/8e7f7340-4128-9d91-96ab-f98ad0254255.png)


## カスタムの列 extend

カスタムの列を作りたいときは [extend](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/extendoperator) を使います。これ以降、通常の列同じ扱いになります。この例では、`Duration` という列を終了時間 - 開始時間で計算して作成しています。
```sql
StormEvents
| limit 5
| extend Duration = EndTime - StartTime 
| project StartTime, EndTime, Duration, EventType, State
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/449d952f-f062-ff31-cca3-5b7477c75c6b.png)


## 件数を数える count
[count()](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/count-aggfunction) は集合関数です。集計で使うようにできているのでそのように呼ばれます。`by` で指定された列の項目ごとに件数をカウントします。[summarize](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/summarizeoperator) オペレータは集合関数、つまり複数の行にわたっての操作を定義します。カウントも複数の行を扱って件数をカウントするので、`summarize` を使って、集合関数を適用した結果が　`event_count` という変数に格納されています。

```sql
StormEvents
| summarize event_count = count() by State
```

## distinct count
重複がない形の count が [dcount](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/summarizeoperator#list-of-aggregation-functions) です。

```sql
StormEvents 
| summarize StormCount = count(), TypeOfStorms = dcount(EventType) by State
| top 5 by StormCount desc
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/196e1740-951b-c55d-101a-c359aa488aa9.png)

## 一定の時間ごとに集合関数を適用する bin
[bin](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/binfunction)関数は、一定の期間ごとのサマリを作ってくれる関数です。ここでは`StartTime` を１日単位でカウントしてくれています。結果を見ると一日毎にカウントがまとめられているのがわかります。

```sql
StormEvents
| where StartTime > datetime(2007-02-14) and StartTime < datetime(2007-02-21)
| summarize event_count = count() by bin(StartTime, 1d)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/6b5afc98-de15-f771-1d72-a3a160cb244e.png)

## グラフを出力する render

`render`によって、グラフを出力することができます。使えるチャートの種類は [render](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/renderoperator?pivots=azuredataexplorer)のページで確認できます。簡単にグラフ出力できて最高！

```sql
StormEvents 
| summarize event_count=count(), mid = avg(BeginLat) by State 
| sort by mid
| where event_count > 1800
| project State, event_count
| render columnchart
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/d74536ef-e7e1-efdd-64a1-64f640d6b0f9.png)

## 複数の列でサマリーする

`in` 句は、特定の列が選択したデータを持ってるもののみを抽出するオペレータです。この例では、サマリを、10h 毎という軸だけではなく、Source つまり、ここで、選択された、`Source, Public, Emergency Manager...` 等のソースごとに集計をしています。カンマで区切るだけなので簡単ですね。

```sql
StormEvents 
| where StartTime > datetime(2007-06-04) and StartTime < datetime(2007-06-10) 
| where Source in ("Source","Public","Emergency Manager","Trained Spotter","Law Enforcement")
| summarize count() by bin(StartTime, 10h), Source
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/bfede597-0bc1-16aa-7fe5-6d69d6bdb6d2.png)

## 周期の分析 bin/floor
ここでは、`bin` のシノニムの `floor` を使っていますが、ポイントは、`% 1d` の部分です。１時間ごとで集計するだけではなく、ここでは、`% 1d` によって、一日のあまりと表現することで、１日のどの時間単に、どれだけの件数があったかを、１日という時間単位で見ることができます。

```sql
StormEvents
| extend hour = floor(StartTime % 1d , 1h)
| summarize event_count=count() by hour
| sort by hour asc
| render timechart
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/c0eee73e-2328-90ff-b50d-691417d54fc8.png)

## 複数軸で、１日の周期的傾向を見る

上記は、時間軸のみでしたが、時間と、州といった２軸で傾向を見ることもできます。カンマで区切るだけです。

```sql
StormEvents
| extend hour= floor( StartTime % 1d , 1h)
| where State in ("GULF OF MEXICO","MAINE","VIRGINIA","WISCONSIN","NORTH DAKOTA","NEW JERSEY","OREGON")
| summarize event_count=count() by hour, State
| render timechart
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/14508b2b-dd5c-ab72-3dd6-fa26ad888e05.png)

## 複数のデータの結合 join

雷と雪崩の両方起きた州を見つけたいときはどうしたらいいでしょうか？　`distict` で重複をなくすのはいいとして、[join](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator?pivots=azuredataexplorer) を使うと、両方の条件に一致する州のみをピックアップしてくれます。

```sql
StormEvents
| where EventType == "Lightning"
| join (
    StormEvents 
    | where EventType == "Avalanche"
) on State  
| distinct State
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/f4cc802d-b4ae-3562-98d2-a6493b95a216.png)

## セッションIdが同一の開始時間と終了時間のペアのデータを処理する join

これも join の例ですが、よくありがちな例として、開始時間を持つイベント、終了時間をもつイベントがあって、そのレイテンシを測定したり、観察したいというケースがよくあります。普通は目 grep になりそうですが、join を使うことで、クレバーに表現することができます。`session_id` が同一であるものという絞り込みをしているのがポイントです。

```sql
Events
| where eventName == "session_started"
| project start_time = timestamp, stop_time, country, session_id
| join ( Events
    | where eventName == "session_ended"
    | project stop_time = timestamp, session_id
    ) on session_id
| extend duration = stop_time - start_time
| project start_time, stop_time, country, duration
| take 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/3c4c8d45-0745-1620-d7fd-92fd9507079a.png)

## パーセンタイル percentiles

これは、集合の傾向を見たいときによく使われるパーセンタイル形式での集計をサポートしています。[パーセンタイル](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/percentiles-aggfunction)は図の通りで、全体の５％のデータであれば、平均はこれだけ、２０％であれば、平均はこれだけ、、、と、集合のパーセンテージ毎の平均値を出したりすることができるものです。

```sql
StormEvents
| extend  duration = EndTime - StartTime
| where duration > 0s
| where duration < 3h
| summarize event_count = count()
    by bin(duration, 5m), State
| sort by duration asc
| summarize percentiles(duration, 5, 20, 50, 80, 95) by State
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/7bc54b5d-08ee-3e8f-0ab9-ea5a64072ec1.png)![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470/d00cfd3d-ba04-bddd-8beb-1a34c31bfd63.png)

## 変数の定義 let 
変数の定義も可能です。Kustoクエリーイケメンがよくこれを使っていました。

```sql
let LightningStorms = 
    StormEvents
    | where EventType == "Lightning";
let AvalancheStorms = 
    StormEvents
    | where EventType == "Avalanche";
LightningStorms 
| join (AvalancheStorms) on State
| distinct State
```

## 複数のクラスタ、デーベースにわたった検索 cluster, database and union

[複数のクラスタやデータベースにわたった検索](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/cross-cluster-or-database-queries?pivots=azuredataexplorer)をしたいケースがあります。[union](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/unionoperator?pivots=azuredataexplorer)オペレータで検索するデータを結合することができて、`cluster` や `database` の特殊関数を使うことで別のクラスタや、データベースの表を対象にすることができます。最後の例は複数のテクニックを組み合わせた例になります。

```sql
FunctionsLogs
| union cluster('clusterA').database('databaseA').FunctionsLogs, cluster('clusterB').database('databaseB').Logs
| where TIMESTAMP >= datetime(2020-09-24 17:30:00) and TIMESTAMP <= datetime(2020-09-24 18:55:00)
```

このクエリーのサンプルでは、`Summary` に詳細な説明がテキストで書いてあって、ある特別な列だけ　`HttpTrigger Completed .... Duration=22ms` とか書いてあります。この`22` のみを抽出して、新たな列を作りたいと考えました。そこで正規表現を使います。[extract](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/extractfunction) でそれが可能になります。`Duration=`に続く数値をマッチさせています。`1` は最初にマッチしたものを指します。ここで、テキストから取得した、Duration の型は残念ながら string なので、 [toint](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/tointfunction)関数で、数値型に変換しています。サマライズの軸を、５秒刻み、Duration（レイテンシ）の平均値、サイト名の結果で、サマリをしたものをタイムチャートにレンダリングしています。

```sql
        | extend Duration = toint(extract("Duration=([0-9.]+)", 1, Summary))
        | order by TIMESTAMP asc
        | project TIMESTAMP, EventName, RuntimeSiteName, Summary, Duration
        | summarize avg_duration=avg(Duration) by bin(TIMESTAMP, 5s), RutimeSiteName
        | render timechart 
```

# さいごに
今回は Kusto の基本関数を学びましたが、これだけでも相当レベルアップしている感があります。今後は実業務の中でやりたくなったことを記録していこうと思っています。最後にとても便利なリンクをまとめておきました。kusto は様々なものにつかわれていますので、一度慣れておくと応用がきいて便利ですね！

* [KQL quick reference](https://docs.microsoft.com/en-us/azure/data-explorer/kql-quick-reference)
* [Query best practices](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/best-practices)
* [Samples](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/samples)