时序数据库操作

转载

创建时序数据库hypertable

1、创建一个标准表（PostgreSQL docs）。

 

CREATE TABLE conditions (

 time        TIMESTAMPTZ       NOT NULL,

 location    TEXT              NOT NULL,

 temperature DOUBLE PRECISION  NULL

);

2.转换为hypertable（执行TimescaleDB 命令）

首先将表转换conditions为hypertable，仅对column进行时间分区time，然后在location四个分区上添加一个额外的分区键：

#将普通表转换为时序表（对时间进行分区）

SELECT create_hypertable('conditions', 'time');

SELECT create_hypertable('conditions', 'time', chunk_time_interval => INTERVAL '1 day');

#或则可以给时间块添加时间间隔，默认为7天（1.5版本以前为30天）

SELECT add_dimension('conditions', 'time', chunk_time_interval => INTERVAL '1 day');

 

#重新设置时间分区间隔，只会对新创建的分区生效

SELECT set_chunk_time_interval('conditions', INTERVAL '24 hours');

SELECT set_chunk_time_interval('conditions', 86400000000);

SELECT set_chunk_time_interval('conditions', 86400000); #unix是毫秒

 

另外也可以添加空间分区

#直接创建

SELECT create_hypertable('conditions', 'time', 'location', 4); #添加4个空间分区，根据location划分分区

#若hypertable已存在，可以采用 add_dimension直接进行添加，number_partitions为空间分区的个数，必须指定

SELECT create_hypertable('conditions', 'time');

SELECT add_dimension('conditions', 'location', number_partitions => 4);

#重新设置空间分区，只会对新创建的分区生效

SELECT set_number_partitions('conditions', 2);#单个维度

SELECT set_number_partitions('conditions', 2, 'device_id'); #多个维度

创建索引

#创建索引（索引可以针对块进行事物，而不是整个hypertable）

CREATE INDEX ON conditions(time, location) USING brin
  WITH (timescaledb.transaction_per_chunk);

 

批量删除

drop_chunks（）

删除时间范围完全落在指定时间之前（或之后）的数据块，（不同与delete，直接删除磁盘文件也会清除）
older_than 截止点的规范，所有早于此时间戳的完整块都应删除。
table_name 从中删除块的hypertable或连续聚合。
newer_than 截止点的规范，所有比此时间戳新的完整块都应删除。

可选参数
cascade 数据级联，默认为FALSE

实例

#保留最近三个月以内的数据

SELECT drop_chunks(INTERVAL '3 months', 'conditions');

#删除早于三个月以前的所有数据，包括相关对象（例如，视图）：

SELECT drop_chunks(INTERVAL '3 months', 'conditions', cascade => TRUE);

#删除，前四个月到前三个月之间的数据

SELECT drop_chunks(older_than => INTERVAL '3 months', newer_than => INTERVAL '4 months', table_name => 'conditions')

#删除超过未来三个月的数据

SELECT drop_chunks(newer_than => now() + INTERVAL '3 months', table_name => 'conditions');

#删除2017年之前所有的数据

SELECT drop_chunks(DATE '2017-01-01', 'conditions');

SELECT drop_chunks(1483228800000, 'conditions'); # 时间戳操作也可以

 

查看当前块

show_chunks（）

hypertable 从中选择块的hypertable名称。如果未提供，则显示所有块。
older_than 截止点的规范，应在该截止点处显示所有早于此时间戳的完整块。
newer_than 截止点的规范，其中应显示比此时间戳新的完整块。

实例

#查看所有的块

SELECT show_chunks();

#获取与表关联的所有块

SELECT show_chunks('conditions');

#获取近三个月的所有块

SELECT show_chunks(older_than => INTERVAL '3 months');

自动化策略

add_drop_chunks_policy（）

创建删除策略，使用后会返回一个job_id（整形），每个hypertable只能存在一个删除策略。

#创建策略 只保留保留最近七天的数据（直接删除块）

SELECT add_drop_chunks_policy('conditions', INTERVAL '6 months');

---

删除策略

remove_drop_chunks_policy（）

删除特定超级表的块的策略。

#传入要删除策略的表名
SELECT remove_drop_chunks_policy('conditions');

alter_job_schedule（）

计划任务，定时任务执行，引导job_id，
#设置表策略（更新也是这个），job_id为策略返回的job_id

示例

# 设置策略 每两天运行一次排序策略 #另外有批量删除策略（drop_chunks_policies)

SELECT alter_job_schedule(job_id, schedule_interval => INTERVAL '2 days')

FROM timescaledb_information.reorder_policies  

WHERE hypertable = 'conditions'::regclass;

#conditions_agg视图的连续聚合作业，使其每五分钟运行一次

SELECT alter_job_schedule(job_id, schedule_interval => INTERVAL '5 minutes')

FROM timescaledb_information.continuous_aggregate_stats

WHERE view_name = 'conditions_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');

查看当前设置的所有策略

timescaledb_information.drop_chunks_policies

显示有关由用户创建的drop_chunks策略的信息

显示字段表述

字段名	描述
hypertable	（REGCLASS）应用策略的超级表的名称
older_than	（间隔）运行该策略时，将丢弃比此时间长得多的块
cascade	（布尔值）是否在级联选项打开的情况下运行策略，这将导致依赖对象以及块被丢弃。
job_id	（INTEGER）为实施drop_chunks策略而设置的后台作业的ID
schedule_interval	（间隔）作业运行的间隔
max_runtime	（间隔）后台作业调度程序在停止作业之前将允许其运行的最长时间
max_retries	（整数）如果作业失败，将重试该作业的次数
retry_period	（间隔）调度程序在两次失败重试之间等待的时间

示例

SELECT * FROM timescaledb_information.drop_chunks_policies; # 查询策略

 hypertable |  older_than   | cascade | job_id | schedule_interval | max_runtime | max_retries | retry_period | cascade_to_materializations

------------+---------------+---------+--------+-------------------+-------------+-------------+--------------+-----------------------------

 conditions | (t,"7 days",) | t       |   1011 | 1 day             | 00:05:00    |          -1 | 00:05:00     | f

 

---

查询策略状态

timescaledb_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 timescaledb_information.policy_stats; #查询当前策略状态

#结果

hypertable | job_id |  job_type   | last_run_success |          last_finish          |    last_successful_finish     |          last_start           |          next_start           | total_runs | total_failures

------------+--------+-------------+------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+------------+----------------

 conditions |   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 timescaledb_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