Microsoft SQL ServerへのMQTTデータ取り込み
SQL Serverは、企業や組織で広く利用されている主要なリレーショナル商用データベースソリューションの一つです。EMQX CloudはSQL Serverとの統合をサポートしており、MQTTメッセージやクライアントイベントをSQL Serverに保存できます。これにより、データ管理や分析のための複雑なデータパイプラインや分析プロセスの構築、デバイス接続の管理、ERP、CRM、BIなどの他の企業システムとの連携が可能になります。
本ページでは、EMQX CloudとMicrosoft SQL Server間のデータ統合について、作成および検証の実践的な手順を含めて詳細に解説します。
動作概要
Microsoft SQL Serverとのデータ統合はEMQX Cloudの標準機能であり、EMQX Cloudのデバイス接続およびメッセージ送信機能とMicrosoft SQL Serverの強力なデータ保存機能を組み合わせています。組み込みのルールエンジンコンポーネントを通じて、MQTTメッセージやクライアントイベントをMicrosoft SQL Serverに保存できます。さらに、イベントによりMicrosoft SQL Server内のデータの更新や削除をトリガーし、デバイスのオンライン状態や接続履歴などの情報を記録することも可能です。この統合により、EMQX CloudからSQL Serverへのデータ取り込みが簡素化され、複雑なコーディングを必要としません。
以下の図は、EMQXとSQL Server間の典型的なデータ統合アーキテクチャを示しています。
Microsoft SQL ServerへのMQTTデータ取り込みの流れは以下の通りです。
- メッセージのパブリッシュと受信：産業用IoTデバイスはMQTTプロトコルを通じてEMQX Cloudに正常に接続し、機械、センサー、製品ラインの稼働状態や計測値、トリガーイベントに基づくリアルタイムMQTTデータをEMQX Cloudにパブリッシュします。EMQX Cloudはこれらのメッセージを受信すると、ルールエンジン内でマッチング処理を開始します。
- メッセージデータ処理：メッセージが到着すると、ルールエンジンを通過し、EMQX Cloudで定義されたルールにより処理されます。ルールは事前定義された条件に基づいて、どのメッセージをMicrosoft SQL Serverにルーティングするかを決定します。ペイロードの変換が指定されている場合は、データ形式の変換、特定情報のフィルタリング、追加コンテキストによるペイロードの拡充などが適用されます。
- SQL Serverへのデータ取り込み：ルールがメッセージのMicrosoft SQL Serverへの書き込みをトリガーします。SQLテンプレートを利用して、ルール処理結果からデータを抽出し、SQLを構築してSQL Serverに送信し、メッセージの特定フィールドを対応するデータベースのテーブルやカラムに書き込んだり更新したりします。
- データ保存と活用：Microsoft SQL Serverにデータが保存された後、企業はそのクエリ機能を活用して様々なユースケースに利用できます。
特長と利点
Microsoft SQL Serverとのデータ統合は、効率的なデータ送信、保存、活用を実現するための多彩な特長と利点を提供します。
- リアルタイムデータストリーミング：EMQX Cloudはリアルタイムデータストリームの処理に最適化されており、ソースシステムからMicrosoft SQL Serverへの効率的かつ信頼性の高いデータ送信を保証します。即時の洞察やアクションが必要なユースケースに理想的です。
- 高いパフォーマンスとスケーラビリティ：EMQX CloudとMicrosoft SQL Serverは共に拡張性と信頼性を備えており、大規模なIoTデータの処理に適しています。需要の増加に応じて水平・垂直の拡張を継続的に行い、IoTアプリケーションの継続性と信頼性を確保します。
- 柔軟なデータ変換：EMQX Cloudは強力なSQLベースのルールエンジンを提供し、Microsoft SQL Serverに保存する前にデータの前処理が可能です。フィルタリング、ルーティング、集約、拡充など多様なデータ変換機構をサポートし、組織のニーズに合わせてデータを整形できます。
- 高度な分析機能：Microsoft SQL ServerはAnalysis Servicesを用いた多次元データモデルの構築など強力な分析機能を提供し、複雑なデータ分析やデータマイニングを支援します。また、Reporting Servicesを通じてレポートの作成・公開が可能で、IoTデータの洞察や分析結果を関係者に提示できます。
はじめる前に
本節では、Microsoft SQL Serverデータ統合の作成を開始する前に必要な準備について説明します。Microsoft SQL Serverのインストールと接続、データベースおよびデータテーブルの作成、ODBCドライバーのインストールと設定方法を含みます。
前提条件
ネットワーク設定
データ統合を構成する前に、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ゲートウェイを構成してください。
Microsoft SQL Serverのインストールと接続
本節では、Dockerイメージを用いてLinux/MacOS上でMicrosoft SQL Server 2019を起動し、sqlcmdで接続する方法を説明します。その他のインストール方法については、Microsoft SQL Serverインストールガイドをご参照ください。
Dockerを用いてMicrosoft SQL Serverをインストールし、以下のコマンドでDockerイメージを起動します。パスワードは
mqtt_public1を使用します。Microsoft SQL Serverのパスワードポリシーについてはパスワードの複雑さをご覧ください。注意：環境変数
ACCEPT_EULA=Yを指定してDockerコンテナを起動することで、MicrosoftのEULAに同意したことになります。詳細はMICROSOFT SOFTWARE LICENSE TERMS MICROSOFT SQL SERVER 2019 STANDARD(EN_US)をご確認ください。bash# Microsoft SQL ServerのDockerイメージを起動し、パスワードを`mqtt_public1`に設定 $ docker run --name sqlserver -p 1433:1433 -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=mqtt_public1 -d mcr.microsoft.com/mssql/server:2019-CU19-ubuntu-20.04コンテナにアクセスします。
bashdocker exec -it sqlserver bashコンテナ内のサーバーに接続するために、設定したパスワードを入力します。パスワード入力時は文字が表示されません。入力後はそのまま
Enterを押してください。bash$ /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa $ Password: 1>TIP
Microsoftが提供するMicrosoft SQL Serverコンテナには
mssql-toolsがインストールされていますが、実行ファイルは$PATHに含まれていません。そのため、mssql-toolsの実行ファイルパスを指定する必要があります。本例のDocker環境ではパスは/optです。mssql-toolsの使い方についてはsqlcmdユーティリティをご参照ください。
これでMicrosoft SQL Server 2019インスタンスのデプロイが完了し、接続可能な状態になりました。
データベースおよびデータテーブルの作成
本節では、Microsoft SQL Serverでのデータベースおよびデータテーブルの作成方法を説明します。
前節で作成した接続を用いて、Microsoft SQL Serverに
emqxというデータベースを作成します。bash... Password: 1> USE master 2> GO Changed database context to 'master'. 1> CREATE DATABASE emqx; 2> GO以下のSQL文でデータテーブルを作成します。
デバイスから報告される温度と湿度のデータを保存するための
temp_humテーブルを作成します。sqlCREATE 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コネクターの作成
データ統合ルールを作成する前に、Microsoft SQL Serverにアクセスするためのコネクターを作成する必要があります。
デプロイメント画面に移動し、左側メニューからデータ統合をクリックします。
初めてコネクターを作成する場合は、データ永続化カテゴリの中からMicrosoft SQL serverを選択します。すでにコネクターを作成済みの場合は、新しいコネクターを選択し、続いてデータ永続化カテゴリの中からMicrosoft SQL serverを選択します。
コネクター名はシステムが自動生成します。
接続情報を入力します。
- サーバーホスト：サーバーのIPアドレスとポート番号
- データベース名：
emqxを入力 - ユーザー名：
saを入力 - パスワード：設定したパスワード
mqtt_public1または実際のパスワードを入力 - SQL Serverドライバー名：EMQX Cloudにデフォルトでインストールされている
ODBC Driver 18 for SQL Serverを入力
テストボタンをクリックし、Microsoft SQL Serverサービスにアクセスできる場合は成功メッセージが表示されます。
新規作成ボタンをクリックして作成を完了します。
ルールの作成
次に、書き込むデータを指定し、処理済みデータをMicrosoft SQL Serverに転送するためのアクションを追加するルールを作成します。
ルールエリアで新規ルールをクリックするか、作成したコネクターのアクション列にある新規ルールアイコンをクリックします。
SQLエディターにルールマッチング用のSQL文を入力します。以下のルールでは、メッセージの報告時間
up_timestamp、クライアントID、temp_hum/emqxトピックのペイロードから温度と湿度を読み取ります。sqlSELECT timestamp as up_timestamp, clientid as client_id, payload.temp as temp, payload.hum as hum FROM "temp_hum/emqx"TIP
初心者の方は、SQL例をクリックし、Try It OutでSQLルールを学習・テストできます。
次へをクリックしてアクションを追加します。
コネクターのドロップダウンから先ほど作成したコネクターを選択します。
利用する機能に応じてSQLテンプレートを設定します。注意：これは前処理済みのSQLなので、フィールドは引用符で囲まず、文末にセミコロンを付けないでください。
sqlINSERT INTO temp_hum(client_id, temp, hum) VALUES ( ${client_id}, ${temp}, ${hum} )SQLテンプレート内でプレースホルダー変数が未定義の場合、SQLテンプレート上部の未定義変数をNULLとして扱うスイッチでルールエンジンの動作を切り替えられます。
無効（デフォルト）：ルールエンジンは文字列
undefinedをデータベースに挿入します。有効：変数が未定義の場合、ルールエンジンは
NULLをデータベースに挿入します。TIP
可能な限りこのオプションは有効にしてください。無効にするのは後方互換性を保つ場合のみです。
詳細設定（任意）を行います。
確定ボタンをクリックしてルール作成を完了します。
新規ルール作成成功のポップアップでルールに戻るをクリックし、データ統合設定の一連の流れを完了します。
ルールのテスト
MQTTXを使って温度・湿度データの報告をシミュレートすることを推奨しますが、他の任意のクライアントでも構いません。
MQTTXでデプロイメントに接続し、以下のトピックにメッセージを送信します。
トピック：
temp_hum/emqxペイロード：
json{ "temp": "27.5", "hum": "41.8" }
メッセージがMicrosoft SQL Serverに転送されているか確認します。
bash1> SELECT * FROM temp_hum ORDER BY up_timestamp; 2> GO client_id temp hum up_timestamp ---------------------------------------------------------------- ------------ ------------ ----------------------- test_client 27.50 41.80 2024-03-25 05:49:21.237コンソールで運用データを確認します。ルール一覧のルールIDをクリックすると、ルールの統計情報およびそのルール配下の全アクションの統計情報を確認できます。