SQL-based visualizations

Creating visualizations using SQL queries in ClickStack

ClickStack 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 the SQL tab.

From there:

1. Select a ClickHouse connection to run the query against.
2. Optionally select a Source — this enables dashboard-level filters to be applied to your chart via the $__filters macro.
3. Write your SQL query in the editor, using query parameters and macros to integrate with the dashboard time range and filters.
4. Click the play button to preview results, then Save.

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:

Line and Stacked Bar 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)
{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 are only available for Line and Stacked-bar visualizations.

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.

How query results are plotted

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

Line and Stacked Bar 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

Required system table access

You will need to specify otel_v2.otel_logs or otel_v2.otel_traces if running the following examples on play-clickstack.clickhouse.com.

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

Line 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

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.
• SQL-based visualizations must be exactly one SQL 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, for accurate filtering.