little-y8763 / modbus-iot-hub-postgresql

An example of writing Modbus data into Azure IoT Hub and then storing it into PostgreSQL

Modbus 資料傳到 IoT-Hub 再存入 PostgreSQL

• 測試環境
• Install
• 範例

測試環境

Raspberry Pi 4 Model B 4GB

Install

以下操作在 Raspberry Pi 執行

開啟 Raspberry Pi terminal

檢查有沒有安裝 Node.js，有安裝的話會出現版本號

node -v

執行 node -v 出現 Command 'node' not found 表示沒有安裝 Node.js，執行以下指令安裝 Node.js

sudo apt-get install nodejs-legacy

檢查有沒有安裝 npm，有安裝的話會出現版本號

npm -v

執行 npm -v 出現 Command 'npm' not found 表示沒有安裝 npm，執行以下指令安裝 npm

sudo apt-get install npm

安裝 Node-RED

sudo npm install -g --unsafe-perm node-red node-red-admin 

啟動 Node-RED service

node-red-start

啟動後，打開瀏覽器輸入樹梅派 IP，Port 1880 就可以進入

http://xxx.xxx.xxx.xxx:1880

停止 Node-RED service

node-red-stop

假設部屬完後 service 掛掉，可以進入安全模式把有衝突或打錯的東西改掉再進行一次部屬(以建立到 2 個一樣 port 的 Modbus Server 為例)

node-red --safe

設定開機自動啟動 Node-RED service

sudo systemctl enable nodered.service

範例

1. 安裝必要套件

打開瀏覽器輸入樹梅派 IP，Port 1880 就可以進入 Node-RED

http://xxx.xxx.xxx.xxx:1880

進入 Node-RED 後，點擊畫面右上角 ，再點擊設置

開啟設置後，1. 點擊 Palette，2. 點擊安裝，3. 在搜尋列輸入 modbus，4. 找到 node-red-contrib-modbus 點擊安裝

在搜尋列輸入 iot-hub，找到 node-red-contrib-azure-iot-hub 點擊安裝

2. 匯入範例

在 Node-RED，點擊畫面右上角 ，再點擊匯入

複製以下 Json，貼入剪貼簿，再點擊匯入

[{"id":"d6f7e07434fae5c3","type":"tab","label":"F2","disabled":false,"info":"","env":[]},{"id":"f775e252.a49f2","type":"debug","z":"d6f7e07434fae5c3","name":"Log","active":true,"console":"false","complete":"true","x":1170,"y":280,"wires":[]},{"id":"817f33a3.ddf5f","type":"azureiothubreceiver","z":"d6f7e07434fae5c3","name":"Azure IoT Hub Receiver","x":880,"y":340,"wires":[["c2825fc8.d6323"]]},{"id":"c2825fc8.d6323","type":"debug","z":"d6f7e07434fae5c3","name":"Log","active":true,"console":"false","complete":"true","x":1170,"y":340,"wires":[]},{"id":"d934aff363c2ead2","type":"azureiothub","z":"d6f7e07434fae5c3","name":"Azure IoT Hub","protocol":"mqtt","x":900,"y":280,"wires":[["f775e252.a49f2"]]},{"id":"7527b5fdb64f6132","type":"function","z":"d6f7e07434fae5c3","name":"","func":"msg.payload = {\n\tdeviceId: \"test777\",\n    key: \"aUecqAb3OJ2CNak4MU5X9pGES2QCumQs2L0XEb2DNPM=\",\n    protocol: \"mqtt\",\n    data: {\n        a: msg.payload[0],\n        b: msg.payload[1],\n        c: msg.payload[2],\n        d: msg.payload[3],\n        e: msg.payload[4],\n        f: msg.payload[5],\n        g: msg.payload[6],\n        h: msg.payload[7],\n        i: msg.payload[8],\n        j: msg.payload[9]\n    }\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":710,"y":280,"wires":[["d934aff363c2ead2"]]},{"id":"53700dcf95a61fe6","type":"modbus-flex-getter","z":"d6f7e07434fae5c3","name":"","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"109d23776188deaa","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":510,"y":440,"wires":[["7527b5fdb64f6132"],["9478fddd2959d993"]]},{"id":"d0f7193a0650840e","type":"modbus-flex-write","z":"d6f7e07434fae5c3","name":"","showStatusActivities":false,"showErrors":false,"server":"109d23776188deaa","emptyMsgOnFail":false,"keepMsgProperties":false,"x":850,"y":520,"wires":[[],["b05172fdbc83e9af"]]},{"id":"cd0cdb6a22ed2a40","type":"function","z":"d6f7e07434fae5c3","name":"fc3 1[0~9]","func":"msg.payload = {\n    'fc': 3,\n    'unitid': 1,\n    'address': 0,\n    'quantity': 10\n};\nreturn msg;","outputs":1,"noerr":0,"x":320,"y":440,"wires":[["53700dcf95a61fe6"]]},{"id":"10f700938e9fb9f7","type":"inject","z":"d6f7e07434fae5c3","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"0.1","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":155,"y":440,"wires":[["cd0cdb6a22ed2a40"]]},{"id":"9478fddd2959d993","type":"modbus-response","z":"d6f7e07434fae5c3","name":"","registerShowMax":20,"x":740,"y":446,"wires":[]},{"id":"6b975682a2bbc647","type":"random","z":"d6f7e07434fae5c3","name":"","low":"0","high":"30","inte":"true","property":"payload","x":320,"y":520,"wires":[["0b45661d60566779"]]},{"id":"7cb838c943abe495","type":"function","z":"d6f7e07434fae5c3","name":"fc16 1[0~9]","func":"msg.payload = {\n    'value': msg.payload,\n    'fc': 16,\n    'unitid': 1,\n    'address': 0,\n    'quantity': 10\n};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":650,"y":520,"wires":[["d0f7193a0650840e"]]},{"id":"0b45661d60566779","type":"join","z":"d6f7e07434fae5c3","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"10","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":490,"y":520,"wires":[["7cb838c943abe495"]]},{"id":"1cd641c99f86a519","type":"inject","z":"d6f7e07434fae5c3","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"0.1","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":520,"wires":[["6b975682a2bbc647"]]},{"id":"b05172fdbc83e9af","type":"modbus-response","z":"d6f7e07434fae5c3","name":"","registerShowMax":20,"x":1070,"y":520,"wires":[]},{"id":"4ef41e123d23f75a","type":"modbus-server","z":"d6f7e07434fae5c3","name":"","logEnabled":false,"hostname":"0.0.0.0","serverPort":"7777","responseDelay":100,"delayUnit":"ms","coilsBufferSize":10000,"holdingBufferSize":10000,"inputBufferSize":10000,"discreteBufferSize":10000,"showErrors":false,"x":520,"y":160,"wires":[[],[],[],[],[]]},{"id":"109d23776188deaa","type":"modbus-client","name":"777","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"127.0.0.1","tcpPort":"7777","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","serialAsciiResponseStartDelimiter":"0x3A","unit_id":"1","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true}]

範例成功匯入

範例的 Modbus Server 以本地 port 7777 為例(可以按需求做更改)

3. IoT Hub 節點設定

IoT Hub 節點

function 節點要填入 IoT Hub device 的 deviceId 跟 device primary key

Azure IoT Hub 節點要填入 IoT Hub 的 hostname，使資料可以依前面 function 節點設定傳入 IoT Hub

Azure IoT Hub Receiver 節點要填入 IoT Hub 的 connection string，接收傳入 IoT Hub 的資料

4. Modbus 寫入功能

Modbus 寫入功能的節點

inject 節點，設定 0.1秒 poll 一次

random 節點，生成 0 到 30 的數字

join 節點，接前面 random 節點生成的數字，再以每 10 個數字組成一個陣列

function 節點，設定 Function Code、Slave Id、register 起始位址、資料筆數，Modbus Flex Write 節點接到前面 function 節點設定後將資料寫入(以寫入 0 到 9 號 register 為例)

5. Modbus 讀取功能

Modbus 讀取功能的節點

function 節點，設定 Function Code、Slave Id、register 起始位址、資料筆數，Modbus Flex Getter 節點接到前面 function 節點設定後讀取資料(以讀取 0 到 9 號 register 為例)

Modbus Flex Getter 節點連接 function 節點，把資料轉成 IoT Hub 節點要的格式，傳入 IoT Hub

點擊畫面右上角的 進入除錯窗口，可以看到資料成功寫入 IoT Hub，跟讀取剛寫入 IoT Hub 的資料

6. 用 Azure Function 把上面 Node-RED 寫入 IoT Hub 的資料存入 PostgreSQL

範例程式

    public void Run([IoTHubTrigger("messages/events", Connection = "Connectionstring")] EventData message, ILogger log)
    {
        try
        {
            string mes = $"{Encoding.UTF8.GetString(message.Body.Array).ToString()}";
            using (var db = new TestContext())
            {
                var test = new TestTable();
                test.Test = mes;
                db.Tests.Add(test);
                db.SaveChanges();
                log.LogInformation($"message: {Encoding.UTF8.GetString(message.Body.Array).ToString()}");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
    }

7. PostgreSQL Query 範例

顯示所有資料

SELECT * FROM PUBLIC."Tests";

顯示 Json Key "a" 的資料最小、最大、總和、平均值

SELECT 
   MIN (CAST (PUBLIC."Tests"."Test" -> 'a' AS INTEGER)),
   MAX (CAST (PUBLIC."Tests"."Test" -> 'a' AS INTEGER)),
   SUM (CAST (PUBLIC."Tests"."Test" -> 'a' AS INTEGER)),
   AVG (CAST (PUBLIC."Tests"."Test" -> 'a' AS INTEGER))
FROM PUBLIC."Tests";