InfluxDB
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 =~ //