README.md

ClickHouse datasource for Grafana 4.6+

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

Quick start

Install from grafana.net

OR

Copy files to your Grafana plugin directory. Restart Grafana, check datasources list at https://your.grafana.instance/datasources/new, choose ClickHouse option.

Features:

• Access to CH via HTTP
• Query setup
• Raw SQL editor
• Query formatting
• Macros support
• Additional functions
• Templates
• Table view
• SingleStat view
• Ad-hoc filters
• Annotations

Access to CH via HTTP

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 to queue 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 selected database. Second row contains selectors for time filtering:

• Column:Date (EventDate)
• 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 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.

Macros

Plugin supports the following marcos:

• $table - replaced with selected table name from Query Builder
• $dateCol - 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". Requires Column:Date and Column:DateTime or Column:TimeStamp to be selected
• $timeFilterByColumn($column) - replaced with currently selected "Time Range" for 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...".
• $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 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:

$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:

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
) 

---

$columns(key, value) - query values as array of [key, value], where key will be used as label

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:

---

$rateColumns(key, value) - is a combination of $columns and $rate

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
) 

$perSecond(cols...) - converts query results as "change rate per interval" for Counter-like(growing only) metrics

Example usage:

$perSecond(total_requests) FROM requests

Query will be transformed into:

SELECT
    t,
    if(runningDifference(max_0) < 0, nan, runningDifference(max_0) / runningDifference(t / 1000)) AS max_0_Rate
FROM
(
    SELECT
        (intDiv(toUInt32(Time), 60) * 60) * 1000 AS t,
        max(total_requests) AS max_0
    FROM requests
    WHERE ((Date >= toDate(1535711819)) AND (Date <= toDate(1535714715)))
    AND ((Time >= toDateTime(1535711819)) AND (Time <= toDateTime(1535714715)))
    GROUP BY t
    ORDER BY t ASC
)

// see issue 78 for the background

---

$perSecondColumns(key, value) - is a combination of $columns and $perSecond for Counter-like metrics

Example usage:

$perSecondColumns(type, total) FROM requests WHERE Type in ('udp','tcp')

Query will be transformed into:

SELECT
    t,
    groupArray((type, max_0_Rate)) AS groupArr
FROM
(
    SELECT
        t,
        type,
        if(runningDifference(max_0) < 0, nan, runningDifference(max_0) / runningDifference(t / 1000)) AS max_0_Rate
    FROM
    (
        SELECT
            (intDiv(toUInt32(Time), 60) * 60) * 1000 AS t,
            type,
            max(total) AS max_0
        FROM requests
        WHERE ((Date >= toDate(1535711819)) AND (Date <= toDate(1535714715)))
        AND ((Time >= toDateTime(1535711819)) AND (Time <= toDateTime(1535714715)))
        AND (Type IN ('udp', 'tcp'))
        GROUP BY
            t,
            type
        ORDER BY
            type ASC,
            t ASC
    )
)
GROUP BY t
ORDER BY t ASC

// see issue 80 for the background

---

Templating

Query Variable

If you add a template variable of the type Query, you can write a ClickHouse query that can return things like measurement names, key names or key values that are shown as a dropdown select box.

For example, you can have a variable that contains all values for the hostname column in a table if you specify a query like this in the templating variable Query setting.

SELECT hostname FROM host

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

SELECT event_name FROM event_log WHERE $timeFilterByColumn(time_column)

Another option is a query that can create a key/value variable. The query should return two columns that are named __text and __value. The __text column value should be unique (if it is not unique then the first value is used). The options in the dropdown will have a text and value that allows you to have a friendly name as text and an id as the value. An example query with hostname as the text and id as the value:

SELECT hostname AS __text, id AS __value FROM host

You can also create nested variables. For example if you had another variable named region. Then you could have the hosts variable only show hosts from the current selected region with a query like this (if region is a multi-value variable then use the IN comparison operator rather than = to match against multiple values):

SELECT hostname FROM host WHERE region IN ($region)

Conditional Predicat