Brandon92
Posts: 770
Joined: Wed Jul 25, 2018 9:29 pm
Location: Netherlands

Start with a database for sensor data

Wed May 15, 2019 2:06 pm

Hello,

I have a question about how to set up a database with sensor data. This question is probably asked a million times and the answer is probably easy. But this is the first time I have done this.

The goal of this project:
This project is started to get more insight of our pond at home. I would like to store the temperature, pressure of the pomp, when a certain filter is active. And in the near future there will probable more sensor. The “stored” data is eventually showed in a nice webpage. Also other sensor based on a ESP32 could be used in this project later on. And a ESP32 could also be used to show some details of a sensor on a small screen.

Project:
The hardware side of this project is not a real issue for me and the first prototype is already running. And the data for all the sensors is stored into a text file. But I would like to make the following:
Consept software diagram.png
Consept software diagram.png (13.99 KiB) Viewed 618 times
And here is where the “problem” start. I found on the internet that there are a lot of options to handle this issue. But which one the most suitable for my project.

Possible solutions I found:
I found that the MQTT protocol and the mySQL are often used for this kind of application. But I also read that the MQTT protocol cannot store the data directly to the mySQL database. So, is it not easier to store the data from the sensors directly to the mySQL database?

Software packages:
The following packages I found that could be used for this project:
On the NAS On the Rpi: Question:
Is what I had in mind feasible for this kind of project. To make a SQL database with MariaDB and store all the sensor data on my NAS. And use that data in node-red to make a nice web page for it.
Or are there better options that I could use for this kind of application?

Kind regards,

Brandon

Andyroo
Posts: 2837
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Start with a database for sensor data

Wed May 15, 2019 5:04 pm

You can actually use Node-Red to capture the MQTT data and store it into the database using the node-red-node-mysql node (too many 'nodes' in the bit :lol: )...

An other option is to use Python to capture the MQTT data using the Paho module - Steves MQTT guide has two logging options - one to a log file and one to MySQL.
Need Pi spray - these things are breeding in my house...

Brandon92
Posts: 770
Joined: Wed Jul 25, 2018 9:29 pm
Location: Netherlands

Re: Start with a database for sensor data

Wed May 15, 2019 7:21 pm

Thanks for the response.
That could also be an option. But I think it would be easier that the "sensor module" send the data direct to the database. So that the data storage is not depending on a "other" program that is running. If this makes sense :lol:

Do you think it is a good idea that the "sensor module" (Rpi zero w) send the data via mySQL (MariaDB) to my NAS. And after that the data is processed by node-red. That is running on the same nas. But I think I will try it first on a separate Rpi/ laptop.

Andyroo
Posts: 2837
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Start with a database for sensor data

Wed May 15, 2019 8:03 pm

Brandon92 wrote:
Wed May 15, 2019 7:21 pm
Thanks for the response.
That could also be an option. But I think it would be easier that the "sensor module" send the data direct to the database. So that the data storage is not depending on a "other" program that is running. If this makes sense :lol:

Do you think it is a good idea that the "sensor module" (Rpi zero w) send the data via mySQL (MariaDB) to my NAS. And after that the data is processed by node-red. That is running on the same nas. But I think I will try it first on a separate Rpi/ laptop.
You can write direct to a remote database from the Zero - not sure if the ESP32 chip has a module to do that though (apologies - should have Googled this module first.

I've just gotten use to using MQTT as I have it set up and splatting data around with multiple devices reading the same data :lol:
Need Pi spray - these things are breeding in my house...

Brandon92
Posts: 770
Joined: Wed Jul 25, 2018 9:29 pm
Location: Netherlands

Re: Start with a database for sensor data

Wed May 15, 2019 8:35 pm

Thanks for the provided information. Will try tomorrow if I can get it to work :)
Andyroo wrote: Need Pi spray - these things are breeding in my house...
I have heard that the Ri is very allergic for water. And when you dissolve some salt in that same water it will work even better. Spray that mix on a working Pi and it should work a treat :lol:

Andyroo
Posts: 2837
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Start with a database for sensor data

Wed May 15, 2019 8:37 pm

Brandon92 wrote:
Wed May 15, 2019 8:35 pm
Thanks for the provided information. Will try tomorrow if I can get it to work :)
Andyroo wrote: Need Pi spray - these things are breeding in my house...
I have heard that the Ri is very allergic for water. And when you dissolve some salt in that same water it will work even better. Spray that mix on a working Pi and it should work a treat :lol:
Shush - got caught today by my wife buying some more bits. You will give her ideas :lol:
Need Pi spray - these things are breeding in my house...

PhatFil
Posts: 1189
Joined: Thu Apr 13, 2017 3:55 pm
Location: Oxford UK

Re: Start with a database for sensor data

Wed May 15, 2019 9:01 pm

I have to agree with @andyroo above.

Use mqtt to publish your sensor data, from every device, Then host some control s/w to subscribe to and log your data as well as instigating any actions/responses to any particular data states (ie high pressure/pump blocked -> turn off pump)

Regardless of how you handle the mqtt subscriptions, expanding to add more inputs is as simple as copy and paste of the first handler, modding only to fit the new data source and destination

Node Red Is a very powerful tool when used with Mqtt, But you could also write your own python mqtt subscribing/handling script or scripts, or any other mqtt enabled toolkit like any of the home automation suite options hassio etc..

Node Red is Data flow centric which can take a bit of getting the hang of, (I started drawing flows with lots of javascript function nodes to start with..)
Fwiw I currently host mosquitto node red and a raft of other servers/services on a Pi-0-W with only wifi networking, servicing 30 odd esp/tasmota devices and upto 10 ble sensors without any probs..

Brandon92
Posts: 770
Joined: Wed Jul 25, 2018 9:29 pm
Location: Netherlands

Re: Start with a database for sensor data

Thu May 16, 2019 2:33 pm

Thanks for the response. I'll will take a closer look at he MQTT and maybe it is indeed the best solution.
With the knowledge that I have now, it would look something like this setup:
Concept software diagram V2.png
Concept software diagram V2.png (25.28 KiB) Viewed 511 times
And this is maybe this is better what I had first in mind. Because the Rpi zero w will do "everything" and the nas is only for the data storage. Also because Node-red is not official supported by my nas and this could also be a trouble to install. Than this is a "saver" route.

So for now I'll read more about this topic and will see what I could find :)

Andyroo
Posts: 2837
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Start with a database for sensor data

Thu May 16, 2019 2:47 pm

I maybe tempted to still run the SQL server and data on the NAS - just being paranoid over SD card failure and big queries hitting the Pi.

Then again my SQL data was enormous and had 100s hitting it :lol:
Need Pi spray - these things are breeding in my house...

Brandon92
Posts: 770
Joined: Wed Jul 25, 2018 9:29 pm
Location: Netherlands

Re: Start with a database for sensor data

Thu May 16, 2019 5:07 pm

Andyroo wrote:
Thu May 16, 2019 2:47 pm
I maybe tempted to still run the SQL server and data on the NAS - just being paranoid over SD card failure and big queries hitting the Pi.

Then again my SQL data was enormous and had 100s hitting it :lol:
That was indeed the plan. The NAS get always the latest data and the SQL server is indeed running on the NAS and not on the Rpi.
Maybe this basic block diagram will give a better idea:
Concept software diagram V3.png
Concept software diagram V3.png (35.38 KiB) Viewed 496 times

Return to “General programming discussion”