WindowsのNode-REDに、「MQTT→SQL Serverへinsert」させる （全３回：第３回　MQTTメッセージ→SQL Serverへinsertのフローを作成）

製造業向けIoT担当です。

製造業界隈によくある環境（Windows＆SQL Server）で、MQTTメッセージ→SQL Serverへinsertの動作確認ができるまでをセットアップしてみます。

最後は、実際のNode-REDを使って、MQTTメッセージの送信／受信、受信したデータのデータベース登録を行います。

 

まとめ（手順サマリ）

第１回　Node-REDのサービス化

1.PackageManagementのインストール（パッケージ管理環境を整えます。その１）
2.Chocolateyのインストール（パッケージ管理環境を整えます。その２）
3.nssmのインストール（サービス化ツール）
4.Node-REDのインストール
5.nssmで、Node-REDのサービス化

第２回　MQTTメッセージブローカーの設定

1.Erlangのインストール（RabbitMQの動作用）
2.RabbitMQのインストール（メッセージミドルウェア）とMQTTブローカ追加

第３回　MQTTメッセージ→SQL Serverへinsertのフローを作成（今回）

1.Node-REDのパレットに、MSSQLノードを追加
2.フローを作成

 

１．Node-REDのパレットに、MSSQLノードを追加

右上の「≡」マークをクリック、「パレットの管理」を選択します。

「ノードの追加」タブで、「node-red-contrib-mssql」を検索します。

同名の候補を見つけ、「ノードを追加」をクリックして、完了したら「閉じる」で、元の画面戻ります。

（上記の図は、追加済みです。）

左ペインの「ストレージ」に、「MSSQL」ノードが追加されています。

２. フローを作成

ステップバイステップで、進めてみます。

2.1.  MQTTメッセージの受け取り

入力から、「inject」をフローにドラック＆ドロップします。

ドロップしたノードをダブルクリックして、ノードの設定を行います。

ペイロードに「文字列」を選択し、任意の文字列を入力。「設備からメッセージ（デバッグ）」としておきます。

他は、何も入力せず、「完了」をクリックします。

次は、出力の「mqtt」をドラック＆ドロップします。先ほどのinjectノードの右隣りがいいでしょう。

ドロップしたノードをダブルクリックして、ノードの設定を行います。

サーバの右にある鉛筆マークで、mqttサーバの指定を行います。

mqtt-brakerノードの設定が開くので、設定します。

RabbitMQのmqttプラグインの追加だけの行っているため、セキュリティタブの「ユーザ」「パスワード」は、何も入力しません。

接続タブの下記以外、すべて、デフォルト、または、未入力にします。

サーバ：「localhost」、ポート：「1883」

入力したら、「完了」（下図は「更新」になっています）をクリックして、mqtt out ノードの編集画面に戻ります。

mqtt out ノード編集に戻り、下記を入力して、「完了」をクリックします。

トピック：「for_demo」

QoS：2 （QoS2＝正確に1回）

injectノードとmqtt outノードを繋げます。

接続部にマウスが乗ると色が変わるので、変わったところでクリックしたまま、繋げ先の接続部にドラッグします。

これで、送信部品が出来上がりました。

↓

次は、受信部品を作ってみます。

（自分で、自分にmqtt送信する仕掛けになります。）

入力の「mqtt」をドラック＆ドロップします。ドロップしたノードをダブルクリックして、ノードの設定を行います。

サーバ欄とQoS欄は、デフォルトで、先ほどのmqtt out ノードの設定になります。

トピックをmqtt out ノードと同じ（「for_demo」）にして、「完了」をクリックします。

受信できたか確認するため、デバッグ部品を繋げます。

出力の「debug」をドラック＆ドロップします。mqtt outノードの結果を表示するので、mqtt outノードの右隣りに置きます。

並べたら、ノードを繋げて完了です。

↓

右上の「デプロイ」があるので、クリックするか、「変更したフロー」を選択します。

上中央に、デプロイの成功メッセージが表示されます。

右のペインの「デバッグ」タブを開いて、デバッグメッセージを表示する準備を行います。

初めに用意したinjectノードの左端のボタンをクリックすると、送信・受信が確認できます。

injectのペイロードは、mqtt通信を通して、debugノードで表示されることを確認できます。

（事前準備）SQL Serverにデモ用テーブルを登録します。

ココでは、以下のテーブルを登録しました。

下記は、適宜、実施する環境で変更ください。

データベース：＜データベース名＞

テーブル名：W999_WORK_MQTTDATA

CREATE TABLE ＜データベース名＞.dbo.W999_WORK_MQTTDATA

(

Topic      nvarchar(255)    NOT NULL

, Payload    nvarchar(1000)   NOT NULL

, Timestamp  datetime2        NOT NULL

, CONSTRAINT PK_W999_WORK_MQTTDATA

PRIMARY KEY CLUSTERED (Topic, Timestamp)

);

2.2. 受け取ったメッセージのINSERT文の作成

「機能」の「functon」をフローにドラッグ＆ドロップします。

ドラッグしたノードをダブルクリックします。

名前：insert

コード：以下を張り付けてください。

d = new Date(),

dformat = [d.getMonth()+1,

d.getDate(),

d.getFullYear()].join(‘/’)+’ ‘+

[d.getHours(),

d.getMinutes(),

d.getSeconds()].join(‘:’);

 

pld =       “INSERT INTO [dbo].[W999_WORK_MQTTDATA] ”

pld = pld + “(Topic, Payload, Timestamp) ”

pld = pld + “VALUES (‘” + msg.topic + “‘, ‘” + msg.payload + “‘, ‘” + dformat + “‘)”

 

msg.topic = ”

msg.payload = pld

return msg;

 

他は、変更せず、完了します。

 

先に作っておいたMQTT受信ノードと結合します。

 

気になる方は、今作ったfunctionノードにデバッグノードを繋げてもいいです。

デプロイして、injectノードのボタンを押すと、デバッグにINSERT文が出力されます。

 

↓こうなります。

2.3. INSERTの実行

SQL Serverに接続して、先ほどのINSERTを実行します。

ストレージから、MSSQLノードを選択して、フローにドラッグ＆ドロップします。

配置したMSSQLをダブルクリックして、設定画面を表示します。

Connectionの右にある鉛筆マークボタンで、DBへの接続設定を行ってください。

設定が完了したら、右上にある「追加（下記の図は更新）」ボタンを押下ください。

Name：任意の名称

Server：接続先サーバ

Username：接続するDBへのユーザ名

Password：接続するDBへのユーザパスワード

Domain：（今回は、設定しません。）

Database：接続するデータベース名

 

元のMSSQLノードの設定画面に戻ります。

Connectionに、先に設定した接続情報の名称が選択できるので、ConnectionとNameを設定したら、「完了」してください。

最後に、フローのINSERT文を設定した「function」ノードと今作ったMSSQLノードを接続します。

フローの全体は、↓のようになります。

再び、右上にある「デプロイ」を押下して、このフローを動作反映させます。

初めに用意したinjectノードの左端のボタンをクリックすると、送信・受信が確認できます。

データベースに登録されたことが確認ください。

（下記は、A5:SQL Mk-2（https://a5m2.mmatsubara.com/）で参照しました。）

 

３回にわたって、「MQTT→SQL Serverへinsert」させるまでの環境を作成しました。

 

例えば、工場の事務所のノートPCを利用することで、エッジコンピューティングの実証実験が行えると思います。

 

【参考】
第１回　Node-REDのサービス化
第２回　MQTTメッセージブローカーの設定
第３回　MQTTメッセージ→SQL Serverへinsertのフローを作成

 

ホームページ　http://www.ois-yokohama.co.jp

facebook　　　https://www.facebook.com/orientalinformationservice/