New Release for Grafana ClickHouse Plugin

ByAndrey Monakhov
Subscribe

 

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!

Release Notes

Changes since 1.8.1.

New features

  • Added macro conditionalTest (thx to @TH-HA) #122

  • Added support for connect to Yandex.Cloud ClickHouse (thx to @negasus) #106

Big Fixes

  • 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

$conditionalTest macro

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. 

 

 

Share