Sending sensor data from ESP32 to InfluxDB | Local access - time-series database

[Music] so in today's video i'm going to solve a problem at work and my previous video uh slightly linked to what i'm trying to explain here is at work we have a bunch of sensors that give temperature data to some industrial quality redundant controllers that keep the room temperature below 75 degrees fahrenheit which is what is recommended for the scientific equipment that costs a lot of money to maintain and repair so to have a good ambient temperature of less than 75 degrees fahrenheit as desired so while i was at work um so this was very easy to monitor so every time i go to this particular room go check the the temperature and um i can go and give a feedback on hey this needs to be adjusted and stuff like that and ideally this would send out emails with warnings if something uh goes wrong um but the problem happens especially during um there are like weather events or uh weather related stuff that is when we are in the transition from um the winter to summer that is when the acs and the heaters uh don't uh go on and off at the same time so what happens is this temperature controller kind of is a hit or a miss and then we end up with non-ideal room conditions so the solution for this is probably a simple one to use esp32 chip with a temperature controller that can send us emails so this way you know it doesn't actually proactively go and change the temperature but probably with these emails i can get in touch with the right people to actually get this fixed in in the right time and especially during covet uh when we mostly work from home it's kind of very uh easy that is we get this um kind of data from a lot of rooms that can be automated and i'll show you what i do with those kind of data so in my previous video i have shown you how you can use esp32 to connect to wi-fi in my case i'm using edu rom it's offered at university of michigan which is the wpa2 enterprise wi-fi um so this is only possible on the esp32 and not on in esp8266 i'll leave the links uh for the previous video in the description so you can go and watch it um so uh the idea is to then use this esp32 so what i got um here was actually um the tt go t energy so what this has is a 18650 battery holder so you can put an 18 650 so even if there is um power no power um or i want this to be at a remote location this can run on the battery for a while um and then i connected all that display um using i square c protocol and also our bme 280 sensor very inexpensive accessories so the total cost of this is like less than 20 bucks um so within with less than 20 bucks uh we have a sensor so i um started programming uh and then uh get this data and send it so in terms of um the the the the arduino code that i have in there um it gives out the rest api that exposes the latest value from the sensor it also goes and connects to the dns so that i can access this on campus um if you uh want to do some in browser rendering chart.js is pretty amazing so um it has something called psram so i store a bunch of historical data on the ps ram that can be rendered directly to chat.js so i can look at historical data directly on the microcontroller and we also send this data to an inflex db server inside the campus and also this esp32 can send emails out using uh the local gmail servers that we have on campus um so if you don't have access to any of this so i there is this company called initial state uh they have the time series database that is that's on the internet is pretty amazing it looks very similar to what uh influx db does and they also have rest apis that you can use to actually send data from the sensor to here so my code that i have written here unfortunately i cannot share because this was done as a part of my work um but if you're interested send me an email if an educational institution i'm totally open to sharing the source code um so what this does is actually sends us data to all all these different outputs and also uh to an initial state uh database so today's video i'm going to focus on how we actually do a huge amount of data that is coming from multiple sensors into influx tv database so what is influx tv database you probably heard of mysql when i was probably in high school that was the craze but mysql is pretty good when you have a lot of different kinds of data so the data that these sensors generate is something called as a time series data so infectsdb is a time series database that is one of the access on uh anything that you plot in a time space database is time so you need time as one of the inputs and the data so basically you can plot that as a function of time so um this is uh the picture that you can get from influx tv's website so what influx db is is actually a bunch of uh time series database uh that can get data from something called as telegraph so you can have multiple um sources of input that can be transferred into inflex tv but today's video i'm going to show that we have some libraries in arduino where these devices can send data directly to inflex tv and this is pretty new over the last year or so uh flux is the language they they've been using the sql kind of thing which is so annoying so the flux is so easy i'm going to show you uh some examples of these flux queries um so basically you can create huge amount of data queries uh from this database and get the kind of plots that you're interested in as well as you can have alerting frameworks that is if something is not what you think it should be it should alert you so this is pretty pretty good um so i'm going to show you how you get this uh so basically um once the influx db is in in production on on a server uh we can use uh another software called grafana i'm not going to show you how to install this but graphene is pretty amazing at connecting with various inputs so basically in our case the grafana connects to infix db and then it can be used for monitoring so you can do a whole bunch of mathematical calculations on the fly you can do moving averages you can get derivatives and stuff like that and the good thing about graphene is you also have alerting networks so basically you can create a telegram message or a gmail in our case i use gmail that actually is set up as an alert so this way anything that is out of normal in this case temperature above 82 it's going to send me an email um so so so that is one of those uh powerful things that you can do once you have the data into the influx tv database um so how to install nfxdb you can go to influx db's website uh go and check out um the various ways to install for the operating system of your choice but in in 2021 uh the best way to actually install inflex tv is use something called uh docker uh docker is independent of what uh operating system that you're working in whether you have a mac uh a pc or a linux operating system and uh in order to install influx db all you have to do is docker pull this command again this is all in their website as well and once you uh actually do this you would run this command so you just say docker run minus p 8086 colon 8086 and then uh what do you want to run um so what this means is it's actually forwarding the port of 8086 that is inside this docker to the outside so i'll just show you how this works so this is just a powershell that is open in vs code you don't have to use vs code you can just open any powershell on windows um open a terminal in linux or an extern or terminal in mac os and you can just type in this command so you just say docker pull this it takes uh about a few uh five minutes or so to get everything pulled from the servers um so um i have already uh downloaded it that's why it is so quick but have a little bit of patience so it takes a while to actually get everything set up so once you have that you can then use this command called docker run so basically it takes a few seconds so once uh this is running uh you can then open a web browser and set everything up i'm going to walk you through every one of these steps okay so now to said hey i have already started this on port 8086 so let's open the docker terminal so in docker desktop you see that it is actually giving a silly name to this but you can again go and define the name that you want to give and then you can just say open in browser once you click that go and type in localhost colon 8086 and then do get started so username you can just call it whatever username you want to give so uh give a password that is secure so this is something that is very important so you want to give organization in this case for this example i'm just going to say og and for bucket i'm just going to call sensors okay so bucket is actually the name of the database that you want to work with okay so basically again so this is the user that is test and the organization is this tiny little thing that's here that you're going to use it um so that's what you know you need to you do that and basically you go on data and click on buckets and buckets of these databases so in this case this is that sensor bucket okay um then you can add a few things like you can define how long you want to keep this database so you can then say that hey just keep it for one year um and so that you know it doesn't overwhelm um so basically uh this database is automatically pushed every 365 days okay so you can define um those kind of things for a particular database then what you create are something called as tokens so you then click on generate read write token so i'll just call it sensors underscore token okay you can name it whatever you want in this case it's going to have access to the reading the values from this as well as writing okay so we're going to use these uh token to actually read and write into this particular database so then you click on this then it gives you this big alpha numerical token that you're going to use for the next few steps so remember you require the organization you require the the bucket you require the ib address you require the port and you require this particular token all you have to do is use this particular library so someone has done all the hard work it's on github i'm going to leave the links for this below so once you get this installed you just type in include this particular library and then you want to define these four things so basically your inflex tv url is going to be the ip address colon the port if you're going to follow my instructions you can just use 8086 and this will be the ip address don't use localhost right um so localhost is because this is on this particular computer so you want to install you want to put in the ip of where you installed your influx db database um so this is that token that i showed you in the last step that is this alphanumerical value and you go and put that value over here um this is the organization so remember you can then if you forget what you put in you can go to your settings profile and under the name you'll see this particular organizations and for the bucket we we're using the sensor bucket okay so once you define that all you have to do is put this particular line in on top of your code and then you want to define a sensor in this case this is just a sensor that reads temperature humidity and pressure for that particular room um so you can also add tags that are common to this particular sensor so in this case you can say hey this is a device this is the name of that particular device as well as the location so basically you can then get that kind of information as well once you have defined all that in your setup um every time that you get data that is every 30 seconds 40 seconds every minute whatever it is the frequency that you want you want to clear this particular sensor value and then um you can add different values in this case i'm reading the temperature and putting it into this temperature variable so you can have as many variables as you want and once you have added those values then you can go in and put the client at right point for this particular sensor so basically what this will do is go and put that data into the infoxdb database so in my case i have three different sensors in three different rooms and they all continuously send data every uh 30 seconds all that data is put into that influx db database and i'm going to um then once this data is put in there i'm going to read it on grafana so you can open grafana i'm not going to go into instructions on how to get this installed you can go to the website install it you can use docker as well um so all you have to do is go to configuration say add data source so it will give you so many options so what you have to do is type in flux flux so in this case you just click on this and select okay so here you would then give a name the query language so it depends if you have the older influx db like one point something you'd use the sql kind of language but i'm going to show i'm going to use flux and this is going to be the ip address so you want to put in the ip and then the port so whatever is your ip and then the port so here you want to say you want to use no authentication the organization is going to be your organization that you have set up um so the token is that same token that you got from your influx db so that's going to be this guy you don't have to define a bucket and then you just say save and test so in my case i already have this setup so i'm just going to show you one of those dashboards and these influx queries so in grafana this is just showing some temperature data from three different sensors over here um so here what i'm doing is saying hey from this bucket sensors um from the time that i defined here till the time that is defined over here so start and stop uh you filter the device name uh you uh filter the temperature because it also gives out temperature humidity and pressure so in this case i just want temperature out of it um and i drop a few columns so the measurement and the field um so this way it kind of gives me a linear data and uh then what i do is aggregate over 15 minutes and get the mean so it's kind of like gets average over 15 minutes and that is what is plotted over here so basically this is gives me within one glance i can get a very good idea about what is happening over here so i'm going to show you how i create alerts um so then i have some similar queries over here where i just get the last value over here um so in in these alerts what i can do is get these uh last values from this and once it is above 80 it will send me an email so basically um here you see that is that alert so if this particular temperature goes beyond 80 fahrenheit um it's going to send me an email so this is this is kind of like those notification settings again here you can create uh send emails and telegram message whatever so you can have multiple um outputs uh for notifications uh so this way it also actually also creates a picture or a snapshot of what is going wrong and then it is going to send you that uh particular message so grafana along with influx tv is very powerful and this is just showing your real-time data from my sensors that i have at these rooms and hopefully this gives you an idea how you can take huge data sets and actually plot it you can also get the gradients as well you can create derivatives uh this is very powerful so you can uh create those kind of information from here um you can also create statistics as well and you can use this data to do some kind of machine learning and kind of get these alerts based on those advanced you know machine learning algorithms that can figure out anomalies among huge noise of this data right you want the temperature to be constant and that's what it is but then once you see something like this it would alert you saying hey this is something abnormal and stuff like that so you can do all those kind of things again i'm not going to go into that but this is like a very quick and simple way to get a lot of data plotted get that kind of reactive information and uh proactively go and fix whatever the issue in this case uh the temperature that is the anomaly that i'm looking for so yeah so go ahead and try it out leave a few comments if you have issues go and check out that particular library i'm going to leave those links below as well bye

Channel: Debashish Sahu

Views: 2,595

Rating: 4.9298244 out of 5

Keywords:

Id: Jr3KbuwHPgw

Length: 19min 36sec (1176 seconds)

Published: Mon Mar 15 2021