What "TYPE" of data base do I need?

General
1

I am NEARLY there with parts of my flow in getting readings which I want to keep.

The data will be simple stuff like Temperature, Humidity, CPU loads, CPU temps, stuff like that.
I may time stamp them too.

But for the sake of learning how to store data and graph it (yeah, another Yak to be shaved)I was wondering what "kind" of database I need.

I've heard of this mongo (?) database, then there is sql, and.... a lot more.

Basically it will be the time and a reading. Be it temp' humidity, load, etc.
I'm not sure the best way of time-stamping. Do I do it with epoch (unix) time or YY-MM-DD-HH-MM-SS type.

Size.... Well, not sure as yet. Though I think BIG would be a word in the size description.

Oh, some would be controlled that if the device is not available, no data would be logged. But that is before it gets to the database, so I guess it is neither here nor there.

Thanks in advance.

1 Like
2

An important question here is what do you want to do with the data after you have saved it. The answer to that will help to determine what database to use to save it.

3

Have you tried doing a search on your favourite search engine for "what type of database do I need"

There are lots of websites that will explain the key differences and let you work out what your requirements are.
If this is for home use, it isn't BIG...

4

At this point all I "understand" is that I want to record the data for latter processing/history browsing.

Looking for patterns/trends.

I've looked at some of the different types, but because I don't (yet) get the requirements of the databases, it is hard to pin it down.

UKMoose, Ok, granted. It is for home use. But I'm guessing it will grow to a few Meg in size.
But there will be many points.
So far 5 points, but then there will be machines: say 12.
A few other things logged at non regular times. Say 4.
And a few more things once I get the hardware working and I want to monitor more points.

Mongo seems not the best because every entry is a new file and so there would be a lot of wasted disc space.

I've played with the SQL and JSON ones, but the examples that come with them aren't really..... clear in what they are doing.

Writing a line to a file (with timestamp data) and looking at it is not really a database.

The other one complains it can't write zeros to the file, or something.
Some weird error on the example flow to show how it works is not encouraging to want to use it.

Oh, and finding the files......
The one which worked: With only two entries the database file was 12228 bytes!
The other one - which granted if it wasn't initialised - didn't exist, so it wasn't very indicative of what it could do.

At this point the file entry would be simply: Event; data; time stamp.
So granted it isn't complicated.

5

Go look at sqlite in the flow library. Self contained, no need to run and manage a separate database application, will be more than enough for home use.

6

For storage of real time data then I think Influxdb is hard do beat. It is designed for the efficient storage of such data. There is a node-red influxdb node that makes writing to it very easy. In addition you can then very easily draw charts using Grafana. They give you out of the box access to historical data, zoom in on interesting areas, etc. In addition influx and grafana will run well on a pi.

Just a note about date/timestamps in a database. The database will have a date/time column type that will format the date internally in whatever format it uses. It is best to save the time in UTC, then you don't have to worry about what to do when summer time kicks in/out. Then conversion to local time is then handled on displaying the data.

7

knolleary,

Thanks, but I fear that is one (just checked) that just doesn't work.

See code:

[{"id":"eb329916.3fbb8","type":"inject","z":"1d001802.89402","name":"Go","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":90,"y":80,"wires":[["b0e7ccc.17f343"]]},{"id":"b0e7ccc.17f343","type":"function","z":"1d001802.89402","name":"INSERT Bind","func":"msg.topic = \"INSERT INTO test (id,text) VALUES (?,?)\";\nmsg.payload = [2, \"This is a funny world's end\"];\nreturn msg;","outputs":1,"x":285,"y":81,"wires":[["953e765a.26117"]]},{"id":"953e765a.26117","type":"sqlite","z":"1d001802.89402","mydb":"74998355.c5df7c","sql":"","name":"Test DB","x":603,"y":112,"wires":[["e5e8ccf0.71cce"]]},{"id":"e5e8ccf0.71cce","type":"debug","z":"1d001802.89402","name":"","active":true,"console":"false","complete":"false","x":691,"y":212,"wires":[]},{"id":"33dd4497.bebbf4","type":"inject","z":"1d001802.89402","name":"Go","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":88,"y":132,"wires":[["c4905d83.863d1"]]},{"id":"c4905d83.863d1","type":"function","z":"1d001802.89402","name":"INSERT Old","func":"msg.topic = \"INSERT INTO test (id,text) VALUES (3,'Directly in topic')\";\nreturn msg;","outputs":1,"x":267,"y":136,"wires":[["953e765a.26117"]]},{"id":"9a148cf2.7a5628","type":"inject","z":"1d001802.89402","name":"Go","topic":"","payload":"funny","payloadType":"string","repeat":"","crontab":"","once":false,"x":107,"y":296,"wires":[["9542d9ab.3f1618"]]},{"id":"9542d9ab.3f1618","type":"function","z":"1d001802.89402","name":"SELECT Old","func":"msg.topic = \"SELECT * FROM test WHERE text LIKE '%\" + msg.payload + \"%'\";\nreturn msg;","outputs":1,"x":334,"y":299,"wires":[["953e765a.26117"]]},{"id":"723342b4.34e0ac","type":"inject","z":"1d001802.89402","name":"INSERT Bind - via JSON","topic":"INSERT INTO test (id,text) VALUES (?, ?)","payload":"[4, \"This is a funny world's end\"]","payloadType":"str","repeat":"","crontab":"","once":false,"x":109,"y":197,"wires":[["521c83d.287f4fc"]]},{"id":"521c83d.287f4fc","type":"json","z":"1d001802.89402","name":"","x":309,"y":197,"wires":[["953e765a.26117"]]},{"id":"3dcfe3fc.a32aa4","type":"inject","z":"1d001802.89402","name":"Go \"funny\"","topic":"","payload":"%funny%","payloadType":"string","repeat":"","crontab":"","once":false,"x":103,"y":350,"wires":[["57629578.436a0c"]]},{"id":"57629578.436a0c","type":"function","z":"1d001802.89402","name":"SELECT Bind","func":"msg.topic = \"SELECT * FROM test WHERE text LIKE ?\";\nmsg.payload = [msg.payload];\nreturn msg;","outputs":1,"x":330,"y":353,"wires":[["953e765a.26117"]]},{"id":"1cdc3d9b.32f99a","type":"inject","z":"1d001802.89402","name":"Go \"world's\"","topic":"","payload":"%world's%","payloadType":"string","repeat":"","crontab":"","once":false,"x":107,"y":412,"wires":[["57629578.436a0c"]]},{"id":"7f7bb046.f7d28","type":"comment","z":"1d001802.89402","name":"Database messing about","info":"","x":640,"y":40,"wires":[]},{"id":"74998355.c5df7c","type":"sqlitedb","z":"","db":"/home/me/TEMP/test.sqlite"}]

When I run it (after editing the path to the database)
I press the top GO button and get this:

"Error: SQLITE_ERROR: no such table: test"

Sorry, but that has me stumped.

** NOTE **
The path is slight edited from /home/pi/.... to /home/me/..... as I am doing test work on my NUC and it has more ..... than the Pi and so is quicker to test.

8

(To keep things separate)

Went to this link:
https://flows.nodered.org/flow/36f2de38ed4bb62cf3a5e8fdc76d779e
and imported it.
Edited paths where needed but left as much intact as possible.
(/home/pi/.... to home/me/.....) and made the required directories.

Clicked on the startup button got these errors:
(Shortened)

"Error: ENOENT: no such file or directory, open '/home/pi/charts/miflora_temp.dump'"
7/7/2018, 7:49:06 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:06 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:06 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:06 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:06 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:07 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:26 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:26 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:26 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:26 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:27 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:27 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:46 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:46 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:46 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:46 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:47 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:49:47 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:50:06 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:50:06 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:50:06 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:50:07 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:50:07 PMnode: 620d87d0.13737msg : string[38]
"failed to open /home/me/sqlite/nodered"
7/7/2018, 7:50:07 PMnode: 620d87d0.13737
msg : string[38]
"failed to open /home/me/sqlite/nodered"
9

There is a comment above the flow saying to create the table first. Though it could be written to indicate that you need to do that more clearly. I think it is assumed the the user knows a little about databases and what the problem is if that error appears.

10

Did you watch the video carefully to make sure you had everything setup appropriately?

11

I've seen this a few times on other clips when talking about SQL(ite).

What is this "PHPLite ADMIN" screen that pops up?

Is that another set of things I need to install?

Just to clarify that:

We are talking SQL(ite) stuff..... Suddenly PHPLite ADMIN is mentioned.... I don't get the connection.

Is PHP and SQL interchangeable? Their names seem to be how this is presented.

Oh, watching the clip...

It would be nice if I could read the addresses in the URL at the top of the browser.
See attached.
I know it is my problem, but I don't get why watching youtube on the NUC (i5 quad core) with a lot of memory the picture shown is not clear.

Screenshot%20from%202018-07-07%2020-21-21
Screenshot from 2018-07-07 20-21-21.jpg1360Ă—768 244 KB

12

Learning basic SQL is going to help a lot:

https://www.w3schools.com/sql/default.asp

I, like Colin, would recommend InfluxDB with Grafana for storing and displaying data.

13

ARGH!

(OK, not on the RPI - it is somewhat busy with another compiling for python)

I'm on my NUC and a LOT of tabs at the top of the middle part of the screen.

I keep them separate so I can keep things grouped. In doing this, I have imported a lot of "test" FLOWs just now.

One of the tabs is the one with SQlite example. It gives an error about:

"failed to open /home/me/sqlite/nodered"

Ok, what ever. I open the edit part and DISABLE that whole tab.

The errors keep coming up.

I know I am getting off topic, but I am not wanting to have to re-copy/paste when/if I get back to this. So I thought that disabling the tab would work.

Seems not.

(I actually have ALL the tabs I made today disabled, so there is really no chance of anything done today working.)

14

If you google for it then it will tell you what it is. You don't need to install it if you don't want to, there are other ways of achieving the same thing.

15

Colin,

Maybe. I can't say. Where I am on the curve, it is all a foreign language written for people who already know the answer.

(I just posted that my Node-Red is being bombarded with errors. I even went to the extent of actually deleting all the tabs I made today and I am STILL getting the error.)

I can't see how they are talking about SQL data bases and then suddenly talk about PHP stuff and call up new tabs with it running.

I am not getting what they are trying to do and trying to put it together with no understanding is painful.

I think I have to cut my losses for now and try to get this NR error removed.

16

First check that it doesn't say anything about unused config nodes when you deploy. If it does then delete them.
Otherwise you should be able to click on the node id on the line above the error (assuming that is in the debug tab) and it should take you to the node giving the error.

On SQLite I am afraid you can't just expect to start using a tool like that without several hours of learning first. Look for a beginners tutorial on using sqlite, if sqlite is what you want to use.

17

  1. To get rid of the messages, try stopping and restarting NR

  2. are you running NR with the user "me"? as seen in your error message:
    "failed to open /home/me/sqlite/nodered"

  3. do you have a folder called 'sqlite' under your user 'me'?

18

zenofmud,

Yes, I do.

Other things happened and I rebooted.

The problem has gone away.

I'll update the other thread now.

19

Yes, I do.

what question are you answering with this?

20

Sorry: Overload.

Yes I have a directory /home/me/sqlite/nodered

pwd
/home/me/sqlite/nodered

I think I have overloaded my brain today. May need to give it a rest today and come at it again tomorrow.