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!