Documentation Plugins ClickHouse data source 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 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.
• 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 when you select the ClickHouse data source, or when you add or edit a panel and select ClickHouse. It includes the following elements.

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.

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

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

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. Set Format to Trace when building the query (available from plugin version 2.2.0).

If you use the OpenTelemetry Collector and ClickHouse exporter, 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

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

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

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

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

Next steps

• ClickHouse template variables — Use variables in dashboards and queries.
• Configure the ClickHouse data source — Connection and authentication options.