SQL-based visualizations

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

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 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[​](#number-chart "Direct link to 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[​](#table-chart "Direct link to Table chart")

All result columns are displayed directly as table columns.

## Examples[​](#examples "Direct link to 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](https://play-clickstack.clickhouse.com).

### Line chart — log count over time by service[​](#example-line-chart "Direct link to 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[​](#example-line-chart-macros "Direct link to 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[​](#example-stacked-bar "Direct link to 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[​](#example-table "Direct link to 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[​](#example-pie "Direct link to 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[​](#example-number "Direct link to 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[​](#notes "Direct link to 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.

- [Creating a SQL-based visualization](#creating-a-raw-sql-chart)- [Query parameters](#query-parameters)
    - [Available parameters](#available-parameters)- [Macros](#macros)
      - [Time boundary macros](#time-boundary-macros)- [Time filter macros](#time-filter-macros)- [Time interval macros](#time-interval-macros)- [Dashboard filter macro](#dashboard-filter-macro)- [How query results are plotted](#how-results-are-plotted)
        - [Line and Stacked Bar charts](#line-and-stacked-bar-charts)- [Pie chart](#pie-chart)- [Number chart](#number-chart)- [Table chart](#table-chart)- [Examples](#examples)
          - [Line chart — log count over time by service](#example-line-chart)- [Line chart — using macros](#example-line-chart-macros)- [Stacked bar chart — error count by severity](#example-stacked-bar)- [Table chart — top 10 slowest endpoints](#example-table)- [Pie chart — request distribution by service](#example-pie)- [Number chart — total error count](#example-number)- [Notes](#notes)

Was this page helpful?