Tag: Influxdb

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:

1. nrdb – the InfluxDB database
2. mytemps – the measurement variable for my temperatures
3. Burlington, Hamilton – two locations for the temperatures
4. 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.