Designing a schema for observability

```
CREATE TABLE bytes_per_hour
(
  `Hour` DateTime,
  `TotalBytes` UInt64
)
ENGINE = SummingMergeTree
ORDER BY Hour
```

To demonstrate our materialized view, assume our `bytes_per_hour` table is empty and yet to receive any data. Our materialized view performs the above `SELECT` on data inserted into `otel_logs` (this will be performed over blocks of a configured size), with the results sent to `bytes_per_hour`. The syntax is shown below:

```
CREATE MATERIALIZED VIEW bytes_per_hour_mv TO bytes_per_hour AS
SELECT toStartOfHour(Timestamp) AS Hour,
       sum(toUInt64OrDefault(LogAttributes['size'])) AS TotalBytes
FROM otel_logs
GROUP BY Hour
```

The `TO` clause here is key, denoting where results will be sent to i.e. `bytes_per_hour`.

If we restart our OTel Collector and resend the logs, the `bytes_per_hour` table will be incrementally populated with the above query result. On completion, we can confirm the size of our `bytes_per_hour` - we should have 1 row per hour:

```
SELECT count()
FROM bytes_per_hour
FINAL

┌─count()─┐
│     113 │
└─────────┘

1 row in set. Elapsed: 0.039 sec.
```

We've effectively reduced the number of rows here from 10m (in `otel_logs`) to 113 by storing the result of our query. The key here is that if new logs are inserted into the `otel_logs` table, new values will be sent to `bytes_per_hour` for their respective hour, where they will be automatically merged asynchronously in the background - by keeping only one row per hour `bytes_per_hour` will thus always be both small and up-to-date.

Since the merging of rows is asynchronous, there may be more than one row per hour when a user queries. To ensure any outstanding rows are merged at query time, we have two options:

- Use the [`FINAL` modifier](/docs/sql-reference/statements/select/from#final-modifier) on the table name (which we did for the count query above).
- Aggregate by the ordering key used in our final table i.e. Timestamp and sum the metrics.

Typically, the second option is more efficient and flexible (the table can be used for other things), but the first can be simpler for some queries. We show both below:

```
SELECT
        Hour,
        sum(TotalBytes) AS TotalBytes
FROM bytes_per_hour
GROUP BY Hour
ORDER BY Hour DESC
LIMIT 5

┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘

5 rows in set. Elapsed: 0.008 sec.

SELECT
        Hour,
        TotalBytes
FROM bytes_per_hour
FINAL
ORDER BY Hour DESC
LIMIT 5

┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘

5 rows in set. Elapsed: 0.005 sec.
```

This has sped up our query from 0.6s to 0.008s - over 75 times!

Note

These savings can be even greater on larger datasets with more complex queries. See [here](https://github.com/ClickHouse/clickpy) for examples.

#### A more complex example[​](#a-more-complex-example "Direct link to A more complex example")

The above example aggregates a simple count per hour using the [SummingMergeTree](/docs/engines/table-engines/mergetree-family/summingmergetree). Statistics beyond simple sums require a different target table engine: the [AggregatingMergeTree](/docs/engines/table-engines/mergetree-family/aggregatingmergetree).

Suppose we wish to compute the number of unique IP addresses (or unique users) per day. The query for this:

```
SELECT toStartOfHour(Timestamp) AS Hour, uniq(LogAttributes['remote_addr']) AS UniqueUsers
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC

┌────────────────Hour─┬─UniqueUsers─┐
│ 2019-01-26 16:00:00 │     4763    │
│ 2019-01-22 00:00:00 │     536     │
└─────────────────────┴─────────────┘

113 rows in set. Elapsed: 0.667 sec. Processed 10.37 million rows, 4.73 GB (15.53 million rows/s., 7.09 GB/s.)
```

In order to persist a cardinality count for incremental update the AggregatingMergeTree is required.

```
CREATE TABLE unique_visitors_per_hour
(
  `Hour` DateTime,
  `UniqueUsers` AggregateFunction(uniq, IPv4)
)
ENGINE = AggregatingMergeTree
ORDER BY Hour
```

To ensure ClickHouse knows that aggregate states will be stored, we define the `UniqueUsers` column as the type [`AggregateFunction`](/docs/sql-reference/data-types/aggregatefunction), specifying the function source of the partial states (uniq) and the type of the source column (IPv4). Like the SummingMergeTree, rows with the same `ORDER BY` key value will be merged (Hour in the above example).

The associated materialized view uses the earlier query:

```
CREATE MATERIALIZED VIEW unique_visitors_per_hour_mv TO unique_visitors_per_hour AS
SELECT toStartOfHour(Timestamp) AS Hour,
        uniqState(LogAttributes['remote_addr']::IPv4) AS UniqueUsers
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC
```

Note how we append the suffix `State` to the end of our aggregate functions. This ensures the aggregate state of the function is returned instead of the final result. This will contain additional information to allow this partial state to merge with other states.

Once the data has been reloaded, through a Collector restart, we can confirm 113 rows are available in the `unique_visitors_per_hour` table.

```
SELECT count()
FROM unique_visitors_per_hour
FINAL
┌─count()─┐
│   113   │
└─────────┘

1 row in set. Elapsed: 0.009 sec.
```

Our final query needs to utilize the Merge suffix for our functions (as the columns store partial aggregation states):

```
SELECT Hour, uniqMerge(UniqueUsers) AS UniqueUsers
FROM unique_visitors_per_hour
GROUP BY Hour
ORDER BY Hour DESC

┌────────────────Hour─┬─UniqueUsers─┐
│ 2019-01-26 16:00:00 │      4763   │
│ 2019-01-22 00:00:00 │      536    │
└─────────────────────┴─────────────┘

113 rows in set. Elapsed: 0.027 sec.
```

Note we use a `GROUP BY` here instead of using `FINAL`.

### Using Materialized views (incremental) for fast lookups[​](#using-materialized-views-incremental--for-fast-lookups "Direct link to Using Materialized views (incremental) for fast lookups")

You should consider their access patterns when choosing the ClickHouse ordering key with the columns that are frequently used in filter and aggregation clauses. This can be restrictive in Observability use cases, where users have more diverse access patterns that can't be encapsulated in a single set of columns. This is best illustrated in an example built into the default OTel schemas. Consider the default schema for the traces: