使用用户自定义操作来降采样和压缩数据块

通过将连续聚合刷新策略与压缩策略相结合，Timescale 允许您降采样和压缩数据块。

如果您想实现这些策略不支持的功能，您可以编写用户自定义操作来降采样和压缩数据块。以下示例将原始数据降采样为每小时数据的平均值。这是一个说明性示例，可以使用连续聚合策略更简单地完成。但是您可以使查询任意复杂。

使用用户自定义操作来降采样和压缩数据块

创建一个过程，首先查询超表的数据块，以确定它们是否比 lag 参数旧。本例中的超表名为 metrics。如果数据块尚未压缩，则通过取原始数据的平均值来对其进行降采样。然后压缩它。临时表用于在计算平均值时存储数据。


CREATE OR REPLACE PROCEDURE downsample_compress (job_id int, config jsonb)
LANGUAGE PLPGSQL
AS $$
DECLARE
  lag interval;
  chunk REGCLASS;
  tmp_name name;
BEGIN
  SELECT jsonb_object_field_text (config, 'lag')::interval INTO STRICT lag;

  IF lag IS NULL THEN
    RAISE EXCEPTION 'Config must have lag';
  END IF;

  FOR chunk IN
    SELECT show.oid
    FROM show_chunks('metrics', older_than => lag) SHOW (oid)
      INNER JOIN pg_class pgc ON pgc.oid = show.oid
      INNER JOIN pg_namespace pgns ON pgc.relnamespace = pgns.oid
      INNER JOIN timescaledb_information.chunks chunk ON chunk.chunk_name = pgc.relname
        AND chunk.chunk_schema = pgns.nspname
    WHERE chunk.is_compressed::bool = FALSE
  LOOP
    RAISE NOTICE 'Processing chunk: %', chunk::text;

    -- build name for temp table
    SELECT '_tmp' || relname
    FROM pg_class
    WHERE oid = chunk INTO STRICT tmp_name;

    -- copy downsampled chunk data into temp table
    EXECUTE format($sql$ CREATE UNLOGGED TABLE %I AS
      SELECT time_bucket('1h', time), device_id, avg(value) FROM %s GROUP BY 1, 2;
    $sql$, tmp_name, chunk);

    -- clear original chunk
    EXECUTE format('TRUNCATE %s;', chunk);

    -- copy downsampled data back into chunk
    EXECUTE format('INSERT INTO %s(time, device_id, value) SELECT * FROM %I;', chunk, tmp_name);

    -- drop temp table
    EXECUTE format('DROP TABLE %I;', tmp_name);

    PERFORM compress_chunk (chunk);

    COMMIT;
  END LOOP;
END
$$;

注册该作业以每天运行。在 config 中，将 lag 设置为 12 个月，以删除包含 12 个月前数据的数据块。
```
SELECT add_job('downsample_compress','1d', config => '{"lag":"12 month"}');
```

使用用户自定义操作来降采样和压缩数据块

使用用户自定义操作来降采样和压缩数据块

相关内容