-database name |
connect host:port |
SQL-like query language named InfluxQL
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
)
influx
Command Line Intrepreter queries the database.(slow if on same system as database )
Keywords
ALL |
influx >SHOW DATABASES name: databases _internal ruuvi NOAA_water_database >qIt an be run from another system using
influx -host dbhosst
ERR: no data received
The database is not running.
influx -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 (gray) keys are calculated values included by default with ruuvi-collector.properties:storage.values=extended They can be 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 >show tag keys name: ruuvi_measurements tagKey ------ dataFormat mac | >show measurements name: measurements name ---- ruuvi_measurements > show series key --- ruuvi_measurements,dataFormat=3,mac=C7103C6824C2 ruuvi_measurements,dataFormat=3,mac=D2503AFE1A96 ruuvi_measurements,dataFormat=3,mac=DC4B596ED18B ruuvi_measurements,dataFormat=3,mac=E4D56DEC6E29 ruuvi_measurements,dataFormat=4,mac=C7103C6824C2 ruuvi_measurements,dataFormat=5,mac=C3F5C373450A ruuvi_measurements,dataFormat=5,mac=C4D8A4A90981 ruuvi_measurements,dataFormat=5,mac=C876E596B2C3 ruuvi_measurements,dataFormat=5,mac=D0FD6D6506DC ruuvi_measurements,dataFormat=5,mac=D2503AFE1A96 ruuvi_measurements,dataFormat=5,mac=D3517872EC0F ruuvi_measurements,dataFormat=5,mac=D6F3DADAC85E ruuvi_measurements,dataFormat=5,mac=DC4B596ED18B ruuvi_measurements,dataFormat=5,mac=DCDF6E338D04 ruuvi_measurements,dataFormat=5,mac=E49ED586E4CE ruuvi_measurements,dataFormat=5,mac=E4D56DEC6E29 ruuvi_measurements,dataFormat=5,mac=E9383FDD20BC ruuvi_measurements,dataFormat=5,mac=F2C0C643AD03 ruuvi_measurements,dataFormat=5,mac=F7B7744722AB ruuvi_measurements,dataFormat=5,mac=F7FA744A1E1A |
SELECT COUNT( * | field_key | field_key,tag_key | /regular_expression/ ) [INTO ] FROM [ WHERE† ] [ GROUP BY [* | tag_key[,tag_key]]] [ ORDER BY time ] [ LIMIT ] [ OFFSET] [ SLIMIT ] [ SOFFSET ] [ tz('time_zone') ]For field_keys and tag_key with the same name use:
_key
::[field
|tag
]
time
=
, <> ,
!= , > , >= , <
or <=
>
now() -duration_unit
: w d h m s ms u ns
No month code.
tz('America/New_York
)
Example if tz is specified:
'yyyy-mm-dd[Thh:mm:ss-hh:00'
'2021-03-23T00:10:00-04:00' '2021-03-23'
'yyyy-mm-dd[ hh:mm:ss[.thmuuunnn †]'
'yyyy-mm-dd[Thh:mm:ss[.thmuuunnnZ']
… time > '2019-03-26' 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 44MEAN( ) from ruuvi_measurements GROUP By time … for each time(12m) interval : faster too!… time >= now()-2h GROUP BY time(10m) requires at least one aggregate function.
SELECT MEAN(batteryVoltage) FROM ruuvi_measurements WHERE … GROUP BY *† ,time(12m) LIMIT 2 ; SELECT 32+(9/5*mean( temperature) ) as temp, mean(batteryVoltage)*100 as volts FROM ruuvi_measurements WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND "mac"='E4D56DEC6E29' group by *,time(6h)
now() - 7d
where
does not support or
for multiple time ranges.
E
astern S
tandard T
ime zone) of the most recent (last
) temperature
, etc:
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 10All
mac
s with temperature
converted to fahrenheitselect mac,ceil(temperature*9/5)+32 from ruuvi_measurements where time > now()-1h group_by mac tz('America/New_York')All selection from Command Line from a specfic ruuvi tag.
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 and set database context(to defaults)
influx -database 'metrics' -host 'localhost' -port '8086'
cURL connects to a 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]]}]}]}
curl --silent --show-error --connect-timeout 6 --get 'http://pi93graf:8086/query?' \ --data-urlencode "db=ruuvi" \ --data-urlencode \ "q=SELECT mac,32+last(temperature)*9/5 FROM ruuvi_measurements group by mac tz('America/New_York') " |\ sed "s/name.:.ruuvi_measurements/ \\n/g; s/.columns.:..time.,.mac.,.//g; s/.......-..:..//g" |\ sed "s/0000000001//g; s/99999999999//g ; s/\[/\n[/g" # to get it to be 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 :Additional examples:
| sed 's/},{/\n/g; s/"//g; s/\[//g ; s/]/\n/g; s/name:ruuvi_measurements,//g; s/columns://g; '\ 's/values://g ; s/{mac://g; s/-0.:00//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 # rfc3339 uses better time display select mac,ceil(temperature*9/5)+32 temp from ruuvi_measurements where time > now() - 15m 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, pressure from ruuvi_measurements where mac='D3517872EC0F' and time > 154079 and rssi> -70 limit 2; name: ruuvi_measurements time temperature absoluteHumidity pressure ---- ----------- ---------------- --------------- 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 lines wrapped and precision edited for breivity (ed) select * from ruuvi_measurements where mac='D3517872EC0F' and time > 154079 and rssi> -70 limit `; name: ruuvi_measurements time absoluteHumidity accelerationAngleFromX accelerationAngleFromY accelerationAngleFromZ accelerationTotal ---- ---------------- ---------------------- ---------------------- ---------------------- ----------------- 1540249289169000000 8.624138716002781 76.08684542427619 14.79288467670658 85.07363877795116 0.9898070519045619 accelerationX accelerationY accelerationZ airDensity batteryVoltage dataFormat ------------- ------------- ------------- ---------- -------------- ---------- 0.238 0.957 0.085 1.2378516828652366 2.929 3 dewPoint equilibriumVaporPressure humidity mac measurementSequenceNumber movementCounter pressure -------- ------------------------ -------- --- ------------------------- --------------- -------- 8.66 1121.3290262788335 100 D3517872EC0F 1004 86 rssi temperature txPower ---- ----------- ------- -61 8.66 4 select time, temperature,humidity,batteryVoltage, rssi from ruuvi_measurements where mac='D3517872EC0F' order by time limit 4 name: ruuvi_measurements time temperature humidity batteryVoltage rssi ---- ----------- -------- -------------- ---- 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 6 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 6; name: ruuvi_measurements time temperature rssi ---- ----------- ---- 1540901073139000000 21 -62 1540901087223000000 24 -69 1540901097337000000 24 -85 1540909474167000000 24.91 -70 1540909517698000000 24.95 -50 1540911372699000000 26.97 -76 select time, temperature,humidity,batteryVoltage, rssi from ruuvi_measurements where mac='D3517872EC0F' and time > 1542200000000000000 limit 5; 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_clause
INTO
- measurement_name
- database_name..measurement_name
- database_name.retention_policy_name.measurement_name
- database_name.retention_policy_name.:MEASUREMENT FROM /regular_expression/
FROM_clause [WHERE_clause] [GROUP_BY_clause]
:MEASUREMENT
) maintains the source measurement names in the destination .INTO
queries with different measurements or time boundaries with the WHERE
.
Example:
SELECT "water_level" INTO "h2o_feet_copy_1" FROM "h2o_feet" WHERE "location" = 'coyote_creek'
.
h2o_feet_copy_1
is a new new measurement
Result displays number of points and a meaningless timestamp.
SELECT MEAN("water_level") INTO "all_my_averages" FROM "h2o_feet"
WHERE "location" = 'coyote_creek'
AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
Aggregationswith text fields (likeMAC ) returns null
| ||||||||||||||||||||||
COUNT( don't use * |
Selectors | |||||||||||||||||||||||
SAMPLE( field,n |
select count(batteryVoltage),percentile(batteryVoltage,10), min(batteryVoltage),mean(batteryVoltage),max(batteryVoltage),stddev(batteryVoltage) from ruuvi_measurements where batteryVoltage<1.78 and time > '2021-01-01' and mac='E9383FDD20BC' tz('America/New_York') name: ruuvi_measurements time count percentile min mean max stddev ---- ----- ---------- --- ---- --- ------ 2021-01-01T00:00:00.000000001-05:00 16 1.743 1.729 1.76225 1.778 0.013542525613784159 select mac,ceil(pressure/100),ceil(batteryVoltage*100)/100,ceil(temperature*9/5)+32,ceil(humidity) from ruuvi_measurements where time > NOW()-1m group by mac limit 5 tz('America/New_York')
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"…
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]
GornellVT SHOW RETENTION POLICIES on ruuvi name duration shardGroupDuration replicaN default ---- -------- ------------------ -------- ------- autogen 0s† 168h0m0s 1 true > alter retention policy autogen on ruuvi DURATION 18240h > SHOW RETENTION POLICIES on ruuvi name duration shardGroupDuration replicaN default ---- -------- ------------------ -------- ------- autogen 18240h0m0s 168h0m0s 1 true > SHOW RETENTION POLICIES on ruuvi name duration shardGroupDuration replican default ---- -------- ------------------ -------- ------- autogen 0s 168h0m0s 1 true alter retention policy autogen on ruuvi DURATION 4320h # 180 days 1/2 year, (365+25)= 9360h (365*2+30)= 18240h autogen 4320h0m0s 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]
influxd daemon (service)Is influxd running? curl --connect-timeout 2 --head http://localhost:8086/ping or 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 :
show stats
|
>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
//http://influxhost:8086/debug/pprof
/debug/pprof/Count | Profile |
1262 | allocs |
44 | block |
0 | cmdline |
22 | goroutine |
1262 | heap |
16 | mutex |
0 | profile |
10 | threadcreate |
0 | trace |
Profile Descriptions:
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