Artificial Intelligence1 min read

ClickHouse forecasting and anomaly detection with time series foundation models

By · Published by Everything Blog

In short

ClickHouse showcased this capability by linking with the TimesFM 3.0 foundation model to run inference straight from standard SQL aggregations. The environment utilizes Python user-defined functions communicating over HTTP with an external inference server running on standard CPU hardware. Demonstrated using daily retail sales from the M5 dataset, the setup produces median estimates along with prediction intervals ranging from the 10th to the 90th percentiles.

Key points

  • Pretrained foundation models allow ClickHouse users to perform forecasting on time-series…: Pretrained foundation models allow ClickHouse users to perform forecasting on time-series data without training separate models for each metric.
  • Dedicated SQL functions such as aiForecast and aiDetectAnomalies enable zero-shot analyti…: Dedicated SQL functions such as aiForecast and aiDetectAnomalies enable zero-shot analytical inference directly inside standard database queries.
  • Current TimesFM 3.0 weight restrictions prohibit commercial and production use, keeping t…: Current TimesFM 3.0 weight restrictions prohibit commercial and production use, keeping this architecture scoped strictly to local evaluation.

Sources checked by our analysis pipeline: 1

ClickHouse is a common home for time-series data, from logs and infrastructure metrics to sales and customer activity. Our own internal logging platform, LogHouse, stores more than a quadrillion time series rows. Forecasting future values and detecting unusual observations are common tasks for this data, but training and maintaining a separate classical time series model, like ARIMA, for every service, customer, or product can be difficult as the number of series grows.

Time-series foundation models (TSFMs) promise to remove that per-series training requirement: pretrained on many series, they can produce zero-shot forecasts from a new series' history, which can also be used to flag observations that deviate from expectations. This makes them a natural fit for ClickHouse's real-time analytics and data warehousing workloads, where SQL already handles the aggregation, time bucketing, and filtering needed to construct each series.

In this post, we connect TimesFM 3.0 to ClickHouse through executable user-defined functions. As an illustration, we use recorded retail sales from the M5 dataset to forecast daily demand and identify unusual historical observations.

Setup

The companion clickhouse-timeseries-ai

repository contains a TimesFM…

Artificial Intelligence