Clickhouse存储延迟分位数时序数据报错求助及插入示例需求

错误原因 #

你的ClickHouse 23.3.1版本中不存在quantilesApprox这个聚合函数，这是报错的直接原因。另外原建表语句的ORDER BY子句使用了未定义的列time，需要修正为timestamp。

方案一：使用tdigest聚合列（推荐） #

tdigest可以存储单个聚合状态，支持后续计算任意分位数，灵活性更高，同时节省存储。

正确建表语句 #

CREATE TABLE IF NOT EXISTS q
(
  timestamp DateTime,
  query LowCardinality(String),
  latency_tdigest AggregateFunction(tdigest, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (toDate(timestamp), query, toStartOfMinute(timestamp))

插入语句示例 #

从原始表聚合插入

假设你有原始日志表raw_latency（结构：timestamp DateTime, query String, latency UInt64），可通过聚合后插入：

INSERT INTO q
SELECT
  timestamp,
  query,
  tdigestState(latency) AS latency_tdigest
FROM raw_latency
GROUP BY timestamp, query

单条/批量插入原始数据

直接插入单条或批量原始延迟数据时，需用tdigestState包装原始值：

INSERT INTO q (timestamp, query, latency_tdigest)
VALUES
  (now(), 'SELECT * FROM users', tdigestState(123)),
  (now(), 'SELECT * FROM orders', tdigestState(456))

查询分位数 #

从聚合表中计算指定分位数：

SELECT
  toDate(timestamp) AS date,
  query,
  toStartOfMinute(timestamp) AS minute,
  quantile(0.5)(latency_tdigest) AS p50_latency,
  quantile(0.75)(latency_tdigest) AS p75_latency,
  quantile(0.9)(latency_tdigest) AS p90_latency,
  quantile(0.99)(latency_tdigest) AS p99_latency
FROM q
GROUP BY date, query, minute

方案二：为每个分位数单独创建聚合列 #

如果需要预先绑定特定分位数，可以为每个分位数创建单独的quantileApprox聚合列。

正确建表语句 #

CREATE TABLE IF NOT EXISTS q
(
  timestamp DateTime,
  query LowCardinality(String),
  latency_p50 AggregateFunction(quantileApprox(0.5), UInt64),
  latency_p75 AggregateFunction(quantileApprox(0.75), UInt64),
  latency_p90 AggregateFunction(quantileApprox(0.9), UInt64),
  latency_p99 AggregateFunction(quantileApprox(0.99), UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (toDate(timestamp), query, toStartOfMinute(timestamp))

插入语句示例 #

从原始表聚合插入

INSERT INTO q
SELECT
  timestamp,
  query,
  quantileApproxState(0.5)(latency) AS latency_p50,
  quantileApproxState(0.75)(latency) AS latency_p75,
  quantileApproxState(0.9)(latency) AS latency_p90,
  quantileApproxState(0.99)(latency) AS latency_p99
FROM raw_latency
GROUP BY timestamp, query

单条/批量插入原始数据

INSERT INTO q (timestamp, query, latency_p50, latency_p75, latency_p90, latency_p99)
VALUES
  (now(), 'SELECT * FROM users', quantileApproxState(0.5)(123), quantileApproxState(0.75)(123), quantileApproxState(0.9)(123), quantileApproxState(0.99)(123)),
  (now(), 'SELECT * FROM orders', quantileApproxState(0.5)(456), quantileApproxState(0.75)(456), quantileApproxState(0.9)(456), quantileApproxState(0.99)(456))

查询分位数 #

SELECT
  toDate(timestamp) AS date,
  query,
  toStartOfMinute(timestamp) AS minute,
  quantileApproxMerge(latency_p50) AS p50_latency,
  quantileApproxMerge(latency_p75) AS p75_latency,
  quantileApproxMerge(latency_p90) AS p90_latency,
  quantileApproxMerge(latency_p99) AS p99_latency
FROM q
GROUP BY date, query, minute

内容的提问来源于stack exchange，提问作者mq7