Use Node-RED to Send I/O Data to MS SQL

One of the most important aspects of any IOT application is the acquisition of data. Once we have the data we can historically log it, perform analysis like predictive maintenance and even apply machine learning to it. But first how do we get the numerous sensors, program variables, and other data points acquired and stored quickly, efficiently and simply. Well in this workshop I'll show you how to get physical sensor and program data from a SNAP PAC controller and log it to a sequel database using Node-RED running on a groov AR1. My data is going to come from this SNAP PAC Learning Center. It's basically a SNAP PAC system with a controller, several i/o modules and a rack. We use it for training here at Opto 22. This front panel simulates some typical i/o signals like a meter, temperature sensor, potentiometer, LEDs, switches, and a buzzer. The controller is running a convenience-store strategy that's loaded into its memory. This is a groov AR1 box. It's an industrially hardened IoT appliance that is running both groov and the Node-RED instance I'll use to move my data. I'm going to use the AR1 to bring these physical I/O points into Node-RED and regularly log the fuel level and temperature in a time series data table in a database named 'workshops' running on a Microsoft SQL server up on the cloud on Amazon Web Services. I will also log the emergency alarm and freezer door state changes in another data table. I already have the signals for these data points wired to the I/O modules mounted on the rack and have created tags for them in the strategy running in the memory on the controller. So now we'll head over to Node-RED and get started. Here you can see I already have the store temperature inserts set up. My flow, every 10 seconds, reads in the store temperature and then inserts it into a time series data table in the workshops database. I also have an emergency alarm state insert that only inserts data when the emergency switch is toggled. I'll add one more I/O point to each table starting with the fuel level to the time series table. I want to use the same ten-second flow path, so, check out this temperature read node here. I already have my controller set up and that's as simple as having an API key from my controller's admin keys page. You can find instructions on how to set this up on developer.opto22.com. I'm using this node to read in the analog input store temperature and put it into the payload store temperature message property. So now I'm going to bring in a new SNAP PAC read node, drop it into my flow, and edit it to use the same controller as before. But, this time I'm going to be getting the analog input called fuel level. This one can go into the value message dot payload fuel level. Note that I made sure to put the fuel and temperature on different message properties so that I don't need to worry about one overriding the other. My flow can pick up this extra data on the way to the database. So I'll just drag it onto the wire between temperature and the MS SQL node. Now Node-RED gets and stores the temperature and the fuel level every 10 seconds and then inserts them into the database. Here is where the database query is made using the transact-sql or T-SQL language. What I'm going to do here is start by declaring the fuel level as a new float. So now, just like temperature, I can set that fuel level to be the unchanged variable payload fuel level. The triple curly-braces do this using mustache formatting which you can find out more about using this link here. Once I have the variable I can insert it into the convenience store time series table called C store data in the workshops database. The column name there is fuel level and the tag I'm handing in is at fuel level that I've just created. To set up the database that this table resides in click the pencil next to connection. In this window you can name the connection, supply the server details, your credentials, and the database name. I am using a database named workshops on Amazon Web Services and logging in with my developer username and password. Once you have all this set up you're ready to select and insert from the table. I'll save this by hitting done but before I deploy the flow I'll add the state data from the freezer door switch. State data is done a little differently to time series data. Instead of regularly reporting the value I constantly scan the status and only let the flow continue when it changes. Once I know the emergency switch has been toggled, I go into a change node where I turn the true-false value of the switch and replace it with a string for a normal or alert status and then that is inserted into the table. To get another piece of data I will need another SNAP PAC read node. So, I'll drag one in and double-click to edit it. Here I'm going to use the same controller and this time I will use a digital input. This one is going to be called 'freezer door'. Since the door is separate from the alarm I won't be getting at the same time as the emergency state so it goes on its own path and can be written to msg.payload. To make sure the flow only continues when the switch is toggled I'll go down to the function section and I'll bring in an RBE for report by exception. I'll also edit it to ignore the initial value so it only reports when it's toggled and not when I just deploy the flow. Now I have the true /false value from the freezer door. But that's not very descriptive so, like emergency, I'll bring in a change node. Here I'm going to add some rules to edit the strings that are in payload. I'm going to change the message dot payload and search for the boolean value true and I'm going to replace that with open. Then I can add another rule. And then I'm also going to change msg.payload and search for the boolean value false and then I'll replace that with closed. Now we'll make a new insert query. For that, I'm going to go down to the storage section and use an MS SQL node to add another statement. I'm still accessing the workshops database but this time I am inserting my freezer state. This time instead of the number being a float I will declare a new string or 'vachar' I set this new varchar to hold the value in payload. Since it's a string I put quotes around it. Now that my variable is ready I can insert it into my c-store state table which is different from the C-store data table that holds my time series data. The columns that I'm writing to are: freezer door and time stamp. The values that go into those columns are my new freezer door varchar and the current time stamp. And that's all there is to it. This insert statement is totally done now. Every 10 seconds Node-RED checks the store temperature and fuel level and pushes them into the time series data table in the workshops database. Also once every second checks the emergency alarm and freezer door states and when one of them is flipped it converts the true/false into a status and writes that to the table when the change is made. So now I'll deploy and head over to Microsoft SQLl server to see the data appearing in my database. Here I can connect to my server and bring open a select query that I already made. This one will get the 25 latest entries in the time series data table. When I execute this select I can see the data appear here. If I make a change to my fuel level, wait for Node-RED to insert the data, and then execute the query once more I'll see that my fuel level has changed. Now I can bring open another query to see my data series. When I open that and execute I can see all the state changes that have occurred. if I switch my freezer door and open it I can execute this script and I can see that the open event has been logged. Now if I close the door and execute the script again I see that it's closed. So there you have it I'm building up historical data from the SNAP PAC I/O and now it's stored ready to analyze with artificial intelligence or feed into any other service that I want. Now that I've shown you how to do it you can freely add it to any or all of your own applications. Thanks for watching!

Channel: Opto Video

Views: 67,472

Keywords: automation, opto 22, opto, opto22, automatizacion y control industrial, node-red, node red tutorial, nodered tutorial, node-red tutorial, node-red tutorial for beginners, intro to node-red, pac, snap pac, pac controller, ms sql, sql, sql data base, node-red to ms sql, how to, tutorial, iot application, iot, iiot, iot data, iot data to database, iot data to MS SQL, aws, amazon web services, cloud, amazon cloud, ms sql on amazon, groov, groov ar1, ar1, microsoft

Id: cO8QpyUnEwg

Length: 9min 52sec (592 seconds)

Published: Wed Nov 15 2017