Azure SQL Database に MQTT データをストリームする

Azure SQL Database は、Microsoft が提供するフルマネージドのリレーショナルデータベースサービスであり、高可用性、セキュリティ、スケーラビリティを備えた構造化データの管理を可能にします。EMQX Cloud と Azure SQL Database の統合により、MQTT データを信頼性高く構造化データベースに保存・管理でき、リアルタイム分析、レポーティング、下流処理を実現します。この統合により、EMQX は MQTT 対応デバイスと Azure SQL Database 間のブリッジとして機能し、IoT テレメトリをリレーショナルテーブルにシームレスに取り込みます。また、Power BI、Azure Synapse、Logic Apps などの他の Azure サービスとの連携も容易にし、高度な分析、可視化、自動化ワークフローをサポートします。

本ページでは、EMQX と Azure SQL Database の統合方法について包括的に解説します。Azure SQL Database コネクターの作成、ルールの設定およびテスト方法を説明し、さらに MQTT プロトコルを介してシミュレートした温度・湿度データを EMQX Cloud に送信し、構成済みのデータ統合を通じて Azure Event Hubs に保存する方法も示します。

動作概要

Azure SQL Database とのデータ統合は EMQX Cloud の標準機能であり、EMQX のリアルタイムデバイス接続およびメッセージ処理能力と、Azure SQL Database の構造化ストレージおよびクエリ機能を組み合わせています。EMQX の組み込みルールエンジンを利用することで、複雑なコードを書くことなく MQTT メッセージを変換、拡充し、Azure SQL Database に保存できます。

全体の流れは以下の通りです：

1. IoT デバイスがメッセージをパブリッシュ：デバイスは MQTT プロトコルで EMQX に接続し、特定のトピックにテレメトリ、ステータス更新、センサー情報をパブリッシュします。受信したメッセージはルールエンジンのトリガーとなります。
2. ルールエンジンがメッセージを処理：EMQX の SQL ベースのルールエンジンは、定義されたルールに基づいてメッセージを評価します。メッセージはフィルタリング、変換、タイムスタンプやデバイスメタデータなどの追加コンテキストで拡充されてから保存されます。
3. Azure SQL Database へのデータ書き込み：ルール設定に従い、処理済みメッセージは Azure SQL Database の特定テーブル・カラムに挿入または更新されます。SQL テンプレートにより、メッセージフィールドとデータベースカラムの正確なマッピングが可能で、構造化かつ一貫した保存を実現します。
4. データの保存と活用：保存された IoT データは即座にクエリ、レポート、分析に利用可能です。また、Power BI、Synapse Analytics、Logic Apps など他の Azure サービスと連携し、可視化、自動化ワークフロー、高度な処理を行えます。

この統合により、リアルタイムデータ取り込み、構造化ストレージ、シームレスな分析を組み合わせたフルマネージドの IoT データパイプラインを構築でき、デバイスのテレメトリから迅速な洞察と運用インテリジェンスを得られます。

特長とメリット

EMQX Cloud と Azure SQL Database 間のデータ統合は、以下の機能とビジネス上の利点を提供します：

リアルタイム IoT データ取り込み： EMQX は高頻度の MQTT メッセージを直接 Azure SQL Database に処理・転送でき、即時保存およびリアルタイムの監視、分析、運用ワークフローを可能にします。

構造化かつクエリ可能なストレージ： Azure SQL Database はスキーマ、制約、インデックスを完全サポートしたリレーショナルテーブルに IoT データを保存します。これにより正確なクエリ、レポート、履歴分析が可能となり、デバイスデータから実用的な洞察を得やすくなります。

前処理およびデータ変換： EMQX の SQL ベースルールエンジンにより、メッセージは Azure SQL Database に届く前にフィルタリング、拡充、集約、再フォーマットが可能です。これにより関連性が高く整ったデータのみが保存され、下流処理の複雑さを軽減します。

スケーラブルかつ高性能： EMQX と Azure SQL Database は水平・垂直スケーリングをサポートします。EMQX は数百万の MQTT 接続を処理でき、Azure SQL Database は増大するデータ量に応じて計算資源とストレージを自動スケールし、性能を維持します。

はじめる前に

このセクションでは、Azure SQL Database データ統合を作成する前に必要な準備について説明します。Azure SQL Database インスタンスの作成（未作成の場合）、SQL サーバーおよびネットワークアクセスルールの設定、MQTT メッセージ保存用のデータベースとテーブルの準備が必要です。また、後で EMQX Cloud で使用する接続情報も用意してください。

前提条件

• ルールの理解
• データ統合の理解

ネットワーク設定

データ統合を構成する前に、EMQX Cloudのデプロイメントを作成し、EMQX Cloudと対象サービス間のネットワーク接続を確立していることを確認してください。

• Dedicated Flexデプロイメントの場合：

  EMQX CloudのVPCと対象サービスのVPC間でVPCピアリング接続を作成します。ピアリング接続が確立されると、EMQX Cloudは対象サービスのプライベートIPアドレスを介してアクセス可能になります。

  パブリックIP経由でのアクセスが必要な場合は、NATゲートウェイを構成してアウトバウンド接続を有効にしてください。

• BYOC（Bring Your Own Cloud）デプロイメントの場合：

  BYOCデプロイメントが稼働しているVPCと対象サービスをホストするVPC間でVPCピアリング接続を作成します。ピアリングが確立されると、対象サービスのプライベートIPアドレスを介してアクセス可能になります。

  対象サービスにパブリックIP経由でアクセスする必要がある場合は、クラウドプロバイダーのコンソールを使用してBYOC VPCにNATゲートウェイを構成してください。

Azure SQL Database のセットアップ

MQTT メッセージを保存するための Azure SQL Database インスタンス作成手順です。既にデータベースがある場合はこのステップをスキップし、プライベートエンドポイントの作成に進んでください。

1. Azure ポータルで Azure SQL | SQL databases サービスページに移動し、作成 を選択します。
2. Create SQL Database フォームの 基本 タブで以下を設定します：
   • サブスクリプション：希望の Azure サブスクリプションを選択。
   • リソースグループ：既存のリソースグループを選択、または 新規作成 を選択。
   • データベース名：後でコネクター作成時に使用する名前を入力。本ガイドでは emqx を使用します。
   • サーバー：新規作成 を選択し、フォームに記入：
     • サーバー名：サーバー名を入力。
     • 場所：EMQX デプロイと同じリージョンを選択。
     • 認証方法：SQL 認証を使用 を選択。
     • サーバー管理者ログイン / パスワード：Azure の要件を満たすユーザー名とパスワードを入力。後で接続・コネクター作成時に使用するため控えておきます。
3. その他のオプションは必要に応じて設定してください。
4. OK を選択してサーバー作成を完了します。
5. Create SQL Database フォームに戻り、次へ: ネットワーク を選択して進みます。

プライベートエンドポイントの作成

Azure SQL Database への安全なネットワークアクセスを有効にするため、プライベートエンドポイントを作成します。

• 既にデータベースがある場合は、概要 → プロパティ → ネットワーク → プライベート → + プライベートエンドポイントの追加 に進みます。
• 前節でデータベースを作成した場合は、ネットワーク タブに直接 + プライベートエンドポイントの追加 ボタンがあります。

1. プライベートエンドポイントの作成 フォームで、対象のサブスクリプション、リソースグループ、仮想ネットワーク、サブネットを選択します。
2. DNS 設定では、プライベート DNS ゾーンと統合 を はい にし、デフォルトの プライベート DNS ゾーン をそのままにします。
3. OK を選択。プライベートエンドポイントが作成されると、状態が Succeeded と表示されます。
4. プライベートエンドポイント → 設定 → DNS 設定 から IP アドレス と FQDN を控えておきます。

データベースとデータテーブルの作成

Azure SQL Database に接続し、MQTT データ保存用のテーブルを作成する方法を説明します。接続方法はいくつかありますが、ここでは Azure ポータルの クエリエディター を使用した手順を示します。

1. こちらのドキュメントに従い、サーバーレベルの IP ファイアウォールルールを作成します。
2. SQL データベースの 概要 ページで、左メニューから クエリエディター（プレビュー） を選択します。
3. サインイン画面でデータベース接続用の認証情報を入力します。
4. 以下の SQL コマンドを実行し、temp_hum テーブルを作成します。このテーブルはデバイスから報告される温度・湿度データの保存に使用します。

 CREATE TABLE temp_hum(
   client_id VARCHAR(64) NULL,
   temp NVARCHAR(100) NULL,
   hum NVARCHAR(100) NULL,
   up_timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
 GO;

Microsoft SQL Server コネクターの作成

データ統合ルールを作成する前に、Azure SQL サーバーにアクセスするための Microsoft SQL Server コネクターを作成します。

1. デプロイメントに移動し、左ナビゲーションメニューから データ統合 を選択します。
2. 初めてコネクターを作成する場合は、データ永続化 カテゴリの下にある Microsoft SQL Server を選択します。既にコネクターを作成済みの場合は、新規コネクター を選択し、続いて データ永続化 カテゴリの下の Microsoft SQL Server を選択します。
3. コネクター名：システムが自動的にコネクター名を生成します。
4. 接続情報を入力します：
   • サーバーホスト：前節で控えた FQDN とポート 1433 を入力（例：emqx-test.database.windows.net:1433）。
   • データベース名：作成したデータベース名を入力（例：emqx）。
   • ユーザー名：username@FQDN 形式で入力。例：ユーザー名が sa、FQDN が emqx-test.database.windows.net の場合は sa@emqx-test.database.windows.net と入力。
   • パスワード：SQL Server アカウントのパスワードを入力。
   • SQL Server ドライバー名：EMQX Cloud にデフォルトでインストールされている ODBC Driver 18 for SQL Server を入力。
5. テスト ボタンをクリック。Microsoft SQL Server サービスにアクセスできれば成功メッセージが表示されます。
6. 新規 ボタンをクリックして作成を完了します。

ルールの作成

次に、書き込むデータを指定し、処理済みデータを Azure SQL Database に転送するアクションをルールに追加します。

1. ルールエリアの 新規ルール をクリック、または作成したコネクターの アクション 列にある新規ルールアイコンをクリックします。

2. SQL エディター にルールマッチング用の SQL 文を入力します。以下の例は、temp_hum/emqx トピックに送信されたメッセージから報告時間 up_timestamp、クライアント ID、メッセージ本文（ペイロード）から温度と湿度を抽出します。

    SELECT 
      timestamp as up_timestamp, 
      clientid as client_id, 
      payload.temp as temp,
      payload.hum as hum
    FROM
      "temp_hum/emqx"

   TIP

   初心者の方は、SQL Examples と Try It Out をクリックして SQL ルールを学習・テストしてください。

3. 次へ をクリックしてアクションを追加します。

4. コネクター ドロップダウンから先ほど作成したコネクターを選択します。

5. 使用する機能に基づき、SQL テンプレート を設定します。これは前処理済みの SQL なので、フィールドは引用符で囲まず、文末にセミコロンを書かないでください：

    INSERT INTO temp_hum(client_id, temp, hum)
    VALUES (
      ${client_id},
      ${temp},
      ${hum}
    )

   SQL テンプレート内でプレースホルダー変数が未定義の場合、SQL テンプレート 上部の Undefined Vars as Null スイッチでルールエンジンの動作を切り替えられます：

   • 無効（デフォルト）：ルールエンジンは文字列 undefined をデータベースに挿入します。

   • 有効：変数が未定義の場合、ルールエンジンは NULL を挿入します。

     TIP

     可能な限りこのオプションは有効にしてください。無効にするのは後方互換性を確保する場合のみです。

6. 詳細設定（任意）。

7. 確定 ボタンをクリックしてルール作成を完了します。

8. 新規ルール作成成功 ポップアップで ルールに戻る をクリックし、データ統合の設定チェーンを完了します。

ルールのテスト

MQTTX を使って温度・湿度データの報告をシミュレートすることを推奨しますが、他のクライアントでも構いません。

1. MQTTX でデプロイメントに接続し、以下のトピックにメッセージを送信します。

   • トピック：temp_hum/emqx

   • ペイロード：

     {
       "temp": "27.5",
       "hum": "41.8"
     }

2. メッセージが Azure SQL Database に転送されているか確認します。
3. コンソールで運用データを確認します。ルール一覧でルール ID をクリックすると、ルールの統計情報およびそのルール配下のすべてのアクションの統計が表示されます。