ClickHouse datasource plugin provides a support for ClickHouse as a backend database.
Install from grafana.net
OR
Copy files to your Grafana plugin directory. Restart Grafana, check datasources list at http://your.grafana.instance/datasources/new, choose ClickHouse option.
- Access to CH via HTTP
- Query setup
- Raw SQL editor
- Query formatting
- Macros support
- Additional functions
- Templates
- Table view
- SingleStat view
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 preventClickHouse
overloading fromGrafana
. - Protection against request bursts for dashboards with numerous graphs.
CHProxy
allows to queue requests and execute them sequentially. To learn more - read about paramsmax_queue_size
andmax_queue_time
at CHProxy page. - Response caching for the most frequent queries as shown here.
Caching
will protectClickHouse
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 useRound
option atRaw Editor
Query setup interface:
First row FROM
contains two options: database and table. Table values depends on selected database.
Second row contains selectors for time filtering:
- Column:Date (EventDate) - is mandatory for MergeTree tables
- Column:DateTime (DateTime) or Column:TimeStamp (UInt32).
Plugin will try to detect date columns automatically
Column:DateTime or Column:TimeStamp are required for time-based macros and functions, because all analytics is based on these values
Button Go to Query
is just a toggler to Raw SQL Editor
Raw Editor allows custom SQL queries to be written:
Raw Editor allows to type queries, get info about functions and macroses, format queries as Clickhouse do. Under the Editor you can find a raw query (all macros and functions have already been replaced) which will be sent directly to ClickHouse.
Plugin supports the following marcos:
- $table - replaced with selected table name from Query Builder
- $timeCol - replaced with Date:Col value from Query Builder
- $dateTimeCol - replaced with Column:DateTime or Column:TimeStamp value from Query Builder
- $from - replaced with timestamp/1000 value of selected "Time Range:From"
- $to - replaced with timestamp/1000 value of selected "Time Range:To"
- $interval - replaced with selected "Group by time interval" value (as a number of seconds)
- $timeFilter - replaced with currently selected "Time Range". Require Column:Date and Column:DateTime or Column:TimeStamp to be selected
- $timeSeries - replaced with special ClickHouse construction to convert results as time-series data. Use it as "SELECT $timeSeries...". Require Column:DateTime or Column:TimeStamp to be selected
A description of macros is available by typing their names in Raw Editor
Functions are just templates of SQL queries and you can check the final query at Raw SQL Editor mode. If some additional complexity is needed - 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 begins from function name
- Only one function can be used per query
Plugin supports the following functions:
Example usage:
$rate(countIf(Type = 200) AS good, countIf(Type != 200) AS bad) FROM requests
Query will be transformed into:
SELECT
t,
good / runningDifference(t / 1000) AS goodRate,
bad / runningDifference(t / 1000) AS badRate
FROM
(
SELECT
(intDiv(toUInt32(EventTime), 60)) * 1000 AS t,
countIf(Type = 200) AS good,
countIf(Type != 200) AS bad
FROM requests
WHERE ((EventDate >= toDate(1482796747)) AND (EventDate <= toDate(1482853383))) AND ((EventTime >= toDateTime(1482796747)) AND (EventTime <= toDateTime(1482853383)))
GROUP BY t
ORDER BY t ASC
)
Example usage:
$columns(OSName, count(*) c) FROM requests
Query will be transformed into:
SELECT
t,
groupArray((OSName, c)) AS groupArr
FROM
(
SELECT
(intDiv(toUInt32(EventTime), 60) * 60) * 1000 AS t,
OSName,
count(*) AS c
FROM requests
ANY INNER JOIN oses USING (OS)
WHERE ((EventDate >= toDate(1482796627)) AND (EventDate <= toDate(1482853383))) AND ((EventTime >= toDateTime(1482796627)) AND (EventTime <= toDateTime(1482853383)))
GROUP BY
t,
OSName
ORDER BY
t ASC,
OSName ASC
)
GROUP BY t
ORDER BY t ASC
This will help to build the next graph:
Example usage:
$rateColumns(OS, count(*) c) FROM requests
Query will be transformed into:
SELECT
t,
arrayMap(lambda(tuple(a), (a.1, a.2 / runningDifference(t / 1000))), groupArr)
FROM
(
SELECT
t,
groupArray((OS, c)) AS groupArr
FROM
(
SELECT
(intDiv(toUInt32(EventTime), 60) * 60) * 1000 AS t,
OS,
count(*) AS c
FROM requests
WHERE ((EventDate >= toDate(1482796867)) AND (EventDate <= toDate(1482853383))) AND ((EventTime >= toDateTime(1482796867)) AND (EventTime <= toDateTime(1482853383)))
GROUP BY
t,
OS
ORDER BY
t ASC,
OS ASC
)
GROUP BY t
ORDER BY t ASC
)
Remember that piechart plugin is not welcome for using in grafana - see https://grafana.com/blog/2015/12/04/friends-dont-let-friends-abuse-pie-charts
To create "Top 5" diagram we will need two queries: one for 'Top 5' rows and one for 'Other' row.
Top5:
SELECT
1, /* fake timestamp value */
groupArray((UserName, Reqs))
FROM
(
SELECT
UserName,
sum(Reqs) AS Reqs
FROM requests
GROUP BY UserName
ORDER BY Reqs desc
LIMIT 5
)
Other:
SELECT
1, /* fake timestamp value */
tuple(tuple('Other', sum(Reqs)))
FROM
(
SELECT
UserName,
sum(Reqs) AS Reqs
FROM requests
GROUP BY UserName
ORDER BY Reqs desc
LIMIT 5,10000000000000 /* select some ridiculous number after first 5 */
)
There are no any tricks in displaying time-series data. But to display some summary we will need to fake timestamp data:
SELECT
rand() Time, /* fake timestamp value */
UserName,
sum(Reqs) as Reqs
FROM requests
GROUP BY
UserName
ORDER BY
Reqs
Better to hide Time
column at Options
tab while editing panel
Vertical histogram (https://grafana.com/plugins/graph)
To make vertical histogram from graph panel we will need to edit some settings:
- Display -> Draw Modes -> Bars
- Axes -> X-Axis -> Mode -> Series
And use next query:
$columns(
Size,
sum(Items) Items)
FROM some_table
// It is also possible to use query without macros
Why no alerts support?
Alerts feature requires changes in Grafana
's backend, which can't be extended for now. Grafana
's maintainers are working on this feature.
Since we developed this plugin only for internal needs we don't have some of Grafana's features:
- Alerts (this feature requires additional changes at backend and can't be solved by js-plugin)
- Annotations
- Labels
We know that code quality needs a tons of improvements and unit-tests. We will continue working on this. If you have any idea for an improvement or found a bug do not hesitate to open an issue or submit a pull request. We will appreciate any help from the community which will make working with such amazing products as ClickHouse and Grafana more convenient.
Plugin creation was inspired by great grafana-sqldb-datasource
MIT License, please see LICENSE for details.