[Azure] IoT Hub と SQL DB を Stream Analytics を介して接続する

前回、デバイスからIoT Hubにデータを送信する方法を書きました。

:::note info
[Azure IoT Hubにエッジデバイスからデータを送信から確認まで #Azure - Qiita](https://qiita.com/fsd_maclala/items/d5a295039abd913b6af9)
:::

今回は IoT Hubに到達したデータをデータベース(Azure SQL)に格納してゆく方法を書きます。
なお、Azure SQLには必要なデータだけを格納してゆきますので、通信の生データを同時にblobストレージに格納することもやります。

## おおまかな流れ
1. Stream Analyticsの作成
1. Azure SQL(サーバー)の作成
1. Azure SQL(DB)の作成
1. Azure Storage Accountの作成
1. Stream Analyticsの設定
1. ジョブの開始
1. Azure SQL DBの確認
1. Azure Storage Accountの確認

## Stream Analyticsの作成
まずは前回作成したIoT Hubと同じリソースグループにStream Analyticsを作成します。
![スクリーンショット 2024-03-08 162520.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/b94070e7-578c-522e-5201-537e0d3dd11e.png)
こいつですね

ホスティング環境はクラウドに、
![スクリーンショット 2024-03-08 162607.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/4a9d71ed-db33-5721-35de-2e8026afdd56.png)

ストレージは作りませんでした。
![スクリーンショット 2024-03-08 162615.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/cfcbd99c-8207-6cae-b60c-64e61d958620.png)

一旦ここまで

## Azure SQL(サーバー)の作成
次にSQLサーバーを作ります。
Azure SQLはAzure SQL(サーバー)とAzure SQL(DB)をセットで作成しないといけないようです。
DBは実体で、サーバーは論理的にそれらを管理するリソースということのようです。

![スクリーンショット 2024-03-08 162928.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/7190ff68-19fa-8b97-a678-e57f899cc9bd.png)
こいつを選んだあとに

![スクリーンショット 2024-03-08 162938.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/1c88a4b2-d015-6922-b9e3-2a1a12df8ccf.png)
データベースサーバーを選びます

それで認証を方法を選ぶ場面があるのですが、ここはSQL 認証を使用するを選んでおいたほうがよさそうです。
それ以外の方法ではStream AnalyticsとDBの接続がうまくいきませんでした。（うまくいく方法もあるとは思うのですが）

![スクリーンショット 2024-03-08 163211.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/9875fd16-2420-3877-ce83-8c44e9383f41.png)

それで、このときサーバー管理者ログインの欄にメールアドレスを入力したんですよね。このときバリデートが通ってしまって、普通に作れるんですけど、メールアドレスの形式ではどうもStream Analyticsからの指定接続ができないっぽかったです（これもできる方法あるのかもしれませんが）

なので、普通の無難なIDの形式にしたほうがよいと思います。

このあたりで躓いて結構時間食ってしまいました。

![スクリーンショット 2024-03-08 163506.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/92fd738e-ba98-7075-8619-bc5063a96bcd.png)

あとネットワークのこの部分は「はい」にしておきましょう（後からリカバリーできますが）

これで作成完了

## Azure SQL(DB)の作成
次にAzure SQL(DB)を作っていきます

さっきのSQLデータベースの作成選択肢のなかから単一データベースを選択します。

![スクリーンショット 2024-03-08 163753.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/81f00690-ad35-08a6-ed04-025876797e66.png)

ここでサーバーを選択する場面があるので、先程作ったAzure SQL(サーバー)を選択しましょう。

![スクリーンショット 2024-03-08 163839.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/c1d6f29c-7cb5-d8b7-b675-5581a0d0f4ad.png)

ローカルの環境からDBにアクセスしたいこともあると思うので、ファイアウォールでは自分のIPアドレスを追加しておきましょう

![スクリーンショット 2024-03-08 164103.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/d8c7d228-cc8e-da15-be85-c6679a60ee00.png)

あとは一旦そのまま、これでAzure SQL(DB)の作成完了、ということでリソースに移動してテーブルを作成します。

### Azure SQL(DB)テーブルの準備
SQLデータベースリソースの左側のペインの「クエリエディター」に移動して、下記のSQLを投げます。

```SQL
CREATE TABLE test_table (
    create_time DATETIME PRIMARY KEY,
    temperature FLOAT,
    humidity FLOAT
);
```

![スクリーンショット 2024-03-08 171926.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/7cd21ddd-173f-1c22-0858-4213f40b90f5.png)

するとテーブルが作成されました。これでOK

![スクリーンショット 2024-03-08 172344.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/f38b60d9-5c17-f850-45ee-14c2cd125cff.png)


## Azure Storage Accountの作成

IoT Hubから飛んでくる生データを格納するストレージを作成します。

![スクリーンショット 2024-03-08 164405.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/9b73b143-5a6f-4deb-ef73-86e70b47a08f.png)
こいつですね。

作成したあとはリソースメニューの中からコンテナーも作成しておきましょう。

![スクリーンショット 2024-03-08 173252.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/3b3f3f31-4b60-3bca-60ff-21f15ee04e50.png)

これで完了。

## Stream Analyticsの設定


### IoT Hubを入力元として接続

いよいよつなぎ込みをやっていきます。
Stream Analyticsのリソースに移動して「入力」を選択します。

![input.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/a075e8cd-c0a6-860e-c116-3197fc6b2424.png)


入力の追加からIoT Hubを選択して追加します。

![スクリーンショット 2024-03-08 172833.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/d86a55b8-baea-0b99-c6dd-8e0d245dfdc9.png)

IoT Hubを選択すると右側にペインが出てきて、IoT Hubの接続の設定をします。
サブスクリプションの中のIoT Hubを自動で引っ張ってきてくれるので簡単です。
一点、「入力のエイリアス」は「input〇〇」としておいたほうが後からわかりやすくてよいです。
今回は「input-iot-hub」としました。

![スクリーンショット 2024-03-08 165740.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/bc0d6fe5-96d4-e29c-f323-aba00e40c273.png)

### Blob Storageを出力先として接続

Stream Analyticsリソースの左側ペインから「出力」を選択

![output.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/c1d154f1-a95e-de9a-00aa-6671af744494.png)

「出力を追加する」から「Blob storageまたは…」を選択します
![スクリーンショット 2024-03-08 172622.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/e22dd8d2-8031-dd22-bcf2-fa4b12a5753f.png)

出力エイリアスを「output-blog」などにしておいたほうが後々わかりやすいです。

コンテナーは、ストレージで作っておいたrawdataコンテナーを選びます。

パスパターンは {date} とします。

![スクリーンショット 2024-03-08 170124.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/def85c1b-1521-26fb-4662-10aa6a705a7c.png)



### SQL DBを出力先として接続

「出力を追加する」から「SQL Database」を選択します。

![db.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/f0e7b604-dad2-574e-d195-827665f16989.png)

同じくエイリアスはoutput-sql-dbなどにしておきましょう。

ユーザー名とパスワードを入れると、テーブル部分は既存のテーブルの読み込みで取ってこれるので、先程DBの作成の際に作ったテーブルを指定します。

![スクリーンショット 2024-03-08 171617.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/f5fceae8-7b08-8e81-160b-d8e7dbeefbf0.png)

### クエリを作成

次にクエリを作成して、入力元と出力元を繋げていきます。

![スクリーンショット 2024-03-08 164624.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/fb7c9124-b3a7-0c48-d7f2-3d77e47ca1e4.png)

クエリを開くと、概ね以下のような状況になっているかと思います（クエリはまだ書かれていないはず）

![スクリーンショット 2024-03-11 134109.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/4e1a3bca-3566-3d8e-f166-35210bdbb1f4.png)

#### 入力と出力

これまでの作業で入力と出力を設定できていると、以下のような状況になっていると思います。

![スクリーンショット 2024-03-11 133744.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/aae4c28d-a047-451e-b381-1cabee33f43b.png)

この入力と出力として設定したエイリアスは右側のクエリ部で引用して使用することができます。

![スクリーンショット 2024-03-11 133828.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/8e8d645b-4773-2dde-88d0-23e6e0784fed.png)

クエリには一旦以下のように書くと良いです。

```sql
SELECT
    *
INTO
    [output-blob]
FROM
    [input-iot-hub]

SELECT
    EventProcessedUtcTime as [create_time],
    temperature,
    humidity
INTO
    [output-sql-db]
FROM 
    [input-iot-hub]
```

下記部分で、IoT Hubからの入力をblobストレージに保存しています。

```sql
SELECT
    *
INTO
    [output-blob]
FROM
    [input-iot-hub]
```

加えて、下記部分でIoT Hubからの入力をAzure Sqlに格納するということになります。

```sql
SELECT
    EventProcessedUtcTime as [create_time],
    temperature,
    humidity
INTO
    [output-sql-db]
FROM 
    [input-iot-hub]
```



#### プレビューエリアについて

#### 入力のプレビュー

このエリアではIoT Hubからの入力を確認したり、SQL-DBに出力としてどのように格納されるかということを確認することができます。

下図の最新の情報に更新ボタンを押すと、IoT Hubに入ってきている情報を見ることができます。
ボタンを押してしばらくすると、その下段にtemperature: 25.56 humidity:60.35 などの情報がIoT Hubに入力されていることが確認できました。

![スクリーンショット 2024-03-11 134453.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/4048d447-31a6-af27-3cb3-7723967d9ba8.png)

:::note info
IoT Hubに情報をおくる方法は前回の記事を参照してください。

[Azure IoT Hubにエッジデバイスからデータを送信から確認まで #Azure - Qiita](https://qiita.com/fsd_maclala/items/d5a295039abd913b6af9)
:::

##### クエリのテスト

クエリを走らせたときに、どのような結果になるかは、テスト結果のタブで見ることができます。
クエリのテストボタンを押すと、このタブに結果が表示されます。

![スクリーンショット 2024-03-11 155932.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/cc38e82b-5f53-615b-0c7d-24c6a37574ae.png)


#### SQLテーブルスキーマのプレビュー

SQLテーブルのカラムと、クエリによって処理される値の型チェックなどを行うことができます。
定かじゃないんですが、**どうもこの部分がすべて「一致」とならないとDBに飛んで行ってくれなかったような挙動をしました。** 
ひょっとすると、その時、その他なにか致命的な設定ミスがあったのかもしれませんが。。
このあたりもひっかかりポイントでしたね。

ジョブを開始するにも停止するにも2~3分時間がかかるので、このあたりの試行錯誤は少し面倒かもしれません。

![スクリーンショット 2024-03-11 160019.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/d26d2498-b08f-f225-244e-58b2750eddbf.png)

## ジョブの開始

ここまで確認できたらいよいよジョブを開始しましょう。

ジョブを開始することで、IoT Hubに入力されたデータが先程Stream Analyticsに設定された処理に基づいて、DBとblobストレージに格納されていくことになります。

![スクリーンショット 2024-03-11 160607.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/102d9c7d-df1e-752e-2e45-814cf2e80dac.png)


## Azure SQL DBの確認

ジョブが開始したらIoT Hubにデータを送ってみます。
データの送信を確認したらAzure SQL(DB)に行って送信先に指定したテーブルの中身を確認してみましょう。

うまく設定ができていれば、データが到着しているのが確認できると思います。

![スクリーンショット 2024-03-11 161059.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/203ee16f-12d7-e1f1-f029-e11170679bc8.png)


## Azure Storage Accountの確認

次に、生データを記録しているはずのblobストレージを確認します。

ストレージアカウントリソースに移動して、コンテナーを確認します。

![スクリーンショット 2024-03-11 161404.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/3943d25c-91dd-9900-b29e-94ea07c1f6aa.png)

中に入ると、日付によってパスが規定されていますので、それを辿ってblobオブジェクトに到達したらダウンロードします。

![スクリーンショット 2024-03-11 161523.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/f0c664cb-356c-bce5-3c36-5adfe0227a21.png)

ダウンロードしたjsonファイルを開くと、IoT Hubから送られた全てのデータが記録されていることが確認できました。

![スクリーンショット 2024-03-11 161751.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3249097/974c518b-26a4-7f01-efd8-5c31a0bd6263.png)


ということで、IoT Hubに送ったデータの生データをblobストレージに、抽出データをDBに格納することができました。

この記事を書くにあたって、下記の記事を大いに参考にさせていただきました。

[［初心者向］Azure IoT システム構築　（ハンズオン） – サポートページTOP](https://cptechweb.teldevice.co.jp/hc/ja/articles/1500006082442--%E5%88%9D%E5%BF%83%E8%80%85%E5%90%91-Azure-IoT-%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0%E6%A7%8B%E7%AF%89-%E3%83%8F%E3%83%B3%E3%82%BA%E3%82%AA%E3%83%B3)

心より感謝申し上げます。