How to connect to influxdb
vagrant@exp:~$ influx
Visit https://enterprise.influxdata.com to
register for updates, InfluxDB server management, and monitoring.
Connected to http://localhost:8086 version
1.1.0
InfluxDB shell version: 1.1.0
How to check DB objects?
> show databases
name: databases
name
----
_internal
pcoe
test
> use test
Using database influxvm
> show
measurements
name
----
cpu
mem
user.logins
How to check tags?
A Tag in
InfluxDB is like an indexed field in a relational table. You typically make a
field as a tag because you need to use it as a condition in the where clause to improve query
performance.
If the name
of a measurement contains dot, you need to quote the name. There seems to be a
lot of issues with dot in measurement or field names, so avoid having dot in the
names.
> show tag keys
from "user.logins"
name: user.logins
tagKey
------
region
service
> show tag values from
"user.logins" with key=region
name: user.logins
key value
--- -----
region us-west
How to use tag in the where clause?
Influxdb support
regex matching.
> select * from user_logins
where region =~ /us.*/
name: user.logins
time
region service value
----
------ ------- -----
1478177907371000064
us-west payroll 1
Regex matching
can also be used in other places:
> SHOW MEASUREMENTS WITH MEASUREMENT
=~ /.*login.*/
name: measurements
name
----
user_logins
How to define a retention policy?
The
default retention policy is “autogent”, which is to keep
data forever. The syntax for creating a retention policy is:
CREATE
RETENTION POLICY <retention_policy_name> ON <database_name>
DURATION <duration> REPLICATION <n> [SHARD DURATION
<duration>] [DEFAULT]
To keep data for 5 days and purge it automatically after 5
days, use:
CREATE
RETENTION POLICY fivedays ON test DURATION 5d REPLICATION 1
To insert data using the default retention policy:
> insert
user_logins,service=payroll,region=use-west value=1
To insert data using a specific retention policy:
>insert into fivedays
user_logins,service=payroll,region=use-east value=5
To select the data point just inserted, you need to prefix
the measurement name with the retention policy name:
> select * from user_logins
name:
user_logins
time region service value
---- ------ ------- -----
1484127510987890238 use-west payroll 1
> select * from fivedays.user_logins
name:
user_logins
time region service value
---- ------ ------- -----
1484127751197523399 use-east payroll 5
To make a
retention policy default, create it with “default” clause:
> drop retention policy fivedays on test
(data
rentented with this policy will be dropped too)
> CREATE RETENTION POLICY fivedays ON test DURATION
5d REPLICATION 1 default
Or use alter command:
alter retention policy fivedays on test default
show shards command will show retention policies
on each database:
name: test
id database retention_policy shard_group start_time end_time expiry_time owners
-- -------- ---------------- ----------- ---------- -------- ----------- ------
125 test autogen 125 2016-09-19T00:00:00Z 2016-09-26T00:00:00Z 2016-09-26T00:00:00Z
695 test fivedays 695 2017-01-11T00:00:00Z 2017-01-12T00:00:00Z 2017-01-17T00:00:00Z
How to insert a data point with a specific timestamp?
In the previous example, “time” field contains the value
when the data point was inserted, how to insert a data point with a specific
timestamp?
> insert user_logins,service=payroll,region=use-west value=1
1478177907371000064
> select * from user_logins
name: user_logins
time
region service value
----
------ ------- -----
1478177907371000064
use-west payroll 1
Note, there is a whitespace between the last field and the
time field, and the time field value should a Unix nanosecond.
> insert user_logins,service=payroll,region=use-west value=1
2016-11-03T12:58:27.371000064Z
ERR:
{"error":"unable to parse
'user_logins,service=payroll,region=use-west value=1
2016-11-03T12:58:27.371000064Z': bad timestamp"}
Any timestamp field should be in the format of Unix
nanosecond:
> insert
user_logins,service=payroll,region=use-west
value=1,finishtime=1478177907371000064 1478177907371000064
> select * from user_logins
name: user_logins
time finishtime region service value
---- ---------- ------ ------- -----
2016-11-03T12:58:27.371000064Z 1.478177907371e+18 use-west payroll 1
Other timestamp fields seem to stored as integers, it is not
difficult to convert them to timestamps after retrieving the data.
What is one-line format?
The example in the InfluxDB documentation makes it clear:
A one-line contains 3-parts, measurement, tags and fields. Tags
are separated from the measurement with comma, fields are separated from tags
with a whitespace. Tags in the tag set are separated with comma, and fields in
the field set are separated with common.
insert
user_logins,service=payroll,region=use-west times=9,errors=5,value=9
In
this example, user_logins is the metric (a.k.a the table name),
tags are service and
region, Fields are times, errors and
value.
Telegraf
StatsD plugin supports
multiple tags in a similar one-line format:
echo
"user_logins,service=payroll,region=us-south:2|c" | nc -C -w 1 -u
localhost 8125
However,
it doesn’t support multiple fields, as stated here: https://github.com/influxdata/telegraf/issues/1876.
Unfortunately, for unknown reasons, the feature was once included according to https://github.com/influxdata/telegraf/issues/39,
then removed.
How to drop all measurements?
drop measurement
#NAME# will drop one measurement, but it is not possible to drop all
measurements at one time, but this command can achieve the effect:
drop series from /.*/
What is a series?
A series is a collection of data that have the same
measurement, tags and retention policy.
> show series from user_logins
key
---
user_logins,host=exp,metric_type=counter,region=us-south,service=payroll
user_logins,region=use-east,service=payroll
user_logins,region=use-west,service=payroll
(the output doesn’t show the retention policy active on the
series)
How to output timestamp in a human-readable way?
> precision rfc3339
> select * from user_logins
name: user_logins
time region service value
---- ------ ------- -----
2017-01-11T09:38:30.987890238Z use-west payroll 1
To reverse back:
>
precision ns
What is a continuous query?
A continuous query creates a measurement, which InfluxDB
will insert data into at the group by
frequency.
>CREATE CONTINUOUS QUERY "hourly_schedule" on
"test"
begin
select count("value") into "hourly_schedule"
from "schedules"
group by time(1h)
end
The above statement creates a measurement "hourly_schedule", as data inserts into "schedules", InfluxDB will down-sample the
data and insert it into "hourly_schedule".
This only works for new data inserted into "schedules". For existing data, you just
write an ordinary select … into …
statement:
select count("value") into "hourly_schedule"
from "schedules" where time>0 group by time(1h)
where time>0
is useless here, but InfluxDB throws an error saying “ERR:
error parsing query: aggregate functions with GROUP BY time require a WHERE
time clause” if you don’t
provide a where time clause.
Unlike a SQL in relation DB, InfluxDB “group by” doesn’t support having
clause, but with the above created down-sampling measurements, you can achieve
the same effect:
select * from "hourly_schedule" where count>5 limit 10
How to check the size of a DB?
> use
_internal
Using database
_internal
>
select count(diskBytes) from "tsm1_filestore" where
"database"='pcoe'
name: tsm1_filestore
time count
---- -----
0 392666
