TimescaleDB 开源时序数据库

• 开源中间件
  

1. # TimescaleDB
3. https://iothub.org.cn/docs/middleware/
4. https://iothub.org.cn/docs/middleware/timescale/timescale-summary/

复制代码

1.TimescaleDB介绍

TimescaleDB是基于PostgreSQL数据库打造的一款时序数据库，插件化的形式，随着PostgreSQL的版本升级而升级。
TimescaleDB具备以下特点

• 基于时序优化
  
• 自动分片（按时间、空间自动分片(chunk)）
  
• 全SQL接口
  
• 支持垂直于横向扩展
  
• 支持时间维度、空间维度自动分区。空间维度指属性字段（例如传感器ID，用户ID等）
  
• 支持多个SERVER，多个CHUNK的并行查询。分区在TimescaleDB中被称为chunk
  
• 自动调整CHUNK的大小
  
• 内部写优化（批量提交、内存索引、事务支持、数据倒灌）
  内存索引，由于chunk size比力适中，所以索引根本上都不会被交换出去，写性能比力好
  数据倒灌，由于有些传感器的数据可能写入耽误，导致需要写从前的chunk，timescaleDB允许如许的事情发生(可配置)
  
• 复杂查询优化（根据查询条件自动选择chunk，最近值获取优化(最小化的扫描,类似递归收敛)，limit子句pushdown到差别的server,chunks，并行的聚合操纵）
  《时序数据合并场景加快分析和实现 - 复合索引，窗口分组查询加快，变态递归加快》
  
• 利用已有的PostgreSQL特性（支持GIS，JOIN等），方便的管理（流复制、PITR）
  
• 支持自动的按时间保留策略（自动删除过旧数据）
  

1. # 官网地址
2. https://www.timescale.com/
4. # 文档
5. https://docs.timescale.com/latest/main
7. # 安装
8. https://docs.timescale.com/latest/getting-started/installation/rhel-centos/installation-yum
10. # github
11. https://github.com/timescale/timescaledb
13. # docker
14. https://hub.docker.com/r/timescale/timescaledb

复制代码

2.Hypertable 和 chunk

TimescaleDB作为PostgreSQL的扩展实现，这意味着Timescale数据库在整个PostgreSQL实例中运行。 该扩展模子允许数据库利用PostgreSQL的许多属性，如可靠性，安全性以及与各种第三方工具的连接性。 同时，TimescaleDB通过在PostgreSQL的查询规划器，数据模子和执行引擎中添加钩子，充分利用扩展可用的高度自界说。
从用户的角度来看，TimescaleDB公开了一些看起来像单数表的称为hypertable的表，它们实际上是一个抽象大概多单独表的假造视图，这些表包罗称为块的数据。
通过将hypertable的数据分别为一个或多个维度来创建块：所有可编程元素按时间隔断进行分区，而且可以通过诸如设备ID，位置，用户ID等的关键字进行分区。我们偶然将此称为分区 横跨“时间和空间”。

• Hypertable
  

与数据交互的重要点是一个可以抽象化的跨越所有空间和时间隔断的单个一连表，从而可以通过标准SQL查询它。
实际上，所有与TimescaleDB的用户交互都是利用可调整的。 创建表格和索引，修改表格，插入数据，选择数据等都可以（也应该）在hypertable上执行。
在TimescaleDB中创建一个超表需要两个简朴的SQL命令：创建表（利用标准SQL语法），然后选择CLEATEYHYTABLE（）。

• chunk
  

在内部，TimescaleDB自动将每个可分区块分割成块，每个块对应于特定的时间隔断和分区键空间的一个区域（利用散列）。 这些分区是不相交的（非重叠的），这有助于查询筹划职员最小化它必须打仗以办理查询的组块集合。
每个块都利用标准数据库表来实现。 （在PostgreSQL内部，这个块实际上是一个“父”可变的“子表”。）
块是正确的大小，确保表的索引的所有B树可以在插入期间驻留在内存中。 这可以避免在修改这些树中的任意位置时发生颠簸。

1. SELECT show_chunks('conditions');
2. SELECT show_chunks('conditions', older_than => INTERVAL '3 months');
3. SELECT show_chunks('conditions', older_than => DATE '2017-01-01');

复制代码

3.Hypertable

1. create_hypertable
3. SELECT * FROM create_hypertable(...)
5. # 创建超表
6. SELECT create_hypertable('conditions', 'time');
8. # 将表条件转换为超表，将chunk_time_interval设置为24小时。
9. SELECT create_hypertable('conditions', 'time', chunk_time_interval => 86400000000);
10. SELECT create_hypertable('conditions', 'time', chunk_time_interval => INTERVAL '1 day');
12. chunk_time_interval
13. Interval in event time that each chunk covers. Must be > 0. As of TimescaleDB v0.11.0, default is 7 days. For previous versions, default is 1 month.  
16. # 使用时间分区和位置分区（4个分区）将表条件转换为超表：
17. SELECT create_hypertable('conditions', 'time', 'location', 4);

复制代码

• create_hypertable()
  
  
  
  
• add_dimension()
  
  
  
  

4.Hypertable操纵

1. 1. 创建时序表(hypertable)
2. # Create a schema for a new hypertable  
3. CREATE TABLE sensor_data (  
4. "time" timestamp with time zone NOT NULL,  
5. device_id TEXT NOT NULL,  
6. location TEXT NULL,  
7. temperature NUMERIC NULL,  
8. humidity NUMERIC NULL,  
9. pm25 NUMERIC  
10. );  
11.   
12. # Create a hypertable from this data  
13. SELECT create_hypertable  
14. ('sensor_data', 'time', 'device_id', 16);  
16. 2. 迁移数据到hyper table
17. # Migrate data from existing Postgres table into  
18. # a TimescaleDB hypertable  
19. INSERT INTO sensor_data (SELECT * FROM old_data);  
21. 3. 查询hyper table
22. # Query hypertable like any SQL table  
23. SELECT device_id, AVG(temperature) from sensor_data  
24. WHERE temperature IS NOT NULL AND humidity > 0.5  
25. AND time > now() - interval '7 day'  
26. GROUP BY device_id;  
28. 4. 查询最近异常的数据
29. # Metrics about resource-constrained devices  
30. SELECT time, cpu, freemem, battery FROM devops  
31. WHERE device_id='foo'  
32. AND cpu > 0.7 AND freemem < 0.2  
33. ORDER BY time DESC  
34. LIMIT 100;  
36. 5. 计算最近7天，每小时的异常次数
37. # Calculate total errors by latest firmware versions  
38. # per hour over the last 7 days  
39. SELECT date_trunc('hour', time) as hour, firmware,  
40. COUNT(error_msg) as errno FROM data  
41. WHERE firmware > 50  
42. AND time > now() - interval '7 day'  
43. GROUP BY hour, firmware  
44. ORDER BY hour DESC, errno DESC;  
46. 6. 计算巴士的每小时平均速度
47. # Find average bus speed in last hour  
48. # for each NYC borough  
49. SELECT loc.region, AVG(bus.speed) FROM bus  
50. INNER JOIN loc ON (bus.bus_id = loc.bus_id)  
51. WHERE loc.city = 'nyc'  
52. AND bus.time > now() - interval '1 hour'  
53. GROUP BY loc.region;  
55. 7. 展示最近12小时，每小时的平均值
56. =#  SELECT date_trunc('hour', time) AS hour, AVG(weight)  
57.     FROM logs  
58.     WHERE device_type = 'pressure-sensor' AND customer_id = 440  
59.       AND time > now() - interval '12 hours'  
60.     GROUP BY hour;  
61.   
62. hour               | AVG(weight)  
63. --------------------+--------------  
64. 2017-01-04 12:00   | 170.0  
65. 2017-01-04 13:00   | 174.2  
66. 2017-01-04 14:00   | 174.0  
67. 2017-01-04 15:00   | 178.6  
68. 2017-01-04 16:00   | 173.0  
69. 2017-01-04 17:00   | 169.9  
70. 2017-01-04 18:00   | 168.1  
71. 2017-01-04 19:00   | 170.2  
72. 2017-01-04 20:00   | 167.4  
73. 2017-01-04 21:00   | 168.6  
75. 8. 监控每分钟过载的设备数量
76. =#  SELECT date_trunc('minute', time) AS minute, COUNT(device_id)  
77.     FROM logs  
78.     WHERE cpu_level > 0.9 AND free_mem < 1024  
79.       AND time > now() - interval '24 hours'  
80.     GROUP BY minute  
81.     ORDER BY COUNT(device_id) DESC LIMIT 25;  
82.   
83. minute             | heavy_load_devices  
84. --------------------+---------------------  
85. 2017-01-04 14:59   | 1653  
86. 2017-01-04 15:01   | 1650  
87. 2017-01-04 15:00   | 1605  
88. 2017-01-04 15:02   | 1594  
89. 2017-01-04 15:03   | 1594  
90. 2017-01-04 15:04   | 1561  
91. 2017-01-04 15:06   | 1499  
92. 2017-01-04 15:05   | 1460  
93. 2017-01-04 15:08   | 1459  
95. 9. 最近7天，按固件版本，输出每个固件版本的报错次数
96. =#  SELECT firmware_version, SUM(error_count) FROM logs  
97.     WHERE time > now() - interval '7 days'  
98.     GROUP BY firmware_version  
99.     ORDER BY SUM(error_count) DESC LIMIT 10;  
100.   
101. firmware_version  | SUM(error_count)  
102. -------------------+-------------------  
103. 1.0.10            | 191  
104. 1.1.0             | 180  
105. 1.1.1             | 179  
106. 1.0.8             | 164  
107. 1.1.3             | 161  
108. 1.1.2             | 152  
109. 1.2.1             | 144  
110. 1.2.0             | 137  
111. 1.0.7             | 130  
112. 1.0.5             | 112  
113. 1.2.2             | 110  
115. 10. 某个范围，每小时，温度高于90度的设备数量。
116. =#  SELECT date_trunc('hour', time) AS hour, COUNT(logs.device_id)  
117.     FROM logs  
118.     JOIN devices ON logs.device_id = devices.id  
119.     WHERE logs.temperature > 90 AND devices.location = 'SITE-1'  
120.     GROUP BY hour;  
121.   
122. hour               | COUNT(logs.device_id)  
123. --------------------+------------------------  
124. 2017-01-04 12:00   | 994  
125. 2017-01-04 13:00   | 905  
126. 2017-01-04 14:00   | 875  
127. 2017-01-04 15:00   | 910  
128. 2017-01-04 16:00   | 905  
129. 2017-01-04 17:00   | 840  
130. 2017-01-04 18:00   | 801  
131. 2017-01-04 19:00   | 813  
132. 2017-01-04 20:00   | 798  

复制代码

• 开源中间件
  

1. # TimescaleDB
3. https://iothub.org.cn/docs/middleware/
4. https://iothub.org.cn/docs/middleware/timescale/timescale-summary/

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。