SQL-based visualizations

ClickHouse Observability supports visualizations based on raw SQL queries. This gives you full control over the query logic while still integrating with dashboard-level time ranges, filters, and chart rendering. SQL-based visualizations are useful when you need to go beyond the built-in Chart Explorer — for example, to join tables or build complex aggregations that are not supported by the chart builder.

Creating a SQL-based visualization

To create a SQL-based visualization, open a dashboard tile editor and select SQL. From there:

Select the Connection to run the query against.
Optionally select a Source — this enables dashboard-level filters to be applied to your chart via the $__filters macro.
Write your SQL query in the editor, using query parameters and macros to integrate with the dashboard time range, filters, and variables.
Click Run to preview the results, then Save to save the visualization.

Query parameters

Query parameters allow your SQL to reference the dashboard’s current time range and granularity. They use the ClickHouse parameterized query syntax: {paramName:Type}.

Available parameters

The parameters available depend on the chart type: Time Series charts (including stacked bars):

Parameter	Type	Description
`{startDateMilliseconds:Int64}`	Int64	Start of the dashboard date range (milliseconds since epoch)
`{endDateMilliseconds:Int64}`	Int64	End of the dashboard date range (milliseconds since epoch)
`{intervalSeconds:Int64}`	Int64	Time bucket size in seconds (based on granularity)
`{intervalMilliseconds:Int64}`	Int64	Time bucket size in milliseconds (based on granularity)

Table, Pie, and Number charts:

Parameter	Type	Description
`{startDateMilliseconds:Int64}`	Int64	Start of the dashboard date range (milliseconds since epoch)
`{endDateMilliseconds:Int64}`	Int64	End of the dashboard date range (milliseconds since epoch)

Macros

Macros are shortcuts that expand into common ClickHouse SQL expressions. They are prefixed with $__ and are replaced before the query is sent to ClickHouse.

Time boundary macros

These macros return a ClickHouse expression representing the dashboard’s start or end time. They take no arguments.

Macro	Expands to	Column type
`$__fromTime`	`toDateTime(fromUnixTimestamp64Milli({startDateMilliseconds:Int64}))`	DateTime
`$__toTime`	`toDateTime(fromUnixTimestamp64Milli({endDateMilliseconds:Int64}))`	DateTime
`$__fromTime_ms`	`fromUnixTimestamp64Milli({startDateMilliseconds:Int64})`	DateTime64
`$__toTime_ms`	`fromUnixTimestamp64Milli({endDateMilliseconds:Int64})`	DateTime64
`$__interval_s`	`{intervalSeconds:Int64}`	Int64

Time filter macros

These macros generate a WHERE clause fragment that filters a column to the dashboard time range.

Macro	Description
`$__timeFilter(column)`	Filters a `DateTime` column to the dashboard range
`$__timeFilter_ms(column)`	Filters a `DateTime64` (millisecond) column to the dashboard range
`$__dateFilter(column)`	Filters a `Date` column to the dashboard range
`$__dateTimeFilter(dateCol, timeCol)`	Filters using separate `Date` and `DateTime` columns
`$__dt(dateCol, timeCol)`	Alias for `$__dateTimeFilter`

Example expansion of $__timeFilter(TimestampTime):

TimestampTime >= toDateTime(fromUnixTimestamp64Milli({startDateMilliseconds:Int64}))
AND TimestampTime <= toDateTime(fromUnixTimestamp64Milli({endDateMilliseconds:Int64}))

Time interval macros

These macros bucket a timestamp column into intervals matching the dashboard granularity. They are typically used in SELECT and GROUP BY clauses for time series charts. These macros are only available for Time Series visualizations, including stacked bars.

Macro	Description
`$__timeInterval(column)`	Buckets a `DateTime` column into intervals of `intervalSeconds`
`$__timeInterval_ms(column)`	Buckets a `DateTime64` column into intervals of `intervalMilliseconds`

Example expansion of $__timeInterval(TimestampTime):

toStartOfInterval(toDateTime(TimestampTime), INTERVAL {intervalSeconds:Int64} second)

Dashboard filter macro

Macro	Description
`$__filters`	Replaced with the dashboard-level filter conditions (requires a Source to be selected)

When a Source is selected on the chart and dashboard filters are active, $__filters expands to the corresponding SQL WHERE conditions. When no source is selected or no filters are applied, it expands to (1=1), so it is always safe to include in a WHERE clause. Only filters with Broadcast filter condition enabled are applied by $__filters. Filters exposed as variables are referenced explicitly, as described below.

Dashboard variable macros

When a dashboard filter is available as a variable, its selected values can be referenced anywhere in the query. These macros are only available on tiles belonging to a dashboard that declares at least one variable.

Macro	Description
`$__filter($<variable>)`	Expands to `toString(<filter expression>) IN ($variable)` when values are selected for `variable`, and `1=1` otherwise.
`$__filter(<expression>, $<variable>)`	Expands to `<expression> IN ($variable)` when values are selected for `variable`, and `1=1` otherwise.
`$__conditionalAll(<condition>, $<variable>)`	Expands to `<condition>` when the variable has a selection, `1=1` otherwise.

A variable’s values can also be interpolated directly as $name or ${name}, with an optional format — ${name:sqlstring} (the default), ${name:csv}, or ${name:regex}. In the default format a direct reference renders as NULL before anything is selected, so prefer the macros above wherever a predicate is expected.

How query results are plotted

The UI automatically maps result columns to chart elements based on column types. The mapping rules differ by chart type.

Time Series charts

Role	Column type	Description
Timestamp	First `Date` or `DateTime` column	Used as the x-axis.
Series Value	All numeric columns	Each numeric column is plotted as a separate series. These are typically aggregate values.
Group Names	`String`, `Map`, or `Array` columns	Optional. Rows with different group values are plotted as separate series.

Pie chart

Role	Column type	Description
Slice Value	First numeric column	Determines each slice’s size.
Slice Label	`String`, `Map`, or `Array` columns	Optional. Each unique value becomes a slice label.

Number chart

Role	Column type	Description
Number	First numeric column	The value from the first row of the first numeric column is displayed.

Table chart

All result columns are displayed directly as table columns.

Examples

Example table namesThe examples use otel_logs and otel_traces. Adjust the table and column names to match your data. When running the examples on play-clickstack.clickhouse.com, use otel_v2.otel_logs or otel_v2.otel_traces.

Time Series chart — log count over time by service

This query counts log events per service, bucketed into time intervals matching the dashboard granularity.

SELECT
  toStartOfInterval(TimestampTime, INTERVAL {intervalSeconds:Int64} second) AS ts,
  ServiceName,
  count() AS count
FROM otel_logs
WHERE TimestampTime >= fromUnixTimestamp64Milli({startDateMilliseconds:Int64})
  AND TimestampTime < fromUnixTimestamp64Milli({endDateMilliseconds:Int64})
  AND $__filters
GROUP BY ServiceName, ts
ORDER BY ts ASC

ts (DateTime) is used as the x-axis timestamp.
count (numeric) is plotted as the series value.
ServiceName (string) creates a separate line per service.

Time Series chart — using macros

The same query written using macros for brevity:

SELECT
  $__timeInterval(TimestampTime) AS ts,
  ServiceName,
  count() AS count
FROM otel_logs
WHERE $__timeFilter(TimestampTime)
  AND $__filters
GROUP BY ServiceName, ts
ORDER BY ts ASC

Time Series chart — using dashboard variables

Given a dashboard with a service variable on ServiceName and a severity variable on SeverityText, this query scopes the chart to the current service selection and excludes the selected severities. Both macros expand to 1=1 while their variable has no selection, so the chart renders unfiltered until the viewer picks a value.

SELECT
  $__timeInterval(TimestampTime) AS ts,
  count() AS count
FROM otel_logs
WHERE $__timeFilter(TimestampTime)
  AND $__filter(ServiceName, $service)
  AND $__conditionalAll(SeverityText NOT IN ($severity), $severity)
GROUP BY ts
ORDER BY ts ASC

Stacked bar chart — error count by severity

SELECT
  $__timeInterval(TimestampTime) AS ts,
  lower(SeverityText),
  count() AS count
FROM otel_logs
WHERE $__timeFilter(TimestampTime)
  AND lower(SeverityText) IN ('error', 'warn')
  AND $__filters
GROUP BY SeverityText, ts
ORDER BY ts ASC

Table chart — top 10 slowest endpoints

SELECT
  SpanName AS endpoint,
  avg(Duration) / 1000 AS avg_duration_ms,
  count() AS request_count
FROM otel_traces
WHERE $__timeFilter(Timestamp)
  AND $__filters
GROUP BY SpanName
ORDER BY avg_duration_ms DESC
LIMIT 10

Pie chart — request distribution by service

SELECT
  ServiceName,
  count() AS request_count
FROM otel_traces
WHERE $__timeFilter(Timestamp)
  AND $__filters
GROUP BY ServiceName

request_count (numeric) determines each slice’s size.
ServiceName (string) labels each slice.

Number chart — total error count

SELECT
  count() AS total_errors
FROM otel_logs
WHERE $__timeFilter(TimestampTime)
  AND SeverityText = 'error'
  AND $__filters

The single numeric value total_errors from the first row is displayed.

Notes

SQL-based visualizations execute with readonly mode enabled — only SELECT queries are permitted.
Each SQL-based visualization supports a single query; multiple queries are not supported.
The SQL editor provides autocomplete suggestions for both query parameters and macros.
A source must be selected to apply dashboard filters to SQL-based visualizations. The source should match the table being queried to ensure accurate filtering.
Dashboard variables need no source — the values come from the dashboard’s filter dropdowns, not from the tile’s own source.
Alerts on a SQL tile that references a dashboard variable evaluate with every variable in its empty state, not the values selected on the dashboard.

Introduction

Features

Administration

Guides

Integration partners

Self-managed

Creating a SQL-based visualization

Query parameters

Available parameters

Macros

Time boundary macros

Time filter macros

Time interval macros

Dashboard filter macro

Dashboard variable macros

How query results are plotted

Time Series charts

Pie chart

Number chart

Table chart

Examples

Time Series chart — log count over time by service

Time Series chart — using macros

Time Series chart — using dashboard variables

Stacked bar chart — error count by severity

Table chart — top 10 slowest endpoints

Pie chart — request distribution by service

Number chart — total error count

Notes

​Creating a SQL-based visualization

​Query parameters

​Available parameters

​Macros

​Time boundary macros

​Time filter macros

​Time interval macros

​Dashboard filter macro

​Dashboard variable macros

​How query results are plotted

​Time Series charts

​Pie chart

​Number chart

​Table chart

​Examples

​Time Series chart — log count over time by service

​Time Series chart — using macros

​Time Series chart — using dashboard variables

​Stacked bar chart — error count by severity

​Table chart — top 10 slowest endpoints

​Pie chart — request distribution by service

​Number chart — total error count

​Notes

Creating a SQL-based visualization

Query parameters

Available parameters

Macros

Time boundary macros

Time filter macros

Time interval macros

Dashboard filter macro

Dashboard variable macros

How query results are plotted

Time Series charts

Pie chart

Number chart

Table chart

Examples

Time Series chart — log count over time by service

Time Series chart — using macros

Time Series chart — using dashboard variables

Stacked bar chart — error count by severity

Table chart — top 10 slowest endpoints

Pie chart — request distribution by service

Number chart — total error count

Notes