能源消耗分析

将时间序列数据导入超表

1. 将

   metrics.csv.gz
   解压到 <本地文件夹>。

   此测试数据集包含能源消耗数据。

   要直接从当前基于 PostgreSQL 的数据库导入高达 100GB 的数据，请使用原生的 PostgreSQL 工具进行停机迁移。要无缝导入 100GB-10TB+ 的数据，请使用 Timescale 提供的实时迁移工具。要从非 PostgreSQL 数据源添加数据，请参阅导入和摄取数据。

2. 在终端中，导航到 <本地文件夹> 并使用您的连接详情更新以下字符串以连接到您的服务。

   psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>?sslmode=require"

   复制

3. 为您的时间序列数据创建优化的超表

   1. 使用 CREATE TABLE 命令，为您的时间序列数据创建一个默认启用 Hypercore 的超表。为了在列式存储中高效查询数据，请记住根据您最常用于过滤数据的列进行 segmentby 设置。

      在您的 SQL 客户端中，运行以下命令：

      CREATE TABLE "metrics"(
        created timestamp with time zone default now() not null,
        type_id integer                                not null,
        value   double precision                       not null
      ) WITH (
        tsdb.hypertable,
        tsdb.partition_column='created',
        tsdb.segmentby = 'type_id',
        tsdb.orderby = 'created DESC'
      );

      复制

      如果您自托管 TimescaleDB v2.19.3 及更早版本，请先创建 PostgreSQL 关系表，然后使用 create_hypertable 将其转换为超表。然后通过调用 ALTER TABLE 启用 hypercore。

4. 将数据集上传到您的服务

   \COPY metrics FROM metrics.csv CSV;

   复制

快速查看您的数据

您查询超表的方式与查询关系型 PostgreSQL 表完全相同。使用以下 SQL 编辑器之一运行查询并查看您上传的数据：

• 数据模式：在 Timescale Console 中编写查询、可视化数据并分享结果，适用于您的所有 Timescale Cloud 服务。
• SQL 编辑器：在 Timescale Console 中更快、更准确地编写、修复和组织 SQL，用于 Timescale Cloud 服务。
• psql：从终端轻松地在您的 Timescale Cloud 服务或自托管 TimescaleDB 部署上运行查询。

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;

复制

对于如此大量的数据，此针对行式存储中数据的查询大约需要 3.6 秒。您会看到类似以下内容：

连接到您的 Timescale Cloud 服务

在 Timescale Console 中打开一个 SQL 编辑器。控制台中的编辑器会显示查询速度。您也可以使用 psql 连接到您的服务。

添加策略以在特定时间间隔将块转换为列式存储

例如，数据添加到表后 60 天

CALL add_columnstore_policy('metrics', INTERVAL '8 days');

复制

请参阅 add_columnstore_policy。

列式存储中数据的更快分析查询

现在再次运行分析查询

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;

复制

对于如此大量的数据，此针对列式存储中数据的分析查询大约需要 250 毫秒。

日常监测能源消耗

1. 为能源消耗创建连续聚合 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;

   复制

2. 添加刷新策略以保持 kwh_day_by_day 最新

   SELECT add_continuous_aggregate_policy('kwh_day_by_day',
      start_offset => NULL,
      end_offset => INTERVAL '1 hour',
      schedule_interval => INTERVAL '1 hour');

   复制

按小时监测能源消耗

1. 为能源消耗创建连续聚合 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;

   复制

2. 添加刷新策略以保持连续聚合最新

   SELECT add_continuous_aggregate_policy('kwh_hour_by_hour',
    start_offset => NULL,
       end_offset => INTERVAL '1 hour',
       schedule_interval => INTERVAL '1 hour');

   复制

分析您的数据

现在您已经创建了连续聚合，最好使用它们对您的数据进行分析。例如，要查看过去一年中工作日平均能源消耗的变化，请运行以下查询：

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);

复制

您会看到类似以下内容：

登录 Grafana

在您的浏览器中，登录以下任一地址：

• 自托管 Grafana：在 http://localhost:3000/。默认凭据是 admin，admin。
• Grafana Cloud：使用您创建账户时设置的 URL 和凭据。

将您的服务添加为数据源

1. 打开 Connections > Data sources，然后点击 Add new data source。

2. 从列表中选择 PostgreSQL。

3. 配置连接

   • 主机 URL、数据库名称、用户名和 密码

     使用您的连接详情进行配置。主机 URL 格式为 <主机>:<端口>。

   • TLS/SSL 模式：选择 require。

   • PostgreSQL 选项：启用 TimescaleDB。

   • 其他所有字段保留默认设置。

4. 点击 保存并测试。

   Grafana 检查您的详细信息是否设置正确。

创建仪表盘

1. 在 仪表盘 页面上，点击 新建 并选择 新建仪表盘。

2. 点击 添加可视化，然后选择连接到您的 Timescale Cloud 服务的数据源和 条形图 可视化。

   

   展开图片

3. 在 查询 部分，选择 代码，然后根据您的连续聚合运行以下查询：

   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;

   复制

   此查询按小时对特定时区家庭的能源消耗结果进行平均并按时间排序。由于您使用了连续聚合，这些数据始终是实时准确的。

   

   展开图片

   您会发现能源消耗在傍晚和早餐时间最高。您还知道傍晚时分风力会减弱。这些数据证明您需要在高峰时段提供补充电源，或计划在白天储存能源以备高峰时段使用。

点击 保存仪表盘