-database name |
connect host:port | |||||||||||||||||||||||||||
SQL-like query language named InfluxQL
ERR: no data received The database is notrunning
Measurement names, tag keys, tag values, field keys, and field values are case sensitive.
Numerical field values are floats unless they include a trailing i
Use " (quotes) not ' (apostrophes)
(don't use tz('EST')
use America/New_York or something from this list.
Not timestamps nor boolean (true, false)
Keywords
ALL |
SELECT COUNT( * | field_key | field_key,tag_key | /regular_expression/ )
[INTO clause]
FROM clause
[ WHERE clause]
[ GROUP BY clause]
[ ORDER BY time _clause]
[ LIMIT clause]
[ OFFSET_clause]
[ SLIMIT clause]
[ SOFFSET clause]
[ tz('time_zone') ]
For field_keys and tag_key with the same name use: _key ::[field|tag ]
Time = , <> ,
!= , > , >= , < or <=
All time in InfluxDB is UTC.
'yyyy-mm-dd[Thh:mm:ss[.thmuuunnnZ']'yyyy-mm-dd[ hh:mm:ss[.thmuuunnn †]'
Example:
… time > '2019-03-26 17:00:00' and time <'2019-03-26 17:01:00' …time > '2019-03-26T17:00:08.960Z' limit 1 time floor ---- ----- 2019-03-26T17:00:08.964Z 44 … time > '2019-03-26T17:00:08.965Z' limit 1 time floor ---- ----- 2019-03-26T17:00:19.062Z 44
duration_unit : w d h m s ms u ns
now() - 7d
where does not support or for multiple time ranges.
Eastern Standard Time zone) of the most recent (last) temperature
influx -precision rfc3339 -database ruuvifrom a specfic ruuvi tag.
select mac,ceil(temperature*9/5)+32 from ruuvi_measurements where time > now() -2m group by mac tz('America/New_York')
select temperature from ruuvi_measurements where time > now() - 15m AND "mac"='D3517872EC0F' limit 10
select temperature from ruuvi_measurements where time > '2019-02-19' AND "mac"='D3517872EC0F' limit 10
select temperature from ruuvi_measurements where time > '2019-02-19T04:08:00Z' AND "mac"='D3517872EC0F' limit 10
select mac,ceil(temperature*9/5)+32 from ruuvi_measurements where time > group_by mac tz('America/New_York')
influx -precision rfc3339 -database ruuvi -execute "select last(temperature)*9/5+32 from ruuvi_measurements where mac='DC4B596ED18B' tz('America/New_York')"
non-interactive query database "metrics" and pretty print json:
influx -database 'metrics' -execute 'select * from cpu' -format 'json' -pretty
Connect to a specific database on startup and set database context:(to defaults)
influx -database 'metrics' -host 'localhost' -port '8086'
influx -precision rfc3339 -database ruuvi
select mac,ceil(pressure/100),ceil(batteryVoltage*100)/100,ceil(temperature*9/5)+32,ceil(humidity) from ruuvi_measurements where time > 154623102547100000 group by mac tz('America/New_York')
influx commane Line Intrepreter runs a go program to directly query the database.(takes a while)
cURL connects to the running database engine
Either can be run from a remote system
curl --silent --show-error --get 'http://pi93graf:8086/query?' --data-urlencode "db=ruuvi" --data-urlencode \
"q=SELECT mac,last(temperature) FROM ruuvi_measurements where mac='DC4B596ED18B' limit 3 tz('America/New_York') "
{"results":[{"statement_id":0,"series":[{"name":"ruuvi_measurements",
"columns":["time","mac","last"],
"values":[["2019-05-22T01:43:53.159467957-04:00","DC4B596ED18B",16.18]]}]}]}
Use
sed 's/},{/\n/g; s/"//g; s/\[//g ; s/]//g; s/name:ruuvi_measurements,tags://g; s/columns://g; s/values://g ; s/{mac://g; ; /^ *$/d'
to get it tobe more human readable.
curl --silent --show-error --get 'http://localhost:8086/query?pretty=true' --data-urlencode "db=ruuvi" --data-urlencode "q=SELECT rssi FROM ruuvi_measurements limit 3"
{
"results": [
{
"statement_id": 0,
"series": [
{
"name": "ruuvi_measurements",
"columns": [
"time",
"rssi"
],
"values": [
[
"2018-03-25T00:23:19.564Z",
-54
],
[
"2018-03-25T00:23:23.63Z",
-59
],
[
"2018-03-25T00:23:28.299Z",
-50
]
]
}
]
}
]
}
To make this more human readable :
sed 's/},{/\n/g; s/"//g; s/\[//g ; s/]//g; s/name:ruuvi_measurements,tags://g; s/columns://g; s/values://g ; s/{mac://g'
curl --get 'http://localhost:8086/query?' --data-urlencode "db=ruuvi" --data-urlencode "q=SELECT rssi FROM ruuvi_measurements limit 3"
{"results":[{"statement_id":0,"series":[{"name":"ruuvi_measurements",
"columns":["time","rssi"],
"values":[["2018-03-25T00:23:19.564Z",-54],["2018-03-25T00:23:23.63Z",-59],["2018-03-25T00:23:28.299Z",-50]]}]}]}
influx -precision rfc3339 -database ruuvi
Connected to http://localhost:8086 version 1.7.4
InfluxDB shell version: 1.7.6 as of 4/25/19 Raspberry Pi
use ruuvi
show FIELD KEYS
name: ruuvi_measurements
fieldKey fieldType
-------- ---------
These calculated values are included by default with ruuvi-collector.properties:storage.values=extended, excluded with =raw
absoluteHumidity float
accelerationAngleFromX float
accelerationAngleFromY float
accelerationAngleFromZ float
accelerationTotal float
accelerationX float
accelerationY float
accelerationZ float
batteryVoltage float
airDensity float
dewPoint float
equilibriumVaporPressure float
humidity float
measurementSequenceNumber integer
movementCounter integer
pressure float
rssi integer
temperature float
txPower integer
influx -precision rfc3339 -database ruuvi # rfc3339 uses better time display
select mac,ceil(temperature*9/5)+32 from ruuvi_measurements where time > '2019-03-26 17:00:08' group by mac limit 10 tz('America/New_York')
name: ruuvi_measurements
tags: mac=D3517872EC0F
time mac ceil
---- --- ----
2018-12-28T13:34:11.836-05:00 D3517872EC0F 56
2018-12-28T13:34:21.946-05:00 D3517872EC0F 56
2018-12-28T13:34:32.029-05:00 D3517872EC0F 56
name: ruuvi_measurements
tags: mac=E9383FDD20BC
time mac ceil
---- --- ----
2018-12-28T13:34:20.964-05:00 E9383FDD20BC 75
2018-12-28T13:34:31.116-05:00 E9383FDD20BC 75
2018-12-28T13:34:41.268-05:00 E9383FDD20BC 75
select LAST(temperature) from ruuvi_measurements where mac='DC4B596ED18B' tz('America/New_York') (Not ET, not EDT )
name: ruuvi_measurements
time last
---- ----
2018-12-03T20:40:28.217-05:00 24.41
select ceil((temperature*9/5)+32) from ruuvi_measurements where time > 1545583683677000000 tz('America/New_York')
2018-12-23T11:48:13.764-05:00 41
2018-12-23T11:48:23.838-05:00 41
2018-12-23T11:48:33.939-05:00 41
…
2018-12-23T12:11:50.314-05:00 42
2018-12-23T12:12:00.42-05:00 42 note ugly time when ms is nn0
2018-12-23T12:12:11.032-05:00 42
select count(temperature) from ruuvi_measurements where time > 1545583683677000000 tz('America/New_York')
name: ruuvi_measurements
time count
---- -----
2018-12-23T11:48:03.677000001-05:00 169
select min(batteryVoltage),mean(batteryVoltage),max(batteryVoltage), STDDEV(batteryVoltage ) from ruuvi_measurements where time > 1545583683677000000 tz('America/New_York')
time min mean max stddev
---- --- ---- --- ------
2018-12-23T11:48:03.677000001-05:00 2.803 2.9022596153846107 2.923 0.008921011610828401
select time, temperature, absoluteHumidity,batteryVoltage, pressure, txPower from ruuvi_measurements
where mac='D3517872EC0F' and time > 154079 and rssi> -70 limit 2;
name: ruuvi_measurements
time temperature absoluteHumidity batteryVoltage pressure txPower
---- ----------- ---------------- -------------- -------- -------
2018-03-22T17:05:14.741Z 6 4.064597097450367 99300
2018-03-22T17:06:15.745Z 6 4.064597097450367 99300
select time, temperature, absoluteHumidity,batteryVoltage, pressure, txPower from ruuvi_measurements
where mac='D3517872EC0F' and batteryVoltage>0 limit 2
time temperature absoluteHumidity batteryVoltage pressure txPower
---- ----------- ---------------- -------------- -------- -------
2018-06-06T17:53:25.336Z 49.86 40.90683320213987 3.001 99442 ### last entry as of 11/30/18
2018-06-06T17:53:36.438Z 49.83 40.84931173861518 3.019 99454 ### notice no txPower values
Other MACs : E511D11ECBB8, E6C3BE6F656C , FB0ECBEF7354, F7FA744A1E1A, DFC8B0FE5CC0 D3517872EC0F
precision edited for breivity (ed)
select * from ruuvi_measurements where mac='D3517872EC0F' and time > 154079 and rssi> -70 limit 2;
name: ruuvi_measurements
time absoluteHumidity accelerationAngleFromX accelerationAngleFromY accelerationAngleFromZ accelerationTotal accelerationX accelerationY accelerationZ airDensity batteryVoltage dataFormat dewPoint equilibriumVaporPressure humidity mac measurementSequenceNumber movementCounter pressure rssi temperature txPower
---- ---------------- ---------------------- ---------------------- ---------------------- ----------------- ------------- ------------- ------------- ---------- -------------- ---------- -------- ------------------------ -------- --- ------------------------- --------------- -------- ---- ----------- -------
1540249289169000000 8.624138716002781 76.08684542427619 14.79288467670658 85.07363877795116 0.9898070519045619 0.238 0.957 0.085 1.2378516828652366 2.929 3 8.66 1121.3290262788335 100 D3517872EC0F 100486 -61 8.66
1540249309853000000 8.618610510024277 75.79732116716174 14.938510454645076 85.46427794222969 0.9863351357424107 0.242 0.953 0.078 1.2379486447936163 2.929 3 8.65 1120.570472328523 100 D3517872EC0F 100490 -64 8.65
select time, temperature,humidity,batteryVoltage, rssi from ruuvi_measurements where mac='D3517872EC0F' order by time limit 9
name: ruuvi_measurements
time temperature humidity batteryVoltage rssi
---- ----------- -------- -------------- ----
1540249289169000000 8.66 100 2.929 -61
1540249299762000000 8.65 100 2.929 -76
1540249309853000000 8.65 100 2.929 -64
1540249320455000000 8.64 100 2.923 -62
1540249331052000000 8.65 100 2.929 -77
1540249341135000000 8.65 100 2.929 -76
1540249351743000000 8.65 100 2.929 -62
1540249361841000000 8.66 100 2.923 -64
1540249371940000000 8.64 100 2.923 -78
select time, mac, temperature, rssi from ruuvi_measurements order by time limit 9
name: ruuvi_measurements
time mac temperature rssi
---- --- ----------- ----
1540249289169000000 D3517872EC0F 8.66 -61
1540249290475000000 F2C0C643AD03 21 -92
1540249299762000000 D3517872EC0F 8.65 -76
1540249301098000000 F2C0C643AD03 21 -93
1540249309853000000 D3517872EC0F 8.65 -64
select time, temperature, rssi from ruuvi_measurements where mac='F2C0C643AD03' and time > 1540900000000000000 limit 900;
name: ruuvi_measurements
time temperature rssi
---- ----------- ----
1540901073139000000 21 -62
1540901087223000000 24 -69
1540901097337000000 24 -85
1540901107431000000 24 -78
1540901118022000000 24 -77
1540901128623000000 25 -93
1540901139223000000 25 -79
1540901149313000000 25 -80
1540901159404000000 25 -78
1540901170492000000 25 -86
1540901180592000000 25 -78
1540901190676000000 25 -78
1540901273348000000 26.09 -85
1540901513209000000 27.14 -56
1540909474167000000 24.91 -70
1540909517698000000 24.95 -50
1540911372699000000 26.97 -76
1540911703507000000 26.83 -81
select time, temperature,humidity,batteryVoltage, rssi from ruuvi_measurements where mac='D3517872EC0F' and time > 1542200000000000000 limit 90;
name: ruuvi_measurements
time temperature humidity batteryVoltage rssi
---- ----------- -------- -------------- ----
1542200000530000000 2.51 75 2.905 -56
1542200010627000000 2.5 74.5 2.911 -72
1542200020714000000 2.49 74 2.899 -71
1542200031307000000 2.51 73.5 2.911 -72
1542200041395000000 2.5 73.5 2.911 -70
select * from ruuvi_measurements;
name: ruuvi_measurements
time absoluteHumidity accelerationAngleFromX accelerationAngleFromY accelerationAngleFromZ accelerationTotal accelerationX accelerationY accelerationZ airDensity batteryVoltage dataFormat dewPoint equilibriumVaporPressure humidity mac measurementSequenceNumber movementCounter pressure rssi temperature txPower
---- ---------------- ---------------------- ---------------------- ---------------------- ----------------- ------------- ------------- ------------- ---------- -------------- ---------- -------- ------------------------ -------- --- ------------------------- --------------- -------- ---- ----------- -------
1540249289169000000 8.62 76.08 14.79 85.07 0.989807 0.238 0.957 0.085 1.2378 2.929 3 8.66 1121.3290262788335 100 D3517872EC0F 100486 -61 8.66
1540249290475000000 8.059030710937025 1.1862117308049511 4 8.292341167820647 2485.764136776915 44 F2C0C643AD03 100500 -92 21
influx -precision rfc3339 # Hey it's a time series data base rfc3339 := YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ
InfluxDB HTTP API runs on port 8086
system _internal
Retention Policy
Unique per database, define:
duration: how long data is kept, example 4320h0m0s (180 days) must be greater than 1h
replication factor: how many copies of this data is stored in the cluster and the
shard group duration: time range covered by shard groups.
sduration must be greater than 1h,
defaults to 1h if duration < 2d; 7d if duration > 6m and 1d otherwise.
autogen with infinite duration, replication factor of 1, and shard group duration of 7 days (7*24=168h).
ALTER [CREATE] RETENTION POLICY retention_policy_name ON database_name
DURATION duration REPLICATION n
[SHARD DURATION sduration] [DEFAULT]
SHOW RETENTION POLICIES on ruuvi name duration shardGroupDuration replican default ---- -------- ------------------ -------- ------- autogen 0s 168h0m0s 1 true alter retention policy autogen on ruuvi DURATION 4320h0m0s # 180 days 1/2 year, (365+25)= 9360h autogen 4320h0m0s 168h0m0s 1 truedocs.influxdata.com functions
show stats
sample data curl https://s3.amazonaws.com/noaa.water-database/NOAA_data.txt -o NOAA_data.txt 44 seconds 5MB CREATE DATABASE NOAA_water_database influx -import -path=NOAA_data.txt -precision=s -database=NOAA_water_database influx -precision rfc3339 -database NOAA_water_database SHOW measurements name: measurements name ---- average_temperature h2o_feet h2o_pH h2o_quality h2o_temperature SELECT COUNT("water_level") FROM h2o_feet name: h2o_feet time count ---- ----- 1970-01-01T00:00:00Z 15258 SELECT * FROM h2o_feet LIMIT 5 name: h2o_feet time level description location water_level ---- ----------------- -------- ----------- 2015-08-18T00:00:00Z below 3 feet santa_monica 2.064 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12 2015-08-18T00:06:00Z below 3 feet santa_monica 2.116 2015-08-18T00:06:00Z between 6 and 9 feet coyote_creek 8.005 2015-08-18T00:12:00Z below 3 feet santa_monica 2.028SHOW DATABASES, RETENTION POLICIES, SERIES, MEASUREMENTS, TAG KEYS, TAG VALUES, FIELD KEYS SHOW DATABASES name: databases _internal ruuvi NOAA_water_database SHOW RETENTION POLICIES [ON database_name] name duration shardGroupDuration replicaN default autogen 0s 168h0m0s 1 true SHOW SERIES [ON database_name] [FROM_clause] [WHERE tag_key operator [ 'tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause] SHOW MEASUREMENTS [ON database_name] [WITH MEASUREMENT regular_expression] [WHERE tag_key operator ['tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause SHOW TAG KEYS [ON database_name] [FROM_clause] [WHERE tag_key operator ['tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause] SHOW TAG VALUES [ON database_name][FROM_clause] WITH KEY [ [operator "tag_key" | regular_expression] | [IN ("tag_key1","tag_key2")]] [WHERE tag_key operator ['tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause] SHOW FIELD KEYS [ON database_name] [FROM measurement_name] show SERIES key --- average_temperature,location=coyote_creek average_temperature,location=santa_monica h2o_feet,location=coyote_creek h2o_feet,location=santa_monica h2o_pH,location=coyote_creek h2o_pH,location=santa_monica h2o_quality,location=coyote_creek,randtag=1 h2o_quality,location=coyote_creek,randtag=2 h2o_quality,location=coyote_creek,randtag=3 h2o_quality,location=santa_monica,randtag=1 h2o_quality,location=santa_monica,randtag=2 h2o_quality,location=santa_monica,randtag=3 h2o_temperature,location=coyote_creek h2o_temperature,location=santa_monica average_temperature,location=coyote_creek average_temperature,location=santa_monica h2o_feet,location=coyote_creek h2o_feet,location=santa_monica h2o_pH,location=coyote_creek h2o_pH,location=santa_monica h2o_quality,location=coyote_creek,randtag=1 h2o_quality,location=coyote_creek,randtag=2 h2o_quality,location=coyote_creek,randtag=3 h2o_quality,location=santa_monica,randtag=1 h2o_quality,location=santa_monica,randtag=2 h2o_quality,location=santa_monica,randtag=3 h2o_temperature,location=coyote_creek h2o_temperature,location=santa_monica show SERIES key --- ruuvi_measurements,dataFormat=3,mac=DFC8B0FE5CC0 ruuvi_measurements,dataFormat=3,mac=E6C3BE6F656C ruuvi_measurements,dataFormat=3,mac=F7FA744A1E1A ruuvi_measurements,dataFormat=3,mac=FB0ECBEF7354 ruuvi_measurements,dataFormat=4,mac=D3517872EC0F ruuvi_measurements,dataFormat=4,mac=F2C0C643AD03 ruuvi_measurements,dataFormat=5,mac=E511D11ECBB8 ruuvi_measurements,dataFormat=5,mac=E6C3BE6F656C show FIELD KEYS name: ruuvi_measurements fieldKey fieldType -------- --------- absoluteHumidity float accelerationAngleFromX float accelerationAngleFromY float accelerationAngleFromZ float accelerationTotal float accelerationX float accelerationY float accelerationZ float airDensity float batteryVoltage float dewPoint float equilibriumVaporPressure float humidity float measurementSequenceNumber integer movementCounter integer pressure float rssi integer temperature float txPower integer show FIELD KEYS name: average_temperature degrees float name: h2o_feet level description string water_level float name: h2o_pH pH float name: h2o_quality index float name: h2o_temperature degrees float
Aggregations | |||||||||||||||||||
COUNT( don't use * | |||||||||||||||||||
Selectors | |||||||||||||||||||||||
SAMPLE( field,n | |||||||||||||||||||||||
Transformations | ||||||||||||||||||
CEILING( rounded up | ROUND( | |||||||||||||||||
| Predictors | ||||||||
HOLT_WINTERS(
| ||||||||
sigQUIT (usually ^\) while a query is running causes influx to output partial results like: ERR: -12-31T00:31:48.091-05:00","D3517872EC0F",33],
["2018-12-31T00:31:58.171-05:00","D3517872EC0F",33],["2018-12-31T00:32:08.251-05:00","D3517872EC0F",33],
["2018-12-31T00:32:18.359-05:00","D3517872EC0F",33],["2018-12-31T00:32:28.463-05:00","D3517872EC0F",33],
["2018-12-31T00:32:38.566-05:00","D3517872EC0F",33],["2018-12-31T00:32:48.679-05:00","D3517872EC0F",33],
["2018-12-31T00:34:39.727-05:00","D3517872EC0F",33],["2018-12-31T00:34:49.818-05:00","D3517872EC0F",33], ["2018-12-31T00:35:00.396-05:00"…
influxd daemon (service)Is influxd running? curl --connect-timeout 2 --head http://localhost:8086/pingor curl --connect-timeout 2 --head http://pi93graf:8086/ping
HTTP/1.1 204 No Content Content-Type: application/json Request-Id: 75c9524a-7d8c-11e9-9827-b827eb1c5b93 X-Influxdb-Build: OSS X-Influxdb-Version: 1.7.6 X-Request-Id: 75c9524a-7d8c-11e9-9827-b827eb1c5b93 Date: Thu, 23 May 2019 18:56:23 GMT current timeommitting the ping returns HTTP/1.1 404 Not Found Content-Type: text/plain; charset=utf-8 X-Content-Type-Options: nosniff X-Influxdb-Build: OSS X-Influxdb-Version: 1.7.9 Date: Mon, 30 Dec 2019 21:52:23 GMT Content-Length: 19
Starting (takes 40 seconds! on pi zero)
Interactively (shows errors):
commands :
|
influxd backup [options] path
Exactly one backup path is required.
-portable |
Use gzip to compress 80% !
datafiles must be owned/group influxdb influsdb
sudo find /var/lib/influxdb -user root # any found need to be |
Benchmark vs Elasticsearch pdf
from raspberry pi rev B as of 18-12-14 InfluxDB shell version: 1.0.2
from pi zero 3/27/19 InfluxDB shell version: 1.7.4 , `
influx_inspect,
chronograf