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

Last updated at 2024-03-17Posted at 2024-03-11

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

Azure IoT Hubにエッジデバイスからデータを送信から確認まで #Azure - Qiita

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

おおまかな流れ

Stream Analyticsの作成
Azure SQL(サーバー)の作成
Azure SQL(DB)の作成
Azure Storage Accountの作成
Stream Analyticsの設定
ジョブの開始
Azure SQL DBの確認
Azure Storage Accountの確認

Stream Analyticsの作成

まずは前回作成したIoT Hubと同じリソースグループにStream Analyticsを作成します。

こいつですね

ホスティング環境はクラウドに、

ストレージは作りませんでした。

一旦ここまで

Azure SQL(サーバー)の作成

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

こいつを選んだあとに

データベースサーバーを選びます

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

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

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

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

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

これで作成完了

Azure SQL(DB)の作成

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

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

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

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

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

Azure SQL(DB)テーブルの準備

SQLデータベースリソースの左側のペインの「クエリエディター」に移動して、下記のSQLを投げます。

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

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

Azure Storage Accountの作成

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

こいつですね。

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

これで完了。

Stream Analyticsの設定

IoT Hubを入力元として接続

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

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

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

Blob Storageを出力先として接続

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

「出力を追加する」から「Blob storageまたは…」を選択します

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

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

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

SQL DBを出力先として接続

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

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

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

クエリを作成

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

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

入力と出力

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

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

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

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ストレージに保存しています。

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

加えて、下記部分でIoT Hubからの入力をAzure 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に入力されていることが確認できました。

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

Azure IoT Hubにエッジデバイスからデータを送信から確認まで #Azure - Qiita

クエリのテスト

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

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

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

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