Plugins 〉ClickHouse

Data Source

grafana

ClickHouse

Overview
Installation
Change log
Related content

Official ClickHouse data source for Grafana

The ClickHouse data source plugin allows you to query and visualize ClickHouse data in Grafana.

Grafana Dashboard Screenshot - Query Analysis

Grafana Dashboard Screenshot - Data Analysis

Version compatibility

Users on Grafana v9.x and higher of Grafana can use v4. Users on Grafana v8.x are encouraged to continue using v2.2.0 of the plugin.

* As of 2.0 this plugin will only support ad hoc filters when using ClickHouse 22.7+

Installation

For detailed instructions on how to install the plugin on Grafana Cloud or locally, please checkout the Plugin installation docs.

Configuration

ClickHouse user for the data source

Set up an ClickHouse user account with readonly permission and access to databases and tables you want to query. Please note that Grafana does not validate that queries are safe. Queries can contain any SQL statement. For example, statements like ALTER TABLE system.users DELETE WHERE name='sadUser' and DROP TABLE sadTable; would be executed.

To configure a readonly user, follow these steps:

Create a readonly user profile following the Creating Users and Roles in ClickHouse guide.
Ensure the readonly user has enough permission to modify the max_execution_time setting required by the underlying clickhouse-go client.
If you're using a public Clickhouse instance, it's not recommended to set readonly=2 in the readonly profile. Instead, leave readonly=1 and set the constraint type of max_execution_time to changeable_in_readonly to allow modification of this setting.

ClickHouse protocol support

The plugin supports both Native (default) and HTTP transport protocols. This can be enabled in the configuration via the protocol configuration parameter. Both protocols exchange data with ClickHouse using optimized native format.

Note that the default ports for HTTP/S and Native differ:

HTTP - 8123
HTTPS - 8443
Native - 9000
Native with TLS - 9440

Manual configuration via UI

Once the plugin is installed on your Grafana instance, follow these instructions to add a new ClickHouse data source, and enter configuration options.

With a configuration file

It is possible to configure data sources using configuration files with Grafana’s provisioning system. To read about how it works, refer to Provisioning Grafana data sources.

Here are some provisioning examples for this data source using basic authentication:

apiVersion: 1
datasources:
  - name: ClickHouse
    type: grafana-clickhouse-datasource
    jsonData:
      defaultDatabase: database
      port: 9000
      host: localhost
      username: username
      tlsSkipVerify: false
      # tlsAuth: <bool>
      # tlsAuthWithCACert: <bool>
      # secure: <bool>
      # dialTimeout: <seconds>
      # queryTimeout: <seconds>
      # protocol: <native|http>
      # defaultTable: <string>
      # httpHeaders:
      # - name: X-Example-Header
      #   secure: false
      #   value: <string>
      # - name: Authorization
      #   secure: true
      # logs:
      #   defaultDatabase: <string>
      #   defaultTable: <string>
      #   otelEnabled: <bool>
      #   otelVersion: <string>
      #   timeColumn: <string>
      #   ...Column: <string>
      # traces:
      #   defaultDatabase: <string>
      #   defaultTable: <string>
      #   otelEnabled: <bool>
      #   otelVersion: <string>
      #   durationUnit: <seconds|milliseconds|microseconds|nanoseconds>
      #   traceIdColumn: <string>
      #   ...Column: <string>
    secureJsonData:
      password: password
      # tlsCACert: <string>
      # tlsClientCert: <string>
      # tlsClientKey: <string>
      # secureHttpHeaders.Authorization: <string>

Building queries

Queries can be built using the raw SQL editor or the query builder. Queries can contain macros which simplify syntax and allow for dynamic SQL generation.

Time series

Time series visualization options are selectable after adding a datetime field type to your query. This field will be used as the timestamp. You can select time series visualizations using the visualization options. Grafana interprets timestamp rows without explicit time zone as UTC. Any column except time is treated as a value column.

Multi-line time series

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

field 1: datetime field with an alias of time
field 2: value to group by
field 3+: the metric values

For example:

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 will always be available for any valid ClickHouse query.

Visualizing 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 mode, set the timestamp alias to log_time.

For example:

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

To force rendering as logs, in absence of a log_time column, set the Format to Logs (available from 2.2.0).

Visualizing traces with the Traces Panel

Ensure your data meets the requirements of the traces panel. This applies if using the visualization or Explore view.

Set the Format to Trace when constructing the query (available from 2.2.0).

If using the Open Telemetry Collector and ClickHouse exporter, the following query produces the required column names (these are case sensitive):

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

To simplify syntax and to allow for dynamic parts, like date range filters, the query can contain macros.

Here is an example of a query with a macro that will use Grafana's time filter:

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

Macro	Description	Output example
$__dateFilter(columnName)	Replaced by a conditional that filters the data (using the provided column) based on the date range of the panel	`date >= toDate('2022-10-21') AND date <= toDate('2022-10-23')`
$__timeFilter(columnName)	Replaced by a conditional that filters the data (using the provided column) based on the time range of the panel in seconds	`time >= toDateTime(1415792726) AND time <= toDateTime(1447328726)`
$__timeFilter_ms(columnName)	Replaced by a conditional that filters the data (using the provided column) based on the time range of the panel in milliseconds	`time >= fromUnixTimestamp64Milli(1415792726123) AND time <= fromUnixTimestamp64Milli(1447328726456)`
$__dateTimeFilter(dateColumn, timeColumn)	Shorthand that combines $__dateFilter() AND $__timeFilter() using separate Date and DateTime columns.	`$__dateFilter(dateColumn) AND $__timeFilter(timeColumn)`
$__fromTime	Replaced by the starting time of the range of the panel casted to `DateTime`	`toDateTime(1415792726)`
$__toTime	Replaced by the ending time of the range of the panel casted to `DateTime`	`toDateTime(1447328726)`
$__fromTime_ms	Replaced by the starting time of the range of the panel casted to `DateTime64(3)`	`fromUnixTimestamp64Milli(1415792726123)`
$__toTime_ms	Replaced by the ending time of the range of the panel casted to `DateTime64(3)`	`fromUnixTimestamp64Milli(1447328726456)`
$__interval_s	Replaced by the interval in seconds	`20`
$__timeInterval(columnName)	Replaced by a function calculating the interval based on window size in seconds, useful when grouping	`toStartOfInterval(toDateTime(column), INTERVAL 20 second)`
$__timeInterval_ms(columnName)	Replaced by a function calculating the interval based on window size in milliseconds, useful when grouping	`toStartOfInterval(toDateTime64(column, 3), INTERVAL 20 millisecond)`
$__conditionalAll(condition, $templateVar)	Replaced by the first parameter when the template variable in the second parameter does not select every value. Replaced by the 1=1 when the template variable selects every value.	`condition` or `1=1`

The plugin also supports notation using braces {}. Use this notation when queries are needed inside parameters.

Templates and variables

To add a new ClickHouse query variable, refer to Add a query variable.

After creating a variable, you can use it in your ClickHouse queries by using Variable syntax. For more information about variables, refer to Templates and variables.

Importing dashboards for ClickHouse

Follow these instructions to import a dashboard.

You can also find available, pre-made dashboards by navigating to the data sources configuration page, selecting the ClickHouse data source and clicking on the Dashboards tab.

We distribute the following dashboards with the plugin. These are aimed at assisting with support analysis of a ClickHouse cluster and do not rely on external datasets. The querying user requires access to the system database.

Cluster Analysis - an overview of configured clusters, merges, mutations and data replication.
Data Analysis - an overview of current databases and tables, including their respective sizes, partitions and parts.
Query Analysis - an analysis of queries by type, performance and resource consumption.

Ad Hoc Filters

Ad hoc filters are only supported with version 22.7+ of ClickHouse.

Ad hoc filters allow you to add key/value filters that are automatically added to all metric queries that use the specified data source, without being explicitly used in queries.

By default, Ad Hoc filters will be populated with all Tables and Columns. If you have a default database defined in the Datasource settings, all Tables from that database will be used to populate the filters. As this could be slow/expensive, you can introduce a second variable to allow limiting the Ad Hoc filters. It should be a constant type named clickhouse_adhoc_query and can contain: a comma delimited list of databases, just one database, or a database.table combination to show only columns for a single table.

For more information on Ad Hoc filters, check the Grafana docs

Using a query for Ad Hoc filters

The second clickhouse_adhoc_query also allows any valid Clickhouse query. The query results will be used to populate your ad-hoc filter's selectable filters. You may choose to hide this variable from view as it serves no further purpose.

For example, if clickhouse_adhoc_query is set to SELECT DISTINCT machine_name FROM mgbench.logs1 you would be able to select which machine names are filtered for in the dashboard.

Learn more

Add Annotations.
Configure and use Templates and variables.
Add Transformations.
Set up alerting; refer to Alerts overview.

Installing ClickHouse on Grafana Cloud:

For more information, visit the docs on plugin installation.

Changelog

4.5.0 Features

Implemented log context for log queries
Added configuration options for log context columns
Queries parsed from the SQL editor will now attempt to re-map columns into their correct fields for Log and Trace queries.
Added support for IN operator in adhoc filters

Fixes

Fixed and enhanced the logic for parsing a query back into the query builder.

4.4.0

Features

Added "Labels" column selector to the log query builder
Datasource OTel configuration will now set default table names for logs and traces.

Fixes

Added warning for when uid is missing in provisioned datasources.
Map filters in the query builder now correctly show the key instead of the column name
Updated and fixed missing system.dashboards dashboard in list of dashboards
Updated the duration value in example traces dashboard to provide useful information
Fix to display status codes from spans in trace queries (#950)

4.3.2

Fixes

Optimized performance for types dependent on the JSON converter
Dependency updates

4.3.1

Features

Added preset dashboard from system.dashboards table

Fixes

Fix trace start times in trace ID mode (#900)
Fixed OTel dashboard that waa failing to import (#908)

4.3.0

Features

Added OpenTelemetry dashboard (#884)

Fixes

Fix support for LowCardinality strings (#857)
Update trace queries to better handle time fields (#890)
Dependency bumps

4.2.0

Features

Added $__dateTimeFilter() macro for conveniently filtering a PRIMARY KEY composed of Date and DateTime columns.

4.1.0

Features

Added the ability to define column alias tables in the config, which simplifies query syntax for tables with a known schema.

4.0.8

Fixes

Fixed IN operator escaping the entire string (specifically with Nullable(String)), also added FixedString(N) (#830)
Fixed query builder filter editor on alert rules page (#828)

4.0.7

Upgrade dependencies

4.0.6

Fixes

Add support for configuring proxy options from context rather than environment variables (supported by updating sqlds) (#799)

4.0.5

Fixes

Fixed converter regex for Nullable(IP) and Nullable(String). It won't match to Array(Nullable(IP)) or Array(Nullable(String)) any more. (#783)
Updated grafana-plugin-sdk-go to fix a PDC issue. More details here (#790)

4.0.4

Fixes

Changed trace timestamp table from the constant otel_traces_trace_id_ts to a suffix _trace_id_ts applied to the current table name.

4.0.3

Features

Added $__fromTime_ms macro that represents the dashboard "from" time in milliseconds using a DateTime64(3)
Added $__toTime_ms macro that represents the dashboard "to" time in milliseconds using a DateTime64(3)
Added $__timeFilter_ms macro that uses DateTime64(3) for millisecond precision time filtering
Re-added query type selector in dashboard view. This was only visible in explore view, but somehow it affects dashboard view, and so it has been re-added. (#730)
When OTel is enabled, Trace ID queries now use a skip index to optimize exact ID lookups on large trace datasets (#724)

Fixes

Fixed performance issues caused by $__timeFilter using a DateTime64(3) instead of DateTime (#699)
Fixed trace queries from rounding span durations under 1ms to 0 (#720)
Fixed AST error when including Grafana macros/variables in SQL (#714)
Fixed empty builder options when switching from SQL Editor back to Query Editor
Fix SQL Generator including "undefined" in FROM when database isn't defined
Allow adding spaces in multi filters (such as WHERE .. IN)
Fixed missing AND keyword when adding a filter to a Trace ID query

4.0.2

Fixes

Fixed migration script not running when opening an existing v3 config

4.0.1

Fixes

Set protocol to native by default in config view. Fixes the "default port" description.

4.0.0

Features

Version 4.0.0 contains major revisions to the query builder and datasource configuration settings.

Query Builder

Completely rebuilt query builder to have specialized editors for Table, Logs, Time Series, and Traces.
Completely rebuilt SQL generator to support more complicated and dynamic queries.
Updated query builder options structure to be clearer and support more complex queries.
Updated database/table selector to be in a more convenient location. Database and table options are automatically selected on initial load.
Upgraded query builder state management so queries stay consistent when saving/editing/sharing queries.
Separated Table and Time Series query builders. Table view operates as a catch-all for queries that don't fit the other query types.
Combined "format" into the query type switcher for simplicity. The query tab now changes the builder view and the display format when on the Explore page. This includes the raw SQL editor.
Added an OTEL switch for logs and trace views. This will allow for quicker query building for those using the OTEL exporter for ClickHouse.
Updated Time Series query builder with dedicated Time column. Default filters are added on-load.
Added an IS ANYTHING filter that acts as a placeholder for easily editing later (useful for query templates/bookmarks on the Explore page.)
Added better support for Map types on the Filter editor.
LIMIT editor can now be set to 0 to be excluded from the query.
Table and Time Series views now have a simple / aggregate mode, depending on the query complexity.
Updated the logs histogram query to use the new query builder options and column hints.
Added Logs query builder with dedicated Time, Level, and Message columns. Includes OTEL switch for automatically loading OTEL schema columns. Default filters are added on-load.
Added Trace query builder with dedicated trace columns. Includes OTEL switch for automatically loading OTEL schema columns. Default filters are added on-load.
Updated data panel filtering to append filters with column hints. Visible in logs view when filtering by a specific level. Instead of referencing a column by name, it will use its hint.
Order By now lists aggregates by their full name + alias.
Order By column allows for custom value to be typed in.
Aggregate column name allows for custom value to be typed in.
Filter editor allows for custom column names to be typed in.
Increased width of filter value text input.
Columns with the Map* type now show a [] at the end to indicate they are complex types. For example, SpanAttributes[].
Filter editor now has a dedicated field for map key. You can now select a map column and its key separately. For example, SpanAttributes['key'].
Map types now load a sample of options when editing the key for the map. This doesn't include all unique values, but for most datasets it should be a convenience.
Added column hints, which offers better linking across query components when working with columns and filters. For example, a filter can be added for the Time column, even without knowing what the time column name is yet. This enables better SQL generation that is "aware" of a column's intended use.

Plugin Backend

Added migration logic for v3 configs going to v4+. This is applied when the config is loaded when building a database connection.
$__timeFilter, $__fromTime, and $__toTime macros now convert to DateTime64(3) for better server-side type conversion. Also enables millisecond precision time range filtering.

Datasource Configuration

Added migration script for v3.x configurations to v4+. This runs automatically when opening/saving the datasource configuration.
Renamed config value server to host.
Renamed config value timeout to the more specific dial_timeout.
Updated labeling for port selection. The default port will now change depending on native/http and secure/unsecure setting.
Rearranged fields and sections to flow better for initial setup of a new datasource.
Added plugin version to config data for easier config version migrations in the future.
Added fields for setting default values for database/table.
Added section for setting default log database/table/columns. Includes OTEL. These are used when using the log query builder.
Added section for setting default trace database/table/columns. Includes OTEL. These are used when using the trace query builder.
Added OTEL switches for logs/traces for quicker query building. OTEL defaults to the latest version, and will auto update if kept on this setting.
Increased width of inputs for typically long values (server URL, path, etc.)
Allow adding custom HTTP headers with either plain text or secure credentials. #633
Add path setting to specify an additional URL path when using the HTTP protocol. #512

Fixes

Queries will now remain consistent when reloading/editing a previously saved query.
Fixed default Ad-Hoc filters. #650
Fixed Ad-Hoc filters parsing numeric fields. #629
Fixed majority of usability quirks with redesigned query builder.

Upgrades

Updated all dependencies to latest compatible versions (Includes Dependabot PRs)

3.3.0

Features

Support Point geo data type.

Fixes

Fix timeInterval_ms macro.
Fix Table summary and Parts over time panels in Data Analysis dashboard.

Upgrades

Upgrade grafana-plugin-sdk-go.

3.2.0

Features

Add timeInterval_ms macro to allow higher precision queries on DateTime64 columns. #462.

Fixes

Ensure databases, tables, and columns are escaped correctly. #460.
Fix conditionAll handling. #459.
Fix support for ad-hoc regexp filters: =~, !~ #414.
Do not create malformed adhoc filters #451. invalid values will be ignored.
Fix auto formatting by reverting to table correctly. #469.
Fix parsing of numeric configuration values in yaml file. #456.

3.1.0

Stable release of v3.0.4-beta

3.0.4-beta

Update Grafana dependencies to >=v9.0.0
Feature - Add support for the secure socks proxy

3.0.3-beta

Update ClickHouse driver to v2.9.2

3.0.2-beta

Custom ClickHouse settings can be set in data source settings. Allow passing custom ClickHouse settings in datasource
Histogram UI fixes Histogram UI fixes
- Support filter/filter out logs view actions
- Fix undefined database name by default
- Reset level and time field properly on table/database change
- Make it possible to clear the level field (so the histogram will render without grouping by level)
- Fix filter value that gets stuck in the UI
Tracing dashboard added to default dashboards. Tracing dashboard

3.0.1-beta

Users on v8.x of Grafana are encouraged to continue to use v2.2.0 of the plugin.
Users of Grafana v9.x can use v3 however it is beta and may contain bugs.

3.0.0

Feature - Logs volume histogram support
Chore - Update clickhouse-go to v2.8.1

2.2.1

Chore - Backend binaries compiled with latest go version 1.20.4
Custom ClickHouse settings can be set in data source settings. Allow passing custom ClickHouse settings in datasource
Standard Golang HTTP proxy environment variables support (HTTP_PROXY/HTTPS_PROXY/NO_PROXY). See FromEnvironment for more information. If the Grafana instance is started with one of these env variables, the driver will automatically load them now.

2.2.0

Feature - Support format dropdown and support for rendering traces

2.1.1

Fix - Date and Date32 type normalization with user's timezone

2.1.0

Fix - Quote table names with dots by @slvrtrn in https://github.com/grafana/clickhouse-datasource/pull/298
Add a predefined TimeRange filter if there is at least one DateTime* column by @slvrtrn in https://github.com/grafana/clickhouse-datasource/pull/304

2.0.7

Fix - Empty template variables used with the conditionalAll macro work the same as selecting All. Allow empty Inputs for $__conditionalAll
Fix - Intervals are limited to 1 second. limit $__interval_s to at least 1 second
Chore - Bump ClickHouse go API to v2.5.1 Bump github.com/ClickHouse/clickhouse-go/v2 from 2.4.3 to 2.5.1

2.0.6

Chore - Backend binaries compiled with latest go version 1.19.4
Chore - Backend grafana dependencies updated to latest version
Chore - Clickhouse-go client updated to v2.4.3

2.0.5

Chore - Update sqlds to 2.3.17 which fixes complex macro queries
Chore - Backend grafana dependency updated
Fix - Allow default protocol toggle value when saving in settings

2.0.4

Fix - Query builder: allow custom filter values for fields with Map type

2.0.3

Chore - Backend binaries compiled with latest go version 1.19.3
Chore - Backend grafana dependencies updated

2.0.2

Feature - Update sqlds to 2.3.13 which fixes some macro queries

2.0.1

Bug - Now works with Safari. Safari does not support regex look aheads

2.0.0

Feature - Upgrade driver to support HTTP
Feature - Changed how ad hoc filters work with a settings option provided in CH 22.7
Feature - Conditional alls are now handled with a conditional all function. The function checks if the second parameter is a template var set to all, if it then replaces the function with 1=1, and if not set the function to the first parameter.
Bug - Visual query builder can use any date type for time field
Fix - 'any' is now an aggregation type in the visual query builder
Fix - Time filter macros can be used in the adhoc query
Bug - Time interval macro cannot have an interval of 0
Fix - Update drive to v2.1.0
Bug - Expand query button works with grafana 8.0+
Fix - Added adhoc columns macro