InfluxDB 1.x
The contents on this page is specific to InfluxDB 1.x. For information on 2.x, see the main InfluxDB article.
Using the InfluxDB command line
Get started with the InfluxDB command line. If using a docker container, you can connect to localhost without credentials on the default port. You can execute queries either by entering it to the influx shell or by piping it to influx
.
# echo "show databases" | influx -host localhost
# influx -host localhost
Connected to http://localhost:8086 version 1.7.10
InfluxDB shell version: 1.7.10
> show databases
Cheat Sheet
Action | Command |
---|---|
Show databases | show databases;
|
Create a database | create database $database;
|
Select a database | use $database;
|
Show all measurements | show measurements
|
Drop a measurement | drop measurement weather;
|
Show measurements matching a tag | show measurement where tag = 'value'
|
Show running queries | show queries;
|
Show all users | show users;
|
Show retention policies on a database | show retention policies on "$database"
|
Show all series | show series from temperature
|
Show all tags | show tag keys from "$measurement"
|
Copy data to another measurement/database | select * INTO newdb.autogen.cpu FROM olddb.autogen.cpu WHERE host = 'arc' GROUP BY *
|
Continuous Queries | show continuous queries
|
Delete a continuous query | drop continuous query cq-name ON measurement
|
Deleting Measurement
To delete an entire measurement, you must enter the influx command line, set the database and then run DROP MEASUREMENT
. You cannot do this from Chronograf.
# influx -host localhost
Connected to http://localhost:8086 version 1.7.10
InfluxDB shell version: 1.7.10
> show databases;
nodered
> use nodered
Using database nodered
> drop measurement local_weather;
>
Delete a Series
To delete a specific series based on tag values, use the DELETE FROM
query from the influx command line.
# influx -host localhost
Connected to http://localhost:8086 version 1.7.10
InfluxDB shell version: 1.7.10
> use nodered
Using database nodered
> DELETE FROM "power" WHERE device = 'AWP04L-1'
Deleting a series will not drop any fields even if the field no longer has any values. See the next section on dropping a field.
Copy measurements to another database
You can copy a measurement to another database by using the SELECT * INTO
type query. This might be desirable if you wish to make a backup of the data for only a specific set of measurements based on a tag. For example, to copy data from the source database olddb
to the destination database newdb
, but only for measurements where the tag host
is arc
:
SELECT * INTO newdb.autogen.cpu
FROM olddb.autogen.cpu
WHERE host = 'arc'
GROUP BY *
Down-sampling Data
See: https://docs.influxdata.com/influxdb/v1.8/guides/downsample_and_retain/
You need to have a separate measurement for the down sampled data. Use a continuous query (CQ) to push data from the non-down sampled measurement into the down sampled measurement.
Eg:
CREATE RETENTION POLICY "longterm" ON telegraf DURATION 1825d REPLICATION 1
CREATE CONTINUOUS QUERY "30m_telegraf_cpu" ON telegraf
BEGIN
SELECT
mean(usage_guest) as usage_guest,
mean(usage_guest_nice) as usage_guest_nice,
mean(usage_idle) as usage_idle,
mean(usage_iowait) as usage_iowait,
mean(usage_irq) as usage_irq,
mean(usage_nice) as usage_nice,
mean(usage_softirq) as usage_softirq,
mean(usage_steal) as usage_steal,
mean(usage_system) as usage_system,
mean(usage_user) as usage_user
INTO telegraf.longterm.:measurement
FROM telegraf.autogen.cpu GROUP BY time(30m)
END
Drop a Field
There is no capability to drop a field in InfluxDB. There is a feature request for it at https://github.com/influxdata/influxdb/issues/6150.
A work around is to SELECT the fields that you want into a placeholder measurement, drop the original measurement with the extraneous fields, and then re-select everything from the placeholder back into the original measurement.
# influx -host localhost
Connected to http://localhost:8086 version 1.7.10
InfluxDB shell version: 1.7.10
> use nodered
Using database nodered
## Note the extra field starting with an uppercase
> select * FROM power LIMIT 1;
name: power
time Amperage ApparentPower Factor ReactivePower Voltage Wattage amperage apparentPower device factor reactivePower voltage wattage
---- -------- ------------- ------ ------------- ------- ------- -------- ------------- ------ ------ ------------- ------- -------
1588742430632718326 0.033 02200194dc4f22137d7f
## Copy desired fields to a placeholder measurement
> select time, amperage, apparentPower, device, factor, reactivePower, voltage, wattage INTO demo FROM power group by *
name: result
time written
---- -------
0 574590
## Drop the original measurement
> drop measurement power
## Re-insert everything back into the original measurement
> select * INTO power FROM demo
name: result
time written
---- -------
0 574590
## Drop the placeholder
> drop measurement demo
Because you are re-copying the data again, this might not be a good solution for extremely large measurements.
Show unique tag values
To show all tag values that are in a measurement (such as the 'jobs' measurement in the example below), use the SHOW TAG VALUES
query. More information can be found from the InfluxDB documentation.
# influx -host localhost
Connected to http://localhost:8086 version 1.7.10
InfluxDB shell version: 1.7.10
> use x
Using database x
> show tag values from jobs with key = partition
name: jobs
key value
--- -----
partition apophis
partition apophis-bf
partition bigmem
partition breezy
partition cpu2013
partition cpu2019
partition gpu
partition gpu-v100
partition lattice
partition parallel
In this case, the measurement 'jobs' has a tag 'partition' which has these distinct values. To exclude some value in this query, add a WHERE
clause that excludes certain values. Eg.:
> SHOW TAG VALUES FROM jobs WITH KEY = partition WHERE partition !~ /bigmem/
This type of query is great when generating dashboard variables in Grafana.
Writing data
The schema of a InfluxDB database is based on the very first initial data types added. Adding additional data with a different type will be rejected.
- https://docs.influxdata.com/influxdb/v1.7/guides/writing_data/
- https://www.influxdata.com/blog/how-to-send-sensor-data-to-influxdb-from-an-arduino-uno/
With Curl using the line protocol
Using curl
, you can POST values to a particular database using the line protocol. For more information, see https://docs.influxdata.com/influxdb/v0.9/write_protocols/line/
$ curl -X POST 'http://influxdb/write?db=lsf' --data-binary 'measurement,tag1=x,tag2=x recorded_value1=x `date +%s"000000000"`'
For multiple entries, write multiple lines separated by a \n
newline and POST with Curl like above.
$ cat /tmp/measurements | curl -X POST --data-binary @- 'http://influxdb/write?db=database'
Large inputs might result in a 413 Payload Too Large error and requires you to split the input. The simplest way is to just split the input. Eg. sending data in batches of 500 lines:
$ cat /tmp/measurements | split -l 500 --filter="curl -X POST --data-binary @- 'http://influxdb/write?db=database'"
Backing up data
Influxd
With Influxd, to backup:
# influxd backup -portable -database telegraftwo /tmp/backup
To restore, copy the generated files to the host you want to restore to and run:
# influxd restore -portable -database telegraftwo /tmp/backup
Curl
There is no easy way to restore the JSON output that's generated using this method. However, it's in a readable format for other purposes.
$ curl "http://influxdb/query?db=slurm-metrics&" --data-urlencode "q=SELECT * FROM $series;" > series
Migrating from Prometheus
There is a go utility called prom2influx
(https://github.com/zhyon404/prom2influx). More information documented at: https://docs.tibco.com/pub/ftl/5.4.0/doc/html/GUID-BAE2C630-90B2-47FC-A7D6-97AB16065A0C.html
To migrate data from Prometheus to InfluxDB, use the prom2influx migration utility
Ensure that the Prometheus server is running.
Ensure that the InfluxDB server is running.
Run the migration utility. For example, this command migrates all the data points in Prometheus:
prom2influx -prometheus http://prom_host:9090 -influx http://influx_host:8086
The duration of this step depends on the amount of data you migrate, network communication bandwidth and speeds, and other factors. You can migrate a subset of the data by specifying the -start and -range parameters, for example:
prom2influx -prometheus http://prom_host:9090 -influx http://influx_host:8086 -start 2018-03-12T12:00:57-05:00 -range 1y
Dealing with non-periodic measurements with the sum aggregate
For certain measurements, a sum aggregate needs to be used. However, this requires measurements to occur on a specific time interval or else the summed measurement would be incorrect. In a sense, what we really need is to sum the measurements on a small time interval and then average the results.
This can be accomplished using nested queries. One of the metrics I wanted to graph which was logged every 24 hours used:
SELECT avg("x") FROM
(SELECT sum("usage") as x FROM "storage_usage" WHERE "partition" = 'work' AND $timeFilter GROUP BY time(1h) )
GROUP BY time($__interval) fill(null)
Measurements taken more frequently than 24 hours but less frequent than 1 hour won't cause the result to be incorrect.