InfluxDB Quick Start
(→Import from csv) |
(→Python Client) |
||
第563行: | 第563行: | ||
* http://influxdb-python.readthedocs.io/en/latest/api-documentation.html#influxdb.InfluxDBClient.write_points | * http://influxdb-python.readthedocs.io/en/latest/api-documentation.html#influxdb.InfluxDBClient.write_points | ||
+ | |||
+ | <br><br> | ||
+ | |||
+ | == PHP Client == | ||
+ | |||
+ | * https://github.com/influxdata/influxdb-php | ||
<br><br> | <br><br> |
2018年7月6日 (五) 12:01的版本
目录 |
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
5 Drop a database
> show databases; name: databases name ---- _internal test telegraf mydb > drop database test; > show databases; name: databases name ---- _internal telegraf mydb >
6 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'
7 Queries
7.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'
8 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
9 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
10 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.
11 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"
12 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
Need the python-influxdb:
sudo apt-get install python-influxdb
$ 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
13 Python Client
$ pip install influxdb $ pip install --upgrade influxdb $ pip uninstall influxdb
On Debian/Ubuntu, you can install it with this command:
$ sudo apt-get install python-influxdb
14 PHP Client
15 Debug
仪表盘异常,应该看一下数据类型:
> show field keys from weather name: weather fieldKey fieldType -------- --------- Humi string Lumi string Pres string Temp string
数据是 string 而不是 float/int,表盘的很多特性就用不了啦