InfluxDB Quick Start

来自Jack's Lab
(版本间的差异)
跳转到: 导航, 搜索
(Import from csv)
(Python Client)
第610行: 第610行:
  
 
* https://www.influxdata.com/blog/writing-data-to-influxdb-with-python/
 
* https://www.influxdata.com/blog/writing-data-to-influxdb-with-python/
 
<br><br>
 
 
== Python Client ==
 
 
<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 python-influxdb
 
</source>
 
 
 
* http://influxdb-python.readthedocs.io/en/latest/api-documentation.html#influxdb.InfluxDBClient.write_points
 
  
 
<br><br>
 
<br><br>

2021年3月18日 (四) 13:36的版本

目录

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

#显示用户
> show users

#创建用户
> create user "username" with password 'password'

#创建管理员权限用户
> create user "username" with password 'password' with all privileges

#删除用户
> drop user "username"



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


13 Python Notes

Need the python-influxdb:

 sudo apt-get install python-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




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,表盘的很多特性就用不了啦


influxdb 中用正确的姿势删除measurement

> show series;

> drop series from quark; # 删除所有 series,即删除 quark 这个measurement

> drop series from quark where dev_id='11707000007'



16 优化

优化内存使用:



















个人工具
名字空间

变换
操作
导航
工具箱