时序时空数据库TSDB

    支持SQL查询

    基本操作说明

    TSDB支持SQL的查询接口,可以通过SQL语言实现对TSDB数据的筛选和查询。利用SQL的强大能力,用户即可以熟悉方便地对数据进行操作,也可以充分利用SQL函数的计算能力,挖掘数据价值。

    举个例子:一个智能电表监控的物联网集成方案,采集了智能电表的各个监控点的数据。在TSDB中这样组织(如下图),metric为SmartMeter,表示TSDB存的是智能电表的数据,每个电表有power和MeterCurrent两个域(field),用两个tag,即MeterID和City,来代表每个数据点来自哪个电表ID和城市。电表每5s上传一次功率值和电流值。

    可以将上表看成一个二维表,针对二维表来写SQL语句

    应用场景一:要过滤功率值大于400、电流值小于5,电表ID为2345HDYE的数据

    select timestamp, power, MeterCurrent, MeterID, City from SmartMeter where power > 400 and current<5 and MeterID = " 2345HDYE"

    应用场景二:电表ID为2345HDYE的电表中,返回每10秒的功率平均值

    select time_bucket(timestamp, '10 seconds') as TIME, avg(power) as AVG_POWER from SmartMeter group by time_bucket(timestamp, '10 seconds') order by time_bucket(timestamp, '10 seconds')

    应用场景三:join两个metric

    两个metric如下表

    用MeterID将两个表进行join,SQL语句如下: select * from SmartMeter join SmartTemperature on SmartMeter.MeterID = SmartTemperature.MeterID ;

    得到以下数据:

    TSDB_SQL_01-1

    SQL函数

    • 查询函数举例
    类型 SQL 解释
    时间查询 select timestamp from metric timestamp: 固定查询时间戳关键字
    metric:用户需要查询的metric名称
    单域查询 select value from metric value:用户需要查询的field的名称
    metric:用户需要查询的metric名称
    Tag查询 select tag_key from metric tag_key:用户需要查询的tag的key
    metric:用户需要查询的metric名称
    多列查询 select timestamp,field1 from metric
    select * from metric
    tag_key:用户需要查询的tag的key
    field1:用户需要查询的field的名称
    metric:用户需要查询的metric名称
    按照时间排序 select timestamp, value from metric order by timestamp
    select timestamp, value from metric order by timestamp desc
    select timestamp, value from metric order by timestamp offset 1 limit 1
    order by:查询排序方式
    asc/desc:升序/降序
    offset和limit:排序后从第offset条开始,取limit条
    带过滤查询 select timestamp, value from metric where value > 30 and timestamp >150937263000 value > 30:过滤value的值,UTC时间戳精度毫秒
    分组查询 select tag_key, count(1) from metric group by tag_key group by tag_key:按照tag的Key值分组
    聚合查询 select time_bucket(timestamp, '2 days') as DAY, sum(value) as SUM from metric group by time_bucket(timestamp, '2 days') order by time_bucket(timestamp, '2 days') time_bucket: 时间聚合函数
    timestamp:实际数据点中的时间戳字段(不用修改)
    2 days:时间窗口是2天
    group by time_bucket:按照时间2 days分组
    sum(value):聚合2 days的value的值

    time_bucket支持日历对齐和自定义起始时间:
    如time_bucket(timestamp, 1dc),字符'c'表示日历对齐。时间单位请参考文档时间单位
    窗口的默认起始时间是2000-01-01 00:00:00,如需自定义起始时间可以将'2 days'改成'2 days,2019-01-01 00:00:00',即表示从2019-01-01开始 每2天一个时间窗口。

    日历对齐规则示例如下:
    如time_bucket(timestamp,'1 dc,2019-01-01 12:00:00')
    则第一个窗口是01月01日12:00:00.000 - 01月01日23:59:59.99
    第二个窗口是01月02日00:00:00.000 - 01月02日23:59:59.99
    以此类推
    自定义函数计算查询 select timestamp, ((field2 - field1) * 10) as RESULT, tag_key from metric (field2 - field1) * 10 :用户自定义函数计算
    • 自定义函数

    TSDB支持标准ANSI SQL语义,选取常用的部分自定义函数如下所示。

    自定义函数说明:

    分类 支持函数 举例 说明
    条件表达式 CASE SELECT field1,
                   CASE field1
                             WHEN 1 THEN 'one'
                             WHEN 2 THEN 'two'
                             ELSE 'many'
                   END

    FROM metric
    case表达式
    条件表达式 IF SELECT field1,
                   IF (field1>100,1,0) as result

    FROM metric
    if表达式,if(condition, true_value)或
    if(condition, true_value, false_value)
    条件表达式 COALESCE SELECT field1, field2,
                   COALESCE (field1, field2) as result

    FROM metric
    返回列表中第一个非空值
    计算函数 abs(x) SELECT field1,
                    abs (field1) as result

    FROM metric
    返回x的绝对值
    计算函数 sqrt(x) SELECT field1,
                    sqrt (field1) as result

    FROM metric
    返回x的平方根
    计算函数 cbrt(x) SELECT field1,
                    cbrt (field1) as result

    FROM metric
    返回x的立方根
    计算函数 ceil(x) SELECT field1,
                   ceil (field1) as result

    FROM metric
    返回不小于x的最小整数值
    计算函数 ceiling(x) 同ceil(x) 返回大于或等于x的最小整数值
    计算函数 floor(x) SELECT field1,
                   floor (field1) as result

    FROM metric
    返回小于或等于x的最大整数值
    字符串操作 || SELECT field1 || field2 as result
    FROM metric
    字符串级联
    聚合函数 avg(x) SELECT time_bucket(timestamp, '2 days') as DAY, avg(field1) as result
    FROM metric group by time_bucket(timestamp, '2 days') order by time_bucket(timestamp, '2 days')
    返回field1的平均值
    聚合函数 count(*) SELECT count(*) as result
    FROM metric where timestamp < 1525611901
    返回数量
    聚合函数 count(x) SELECT time_bucket(timestamp, '2 days') as DAY, count(field1) as count
    FROM metric group by time_bucket(timestamp, '2 days') order by time_bucket(timestamp, '2 days')
    返回非空值的数量
    聚合函数 max_by(x, y) SELECT max_by(field1,field2) as result
    FROM metric where timestamp < 1525611901000
    返回y最大时的x,UTC时间戳精度毫秒
    聚合函数 min_by(x, y) SELECT min_by(field1,field2) as result
    FROM metric where timestamp < 1525611901000
    返回y最小时的x,UTC时间戳精度毫秒
    聚合函数 max(x) SELECT max(field1) as result
    FROM metric where timestamp < 1525611901000
    返回x的最大值,UTC时间戳精度毫秒
    聚合函数 min(x) SELECT min(field1) as result
    FROM metric where timestamp < 1525611901000
    返回x的最小值,UTC时间戳精度毫秒
    聚合函数 sum(x) SELECT time_bucket(timestamp, '2 days') as DAY, sum(field1) as sum
    FROM metric group by time_bucket(timestamp, '2 days') order by time_bucket(timestamp, '2 days')
    返回x的和值
    上一篇
    多用户访问控制
    下一篇
    对接hive-sql