Python工具箱系列(三十三)

指着月亮说上

Timescaledb
在物联网时代，出现了大量以时间为中心海量产生的传感器数据，称为时序数据。这类数据的特点是：

• 数据记录总有一个时间戳。
  
• 数据几乎总是追加，不更新也不删除。
  
• 大量使用近期的数据。很少更新或者回填时间间隔的缺失数据。
  
• 与时间间隔频率关系不大。但累积的数据量大，可能会有峰值。
  
• 对这类数据有多种聚合查询的需求，并且越快越好。例如，截止到目前为止，最大值/最小值/平均值是多少，数据流速是多少等。
  

为此，IT界兴起了时序数据库。TimeScaleDB是其中的佼佼者，截止到2022年7月，它的排名在第5名，值得使用。由于TimeScaleDB是postgresql的一个插件，因此非常便于安装与使用。同时，它也是一个开源的时间序列数据库，为快速获取和复杂查询进行了优化。此外，它也是多模型设计，在体现与时序数据相关的特性外，它执行的是“完整的SQL”，程序员很容易使用与管理它。
它的安装不复杂。使用以下命令在ubuntu bionic下安装单机版本。

1. apt install -y gnupg postgresql-common apt-transport-https lsb-release wget
2. /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
3. echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" > /etc/apt/sources.list.d/timescaledb.list
4. wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | apt-key add -
5. apt update
6. apt install -y timescaledb-2-postgresql-14
7. # 做一个调整
8. timescaledb-tune --quiet --yes
9. # 重新启动数据库服务
10. systemctl restart postgresql
12. # 以postgres用户启动命令行
13. su postgres -c psql
15. # 在psql命令行环境中。输入以下命令，从而关联到timescaledb这个扩展上。
16. CREATE database sensordb;
17. \c sensordb
18. CREATE EXTENSION IF NOT EXISTS timescaledb;
19. \q
21. # 再次连接
22. su postgres -c 'psql -d sensordb'
23. # 显示扩展列表(extensions)
24. \dx

复制代码

一、创建时序相关得表
在sensordb下创建测试用的表，这个创建的过程有些特殊。相关命令如下：
 

1. # 传感器表，传统的表
2. CREATE TABLE sensors(
3.   id SERIAL PRIMARY KEY,
4.   type VARCHAR(50),
5.   location VARCHAR(50)
6. );
8. # 传感器数据库，这个将转换成为超表
9. CREATE TABLE sensor_data (
10.   time TIMESTAMP NOT NULL,
11.   sensor_id INTEGER,
12.   pm25 DOUBLE PRECISION,
13.   temperature DOUBLE PRECISION,
14.   FOREIGN KEY (sensor_id) REFERENCES sensors (id)
15. );
17. # CREATE EXTENSION IF NOT EXISTS timescaledb;
19. # 转换为超表
20. SELECT create_hypertable('sensor_data', 'time');
22. # 生成4个传感器
23. INSERT INTO sensors (type, location) VALUES
24. ('a','地板'),
25. ('a', '天花板'),
26. ('b','地板'),
27. ('b', '天花板');
29. # 测试一下。
30. select * from sensors;

复制代码

 
接下来，使用python连接时序数据库，并且模拟相关的数据插入到表中。
二、使用Python模拟数据
 

1. import psycopg2
2. import random
3. import datetime
5. # 事先创建后数据库demodb
6. demodb = psycopg2.connect(database="sensordb", user="postgres",
7.                           password="88488848", host="172.17.2.151", port="5432")
9. democur = demodb.cursor()
11. currenttime = datetime.datetime.now()
13. # 插入模拟出来的数据。
14. for _ in range(100000):
15.     currenttime = currenttime+datetime.timedelta(seconds=1)
16.     for id in range(1,5,1):
17.         pm25 = random.uniform(0, 300)
18.         temp = random.uniform(0, 40)
19.         insertsql = f'''insert into sensor_data(sensor_id,pm25,temperature,time) values({id},{pm25},{temp},'{currenttime}')'''
20.         democur.execute(insertsql)
21.     demodb.commit()
23. democur.close()
24. demodb.close()

复制代码

 
这里插入10万秒的数据，相当于100000/86400=1.15(天)的数据。在插入数据的同时，就可以同时在数据库中进行按30分钟的分桶查询，这是时序数据库的一个特殊功能。

1. # su postgres -c 'psql -d sensordb'
3. SELECT
4.   time_bucket('30 minutes', time) AS period,
5.   AVG(temperature) AS avg_temp,
6.   last(temperature, time) AS last_temp,
7.   AVG(pm25) AS avg_pm25
8. FROM sensor_data
9. GROUP BY period;
11. SELECT
12.   time_bucket('60 minutes', time) AS period,
13.   AVG(temperature) AS avg_temp,
14.   last(temperature, time) AS last_temp,
15.   AVG(pm25) AS avg_pm25
16. FROM sensor_data
17. GROUP BY period;

复制代码

此时，按30分钟时间窗口聚会的数据查询效果如下图所示：

可以看出，TimeScaleDB已经将数据按30分钟来聚合分析。当然，改成任意时间也是可以的，例如，可以改成5秒分析一次也可以，生成结果的时间也非常快。
 

来源:https://www.cnblogs.com/shanxihualu/p/17434151.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！