Deploy and Use MySQL
You can make the most of the massive amount of data collected by Belden Horizon Data Operations by storing the data in a database for further analysis. The Belden Horizon Data Operations default Marketplace includes a MySQL application.
Step 1: Deploy the MySQL Application
Belden Horizon Data Operations provides a variety of applications in its default marketplace catalog. In the case of MySQL, a container-based MySQL database is provided, which is local to the Belden Horizon Data Operations device. Data collected at the edge can be stored in this local database. In addition, you can extract data from the local database for consumption by other databases in your enterprise.
Caution:
- Caution should be used when using this local database. If the size exceeds the hardware memory size, it crashes the Belden Horizon Data Operations system, causing data to be lost and requiring a fresh installation.
- To manage the size of the local database, we recommend weekly backups and management of local data by using scripts.
- As an alternative to the local database, collect data and send it to an external database that is on the same network as the Belden Horizon Data Operations device.
Before You Begin
Internet connectivity is required to deploy the Default Public Marketplace and to deploy applications. Once you create an application instance, you no longer need internet access to create additional instances of the same application.
To deploy the MySQL application:
- In Belden Horizon Data Operations, navigate to Applications > Marketplace.
- Click the MySQL tile.
- The MySQL Launch dialog box appears.
- From the Installation script version drop-down list, select latest. The MySQL form appears.
- Configure the following parameters.
- Name
- Description (Optional)
- Port: Enter a port. The default value is 3306.
- Database: Enter a database name.
- User: Enter a user name.
- Password: Enter a MySQL password.
- MySQL password: Enter a MYSQL root password.
- Restart: Configure the restart setting. Enter no, always, or on-failure.
- Click Launch. The MySQL application appears as a tile in the Overview pane.
Step 2: Create a MySQL Database Table
You will now need to define a database table for the data you're collecting.
You can use a tool, such as MySQL Workbench, to connect to the Belden Horizon Data Operations local database to create a table.
Identify Database Table and Column Requirements
The columns and data types required for a database table depend on a device tag's configuration. For this exercise, a basic database table illustrates data that can be saved to the database. Using the Flows Manager, you can collect and parse the message payload to extract the device ID, device tag, and the register's value.
Before You Begin
- You will need to configure the DataHub Subscribe node with a topic for a connected device. Navigate to DeviceHub > Tags, select a device, and copy the RAW Topic for the tag you want to use.
- You need to know the message format. In the following example, you can see the field names and values that you may want to save in a database.
To identify database table and column requirements:
- In Belden Horizon Data Operations, navigate to Flows Manager.
Click the Go To Flow Definition icon for a selected Flows Manager. The Flow canvas opens in a new browser tab.
Go To Flow Definition iconFrom the node palette, drag the DataHub Subscribe node (DataConnector section) to the canvas.
DataHub Subscribe node- Drag the Debug node to the canvas and connect the two nodes.
- Double click the DataHub Subscribe node. The Edit DataHub Subscribe Node dialog box appears.
- In the Topic field, paste the topic name you previously copied and click Done.
- If needed, configure the Datahub Subscribe connection. See the "Step 3: Configure Connector Nodes" section in Create a Flow to learn more.
- Click Deploy.
Drag the Sidebar beneath the flow up and click the Debug icon. Verify that messages are displaying.
Debug node messages
Create a Database Table
While table creation can be accomplished by writing SQL statements in a flow, the preferred method uses a database tool, such as MySQL Workbench, to create the table and columns in the Belden Horizon Data Operations local database.
For the purpose of this exercise, connect to the Belden Horizon Data Operations database and create a database table with the following columns: deviceID, tagName, and tagValue.
Create Flow to Populate a MySQL Database
You can create another flow to collect data and store it in a MySQL database.
Note: You can access the MySQL database located on the Belden Horizon Data Operations device or you can use an external database on the same network as the Belden Horizon Data Operations device.
Refer to the flow below that you will create.
Before You Begin
- You will need to configure the DataHub Subscribe node with a topic for a connected device. Navigate to DeviceHub > Tags, select a device, and copy the RAW Topic for the tag you want to use.
- You must have experience working with flows and SQL queries.
To create a flow to populate a MySQL database:
- In Belden Horizon Data Operations, navigate to Flows Manager.
Click the Go To Flow Definition icon for a selected Flows Manager. The Flow canvas opens in a new browser tab.
Go To Flow Definition icon
DataHub Subscribe and JSON Nodes
After configuring the DataHub Subscribe node with a topic from a connected device, it collects the message. The JSON node processes the message to ensure that it is in the proper JSON format required for further processing.
- Drag the DataHub Subscribe and JSON nodes onto the canvas and connect both nodes.
- Double-click the DataHub Subscribe node. The Edit DataHub Subscribe Node dialog box appears.
- In the Topic field, paste the topic name you previously copied and click Done.
- Click Deploy.
Function Node
The Function node parses the incoming message.
- Drag the Function node onto the canvas and connect it to the JSON node.
- Double-click the Function node and enter the JavaScript code below in the On Message tab. This parses the message payload.
- Click Deploy.
var obj = {};
obj.deviceID = msg.payload.deviceID;
obj.tagName = msg.payload.tagName;
obj.value = msg.payload.tagValue;
msg.payload = obj;
return msg;Template Node
Use the Template node to write SQL statements to insert data into the database columns.
- Drag the Template node onto the canvas and connect it to the Function node.
- Double-click the Template node.
- In the Property field, select msg. and enter topic.
- Enter the SQL statement below in the Template window.
- Click Deploy.
Note: In the example statement below, test represents the name of the database table, which you need to create. The table doesn't exist by default.
INSERT INTO test (deviceID,tagName,tagValue) VALUES
('{{payload.deviceID}}','{{payload.tagName}}','{{payload.tagValue}}');MySQL Node
Configure this node with a port and credentials to connect to the local Belden Horizon Data Operations MySQL database when using the Marketplace application. When using an external MySQL database on the same network as the Belden Horizon Data Operations device, configure the MySQL node with the IP address, port, and credentials for that database server.
- Drag the MySQL node onto the canvas and connect it to the Template node.
- Double-click the MySQL node.
- In the Database field, click the Edit icon.
- Configure the following parameters.
- Host: 127.0.0.1
- Port: 3306
- User: root
- Password: Enter the MySQL database password
- Database: Enter the name of the database
Debug Node
Verify your flow is working as designed.
- Drag the Debug node onto the canvas and connect it to the MySQL node.
Drag the Sidebar beneath the flow up and click the Debug icon. Verify that messages are displaying.
Debug node messages
Validate Database Updates
To check that collected values are being inserted into the MySQL database, create a basic flow.
Refer to the updates to the flow created in the previous steps.
To validate database updates:
- From the flow canvas you used to create the previous flow, copy and paste the Template node previously created and connect it to the Function node.
- Double-click the Template node.
- Enter the following SQL statement in the Template window and click Done. select * from test;
- Click Deploy.
- Copy and paste the MySQL node previously created and connect it to the newer Template node.
- Click Deploy.
- Drag a new Debug node onto the canvas and connect it to the newer MySQL node.
Drag the Sidebar beneath the flow up and click the Debug icon. Verify that messages are displaying.
Debug node messages
Step 3: Save Multiple Registers to MySQL
You will now update the original template node on the flow canvas to do the following:
- Collect raw data from various PLC registers.
- Aggregate the data at regular intervals.
- Save the aggregated data for use by other applications.
Use the Template node to format the SQL statement to update the database with values that have been collected from multiple registers.
To save multiple registers to MySQL:
- From the same flow canvas you used to create and update flows, double-click the original Template node.
- Enter the following SQL statement in the Template window and click Done.
INSERT INTO mytable(value1,value2,value3,value4) values
({{output1}},{{output2}},"{{output3}}","{{output4}}");Note: Depending on the MYSQL column types, certain formatting may be required. For example, if a column is VARCHAR, the following notation must be used: "{{}}".