首页 > 数据库 >TimescaleDB时间序列数据库

TimescaleDB时间序列数据库

时间:2024-07-15 15:25:22浏览次数:19  
标签:聚合 chunk -- 压缩 TimescaleDB time 序列 数据库 SELECT

TimescaleDB:

这是一款支持完整sql开源的时间序列数据库。

用处

1、数据量庞大

2、只做时间索引类的插入

3、很少更新数据

TimescaleDB的好处:

  1. 基于时序优化
  2. 自动分片(自动按时间、空间分片(chunk))
  3. 全 SQL 接口
  4. 支持垂直于横向扩展
  5. 支持时间维度、空间维度自动分区。空间维度指属性字段(例如传感器 ID,用户 ID 等)
  6. 支持多个 SERVER,多个 CHUNK 的并行查询。分区在 TimescaleDB 中被称为 chunk。
  7. 自动调整 CHUNK 的大小
  8. 内部写优化(批量提交、内存索引、事务支持、数据倒灌)。
    内存索引,因为 chunk size 比较适中,所以索引基本上都不会被交换出去,写性能比较好。
    数据倒灌,因为有些传感器的数据可能写入延迟,导致需要写以前的 chunk,timescaleDB 允许这样的事情发生(可配置)。
  9. 复杂查询优化(根据查询条件自动选择 chunk,最近值获取优化(最小化的扫描,类似递归收敛),limit 子句 pushdown 到不同的 server,chunks,并行的聚合操作)
  10. 利用已有的 PostgreSQL 特性(支持 GIS,JOIN 等),方便的管理(流复制、PITR)
  11. 支持自动的按时间保留策略(自动删除过旧数据)

超表的创建

--将普通的表转换为超标 将时间字段作为分片字段
SELECT create_hypertable('表名', '字段');

超表的操作跟普通表的操作一样,只不过是插件自动帮我们分片

参数详解:

chunk_time_interval:显式配置时间间隔,就是根据时间来自行分区,默认是7天,选择时间间隔的关键属性是属于最近间隔的块(包括索引)(或块,如果使用空间分区)适合内存。因此,我们通常建议设置间隔,使这些块不超过主内存的 25%。

--创建超表的时候设置时间间隔 例:设置一天
SELECT create_hypertable('conditions', 'time', chunk_time_interval => 86400000000);
SELECT create_hypertable('conditions', 'time', chunk_time_interval => INTERVAL '1 day');

针对已创建的超标设置

SELECT set_chunk_time_interval('表名', interval '24 hours');
SELECT set_chunk_time_interval('表名', 86400000000);
按空间分区,创建4个分区
SELECT create_hypertable('表名', 'time', 'location', 4);

获取超表空间大小

SELECT hypertable_size('devices') ;

数据保留策略:

相当于任务,定时清除历史数据

--清除6个月以前的历史数据
SELECT add_retention_policy('表名', INTERVAL '6 months');
--删除现有表的策略
SELECT remove_retention_policy('表名');

手动删除旧的数据块

--要删除超过三周的所有数
SELECT drop_chunks('stocks_real_time', INTERVAL '3 weeks');
--删除超过两周但新于三周的所有数据:
SELECT drop_chunks(
  'stocks_real_time',
  older_than => INTERVAL '2 weeks',
  newer_than => INTERVAL '3 weeks'
)

创建连续聚合:

聚合查询:

--当前语句查询当前数据集 每天的 最高值,最低值,第一个值和最后的一个值
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol
ORDER BY day DESC, symbol;

从聚合查询变为连续聚合:

可以通过CREATE MATERIALIZED VIEW命令触发数据库创建具有给定名称的物化视图,(相当于里面是有数据存储的)WITH (timescaledb.continuous)指示 TimescaleDB 创建一个连续的聚合,而不仅仅是一个通用的物化视图。最后,在AS关键字之后添加之前的查询。

CREATE MATERIALIZED VIEW stock_candlestick_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol;

查询数据的话只需要查询当前视图数据即可

SELECT * FROM stock_candlestick_daily
  ORDER BY day DESC, symbol;

实时连续聚合

默认情况下,所有连续聚合都创建为实时聚合。这意味着 TimescaleDB 会将UNION尚未通过刷新策略具体化的最新数据附加(或)到连续聚合的输出中

使用和设置连续聚合策略:

1、自动连续聚合刷新策略

--此策略每天运行一次,由 设置schedule_interval。当它运行时,
--它会具体化 3 天前到 1 小时前的数据,由 start_offset和设置end_offset。
--偏移时间是相对于查询执行时间计算的。
--执行的查询是在连续聚合中定义的查询stock_candlestick_daily。
SELECT add_continuous_aggregate_policy('stock_candlestick_daily',
  start_offset => INTERVAL '3 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 days');

2、手动刷新

这在插入或修改超出刷新策略start_offset和end_offset间隔的数据时最有用。这在边缘物联网系统中很常见,其中设备长时间失去互联网连接,并在重新连接后最终发送历史读数。

--此手动刷新仅更新您的连续聚合一次。它不会自动使聚合保持最新。
--要设置自动刷新策略,请参阅前面关于连续聚合刷新策略的部分。
--当前语句是刷新1周前到当前的数据到连续聚合中
CALL refresh_continuous_aggregate(
  'stock_candlestick_daily',
  now() - INTERVAL '1 week',
  now()
);

数据压缩

要启用压缩,您需要ALTER超stocks_real_time表。启用压缩时可以指定三个参数:
timescaledb.compress(必需):在超表上启用 TimescaleDB 压缩
timescaledb.compress_orderby(可选):用于对压缩数据进行排序的列
timescaledb.compress_segmentby(可选):用于对压缩数据进行分组的列
如果不指定compress_orderby或compress_segmentby列,则压缩数据按超表时间列自动排序

在超表上启用压缩

ALTER TABLE stocks_real_time SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time DESC',
  timescaledb.compress_segmentby = 'symbol'
);

自动压缩:

压缩策略:

add_compression_policy:许您设置一个策略,系统会根据该策略在块达到给定年龄后在后台自动压缩块。

添加策略以压缩“cpu”超表上超过 60 天的块。

SELECT add_compression_policy('cpu', INTERVAL '60d');

压缩超表上超过两周的数据

SELECT add_compression_policy('stocks_real_time', INTERVAL '2 weeks');

与连续聚合策略和保留策略类似,当您运行此 SQL 时,包含至少两周前的数据的所有块都将被压缩stocks_real_time,并创建循环压缩策略。

不要试图压缩所有数据,这一点很重要。尽管您可以将新数据插入到压缩块中,但无法更新或删除压缩行。因此,最好只在数据老化后才压缩数据,一旦数据不太可能需要更新。

手动压缩:

compress_chunk 函数用于压缩特定的块。当用户想要更多地控制压缩的调度时,这最常用来代替 add_compression_policy函数。
使用此查询手动压缩包含超过 2 周的数据的块。如果您手动压缩超表块,请考虑添加 if_not_compressed=>true(设置为true跳过已压缩的块)到compress_chunk()函数中。否则,TimescaleDB 在尝试压缩已压缩的块时会显示错误

SELECT compress_chunk(i, if_not_compressed=>true)
  FROM show_chunks('stocks_real_time', older_than => INTERVAL ' 2 weeks') i;

验证压缩:

可以使用此查询检查超表的整体压缩率,以查看应用压缩前后压缩块的大小

SELECT pg_size_pretty(before_compression_total_bytes) as "before compression",
  pg_size_pretty(after_compression_total_bytes) as "after compression"
  FROM hypertable_compression_stats('stocks_real_time');

标签:聚合,chunk,--,压缩,TimescaleDB,time,序列,数据库,SELECT
From: https://blog.csdn.net/wxj_wxj_wxj/article/details/140440175

相关文章

  • MySQL高级应用指南:从数据库设计到高可用架构
    引言欢迎阅读这篇MySQL文章本文旨在帮助你更深入地了解和掌握MySQL数据库的高级应用技术通过一些实际的开发案例和代码示例你将能够更好地运用MySQL进行复杂的数据操作和管理分类教程一数据库设计与优化在设计和优化数据库时需要考虑到表结构(TableStructure)索引(I......
  • sysbench压测数据库
    版本号:sysbench1.1        简介  sysbench是一个基于LuaJIT的可脚本多线程基准测试工具,最常用于数据库基准测试,支持单点读写、随机读写、混合读写等各种测试类型。  sysbench并非通过JDBC的方式连接数据库,而是通过专门的客户端依赖进行。比如MySQL通......
  • Python数据库应用
      通过文件操作可以实现简单的数据操作功能,如果要处理的数据量巨大,则需要将数据存储在数据库中。Python支持多种数据库。  本章主要介绍数据库概念以及结构化数据库查询语言SQL,分析并理解Python自带的轻量级关系数据库SQLlite的使用方法(同样用于MySQL数据库)  文......
  • 基于mycat实现数据库的读写分离
    一、mycat分布式存储服务部署1.安装java运行环境(db01)[root@db01~]#yuminstall-yjava2.下载、解压mycat包#上传解压mycat程序包[root@db01~]#tarxfMycat-server-1.6.7.4-release-20200105164103-linux_.tar.gz -C/usr/local/[root@db01~]#ll/usr/local/......
  • java连接Oracle数据库
    第一步引用ojdbc.jar 详见https://www.cnblogs.com/hailexuexi/p/15062168.html注:项目代码换路径后要重新引用 注:编译项目时也要把ojdbc6.jar打到包里 完整的java代码OracleUtil.javapackagecom.JavaRabbitMQToDataBase.dbOracle;importjava.sql.Connectio......
  • 深度解析:分库分表策略在数据库性能优化中的核心作用
        目录分库分表的核心原理分库(Sharding)分表(Partitioning)综合运用与挑战在探讨分库分表的深度理解之前,先回顾一下为什么数据库系统会面临性能瓶颈。随着互联网业务的飞速发展,数据量呈指数级增长,同时高并发的访问需求对数据库的读写性能提出了更高要求。传统的......
  • java 连接 oracle数据库时报错 Oracle JDBC驱动未找到! No suitable driver found for
    在用IDEA编写java连接Oracle时,报错:OracleJDBC驱动未找到!可这部分之前测试是好用。想来想去。哦,我把这个项目代码换过路径,问题就出在这。需要重新引用下  ojdbc6.jar架包 下面是java连接oracle的部分代码ClassNotFoundException可以捕获OracleJDBC驱动未找到的异......
  • Windows版PostgreSQL数据库下载及安装教程(关系型数据库管理系统)
    前言PostgreSQL是一个功能非常强大的、源代码开放的客户/服务器关系型数据库管理系统(RDBMS)。PostgreSQL最初设想于1986年,当时被叫做BerkleyPostgresProject。该项目一直到1994年都处于演进和修改中,直到开发人员AndrewYu和JollyChen在Postgres中添加了一个SQL(StructuredQuery......
  • HSQL 数据库介绍(2)--使用
    本文主要介绍HSQLDB的基本使用,文中所使用到的软件版本:Java11.0.22、HSQLDB2.7.2。1、进程内模式直接使用JDBC连接数据库即可,如果数据库不存在会自动创建。1.1、file数据库@TestpublicvoidinProcessFile()throwsSQLException{StringdbName="test";/......
  • sqlalchemy pandas转化字典转为orm写入到sqlite数据库报错类型错误的解决办法
    使用pandas读取csv数据,然后将其转化为字典,再写入到数据库的时候,数据库总是报错类型错误,于是转为orm之前,统一转化一下类型fromsqlalchemyimportDECIMAL,Index,String,Date,Integer,Text,CHAR,SmallInteger,Float,Time,case,and_,extract,TypeDecoratorfrom......