clickhouse-grafana

Altinity Grafana datasource plugin for ClickHouse® (grafana Grafana 4.6+ supported)

Altinity ClickHouse datasource plugin provides a support for ClickHouse as a backend database.

Initially plugin developed by Vertamedia, maintaned by Altinity since 2020.

Quick start

setup notes for plugin version 3.4.9+

Because we added output_format_json_quote_64bit_integers=1 to any browser request from plugin, if your user which you use for connection have readonly=1, change it to readonly=2, for example /etc/clickhouse-server/users.d/readonly.xml

  2

ClickHouse 25.8+ setup notes for plugin version before 3.4.9

New ClickHouse version change default behavior for output_format_json_quote_64bit_integers=0, which not allows to read generated JSON in JavaScript, look details in https://github.com/ClickHouse/ClickHouse/issues/86553 To properly works old version Altinity clickhouse datasource plugin for Grafana use following config /etc/clickhouse-server/users.d/output_format_json_quote_64bit_integers.xml

  1

Grafana 10+ setup notes for plugin version before 3.0.0

Old versions of Altinity ClickHouse datasource plugin for Grafana written in Angular. So you can watch warning like

Angular plugin
This data source plugin uses a deprecated, legacy platform based on AngularJS and will stop working in future releases of Grafana.

Don't worry about warning message, plugin will still working until Grafana 11 will release, after it upgrade to Altinity ClickHouse datasource plugin for Grafana to 3.x version is required.

Grafana 7+ setup notes for plugin version before 2.2.0

When 2.0.x and 2.1.x vertamedia-clickhouse-grafana plugin versions released Grafana team didn't provide worked signing method for community plugins. Current sign process describe on grafana.com

so, for properly setup 2.0.x and 2.1.x plugins you need change configuration option

[plugins]
allow_loading_unsigned_plugins=vertamedia-clickhouse-datasource

or setup environment variable

GF_PLUGINS_ALLOW_LOADING_UNSIGNED_PLUGINS=vertamedia-clickhouse-datasource

You can install plugin from grafana.com

OR

Copy files to your Grafana plugin directory. Restart Grafana, check data sources list at Configuration -> Datasources -> New, choose ClickHouse option.

Features

• Access to CH via HTTP / HTTPS
• Query setup
• Raw SQL editor
• Query formatting
• Macros support
• Additional functions
• Templates
• Table view
• SingleStat view
• Ad-hoc filters
• Annotations
• Alerts support
• Histogram support
• Logs support
• Flamegraph support
• Traces support

Access to ClickHouse via HTTP / HTTPS

Page configuration is standard

There is a small feature - ClickHouse treats HTTP Basic Authentication credentials as a database user and will try to run queries using its name.

CHProxy (optional)

Using of CHProxy will bring additional features:

• Easily setup HTTPS access to ClickHouse as shown here to provide secure access.
• Limit concurrency and execution time for requests from Grafana as shown here to prevent ClickHouse overloading from Grafana.
• Protection against request bursts for dashboards with numerous graphs. CHProxy allows queueing requests and execute them sequentially. To learn more - read about params max_queue_size and max_queue_time at CHProxy page.
• Response caching for the most frequent queries as shown here.

Caching will protect ClickHouse from excessive refreshes and will be optimal option for popular dashboards.

Hint - if you need to cache requests like last 24h where timestamp changes constantly then try to use Round option at Raw Editor

Query setup

Query setup interface:

First row FROM contains two options: database and table. Table values depends on a selected database. Next rows contains selectors for time filtering:

Column timestamp time

• DateTime (DateTime)
• DateTime64 (DateTime64)
• TimeStamp (UInt32).

Timestamp column Date column

Timestmap column are required for time-based macros and functions because all analytics based on these values. Plugin will try to detect Date, Date32 column automatically

Button Go to Query is just a toggler to Raw SQL Editor

Raw SQL Editor

Raw Editor allows custom SQL queries to be written:

Raw Editor allows typing queries, get info about functions and macros, format queries as Clickhouse do. To Execute query on server press "Run Query" or just leave focus from SQL editor textarea.

Under the Editor you can find options which allows setup rounding, time column step and Add metadata to SQL query which allows know which dashboard and user produce workload to your ClickHouse server.

Press Show Generated SQL for see a raw query (all macros and functions have already been replaced) which will be sent directly to ClickHouse.

Macros support

Plugin supports the following marcos:

• $table - replaced with selected table name from Query Builder
• $dateCol - replaced with Column:Date value from Query Builder
• $dateTimeCol - replaced with Column:DateTime or Column:TimeStamp value from Query Builder
• $from - replaced with (timestamp with ms)/1000 value of UI selected "Time Range:From"
• $to - replaced with (timestamp with ms)/1000 value of UI selected "Time Range:To"
• $interval - replaced with selected "Group by a time interval" value (as a number of seconds)
• $timeFilter - replaced with currently selected "Time Range". Requires Column:Date and Column:DateTime or Column:TimeStamp to be selected.
• $timeFilterByColumn($column) - replaced with currently selected "Time Range" for a column passed as $column argument. Use it in queries or query variables as ...WHERE $timeFilterColumn($column)... or ...WHERE $timeFilterColumn(created_at)....
• $timeSeries - replaced with special ClickHouse construction to convert results as time-series data. Use it as "SELECT $timeSeries...".
• $naturalTimeSeries - replaced with special ClickHouse construction to convert results as time-series with in a logical/natural breakdown. Use it as "SELECT $naturalTimeSeries...".
• $unescape - unescapes variable value by removing single quotes. Used for multiple-value string variables: "SELECT $unescape($column) FROM requests WHERE $unescape($column) = 5"
• $adhoc - replaced with a rendered ad-hoc filter expression, or "1" if no ad-hoc filters exist. Since ad-hoc applies automatically only to outer queries the macros can be used for filtering in inner queries.

A description of macros is available by typing their names in Raw Editor

Functions

Functions are just templates of SQL queries, and you can check the final query at Raw SQL Editor mode. If you need some additional complexity - just copy raw sql into Raw Editor and make according changes. Remember that macros are still available to use.

There are some limits in function use because of poor query analysis:

• Column:Date and Column:DateTime or Column:TimeStamp must be set in Query Builder
• Query must begin from function name
• Only one function can be used per query

Plugin supports the following functions:

$rate(cols...) - converts query results as "change rate per interval"

Example usage:

$rate(countIf(Type = 200) AS good, countIf(Type != 200) AS bad) FROM requests

Query will be transformed into:

…

sql $columns( Size, sum(Items) Items) FROM some_table

// It is also possible to use query without macros

### Worldmap panel ([https://github.com/grafana/worldmap-panel](https://github.com/grafana/worldmap-panel))

If you have a table with country/city codes:

```sql
SELECT
    1,
    Country AS c,
    sum(Requests) AS Reqs
FROM requests
GLOBAL ANY INNER JOIN
(
    SELECT Country, CountryCode
    FROM countries
) USING (CountryCode)
WHERE $timeFilter
GROUP BY
    c
ORDER BY Reqs DESC

…

sql
SELECT database, table, name, type FROM system.columns WHERE table='myTable' ORDER BY database, table

That should help to control data fetching by ad-hoc queries.

Template variable values via Query

To use time range dependent macros like $from and $to in your query the refresh mode of the template variable needs to be set to On Time Range Change.

SELECT ClientID FROM events WHERE EventTime > toDateTime($from) AND EventTime  if(addressToSymbol(x) != '', demangle(addressToSymbol(x)), 'unknown') , trace) AS label
WHERE trace_type='Real' AND $timeFilter
GROUP BY level, label, trace
ORDER BY trace, level

…

sql
SELECT
  trace_id AS traceID,
  span_id AS spanID,
  operation_name AS operationName,
  parent_span_id AS parentSpanID,
  'clickhouse' AS serviceName,
  intDiv(finish_time_us - start_time_us, 1000) AS duration,
  intDiv(start_time_us,1000) AS startTime,
  attribute AS tags,
  map('hostName',hostname) AS serviceTags
FROM
  system.opentelemetry_span_log
WHERE $timeFilter
ORDER BY traceID, startTime

Configure the Datasource with Provisioning

It’s now possible to configure datasources using config files with Grafana’s provisioning system. You can read more about how it works and all the settings you can set for datasources on the provisioning docs page.

Here are some provisioning example:

apiVersion: 1

datasources:
 - name: Clickhouse
   type: vertamedia-clickhouse-datasource
   access: proxy
   url: http://localhost:8123
   #  enable/disable basic auth
   basicAuth: false
   #  basic auth username
   basicAuthUser: "default"
   #  enable/disable with credentials headers
   withCredentials: false
   #  mark as default datasource. Max one per org
   isDefault: false
   #  fields that will be converted to json and stored in json_data
   jsonData:
     #  enable/disable sending 'add_http_cors_header=1' parameter
     addCorsHeader: false
     #  enable/disable using POST method for sending queries
     usePOST: false
     #  enable/disable using Accept-Encoding header in each request
     useCompression: false
     #  compression type allowed values: gzip, zstd, br, deflate
     compressionType: ""
     #  default database name
     defaultDatabase: ""
     #  enable/disable tls authorization
     tlsAuth: false
     #  enable/disable tls authorization with custom ca
     tlsAuthWithCACert: false
     #  enable/disable authorization with X-ClickHouse-* headers
     useYandexCloudAuthorization: false
     #  X-ClickHouse-Key header value for authorization
     xHeaderUser: ""
     #  the same value as url when `useYandexCloudAuthorization: true` 
     # @todo remove this workaround when merge https://github.com/grafana/grafana/pull/80858
     dataSourceUrl: "http://localhost:8123"
   secureJsonData:
     #  X-ClickHouse-User header value for authorization
     xHeaderKey: ""
     #  basic auth password
     basicAuthPassword: ""
     #  custom certificate authority for TLS https connection, base64 encoded 
     tlsCACert: ""
     #  custom client certificate for TLS https connection, base64 encoded 
     tlsClientCert: ""
     #  custom client