To achieve operational and business goals, many industrial automation system architectures are designed to push and pull data from a multitude of sources and destinations that come with a variety of formats. Bridging the gap between systems that use distinct standards and protocols can be challenging if the right tools aren't properly selected, and too often users resort to custom software. This is where users can leverage OPC Router’s ETL (Extract, Transform, Load) Tools—a valuable solution for seamlessly repackaging data across processes that use distinct standards or protocols.
In this article, we will briefly introduce the power of OPC Router’s ETL Tools, exploring a few use cases that highlight their role in structuring data into popular file formats. If you are not already familiar with what OPC Router is and what it can do for your particular process, you can visit the OPC Router website or the OPC Router Technical Guides with a Quick Start for New Users.
Extract, Transform, Load
ETL describes the process of consuming data from multiple sources (Extract), deriving the information needed from it (Transform), and outputting it to the relevant systems (Load). OPC Router’s modular configuration makes it effective middleware for assuming this role; and information can be restructured to accommodate each destination.
For consolidating data into text file formats, OPC Router offers the following transfer objects:
- JSON
- CSV
- XML
- Text Convert & Replace
- .NET C# Scripting
Each transfer object represents a module within OPC Router where some processing is performed. To show some of these in action, let’s go over some simple workflows that leverage these tools to push tags from an OPC server to different data destinations.
Example: JSON Payload to an MQTT Broker
The JSON Write object can be used to define a payload that can be published to a broker. The properties of the JSON Write allow you to input an example payload, from which item keys can be extracted.
Example: CSV Files to an FTP Drive
OPC Router has the option to write files to a local directory or to a network drive. This can be combined with ETL objects to generate logs in a specific file format— in the above case, a CSV file is updated with the latest values for the monitored items.
The CSV object can also generate fixed-width files, and alternative delimiters can be specified.
Example: XML Data to a REST API
Like the JSON Write, the XML/XPath object contains a text field to input an example format. XPath expressions are used to extract the items that values will be passed to.
In addition to the tools highlighted above, other transfer objects are provided for ETL operations relating to modifying data through arithmetic operations, filtering, ordering, scripting, and more. For more information about each Transfer Object, please take a look at the product’s help manual online, a link to which is provided when you install with OPC Router.
To start evaluating how OPC Router can securely improve efficiency and interoperability in your environment, you can download the free trial. Please do make sure to visit the resources section on OPC Router’s product page for information on how to use the product with other highly utilized standards.
As always, please contact us with any questions and be sure to subscribe to our blog for more tips on navigating features in OPC Router and our other software.
