InfluxDB Quick Start
(→import from csv) |
(→HTTPS) |
||
(未显示1个用户的18个中间版本) | |||
第1行: | 第1行: | ||
== Key Concepts == | == Key Concepts == | ||
+ | |||
+ | * [https://mp.weixin.qq.com/s?__biz=MzIwNjEwNTQ4Mw%3D%3D&mid=2651576941&idx=1&sn=69e6eddc2598908d48853676c04ce3c5&chksm=8cd9c489bbae4d9fa8ce0c6d0bb09e09788e6feec4a3e756e06c9c2d445713cb08dff0b90f9b 饿了么 Influxdb 实践之路] | ||
+ | * [http://blog.shell909090.org/blog/archives/2863/ openssl证书相关] | ||
+ | |||
=== Database === | === Database === | ||
第140行: | 第144行: | ||
== Auth == | == Auth == | ||
+ | * [https://jasper-zhang1.gitbooks.io/influxdb/content/Query_language/authentication_and_authorization.html 认证和授权] | ||
* https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761 | * https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761 | ||
− | <br><br> | + | 如果你在配置文件里开启了认证但是没有用户,那么 InfluxDB 将不会开启认证,只有当创建一个 admin 用户后,InfluxDB才能开启认证。。 |
+ | |||
+ | 在配置文件中,认证默认是不开启的。将 [http] 区域的配置 auth-enabled 设为 true,可以开启认证: | ||
+ | |||
+ | <source lang=bash> | ||
+ | [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" | ||
+ | </source> | ||
+ | |||
+ | |||
+ | <source lang=bash> | ||
+ | #显示用户 | ||
+ | > 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" | ||
+ | </source> | ||
+ | |||
+ | <br> | ||
+ | |||
+ | == HTTPS == | ||
+ | |||
+ | <source lang=bash> | ||
+ | $ influx -ssl -host zy.my.com -username admin -password pwd -database mydb | ||
+ | </source> | ||
+ | |||
+ | <br> | ||
== Drop a database == | == Drop a database == | ||
第516行: | 第583行: | ||
temphumi,1526203303000000000,39.4,26.9,NOD78783243232,AABBCCDDEEFF | temphumi,1526203303000000000,39.4,26.9,NOD78783243232,AABBCCDDEEFF | ||
</pre> | </pre> | ||
+ | |||
+ | <br> | ||
+ | |||
+ | == Python Notes == | ||
+ | |||
+ | <source lang=bash> | ||
+ | $ pip install influxdb | ||
+ | $ pip install --upgrade influxdb | ||
+ | $ pip uninstall influxdb | ||
+ | </source> | ||
+ | |||
+ | On Debian/Ubuntu, you can install it with this command: | ||
+ | |||
+ | <source lang=bash> | ||
+ | $ sudo apt-get install python3-influxdb | ||
+ | </source> | ||
+ | |||
+ | * http://influxdb-python.readthedocs.io/en/latest/api-documentation.html#influxdb.InfluxDBClient.write_points | ||
+ | |||
+ | |||
+ | <source lang=python> | ||
+ | #!/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) | ||
+ | </source> | ||
+ | |||
+ | |||
+ | '''Old does not work now:''' | ||
<source lang=python> | <source lang=python> | ||
第538行: | 第666行: | ||
True | True | ||
</source> | </source> | ||
+ | |||
+ | |||
+ | * https://www.influxdata.com/blog/writing-data-to-influxdb-with-python/ | ||
<br><br> | <br><br> | ||
− | == | + | == PHP Client == |
− | * | + | * https://github.com/influxdata/influxdb-php |
<br><br> | <br><br> | ||
第563行: | 第694行: | ||
数据是 string 而不是 float/int,表盘的很多特性就用不了啦 | 数据是 string 而不是 float/int,表盘的很多特性就用不了啦 | ||
+ | |||
+ | |||
+ | [https://blog.xiagaogao.com/20160117/influxdb%20%E4%B8%AD%E7%94%A8%E6%AD%A3%E7%A1%AE%E7%9A%84%E5%A7%BF%E5%8A%BF%E5%88%A0%E9%99%A4measurement.html influxdb 中用正确的姿势删除measurement] | ||
+ | |||
+ | <source lang=bash> | ||
+ | > show series; | ||
+ | |||
+ | > drop series from quark; # 删除所有 series,即删除 quark 这个measurement | ||
+ | |||
+ | > drop series from quark where dev_id='11707000007' | ||
+ | </source> | ||
<br><br> | <br><br> | ||
− | + | ||
− | + | == 优化 == | |
− | + | ||
− | + | 优化内存使用: | |
− | + | ||
− | + | * [https://www.influxdata.com/blog/how-to-overcome-memory-usage-challenges-with-the-time-series-index/ How to Overcome Memory Usage Challenges with the Time Series Index] | |
− | + | * [https://docs.influxdata.com/influxdb/v1.8/introduction/get-started/ Get started with InfluxDB OSS] | |
− | + | * [https://archive.docs.influxdata.com/influxdb/v0.10/administration/config/#cache-max-memory-size-524288000 Influx Database Configuration] | |
+ | |||
<br><br> | <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