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