Sep 16, 2019
Grafana is a very powerful and popular open source dashboard tool used in many ClickHouse projects. ClickHouse in Grafana is enabled by the Grafana ClickHouse plugin developed by the company Vertamedia, a ClickHouse early adopter. Many users in the community now depend on the Vertamedia implementation.
Times changed, the plugin authors left Vertamedia for other projects, and it became difficult for them to maintain and develop a plugin they do not use any more. Fortunately the main maintainer Roman Khavronenko was very collaborative and kindly granted Altinity rights to manage the Grafana Clickhouse plugin repository. We have therefore picked up development of the plugin for the community. It took us a while to learn the code, review, and merge some outstanding pull requests. Now we are happy to introduce a new version 1.9.0.
If you encounter any problems with the plugin or have feature requests, please open up issues on the GitHub project. Or even better, send a pull request if you know what needs to be changed. We will review and prioritize all of these along with other ClickHouse projects.
Now for the details of the release 1.9.0!
Changes since 1.8.1.
Added macro conditionalTest (thx to @TH-HA) #122
Added support for connect to Yandex.Cloud ClickHouse (thx to @negasus) #106
Fix identifier back quoting when there is a function call
Fix AST parser errors for quotes (thx to @Fiery-Fenix) #128
Added default database to all requests from datasource options (thx to @Fiery-Fenix) #126
Drop lodash fcn composition (thx to @simPod) #110
Cleanup build (thx to @simPod) #112
Plugin update instructions
The plugin can be updated using the grafana-cli tool from the command line:
$ grafana-cli plugins update vertamedia-clickhouse-datasource
The plugin will be updated into your grafana plugins directory; the default is /var/lib/grafana/plugins. Once plugin update completes, you need to restart the grafana service:
$ service grafana-server restart
Let’s review one notable new feature: the $conditionalTest macro.
Consider we have the following query template that is used in a dashboard over the popular public dataset of US airline flights — the ontime dataset. The template assumes we have a stateFrom filter, so we can select a particular source state to filter the data:
SELECT $timeSeries as t, count(*) Flights FROM $table WHERE OriginState IN ($stateFrom) AND $timeFilter GROUP BY t ORDER BY t
Now suppose that the ‘ALL’ checkbox is selected in a filter. In this case Grafana would take all possible states and generate the following query:
SELECT (intDiv(toUInt32(toDateTime(FlightDate)), 86400) * 86400) * 1000 as t, count(*) Flights FROM ontime WHERE OriginState IN ('GA','NC',...,'TT') AND FlightDate .... GROUP BY t ORDER BY t
The performance is not optimal in this case. We could skip the filter completely. This is what $conditionalTest macro is for. In order to use it, we can modify the source template this way:
SELECT $timeSeries as t, count(*) Flights FROM $table WHERE $timeFilter $conditionalTest(AND OriginState IN ($stateFrom),$stateFrom) GROUP BY t ORDER BY t
Note that the $conditionalTest macro has two parameters: a predicate to be added to the query, and a condition to test. Now, if we select ‘ALL’ in a filter, the unneeded condition will be automatically removed:
SELECT (intDiv(toUInt32(toDateTime(FlightDate)), 86400) * 86400) * 1000 as t, count(*) Flights FROM ontime WHERE FlightDate .... GROUP BY t ORDER BY t
If the filter is not empty, the condition will be added to the query.
The same approach also works for text fields, if those are left empty. Instead of doing extra SQL coding in order to account for empty values (which means no filter), one can now use the $conditionalTest macro. See more examples in the docs: https://github.com/Vertamedia/clickhouse-grafana#conditional-predicate
Enjoy the Release!
This is the first of many new releases of the ClickHouse Grafana plugins under Altinity sponsorship. Try it out and tell us what you think.