[Device] OpcUa Extender Server upload MySQL database and Grafana
Posted: Tue Jun 04, 2024 8:11 am
Description:
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:
Environment:
Operating step:
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).
Also connect PM-3133 to working machine NIFE-105 with USB to RS485 adapter.
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:
Configuration of OpcUa Extender Server:
Upload the configuration .csv file to OpcUa Extender Server:
We will obtain the float format values for voltage, current, and power from addresses 34353, 34355, and 34357 respectively.
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:
Right click to add a new node:
Configure every node to the historical editor:
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
Step4:
Configure MySQL database:
Before uploading data to the database, database should be setup first.
Setup a new connection to the database in workbench.
Setup a password for the user uploaded data from the OpcUa extender server.
Goto "Server" > "User and Privileges"
Select or add an user for OpcUa Extender Server and setup a password.
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.
Now, MySQL database is ready to receive information from the power meter.
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"
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.
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".
Now, power meter data can now be visualized on a Grafana dashboard.
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 1236 times
- Power meter: PM-3133-100P
- RS485 to USB adapter
- Working machine: NIFE-105
- 硬體配置圖.png (85.68 KiB) Viewed 1236 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 1235 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 1235 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 1235 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 1234 times
Right click to add a new node:
- OpcUa Ext historical editor.png (11.4 KiB) Viewed 1234 times
Configure every node to the historical editor:
- OpcUa Ext historical editor2.png (64.68 KiB) Viewed 1233 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 1233 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 1231 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 1222 times
Select or add an user for OpcUa Extender Server and setup a password.
- MySQL user privileges password.png (230.15 KiB) Viewed 1222 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 1232 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 1222 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 1222 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 1221 times
Now, power meter data can now be visualized on a Grafana dashboard.