Search code examples
clickhouse

Clickhouse AggregateFunction + quantilesApprox

CREATE TABLE IF NOT EXISTS q
(
  timestamp DateTime,
  query LowCardinality(String),
  latency_histogram AggregateFunction(quantilesApprox(100, 0.5, 0.75, 0.9, 0.99), UInt64),
)
ENGINE = AggregatingMergeTree()
ORDER BY (toDate(timestamp), query, toStartOfMinute(time))

I'd like to make timeseries of latency quantiles. want to store only aggregated values to save the storage, so didn't use the separate materialized view. How can it be done in Clickhouse? The above query gives the error.

Code: 63. DB::Exception: Unknown aggregate function quantilesApprox. (UNKNOWN_AGGREGATE_FUNCTION) (version 23.3.1.2823 (official build)

Also an insert query example for the aggregating table would be appreciated.

Solution

  • Upgrade your ClickHouse version to 23.4.1 (quantiles approximation was added in April, just 2 weeks ago: https://github.com/ClickHouse/ClickHouse/pull/46428).

    If your repositories do not have latest releases use ClickHouse binaries:

    https://clickhouse.com/docs/en/install