Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Working with the Map type in ClickHouse

View as MarkdownEdit this pageReport an issueSet up docs MCP
All quickstarts
ObservabilityOSS

Prerequisites

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.

Download the sample 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:

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"...

Create the table and load the data

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.

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:

INSERT INTO otel_traces
SELECT * FROM file('~/data/data-otel-traces.csv', CSVWithNames);

Confirm the data was loaded:

SELECT count() FROM otel_traces;

You should see 6,120 rows.

Query the data

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):

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:

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:

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:

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:

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:

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:

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';

Aggregate across maps with the -Map combinator

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):

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:

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:

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:

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.

Optimise for frequently queried keys

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:

ALTER TABLE otel_traces
    ADD COLUMN HostName String
    MATERIALIZED ResourceAttributes['host.name'];

For existing data, backfill the column:

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:

Or go deeper with the reference documentation:

ClickHouse Academy — Master ClickHouse with expert-designed training for every skill level
Check out the ClickHouse academy for on-demand and live training
All quickstarts
Navigation