---
title: "ClickHouse query editor | Grafana Plugins documentation"
description: "This document describes the ClickHouse query editor"
---

# ClickHouse query editor

This document explains how to use the ClickHouse query editor to build and run queries. You can access the query editor from [Explore](/docs/grafana/latest/visualizations/explore/) to run ad hoc queries, or when you add or edit a panel and select the ClickHouse data source.

## Before you begin

- [Configure the ClickHouse data source](/docs/plugins/grafana-clickhouse-datasource/latest/configure/).
- Ensure your ClickHouse user has read access to the databases and tables you want to query.

## Query editor elements

The query editor appears in [Explore](/docs/grafana/latest/visualizations/explore/) when you select the ClickHouse data source, or when you add or edit a panel and select ClickHouse. It includes the following elements.

Expand table

| Element         | Description                                                                                                                                                                                |
|-----------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| **Editor type** | Switch between **SQL** (write raw SQL) and **Query builder** (build queries with drop-downs and filters).                                                                                  |
| **Run Query**   | Runs the current query and refreshes the panel. In the SQL editor you can also use **Ctrl+Enter** (Windows/Linux) or **Cmd+Enter** (macOS).                                                |
| **Query type**  | Choose the result format: **Table**, **Logs**, **Time series**, or **Traces**. This sets how Grafana interprets and visualizes the results. Available in both SQL and Query builder modes. |

**In SQL mode:**

- **SQL editor** — A code editor where you write ClickHouse SQL. It provides schema suggestions (databases, tables, columns) as you type. If SQL validation is enabled in the data source settings, the editor marks invalid syntax.
- **Format code** — Use the editor toolbar to format your SQL.
- **Query type** — Select **Table**, **Logs**, **Time series**, or **Traces** so the panel uses the correct visualization.

**In Query builder mode:**

- **Database** and **Table** — Select the database and table to query from the drop-downs.
- **Query type** — Select **Table**, **Logs**, **Time series**, or **Traces**. The builder shows options that match the type (for example, time column and value columns for time series; columns, filters, group by, and order by for tables).
- **Type-specific options** — Configure columns, filters, grouping, sorting, limit (max rows), and (for traces) trace ID. The builder generates the SQL for you.
- **SQL preview** — At the bottom of the builder, you can see the generated SQL. You can switch to SQL mode to edit it manually.

## Build queries

You can build queries using the **SQL editor** (raw SQL) or the **Query builder**. Queries can include macros for dynamic parts such as time range filters.

### Query builder modes

When using the query builder, the available options depend on the selected **query type**:

Expand table

| Query type      | Builder modes                    | Description                                                                                                                                                             |
|-----------------|----------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| **Table**       | **List** or **Aggregate**        | List returns raw rows. Aggregate lets you add functions like `count()`, `avg()`, and `GROUP BY`.                                                                        |
| **Time series** | **Trend**                        | Automatically groups by time using `$__timeInterval()` and applies aggregate functions. Select a time column, one or more value columns, and optional grouping columns. |
| **Logs**        | **List** or **Aggregate**        | List returns log rows. Aggregate supports grouping for log volume histograms.                                                                                           |
| **Traces**      | **Trace ID** or **Trace search** | Trace ID fetches a single trace by ID. Trace search finds traces matching filters (service name, duration, time range).                                                 |

### Logs query builder

The logs query builder provides structured fields for common log exploration patterns. When **Use OTel** is enabled, the builder pre-fills column mappings for [OpenTelemetry ClickHouse exporter](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/exporter/clickhouseexporter) tables.

Expand table

| Field                  | Description                                                                                                                                                                                                    |
|------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| **Time column**        | The high-precision timestamp column used for sorting log rows.                                                                                                                                                 |
| **Filter Time column** | A lower-precision column for fast time range filtering (for example, a `Date` or `DateTime` column indexed for partition pruning). Using a separate filter column can significantly improve query performance. |
| **Log Level column**   | The column that contains the log severity level.                                                                                                                                                               |
| **Log Message column** | The column that contains the log message body.                                                                                                                                                                 |

You can also filter by log message text using the search field, and add column filters for resource, scope, or log attributes.

### Traces query builder

The traces query builder supports two modes:

- **Trace ID mode** — Enter a trace ID (or use a template variable like `$traceId`) to fetch all spans for that trace.
- **Trace search mode** — Filter traces by service name, span name, duration range, and time range.

When **Use OTel** is enabled, column mappings are pre-filled for the OpenTelemetry schema. You can also configure:

Expand table

| Field              | Description                                                                                     |
|--------------------|-------------------------------------------------------------------------------------------------|
| **Duration unit**  | The unit for the duration column (`seconds`, `milliseconds`, `microseconds`, or `nanoseconds`). |
| **Flatten nested** | Enable if your traces table was created with `flatten_nested=1`.                                |
| **Events prefix**  | Prefix for event columns (for example, `Events.Timestamp`, `Events.Name`).                      |
| **Links prefix**   | Prefix for link columns (for example, `Links.TraceId`, `Links.SpanId`).                         |

### Log volume and logs sample

When using the **query builder** in Explore with the **Logs** query type, Grafana automatically shows a **log volume** histogram above the log results and can display a **logs sample** panel. These supplementary queries are generated by the plugin and run alongside your main query.

> Note
> 
> Log volume and logs sample are only available when all queries in the panel use the **query builder**. If any query uses the **SQL editor**, supplementary queries are disabled.

## Time series

For time series visualizations, your query must include a `datetime` column. Use an alias of `time` for the timestamp column. Grafana treats timestamp rows without an explicit time zone as UTC. Any column other than `time` is treated as a value column.

## Multi-line time series

To create multi-line time series, the query must return at least 3 columns in this order:

1. A `datetime` column with an alias of `time`
2. A column to group by (for example, category or label)
3. One or more metric columns

Example (replace `mgbench.logs1` with your database and table):

SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy

```sql
SELECT log_time AS time, machine_group, avg(disk_free) AS avg_disk_free
FROM mgbench.logs1
GROUP BY machine_group, log_time
ORDER BY log_time
```

## Tables

Table visualizations are available for any valid ClickHouse query. Select **Table** in the panel visualization options to view results in tabular form.

## Visualize logs with the Logs panel

To use the Logs panel, your query must return a timestamp and string values. To default to the logs visualization in Explore, set the timestamp column alias to `log_time`.

Example (replace `logs1` with your database and table, for example `mydb.logs`):

SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy

```sql
SELECT log_time AS log_time, machine_group, toString(avg(disk_free)) AS avg_disk_free
FROM logs1
GROUP BY machine_group, log_time
ORDER BY log_time
```

When you don’t have a `log_time` column, set **Format** to **Logs** to force logs rendering (available from plugin version 2.2.0).

## Visualize traces with the Traces panel

To use the Traces panel, your data must meet the [requirements of the traces panel](/docs/grafana/latest/explore/trace-integration/#data-api). Set **Format** to **Trace** when building the query (available from plugin version 2.2.0).

If you use the [OpenTelemetry Collector and ClickHouse exporter](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/exporter/clickhouseexporter), the following query returns the required column names (case sensitive). Replace the trace ID in the WHERE clause with your trace ID or a template variable (for example `$traceId`):

SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy

```sql
SELECT
  TraceId AS traceID,
  SpanId AS spanID,
  SpanName AS operationName,
  ParentSpanId AS parentSpanID,
  ServiceName AS serviceName,
  Duration / 1000000 AS duration,
  Timestamp AS startTime,
  arrayMap(key -> map('key', key, 'value', SpanAttributes[key]), mapKeys(SpanAttributes)) AS tags,
  arrayMap(key -> map('key', key, 'value', ResourceAttributes[key]), mapKeys(ResourceAttributes)) AS serviceTags,
  if(StatusCode IN ('Error', 'STATUS_CODE_ERROR'), 2, 0) AS statusCode
FROM otel.otel_traces
WHERE TraceId = '61d489320c01243966700e172ab37081'
ORDER BY startTime ASC
```

## Column roles

When you use the Query builder with the **Logs**, **Time series**, or **Traces** query type, each built-in column slot is mapped to a *semantic role*. The builder renames your columns to the fixed aliases Grafana’s panels expect, so the same panel can visualize data from any ClickHouse schema once you tell it which columns play which roles.

For example, choosing your `EventTime` column as the **Time** role for a Logs query produces `SELECT EventTime AS timestamp, ...`; the Logs panel then sorts on `timestamp` without needing to know your real column name.

### Logs query type

Expand table

| Role          | SQL alias   | OTel column    | Common non-OTel names                                 |
|---------------|-------------|----------------|-------------------------------------------------------|
| **Time**      | `timestamp` | `Timestamp`    | `timestamp`, `event_time`, `@timestamp`, `created_at` |
| **Message**   | `body`      | `Body`         | `message`, `msg`, `log_message`                       |
| **Log Level** | `level`     | `SeverityText` | `level`, `severity`, `severity_text`, `log_level`     |

Optional additional columns (OTel mode only): `TraceId` → `traceID`, plus the attribute maps `ResourceAttributes`, `ScopeAttributes`, and `LogAttributes`.

### Time series query type

Expand table

| Role     | Description                                                                                                                                     |
|----------|-------------------------------------------------------------------------------------------------------------------------------------------------|
| **Time** | Timestamp used to order and bucket the series. Must be a `DateTime` or `DateTime64` column. The panel time range filter applies to this column. |

Any other selected columns become value series. In **Aggregate** mode the builder produces `GROUP BY` on the time column and the aggregated columns.

### Traces query type

Trace-panel column aliases are fixed; choose the table columns that play each role.

Expand table

| Role               | SQL alias       | OTel column          | Common non-OTel names                      |
|--------------------|-----------------|----------------------|--------------------------------------------|
| **Trace ID**       | `traceID`       | `TraceId`            | `trace_id`, `traceId`                      |
| **Span ID**        | `spanID`        | `SpanId`             | `span_id`, `spanId`                        |
| **Parent Span ID** | `parentSpanID`  | `ParentSpanId`       | `parent_span_id`                           |
| **Service Name**   | `serviceName`   | `ServiceName`        | `service`, `service_name`                  |
| **Operation Name** | `operationName` | `SpanName`           | `operation`, `operation_name`, `span_name` |
| **Start Time**     | `startTime`     | `Timestamp`          | `start_time`, `timestamp`                  |
| **Duration Time**  | `duration`      | `Duration`           | `duration`, `duration_ns`, `duration_ms`   |
| **Tags**           | `tags`          | `SpanAttributes`     | `tags`, `attributes`                       |
| **Service Tags**   | `serviceTags`   | `ResourceAttributes` | `resource`, `resource_attributes`          |
| **Kind**           | `kind`          | `SpanKind`           | `kind`, `span_kind`                        |
| **Status Code**    | `statusCode`    | `StatusCode`         | `status`, `status_code`                    |
| **Status Message** | `statusMessage` | `StatusMessage`      | `status_message`                           |

Set the **Duration Unit** to match the units your column stores (OTel uses nanoseconds; other schemas often use milliseconds or seconds). The builder converts durations to milliseconds for the trace panel.

To avoid re-mapping roles for every query, configure defaults under **Data source settings → Logs** and **Data source settings → Traces**. Enabling **OTel** mode populates every role with the OTel-conventional column name automatically.

## Macros

Macros simplify query syntax and add dynamic parts such as dashboard time range filters. The plugin replaces macros with the corresponding SQL before the query is sent to ClickHouse.

Example query using a time filter macro (replace `test_data` and `date_time` with your table and timestamp column):

SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy

```sql
SELECT date_time, data_stuff
FROM test_data
WHERE $__timeFilter(date_time)
```

Expand table

| Macro                                        | Description                                                                                      | Output example                                                                                                                        |
|----------------------------------------------|--------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------|
| `$__dateFilter(columnName)`                  | Filters by the panel date range using the given column.                                          | `date >= toDate('2022-10-21') AND date <= toDate('2022-10-23')`                                                                       |
| `$__timeFilter(columnName)`                  | Filters by the panel time range (seconds).                                                       | `time >= toDateTime(1415792726) AND time <= toDateTime(1447328726)`                                                                   |
| `$__timeFilter_ms(columnName)`               | Filters by the panel time range (milliseconds).                                                  | `time >= fromUnixTimestamp64Milli(1415792726123) AND time <= fromUnixTimestamp64Milli(1447328726456)`                                 |
| `$__dateTimeFilter(dateColumn, timeColumn)`  | Combines date and time filters for separate Date and DateTime columns.                           | `date >= toDate('2022-10-21') AND date <= toDate('2022-10-23') AND time >= toDateTime(1415792726) AND time <= toDateTime(1447328726)` |
| `$__dt(dateColumn, timeColumn)`              | Shorthand alias for `$__dateTimeFilter`.                                                         | Same as `$__dateTimeFilter`.                                                                                                          |
| `$__fromTime`                                | Start of the panel time range as `DateTime`.                                                     | `toDateTime(1415792726)`                                                                                                              |
| `$__toTime`                                  | End of the panel time range as `DateTime`.                                                       | `toDateTime(1447328726)`                                                                                                              |
| `$__fromTime_ms`                             | Start of the panel time range as `DateTime64(3)`.                                                | `fromUnixTimestamp64Milli(1415792726123)`                                                                                             |
| `$__toTime_ms`                               | End of the panel time range as `DateTime64(3)`.                                                  | `fromUnixTimestamp64Milli(1447328726456)`                                                                                             |
| `$__interval_s`                              | Panel interval in seconds.                                                                       | `20`                                                                                                                                  |
| `$__timeInterval(columnName)`                | Interval from panel time range (seconds), for grouping.                                          | `toStartOfInterval(toDateTime(column), INTERVAL 20 second)`                                                                           |
| `$__timeInterval_ms(columnName)`             | Interval from panel time range (milliseconds), for grouping.                                     | `toStartOfInterval(toDateTime64(column, 3), INTERVAL 20 millisecond)`                                                                 |
| `$__conditionalAll(condition, $templateVar)` | Uses the first parameter when the template variable does not select all values; otherwise `1=1`. | `condition` or `1=1`                                                                                                                  |

You can also use brace notation `{}` when the macro parameter must contain a query or other expression.

## Next steps

- [ClickHouse template variables](/docs/plugins/grafana-clickhouse-datasource/latest/template-variables/) — Use variables in dashboards and queries.
- [Configure the ClickHouse data source](/docs/plugins/grafana-clickhouse-datasource/latest/configure/) — Connection and authentication options.