HaToDb - A demonstration on storing historical node data to a database
During the past years, we have received many requests from our customers for an introduction to data transfer from historical databases offering OPC UA Historical Access to their upper-level systems, such as MES, ERP and Big Data systems. This blog post will provide you with an overview of an implementation, along with code snippets demonstrating the crucial parts. We hope it will give you insight on how to create something similar suited to your needs using our SDK for Java product.
The picture above depicts the high-level implementation of the application.
Our underlying server on the left depicts a server containing nodes from which we wish to store values to a given database. It is achieved by creating an OPC UA client using the Prosys OPC SDK for Java, which retrieves values from the underlying server and stores them into the database.
The program flow of the application can be broken down into the following steps:
Read configuration file containing information, such as connection address of underlying OPC UA server, and the database.
Parse file containing NodeIds to know which nodes should be polled for value changes
Create necessary database tables and connection
Read all specified node values using UaClient and insert them into the database at a fixed rate.
To communicate with the underlying server, we instantiate the UaClient class and connect it to the given server address:
As for the database connection, we use the MariaDB jdbc Driver:
To read the nodes at a fixed rate, we create a Runnable function is called at specified rate:
readNodes(monitoredIds) goes through the list of NodeIds whose historical values are to be read and stored.
First, we want to make sure that the given node exists in the AddressSpace and remove it from the monitored nodes list in the case that it doesn’t:
Additionally, we need to make sure the node supports HistoryRead before attempting to fetch historical values.
To fetch a historical data for a variable, we need to define a timeframe between which values are fetched. By default, the timestamp of the most recent value available for a given node in the database is used as a starting point, and all values are fetched until the current moment. However, our implementation also allows the user to decide, whether results should be fetched from the last available database entry, or for example, from the last 10 seconds.
If no entries can be found for the given node, we fetch Historical results from the server based on a user-defined starting point.
Now that we have fetched all the values of the given node, their values can be added as entries into the database.
In this article, we took a look at how historical variable node values can be aggregated and stored from an underlying OPC UA server into a database, such as MariaDB. As we mentioned at the beginning, this topic has been a point of interest for many of our customers. We hope this blog post covered all the possible dark spots and answered occurred questions. Sections of the code snippets used in this post are part of our framework product. Our software services also constantly provide our customers with turn-key solutions based on our framework products. For any further information and any commercial questions, please contact our sales team.
Expertise and responsibility areas: OPC & OPC UA product development and project work
About Prosys OPC Ltd
Prosys OPC is a leading provider of professional OPC software and services with over 20 years of experience in the field. OPC and OPC UA (Unified Architecture) are communications standards used especially by industrial and high-tech companies.
Newest blog posts
The very first blog post about Prosys OPC UA Edge. The article introduces the main features and functionalities of the EDGE software.
How do the Windows DCOM hardening changes affect your OPC Classic applications.
Our input on the debate of the differences between OPC UA and MQTT and how actually there is competition.