Designing a schema for observability

A similar query for the unstructured logs requires the use of regular expressions via the `extractAllGroupsVertical` function.

```
SELECT
        path((groups[1])[2]) AS path,
        count() AS c
FROM
(
        SELECT extractAllGroupsVertical(Body, '(\\w+)\\s([^\\s]+)\\sHTTP/\\d\\.\\d') AS groups
        FROM otel_logs
        WHERE ((groups[1])[1]) = 'POST'
)
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productModelImages │ 10866 │
│ /site/productAdditives   │ 10866 │
└──────────────────────────┴───────┘

5 rows in set. Elapsed: 1.953 sec. Processed 10.37 million rows, 3.59 GB (5.31 million rows/s., 1.84 GB/s.)
```

The increased complexity and cost of queries for parsing unstructured logs (notice performance difference) is why we recommend users always use structured logs where possible.

Consider dictionaries

The above query could be optimized to exploit regular expression dictionaries. See [Using Dictionaries](#using-dictionaries) for more detail.

Both of these use cases can be satisfied using ClickHouse by moving the above query logic to insert time. We explore several approaches below, highlighting when each is appropriate.

OTel or ClickHouse for processing?

You may also perform processing using OTel Collector processors and operators as described [here](/docs/observability/integrating-opentelemetry#processing---filtering-transforming-and-enriching). In most cases, you will find ClickHouse is significantly more resource-efficient and faster than the collector's processors. The principal downside of performing all event processing in SQL is the coupling of your solution to ClickHouse. For example, you may wish to send processed logs to alternative destinations from the OTel collector e.g. S3.

### Materialized columns[​](#materialized-columns "Direct link to Materialized columns")

Materialized columns offer the simplest solution to extract structure from other columns. Values of such columns are always calculated at insert time and can't be specified in INSERT queries.

Overhead

Materialized columns incur additional storage overhead as the values are extracted to new columns on disk at insert time.

Materialized columns support any ClickHouse expression and can exploit any of the analytical functions for [processing strings](/docs/sql-reference/functions/string-functions) (including [regex and searching](/docs/sql-reference/functions/string-search-functions)) and [urls](/docs/sql-reference/functions/url-functions), performing [type conversions](/docs/sql-reference/functions/type-conversion-functions), [extracting values from JSON](/docs/sql-reference/functions/json-functions) or [mathematical operations](/docs/sql-reference/functions/math-functions).

We recommend materialized columns for basic processing. They're especially useful for extracting values from maps, promoting them to root columns, and performing type conversions. They're often most useful when used in very basic schemas or in conjunction with materialized views. Consider the following schema for logs from which the JSON has been extracted to the `LogAttributes` column by the collector:

```
CREATE TABLE otel_logs
(
        `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
        `TraceId` String CODEC(ZSTD(1)),
        `SpanId` String CODEC(ZSTD(1)),
        `TraceFlags` UInt32 CODEC(ZSTD(1)),
        `SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
        `SeverityNumber` Int32 CODEC(ZSTD(1)),
        `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
        `Body` String CODEC(ZSTD(1)),
        `ResourceSchemaUrl` String CODEC(ZSTD(1)),
        `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `ScopeSchemaUrl` String CODEC(ZSTD(1)),
        `ScopeName` String CODEC(ZSTD(1)),
        `ScopeVersion` String CODEC(ZSTD(1)),
        `ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `RequestPage` String MATERIALIZED path(LogAttributes['request_path']),
        `RequestType` LowCardinality(String) MATERIALIZED LogAttributes['request_type'],
        `RefererDomain` String MATERIALIZED domain(LogAttributes['referer'])
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SeverityText, toUnixTimestamp(Timestamp), TraceId)
```

The equivalent schema for extracting using JSON functions from a String `Body` can be found [here](https://pastila.nl/?005cbb97/513b174a7d6114bf17ecc657428cf829#gqoOOiomEjIiG6zlWhE+Sg==).

Our three materialized columns extract the request page, request type, and referrer's domain. These access the map keys and apply functions to their values. Our subsequent query is significantly faster:

```
SELECT RequestPage AS path, count() AS c
FROM otel_logs
WHERE RequestType = 'POST'
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productAdditives   │ 10866 │
│ /site/productModelImages │ 10866 │
└──────────────────────────┴───────┘

5 rows in set. Elapsed: 0.173 sec. Processed 10.37 million rows, 418.03 MB (60.07 million rows/s., 2.42 GB/s.)
Peak memory usage: 3.16 MiB.
```

Note

Materialized columns will, by default, not be returned in a `SELECT *`. This is to preserve the invariant that the result of a `SELECT *` can always be inserted back into the table using INSERT. This behavior can be disabled by setting `asterisk_include_materialized_columns=1` and can be enabled in Grafana (see `Additional Settings -> Custom Settings` in data source configuration).

## Materialized views[​](#materialized-views "Direct link to Materialized views")

[Materialized views](/docs/materialized-views) provide a more powerful means of applying SQL filtering and transformations to logs and traces.

Materialized Views allow you to shift the cost of computation from query time to insert time. A ClickHouse materialized view is just a trigger that runs a query on blocks of data as they're inserted into a table. The results of this query are inserted into a second "target" table.

Real-time updates

Materialized views in ClickHouse are updated in real time as data flows into the table they're based on, functioning more like continually updating indexes. In contrast, in other databases materialized views are typically static snapshots of a query that must be refreshed (similar to ClickHouse Refreshable Materialized Views).

The query associated with the materialized view can theoretically be any query, including an aggregation although [limitations exist with Joins](https://clickhouse.com/blog/using-materialized-views-in-clickhouse#materialized-views-and-joins). For the transformations and filtering workloads required for logs and traces, you can consider any `SELECT` statement to be possible.

You should remember the query is just a trigger executing over the rows being inserted into a table (the source table), with the results sent to a new table (the target table).

In order to ensure we don't persist the data twice (in the source and target tables) we can change the table of the source table to be a [Null table engine](/docs/engines/table-engines/special/null), preserving the original schema. Our OTel collectors will continue to send data to this table. For example, for logs, the `otel_logs` table becomes: