influxdb命令 转载

2017年05月05日 10:17:33 标签： 数据

/ InfluxDB influxdb0.13命令 1、数据构成 INSERT cpu_load_short,host=server01,region=us-west value= 0.64 ,value2= 0.86

1434055562000000000

第一部分：“cpu_load_short,host=server01,region=us-west” 第一部分称为 key ，key中包含了measurement name（类似表）和tags（tags又分为tag key和tag value，tags可以有多个） 注意：在tag value中的空格应以“\”加上空格表示，tags中的值必须是string类型，其实是起到索引的作用

第二部分：“value=0.64,value2=0.86” 第二部分称为 Field ，同样和tags的形式相同，都是键值对的形式，但是tags中的值必须是string类型，而Field中的值可以为Integer、float、Boolean、string类型， 若为Integer类型，则值后必须加“i”，否则该值为float类型， 比如value=23意味着这个值23是float类型， 而value=23i，意味着值23是Integer类型。 Boolean类型的值的表示方式有很多，直接写成：t, T, true, TRUE, f, F, false或 FALSE都可以。 第三部分（可选）：“1434055562000000000” 第三部分称为 Timestamp ，是时间戳，如果该部分省略，则默认将当前时间的时间戳插入数据库，否则按照用户输入的时间戳插入。 注意：influxdb默认使用UTC时区展示数据 2、创建及使用数据库 CREATE DATABASE

” testDB “

– 创建数据库 show databases

– 展示所有数据库 use testDB使用

–数据库 3、增删改查命令 查询 表信息 SHOW MEASUREMENTS –查询当前数据库中含有的表 SHOW FIELD KEYS – 查看当前数据库所有表的字段 SHOW series from pay –查看key数据 SHOW TAG KEYS FROM “pay” –查看key中tag key值 SHOW TAG VALUES FROM “pay” WITH KEY = “merId”

–查看key中tag 指定key值对应的值 SHOW TAG VALUES FROM cpu WITH KEY IN (“region”, “host”) WHERE service = ‘redis’ DROP SERIES FROM < measurement_name[,measurement_name]> WHERE < tag_key>=’ < tag_value>’ –删除key SHOW CONTINUOUS QUERIES –查看连续执行命令 SHOW QUERIES – 查看最后执行命令 KILL QUERY < qid> – 结束命令 SHOW RETENTION POLICIES ON mydb –查看保留数据 查询数据 SELECT * FROM /.*/ LIMIT

1 – 查询当前数据库下所有表的第一行记录 select * from pay order by time desc limit 2 select * from db_name.”POLICIES name”. measurement_name –指定查询数据库下数据保留中的表数据

POLICIES name 数据保留 删除数据 delete from “query” – 删除表所有数据，则表就不存在了 drop MEASUREMENT “query” – 删除表（注意会把数据保留删除使用delete不会） DELETE FROM cpu DELETE FROM cpu WHERE time < ‘2000-01-01T00:00:00Z’ DELETE WHERE time < ‘2000-01-01T00:00:00Z’ DROP DATABASE “testDB” –删除数据库 DROP RETENTION POLICY “dbbak” ON mydb –删除保留数据为dbbak数据 DROP SERIES from pay where tag_key=’’ –删除key中的tag SHOW SHARDS –查看数据存储文件 DROP SHARD 1 SHOW SHARD GROUPS SHOW SUBSCRIPTIONS

4、函数使用 mean- 平均值 sum - 总和 min - 最小值 max - 最大值 count - 总个数 select

*

from

pay order by time desc limit

2 select

mean(allTime)

from

pay

where

time >= today() group by time(10m) time_zone(+ 8 ) select

*

from

pay time_zone(+ 8 ) limit

2 SELECT sum(allTime) FROM

” pay “

WHERE time > now() -

10s select

count(allTime)

from

pay

where

time > now() - 10m group by time(1s)

5、用户管理命令 SHOW USERS CREATE USER jdoe WITH PASSWORD

’ 1337password ‘

–

Create a normal database user. CREATE USER jdoe WITH PASSWORD

’ 1337password ‘

WITH ALL PRIVILEGES –

Create an admin user. REVOKE ALL PRIVILEGES FROM jdoe revoke admin privileges

from

jdoe REVOKE READ ON mydb FROM jdoe

– revoke read privileges

from

jdoe on mydb SHOW GRANTS FOR jdoe

– show grants

for

jdoe GRANT ALL TO jdoe

–

grant admin privileges GRANT READ ON mydb TO jdoe

–

grant read access to a database DROP USER jdoe

6、数据保留命令 查看保留期 SHOW RETENTION POLICIES ON mydb 修改保留期 ALTER RETENTION POLICY

default

ON online DEFAULT 删除保留期 DROP RETENTION POLICY

< retentionpolicy> ON < database> 创建 保留期

CREATE RETENTION POLICY

” rp_name “

ON

” db_name “

DURATION 30d REPLICATION

1

DEFAULT rp_name：策略名 db_name：具体的数据库名 30d：保存30天，30天之前的数据将被删除 它具有各种时间参数，比如：h（小时），w（星期） m minutes h hours d days w weeks INF infinite REPLICATION 1：副本个数，这里填1就可以了 DEFAULT 设为默认的策略

7、创建持续性数据处理结果 提供后续查询 – selects

from

default

retention policy and writes into 6_months retention policy CREATE CONTINUOUS QUERY

” 10m_event_count “ ON db_name BEGIN SELECT count(value) INTO

” 6_months “ .events FROM events GROUP BY time(10m) END; –

this

selects

from

the output of one continuous query

in

one retention policy and outputs to another series

in

another retention policy CREATE CONTINUOUS QUERY

” 1h_event_count “ ON db_name BEGIN SELECT sum(count)

as

count INTO

” 2_years “ .events FROM

” 6_months “ .events GROUP BY time(1h) END; –

this

customizes the resample interval so the interval

is

queried every 10s and intervals are resampled until 2m after their start time – when resample

is

used, at least one of

” EVERY “

or

” FOR “

must be used CREATE CONTINUOUS QUERY

” cpu_mean “ ON db_name RESAMPLE EVERY 10s FOR 2m BEGIN SELECT mean(value) INTO

” cpu_mean “ FROM

” cpu “ GROUP BY time(1m) END; DROP CONTINUOUS QUERY < cq_name> ON < database_name> –删除 SHOW CONTINUOUS QUERIES –查看连续执行命令 ================================================ 案例：根据tags查询交易成功与失败笔数，并保存到一个表中，每分钟统计1分钟内的 CREATE CONTINUOUS QUERY fail ON online BEGIN SELECT count(allTime) as fail INTO online.”default”.sign_result FROM online.”default”.sign where orderFlag=’0’ GROUP BY time(1m) END CREATE CONTINUOUS QUERY success ON online BEGIN SELECT count(allTime) as success INTO online.”default”.sign_result FROM online.”default”.sign where orderFlag=’1’ GROUP BY time(1m) END

select * from sign_result name: sign_result —————– time fail success 1478053740000000000 2 2 1478053800000000000 3 3 1478053860000000000 1 1 1478053920000000000 3 1

8、http api

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  1. 普通保存 < br>curl -i -X POST ‘http://127.0.0.1:8086/write?db=online’

–data-binary ‘pay,host=1,merId=1234567890,orderFlag=1 allTime=347,ecifTime=39,icqTime=88’ < strong>2.Write points from

a file by

passing @filename to curl. < /strong> cpu_data.txt内容如下：

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

Write the data in

cpu_data.txt to the mydb database with: curl -i -XPOST ‘ http://localhost:8086/write?db=mydb ‘

–data-binary @cpu_data.txt < strong>3.单查询 < /strong> < br>curl -GET ‘ http://localhost:8086/query?pretty=true ‘

–data-urlencode “db=mydb”

–data-urlencode “q=SELECT value FROM cpu_load_short WHERE region=’us-west’” < strong>4.多查询 < /strong> 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’;SELECT count(value) FROM cpu_load_short WHERE region=’us-west’” < strong>5.格式化time < /strong> 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’”

注意：如果是自己程序生成时间戳，进行数据保存后，查询时使用用select count（*） from pay进行查询总条数时，需要确认一下influxdb数据库时间与程序生成数据的机器时间，因为查询不添加时间条件默认采用当前系统时间，所以就会造成数据无法做到实时入库，数据查询总是延后； 9、常用命令 9.1 转化查询结果数据time格式 precision rfc3339

select

*

from

sign name: sign ———- time allTime ecifTime host icqTime icqTime1 merId orderFlag 1479880151976609227

348

0

195.203 . 56.35

0

0

305110099990002

null 1479880301566372997

724

0

195.203 . 56.35

641

0

305110048163089

0 1479880846739979577

28

0

195.203 . 56.35

12

0

305110099990002

0 1479881595261796657

25

0

195.203 . 56.35

10

0

305110099990002

0 1479881617138308807

106

0

195.203 . 56.35

17

0

305110099990002

0

precision rfc3339

select

*

from

sign name: sign ———- time allTime ecifTime host icqTime icqTime1 merId orderFlag 2016 - 11 -23T05: 49 : 11 .976609227Z

348

0

195.203 . 56.35

0

0

305110099990002

null 2016 - 11 -23T05: 51 : 41 .566372997Z

724

0

195.203 . 56.35

641

0

305110048163089

0 2016 - 11 -23T06: 00 : 46 .739979577Z

28

0

195.203 . 56.35

12

0

305110099990002

0 2016 - 11 -23T06: 13 : 15 .261796657Z

25

0

195.203 . 56.35

10

0

305110099990002

0 2016 - 11 -23T06: 13 : 37 .138308807Z

106

0

195.203 . 56.35

17

0

305110099990002 0 9.2按时间分组统计数据（分组只能用time()注意空格） select

count(allTime)

from

pay

where

time > now() - 15h group by time(1h) 9.3按指定时间段查询数据 select

count(allTime),mean(allTime)

from

pay

where

time>= ‘ 2016-11-30T16:00:00Z ‘ and time < = ‘ 2016-12-01T16:59:59Z ‘

and orderFlag= ‘ 1 ‘ 9.4脚本执行数据格式 influx -execute

” select count(allTime),mean(allTime) from pay where time>=’2016-12-10T16:00:00Z’and time < =’2016-12-11T16:59:59Z’ and orderFlag=’1’

”

-database

’ online ‘ ; 查询2016-12-11全天数据 格式： influx -execute “sql” -database ‘databasename’ 注意如果自己程序生成的时间戳作为time，则需要注意查询出的数据时间相差8小时，所以查某一天的数据需要减掉8小时，如上