原指标数据表
CREATE TABLE metrics.samples (
`date` Date DEFAULT toDate(0),
`name` String,
`tags` Array(String),
`val` Float64,
`ts` DateTime,
`updated` DateTime DEFAULT now()
) ENGINE = MergeTree(date, (name, tags, ts), 8192)
创建表
CREATE TABLE metrics.samplesnew (
`date` Date,
name String,
tags Array(String),
ts DateTime,
updated DateTime,
avg Float64,
max Float64,
min Float64
)
ENGINE = MergeTree(date, (name, tags, ts), 8192)
查询数据
select name,tags,avg(val),max(val),min(val) from samples where val>=0 group by name,tags
查询分组聚合数据
select `date`,name,tags,avg(val) as avg,max(val) as max,min(val) as min, `date` as ts,`date` as updated from samples where val>=0 group by `date`,name,tags
删除表
DROP TABLE metrics.samplesnew;
删除表中数据
ALTER TABLE samplesnew delete where 1=1;
将统计查询结果导入创建的新表中
insert into samplesnew(`date`,name,tags,avg,max,min,ts,updated) select `date`,name,tags,avg(val) as avg,max(val) as max,min(val) as min, `date` as ts,`date` as updated from samples where val>=0 group by `date`,name,tags
使用时间函数查询
select date,name,tags,avg,max,min,ts,updated from samplesnew where toUnixTimestamp(ts)>1578561978 and toUnixTimestamp(ts)<1580680800 order by ts desc limit 10
查询按照ts排序:
SELECT * FROM
metrics.samples
where name = 'ecs_cpu_util'
order by ts desc
LIMIT 100
prometheus查询远端库
select avg,ts,tags from metrics.samplesnew where ts>=toDateTime(1577068260) and ts<=toDateTime(1580524260) and arrayExists(x -> x IN ('instance=pushgateway'), tags) = 1 and arrayExists(x -> x IN ('job=pushgateway'), tags) = 1 and name='go_memstats_frees_total' order by ts
时间戳转换
select toUnixTimestamp('2020-01-16 19:00:00'),toUnixTimestamp('2020-01-19 08:00:00');
select toDateTime(1578561978),toDateTime(1580680800);