LargestTriangleThreeBuckets dowsampling and Materialized View chaining

[beebee-ache]

Hello,

I am looking through the documentation for some of the interesting features in ClickHouse, and after experimenting I am having trouble figuring out if it is possible to use LTTB like the other aggregation functions.

As an example, I can't figure out what is wrong with the following declaration:

CREATE TABLE rawEvents (
    dateTime DateTime64(3, 'UTC'),
    value UInt32
) 
ENGINE = MergeTree 
ORDER BY dateTime;


CREATE TABLE downSampled
(
      date_time DateTime64(3, 'UTC') NOT NULL,
      down_sampled AggregateFunction(largestTriangleThreeBuckets(10), UInt32) 
)
ENGINE = AggregatingMergeTree() 
ORDER BY date_time;

CREATE MATERIALIZED VIEW downSampled_mv TO downSampled AS 
SELECT toStartOfMinute(dateTime) AS dateTime,
       largestTriangleThreeBuckets(10)(dateTime, value) AS updates
FROM rawEvents
GROUP BY dateTime;

INSERT INTO rawEvents (dateTime, value)
VALUES (now(), 1);

This results in

Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Aggregate function largestTriangleThreeBuckets requires two arguments. 

The guide to chaining materialized views state it takes the form AggregateFunction(<aggregationType>, <dataType>), and the second dataType is the state needed to store intermediate values. What is the correct declarations here?

What is the correct way to chain together LTTB materialized views?
And is someone willing to show what an example materialized view looks like to move into this table?

[den-crane]

Be careful with column names in MatView, they should match to column names in the destination table

https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf

CREATE TABLE downSampled
(
      date_time 
      down_sampled 
)
ENGINE = AggregatingMergeTree() 
ORDER BY date_time;

CREATE MATERIALIZED VIEW downSampled_mv TO downSampled AS 
SELECT toStartOfMinute(dateTime) AS dateTime,    ----<<<<<-  expected date_time !!!
       largestTriangleThreeBuckets(10)(dateTime, value) AS updates   ----<<<<<-  expected down_sampled !!!
FROM rawEvents
GROUP BY dateTime;

Also which dateTime do you want to use here largestTriangleThreeBuckets(10)(dateTime, value)?
rawEvents.dateTime or toStartOfMinute(dateTime) AS dateTime ?

https://fiddle.clickhouse.com/4531a768-3c2e-414e-afd6-765c9eb84ee9

[beebee-ache]

Thank you, that makes sense. I was confused about statement ' AS dateTime' was a global scope. I wanted to rawEvents.datetime

Is my understanding correct, that I can chain other materialized views onto this one, if I choose to write

largestTriangleThreeBucketsState(10)(dateTime, value) AS updates? Much like the example in documentation where they chain an avg aggregation function?

I would be interested in storing a table with LTTB applied to all values within a 1 month time buckets.

[den-crane]

chain other materialized views

You need to learn -MergeState combinator and others -Merge, -State

https://youtu.be/pryhI4F_zqQ
https://youtu.be/QDAJTKZT8y4
https://youtu.be/QUigKP7iy7Y

[den-crane]

Also MatView with largestTriangleThreeBuckets has no sense.
It stores the original data, but in another form, horizontally, not vertically.
Queries to such MatView are much slower than to original data.
Also it doubles the used disk space and doubles the insertion impact.

https://fiddle.clickhouse.com/d6fa9690-7c31-4df0-921d-79dbc3e20484

select to original/source data: 10 rows in set. Elapsed: 0.029 sec

select to materialized view: 10 rows in set. Elapsed: 0.111 sec.

[beebee-ache]

I thought that doing MatView on LTTB would calculate the downsampling on the intermediary result and then use that for later merges?
The documentation for the function is not very descriptive.

I am doing it this way, because I thought it would allow me to downsample datapoints in timeseries that are very spammy into a 'day' bucket with LTTB result for 1000 points. This is the behaviour I need, to collect 1 days worth of points and store them into the result of LTTB(1000).

How would I achieve that in a better way, since the -State does not do what I thought?

[den-crane]

I thought that doing MatView on LTTB would calculate the downsampling
on the intermediary result and then use that for later merges?

I have a very little knowledge of LTTB, but my impression was that the downsampling is not possible mathematically without loosing the accuracy.

[den-crane]

@sinsinan

[beebee-ache]

I think you are right in the general case. It might be that there is a sequential trick to it however, if you know the data is inserted chronologically and the x axis to downsample across is time. Thank you for pointing out this detail, I did not realise it. I am trying to look into methods for rolling up the data using LTTB with TTL instead..