[大数据]【时序数据库】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策略的信息

显示字段表述

示例

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上其他管理任务而创建的策略的信息和统计信息

显示字段表述

示例

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表信息

参数

查询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. 普通表变为超表后，如果对超表执行可压缩操作后，那么表结构将无法修改。