当你的数据和时间有关,就可以尝试InfluxDB
特点
-
schemaless(无结构),可以是任意数量的列
-
Scalable
-
min, max, sum, count, mean, median 一系列函数,方便统计
-
Native HTTP API, 内置http支持,使用http读写
-
Powerful Query Language 类似sql
-
Built-in Explorer 自带管理工具
-
以下文档内容是基于V1.0版本
1. 安装
Ø Ubuntu& Debian
Debian 和Ubuntu 用户可以通过apt-get包管理工具安装最新 稳定版本的InfluxDB
对于 Ubuntu用户可以通过如下命令添加 InfluxData repository
curl -sL https://repos.influxdata.com/influxdb.key| sudo apt-key add -
source/etc/lsb-release
echo"deb https://repos.influxdata.com/${DISTRIB_ID,,} ${DISTRIB_CODENAME}stable"
对于Debian用户添加方法
curl -sLhttps://repos.influxdata.com/influxdb.key | sudo apt-key add -
source/etc/os-release
test$VERSION_ID = "7" && echo "debhttps://repos.influxdata.com/debian wheezy stable" | sudo tee/etc/apt/sources.list.d/influxdb.list
test$VERSION_ID = "8" && echo "debhttps://repos.influxdata.com/debian jessie stable" | sudo tee/etc/apt/sources.list.d/influxdb.list
配置好InfluxData仓库后通过如下命令安装InfluxDB服务
udo apt-getupdate && sudo apt-get install influxdb
sudo serviceinfluxdb start
如果操作系统是(Ubuntu15.04+, Debian 8+)版本,请使用systemd
sudo apt-getupdate && sudo apt-get install influxdb
sudosystemctl start influxd
Ø ReadHat&CentOS
RedHat 和 CentOS用户可以通过yum 包管理工具安装最新 稳定版本的InfluxDB
先配置(InfluxDB版本包仓库地址添加到yum配置中):
cat<<EOF | sudo tee /etc/yum.repos.d/influxdb.repo
[influxdb]
name =InfluxDB Repository - RHEL \$releasever
baseurl =https://repos.influxdata.com/rhel/\$releasever/\$basearch/stable
enabled = 1
gpgcheck = 1
gpgkey =https://repos.influxdata.com/influxdb.key
EOF
配置完后通过如下命令安装InfluxDB服务:
sudo yuminstall influxdb
sudo serviceinfluxdb start
如果操作系统是(CentOS7+, RHEL 7+)版本,请使用systemd
sudo yuminstall influxdb
sudosystemctl start influxdb
Ø SLES& openSUSE
对于SUSE Linux用户,可通过如下方法获取RPM包 安装
# add gorepository
zypper ar -fobs://devel:languages:go/ go
# installlatest influxdb
zypper ininfluxdb
Ø FreeBSD/PC-BSD
InfluxDB已经包含在FreeBSD包管理系统中,可以直接安装运行
sudo pkginstall influxdb
配置文件放在位置: /usr/local/etc/influxd.conf
参考例子: /usr/local/etc/influxd.conf.sample
通过如下命令在后台启动:
sudo serviceinfluxd onestart
在配置文件 /etc/rc.conf 中添加influxd_enable="YES" 配置项 可以把InfluxDB增加到系统启动中
Ø MAC OS X
使用OS X10.8或则更高系统的用户 可以使用 Homebrew包管理工具安装InfluxDB
如果brew已经安 可以直接安装或则更新InfluxDB
brew update
brew installinfluxdb
To have launchd start InfluxDB at login,run:
ln -sfv/usr/local/opt/influxdb/*.plist ~/Library/LaunchAgents
And then to start InfluxDB now, run:
launchctlload ~/Library/LaunchAgents/homebrew.mxcl.influxdb.plist
Or, if you don’t want/need launchctl, in aseparate terminal window you can just run:
influxd-config /usr/local/etc/influxdb.conf
2. 数据探索(Data Exploration)
支持常用查询关键字: select ,where,group by,into,limit,slimit,order by timedesc,offset,soffset
Ø SELECT查询带WHERE条件
【语法】
SELECT <stuff> FROM <measurement_name> WHERE<some_conditions>
【样例】
A.从measurement h2o_feet中查询全部数据
SELECT * FROM "h2o_feet"
B. 从h2o_feet中查询指定tag或field数据
SELECT "leveldescription","location","water_level" FROM"h2o_feet"
C. 查询指定database指定measurement中指定保留策略下数据
SELECT * FROM"NOAA_water_database"."autogen"."h2o_feet"
D. 查询语句带算数运算
SELECT ("water_level" * 2) + 4from "h2o_feet"
E. where查询带tags,time ranges(时间范围),或field过滤查询的数据
SELECT "water_level" FROM"h2o_feet" WHERE "location" = 'santa_monica'
F.查询数据指定时间范围(最近7天数据)
SELECT * FROM "h2o_feet" WHEREtime > now() - 7d
G. where带多个查询条件
SELECT * FROM "h2o_feet" WHERE"location" = 'coyote_creek' AND "water_level" > 8
【注】
逻辑查询串联通过 AND和OR 多个过滤条件通过()分割
支持对比语法:
= equal to(等于)
<>not equal to(不等于)
!= not equal to(不等于)
> greater than(大于)
< less than(小于)
=~ matches against(匹配)
!~ doesn’t match against(不匹配)
Ø GROUP BY分组查询
【语法】
SELECT <function>(<field_key>) FROM <measurement_name>WHERE <time_range> GROUP BYtime(<time_interval>[,<offset_interval>])
支持通过tag key和time分组
【样例】
A.通过location分组 计算平均值
SELECT MEAN("water_level") FROM"h2o_feet" GROUP BY "location"
B. 对所有tag key做分组
SELECT MEAN("index") FROM"h2o_quality" GROUP BY *
C.通过time(时间)做分组
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 BYtime(3d)
【注】
Time支持单位
u microseconds
ms milliseconds
s seconds
m minutes
h hours
d days
w weeks
其中group by查询在指定时间段内如果没有数据的话可以通过fill() 填充指定值,常用fill(none)去掉这些空数据
Ø INTO查询
Relocate data(迁移数据)
Copy data to another database, retentionpolicy, and measurement with the INTO clause:
(通过into语句拷贝数据到指定数据库中,指定保留策略,指定表中)
【语法】
SELECT<field_key> INTO <different_measurement> FROM<current_measurement> [WHERE <stuff>] [GROUP BY <stuff>]
【样例】
A.将查询到的数据 拷贝到当前database中 新的measurement(h2o_feet_copy)中
SELECT "water_level" INTO"h2o_feet_copy" FROM "h2o_feet" WHERE "location"= 'coyote_creek'
B.将查询到的数据 拷贝到已经存在的database(where_else)指定保留策略指定表中
SELECT "water_level" INTO"where_else"."autogen"."h2o_feet_copy" FROM"h2o_feet" WHERE "location" = 'coyote_creek'
Downsample data(降低采样率)
Combine the INTOclause with an InfluxQL function and a GROUP BY clause to write the lowerprecision query results to a different measurement:
该操作一般都是按指定时间分组聚合数据 到新的measurement中
【语法】
SELECT <function>(<field_key>) INTO<different_measurement> FROM <current_measurement> WHERE<stuff> GROUP BY <stuff>
【样例】
A.将查询指定时间段数据 按12分钟求平均值(聚合) 拷贝到当前database中 measurement(average)中
SELECT MEAN("water_level") INTO"average" FROM "h2o_feet" WHERE "location" ='santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m)
B.将查询指定时间段数据 按12分钟求平均值,最大值(聚合) 拷贝到已经存在的database(where_else)指定保留策略指定表中
SELECT MEAN("water_level"),max("water_level") INTO"where_else"."autogen"."aggregates" FROM"h2o_feet" WHERE "location" = 'santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
C.Calculate the average degrees forall temperature measurements (h2o_temperature andaverage_temperature) in the NOAA_water_database and write the results to newmeasurements with the same names in a different database(where_else). :MEASUREMENT tells InfluxDB to write the query results tomeasurements with the same names as those targeted by the query:
(计算所有包含temperature关键字的表格中 degrees平均值数据拷贝到 where_else 数据库中,按原始表名创建新表插入数据)
SELECT MEAN("degrees") INTO "where_else"."autogen".:MEASUREMENTFROM /temperature/ WHERE time >= '2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m)
【注】
InfluxDB does not store null values. Depending on thefrequency of your data, the query results may be missing time intervals. Usefill() to ensure that every time interval appears in the results.(influxdb不会存储空的数据 这样在按时间分组聚合数据时,可能会丢失时间 间隔数据,可以通过fill()填充数据解决该问题)
Ø LIMIT ANDSLIMIT(限制查询返回数据条数)
influxQL supports two different clauses tolimit your query results:
(influxdb查询语句支持2种不同的条件来限制查询结果数据)
LIMIT <N> returns the first <N>points from each series in the specified measurement.
(每一个series中返回前N行points数据)
SLIMIT <N> returns every point from<N> series in the specified measurement.
(返回前N个 series中 所有points数据)
LIMIT <N> followed by SLIMIT<N> returns the first <N> points from <N> series in thespecified measurement.
(返回前 N个 series中 前N行points数据)
【语法】
【样例】
A.返回所有 series中前3行 points数据
SELECT "water_level"FROM "h2o_feet" GROUP BY * LIMIT 3
查询返回结果
name:h2o_feet
tags:location=coyote_creek
time water_level
---- -----------
2015-08-18T00:00:00Z 8.12
2015-08-18T00:06:00Z 8.005
2015-08-18T00:12:00Z 7.887
name:h2o_feet
tags:location=santa_monica
time water_level
---- -----------
2015-08-18T00:00:00Z 2.064
2015-08-18T00:06:00Z 2.116
2015-08-18T00:12:00Z 2.028
B.返回第一个 series中所有points数据
SELECT "water_level" FROM"h2o_feet" GROUP BY * SLIMIT 1
查询返回结果:
name: h2o_feet
tags: location=coyote_creek
time water_level
---- -----
2015-08-18T00:00:00Z 8.12
2015-08-18T00:06:00Z 8.005
2015-08-18T00:12:00Z 7.887
[...]
2015-09-18T16:12:00Z 3.402
2015-09-18T16:18:00Z 3.314
2015-09-18T16:24:00Z 3.235
C.返回第一个 series中前3行points数据
SELECT "water_level" FROM"h2o_feet" GROUP BY * LIMIT 3 SLIMIT 1
查询返回结果:
name:h2o_feet
tags:location=coyote_creek
time water_level
---- -----------
2015-08-18T00:00:00Z 8.12
2015-08-18T00:06:00Z 8.005
2015-08-18T00:12:00Z 7.887
Ø ORDER BY(排序)
目前influxdb只支持对time(时间) 做排序(ASC,DESC)
【样例】
A.按时间降序
SELECT "water_level" FROM"h2o_feet" WHERE "location" = 'santa_monica' ORDER BY timeDESC LIMIT 5
查询返回结果:
name:h2o_feet
----------
time water_level
2015-09-18T21:42:00Z 4.938
2015-09-18T21:36:00Z 5.066
2015-09-18T21:30:00Z 5.01
2015-09-18T21:24:00Z 5.013
2015-09-18T21:18:00Z 5.072
Ø OFFSET和SOFFSET(分页)
通过offset对查询结果 points数据进行分页查询,soffset对查询结果 series数据进行分页查询。二者可以结合使用
【样例】
A.从第4条points处开始 返回3条points数据
SELECT "water_level"FROM "h2o_feet" WHERE "location" = 'coyote_creek' LIMIT 3OFFSET 3
查询返回结果:
name: h2o_feet
----------
time water_level
2015-08-18T00:18:00Z 7.762
2015-08-18T00:24:00Z 7.635
2015-08-18T00:30:00Z 7.5
B.从第2个series处开始返回1个series数据:
SELECT "water_level" FROM"h2o_feet" GROUP BY * LIMIT 3 SLIMIT 1 SOFFSET 1
Ø 查询条件支持正则表达式
【样例】
A.查询location不包含 a的数据
SELECT * FROM "h2o_feet" WHERE"location" !~ /.*a.*/ LIMIT 4
B.查询location包含 y或m 且water_level大于0的数据
SELECT * FROM "h2o_feet" WHERE"location" =~ /[ym]/ AND "water_level" > 0 LIMIT 4
3. 数据库管理(Database Management)
支持功能有数据管理和保留策略管理
Data management
◦ Create adatabase with CREATE DATABASE
◦ Delete adatabase with DROP DATABASE
◦ Dropseries from the index with DROP SERIES
◦ Deleteseries with DELETE
◦ Deletemeasurements with DROP MEASUREMENT
◦ Delete ashard with DROP SHARD
Retention policymanagement
◦ Createretention policies with CREATE RETENTION POLICY
◦ Modifyretention policies with ALTER RETENTION POLICY
◦ Deleteretention policies with DROP RETENTION POLICY
Ø 创建数据库
【语法】
CREATE DATABASE <database_name> [WITH [DURATION<duration>] [REPLICATION <n>] [SHARD DURATION <duration>][NAME <retention-policy-name>]]
【样例】
A.使用默认配置创建数据库
CREATE DATABASE "test_database"
B. 创建数据库test_database 保留3份副本(单机版无效),并使用默认保留策略为保存最近3天数据 没30分钟清理一次。保留策略名为 liquid
CREATEDATABASE "test_database" WITH DURATION 3d REPLICATION 3 SHARDDURATION 30m NAME "liquid"
Ø 删除数据库
【语法】
DROP DATABASE<database_name>
【样例】
A.删除数据库test_database
DROP DATABASE "test_database"
Ø 删除表数据
【语法】
DELETE FROM <measurement_name> WHERE[<tag_key>='<tag_value>'] | [<time interval>]
目前删除数据 where条件只支持 tagkey和time
【样例】
-
删除数据库 表 test中全部数据
DELETE FROM "test"
-
删除数据库 表 test 中tag key randtag=3的全部数据
DELETE FROM"test " WHERE "randtag" = '3'
-
删除数据库 表test 大于指定时间数据
DELETE FROM"test " WHERE time <'2016-01-01'
Ø 删除表
【语法】
DROP MEASUREMENT<measurement_name>
【样例】
-
删除数据库 中h2o_feet表
DROP MEASUREMENT "h2o_feet"
Ø 创建保留策略
【语法】
CREATE RETENTIONPOLICY <retention_policy_name> ON <database_name> DURATION<duration> REPLICATION <n> [SHARD DURATION <duration>][DEFAULT]
-
DURATION determines how long InfluxDB keepsthe data. The options for specifying the duration of the retention policy arelisted below. Note that the minimum retention period isone hour.
m minutes
h hours
d days
w weeks
INF infinite
-
REPLICATION determines how many independent copies ofeach point are stored in the cluster, where nis the number of data nodes.
-
SHARD DURATION determines the time range covered by ashard group. The options for specifying the duration of the shard group arelisted below. The default shard group duration depends on your retention policy’s DURATION.
u microseconds
ms milliseconds
s seconds
m minutes
h hours
d days
w weeks
-
DEFAULT sets the new retentionpolicy as the default retention policy for the database
【样例】
-
在数据库test_database中创建保留策略 one_day_only 数据保存时间为1天,复制因子为1
CREATE RETENTION POLICY"one_day_only" ON "test_database" DURATION 1d REPLICATION 1
B.创建保留策略并将其设置为数据库 默认策略
CREATE RETENTION POLICY"one_day_only" ON "test_database" DURATION 1d REPLICATION 1DEFAULT
Ø 修改保留策略
【语法】
ALTER RETENTIONPOLICY <retention_policy_name> ON <database_name> DURATION<duration> REPLICATION <n> SHARD DURATION <duration> DEFAULT
The ALTER RETENTION POLICY query takes the followingform, where you must declare at least one of the retention policy attributes DURATION, REPLICATION,SHARD DURATION, or DEFAULT:
【样例】
-
修改保留策略 what_is_time 属性DURATION 3周,SHARD DURATION为30分钟并设置为test_database数据库默认保留策略
ALTER RETENTION POLICY"what_is_time" ON "test_database" DURATION 3w SHARDDURATION 30m DEFAULT
Ø 删除保留策略
【语法】
DROP RETENTIONPOLICY <retention_policy_name> ON <database_name>
Delete all measurements and data in a specificretention policy
【样例】
-
test_database中保留策略 what_is_time
DROP RETENTION POLICY"what_is_time" ON "test _database"
4. 函数(Functions)
支持函数列表如下,详细描述请参考官方文档
Aggregations | Selectors | Transformations | Predictors |
COUNT() | BOTTOM() | CEILING() | HOLT_WINTERS() |
DISTINCT() | FIRST() | DERIVATIVE() | |
INTEGRAL() | LAST() | DIFFERENCE() | |
MEAN() | MAX() | ELAPSED() | |
MEDIAN() | MIN() | FLOOR() | |
MODE() | PERCENTILE() | HISTOGRAM() | |
SPREAD() | TOP() | MOVING_AVERAGE() | |
STDDEV() | | NON_NEGATIVE_DERIVATIVE() | |
SUM() | | | |
其中常用聚合操作,count(计算个数)、distinct(去重)、mean(求平均)、sum(求和)
公众号:itest_forever
CSDN:http://blog.csdn.NET/itest_2016
QQ群:274166295(爱测未来2群)、610934609(爱测未来3群)