Plugins 〉ClickHouse
ClickHouse
ClickHouse data source for Grafana
The ClickHouse data source plugin allows you to query and visualize ClickHouse data from within Grafana.
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.
Manual configuration
Once the plugin is installed on your Grafana instance, follow these instructions to add a new ClickHouse data source, and enter configuration options.
Note: this plugin uses the native ClickHouse TCP interface to connect and run queries. Make sure you configure the server address and port accordingly.
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, including all the settings that you can set for this data source, 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
server: localhost
username: username
tlsSkipVerify: false
secureJsonData:
password: password
Building queries
The query editor allows you to query ClickHouse to return time series or tabular data. Queries can contain macros which simplify syntax and allow for dynamic parts.
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 oftime
- 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
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 |
---|---|---|
$__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 >= '1480001790' AND time <= '1482576232' ) |
$__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 >= '1480001790671' AND time <= '1482576232479' ) |
$__fromTime | Replaced by the starting time of the range of the panel casted to DateTime | toDateTime(intDiv(1415792726371,1000)) |
$__toTime | Replaced by the ending time of the range of the panel casted to DateTime | toDateTime(intDiv(1415792726371,1000)) |
$__interval_s | Replaced by the interval in seconds | 20 |
$__timeInterval(columnName) | Replaced by a function calculating the interval based on window size, useful when grouping | toStartOfInterval(column, INTERVAL 20 second) |
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 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.
Grafana Cloud Pro
- $25 / user / month and includes a free trial for new users
- Available with a Grafana Cloud Pro plan
- Access to 1 Enterprise plugin
- Fully managed service (not available to self-manage)
Grafana Cloud Advanced / Grafana Enterprise
- Available with a Grafana Cloud Advanced plan or Grafana Enterprise license
- Access to all Enterprise plugins
- Run fully managed or self-manage on your own infrastructure
Installing ClickHouse on Grafana Cloud:
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
For more information, visit the docs on plugin installation.
Installing on a local Grafana:
For local instances, plugins are installed and updated via a simple CLI command. Plugins are not updated automatically, however you will be notified when updates are available right within your Grafana.
1. Install the Data Source
Use the grafana-cli tool to install ClickHouse from the commandline:
grafana-cli plugins install
The plugin will be installed into your grafana plugins directory; the default is /var/lib/grafana/plugins. More information on the cli tool.
Alternatively, you can manually download the .zip file for your architecture below and unpack it into your grafana plugins directory.
Alternatively, you can manually download the .zip file and unpack it into your grafana plugins directory.
2. Configure the Data Source
Accessed from the Grafana main menu, newly installed data sources can be added immediately within the Data Sources section.
Next, click the Add data source button in the upper right. The data source will be available for selection in the Type select box.
To see a list of installed data sources, click the Plugins item in the main menu. Both core data sources and installed data sources will appear.
Changelog
1.1.2
Bug - add timerange to metricFindQuery
1.1.1
Bug - add timeout
1.1.0
Feature - add convention for showing logs panel in Explore
1.0.0
Official release
0.12.7
Fix - ignore template vars when validating sql
0.12.6
Fix - Time series builder - use time alias when grouping/ordering
0.12.5
Chore - dashboards
0.12.4
Fix - timeseries where clause. make default db the default in visual editor
0.12.3
Fix - when removing conditional all, check scoped vars (support repeating panels)
0.12.2
Fix - when removing conditional all, only remove lines with variables
0.12.1
Fix - handle large decimals properly
0.12.0
Feature - Time series builder: use $__timeInterval macro on time field so buckets can be adjusted from query options.
0.11.0
Feature - Time series: Hide fields, use group by in select, use time field in group by
0.10.0
Feature - Ad-Hoc sourced by database or table
0.9.13
Fix - update sdk to show streaming errors
0.9.12
Fix - format check after ast change
0.9.11
Feature - $__timeInterval(column) and $__interval_s macros
0.9.10
Fix - Set format when using the new Run Query button.
0.9.9
Feature - Query Builder.
0.9.8
Fix - Detect Multi-line time series. Handle cases with functions.
0.9.7
Feature - Multi-line time series.
0.9.6
Bug - Change time template variable names.
0.9.5
Bug - Fix global template variables.
0.9.4
Bug - Fix query type variables.
0.9.3
Bug - Support Array data types.
0.9.2
Bug - Fix TLS model.
0.9.1
Add secure toggle to config editor.
0.9.0
Initial Beta release.