InfluxDB

From Torben's Wiki

Install on Raspberry Pi in Raspbian

Nice Howto: [1]

wget -qO- https://repos.influxdata.com/influxdb.key | sudo apt-key add -
source /etc/os-release
test $VERSION_ID = "7" && echo "deb https://repos.influxdata.com/debian wheezy stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
test $VERSION_ID = "8" && echo "deb https://repos.influxdata.com/debian jessie stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
test $VERSION_ID = "9" && echo "deb https://repos.influxdata.com/debian stretch stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
sudo apt-get install influxdb
sudo service influxdb restart

in /etc/influxdb/influxdb.conf

[http]
# Determines whether HTTP endpoint is enabled.
enabled = true
# require login of user
auth-enabled = true 

in ~/.bashrc

export INFLUX_USERNAME=admin
export INFLUX_PASSWORD=password1

Runs per default on port 8086

CLI: command line interface

sudo apt-get install influxdb-client
influx -precision rfc3339 # for human readable time format
# or 
influx
precision rfc3339

Administration

user permissions are defined per database, so if you need only one level of permissions, one database might be enough

CREATE DATABASE raspi
USE raspi

suggestion: one user per permission

create user uadmin with password 'password1' WITH ALL PRIVILEGES
create user uwrite with password 'password2'
create user uread  with password 'password3'
grant read on raspi to uread
grant write on raspi to uwrite
SHOW DATABASES
SHOW MEASUREMENTS
SHOW RETENTION POLICIES
# display the field types if table1
SHOW FIELD KEYS FROM table1

# keep data for 10 years instead of 6 days (default)
CREATE RETENTION POLICY "years10" ON raspi DURATION 520w REPLICATION 1 DEFAULT
DROP   RETENTION POLICY "years10" ON raspi
# if switching the default RETENTION POLICY old data will require a specific select
# altering the default RP:
ALTER RETENTION POLICY autogen ON raspi DURATION 90d SHARD DURATION 1d REPLICATION 1 DEFAULT
ALTER RETENTION POLICY autogen ON collectd DURATION 7d SHARD DURATION 1d REPLICATION 1 DEFAULT

Backup and Restore

influxd backup  -portable mypath
influxd restore -portable mypath

Renaming a Database via creating a copy of its tables

!!!Attention: this will convert tags to fields!!!

CREATE DATABASE mydb2
SELECT * INTO mydb2.autogen.table1 from mydb1.autogen.table1 group by *
DROP DATABASE mydb1

Test Connection via CURL

curl -G http://raspi3:8086/query -u uwrite:password2 --data-urlencode "q=SHOW DATABASES"

Insert via REST API and CURL

# precision=s (second) is important for performance, if higher accuracy is needed use ms, not ns which is the default!)
VALUE="123.123"
curl -i \
       -u uwrite:password2 \
       -XPOST "http://localhost:8086/write?db=raspi&precision=s" \
       --data-binary "myTable,myTag=SourceA myValue=$VALUE"

INSERT via Python module InfluxDBClient

see [2]

from influxdb import InfluxDBClient
client = InfluxDBClient(host='192.168.178.31', port=8086, username='write', password='password2 ')
# client.create_database('raspi')
print(client.get_list_database())
client.switch_database('raspi')

json = [
    {
        "measurement": "brushEvents",
        "tags": {
            "user": "Carol",
            "brushId": "6c89f539-71c6-490d-a28d-6c5d84c0ee2f"
        },
        "time": "2018-03-28T8:01:00Z",
        "fields": {
            "duration": 127
        }
    },
    {
        "measurement": "brushEvents",
        "tags": {
            "user": "Carol",
            "brushId": "6c89f539-71c6-490d-a28d-6c5d84c0ee2f"
        },
        "time": "2018-03-29T8:04:00Z",
        "fields": {
            "duration": 132
        }
    }
]

if client.write_points(json, time_precision="s") != True:
    print ("ERROR: Write to InfluxDB not successful")
else: 
    print ("data sent")

UPDATE data via overwrite and Python

see https://github.com/entorb/raspi-sensorics/blob/main/influx_value_update.py

result = client.query('SELECT * FROM myMeas WHERE "total" > 1000')
for p1 in result.get_points():
    fields = {"total": round(p1["total"] / 1000, 3)}
    p2 = [
        {
            "measurement": "myMeas",
            "time": p1["time"],
            "tags": {"room": p1["room"]},  # ensure to include all tags
            # it is fine to modify just one fields/value, the others will keep their values
            "fields": {"total": round(p1["total"] / 1000, 3)},
        },
    ]
    # overwrite data
    client.write_points(p2)

SELECT statement

precision rfc3339
# for human readable timeformat
select * from mymeasurement

Timefilter

WHERE time > now() - 3d

Select list of tags or fields

SHOW TAG   KEYS FROM myMeasurement
SHOW FIELD KEYS FROM myMeasurement

for example to be used in Grafana variables of type Query

SHOW TAG VALUES FROM myMeasurement WITH KEY =~ //