Timescale处理时序数据示例 - 能源使用分析
简介
Timescale是PostgreSQL的时序数据库扩展，非常适合时间序列、事件和分析等负载。Timescale是建立在PostgreSQL之上的，开发者可以访问整个PostgreSQL生态系统。Timescale最核心的特性如下：
- Hypertable
Hypertable可以自动按时间对数据进行分区，开发者可以获得PostgreSQL的全部好处，同时可以更容易的管理时序数据。
- 持续聚合
时序数据通常增长非常快，这意味着对数据进行统计汇总会变得非常缓慢。持续聚合以增量方式聚合数据，因此获得实时统计数据的速度变得非常快。
- 压缩
压缩时序数据可以使存储节省90%以上，由于从磁盘加载数据量变少，查询速度也变的更快。
部署Timescale数据库
Timescale是PostgreSQL的时序数据库扩展，因此需要先安装PostgreSQL并加载该插件，比较方便的方式是实用已经做好的docker镜像：
docker pull nimblex/memfiredb:latest
如果觉得自己部署麻烦，MemFire Cloud提供了在线版本，点两下鼠标就能创建一个免费版本的数据库，已经集成了Timescale插件。
使用timescale建表（hypertable）
Hypertables是Timescale的核心，Hypertables使Timescale能够高效地处理时间序列数据。Timescale是PostgreSQL的一个插件，因此我们可以充分利用所有标准的PostgreSQL的能力，包括表、索引、存储过程等。使用Timescale和使用PostgreSQL的体验几乎没有区别。
创建Hypertable表分两个步骤，第一步创建一个普通的PostgreSQL表：
CREATE TABLE "metrics"(
created timestamp with time zone default now() not null,
type_id integer not null,
value double precision not null
);
第二步，使用create_hypertable 函数将pg表转换成hypertable ，并使用时间字段作为分区键。该函数的第一个参数为要转换的表名，第二个参数为timestamp类型的字段名：
SELECT create_hypertable('metrics', by_range('created'));
注意：`by_range` 是Timescale 2.13版本增加的特性。
加载数据
测试数据集下载地址：metrics.csv.gz
下载后，解压文件，如果PostgreSQL运行在本地，可以使用copy命令导入数据：
\COPY metrics FROM metrics.csv CSV;
如果是在云端，则可以使用客户端工具如dbeaver导入csv文件到数据库中。
数据导入后，可以查看一下导入的数据：
SELECT * FROM metrics LIMIT 5;
会得到如下结果：
created | type_id | value
-------------------------------+---------+-------
2023-05-31 23:59:59.043264+00 | 13 | 1.78
2023-05-31 23:59:59.042673+00 | 2 | 126
2023-05-31 23:59:59.042667+00 | 11 | 1.79
2023-05-31 23:59:59.042623+00 | 23 | 0.408
2023-05-31 23:59:59.042603+00 | 12 | 0.96
聚合数据
时序数据通常增长非常快，这意味着将数据汇总成有用的摘要会变得非常缓慢。Timescale的持续聚合（Continuous aggregate）功能专门用来解决该问题，可以加速聚合数据的速度。
如果数据产生的频率非常高，您可能希望将数据按分钟或小时进行聚合。例如，如果你有一张表记录了每秒的温度数据，你可能想查询每小时的平均温度。每次运行此查询时，数据库都需要扫描整个表，并重新计算平均值，随着数据量的积累，该操作会越来越慢。持续聚合功能可以用来解决该问题。
持续聚合也是一种Hypertable，当添加新数据或修改旧数据时，Timescale会跟踪数据集的更改，并在后台自动更新持续聚合背后的Hypertable。
持续聚合是一种雾化视图，但你不需要手动刷新持续聚合，它们会在后台不断更新。持续聚合的维护负担也比常规PostgreSQL物化视图低得多，因为整个视图不是在每次刷新时从头开始创建的。这意味着你可以继续处理数据，而不是维护数据库。
因为持续聚合是基于Hypertable的，所以您可以用与其他表完全相同的方式查询它们，并在持续聚合上启用压缩或分层存储。你甚至可以在持续聚合的基础上创建持续聚合。
从持续聚合查询到的总是最新的实时数据，并且不需要在查询时进行大量的计算，因此查询速度飞快。
- 为每天的能源消耗创建持续聚合：kwh_day_by_day
CREATE MATERIALIZED VIEW kwh_day_by_day(time, value)
with (timescaledb.continuous) as
SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time",
round((last(value, created) - first(value, created)) * 100.) / 100. AS value
FROM metrics
WHERE type_id = 5
GROUP BY 1;
添加刷新策略以使持续聚合保持最新数据：
SELECT add_continuous_aggregate_policy('kwh_day_by_day',
start_offset => NULL,
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
- 为每小时的能耗创建持续聚合：kwh_hour_by_hour
CREATE MATERIALIZED VIEW kwh_hour_by_hour(time, value)
with (timescaledb.continuous) as
SELECT time_bucket('01:00:00', metrics.created, 'Europe/Berlin') AS "time",
round((last(value, created) - first(value, created)) * 100.) / 100. AS value
FROM metrics
WHERE type_id = 5
GROUP BY 1;
添加刷新策略以使持续聚合保持最新数据：
SELECT add_continuous_aggregate_policy('kwh_hour_by_hour',
start_offset => NULL,
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
- 查询continuous_aggregates表，确定持续聚合创建成功了
执行下面的语句：
SELECT view_name, format('%I.%I', materialization_hypertable_schema,materialization_hypertable_name) AS materialization_hypertable
FROM timescaledb_information.continuous_aggregates;
会得到如下结果：
view_name | materialization_hypertable
------------------+--------------------------------------------------
kwh_day_by_day | _timescaledb_internal._materialized_hypertable_2
kwh_hour_by_hour | _timescaledb_internal._materialized_hypertable_3
查询数据
加载数据集后，我们尝试进行一些查询，看看能得到什么结果。本教程使用Timescale的一些函数来构造查询语句，以完成标准PostgreSQL中不可能完成的任务。
在本节中，我们将学习如何构造SQL来回答以下问题：
- 一天中每小时的能耗
- 工作日的能源消耗。
- 每月能源消耗。
查询一天中每小时段消耗能源情况
使用Timescale Toolkit来计算中位数，使用标准的PostgreSQL max函数计算最大值：
WITH per_hour AS (
SELECT
time,
value
FROM kwh_hour_by_hour
WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year'
ORDER BY 1
), hourly AS (
SELECT
extract(HOUR FROM time) * interval '1 hour' as hour,
value
FROM per_hour
)
SELECT
hour,
approx_percentile(0.50, percentile_agg(value)) as median,
max(value) as maximum
FROM hourly
GROUP BY 1
ORDER BY 1;
结果如下：
hour | median | maximum
----------+--------------------+---------
00:00:00 | 0.5998949812512439 | 0.6
01:00:00 | 0.5998949812512439 | 0.6
02:00:00 | 0.5998949812512439 | 0.6
03:00:00 | 1.6015944383271534 | 1.9
04:00:00 | 2.5986701108275327 | 2.7
05:00:00 | 1.4007385207185301 | 3.4
06:00:00 | 0.5998949812512439 | 2.7
07:00:00 | 0.6997720645753496 | 0.8
08:00:00 | 0.6997720645753496 | 0.8
09:00:00 | 0.6997720645753496 | 0.8
10:00:00 | 0.9003240409125329 | 1.1
11:00:00 | 0.8001143897618259 | 0.9
查询一周中每天的能源消耗量
WITH per_day AS (
SELECT
time,
value
FROM kwh_day_by_day
WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year'
ORDER BY 1
), daily AS (
SELECT
to_char(time, 'Dy') as day,
value
FROM per_day
), percentile AS (
SELECT
day,
approx_percentile(0.50, percentile_agg(value)) as value
FROM daily
GROUP BY 1
ORDER BY 1
)
SELECT
d.day,
d.ordinal,
pd.value
FROM unnest(array['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']) WITH ORDINALITY AS d(day, ordinal)
LEFT JOIN percentile pd ON lower(pd.day) = lower(d.day);
结果如下：
day | ordinal | value
-----+---------+--------------------
Mon | 2 | 23.08078714975423
Sun | 1 | 19.511430831944395
Tue | 3 | 25.003118897837307
Wed | 4 | 8.09300571759772
Sat | 7 |
Fri | 6 |
Thu | 5 |
查询每月的能源消耗量
WITH per_day AS (
SELECT
time,
value
FROM kwh_day_by_day
WHERE "time" > now() - interval '1 year'
ORDER BY 1
), per_month AS (
SELECT
to_char(time, 'Mon') as month,
sum(value) as value
FROM per_day
GROUP BY 1
)
SELECT
m.month,
m.ordinal,
pd.value
FROM unnest(array['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']) WITH ORDINALITY AS m(month, ordinal)
LEFT JOIN per_month pd ON lower(pd.month) = lower(m.month)
ORDER BY ordinal;
结果如下：
month | ordinal | value
-------+---------+-------------------
Jan | 1 |
Feb | 2 |
Mar | 3 |
Apr | 4 |
May | 5 | 75.69999999999999
Jun | 6 |
Jul | 7 |
Aug | 8 |
Sep | 9 |
Oct | 10 |
Nov | 11 |
Dec | 12 |
设置压缩
我们已经了解了如何为能耗数据集创建Hypertable并对其进行查询。对于时序类型的数据，很少需要更新旧数据，而且随着时间的推移，表中的数据量会增加。由于这些数据大多是不可变的，可以对其进行压缩以节省空间并避免产生额外成本。
TimescaleDB以更高效的格式存储数据，与普通PostgreSQL表相比，压缩率高达20倍。TimescaleDB压缩是在PostgreSQL中原生实现的，不需要特殊的存储格式。相反，它依靠PostgreSQL的特性在压缩之前将数据转换为列存。由于相似的数据是相邻存储的，所以使用列存可以获得更好的压缩比。
列存压缩的一个额外好处是，某些查询明显更快，因为需要读取到内存中的数据更少。
- 使用Alter table命令，对表启用压缩，并设置segmentby字段和orderby字段：
ALTER TABLE metrics
SET (
timescaledb.compress,
timescaledb.compress_segmentby='type_id',
timescaledb.compress_orderby='created DESC'
);
segmentby和orderby的选择不同，性能和压缩比会不一样，如何正确的选中列，参考这里。
- 开启压缩后，可以手动压缩数据
SELECT compress_chunk(c) from show_chunks('metrics') c;
也可以配置自动压缩策略，在下一节中会详细介绍。
- 查看压缩效果
SELECT
pg_size_pretty(before_compression_total_bytes) as before,
pg_size_pretty(after_compression_total_bytes) as after
FROM hypertable_compression_stats('metrics');
结果：
before | after
--------+-------
180 MB | 16 MB
(1 row)
设置自动压缩策略
为了避免每次有数据要压缩时都手动运行压缩，可以设置压缩策略。压缩策略允许您压缩超过特定时间的数据，例如，压缩超过8天的所有数据块：
SELECT add_compression_policy('metrics', INTERVAL '8 days');
压缩策略定期运行，默认情况下每天运行一次，这意味着使用上述设置，可能有长达9天的未压缩数据。
更多关于压缩策略的信息可以查看这里。
查询加速
前面我们将压缩设置为按type_id列值进行分段（segmentby），这意味着通过对该列进行过滤或分组来获取数据将更加高效。同时，我们按created字段进行降序排序，在查询语句中使用该字段进行降序排序查询性能会更好。
下面是一个利用上述规则加速查询的例子：
SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time",
round((last(value, created) - first(value, created)) *
100.) / 100. AS value
FROM metrics
WHERE type_id = 5
GROUP BY 1;
在压缩和解压的情况下，分别执行上述SQL，会看到相当大的性能差异。
解压数据的方法：
SELECT decompress_chunk(c) from show_chunks('metrics') c;
使用Grafana可视化数据
- 将Timescale添加为Grafana的数据源
安装Grafana后，打开Grafana的dashboard 页面，默认用户名和密码是admin/admin。切换到Configuration → Data sources页面，点击Add data source按钮，搜索PostgreSQL，并选中。
Name字段根据需要写一个名字Host字段，填写数据库的IP:PORTDatabase字段, 填写数据库名字，通常是postgresUser字段, 填写数据库账号，通常是postgresPassword字段, 填写数据库密码TLS/SSL Mode字段, 根据你部署的实际情况来选择。PostgreSQL details字段, 开启TimescaleDB功能。- 展示能源消耗情况
要在Grafana中将其可视化，先创建一个新面板，然后选择条形图可视化。选择数据源，然后键入上一步中的SQL语句，在 Format as配置项，选择 Table.
选择一个配色方案，以便以不同的颜色显示不同的消耗。在选项面板的 Standard options选项下，将 Color scheme 设置为 by value。
配置完成后，展示效果如下：
转载自:https://juejin.cn/post/7350197071943024703