【时序数据库】TimescaleDB操作
郗学
时序数据库操作
创建时序数据库hypertable
1、创建一个标准表（PostgreSQL docs）。
CREATE TABLE data (
ts tsSTAMPTZ NOT NULL,
point TEXT NOT NULL,
value DOUBLE PRECISION NULL
);
2.转换为hypertable（执行tsscaleDB 命令）
首先将表转换data为hypertable，仅对column进行时间分区ts，然后在point四个分区上添加一个额外的分区键：
#将普通表转换为时序表（对时间进行分区）
SELECT create_hypertable('data', 'ts');
SELECT create_hypertable('data', 'ts', chunk_time_interval => INTERVAL '1 day');
#或则可以给时间块添加时间间隔，默认为7天（1.5版本以前为30天）
SELECT add_dimension('data', 'ts', chunk_time_interval => INTERVAL '1 day');
#重新设置时间分区间隔，只会对新创建的分区生效
SELECT set_chunk_time_interval('data', INTERVAL '24 hours');
SELECT set_chunk_time_interval('data', 86400000000);
SELECT set_chunk_time_interval('data', 86400000); #unix是毫秒
另外也可以添加空间分区
#直接创建
SELECT create_hypertable('data', 'ts', 'point', 4); #添加4个空间分区，根据point划分分区
#若hypertable已存在，可以采用 add_dimension直接进行添加，number_partitions为空间分区的个数，必须指定
SELECT create_hypertable('data', 'ts');
SELECT add_dimension('data', 'point', number_partitions => 4);
#重新设置空间分区，只会对新创建的分区生效
SELECT set_number_partitions('data', 2);#单个维度
SELECT set_number_partitions('data', 2, 'device_id'); #多个维度
创建索引
#创建索引（索引可以针对块进行事物，而不是整个hypertable）
CREATE INDEX ON data(ts, point) USING brin
WITH (tsscaledb.transaction_per_chunk);
批量删除
drop_chunks（）
删除时间范围完全落在指定时间之前（或之后）的数据块，（不同与delete，直接删除磁盘文件也会清除）
older_than 截止点的规范，所有早于此时间戳的完整块都应删除。
table_name 从中删除块的hypertable或连续聚合。
newer_than 截止点的规范，所有比此时间戳新的完整块都应删除。
可选参数
cascade 数据级联，默认为FALSE
实例
#保留最近三个月以内的数据
SELECT drop_chunks(INTERVAL '3 months', 'data');
#删除早于三个月以前的所有数据，包括相关对象（例如，视图）：
SELECT drop_chunks(INTERVAL '3 months', 'data', cascade => TRUE);
#删除，前四个月到前三个月之间的数据
SELECT drop_chunks(older_than => INTERVAL '3 months', newer_than => INTERVAL '4 months', table_name => 'data')
#删除超过未来三个月的数据
SELECT drop_chunks(newer_than => now() + INTERVAL '3 months', table_name => 'data');
#删除2017年之前所有的数据
SELECT drop_chunks(DATE '2017-01-01', 'data');
SELECT drop_chunks(1483228800000, 'data'); # 时间戳操作也可以
查看当前块
show_chunks（）
hypertable 从中选择块的hypertable名称。如果未提供，则显示所有块。
older_than 截止点的规范，应在该截止点处显示所有早于此时间戳的完整块。
newer_than 截止点的规范，其中应显示比此时间戳新的完整块。
实例
#查看所有的块
SELECT show_chunks();
#获取与表关联的所有块
SELECT show_chunks('data');
#获取近三个月的所有块
SELECT show_chunks(older_than => INTERVAL '3 months');
自动化策略
add_drop_chunks_policy（）
创建删除策略，使用后会返回一个job_id（整形），每个hypertable只能存在一个删除策略。
#创建策略 只保留保留最近七天的数据（直接删除块）
SELECT add_drop_chunks_policy('data', INTERVAL '6 months');
删除策略
remove_drop_chunks_policy（）
删除特定超级表的块的策略。
#传入要删除策略的表名
SELECT remove_drop_chunks_policy('data');
alter_job_schedule（）
计划任务，定时任务执行，引导job_id，
#设置表策略（更新也是这个），job_id为策略返回的job_id
示例
# 设置策略 每两天运行一次排序策略 #另外有批量删除策略（drop_chunks_policies)
SELECT alter_job_schedule(job_id, schedule_interval => INTERVAL '2 days')
FROM tsscaledb_information.reorder_policies
WHERE hypertable = 'data'::regclass;
#data_agg视图的连续聚合作业，使其每五分钟运行一次
SELECT alter_job_schedule(job_id, schedule_interval => INTERVAL '5 minutes')
FROM tsscaledb_information.continuous_aggregate_stats
WHERE view_name = 'data_agg'::regclass;
#job_id 1015 指定下一次作业（2020年3月15日上午9:00:00）开始
SELECT alter_job_schedule(1015, next_start => '2020-03-15 09:00:00.0+00');
查看当前设置的所有策略
tsscaledb_information.drop_chunks_policies
显示有关由用户创建的drop_chunks策略的信息
显示字段表述
| 字段名 | 描述 |
|---|---|
| hypertable | （REGCLASS）应用策略的超级表的名称 |
| older_than | （间隔）运行该策略时，将丢弃比此时间长得多的块 |
| cascade | （布尔值）是否在级联选项打开的情况下运行策略，这将导致依赖对象以及块被丢弃。 |
| job_id | （INTEGER）为实施drop_chunks策略而设置的后台作业的ID |
| schedule_interval | （间隔）作业运行的间隔 |
| max_runts | （间隔）后台作业调度程序在停止作业之前将允许其运行的最长时间 |
| max_retries | （整数）如果作业失败，将重试该作业的次数 |
| retry_period | （间隔）调度程序在两次失败重试之间等待的时间 |
示例
SELECT * FROM tsscaledb_information.drop_chunks_policies; # 查询策略
hypertable | older_than | cascade | job_id | schedule_interval | max_runts | max_retries | retry_period | cascade_to_materializations
------------+---------------+---------+--------+-------------------+-------------+-------------+--------------+-----------------------------
data | (t,"7 days",) | t | 1011 | 1 day | 00:05:00 | -1 | 00:05:00 | f
查询策略状态
tsscaledb_information.policy_stats
显示有关为管理数据保留以及hypertable上其他管理任务而创建的策略的信息和统计信息
显示字段表述
| 字段名 | 描述 |
|---|---|
| hypertable | （REGCLASS）应用策略的超级表的名称 |
| job_id | （INTEGER）为实施策略而创建的后台作业的ID |
| job_type | （文本）创建作业以实施的策略类型 |
| last_run_success | （布尔值）上次运行成功还是失败 |
| last_finish | 上次运行结束的时间 |
| last_start | 上次运行开始的时间 |
| next_start | 下一次运行的时间 |
| total_runs | （整数）此作业的运行总数 |
| total_failures | （整数）此作业失败的总次数 |
示例
SELECT * FROM tsscaledb_information.policy_stats; #查询当前策略状态
#结果
hypertable | job_id | job_type | last_run_success | last_finish | last_successful_finish | last_start | next_start | total_runs | total_failures
------------+--------+-------------+------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+------------+----------------
data | 1011 | drop_chunks | t | 2020-07-26 13:52:02.552562+08 | 2020-07-26 13:52:02.552562+08 | 2020-07-26 13:52:02.050851+08 | 2020-07-27 13:52:02.552562+08 | 3 | 0
查询hypertable表信息
参数
| 字段名 | 描述 |
|---|---|
| table_schema | hypertable的架构名称。 |
| table_name | hypertable的表名。 |
| table_owner | hypertable的所有者。 |
| num_dimensions | 尺寸数。 |
| num_chunks | 块数。 |
| table_size | hypertable使用的磁盘空间 |
| index_size | 索引使用的磁盘空间 |
| toast_size | 大字段的磁盘空间 |
| total_size | 指定表使用的总磁盘空间，包括所有索引和TOAST数据 |
查询hypertable的信息
SELECT * FROM tsscaledb_information.hypertable;
#结果
table_schema | table_name | table_owner | num_dimensions | num_chunks | table_size | index_size | toast_size | total_size
--------------+------------+-------------+----------------+------------+------------+------------+------------+------------
public | metrics | postgres | 1 | 5 | 99 MB | 96 MB | | 195 MB
public | devices | postgres | 1 | 1 | 8192 bytes | 16 kB | | 24 kB
需要注意的几点
\1. 普通表变为超表需要满足表中字段无主键，或者时间序列字段为主键。
比如平常的表只对id设置了主键，没有对时间序列设置主键，这样会导致无法转换超表。
解决办法：(1) 取消id的主键 （2）对事件序列也设置主键。
\2. 普通表转为超表必须表内数据为空。
\3. 普通表变为超表后，如果对超表执行可压缩操作后，那么表结构将无法修改。
类似资料：