In this video we use the SCADA Software Ignition to add data trending using a tool called a Historian, and then write a script to store data in SQL after running through a sequence!
A PLC or Programmable Logic Controller is the staple of industrial automation and controls, and Arduino has made one of the most affordable ways to get into the industry! This micro-PLC has Relay outputs, and programmable digital or analog input and output channels as well.
It also supports Modbus OPC communications for easy integration into SCADAs or larger controls systems, RJ45 ethernet communications and WI-FI connectivity! Additionally there are no licensing fees to use the Arduino PLC IDE where you can program in Ladder, Structured Text, FBD, SFC or using the regular Arduino IDE/C++ based language!
Ignition for Makers puts an incredibly powerful, next generation SCADA software available for makers and hobbyists and learners to start developing industrial automation and controls skills for free!
Don't forget to leave a like, subscribe to the channel, and comment what you want to see next!
Hello and welcome back to L Master Tech.
And in today's video, we're going to
continue our controls and automation
tutorial series by introducing the
concept of a historian or a continuous
trending tool as well as SQL, which is
kind of a more conventional database
tool. So, by far these are the two most
common ways that data gets represented
in the world of industrial automation
and controls. SQL SQL stands for
structured query language. There are
ample tutorials on the internet on how
to get started with SQL but specifically
how to embed it within like a controls
and automation project is very much like
an automation concept. Uh and then
historians are sort of the standard for
continuous trending of automation data.
So if you are just watching this video
on its own uh it will stand alone as a
tutorial on how to use both the
historian tool and create a SQL uh query
from ignition which I think is useful on
its own. But if you've been following
along with the tutorial series, you know
that we physically have this Arduino
Opta PLC. Uh we have the button and the
blue light and the green light and the
red light. Uh all all tied to the HMI on
ignition and then as well as input 8
there in the top right corner which I
have a physical hand switch for. So when
I make the hand switch, we can see that
input 8 goes on. So, in the last uh
video, we kind of introduced sequential
programming and we made this sequence
where we could start uh we could wait to
see that hand switch go high and then uh
we start a timer that's going to count
to a set point that we can adjust and
then we need to click this button until
we get to a target value and then we
press stop. Okay, so I have a sequence.
Uh if you don't want to follow along
exactly with my example, that's okay.
Um, but you should have something
sequential that you want to track if you
want to this to be like a practical
hands-on example for you. Um, so we're
using Ignition for Makers, which is a
very popular SCADA supervisory control
and data acquisition software. We're
using Ignition Maker Edition, which is
which is an awesome free tool uh that
inductive automation put out. We have a
basic HMI project that shows some of our
tags and uh then we have the Arduino opt
sharing data with Modbus TCP data. So
we're going to go into the designer of
ignition and we're going to take a look
first at a very simple continuous trend.
Um so one thing to note is when you
start storing data when you start
looking at data history over time you
need some version of a database to store
that in. One really nice feature of
Ignition for makers and the uh
industrial version of it is uh it'll by
default have a sample SQL light database
enabled and it'll use some of the
storage of your computer uh by default
because SQLite is just a file. So it's a
way of storing data that looks like you
know Microsoft SQL or any other sort of
like table and columns and rows version
of uh uh SQL but SQLite is just a file.
So by default, we don't have to do
anything special in Ignition. We have
one connection to a database and it's a
sample SQLite database.
That's really important because anytime
you want to store data, you have to
store it somewhere. Okay. Now, let's go
ahead and just pull up the uh HMI that
we were developing before. It's kind of
just a little square here. Um, and I had
it mode fixed. So, what I'm going to do
is zoom out a little bit and widen it to
be a little closer to the width of the
screen. Uh, I'm going to hit save. And
then I'm going to do one thing here. I'm
going to change mode from fixed to
percent. So now it should scale with the
screen. Before, uh, we did that. If we
were, uh, zooming in and zooming out,
all those objects would also be zooming
in and out. But now it's scaled to
always be full screen. So that's just a
little feature of Ignition in
particular. Um, but that also means if
we want to add a trend over here, uh, we
can. So, if we hit the perspective
components uh pallet over on the right,
um I'm just going to grab a
power chart and I'm going to drag it
onto the screen. And what's really cool
is you don't have to tell it what tag to
look at or anything. You just drag this
powers chart onto the screen and
Ignition has a pre-built object that
will let you browse tags within that.
So, just by hitting save and going back
to the web page, you can see this chart
is on screen. The only problem is
there's only example data in here. So I
have uh I have tags that I added before
for testing and then they give you these
sample tags of realistic values to show
you what navigating it looks like. Um
but since in the designer we haven't
added historical data to any tags um
we're not seeing anything. Okay. So if
you've been following along with the
tutorials you should have the same tags
but if you don't that's okay. Um, I
don't have data trending enabled on any
of these tags. So, let's grab a few that
we might want to historiize. So,
sequence step is a good one to start
with as an example. If I want to see the
sequence step over time, that would tell
me where in the sequence it was over
time. Um, and all I have to do when I
open the tag editor is go to history and
then true. And then I need to give it a
storage provider, which in this case
will make that sample SQL light
database. And then there's a few
important things here. There's uh
deadbands, there's deadband styles. You
can leave most of these on the default
and it'll just start working if all you
do is enable history and give it a
storage provider and hit apply. Um, it's
important, I think, to call out onchange
versus periodic because onchange by
default will only store new values when
the value changes. That's kind of like a
memory optimization, but you can tell it
no matter what, I want uh it to store at
a certain periodic interval as well. So,
we'll leave it as onchange and I'm going
to hit apply and okay. And so, now it
should have history enabled. And let's
go to that web page. And let's open the
tag browser. And let's browse through
this uh to default and then sequence
tags and then sequence step. And let's
add that selected tag here. So, if I
collapse the tag browser, what you can
hopefully see, although it might be hard
on the recording and we can click this
X-race tool, which tells us the value,
we can see that sequence step is a
constant zero. And if I change my real
time uh value to show the last five
minutes, and hit apply, and you can
always edit that here in the set date
range.
But if I show the last five minutes, uh
now let's go ahead and start that
sequence. and I hit start and it should
go to step one. And you can see this
trend jumped right to one in real time.
Now it's waiting to see input 8
transition high. So I'll go ahead and
turn that hand switch. And you can see
the value jumps up to two right away.
Now it hits our 5-second timer and it
jumps up to three. Now it's waiting for
the pushes to reach the set point. One,
two, three. Should jump up to five. And
then if I hit the stop button,
the sequence step goes back to zero. So
it's really cool. You can see just right
there us stepping through the sequence.
So if I were to go to uh time set point
10 now, and we did it one more time kind
of as fast as we can here. Hit start. Uh
turn the set point on.
And then it's going to take 10 seconds
and then we count up to three.
And then we hit stop.
But what's cool is you can see between
those two runs, step two, which is the
timebased step, was 5 seconds here and
was 10 seconds here. And we can narrow
that window as well. We can say last 2
minutes. And you get a more precise uh
you get a more precise graph going.
Okay. So that's going to do it for our
very basic introduction to the historian
because that is objectively the simpler
of the data tracking tools that we're
trying to cover today. Um, kind of like
I said in the outset, when you just want
to see processed data continuously over
time, a historian tool is great for that
and is plenty to do that. But when you
want to create a more um a more, I
guess, logical data story over time, and
you want to tie what time I started,
what time I stopped with, you know, what
were these settings at that time, who
was doing that work, um, that's usually
when you want to go to something like a
SQL database. And uh like I said, we
have a um SQL light already here in
Ignition. Again, if you're not using the
ignition platform, I guarantee your
SCADA supports some way of interfacing
with a database like a SQL tool. Um you
just have to do a little reading on how
to connect insert name of your SCADA or
HMI platform to SQL and I guarantee
someone's done it. All right. So, from
the ignition designer, I'm going to go
ahead and go to the database query
browser. And I previously had um a
sequence data table while I was testing.
I'm going to go ahead and delete that
real quick with this drop table command
so that we can rebuild it from scratch.
So, I had sequence data. I just deleted
it there. Um so, you guys can follow the
exact steps that I do. So, I'm going to
kind of think about our sequence real
quick and the things that I would want
to add to capture how the sequence was
running. So, I would want to capture the
time it started. I'll call that start
time. I would want to capture end time.
I want to capture what the time delay
set point was and the count set point
were. Um, and then potentially a
username if someone was logged in. Um,
and then maybe like a run count, an ID
that says, hey, this was run 1 2 3 4 5
6. And it just keeps incrementing up.
Okay. So, I don't have all of those
variables defined yet, but I have many
of them. We're not going to worry about
the tags existing yet. We're going to
make that table first. So, let's go back
into our tools and let's go into our
database query browser and let's create
a table in the query browser that's
going to have that data that we want.
So, to do this, we're going to say
create table. And at this point, you
guys have probably seen Ignition can use
Python in some places. It has specific
ignition syntax for certain things. This
is SQL syntax. and in particular SQL
light, but all uh SQL is pretty similar
if you know what you're looking at. So,
we're going to create a table. We'll
call it sequence data, and we'll expand
these parenthesis out. And we'll have a
field called run count, and we'll make
that an integer, and we'll make this the
primary key. So, you don't have to know
about keys and primary keys and stuff to
build a simple table. You don't have to
include that primary key bit. Um, but if
you want something that is like uh
unique for a table, which would be a
primary key, you're saying, "Hey, run
can never repeat itself. If I already
did run one or run two or run three, I
can't have another run one or run two or
run three. That's going to corrupt my
data." So, you can make that a primary
key.
And then we'll have a username, which
will make a varchar 50. If you haven't
seen that format before, that's SQL's
way of saying it could be a string. var
means variable character and you can
basically pass anything in there.
Okay. And then we'll say start date
time. And the reason I'm putting null at
the end is kind of SQL's format for
saying do you want that field to be able
to be left empty or not. Um and in my
case I'm actually okay if any of these
fields get left empty except for uh the
run count because I'm sort of just
trying to prove out the function. uh but
a lot of the times you might say hey a
row of data is going to be useless if I
don't have start time or end time or a
username and in that case you would say
not null
click count int null okay so what I am
doing right here with this command is
creating a table that has an integer for
run count a string variable character
for username a datetime parameter for
start time and end time and then I'm
going to store the set points for time
delay and click count And if I hit
execute on this and then I refresh my
little browser over here in the right,
you can see sequence data has those six
columns. And a statement that we'll use
a few times is I'm going to select all
by using the star from sequence data.
Okay. And if I hit execute, you can see
nothing exists in there. Um, and that's
actually what we expect. We expect
nothing because we just made that table.
So, we're going to go ahead and close
this. Uh, and what we're going to do
next is take a look at from ignition
from our sequence, how to uh store that
data, how to trigger um events in our
ignition sequence that are going to
write data to SQL. And that'll give you
an example of how to store batch data
out of a sequence that you might have
written in the PLC and are monitoring
from an HMI. So, very cool
interconnected automation controls
concepts going on here. And to do this,
we're going to add a few new tags. So,
we had our original Arduino tags from
our first example of tying Arduino into
Ignition. We had our sequence tags from
our additional sequence. We're going to
make a few new tags um just so we can
keep track of when we added what. So,
we're going to make a new folder called
SQL tags. And I accidentally added it
inside that folder. Let's go ahead and
liberate it. Um and we need to add a few
new tags. One of the first tags is going
to be a new standard tag. I'm going to
make it a memory tag. And I'm just going
to call it run count. Okay. Uh and we're
going to leave it in the default tag
group. It's going to be memory. It's
going to be integer. And to start it'll
be a zero.
Okay. So the run count is going to be
that primary key that tells us how many
times we've run through this sequence.
And we're going to make a another new
tag, standard tag, memory tag. And this
one we'll call user name.
Okay. So, if you were going to log in,
you could store that info in a string
tag. Uh, initially, we'll say it's Pete.
That sounds like a cool guy.
And then the last new tags that we have
to create are start time and end time.
So, I'm going to do new standard tag,
memory tag. Uh, this format is going to
be datetime, and we'll call this start
time. And actually getting a little bit
ahead of ourselves here, but I'm going
to only make start time. And we're not
going to need a tag for end time because
we're going to use the trigger event
that finishes the sequence as the time
to go do end time. Um,
all right. So,
there's there's just me adding the tags
that round out uh what we're going to
write to SQL because everything else
already existed. Let's capture the start
time of our sequence. So when we click
this button uh for start and we're in
step zero, that's when we go from step
zero to step one. So I'm going to click
on the sequence step tag and I'm going
to go to scripting and I'm going to go
to value change and I'm going to add a
little if current value
is equal to one and
previous value do value was equal to
zero. So here I'm taking advantage of
the tag change event which has current
value and previous value as parameters
of the event. And technically this
sequence only ever goes to step one from
step zero. So this is kind of an
overengineered statement but we're going
to leave it. And I'm going to say okay
that means someone just started up the
uh sequence. So I'm going to do
system.tag.right
write blocking, which is the uh ignition
syntax for writing a value to a table.
And I'm going to copy the path for that
start time tag that I just made. Copy
path. And I'm going to paste that path
in here. And then I'm just going to say
use system.date.now.
Okay. So, this is Ignition's like system
formatted. Go grab a timestamp of what
my computer says it currently is and
store that in a tag. And I'm going to
hit apply. And I'm going to hit okay.
And I'm going to hit save. And now we're
going to go back here. And we're going
to hit start real quick. And now we're
in step one. And if we go back into the
designer, now we can see we have uh, you
know, August 12th at 10:17 p.m. stored
in start time. So that's cool. That
tells us that our script worked. Now
we've got the time capturing into start
time, which is very cool. Um, but to
actually write to SQL, we need to hit a
database, which is technically a gateway
uh scoped thing. So, we're going to go
to gateway tag change events, and we're
going to go to tag change, and we're
going to create a new event, and we're
going to call it write to SQL. But
basically we're going to say when that
sequence step tag changes when the value
specifically changes. So not the quality
not the time stamp but the value of that
tag changes. We're going to trigger this
right to SQL script. Um but we don't
want to trigger that script every time
the value changes only when it resets.
So we're going to start by doing
system.tag tag dot read
blocking on that sequence step and then
square bracket0 value. That's just a
little ignition specific syntax for how
to read the value out of a tag. And
we're specifically going to say if value
equals zero
because value equaling zero means that
the sequence has just reset. This is an
onchange on tag change event script. So
the tag is only changing to zero when
someone hits stop because it was in
sequence step five. So that's when we're
actually going to trigger write data to
SQL. Now all we really need to do is we
need to go out and read all tags that
have SQL data. And then we're going to
do a section of define the SQL query and
arguments.
And then we're going to call the SQL
query and increment the counter. So if
you remember, we have like a counter
variable. It's currently at zero. And
we're going to increment that every time
we do a new run. Okay. So these are kind
of the three sections we have to create
in this script. And it's immediately
hopefully sort of apparent um why you
don't want to do this just on a tag
change script. It's a little bit easier
with gateway events um to keep revision
control and sight on things like that.
So, we're going to read all the tags
that have SQL data. And if we remember
our table, we want to start with uh
start time. And I'm going to try to keep
the convention going to try to keep the
convention uh of first letter lowerase
and then all following words. What is
that called? Snake case, I think, or
camel case. I get the two backwards.
Okay. In any case, we need start time,
we need end time, we need the username,
we need the count set point, we need the
time delay set point,
and we need the uh run count, that
unique parameter. So, we need to read
all these tags. I'm going to steal this
system.tag readblocking uh command here,
and I'm going to just replace the tag.
So start time is going to be that start
time tag. Copy path and paste it in here
instead. And then actually that isn't
what we want it to be for end time. We
want system uh datenow. No
system.time.now.
Um no system.date.now. Come on Pete
getting tired. Uh so we want
system.date.now
for end time. And then for the username,
that's going to be uh we're going to
copy this line and look at user name
instead of start time. So we're just
going to update this bit to username.
And to write uh the basically all we're
trying to do, just to be super clear, is
that when we write a SQL query, we need
to pass in all of the data we want
stored in SQL. So within our script, we
have to go out and grab the data that we
want it to read. Um, you can read all of
these tags in one line of code. I think
it's a little easier for beginners to
see it broken out this way. Um, so let's
go ahead and say, uh, run count is this
just
run count like that. And then time delay
set point is actually copy path in a
little bit of a different location. So
we'll copy that one. And then count set
point is right next to time delay set
point, but it's called HMI
count S sp. Okay, so that should give us
the six fields that we want to write
into SQL. Um, one thing that is worth
calling out. So we've got start time.
We're actually going to format start
time here. We're going to use a command
called system.date.format
format because uh the ignition date and
what my SQL or SQL light uses are
slightly different. So we're going to
take that start time and we're going to
explicitly tell it to be fourdigit year
with y twodigit month uh twodigit day
and then twodigit hour twodigit minute
and then twodigit second. So, we're
explicitly defining a date format
because SQL will throw an alarm uh throw
an error if it uses the exact ignition
format. So, we're going to make this new
variable start and end instead of start
time and end time. And they're just
going to have the transform data. You
can do system.date format and system.tag
reblocking on the same line. I think
it's easier to split it out this way,
especially for beginners. Um, but if you
look at this tag and if you were to see
that printed, it's got PM on it. And,
uh, it can also, depending on, uh, the
ignition statement, it can sometimes
have the date, too. Like it'll say
Monday, the whatever. Um, so this is
just us explicitly calling out a format
that we know SQL will support. Um, okay.
So, we've got all of the we've got all
of the raw data we need. Now, we're
going to define a query. And this is a
cool thing about Ignition. You can just
write simple queries in a string of
text. This is true of Python as well. If
you're if you're a bit of a Python
programmer, we can just type in insert
into uh sequence data and we can say
username
usern. Let's go real quick to the
database browser, query browser, and
make sure I get all these column names
right. Whoops. Where'd they go?
Um, yeah. So, we've got run count. I'm
just going to take a snip of this real
quick. Run count. No, I'm going to leave
it up the whole time. Even better.
Gateway events. Okay. Uh, so we want
username
like that. So, this is case sensitive,
which is the main reason I'm being extra
careful about this. Oh, where'd my
gateway event go?
And we're going to insert you. Now, the
order also doesn't really matter. It's
okay that we um grabbed the uh grabbed
the order in a different
grabbed these raw tags in a different
order that I'm defining them right here.
Uh click count.
What else did we have? Let's just finish
typing these in so that then I can talk.
Time delay
start time and end time. Okay. What I
was trying to say is it's okay that
these are in different orders because
then we say the values are going to be
um parameters that we pass in. So we do
that with these question marks. So there
should be six parameters that we're
passing in. Um so our sequence our SQL
statement is just saying we're going to
insert values into those six columns. Um
and the values are things we're going to
pass in. They're going to be arguments.
So, args is equal to and then a square
bracket.
And here I'm going to do a little type
casting. I'm going to say, okay, string
username. It's never a bad idea, even if
you're pretty sure it's already the
right format, to explicitly make sure
that it gets defined as the right
format. So, like, um, run count needs to
be an integer for it to work. Uh, click
count, same deal, which what do we call
click count? Count set point. Count set
point. Um, and then we have the time
delay set point. Same thing. That needs
to be an integer. Time delay SP.
And then we have our start time and our
end time. And actually for um another
just my SQLite uh thing that I'm sharing
with you all is that can cast casting it
to a string in the correct format is the
best way to ensure that it gets stored
in SQLite appropriately. So there's a
little bit of just knowing how to play
the game here, but basically what we've
done is we've said username, run count,
click count, time delay, start time, end
time are going to be passed in as six
parameters. That's what the question
marks mean. And then this order is very
important. These need to correlate to
the column order that you just did. So
it needs to be username, run count,
click count, time, delay, start time,
end time. Um, now there's only two lines
of code left and then we're done. Uh, we
want to do system. Db.run
prep update. And we're going to pass in
the query and we're going to pass in the
arguments. Uh, and then I'm going to
delete out all the other autogenerate
stuff. System.db.run
prep update querying the arguments. And
then the last thing I want to do, which
will also show us when we run it that
it's working, is I want to do
system.tag.rightblocking.
Uh, and I want to write to the uh count
set point, no run count. I want to write
to this tag.
write to this tag
a value of
run count + one. Okay, so this is me
saying, hey, at the very end of logging
data to SQL, I want to increment the run
count parameter by one. Okay, so we're
going to go ahead and hit save. And to
see if it's working, we're going to go
ahead and run through the sequence. So,
all right, let's count up. 1 2 3. Now,
it's in uh step four. Let's go ahead and
hit stop. Okay, now it's idle. It's
ready to go again. Let's go ahead and go
back to the designer. And you can see
run count is one. So, if you weren't
looking before, um, it was zero to make
sure that it actually got to SQL. We'll
go to tools. We'll go to query browser.
And again, we'll go ahead and select all
from sequence data like we did before.
Hit execute. And that's pretty cool. Now
we have run count, username Pete, start
time, end time look uh accurate. I had a
pretty pretty big break between when we
started and now. Um the time delay, set
point, and the click count. Okay, so
let's go ahead and go back there one
more time. Let's say now the count set
point is only going to be two, but our
time delay is going to be 5 seconds. And
in the designer, you could make it a
label on the field, but in the designer,
I'm just going to change the username
from Pete to Peter. And let's go ahead
and start this thing again. So start
and waiting to see input eight. There we
go. Time set point count up to five.
Count set point count up to two. And
press stop. Okay. So back to designer we
go. And now we can see run count is up
to two. We go ahead and we go to the
query browser and we search again. We
have two entries. So, one last thing
because it's nice to tie it back into a
visualization. In SQL tags, we're going
to add a new tag and a new standard tag
and a query tag. And we're just going to
say SQL results. Uh, I'm trying to go
pretty fast now because I know how long
this tutorial's gotten. Um, and we're
going to make this a query tag. We're
going to make it a data set.
Data set. I know you're in here. There
it is. Um, and we're going to just make
it uh every let's say 10 seconds. No, 5
seconds. What the heck? It's It's not
doing much. Um, we're going to make a
query that is just going to be select
all from sequence data just like we've
been running. Uh, and the data source is
going to be our SQLite database. I'm
going to hit apply. And now I'm going to
drag that SQL results onto the screen
and I'm going to make it a table.
And one ignition thing, I've seen a lot
of forums, a lot of people are like, why
do my dates uh look jacked up? This is
just a little bonus lesson for you. When
you have a table, um you need to
explicitly define the columns are date
time to get the date uh and time to look
uh correct. So if we go to columns in
the table um and we add uh array
elements and we say 1 2 3 four five and
six we can go to uh two and three which
in this case are start time and end time
and we can say start time uh and then
you come down to date format date time
and there you go start time looks good
we can do the same thing go to three end
time this very much is a tidbit of
useful if you're working in ignition
Uh, no other SCADA that I've seen has
this like explicitly needing to define
dates and times, but we're not going to
worry about that. We have a table on the
screen. Let's go ahead and run it one
more time so you you guys can see this
power of live updating. So, we're going
to start it again. Let's change this to
six and this to four. Um, and transition
the hand switch. We've got to wait six
seconds. So, we should have one now that
shows starting after this 2303. One,
two, three, four, and push stop. And
within 5 seconds, this table here should
update. There you go. It's way less than
5 seconds. New start time, new end time,
new time delay, new click count. Okay,
so we've seen just in a couple minutes.
We've seen how quick and easy it is with
a good SCADA software um to be able to
monitor uh live data. This also, this
same tool can go back in history pretty
far. So you can look at days or weeks
past as long as your computer's
retaining it. Um, and you can look at
continuous trending data like that. You
can look at SQL data like that. Um, far
and away historian and SQL are the two
most powerful data analysis tools for
automation and controls engineers to get
familiar with. So I hope you found this
to be a useful introduction. I hope you
have found the introduction to the
automation and controls tutorial series,
if you've been following the whole
series, to be super useful. We've
covered the majority of the basics to
the point where you should at least
understand the major fields that we're
addressing. And so, the next big video
on the channel is going to be a large
build where we combine all of these
concepts together as a kind of tutorial
and showcase of the skills that we've
developed together. So, as always, if
you have any questions about what you
saw in this video today or things you'd
like to see in the future on the
channel, be sure to let me know about
them in the comments below. Thank you so
much for watching and we'll see you next
time. Thanks. Bye.