BlueXIII Lab

Technical note

InfluxDB学习笔记

阅读
数据库

官网

https://www.influxdata.com/time-series-platform/influxdb/

InfluxDB是InfluxData的核心产品。InfluxDB是一个开源分布式时序、时间和指标数据库，使用Go语言编写，无需外部依赖。其设计目标是实现分布式和水平伸缩扩展。

教程

https://jasper-zhang1.gitbooks.io/influxdb/content/Concepts/key_concepts.html https://docs.influxdata.com/influxdb/v1.5/ https://www.jianshu.com/p/48104975d60a https://www.jianshu.com/p/a373784c0bf9 https://www.jianshu.com/p/b51ba7f88fb0 https://jkzhao.github.io/2017/12/15/时序数据库InfluxDB/ http://www.ywnds.com/?p=10763 https://anyof.me/articles/390 https://xtutu.gitbooks.io/influxdb-handbook/content/an_zhuang_shi_yong.html

https://www.jianshu.com/p/a1344ca86e9b https://code-examples.net/zh-CN/docs/influxdata/influxdb/v1.3/troubleshooting/frequently-asked-questions/index

安装方式

https://influxdata.com/downloads/

macOS

brew update
brew install influxdb

或

https://dl.influxdata.com/influxdb/releases/influxdb-1.5.4_darwin_amd64.tar.gz
tar zxvf influxdb-1.5.4_darwin_amd64.tar.gz

Docker

docker pull influxdb

Ubuntu

wget https://dl.influxdata.com/influxdb/releases/influxdb_1.5.4_amd64.deb
sudo dpkg -i influxdb_1.5.4_amd64.deb

CentOS

wget https://dl.influxdata.com/influxdb/releases/influxdb-1.5.4.x86_64.rpm
sudo yum localinstall influxdb-1.5.4.x86_64.rpm

配置

配置文件路径

/usr/local/etc/influxdb.conf
或
/etc/influxdb/influxdb.conf

生成默认配置

influxd config > /etc/influxdb/influxdb.generated.conf

名词

namedesc
database数据库
measurement数据库中的表
points表里面的一行数据
time每个数据记录时间，是数据库中的主索引(会自动生成)
fields各种记录值（没有索引的属性）也就是记录的值：温度， 湿度
tags各种有索引的属性：地区，海拔
seriestags的组合，类似复合索引

命令行方式登入

influx
influx -precision rfc3339  # 显示标准时间

创建数据库

show databases
create database mydb
use mydb

显示所有表

show measurements

新建表/插入数据

无建表语句，第一次insert后自动创建 p

INSERT cpu,host=serverA,region=us_west value=0.64
INSERT temperature,machine=unit42,type=assembly external=25,internal=37

其中:  
cpu  # 表名
host=serverA,region=us_west  # tag
value=0.64  # field

删除表

drop measurement weather

series操作

series表示这个表里面的数据，可以在图表上画成几条线，series主要通过tags排列组合算出来。

show series from weather

查询数据

SELECT * FROM /.*/ LIMIT 1
SELECT * FROM cpu_load_short
SELECT * FROM cpu_load_short WHERE value > 0.9

查询/修改RETENTION

SHOW RETENTION POLICIES ON "mydb"
单位有 w/d/h/s
ALTER RETENTION POLICY "autogen" ON "mydb" DURATION 100d DEFAULT
ALTER RETENTION POLICY "autogen" ON "mydb" DURATION 0s DEFAULT

用户管理

显示用户
SHOW USERS
创建用户
CREATE USER "username" WITH PASSWORD 'password'
创建管理员权限的用户
CREATE USER "username" WITH PASSWORD 'password' WITH ALL PRIVILEGES
删除用户
DROP USER "username"

HTTP创建和删除数据库

curl -i -XPOST http://localhost:8086/query —data-urlencode “q=CREATE DATABASE mydb” curl -POST http://localhost:8086/query —data-urlencode “q=DROP DATABASE mydb”

HTTP添加数据

单条

curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'cpu_load_short,host=server01,region=us-west value=0.64 1434055562000000000'

多条

curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'cpu_load_short,host=server02 value=0.67
cpu_load_short,host=server02,region=us-west value=0.55 1422568543702900257
cpu_load_short,direction=in,host=server01,region=us-west value=2.0 1422568543702900257'

使用HTTP查询数据

curl -GET 'http://localhost:8086/query?pretty=true' --data-urlencode "db=mydb" --data-urlencode "q=show measurements"

或直接在浏览器中:

http://localhost:8086/query?pretty=true&db=mydb&q=show%20measurements
curl -G 'http://localhost:8086/query?pretty=true' --data-urlencode "db=mydb" --data-urlencode "q=SELECT \"value\" FROM \"cpu_load_short\" WHERE \"region\"='us-west'"

查询多条用分号分割：

curl -GET 'http://localhost:8086/query?db=_internal' --data-urlencode "q=show databases;show measurements"

时间格式

epoch=[h,m,s,ms,u,ns]

curl -G 'http://localhost:8086/query' --data-urlencode "db=mydb" --data-urlencode "epoch=s" --data-urlencode "q=SELECT value FROM cpu_load_short WHERE region='us-west'"

指定每次查询数据大小

chunk_size

curl -G 'http://localhost:8086/query' --data-urlencode "db=mydb" --data-urlencode "chunk_size=200" --data-urlencode "q=SELECT value FROM cpu_load_short WHERE region='us-west'"

WEB控制台

1.3内置8086WEB管理已经移除，需要使用TICK工具栈中的Chronograf来进行管理。

下载安装说明：
https://portal.influxdata.com/downloads

brew install chronograf

使用:
http://localhost:8888

测试数据导入

https://docs.influxdata.com/influxdb/v1.6/query_language/data_download/

CREATE DATABASE NOAA_water_database

curl https://s3.amazonaws.com/noaa.water-database/NOAA_data.txt -o NOAA_data.txt
influx -import -path=NOAA_data.txt -precision=s -database=NOAA_water_database

influx -precision rfc3339 -database NOAA_water_database

数据查询详细语法

https://jasper-zhang1.gitbooks.io/influxdb/content/Query_language/data_exploration.html

备份

# 全量
influxd backup -portable /mnt/disk/bak/influxdb/total
# 指定库
influxd backup -portable -database heimdall /mnt/disk/bak/influxdb/heimdall
# 指定时间
influxd backup -portable -database heimdall -start 2018-09-01T00:00:00Z -end 2018-09-30T23:59:59Z  /mnt/disk/bak/influxdb/heimdall

恢复

# 恢复到名为heimdall_bak的库
influxd restore -portable -db heimdall -newdb heimdall_bak /mnt/disk/bak/influxdb/heimdall

# 移动库
create database heimdall_new 
use heimdall_bak
SELECT * INTO heimdall_new..:MEASUREMENT FROM /.*/ GROUP BY *
drop heimdall_bak

连续查询

自动缩小取样存储到新的measurement中

CREATE CONTINUOUS QUERY "cq_basic" ON "transportation"
BEGIN
  SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
END