Working with time series data in Node Red with SQLite

welcome to a new episode on my home automation open have an old replaced in this video I explain how I work with time series data how I save them in a database and use the UI charts to display them all this is in node-red as usual links to the supporting documents and code examples are in the video description this video is likely to be the first in a series on this topic my node red setup is very very recent I have a few sensors feeding in data but not a lot at the moment I'm trying out the basics of the process and will most probably put more videos as I refine them I will try to remember to come back here and put the link to the newer videos to be honest I only have two data sources one is a shy Oh me me flora soil humidity sensor which gives me temperature sunlight and soil moisture values every 15 minutes and the other one is my solar panel system on the on the top of my roof which gives me used so usual like you know voltage power output and generation in every five minutes or so and so these two sources are you know quite different in terms of the ranges and and there is not much I can compare it between them but still it's good enough to set the setup the basic processes and present them to you what I want to do and what I want to cover in this video is how once you get the data from your inputs high you store them in a database how you use the the UI chart component to display them and also store these values and what to do with the data once you have started collecting it how you start getting rid of your old data and how you start aggregating your data to store information I decided to use a SQLite which is a database system that you can install on Raspberry Pi and on Linux so it's an SQL database engine and it's as the name suggests it's a very light database engine so that's probably a very well suited for raspberry and for low-power machines but again it has some limitations so I'm fairly new to this whole you know node-red process I'm trying to set up something to get something working so I thought I would I will start with something simple but if you have different needs or if you have a lot of data to work with most probably you should be looking at a different database engine which can you know surely can handle more data and also something more elaborate on the other reporting and the presentation side as well I would also like to type and thank the guys in the node-red forum who give me a lot of help in this topic so again I got the whole recommendation to use SQLite and the end the dashboard from the forum but again there have been other people suggesting me to use different databases and different visualization options but again I just started to go with a simple one for the time being so if you want to know a little bit more information about this maybe you can start reading this post and also there should be a lot of other resources on the on the internet as well and if you read this one and in one of the posts you will you will be able to get some insights from chaps who've been using this system for for multiple years or months so and they have some ideas like based on volume how much response you can expect from the system and they looked you know fairly good for me and that was again one more reason I decided to go for that to start with SQLite you need to install it on your Raspberry Pi and again you just go on Google to France as some blocks how to do that and to get it working with node-red there is also a node red node for SQLite so you need to use the nmp install node red node SQLite to get it on your setup and once you have done it then you should have a new node somewhere in the in this storage group which says SQLite them it is really really easy you need to pass a note in sorry a message in in which in topic you specify the the SQL string and then you get the result in a payload so it is really simple to use also what you should be considering is you probably be needing some sort of tool to manage your database again you can do it via the the node itself to create new databases to create tables and everything but probably it's easier to use something like this I decided to use PHP Lite admin again you find some resources on on the net how to install it it's not really complicated and this is web-based and it also runs on the on my PI which means that I have also needed to install Apache as a webserver just to to manage this but the way I'm using at the moment is I'm always shutting the Apache down and when I need to make any changes to the database I fire it up I use the PI the admin and I shut it down again because I'm not using it for anything else and one more thing about the the SQLite is the SQLite uses just some files to store the database so when you are creating a new in the SQLite confi you specify the folder where you want to create your databases and whenever you create a new database for example I created this node-red database it is created as a file in the file system so again just read up the resources on the net just to make sure that these files have the sufficient are authorization so your database and you can read that and just start with what I have done in the database level agree to the table which is called the sensor data and the structure is really simple so um the first field is an ID field which is my primary key and it's an auto increment field so every time I create an item that a record the the engine will just assign an ID for you and then I'm using a text field to to say what's my devices and now that's with what's the center of that devices and the actual value in reals so at the moment I'm I'm storing like temperatures and humidity and other values so they are all real values but into general real so I'm using the real time and two additional fields what's the epoch which is going to contain the JavaScript time in milliseconds and also another one which contains the more regular time stamp and I'm actually using the database to to default it to the current timestamp and I'm not using this field for anything else but I thought that a besides storing the you know the millisecond time this could be useful as well and to look at the data you can see that at the moment I only have two devices so the first one is the robot which is the inverter and it's giving me power voltage and today which is the generation today and then I have this other one which is the meat flora which gives in battery 10 battery moisture fertility sunlight so these are my devices and these are my sensors and you can see the values here the epochs which is the non human readable date time and the and the day time which is the more human readable date time that also helps me understand it just a little bit better if I'm getting the values if I'm you know if my sensors are working okay if I'm getting all the information I need from them okay good storing the data is relatively simple so if I take the me for example I have covered this in a previous video so the way I've done this is I have a background job in in mice in my pie which is getting the data from the sensor and then pushing the results into over mqtt in JSON format so um I've already covered harm extracting the values from the JSON format but basically what what I'm getting in the in the message in the message payload I'm getting dog temperatures sunlight moisture fertility and battery so what I have stopped doing here is for each of the values I'm constructing and sequel insert commands so insert into sensor data devices sensor value a book and I'm routing the values is the device is me flora the sensories temp the actual value is what I'm getting and the epoch is is my current date time so I'm using the the get date method of the JavaScript date/time object to get the the current millisecond and what I found is if I put this multiple insert into separate by atom by semicolon only the first one gets executed so the way to push multiple messages or multiple sequel instructions to the SQLite node is to put them into a race so I'm creating an empty array putting all these sql's into just an SQL string variable and I use out push and then what again I need to construct a in object where the topic is the is the sequel so I'm doing this five times because I have five values that I want to store and output yeah and eventually I'm doing this return outputs so I'm pushing this array out as an array and this piece here is is just for the status I've started using this status command which gives a message on the on the editor when it was last executed so again he just gave me a visual feedback so I know that you know this service is running and I'm getting the values every 15 minutes so the current system time is actually 604 and I got the last update at six zero zero so the job is definitely working so um I create this array of of messages each of them where the topic is the sequel and I'm passing down to the node-red database node and that that's it that executes the sequel and as you can see I'm getting all these values you know me flora temp battery moisture blah blah blah updated in the database as a new record and similarly to my robot solar panels again I'm getting all those free values so the the voltage the power and the total generation and in an object so I'm doing pretty much the same process here I'm creating the sequel insert sequel command for each of them I'm constructing all of them into an array and I'm pushing that array out to the output which gets fed into the node-red database one more comment on the on this see the the node the database node or the sequel node itself is if you are only passing one single select over you don't have to be that elaborate with the with the output array and then pushing the values into output simply just set message door topic equals the Select and then you just do the normal return message so that you know this piece and the outputs is only required if you want the ones you'll know to execute multiple sequels at the same time and by the way when you do that you are getting the results also in so the output so whenever you execute something in the message payload you are getting the the records that got for example select you're inserted at the output so and if you have doing if you are inserting multiple sequels you're getting multiple outputs as well as I've covered in previous videos anything which I want to you know show on the screen so for example from the from the solar panels I want to show the the power so I can see which you know this value at this curve pretty much corresponds to the sunlight and all I'm doing is here I mean I covered this in previous video extract the you know the actual power value I'm putting into the UI field to display unemotional pushing that data here into a chart and in the chart I just you do the usual setup so I want the chart to display the last day of data and then you know getting rid of the old data is handled automatically by the chart but again one what happens if for whatever reason you need to restore the or you know shut down load read and we started then your UI would be empty until you get the first reading and then the second reading and start building up the chart from that point onwards but probably you you want to do you want some possibilities so the chart updates the later state which can be done with these few notes which you can see here so first of all you use the top output of the chart and then you send it on to and send it to a file so I'm just i created the charts folder when i do all these dumps of my current chart state sort of chart yeah charge states and again just a simple five you use this override file option because you can find it in the documentation is the top node the first output contains an array of the chart state which can be used which can be persisted if needed so again this output every time the chart is updated all the chart values getting saved in a file and you to restore that is really simple again put it in Jack node with an injector once the startup checked you read the same file that you have created and you just add the JSON node which again converts the the the output which is in JSON into a JavaScript object because the chart expects a JavaScript object and you put it in so you define as an input to the charge so again now your chart has two inputs so this line is going to feed in the data every time it receives a new one and this is going to feed in the previous states when no that is restarting and if you have multiple charts you just have to repeat the same process over and over for each of them obviously make sure that you are creating a different file for each of them any oh sorry and you are restoring from the same file otherwise your chart is going to be messed up this is all nice and good and following this process your node-red is going to collect your data or your charts are going to display your last day or last week or whatever you have defined of that data point and they will nicely restore themselves if you repeat if you happen to restore a restart note read but I don't really know how many items I have oh I have 5,000 lines in this database and it's always running for about um probably about two weeks and still I mean most of my data sources are you know every five or every 15 minutes not to mention that my solar panel is only giving me data for about like you know at most eight hours a day in this winter period so I don't have a huge amount of data but imagine you have a couple of template nodes which give you temperature every every minute then this can really add up and not to mention that let's take the temperature for example I mean you probably want to see the temperature for the last week in this sort of resolution or the the Parker for the last week but anything beyond that or previous to those dates you don't think you need I mean for example the power all you need is how much was the total generation for every single day or for temperatures what was the max temperature or the minimum temperature a day any yet just forget the rest so the next thing is to somehow aggregate these time series data and for that I have created another database which is the wall I call it sensor underscore a ddr4 aggregate or aggregated and the structure is again fairly simple the first field is the epoch field which is integer the second is the device and the sensor and the value so the last three is the same as before and I'm making the first three fields as primary keys so for a particular time device and sensor I only want one single value and to populate these I'm using these nodes here in node red and what I have up until now is I want three I have three method methods I want to generate maximum values minimum values and the last values for any particular day and store that in this aggregated table so I'm using free inject nodes and each of them executes free a.m. in the morning actually a.m. 301 and 302 and what I'm doing is I get the current date and time I extract one day so I always go back to the previous day and I'm calculating the from day and the end day and sorry from date and the end date and the from date is so the previous day 0 R 0 minutes 0 seconds 0 millisecond and the end it is the same day 24 59 59 actually I think it's true nine nine nine nine because it's one thousandth of a second what is this anyway and because it's running everyday 3:00 a.m. it should be you know calculating the aggregate values for the previous day and what i'm doing here is select my sensor max value as value from sin theta so i want to get information from my sensor data and i want to calculate the max values and in the very close i'm setting the epic should be greater equals to form date and less than equals to end date so it's basically the entire previous day and and i'm listing all the sensor values that i want to calculate the maximum off so i want the maximum off temperature power and the moisture and you might think that this is all nice but I'm not really defining the device here and because I'm grouping by sensor I can't really you know use the device in here or at least it's not going to be a single select but um I realized that it doesn't really matter what type of temperature I'm I'm measuring you know usually for temperature type values I want to store the maxims so filtering or reflecting on the sensor field is enough for me and I'm grouping my sensor because obviously out the max of them and this goes into the database node and the output I'm going to process the output and the the output is going to be an array and and also because and it will you know this select is going to return me multiple items and so each item in the array is going to be one record one yeah one record from the to base with the max values so for each of them I want I'm creating an insert command so I insert those aggregate values into the aggregated table and I'm using this command which is insert or replace into so I think in normal operation the replace should not happen but if if I'm going to reuse this code to you know recalculate some of the past values then at least using the replace is going to ensure that the insert doesn't fail if the value already existed just going to override it so I'm going to insert in the censor aggregate table inserting the Epoque device sensor and value and the value is the from date so again here oops so here in the from date I calculate the previous day and I set to 0 0 0 so all my aggregate time stems is a particular day midnight the the value sorry the the device is the device the sensor is the sensor which I'm getting back from the select and what i'm doing here is and instead of pulling the sensor I'm putting sensor underscore max so I'm adding a suffix to the the sensor value and and the actual max value is again I'm getting getting it from the select and the reason I can reference this as dot value is because in the Select I'm using this as value so it's not coming through as you know like this in the result but it's coming through as the value so standard sequel stuff so I'm creating this replace and sorry an insert for every single max value and I'm just putting the same thing into the another database node and by the way at the database know all you are doing is you're defining your file for your database and I'm doing the same for the min so the mean is exactly the same the only difference is the funk which is the min and so it creates the inserts and then it goes into database and the last one is is getting the getting the last values and it is slightly different because I'm using the this function here so um for whenever I want the last values I have to create a select statement for every single sensor and I'm using the order by ID descending limit one so it's going to return me the the only one record with the highest ID which is obviously the latest and I'm using the same filter for you know start and end date so in in and of course the device and the and the sensor values also has filters in the very close so I decided to get the last values for D today which is the generation for today because that's all the the inverter auto increments that value and it resets to zero every every morning and also for the moisture I said I just want to last moisture levels so if I let's say water in the morning and it's time stripping back all I'm really really interested is what is the last value not the maximum value and so in in my case and put I'm you know pushing two sides into the into the sequel node again I will get the oh I'm only getting one value back and I'm using the same thing so I'm either using an insert or replace into blah blah blah and the same same processes before the reason I'm I'm I'm not using I don't need the array here because I'm pushing to selects into here and each of the select is going to return only one value one record because obviously I'm using the limit one here and each of the select statement is going to generate one output or one message one payload at the at the end so this particular one is actually going to execute be executed twice and this is what I'm just using the payload zero because I'm only going to get one object in the in the payload and as usual I'm pushing all the selects into this database comment and sorry if I go back to sequel Lite and if I list the values you can see that the maximum and minimum values are cutting calculated correctly so for if if I look at my solar panels from the Pope from the power I want the max and for today I'm getting the last value so there's no like last suffix maybe I should have I should have added and you can see again the moisture is this is the last value but hey I'm getting moisture max as well and yeah power max temperature max battery minimum temperature minimum today blah blah blah you're getting all these values automatically created in the system every single day on my home screen or on whatever screen that I have my sensors I usually have a single you know a simple chart for every single value the time displaying but I wanted a way like a more comprehensive way to display values from different sensors on one single chart where I can also update the period that I want to view and because this is all static stolid in database I can easily do air restore these values in the database and this is what I have done in a separate tab which I call reports and one of the main reason to put in a separate tab so I can have more as space on the screen so I've created a report here which and I'm sorry a chart here and I'm using this chart to display any values that I want based on this selection here I mean it's quite rudimentary at the moment so probably this is going to change because I don't really like the look and feel of it but still it can I can demonstrate you the the basics so I select a period and I select a data source and I click on submit and the chart is updated based on those data which is being retrieved from the database and if I want to select the the same temperature for yesterday it's obviously it's a little bit different and if I want to do it for the week then you get a lot more values so this is all nice but what it can also do and what the chart component can also do by standard that you can also push multiple data points to it so um maybe I do this too so you see that and I left the the chart Auto scale automatically so this is why the values are going up and down but what you can see here is now we are getting the temperature and the moisture on the same graph as well and actually if I really want to go over the top with the longest period and all the values then I can do that as well the only difference is the let me pick this point yeah so the sine light has a really high value so that is making all the other values look really really small and it's really hard to see it on the screen but again it's mostly a proofs of proof of concept it would be really useful if I would have like you know five six different temperature values and I can see them on the same plot because they would you know show up nicely they wouldn't be skewed by any really high numbers and the logic for this is really specific so I don't really want to explain in a great detail what I have done I'm using this UI form to say to define the periods that I want the report to show and in the name for these checkboxes I'm using like period today period yesterday play period slash week so I'm I have like a keyword and a value separated by slash and for data points are using the same so in in in the name I'm using the device slash value or sorry device slash sensor to describe what is it that I want to get later on because when I do the submit all I'm getting back from the form is basically this I'm getting back from the names and in this sequel I'm trying to construct the report based on based on these values that I'm getting from the form so in the loop I'm just going through all the the objects that I'm I'm coming I'm getting in a payload so and these are actually attributes that I'm going getting in the payload and so if I check that these are periods and based on that it is today yesterday or OB I'm standing I'm setting the from and the end date accordingly and if it's not a period then um I construct a select statement based on the incoming values so I said said a select star from the sensor data web device is is basically parts 0 is the is the string before the slash and the sensory is the string after the slash and the date is you know from data end date so for every single one of them which got selected on the form generating a select statement out of it and I'm using this particular one to set the last add an additional parameter the completely was true to the last select statement in this array and I'm pushing the array out and feeding that into the sequel mode as usual I think I'm going to finish here for the first video on this series the only additional things I wanted to add is I've also added this red reset functionality which is at the moment it's working from an inject node but if you are pushing an empty payload an empty array in a payload add to the chart that it resets the chart what it just needs everything for the chart so that can be a nice way of getting rid of the data which is displayed here because well especially on this one I have selected I don't know how many data points for entire week as I can be a lot of data and so I will continue this topic in the next video my kids are up so probably you can hear in the background that should be the site for me to stop so thanks for watching I hope you find it useful and definitely see you in the next video

Channel: Csongor Varga

Views: 62,049

Keywords: automation, sqlite, phpliteadmin, node red, time series data, graph, dashboard, mqtt, sensor

Id: ccKspiI8FRw

Length: 33min 17sec (1997 seconds)

Published: Tue Dec 13 2016