SQL-based visualizations

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/observability/clickstack/dashboards/sql-visualizations.md)

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[​](#creating-a-raw-sql-chart "Direct link to 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 "Direct link to Query parameters")

[Query parameters](/docs/sql-reference/syntax#defining-and-using-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[​](#available-parameters "Direct link to 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 "Direct link to 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[​](#time-boundary-macros "Direct link to 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[​](#time-filter-macros "Direct link to 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[​](#time-interval-macros "Direct link to 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[​](#dashboard-filter-macro "Direct link to 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[​](#how-results-are-plotted "Direct link to 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[​](#line-and-stacked-bar-charts "Direct link to 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[​](#pie-chart "Direct link to Pie chart")