InfluxDB Quick Start
来自Jack's Lab
（版本间的差异）
(→优化) |
(→HTTPS) |
||
| 第204行： | 第204行： | ||
== HTTPS == | == HTTPS == | ||
| + | |||
| + | <source lang=bash> | ||
| + | $ influx -ssl -host zy.my.com -username admin -password pwd -database mydb | ||
| + | </source> | ||
<br> | <br> | ||
2023年9月18日 (一) 17:14的最后版本
目录 |
[编辑] 1 Key Concepts
[编辑] 1.1 Database
A logical container for users, retention policies, continuous queries, and time series data
[编辑] 1.2 Measurement
The part of InfluxDB’s structure that describes the data stored in the associated fields. Measurements are strings.
[编辑] 1.3 Tag
The key-value pair in InfluxDB’s data structure that records metadata. Tags are an optional part of InfluxDB’s data structure but they are useful for storing commonly-queried metadata; tags are indexed so queries on tags are performant. Query tip: Compare tags to fields; fields are not indexed.
[编辑] 1.3.1 Tag Key
The key part of the key-value pair that makes up a tag. Tag keys are strings and they store metadata. Tag keys are indexed so queries on tag keys are performant. Query tip: Compare tag keys to field keys; field keys are not indexed.
[编辑] 1.3.2 Tag Value
The value part of the key-value pair that makes up a tag. Tag values are strings and they store metadata. Tag values are indexed so queries on tag values are performant.
[编辑] 1.3.3 Tag Set
The collection of tag keys and tag values on a point.
[编辑] 1.4 Field
The key-value pair in InfluxDB’s data structure that records metadata and the actual data value.
Fields are required in InfluxDB’s data structure and they are not indexed - queries on field values scan all points that match the specified time range and, as a result, are not performant relative to tags.
Query tip: Compare fields to tags; tags are indexed.
[编辑] 1.4.1 Field Key
The key part of the key-value pair that makes up a field. Field keys are strings and they store metadata.
[编辑] 1.4.2 Field Value
The value part of the key-value pair that makes up a field.
- Field values are the actual data;
- They can be strings, floats, integers, or booleans.
- It is always associated with a timestamp.
Field values are not indexed - queries on field values scan all points that match the specified time range and, as a result, are not performant.
Query tip: Compare field values to tag values; tag values are indexed.
[编辑] 1.4.3 Field Set
The collection of field keys and field values on a point.
[编辑] 1.5 Series
The collection of data in InfluxDB’s data structure that share a measurement, tag set, and retention policy. Note: The field set is not part of the series identification!
[编辑] 2 Influx shell
$ influx Connected to http://localhost:8086 version 1.2.x InfluxDB shell 1.2.x > show databases; name: databases name ---- _internal test telegraf > use telegraf; Using database telegraf > show measurements; name: measurements name ---- cpu disk diskio kernel mem processes swap system >
[编辑] 3 Create a database
$ influx Connected to http://localhost:8086 version 1.2.x InfluxDB shell 1.2.x > create database mydb; > show databases; name: databases name ---- _internal test telegraf mydb
Or:
$ curl -i -XPOST http://localhost:8086/query --data-urlencode "q=CREATE DATABASE mydb"
[编辑] 4 Auth
如果你在配置文件里开启了认证但是没有用户，那么 InfluxDB 将不会开启认证，只有当创建一个 admin 用户后，InfluxDB才能开启认证。。
在配置文件中，认证默认是不开启的。将 [http] 区域的配置 auth-enabled 设为 true，可以开启认证：
[http] enabled = true bind-address = ":8086" auth-enabled = true # ✨ log-enabled = true write-tracing = false pprof-enabled = false https-enabled = false https-certificate = "/etc/ssl/influxdb.pem"
#显示用户 > show users user admin ---- ----- root true # 显示用户的数据库权限 > show grants for root database privilege -------- --------- NOAA_water_database WRITE another_database_name READ # 授权 > grant all privileges to root > grant all on "db_name" to "root" > grant read on db_name to test # 撤回 > revoke all privileges from root > revoke write on db_name from test # 重新设置一个用户的密码 > set password for root = 'xxxx' #创建用户 > create user "username" with password 'password' #创建管理员权限用户 > create user "username" with password 'password' with all privileges #删除用户 > drop user "username"
[编辑] 5 HTTPS
$ influx -ssl -host zy.my.com -username admin -password pwd -database mydb
[编辑] 6 Drop a database
> show databases; name: databases name ---- _internal test telegraf mydb > drop database test; > show databases; name: databases name ---- _internal telegraf mydb >
[编辑] 7 Insert data
> USE mydb > INSERT cpu,host=serverA,region=us_west value=0.64 > select * from cpu; name: cpu time host region value ---- ---- ------ ----- 1494498034415516860 serverA us_west 0.64
Syntax:
<measurement>[,<tag-key>=<tag-value>...] <field-key>=<field-value>[,<field2-key>=<field2-value>...] [unix-nano-timestamp]
$ curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'cpu_load,host=server01,region=us-west value=0.64'
[编辑] 8 Queries
[编辑] 8.1 Show
> use mydb Using database mydb > show series key --- cpu,host=serverA,region=us_west cpu_load_short,host=server01,region=us-west > show tag keys name: cpu tagKey ------ host region name: cpu_load_short tagKey ------ host region > show tag keys from cpu name: cpu tagKey ------ host region > show field keys name: cpu fieldKey fieldType -------- --------- value float name: cpu_load_short fieldKey fieldType -------- --------- value float > show field keys from cpu name: cpu fieldKey fieldType -------- --------- value float
A real example:
> use telegraf Using database telegraf > show series key --- cpu,cpu=cpu-total,host=jackslab cpu,cpu=cpu0,host=jackslab cpu,cpu=cpu1,host=jackslab disk,device=rootfs,fstype=rootfs,host=jackslab,path=/ disk,device=sdb1,fstype=ext4,host=jackslab,path=/ disk,device=sdb2,fstype=ext4,host=jackslab,path=/work disk,device=sdb4,fstype=vfat,host=jackslab,path=/data diskio,host=jackslab,name=sda diskio,host=jackslab,name=sda1 diskio,host=jackslab,name=sda2 diskio,host=jackslab,name=sda5 diskio,host=jackslab,name=sdb diskio,host=jackslab,name=sdb1 diskio,host=jackslab,name=sdb2 diskio,host=jackslab,name=sdb3 diskio,host=jackslab,name=sdb4 kernel,host=jackslab mem,host=jackslab net,host=jackslab,interface=all net,host=jackslab,interface=wlan0 netstat,host=jackslab nstat,host=jackslab,name=netstat nstat,host=jackslab,name=snmp nstat,host=jackslab,name=snmp6 processes,host=jackslab swap,host=jackslab system,host=jackslab > show measurements name: measurements name ---- cpu disk diskio kernel mem net netstat nstat processes swap system > show field keys from mem name: mem fieldKey fieldType -------- --------- active integer available integer available_percent float buffered integer cached integer free integer inactive integer total integer used integer used_percent float > show tag keys from mem name: mem tagKey ------ host
$ curl -GET 'http://raspberrypi:8086/query?pretty=true' \
--data-urlencode "db=mydb" --data-urlencode \
"q=SELECT \"value\" FROM \"cpu_load_short\" WHERE \"region\"='us-west'" \
| jq '.results[0].series'
[编辑] 9 Advanced Queries
> SELECT COUNT("water_level") FROM "h2o_feet" \
WHERE time >= '2015-08-19T00:00:00Z' \
AND time <= '2015-08-27T17:00:00Z' \
AND "location"='coyote_creek' GROUP BY time(3d)
> select * from system limit 1; name: system time host load1 load15 load5 n_cpus n_users uptime uptime_format ---- ---- ----- ------ ----- ------ ------- ------ ------------- 1494233380000000000 jackslab 0.09 0.28 0.29 2 6 807623 9 days, 8:20 > show tag keys from system; name: system tagKey ------ host > show field keys from system; name: system fieldKey fieldType -------- --------- load1 float load15 float load5 float n_cpus integer n_users integer uptime integer uptime_format string > select * from system order by time desc limit 5; name: system time host load1 load15 load5 n_cpus n_users uptime uptime_format ---- ---- ----- ------ ----- ------ ------- ------ ------------- 1495615580000000000 jackslab 0.4 0.23 0.21 2 4 1028371 11 days, 21:39 1495615570000000000 jackslab 0.02 0.2 0.13 2 4 1028361 11 days, 21:39 1495615560000000000 jackslab 0.02 0.2 0.13 2 4 1028351 11 days, 21:39 1495615550000000000 jackslab 0.03 0.21 0.14 2 4 1028341 11 days, 21:39 1495615540000000000 jackslab 0.03 0.21 0.14 2 4 1028331 11 days, 21:38
Only 'order by time' can work. 'order by uptime' can not work
[编辑] 10 Backup
$ influxd backup /tmp/backup 2017/08/31 09:52:26 backing up metastore to /tmp/backup/meta.00 2017/08/31 09:52:26 backup complete $ ls /tmp/backup/ -lht -rw-r--r-- 1 comcat inetsim 1.9K 8月 31 09:52 meta.00 $ influxd backup -database elecdb /tmp/backup/ 2017/08/31 09:53:38 backing up db=elecdb since 0001-01-01 00:00:00 +0000 UTC 2017/08/31 09:53:38 backing up metastore to /tmp/backup/meta.01 2017/08/31 09:53:38 backing up db=elecdb rp=autogen shard=117 to /tmp/backup/elecdb.autogen.00117.00 since 0001-01-01 00:00:00 +0000 UTC 2017/08/31 09:53:39 backing up db=elecdb rp=autogen shard=123 to /tmp/backup/elecdb.autogen.00123.00 since 0001-01-01 00:00:00 +0000 UTC 2017/08/31 09:53:39 backing up db=elecdb rp=autogen shard=130 to /tmp/backup/elecdb.autogen.00130.00 since 0001-01-01 00:00:00 +0000 UTC 2017/08/31 09:53:39 backup complete $ ls -lht /tmp/backup/ -rw-r--r-- 1 comcat inetsim 9.6M 8月 31 09:53 elecdb.autogen.00130.00 -rw-r--r-- 1 comcat inetsim 2.1M 8月 31 09:53 elecdb.autogen.00123.00 -rw-r--r-- 1 comcat inetsim 1.0K 8月 31 09:53 elecdb.autogen.00117.00 -rw-r--r-- 1 comcat inetsim 1.9K 8月 31 09:53 meta.01 -rw-r--r-- 1 comcat inetsim 1.9K 8月 31 09:52 meta.00 $ sudo du -sh /var/lib/influxdb/ 205M /var/lib/influxdb/ $ sudo du -sh /var/lib/influxdb/data/* 12M /var/lib/influxdb/data/elecdb 5.7M /var/lib/influxdb/data/_internal 24K /var/lib/influxdb/data/mydb 166M /var/lib/influxdb/data/telegraf
Take a backup of the autogen retention policy for the telegraf database since midnight UTC on Augest 1st, 2017 by using the command:
$ influxd backup -database telegraf -retention autogen -since 2017-08-01T00:00:00Z /tmp/backup
- Remote backup
$ influxd backup -database mydb -host <remote-node-IP>:8089 /tmp/snapshot
[编辑] 11 Restore
1. Restored the metastore so that InfluxDB knows which databases exist:
$ sudo influxd restore -metadir /var/lib/influxdb/meta /tmp/backup Using metastore snapshot: /tmp/backup/meta.00
2. Recover the backed up data:
$ sudo influxd restore -database elecdb -datadir /var/lib/influxdb/data /tmp/backup Restoring from backup backup/data/elecdb.* unpacking /var/lib/influxdb/data/elecdb/autogen/123/000000007-000000002.tsm unpacking /var/lib/influxdb/data/elecdb/autogen/130/000000005-000000002.tsm
3. Recover the file permissions and restart the influxdb:
$ sudo chown -R influxdb:influxdb /var/lib/influxdb $ sudo service influxdb restart
Usage: influxd restore [flags] PATH
-metadir <path>
Optional. If set the metastore will be recovered to the given path.
-datadir <path>
Optional. If set the restore process will recover the specified
database, retention policy or shard to the given directory.
-database <name>
Optional. Required if no metadir given. Will restore the database
TSM files.
-retention <name>
Optional. If given, database is required. Will restore the retention policy's
TSM files.
-shard <id>
Optional. If given, database and retention are required. Will restore the shard's
TSM files.
[编辑] 12 Dump data into csv
curl -GET 'http://localhost:8086/query?pretty=true' --data-urlencode "db=mydb" --data-urlencode "q=select * from temp" > /tmp/temp.json curl -GET -H "Accept: application/csv" 'http://localhost:8086/query' --data-urlencode "db=elecdb" --data-urlencode "q=select * from temp" influx -host localhost -database mydb -format csv -execute "select * from temp" influx -host localhost -database mydb -format column -execute "select * from temp"
[编辑] 13 Import from csv
export from influxdb:
$ influx -host localhost -database mydb -format csv -execute "select * from temphumi where time <= 1526254976000000000" > test.csv
test.csv:
name,time,Humi,Temp,dev_id,mac temphumi,1526197529000000000,39.3,27.0,NOD78783243232,AABBCCDDEEFF temphumi,1526202738000000000,39.3,26.9,NOD78783243232,AABBCCDDEEFF temphumi,1526203290000000000,39.4,26.9,NOD78783243232,AABBCCDDEEFF temphumi,1526203298000000000,39.4,26.9,NOD78783243232,AABBCCDDEEFF temphumi,1526203303000000000,39.4,26.9,NOD78783243232,AABBCCDDEEFF
[编辑] 14 Python Notes
$ pip install influxdb $ pip install --upgrade influxdb $ pip uninstall influxdb
On Debian/Ubuntu, you can install it with this command:
$ sudo apt-get install python3-influxdb
#!/usr/bin/python
import datetime
from influxdb import client as influxdb
db = influxdb.InfluxDBClient('localhost', 8086, '', '', 'mydb')
def read_data():
with open('quark.csv') as f:
return [x.split(',') for x in f.readlines()[1:]]
a = read_data()
for metric in a:
influx_metric = [{
'measurement': 'quark',
'time': datetime.datetime.utcfromtimestamp(int(metric[1])/1000/1000/1000).isoformat(),
'fields': {
'Alt': float(metric[2]),
'CO2': float(metric[3]),
'Humi': float(metric[4]),
'Lat': float(metric[5]),
'Light': float(metric[6]),
'Lon': float(metric[7]),
'Press': float(metric[8]),
'RSSI': int(metric[9]),
'SNR': int(metric[10]),
'Temp': float(metric[11]),
'Vbat': float(metric[12])
},
'tags': {
'dev_id': metric[13][:-1]
}
}]
#print influx_metric
db.write_points(influx_metric)
Old does not work now:
$ python
Python 2.7.14 (default, Sep 23 2017, 22:06:14)
>>> import pandas as pd
>>> d=pd.read_csv('test.csv', date_parser=lambda x: pd.to_datetime(float(x)), index_col='time')
>>> d=d.drop(columns=['name'])
>>> d
Humi Temp dev_id mac
time
2018-05-13 07:45:29 39.3 27.0 NOD78783243232 AABBCCDDEEFF
2018-05-13 09:12:18 39.3 26.9 NOD78783243232 AABBCCDDEEFF
2018-05-13 09:21:30 39.4 26.9 NOD78783243232 AABBCCDDEEFF
2018-05-13 09:21:38 39.4 26.9 NOD78783243232 AABBCCDDEEFF
2018-05-13 09:21:43 39.4 26.9 NOD78783243232 AABBCCDDEEFF
>>> from influxdb import DataFrameClient
>>> client = DataFrameClient(host='127.0.0.1', port=8086, database='mydb')
>>> client.write_points(d, 'test', tag_columns=['dev_id','mac']
True
[编辑] 15 PHP Client
[编辑] 16 Debug
仪表盘异常，应该看一下数据类型：
> show field keys from weather name: weather fieldKey fieldType -------- --------- Humi string Lumi string Pres string Temp string
数据是 string 而不是 float/int，表盘的很多特性就用不了啦
> show series; > drop series from quark; # 删除所有 series，即删除 quark 这个measurement > drop series from quark where dev_id='11707000007'
[编辑] 17 优化
优化内存使用：
- How to Overcome Memory Usage Challenges with the Time Series Index
- Get started with InfluxDB OSS
- Influx Database Configuration
