ClickStack - Materialized Views

Once you have identified the visualizations that matter most, the next step is consolidation.

### Consolidate visualizations into shared views[​](#consolidate-visualizations-into-shared-views "Direct link to Consolidate visualizations into shared views")

All materialized views in ClickStack should group data by a time interval using functions such as [`toStartOfMinute`](/docs/sql-reference/functions/date-time-functions#toStartOfMinute). However, many visualizations also share additional grouping keys such as service name, span name, or status code. When multiple visualizations use the same grouping dimensions, they can often be served by a single materialized view.

For example (for traces):

- Average duration by service name over time - `SELECT avg(Duration), toStartOfMinute(Timestamp) as time, ServiceName FROM otel_traces GROUP BY ServiceName, time`
- Request count by service name over time - `SELECT count() count, toStartOfMinute(Timestamp) as time, ServiceName FROM otel_traces GROUP BY ServiceName, time`
- Average duration by status code over time - `SELECT avg(Duration), toStartOfMinute(Timestamp) as time, StatusCode FROM otel_traces GROUP BY StatusCode, time`
- Request count by status code over time - `SELECT count() count, toStartOfMinute(Timestamp) as time, StatusCode FROM otel_traces GROUP BY StatusCode, time`

Rather than creating separate materialized views for each query and chart, you can combine these into a single view aggregating by service name and status code. This single view can compute multiple metrics such as count, average duration, max duration, and also percentiles, which can then be reused across several visualizations. An example query, combining the above, is shown below:

```
SELECT avg(Duration), max(Duration), count(), quantiles(0.95,0.99)(Duration), toStartOfMinute(Timestamp) as time, ServiceName, StatusCode
FROM otel_traces
GROUP BY time, ServiceName, StatusCode
```

Consolidating views in this way reduces insert-time overhead, limits the total number of materialized views, reduces issues with part counts, and simplifies ongoing maintenance.

At this stage, **focus on the queries** that will be issued by the visualizations you want to accelerate. In the next section, you'll see an example showing how multiple aggregation queries can be combined into a single materialized view.

## Creating a materialized view[​](#creating-a-materialized-view "Direct link to Creating a materialized view")

Once you have identified a visualization, or set of visualizations, that you want to accelerate, the next step is to identify the underlying queries. In practice, this means inspecting the visualization configuration and reviewing the generated SQL, paying close attention to the aggregation metrics used, and the functions applied.

Note

In cases where a debug panel isn't available inside HyperDX for a component, users can inspect the browser console, where all queries are logged.

After consolidating the required queries, you should familiarize yourself with [**aggregate state functions**](/docs/sql-reference/data-types/aggregatefunction) in ClickHouse. Materialized views rely on these functions to shift computation from query time to insert time. Instead of storing final aggregated values, a materialized view computes and stores **intermediate aggregation states**, which are later merged and finalized at query time. These will typically be much smaller than the original table. These states have dedicated data types and must be explicitly represented in the schema of the target table.

For reference, ClickHouse provides a detailed overview and examples of aggregate state functions, and the table engine used to store them - `AggregatingMergeTree` - in the documentation:

- [Aggregate functions and states](/docs/sql-reference/aggregate-functions)
- [AggregatingMergeTree engine](/docs/engines/table-engines/mergetree-family/aggregatingmergetree)

You can see an example of how to use the AggregatingMergeTree and Aggregate functions in the video below:

It is **strongly recommended** to familiarize yourself with these concepts before moving forward.

### Example materialized view[​](#example-materialized-view "Direct link to Example materialized view")

Consider the following original query, which computes average duration, max duration, a count of events, and percentiles per minute, grouped by service name, and status code:

```
SELECT
    toStartOfMinute(Timestamp),
    ServiceName,
    StatusCode,
    count() AS count,
    avg(Duration),
    max(Duration),
    quantiles(0.95, 0.99)(Duration)
FROM otel_traces
GROUP BY
    time,
    ServiceName,
    StatusCode
```

To accelerate this query, create a target table `otel_traces_1m`, which stores the corresponding aggregation states:

```
CREATE TABLE otel_traces_1m
(
    `Timestamp` DateTime,
    `ServiceName` LowCardinality(String),
    `StatusCode` LowCardinality(String),
    `count` SimpleAggregateFunction(sum, UInt64),
    `avg__Duration` AggregateFunction(avg, UInt64),
    `max__Duration` SimpleAggregateFunction(max, Int64),
    `quantiles__Duration` AggregateFunction(quantiles(0.95, 0.99), Int64)
)
ENGINE = AggregatingMergeTree
ORDER BY (Timestamp, ServiceName, StatusCode);
```

The materialized view - `otel_traces_1m_mv` - definition then computes and writes these states as new data is inserted:

```
CREATE MATERIALIZED VIEW otel_traces_1m_mv TO otel_traces_1m
AS
SELECT
    toStartOfMinute(Timestamp) AS Timestamp,
    ServiceName,
    StatusCode,
    count() AS count,
    avgState(Duration) AS avg__Duration,
    maxSimpleState(Duration) AS max__Duration,
    quantilesState(0.95, 0.99)(Duration) AS quantiles__Duration
FROM otel_v2.otel_traces
GROUP BY
    Timestamp,
    ServiceName,
    StatusCode;
```

This materialized view consists of two parts:

1. The target table, which defines the schema and aggregate state types used to store intermediate results. The [AggregatingMergeTree](/docs/engines/table-engines/mergetree-family/aggregatingmergetree) engine is required to ensure these states are merged correctly in the background.
2. The materialized view query is executed automatically on insert. Compared to the original query, it uses state functions such as `avgState` and `quantilesState` instead of final aggregation functions.

The result is a compact table that stores per-minute aggregation states for each service name and status code. Its size grows predictably with time and cardinality, and after background merges, it represents the same result as running the original aggregation over the raw data. Querying this table is significantly cheaper than aggregating directly from the source traces table, enabling fast, consistent visualization performance at scale.

## Using materialized view usage in ClickStack[​](#materialized-view-usage-in-clickstack "Direct link to Using materialized view usage in ClickStack")

Once materialized views have been created in ClickHouse, they must be registered in ClickStack so they can be used automatically by visualizations, dashboards, and alerts.

### Registering a Materialized for usage[​](#registering-a-view "Direct link to Registering a Materialized for usage")

Materialized views should be registered against the **source** in HyperDX that corresponds to the **original source table** from which the view was derived.

Edit the source

#### Edit the source[​](#edit-the-source "Direct link to Edit the source")

Navigate to the relevant **source** in HyperDX and open the **Edit configuration** dialog. Scroll to the section for materialized views.

Add the materialized view

#### Add the materialized view[​](#add-the-materialized-view "Direct link to Add the materialized view")

Select **Add materialized view**, then choose the database and target table that back the materialized view.

Select metrics

#### Select metrics[​](#select-metrics "Direct link to Select metrics")

In most cases, the timestamp, dimension, and metric columns will be inferred automatically. If not, specify them manually.

For metrics, you must map: