Utilize the OpcUa extender Server to retrieve power meter data and upload it to the MySQL database, and then employ the data visualization platform Grafana to present the data stored in the database.
In this case, OpcUa extender serves as a Opcua server, which retrieve power meter information and transfer to MySQL database.
System block:
- Power meter_IoT Studio_OpcUa Extender Server_MySQL.png (61.86 KiB) Viewed 917 times
- Power meter: PM-3133-100P
- RS485 to USB adapter
- Working machine: NIFE-105
- 硬體配置圖.png (85.68 KiB) Viewed 917 times
Step1:
Hardware connection:
Install clip-on CT onto the power cable, and according to the user manual, connect the L and N terminals of the power meter in parallel with the corresponding terminals of the cable(1P2W-1CT).
- power meter wiring.png (22.5 KiB) Viewed 916 times
Step2:
OpcUa Extender Server retrievie power meter:
Look up the registers for voltage, current and power in the user manual, and configure the register addresses in the OpcUa Extender Server.
Modbus register of PM3133:
- Register.png (54.73 KiB) Viewed 916 times
Code: Select all
Va Va Powermeter_Rtu FLOAT 34353 1
Ia Ia Powermeter_Rtu FLOAT 34355 1
kWa kWa Powermeter_Rtu FLOAT 34357 1
- OpcUa Ext add modbus RTU_config file.png (47.2 KiB) Viewed 916 times
Step3:
OpcUa Extender Server historical setting:
The OpcUa extender uploads historical data for each individual node. Therefore, the configuration should be done for each node individually.
Shift to the "Historical" sheet and set the Historical configuration:
- OpcUa Ext historical.png (17.92 KiB) Viewed 915 times
Right click to add a new node:
- OpcUa Ext historical editor.png (11.4 KiB) Viewed 915 times
Configure every node to the historical editor:
- OpcUa Ext historical editor2.png (64.68 KiB) Viewed 914 times
p.s. NodeID can be found in the UaExpert in the redbox of the picture below. Follow step2 and step3 in posting.php?mode=edit&p=142 to see how to get the node information on UaExpert
- UaExpert get NodeID.png (217.08 KiB) Viewed 914 times
Step4:
Configure MySQL database:
Before uploading data to the database, database should be setup first.
Setup a new connection to the database in workbench.
- MySQL workbench setup new connection.png (45.76 KiB) Viewed 912 times
Setup a password for the user uploaded data from the OpcUa extender server.
Goto "Server" > "User and Privileges"
- MySQL user privileges.png (182.16 KiB) Viewed 903 times
Select or add an user for OpcUa Extender Server and setup a password.
- MySQL user privileges password.png (230.15 KiB) Viewed 903 times
Step5:
Connect OpcUa Extender Server to MySQL database:
Shift to the "Database" sheet, configure database setting.
Database: MySQL 8.x or MySQL 5.x
Hostname: (your database hostname)
Username: (your username)
Password: (your user password)
Default Schema: (the schema you want to upload)
Default Table: (the table in the schema you want to upload)
Click the "Test Connection" button to connect to MySQL.
Then click the "Init. Database" button to initialize the tables in MySQL.
p.s. You may go to viewtopic.php?t=108 for more detailed information to database connection of OpcUa extender server and IoT Studio.
- OpcUa Ext database setting.png (72.03 KiB) Viewed 913 times
Step6:
Visualize electricity meter data from the MySQL database on a Grafana dashboard.
Connect MySQL database to Grafana.
Goto "Connection" on the left option bar > find "MySQL" > "Add new data source"
- Grafana connect to MySQL.png (211.53 KiB) Viewed 903 times
Configure MySQL database connection.
Enter Host URL, Database name, Username and Password.
And click "Save & test". Message "Database Connection OK" will show if database connection succeed.
- Grafana connect to MySQL detail.png (179.71 KiB) Viewed 903 times
Add a dashboard on the Grafana.
Goto "Dashboard" on the left option bar > "Create Dashboard" > "Add visualization" > select the data source we just added "mysql".
- Grafana add dashboard.png (116.37 KiB) Viewed 902 times
Now, power meter data can now be visualized on a Grafana dashboard.