influxDB 常用操作

Devops海洋的渔夫

发布于 2019-05-31 10:36:44

2K0

发布于 2019-05-31 10:36:44

查看数据库

> show databases
name: databases
name
----
_internal
nmon_reports
nmon2influxdb_log
>

删除数据库

> drop database nmon_reports
> drop database nmon2influxdb_log

使用\切换数据库

> use nmon_reports
Using database nmon_reports

查看所有表数据

> show measurements
name: measurements
name
----
CPU_ALL
...
>

删除单表数据

> drop measurement CPU_ALL

查询单表10条数据

> select * from CPU_ALL limit 10
name: CPU_ALL
time                host                  name   value
----                ----                  ----   -----
1551694907000000000 qc_predepl_cms-ngx-02 CPUs   4
1551694907000000000 qc_predepl_cms-ngx-02 Idle%  96.9
1551694907000000000 qc_predepl_cms-ngx-02 Steal% 0
1551694907000000000 qc_predepl_cms-ngx-02 Sys%   0.9
1551694907000000000 qc_predepl_cms-ngx-02 User%  0.7
1551694907000000000 qc_predepl_cms-ngx-02 Wait%  1.5
1551694910000000000 qc_predepl_cms-ngx-02 CPUs   4
1551694910000000000 qc_predepl_cms-ngx-02 Idle%  99.7
1551694910000000000 qc_predepl_cms-ngx-02 Steal% 0
1551694910000000000 qc_predepl_cms-ngx-02 Sys%   0.1
>

查询表中最大的值

> select max(*) from CPU_ALL
name: CPU_ALL
time                max_value
----                ---------
1551695447000000000 100
>

设置时间显示格式

在influxDB的CLI界面执行precision rfc3339即可，但是显示是UTC的时区，与中国时区差了8个小时，需要在查询语句的最后加上tz('Asia/Shanghai')，这样查询的时间才是纠正为中国时区显示。

> precision rfc3339

> select * from CPU_ALL where time >= '2018-11-23 14:30:39' and time <= '2019-11-23 14:32:32' limit 10

name: CPU_ALL
time                 host                  name   value
----                 ----                  ----   -----
2019-03-04T10:21:47Z qc_predepl_cms-ngx-02 CPUs   4
2019-03-04T10:21:47Z qc_predepl_cms-ngx-02 Idle%  96.9
2019-03-04T10:21:47Z qc_predepl_cms-ngx-02 Steal% 0
2019-03-04T10:21:47Z qc_predepl_cms-ngx-02 Sys%   0.9
2019-03-04T10:21:47Z qc_predepl_cms-ngx-02 User%  0.7
2019-03-04T10:21:47Z qc_predepl_cms-ngx-02 Wait%  1.5
2019-03-04T10:21:50Z qc_predepl_cms-ngx-02 CPUs   4
2019-03-04T10:21:50Z qc_predepl_cms-ngx-02 Idle%  99.7
2019-03-04T10:21:50Z qc_predepl_cms-ngx-02 Steal% 0
2019-03-04T10:21:50Z qc_predepl_cms-ngx-02 Sys%   0.1
> 

> select * from CPU_ALL where time >= '2018-11-23 14:30:39' and time <= '2019-11-23 14:32:32' limit 10 tz('Asia/Shanghai')
name: CPU_ALL
time                      host                  name   value
----                      ----                  ----   -----
2019-03-04T18:21:47+08:00 qc_predepl_cms-ngx-02 CPUs   4
2019-03-04T18:21:47+08:00 qc_predepl_cms-ngx-02 Idle%  96.9
2019-03-04T18:21:47+08:00 qc_predepl_cms-ngx-02 Steal% 0
2019-03-04T18:21:47+08:00 qc_predepl_cms-ngx-02 Sys%   0.9
2019-03-04T18:21:47+08:00 qc_predepl_cms-ngx-02 User%  0.7
2019-03-04T18:21:47+08:00 qc_predepl_cms-ngx-02 Wait%  1.5
2019-03-04T18:21:50+08:00 qc_predepl_cms-ngx-02 CPUs   4
2019-03-04T18:21:50+08:00 qc_predepl_cms-ngx-02 Idle%  99.7
2019-03-04T18:21:50+08:00 qc_predepl_cms-ngx-02 Steal% 0
2019-03-04T18:21:50+08:00 qc_predepl_cms-ngx-02 Sys%   0.1
>

条件查询

查询某时间下的数据

> select * from CPU_ALL where "time" = 1551694910000000000
name: CPU_ALL
time                host                  name   value
----                ----                  ----   -----
1551694910000000000 qc_predepl_cms-ngx-02 CPUs   4
1551694910000000000 qc_predepl_cms-ngx-02 Idle%  99.7
1551694910000000000 qc_predepl_cms-ngx-02 Steal% 0
1551694910000000000 qc_predepl_cms-ngx-02 Sys%   0.1
1551694910000000000 qc_predepl_cms-ngx-02 User%  0.2
1551694910000000000 qc_predepl_cms-ngx-02 Wait%  0
>

查询某个时间返回的数据，设置时区为上海时区

> select * from CPU_ALL where time >= '2018-11-23 14:30:39' and time <= '2019-11-23 14:32:32' tz('Asia/Shanghai')

查询特定字段数据

select * from table_name where "字段1" =~ /匹配值/

> select * from CPU_All3 limit 10
name: CPU_All3
time                Cpus Idle% Steal% Sys% User% Wait% host
----                ---- ----- ------ ---- ----- ----- ----
1551689409000000000 4    94.5  0      0.9  0.7   3.9   qc_predepl_cms-ngx-02
1551689412000000000 4    99.8  0      0.2  0.1   0     qc_predepl_cms-ngx-02
1551689415000000000 4    99.5  0      0    0.1   0.4   qc_predepl_cms-ngx-02
1551689418000000000 4    99.4  0      0.1  0.1   0.4   qc_predepl_cms-ngx-02
1551689421000000000 4    99.7  0      0.2  0.2   0     qc_predepl_cms-ngx-02
1551689424000000000 4    99.7  0      0.1  0.1   0.2   qc_predepl_cms-ngx-02
1551689427000000000 4    99.5  0      0.2  0.2   0.2   qc_predepl_cms-ngx-02
1551689430000000000 4    99.7  0      0.2  0.2   0     qc_predepl_cms-ngx-02
1551689433000000000 4    99.7  0      0.1  0.2   0.1   qc_predepl_cms-ngx-02
1551689436000000000 4    99.8  0      0.1  0.1   0     qc_predepl_cms-ngx-02
> 
> 
> SELECT * FROM "CPU_All3" WHERE time < now() - 5m and "Idle%" =~ /94/
name: CPU_All3
time                Cpus Idle% Steal% Sys% User% Wait% host
----                ---- ----- ------ ---- ----- ----- ----
1551689409000000000 4    94.5  0      0.9  0.7   3.9   qc_predepl_cms-ngx-02
1551694925000000000 4    94.8  0      3.5  1.3   0.4   qc_predepl_cms-ngx-02
1551694937000000000 4    94.2  0      4.3  1.3   0.3   qc_predepl_cms-ngx-02
> 
> SELECT * FROM "CPU_All3" WHERE time < now() - 5m and "Idle%" =~ /94.5/
name: CPU_All3
time                Cpus Idle% Steal% Sys% User% Wait% host
----                ---- ----- ------ ---- ----- ----- ----
1551689409000000000 4    94.5  0      0.9  0.7   3.9   qc_predepl_cms-ngx-02
> 
> 
> SELECT * FROM "CPU_All3" WHERE time < now() - 5m and "Idle%" =~ /94.5/ and host =~ /qc_predepl_cms/
name: CPU_All3
time                Cpus Idle% Steal% Sys% User% Wait% host
----                ---- ----- ------ ---- ----- ----- ----
1551689409000000000 4    94.5  0      0.9  0.7   3.9   qc_predepl_cms-ngx-02
>

倒序查询

> select * from CPU_ALL order by time desc limit 10 tz('Asia/Shanghai')
name: CPU_ALL
time                      host                  name   value
----                      ----                  ----   -----
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 Wait%  0.3
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 User%  0.1
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 Sys%   0.1
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 Steal% 0
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 Idle%  99.5
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 CPUs   4
2019-03-04T18:31:41+08:00 qc_predepl_cms-ngx-02 Wait%  0
2019-03-04T18:31:41+08:00 qc_predepl_cms-ngx-02 User%  0.1
2019-03-04T18:31:41+08:00 qc_predepl_cms-ngx-02 Sys%   0.2
2019-03-04T18:31:41+08:00 qc_predepl_cms-ngx-02 Steal% 0

Distinct去重查询

> SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"

name: h2o_feet
time                   count
----                   -----
1970-01-01T00:00:00Z   4

Max()最大值、Min() 最小值

> select min(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time                      min_value
----                      ---------
2019-03-04T18:21:47+08:00 0
> 
> select max(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time                      max_value
----                      ---------
2019-03-04T18:30:47+08:00 100
>

Mean()查询平均值

> select mean(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time                      mean_value
----                      ----------
1970-01-01T08:00:00+08:00 17.336166666666678
>

MEDIAN() 中位数

返回查询结果中间的数值。

> select median(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time                      median_value
----                      ------------
1970-01-01T08:00:00+08:00 0.4
>

SPREAD()最小值与最大值之间的数值差距

> select spread(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time                      spread_value
----                      ------------
1970-01-01T08:00:00+08:00 100
>

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019.05.22 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

数据库

sql

登录后参与评论

0 条评论

热度

influxDB 常用操作

influxDB 常用操作

查看数据库

删除数据库

使用\切换数据库

查看所有表数据

删除单表数据

查询单表10条数据

查询表中最大的值

设置时间显示格式

条件查询

倒序查询

Distinct去重查询

Max()最大值、Min() 最小值

Mean()查询平均值

MEDIAN() 中位数

SPREAD()最小值与最大值之间的数值差距

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐