* **clickhouse-local** installed on your machine. See the [clickhouse-local setup guide](/docs/concepts/features/tools-and-utilities/clickhouse-local) to get started.
What you'll build
In OpenTelemetry, every trace span carries a set of **resource attributes** â key-value metadata describing the entity that produced the telemetry (service name, host, cloud region, Kubernetes pod, etc.). The set of keys varies between services and environments, making this a natural fit for ClickHouse's `Map` type: the keys are dynamic and application-specific, but any given row typically has only a handful of them.
In this quickstart you'll use `clickhouse-local` to load real OTel trace data from a CSV file into a table with `Map(LowCardinality(String), String)` columns, and learn how to query, filter, aggregate, and optimise map data.
The dataset contains 6,120 OTel trace spans exported from a demo microservices application. Each row includes a `ResourceAttributes` and `SpanAttributes` column containing dynamic key-value pairs as JSON maps.
Save the file to a directory you can easily reference, for example `~/data/data-otel-traces.csv`.
Download data-otel-traces.csv (2.9 MB)
Here's what a single row looks like:
```response theme={null}
Timestamp: 2025-12-26 00:00:45.759467000
TraceId: 0da128e6e3c01bc38b6b43a33e5fa522
SpanId: 3774f759424e4006
ParentSpanId: 2fdd1e5b66605098
SpanName: orders receive
SpanKind: SPAN_KIND_CONSUMER
ServiceName: accountingservice
Duration: 5361
StatusCode: STATUS_CODE_UNSET
ResourceAttributes: {"host.name":"f19476836e47","os.type":"linux","process.pid":"1","process.command_args":"[\"./accountingservice\"]","process.executable.path":"...
SpanAttributes: {"network.transport":"tcp","messaging.destination.name":"orders","messaging.kafka.message.offset":"232260","messaging.message.body.size":"216"...
```
Launch `clickhouse-local` and create the following table with a schema matching the CSV.
The key column is `ResourceAttributes Map(LowCardinality(String), String)` - using `LowCardinality` on the key type because OTel attribute keys are drawn from a relatively small, repeating set.
```sql highlight={12} theme={null}
CREATE TABLE otel_traces
(
Timestamp DateTime64(9),
TraceId String,
SpanId String,
ParentSpanId String,
SpanName LowCardinality(String),
SpanKind LowCardinality(String),
ServiceName LowCardinality(String),
Duration UInt64,
StatusCode LowCardinality(String),
ResourceAttributes Map(LowCardinality(String), String),
SpanAttributes Map(LowCardinality(String), String)
)
ENGINE = MergeTree()
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp));
```
Now load the CSV using the `file` table engine. Adjust the path to where you saved the file:
```sql theme={null}
INSERT INTO otel_traces
SELECT * FROM file('~/data/data-otel-traces.csv', CSVWithNames);
```
Confirm the data was loaded:
```sql theme={null}
SELECT count() FROM otel_traces;
```
You should see 6,120 rows.
**Access a specific key** â use bracket syntax to pull a value out of the map. If the key doesn't exist on a given row, you get the default for the value type (empty string for `String`):
```sql theme={null}
SELECT
ServiceName,
SpanName,
ResourceAttributes['host.name'] AS host,
ResourceAttributes['k8s.pod.name'] AS pod,
ResourceAttributes['deployment.environment'] AS env
FROM otel_traces
LIMIT 10;
```
**Filter by a map value** â find all spans from a specific service name:
```sql theme={null}
SELECT
Timestamp,
SpanName,
Duration / 1e6 AS duration_ms
FROM otel_traces
WHERE ResourceAttributes['service.name'] = 'cartservice'
ORDER BY Timestamp
LIMIT 10;
```
**Check whether a key exists** â not every span has Kubernetes metadata. Use `mapContains` to find which ones do:
```sql theme={null}
SELECT
ServiceName,
SpanName,
mapContains(ResourceAttributes, 'k8s.node.name') AS has_node_info
FROM otel_traces
LIMIT 10;
```
**Inspect all keys present across the dataset** â useful for understanding what instrumentation is producing:
```sql theme={null}
SELECT DISTINCT arrayJoin(mapKeys(ResourceAttributes)) AS key
FROM otel_traces
ORDER BY key;
```
**Explode a map into rows with ARRAY JOIN** â turn each key-value pair into its own row, handy for building attribute inventories or feeding dashboards:
```sql theme={null}
SELECT
ServiceName,
key,
value
FROM otel_traces
ARRAY JOIN
mapKeys(ResourceAttributes) AS key,
mapValues(ResourceAttributes) AS value
WHERE ServiceName = 'cartservice'
LIMIT 20;
```
**Filter maps with mapFilter** â extract only the Kubernetes-related attributes from each span:
```sql theme={null}
SELECT
ServiceName,
mapFilter((k, v) -> k LIKE 'k8s.%', ResourceAttributes) AS k8s_attrs
FROM otel_traces
WHERE mapContains(ResourceAttributes, 'k8s.pod.name')
LIMIT 10;
```
**Find error spans and their resource context** â combine regular column filters with map access:
```sql theme={null}
SELECT
Timestamp,
ServiceName,
SpanName,
ResourceAttributes['host.name'] AS host,
ResourceAttributes['k8s.pod.name'] AS pod,
SpanAttributes['error.type'] AS error_type,
SpanAttributes['error.message'] AS error_message
FROM otel_traces
WHERE StatusCode = 'STATUS_CODE_ERROR';
```
ClickHouse's `-Map` aggregate combinator lets you apply any aggregate function to a `Map` column and have it operate on each key independently. The result is itself a `Map` â one entry per key, with the aggregated value. This is especially powerful for OTel metrics, where counters or gauges are stored as map values.
To demonstrate, create a small metrics table where each row records HTTP status code counts as a `Map(String, UInt64)`:
```sql theme={null}
CREATE TABLE otel_http_status_counts
(
Timestamp DateTime,
ServiceName LowCardinality(String),
StatusCounts Map(String, UInt64)
)
ENGINE = MergeTree()
ORDER BY (ServiceName, Timestamp);
INSERT INTO otel_http_status_counts VALUES
('2025-12-26 10:00:00', 'cart-service', {'2xx': 150, '4xx': 12, '5xx': 3}),
('2025-12-26 10:01:00', 'cart-service', {'2xx': 200, '4xx': 8, '5xx': 1}),
('2025-12-26 10:00:00', 'inventory-service', {'2xx': 90, '4xx': 5}),
('2025-12-26 10:01:00', 'inventory-service', {'2xx': 110, '4xx': 3, '5xx': 2}),
('2025-12-26 10:00:00', 'payment-service', {'2xx': 50, '5xx': 10}),
('2025-12-26 10:01:00', 'payment-service', {'2xx': 45, '4xx': 2, '5xx': 15});
```
Now use `sumMap` to total the counts per status code for each service:
```sql theme={null}
SELECT
ServiceName,
sumMap(StatusCounts) AS total_by_status
FROM otel_http_status_counts
GROUP BY ServiceName;
```
The `-Map` suffix works with any aggregate function, so you can use `minMap`, `maxMap`, or `avgMap` just as easily:
```sql theme={null}
SELECT
ServiceName,
avgMap(StatusCounts) AS avg_by_status,
maxMap(StatusCounts) AS peak_by_status
FROM otel_http_status_counts
GROUP BY ServiceName;
```
You can also combine it with other combinators. For example, `sumMapIf` lets you conditionally aggregate â here, only summing the minute windows where the service already had errors:
```sql theme={null}
SELECT
ServiceName,
sumMapIf(StatusCounts, StatusCounts['5xx'] > 0) AS totals_in_error_windows
FROM otel_http_status_counts
GROUP BY ServiceName;
```
**Why this matters for OTel:** When your OTel Collector writes per-minute status code breakdowns into ClickHouse, `sumMap` lets you roll them up to hourly or daily totals in a single query â no `ARRAY JOIN`, no unpivoting, no knowing the full set of keys in advance. Any key that appears in any row is automatically included in the result.
If you find yourself constantly filtering on the same map key â `host.name` is a common one â you can extract it into a materialized column. This avoids the linear scan through the map on every query:
```sql theme={null}
ALTER TABLE otel_traces
ADD COLUMN HostName String
MATERIALIZED ResourceAttributes['host.name'];
```
For existing data, backfill the column:
```sql theme={null}
ALTER TABLE otel_traces MATERIALIZE COLUMN HostName;
```
Now `WHERE HostName = 'prod-cart-01'` reads a single, dedicated column instead of the entire map. This is the recommended pattern in the OTel ClickHouse schema for any attribute you query frequently.
Key takeaways
* **`Map(LowCardinality(String), String)`** is the idiomatic type for OTel attributes â flexible enough to handle varying key sets, and `LowCardinality` keeps the key storage efficient.
* **Bracket syntax** (`map['key']`) is the most common way to access values, but remember it scans linearly â fine for maps with tens of keys, not ideal for hundreds.
* **Materialized columns** are the escape hatch: when a map key becomes a hot filter target, promote it to a real column for indexed, columnar access.
* **`mapContains`, `mapKeys`, `mapValues`, `mapFilter`** and `ARRAY JOIN` give you a rich toolkit for exploring and transforming map data without leaving SQL.
* **The `-Map` aggregate combinator** (`sumMap`, `avgMap`, `maxMap`, etc.) aggregates each key independently across rows â ideal for rolling up OTel metric counters without needing to know the key set in advance. It composes with other combinators too (e.g. `sumMapIf`).
Next steps
Check out the following quickstarts next:
* [Create your first MergeTree table](/docs/get-started/quickstarts/create-your-first-mergetree-table)
* [Create your first materialized view](/docs/get-started/quickstarts/create-your-first-materialized-view)
* [Common getting started issues](/docs/get-started/quickstarts/home)
Or go deeper with the reference documentation:
* [Map type reference](/docs/reference/data-types/map)
* [ClickHouse OTel exporter](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/exporter/clickhouseexporter)
* [Aggregate function combinators](/docs/reference/functions/aggregate-functions/combinators)
