InfluxDB with Node-Red

February 1, 2020April 7, 2021Posted in Node-Red, Raspberry PiTagged Influxdb, IoT, Node-Red, NodeRed

There are a lot of excellent databases out there. Almost all databases can support time tagged information and if you have regularly sampled data everything works well. However if you have irregularly sampled data things can get a little more challenging.

InfluxDB is an open-source time series database (TSDB). It is written in Go and optimized for fast, high-availability storage and retrieval of time series data in fields such as operations monitoring, application metrics, Internet of Things sensor data, and real-time analytics.

InfluxDB has a number of great features:

when data is added, a time stamp is automatically added if it’s already incluced.
InfluxDB manages aggregation of times (i.e. means over the hour)
Open Source Web Trending packages like Grafana and Chronograf will talk directly to InfluxDB
an SQL language with a time based syntax

In this blog I wanted to document my notes on:

How to add sampled data from Node-Red to Influx
How to view Influx historical data in a Node-Red chart

Why Use Node-Red with Influx

With the great Web trending interfaces like Grafana and Chronograf why use Node-Red?

I really like Grafana, but I didn’t find it to be 100% mobile friendly, whereas Node-Red is designed for mobile use.
if you’re inputting data or doing logic in Node-Red it makes sense to keep the interface logic there also.

The downside of using Node-Red is that you will have to make your own charting controls.

Getting Started with InfluxDB

The official installation document lists the various options based on your OS. For a simple Raspberry Pi or Ubuntu installation I used:

sudo apt-get install influxdb

The influxdb configuration/setup is modified by:

sudo nano /etc/influxdb/influxdb.conf

After configuration changes Influx can be restarted by:

sudo service influx restart

The Influx command line interface (CLI) is useful for getting started and checking queries. It is started by entering: influx (Note: it might be slow to initially come up).

Below I’ve opened the influx CLI and created a new database called nrdb.

~$ influx
Connected to http://localhost:8086 version 1.7.9
InfluxDB shell version: 1.7.9
> create database nrdb
> show databases
name: databases
name
----
_internal
pidata
nrdb
>

Node-Red and Influx

Node-Red is pre-installed on Raspberry Pi. If you need to install Node-Red on a Window, MacOS or Linux node see the installation instructions.

For my testing I used the following definitions:

nrdb – the InfluxDB database
mytemps – the measurement variable for my temperatures
Burlington, Hamilton – two locations for the temperatures
temperatures – the actual temperatures

Two Node-Red libraries were installed:

BigTimer – https://flows.nodered.org/node/node-red-contrib-bigtimer
InfluxDB – https://flows.nodered.org/node/node-red-contrib-influxdb

These libraries can either be installed using npm or within Node-Red using the “Manage Pallet” option.

For this project I create two sets of logic. The first set used the BigTimer to write a new simulated input every minute (via the middle output pin of BigTimer), or manual push in a value. The second part of the logic used a selected time to query the data and present it to a chart and table.

The first step is to drop a InfluxDB outpt and then configure the Influx server, table and measurements.

A Javascript function node (“Simulate an Input”) is used to format the fields and values. The first passed item is the key item, and the second parameter is a tagged value. Note: there are a number of different ways to use this node.

The Big Timer middle output will send a value out every minute. I added an Inject Node (“Force Test”) so I could see more values.

To test that things are running, the influx cli can be used:

> use nrdb
Using database nrdb
> show measurements
name: measurements
name
----
mytemps

> select * from mytemps
name: mytemps
time location temperature
---- -------- -----------
1580584703785817412 Burlington 17
1580584706364427345 Burlington 5
1580584761862704310 Burlington 8

Show Influx Data in a Node-Red Dashboard

For a simple Dashboard I wanted to use a dropdown node (as a time selector), a chart and a table.

The drop down node has a selection of different times.

The payload from the dropdown node would be something like: 1m, 5m, 15m. A Javascript function node (“New Time Scale”) used this payload and created an InfluxDB query.

This syntax can be tested in the influx cli:

> select time,temperature from mytemps where location='Burlington' and time > now() - 5m
name: mytemps
time temperature
---- -----------
1580588829859372644 12
1580588889896729245 6
1580588949931621672 17
1580589009972333308 8
1580589069980649689 12

The InfluxDB input node only has the InfluxDB server information. The query is passed in from the Javascript function node (“New Time Scale”) .

A Javascript function node (“Javascript function node (“Format Influx Results”) is used to put the msg.payload into a format that the chart node can use.


//
// Format the InfluxDB results to match the charts JSON format
//

var series = ["temp DegC"];
var labels = ["Data Values"];
var data = "[[";
var thetime;

for (var i=0; i < msg.payload.length; i++) {
    thetime = Number(msg.payload[i].time); // Some manipulation of the time may be required
    data += '{ "x":' + thetime + ', "y":' + msg.payload[i].temperature + '}';
    if (i < (msg.payload.length - 1)) {
        data += ","
    } else {
        data += "]]"
    }
}
var jsondata = JSON.parse(data);
msg.payload = [{"series": series, "data": jsondata, "labels": labels}];
return msg;


Once all the logic has been updated, click on the Deploy button. The Node-Red dashboard can be accessed at: http://node-red_ip:1880/ui. Below is an example:

Final Comments
This project was not 100% there are still some cleanup items to do, such as:

use real I/O
make the times a little cleaner in the table
a better time selections for the chart.

Also to better explain things I only used 1 location but multiple data points could be inserted, queried and charted.
Share this:
Twitter
Facebook
Like Loading...

26 thoughts on “InfluxDB with Node-Red”

Pingback: Influx, Grafana and Node-Red on Ubuntu 16.04 server – Skjoldtech
Antonius says:

September 3, 2020 at 7:21 am

“The drop down node” has wrong Node snapshot….

LikeLike

Reply
Antonius says:

September 4, 2020 at 10:47 am

On “Format Influx Results”, line-21, is written “msg.playload = data;”…. (“play”…. ???)

LikeLike

Reply
Pete Metcalfe says:

September 7, 2020 at 11:08 am

Thanks for catching that. I corrected the page.

LikeLike

Reply
Anonymous says:

October 30, 2020 at 11:44 am

Hey!
Thanks for this tutorial it was really helpful.
As I followed the instructions I noticed some missing/wrong informations:
– As a fresh install node-red doesnt have the dashboard node so the newbies should install it as well.
– The function node : “Format Influx Results” js code shouldn’t contain the ,,msg.payload = data;” line (line 21). I debugged to get over it about an hour 😀
– You missed the bigtimer’s configuration and many details on multiple items

LikeLike

Reply
1. Pete Metcalfe says:
  
  November 1, 2020 at 11:16 am
  
  Hey thanks for comments and feedback.I apologize sometimes I get a litt
  
  LikeLike
  
  Reply
  1. thanosmoh (@thanosmoh) says:
    
    November 6, 2020 at 11:58 am
    
    Hello sir, thanks for the good work you done. May I ask your help I have a problem with the (Format the InfluxDB results node) it show actully 3 errors and could not deploy the flow like for example (
    for (var i=0; i < msg.payload.length; i++)) I don’t think this code is right (for statement )take 3 parameter but in your code it has 4! also i got error in this section of the code ( if (i < (msg.payload.length – 1)) {
    data += “,”
    } else {
    data += “]]”
    }}
    where is the condition ??
    Please cloud you rewrite the code again because it dose not deploy to node red
    
    thanks in advance
    
    LikeLike
thanosmoh (@thanosmoh) says:

November 6, 2020 at 12:10 pm

I mange to solve the error message I got it in your code but and mange also tp deploy the flow ,, and got only this error in the debug node(TypeError: Cannot read property ‘time’ of undefined) could you please help me .

LikeLike

Reply
1. Ciprian Mateiuc says:
  
  November 28, 2020 at 6:49 am
  
  Hi, can you share the fow? thanks
  
  LikeLike
  
  Reply
  1. Pete Metcalfe says:
    
    November 28, 2020 at 7:08 pm
    
    Hi,Unfortunately we had a power surge and my Rasp Pi wasn’t plugged into our surge suppressor. I’m still trying to getting things back. Pete
    
    LikeLike
Pavel Ženíšek says:

February 12, 2021 at 3:02 am

Hi, great manual, many thanks. Your function was broken so I tried to correct it. Here is my repair.

//
// Format the InfluxDB results to match the charts JSON format
//
var series = global.get(“Graf”);
var labels = [“Data Values”];
var data = “[“;
var thetime;

for (var i=0; i < msg.payload.length; i++) {
thetime = Number(msg.payload[i].time); // Some manipulation of the time may be required
data += '{ "x":' + thetime + ', "y":' + msg.payload[i].value + '}';
if (i < (msg.payload.length – 1)) {
data += ","
} else {
data += "]"
}
}
var jsondata = JSON.parse(data);
msg.payload = [{"series": [series], "data": [jsondata], "labels": labels}];
//msg.payload = data;
return msg;

LikeLike

Reply
1. Pavel Ženíšek says:
  
  February 12, 2021 at 3:11 am
  
  And for easier implementation here is my flow – I added the possibility of changing the input data for graph.
  
  [{“id”:”aec81712.e94938″,”type”:”tab”,”label”:”Chart”,”disabled”:false,”info”:””},{“id”:”1d747478.a70a3c”,”type”:”ui_dropdown”,”z”:”aec81712.e94938″,”name”:””,”label”:”Rozpětí”,”tooltip”:””,”place”:”Select option”,”group”:”6da8e969.ae2c18″,”order”:1,”width”:0,”height”:0,”passthru”:true,”multiple”:false,”options”:[{“label”:”1 Minuta”,”value”:”10m”,”type”:”str”},{“label”:”45 Minut”,”value”:”45m”,”type”:”str”},{“label”:”1 Hodina”,”value”:”1h”,”type”:”str”},{“label”:”24 Hodin”,”value”:”24h”,”type”:”str”},{“label”:”1 den”,”value”:”1d”,”type”:”str”},{“label”:”2 dny”,”value”:”2d”,”type”:”str”},{“label”:”3 dny”,”value”:”3d”,”type”:”str”},{“label”:”7 dní”,”value”:”7d”,”type”:”str”},{“label”:”28 dní”,”value”:”28d”,”type”:”str”}],”payload”:””,”topic”:””,”x”:130,”y”:240,”wires”:[[“a8c01471.ced5c8″,”18f1281.19f48d8”]]},{“id”:”a8c01471.ced5c8″,”type”:”function”,”z”:”aec81712.e94938″,”name”:””,”func”:”var graf = global.get(\”Graf\”);\n\n\nmsg.query = \”select time,\”+ graf+\” from xiaomi WHERE time > now() -\” + msg.payload;\nreturn msg;”,”outputs”:1,”noerr”:0,”initialize”:””,”finalize”:””,”x”:280,”y”:240,”wires”:[[“4bca8937.9bb208”]]},{“id”:”4bca8937.9bb208″,”type”:”influxdb in”,”z”:”aec81712.e94938″,”influxdb”:”dd090ea0.54c55″,”name”:”Xiaomi”,”query”:””,”rawOutput”:false,”precision”:””,”retentionPolicy”:””,”org”:”organisation”,”x”:410,”y”:240,”wires”:[[“d14a2855.5e3cf8”]]},{“id”:”8c90f757.87ba68″,”type”:”function”,”z”:”aec81712.e94938″,”name”:””,”func”:”//\n// Format the InfluxDB results to match the charts JSON format\n//\nvar series = global.get(\”Graf\”);\nvar labels = [\”Data Values\”];\nvar data = \”[\”;\nvar thetime;\n\nfor (var i=0; i < msg.payload.length; i++) {\n thetime = Number(msg.payload[i].time); // Some manipulation of the time may be required\n data += '{ \"x\":' + thetime + ', \"y\":' + msg.payload[i].value + '}';\n if (i < (msg.payload.length – 1)) {\n data += \",\"\n } else {\n data += \"]\"\n }\n}\nvar jsondata = JSON.parse(data);\nmsg.payload = [{\"series\": [series], \"data\": [jsondata], \"labels\": labels}];\n//msg.payload = data;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":720,"y":320,"wires":[["f9137346.2a8a5"]]},{"id":"f9137346.2a8a5","type":"ui_chart","z":"aec81712.e94938","name":"","group":"6da8e969.ae2c18","order":1,"width":"6","height":"4","label":"","chartType":"line","legend":"false","xformat":"dd HH:mm","interpolate":"cubic","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"60","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#ff0000","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#ff0000","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":850,"y":320,"wires":[[]]},{"id":"d14a2855.5e3cf8","type":"split","z":"aec81712.e94938","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":590,"y":160,"wires":[["9d09ba8c.43cee8"]]},{"id":"9d09ba8c.43cee8","type":"split","z":"aec81712.e94938","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":590,"y":200,"wires":[["9b4e3bfa.af55b8"]]},{"id":"1c9ba47a.02982c","type":"join","z":"aec81712.e94938","name":"","mode":"custom","build":"merged","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":590,"y":280,"wires":[["15032d36.6ca513"]]},{"id":"9b4e3bfa.af55b8","type":"function","z":"aec81712.e94938","name":"","func":"var data = msg.payload;\nif (typeof msg.payload === 'number') {\nmsg.payload =\n{value: data}\nreturn msg\n\n} \n\nelse {\nmsg.payload =\n{time: data}\n return msg\n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":600,"y":240,"wires":[["1c9ba47a.02982c"]]},{"id":"15032d36.6ca513","type":"join","z":"aec81712.e94938","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":590,"y":320,"wires":[["8c90f757.87ba68"]]},{"id":"f93ade6c.b8baa","type":"ui_dropdown","z":"aec81712.e94938","name":"","label":"Data","tooltip":"","place":"Select option","group":"6da8e969.ae2c18","order":1,"width":0,"height":0,"passthru":true,"multiple":false,"options":[{"label":"Teplota1","value":"temp1","type":"str"},{"label":"Teplota2","value":"temp2","type":"str"},{"label":"Vlhkost1","value":"hum1","type":"str"},{"label":"Vlhkost2","value":"hum2","type":"str"}],"payload":"","topic":"","x":130,"y":200,"wires":[["973ac92c.b613d8","1c1d67d5.22eb88"]]},{"id":"973ac92c.b613d8","type":"change","z":"aec81712.e94938","name":"","rules":[{"t":"set","p":"Graf","pt":"global","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":200,"wires":[[]]},{"id":"1c1d67d5.22eb88","type":"change","z":"aec81712.e94938","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"Rozpeti","tot":"global"}],"action":"","property":"","from":"","to":"","reg":false,"x":240,"y":100,"wires":[["1d747478.a70a3c"]]},{"id":"18f1281.19f48d8","type":"change","z":"aec81712.e94938","name":"","rules":[{"t":"set","p":"Rozpeti","pt":"global","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":280,"wires":[[]]},{"id":"6da8e969.ae2c18","type":"ui_group","name":"Graf","tab":"aa83e694.8c85d8","order":1,"disp":true,"width":"6","collapse":true},{"id":"dd090ea0.54c55","type":"influxdb","hostname":"192.168.0.101","port":"8086","protocol":"http","database":"home","name":"Influx","usetls":false,"tls":"","influxdbVersion":"1.x","url":"http://localhost:8086","rejectUnauthorized":true},{"id":"aa83e694.8c85d8","type":"ui_tab","name":"GRAF","icon":"timeline","order":2,"disabled":false,"hidden":false}]
  
  LikeLike
  
  Reply
  1. Pete Metcalfe says:
    
    February 13, 2021 at 12:38 pm
    
    Hi Pavel,
    Thank you very much for sharing your code. Really appreciate it. All the best. Pete
    
    LikeLike
Volker Wirth says:

February 14, 2021 at 5:46 am

Hello Pavel
and thanks for sharing your code.
Hope you can help me. If i try to import your flow, i get an error:

SyntaxError: Unexpected token “ in JSON at position 2
[{“id”:”aec817

…the quotation marks in front of id are marked with a red square.
Do you have any idea?

So I tried only to copy your “repair”-code into my function-node
what shows me multiple errors.
What version of node red do I need to use your code and example?

Best
Volker

LikeLike

Reply
1. Pavel Ženíšek says:
  
  February 15, 2021 at 12:59 am
  
  Hi, try this JSON file, i m not sure where is problem, even i cant import shared flow but it looks ok.
  https://drive.google.com/file/d/18AYr1vaq5ABllSOjbjF_hoTgIwkjhBxK/view?usp=sharing
  
  LikeLike
  
  Reply
  1. Volker Wirth says:
    
    February 15, 2021 at 1:25 am
    
    HI Pavel
    and thanks for your fast response. A few hours ago, i found the solution.
    The problem are the quotation marks. If i copy and paste anything from this page to node-red the quotation marks are “converted” in some different version of quotation marks. They are looking nearly like yours, but they are not the same.
    Maybe this is a problem with my german keyboard layout.
    Once I found this Problem the solution was easy. I just deleted all ouf your quotation marks and typed them in again.
    Perfekt – everything is running fine now and helped me a lot with my first steps in node-red and in programming the nodes.
    Have a nice day
    Volker
    
    LikeLike
Fredrik Lager says:

March 24, 2021 at 5:57 am

Hello, I’m trying to write a value from an home assistant enitiy to the influxdb. If I try your random value example above it works. But how can I write an actual measurement from an home assistant sensor i.e?
Shall I pull the current value via a node-red current state box before the function or can I in some way just define the msg.payload in the function box to be just: msg.payload = {temperature:my entity}; ?

I don’t really understand the full syntax yet so it could be really simple solution to this 🙂

LikeLike

Reply
1. Fredrik Lager says:
  
  March 24, 2021 at 6:31 am
  
  Solved it by
  var theval = msg.payload
  msg.payload = {el_consump:theval};
  return msg;
  
  LikeLike
  
  Reply
  1. Pete Metcalfe says:
    
    March 24, 2021 at 10:34 am
    
    Hi Fredrik,
    I found that working with InfluxDB on Home Assistant to be a little challenging. To allow me to view and manage InfluxDB I loaded the InfluxCLI (commandline client) on a separate PC.
    
    To push an HA sensor value into InfluxDB you can use the NodeRed “current state” node. If you want to do a schedule write add a “BigTimer” node in front.
    
    I hope that this helps.
    Pete
    
    LikeLike
Jens says:

May 18, 2021 at 8:05 am

Hi,

Have run a SQLite db and look for a way to use this
„The payload from the dropdown node would be something like: 1m, 5m, 15m. A Javascript function node (“New Time Scale”) used this payload and created an InfluxDB query.„
I don’t find an way to use select xxx xxxx where time > now() with SQLite.

Regards

LikeLike

Reply
1. Pete Metcalfe says:
  
  May 18, 2021 at 9:25 am
  
  Hi,
  So if I understand you have adjustable time working. But you want a way to adjust the fields?
  
  I might try to modify the payload format. In a Javascript function node:
  
  msg.payload = {
  “newtime”: “1m”,
  “fields”: “myfield1,mfield2″,
  }
  return msg;
  
  Note: the above is hardcoded to test, you’d pass in the field selection from from a selector.
  
  Then in the 2nd function node:
  
  msg.query=”select ” + msg.payload.fields + ” from mytable where ” time > now – ” + msg.payload.newtime
  
  LikeLike
  
  Reply
Jens says:

May 19, 2021 at 1:41 am

hi, thx.
but this will not work for sqlite
sqlite> select time,leistung from solarstrom where time > now -1 minute;
Error: near “minute”: syntax error
sqlite> select time,leistung from solarstrom where time > now -1min
…> ;
Error: unrecognized token: “1min”
sqlite> select time,leistung from solarstrom where time > now -1m;
Error: unrecognized token: “1m”
sqlite> select time,leistung from solarstrom where time > now -1;
Error: no such column: now
sqlite> select time,leistung from solarstrom where time > now -1h;
Error: unrecognized token: “1h”
sqlite> select time,leistung from solarstrom where time > now ‘-1h’;
Error: near “‘-1h'”: syntax error
sqlite> select time,leistung from solarstrom where time > now -1h;
Error: unrecognized token: “1h”
sqlite> select time,leistung from solarstrom where time > now -1 hour;
Error: near “hour”: syntax error
sqlite> select time,leistung from solarstrom where time > now – 1hour;
Error: unrecognized token: “1hour”
sqlite> select time,leistung from solarstrom where time > now – 1 hour;
Error: near “hour”: syntax error
sqlite> select time,leistung from solarstrom where time > now;
Error: no such column: now

I’ll look for this syntax at sqlite.

regards

LikeLike

Reply
1. Pete Metcalfe says:
  
  May 19, 2021 at 9:39 am
  
  Hi, This SQL statement is for InfluxDB.
  
  See: https://sqlite.org/lang_datefunc.html
  Section 4 has some Sqlite examples.
  
  The date() and time() functions have modifiers that allow you to go ‘+’ or ‘-‘ from a specific time or from ‘now’.
  
  LikeLike
  
  Reply
Jens says:

May 19, 2021 at 10:18 am

Hi, thx.
Try to understand but nothing will work expected.
Regards

LikeLike

Reply
Pingback: Viessmann API und Node-Red – Teil 6 | Rustimation.eu
Pingback: Viessmann API und Node-Red – Teil 6b | Rustimation.eu