Project: ESP32 -> MQTT -> Node-Red -> InfluxDB -> Grafana

Well, now. What an unexpected, interesting and helpful email.
Thank you Nick!

I have been involved on-and-off for many years in IT but mostly my career has been teaching pure maths, physics, etc. So I get rusty and find myself soon left behind with recent software developments. Yes, almost all of what I’m trying to do is new to me, but I’m not a bad learner and although it now takes longer to get to grips with stuff, I can usually get there.

I’m a great fan of MySql and similar databases. I find them simple, straighforward and easy to get up-and running. I have spent many an hour (plural, many, many hours) trying to work with influxDB and for some time had the feeling I’d landed on Mars with locals wondering why I couldn’t grasp Martian. Or through the Looking Glass trying to think 6 impossible things before breakfast.

I came across a website where I found I could load data manually into InfluxDB and the resulting graph was very impressive. That was the main reason for sticking it out. The journey you describe is one I’ve already largely undertaken. Here are some very brief comments to your points:

Yes, I’ve used IOTstack, having first come across it via GitHub; the same as your link (1). It floored me: whilst most packages installed correctly, InfluxDB would not. (see my post here: Login to new Influxdb 2 installation). I also didn’t like the fact that Portainer, one of the first packages to get up and running, couldn’t take control of everything because stacks, volumes, etc, had been created elsewhere. In the end I went for a CLI install of InfluxDB in Docker; all the other packages are still via IOTstack. Installing InfluxDB has consistently installed version 2.7.5 irrespective of whether I choose InfluxDB or InfluxDB2 in IOTstack.

(2) I installed InfluxDB2 without understanding the implications, simply on the naive belief that newer was better. Flux=Martian (at least to me). And (3), yes, I hope 1.8 will be easier bacause I’m OK with SQL. I say “will be” because I’ve given the MQTT->NodeRed->InfluxDB work a bit of a rest, instead I’m re-writing code for the ESP32 to be more event/interrupt driven and removing all blocking code.

(4) I’ve come across Andreas Speiss and it was his videos that encouraged me to persist with IOTstack. I’ve used quite a few times the PiBuilder script he encourages and I’m impressed it runs through to completion without a single error. I use a snapshot of The Raspberry Pi installation (when PiBuilder completes) to restart/reinstall quickly from a solid base.

(5), yep done that.

(6) haven’t yet tried to use batch mode. The advice is to get a single Influx-Out node working first and do the batch stuff when more fluent. I’ve yet to get anything out of Node-Red and into InfluxDB, even with a manual Inject node.

(7) is definitely an achilles heel for me. I really do need to get to grips with that. I understand the structure (somewhat, but not enough) and the distinction between fields and tags but there’s a huge psychological difficulty to overcome: embracing the new constructs and leaving conventional pre-declared fields and primary/secondary indexes to one side. It all seems rather fluid and that’s ‘unsettling’.

(8) The raw data handling in the ESP32 is by structs (peripherals → controller using ESP-NOW). Within the controller, sending data to a web server uses JSON document format. For some reason I didn’t do that for sending data to MQTT. Why? Dunno, perhaps some random cosmic ray hitting a neuron. But that’s easy to reconfigure. In Node-Red I can see data arriving with the topic and value which, when run through a function and change node, yield a “field” name and number. And the Rasperry Pi MQTT instance works just the same as the public EMQX broker. So that part seems OK.

… it should be easy to pass to InfluxDB. Except it hasn’t been:

I shall try your sample flow and maybe I’ll see what I’m doing wrong. I suspect I have mangled the data at some point or failed to adhere to the field/tag structure. If your flow works it will be the first occasion I’ve been able to send data from Node-Red to InfluxDB. If it doesn’t work then something’s wrong with the configuration of the containers and something’s not communicating.

It will be the weekend before I get back to this. Demands at home mean I need to do some plumbing and plastering. Which is wonderful because then only my back hurts rather than my head.

Thanks again, I’ll get back to you.