Create a MySQL Database Table

Estimated reading time: 2 minutes

Use the instructions on this page to define a database table for the data you are collecting.

Recommendation

Use a tool, such as MySQL Workbench, to connect to the OT Link Platform 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 Flows, you can collect and parse the message payload to extract the device ID, device tag, and the register’s value (see Create a Flow to Populate a MySQL Database).

Examine the Message Payload

Before you create a database table, 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. 

{"success": true,"datatype": "int", "timestamp": 1531914109501, "registerId": "D22AC344-B908-481D-AA7E-A45E0041AC46", "value": 433, "deviceID": "45F95B1C-F6F0-4696-A271-60FA1A5E79DD", "tagName": "AB-1"}

To view the message payload, create a simple flow:

Configure the DataHub Subscribe node with a topic for a connected device. 

  1. In the OT Link Platform navigation panel, click Flows and then click Go to Flows.
  2. Drag the DataHub Subscribe node onto the canvas.
  3. Go to Devices > Tags and copy the raw topic for a tag associated with the connected device.

  4. Double-click the DataHub Subscribe node, paste the topic into the node, and click Done.

  5. Drag the Debug node onto the canvas, wire it to the DataHub Subscribe node, and click Save.

  6. Activate the Debug node and view the message in the Debug tab at the bottom of the window.

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 OT Link Platform local database.

For the purpose of this exercise, connect to the OT Link Platform database and create a database table with the following columns: deviceID, tagName, and tagValue. 

Next Step…

Follow the steps in: Create a Flow to Populate a MySQL Database.