背景
随着物联网的发展,时序数据库的需求越来越多,比如水文监控、工厂的设备监控、国家安全相关的数据监控、通讯监控、金融行业指标数据、传感器数据等。
在互联网行业中,也有着非常多的时序数据,例如用户访问网站的行为轨迹,应用程序产生的日志数据等等。
时序数据有几个特点
1. 基本上都是插入,没有更新的需求。
2. 数据基本上都有时间属性,随着时间的推移不断产生新的数据,旧的数据不需要保存太久。
业务方对时序数据通常有几个查询需求
1. 获取最新状态,查询最近的数据(例如传感器最新的状态)
2. 展示区间统计,指定时间范围,查询统计信息,例如平均值,最大值,最小值,计数等。。。
3. 获取异常数据,根据指定条件,筛选异常数据
时序数据库应该具备的特点
1. 压缩能力
通常用得上时序数据库的业务,传感器产生的数据量都是非常庞大的,数据压缩可以降低存储成本。
2. 自动rotate
时序数据通常对历史数据的保留时间间隔是有规定的,例如一个线上时序数据业务,可能只需要保留最近1周的数据。
为了方便使用,时序数据库必须有数据自动rotate的能力。
3. 支持分片,水平扩展
因为涉及的传感器可能很多,单个节点可能比较容易成为瓶颈,所以时序数据库应该具备水平扩展的能力,例如分表应该支持水平分区。
4. 自动扩展分区,
业务对时序数据的查询,往往都会带上对时间区间进行过滤,因此时序数据通常在分区时,一定会有一个时间分区的概念。时序数据库务必能够支持自动扩展分区,减少用户的管理量,不需要人为的干预自动扩展分区。例如1月份月末,自动创建2月份的分区。
5. 插入性能
时序数据,插入是一个强需求。对于插入性能要求较高。
6. 分区可删除
分区可以被删除,例如保留1个月的数据,1个月以前的分区都可以删除掉。
7. 易用性(SQL接口)
SQL是目前最通用的数据库访问语言,如果时序数据库能支持SQL是最好的。
8. 类型丰富
物联网的终端各异,会有越来越多的非标准类型的支持需求。例如采集图像的传感器,数据库中至少要能够存取图像的特征值。而对于其他垂直行业也是如此,为了最大程度的诠释业务,必须要有精准的数据类型来支撑。
9. 索引接口
支持索引,毫无疑问是为了加速查询而引入的。
10. 高效分析能力
时序数据,除了单条的查询,更多的是报表分析或者其他的分析类需求。这对时序数据库的统计能力也是一个挑战。
11. 其他特色
11.1 支持丰富的数据类型,数组、范围类型、JSON类型、K-V类型、GIS类型、图类型等。满足更多的工业化需求,例如传感器的位置信息、传感器上传的数据值的范围,批量以数组或JSON的形式上传,传感器甚至可能上传图片特征值,便于图片的分析。(例如国家安全相关),轨迹数据的上层则带有GIS属性。
这个世界需要的是支持类型丰富的时序数据库,而不是仅仅支持简单类型的时序数据库。
11.2 支持丰富的索引接口,因为类型丰富了,普通的B-TREE索引可能无法满足快速的检索需求,需要更多的索引来支持 数组、JSON、GIS、图特征值、K-V、范围类型等。 (例如PostgreSQL的gin, gist, sp-gist, brin, rum, bloom, hash索引接口)
这两点可以继承PostgreSQL数据库的已有功能,已完全满足。
前提条件
- 实例版本为PostgreSQL 11、12、13。
说明 如果实例版本为PostgreSQL 13,需要内核小版本大于等于20211130,如需升级内核小版本,请参见升级内核小版本。 - 使用该插件前,需要将timescaledb加入到shared_preload_libraries参数中。 您可以使用RDS PostgreSQL参数设置功能,为shared_preload_libraries参数添加timescaledb。具体操作,请参见设置实例参数。
说明 部分存量用户可能已经创建过TimescaleDB插件,如果升级内核小版本后,出现如下类似提示:
ERROR: could not access file "$libdir/timescaledb-1.3.0": No such file or directory
请在对应数据库执行如下SQL更新插件:
alter extension timescaledb update;
添加TimescaleDB插件
使用pgAdmin客户端连接实例,添加TimescaleDB,命令如下:
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
创建时序表
- 创建标准表conditions,示例如下:
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL,
humidity DOUBLE PRECISION NULL
);
- 创建时序表,示例如下:
SELECT create_hypertable('conditions', 'time');
说明 详细命令说明请参见Create a Hypertable。
高效写入
您可以使用标准SQL命令将数据插入超表(Hypertables),示例如下:
INSERT INTO conditions(time, location, temperature, humidity)
VALUES (NOW(), 'office', 70.0, 50.0);
您还可以一次将多行数据插入到超表中,示例如下:
INSERT INTO conditions
VALUES
(NOW(), 'office', 70.0, 50.0),
(NOW(), 'basement', 66.5, 60.0),
(NOW(), 'garage', 77.0, 65.2);
检索
您可以使用高级SQL查询检索数据,示例如下:
--过去3小时内,每15分钟采集一次数据,按时间和温度排序。
SELECT time_bucket('15 minutes', time) AS fifteen_min,
location, COUNT(*),
MAX(temperature) AS max_temp,
MAX(humidity) AS max_hum
FROM conditions
WHERE time > NOW() - interval '3 hours'
GROUP BY fifteen_min, location
ORDER BY fifteen_min DESC, max_temp DESC;
您也可以使用固有的函数进行分析查询,示例如下:
--均值查询(Median)
SELECT percentile_cont(0.5)
WITHIN GROUP (ORDER BY temperature)
FROM conditions;
--移动平均数(Moving Average)
SELECT time, AVG(temperature) OVER(ORDER BY time
ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
AS smooth_temp
FROM conditions
WHERE location = 'garage' and time > NOW() - interval '1 day'
ORDER BY time DESC;