influxdb0.13命令
1胳螟、數(shù)據(jù)構(gòu)成
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可以有多個(gè)）
注意：在tag value中的空格應(yīng)以“\”加上空格表示糖耸，tags中的值必須是string類型秘遏，其實(shí)是起到索引的作用
第二部分：“value=0.64,value2=0.86”
第二部分稱為Field，同樣和tags的形式相同嘉竟，都是鍵值對的形式邦危，但是tags中的值必須是string類型洋侨，而Field中的值可以為Integer、float倦蚪、Boolean希坚、string類型，
若為Integer類型陵且，則值后必須加“i”裁僧，否則該值為float類型，
比如value=23意味著這個(gè)值23是float類型慕购，
而value=23i聊疲，意味著值23是Integer類型。
Boolean類型的值的表示方式有很多沪悲，直接寫成：t, T, true, TRUE, f, F, false或 FALSE都可以获洲。
第三部分（可選）：“1434055562000000000”
第三部分稱為Timestamp，是時(shí)間戳殿如，如果該部分省略贡珊，則默認(rèn)將當(dāng)前時(shí)間的時(shí)間戳插入數(shù)據(jù)庫，否則按照用戶輸入的時(shí)間戳插入涉馁。
注意：influxdb默認(rèn)使用UTC時(shí)區(qū)展示數(shù)據(jù)
2飞崖、創(chuàng)建及使用數(shù)據(jù)庫
CREATE DATABASE "testDB" --創(chuàng)建數(shù)據(jù)庫
show databases --展示所有數(shù)據(jù)庫
use testDB使用 --數(shù)據(jù)庫
3、增刪改查命令
復(fù)制代碼
查詢表信息
SHOW MEASUREMENTS --查詢當(dāng)前數(shù)據(jù)庫中含有的表
SHOW FIELD KEYS --查看當(dāng)前數(shù)據(jù)庫所有表的字段
SHOW series from pay --查看key數(shù)據(jù)
SHOW TAG KEYS FROM "pay" --查看key中tag key值
SHOW TAG VALUES FROM "pay" WITH KEY = "merId" --查看key中tag 指定key值對應(yīng)的值
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 --查看連續(xù)執(zhí)行命令
SHOW QUERIES --查看最后執(zhí)行命令
KILL QUERY <qid> --結(jié)束命令
SHOW RETENTION POLICIES ON mydb --查看保留數(shù)據(jù)
查詢數(shù)據(jù)
SELECT * FROM /.*/ LIMIT 1 --查詢當(dāng)前數(shù)據(jù)庫下所有表的第一行記錄
select * from pay order by time desc limit 2
select * from db_name."POLICIES name".measurement_name --指定查詢數(shù)據(jù)庫下數(shù)據(jù)保留中的表數(shù)據(jù) POLICIES name數(shù)據(jù)保留
刪除數(shù)據(jù)
delete from "query" --刪除表所有數(shù)據(jù)谨胞，則表就不存在了
drop MEASUREMENT "query" --刪除表（注意會把數(shù)據(jù)保留刪除使用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” --刪除數(shù)據(jù)庫
DROP RETENTION POLICY "dbbak" ON mydb --刪除保留數(shù)據(jù)為dbbak數(shù)據(jù)
DROP SERIES from pay where tag_key='' --刪除key中的tag
SHOW SHARDS --查看數(shù)據(jù)存儲文件
DROP SHARD 1
SHOW SHARD GROUPS
SHOW SUBSCRIPTIONS
復(fù)制代碼
4、函數(shù)使用
復(fù)制代碼
mean-平均值
sum-總和
min-最小值
max-最大值
count-總個(gè)數(shù)
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)
復(fù)制代碼
5蒜鸡、用戶管理命令
復(fù)制代碼
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
復(fù)制代碼
6胯努、數(shù)據(jù)保留命令
復(fù)制代碼
查看保留期 SHOW RETENTION POLICIES ON mydb
修改保留期 ALTER RETENTION POLICY default ON online DEFAULT
刪除保留期 DROP RETENTION POLICY <retentionpolicy> ON <database>
創(chuàng)建保留期 CREATE RETENTION POLICY "rp_name" ON "db_name" DURATION 30d REPLICATION 1 DEFAULT
rp_name：策略名
db_name：具體的數(shù)據(jù)庫名
30d：保存30天，30天之前的數(shù)據(jù)將被刪除
它具有各種時(shí)間參數(shù)逢防，比如：h（小時(shí)）叶沛，w（星期）m minutes h hours d days w weeks INF infinite
REPLICATION 1：副本個(gè)數(shù)，這里填1就可以了
DEFAULT 設(shè)為默認(rèn)的策略
復(fù)制代碼
7忘朝、創(chuàng)建持續(xù)性數(shù)據(jù)處理結(jié)果 提供后續(xù)查詢
復(fù)制代碼
-- 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 --查看連續(xù)執(zhí)行命令
================================================
案例：根據(jù)tags查詢交易成功與失敗筆數(shù)灰署，并保存到一個(gè)表中，每分鐘統(tǒng)計(jì)1分鐘內(nèi)的
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
復(fù)制代碼
8局嘁、http api
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
- 普通保存
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內(nèi)容如下：
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>
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'"
注意：如果是自己程序生成時(shí)間戳溉箕，進(jìn)行數(shù)據(jù)保存后，查詢時(shí)使用用select count（*） from pay進(jìn)行查詢總條數(shù)時(shí)悦昵，需要確認(rèn)一下influxdb數(shù)據(jù)庫時(shí)間與程序生成數(shù)據(jù)的機(jī)器時(shí)間肴茄，因?yàn)椴樵儾惶砑訒r(shí)間條件默認(rèn)采用當(dāng)前系統(tǒng)時(shí)間，所以就會造成數(shù)據(jù)無法做到實(shí)時(shí)入庫但指，數(shù)據(jù)查詢總是延后寡痰；
9抗楔、常用命令
9.1 轉(zhuǎn)化查詢結(jié)果數(shù)據(jù)time格式
precision rfc3339
復(fù)制代碼
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
復(fù)制代碼
9.2按時(shí)間分組統(tǒng)計(jì)數(shù)據(jù)（分組只能用time()注意空格）
select count(allTime) from pay where time > now() - 15h group by time(1h)
9.3按指定時(shí)間段查詢數(shù)據(jù)
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腳本執(zhí)行數(shù)據(jù)格式
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全天數(shù)據(jù)
格式： influx -execute "sql" -database 'databasename'
注意如果自己程序生成的時(shí)間戳作為time，則需要注意查詢出的數(shù)據(jù)時(shí)間相差8小時(shí)拦坠，所以查某一天的數(shù)據(jù)需要減掉8小時(shí)连躏，如上
