Set chunk time interval on the continuous aggregates materialization view

user_9708225 0
Viewed 388

I was trying to set the chunk time interval materialization view created through continuous aggregates, using command from timescale doc

To create the continuous aggregates:

CREATE MATERIALIZED VIEW device_summary
WITH (timescaledb.continuous)
AS
SELECT
  time_bucket('1 hour', observation_time ) AS bucket,
  min(observation_time::timestamp) AS min_time,
  device_id,
  avg(metric) AS metric_avg,
  max(metric) - min(metric) AS metric_spread
FROM
  device_readings
GROUP BY bucket, device_id;

and set the chunk time interval (mentioned in best practice section)

SELECT set_chunk_time_interval('device_summary', INTERVAL '10 days');

It return me error of device_summary is not hypertable, it ask me to set chunk time interval on the hypertable, where I think it is caused by the device_summary is a view.

I can see my chunk interval of materialization view through SELECT * FROM timescaledb_information.chunks.My question is, is it possible to set the chunk time interval for materialization view?

timescaledb
2 Answers

to set interval for hypertable of Continuous Aggregates(CA), you first find hypertable of your CA from following query:

SELECT view_name, format('%I.%I', materialization_hypertable_schema,
   materialization_hypertable_name) AS materialization_hypertable
   FROM timescaledb_information.continuous_aggregates where view_name='device_summary';

e.g.

   view_name   |             materialization_hypertable
---------------------+-----------------------------------------------------
 device_summary| _timescaledb_internal._materialized_hypertable_1394

then use

SELECT set_chunk_time_interval('_timescaledb_internal._materialized_hypertable_1394', INTERVAL '10 days');

if your main table(device_readings) has data, you should remember that use WITH NO DATA; in CA definition to prevent create chunks with default interval. Then you can use

CALL refresh_continuous_aggregate('device_summary', START_TIME , END TIME);

to fill CA data;

user_48317780 
select * from timescaledb_information.continuous_aggregates

This gives you the materialization_hypertable_name. (See the tip in the docs link that you pasted above). Use this as an argument to set_chunk_time_interval.

user_160394140
Related