Deploy and Use MySQL

you can make the most of the massive amount of data collected by manufacturing connect edge by storing the data in a database for further analysis the manufacturing connect edge default marketplace includes a mysql application step 1 deploy the mysql application manufacturing connect edge 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 manufacturing connect edge 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 manufacturing connect edge 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 manufacturing connect edge 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 manufacturing connect edge, 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 manufacturing connect edge 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 docid\ q4jyt 6f9weaqu53lw3b , 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 {"success" true,"datatype" "int", "timestamp" 1531914109501, "registerid" "d22ac344 b908 481d aa7e a45e0041ac46", "value" 433, "deviceid" "45f95b1c f6f0 4696 a271 60fa1a5e79dd", "tagname" "ab 1"} to identify database table and column requirements in manufacturing connect edge, 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 from the node palette, drag the datahub subscribe node (dataconnector section) to the canvas 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 docid\ veeod3n3slcasdleedzbr to learn more click deploy drag the sidebar beneath the flow up and click the debug icon verify that messages are displaying 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 manufacturing connect edge local database for the purpose of this exercise, connect to the manufacturing connect edge 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 manufacturing connect edge device or you can use an external database on the same network as the manufacturing connect edge 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 manufacturing connect edge, 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 refer to the following tasks for nodes 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 manufacturing connect edge mysql database when using the marketplace application when using an external mysql database on the same network as the manufacturing connect edge 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 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 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 "{{}}"