Home

🕑 Click-Heus

ClickHouse Custom Metrics Exporter for Prometheus

⭐ ClickHeus Functionality

• Execute recurring Clickhouse queries
• Exctract mapped labels and values
• Aggregate results using metric buckets
• Publish as prometheus metrics

---

🏷️ Usage Example

The following example illustrates mapping of clickhouse query columns to metric labels and values.

1) Choose a Clickhouse Datasource

Let's use the following fictional my_index table as our datasource:

datetime	status	group
1631825843	FINISHED	default
1631825844	FAILED	default
1631825845	FINISHED	default
1631825846	FAILED	custom
1631825847	FINISHED	default
...	...	...

2) Define a Metrics Bucket

Using the prom_metrics array, define and name new bucket and its definitions.

• Type can be gauge or histogram
• LabelNames should match the target tag columns

  "prom_metrics": [{
      "name": "g",
      "type": "gauge",
      "settings": {
         "name": "my_count",
         "help": "My Counter",
         "maxAgeSeconds": 60,
         "labelNames":[
            "status",
            "group"
         ]
      }
  }]

3) Define a Clickhouse Query

Using the queries array, define a clickhouse query to execute and associate it with metrics bucket g

• Place your tags first in the query
• Place your metric value last, and mark its position using the counter_position parameter (count from 0).
• Match the refresh rate in milliseconds to match the query range (ie: 60 seconds)

  "queries":[{
      "name": "my_status",
      "query": "SELECT status, group, count(*) FROM my_index FINAL PREWHERE (datetime >= toDateTime(now()-60)) AND (datetime < toDateTime(now()) ) group by status, group",
      "counter_position": 2,
      "refresh": 60000,
      "metrics":["g"]
  }]

4) Output Metrics

Connect to the configured /metrics HTTP endpoint defined in your configuration and await data

# HELP my_count My Counter
# TYPE my_count gauge
my_count{status="FINISHED",group="default"} 10

---

🏷️ MV Example

While simple queries are easily served, complex/busy/large datasets might lead to memory issues. Materialized Views are the solution. In the example you have aggregation (downsampling) to 5 minutes interval (toIntervalSecond(300)).

CREATE MATERIALIZED VIEW hepic_data.status_distribution
(
    `record_datetime` DateTime,
    `status` String,
    `group` String,
    `count` UInt64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(record_datetime)
ORDER BY (captid, record_datetime)
TTL record_datetime + toIntervalDay(1)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1, merge_with_ttl_timeout = 3600 AS
SELECT
    toStartOfInterval(record_datetime, toIntervalSecond(300)) AS record_datetime,
    status,
    group,
    COUNT(*) AS count
FROM my_db.my_index 
WHERE isNotNull(group)
GROUP BY
    record_datetime,
    status,
    group

Once ready, swap your complex query with the MV we just created:

SELECT status, group, count FROM status_distribution FINAL PREWHERE (record_datetime >= toDateTime(now()-300)) AND (record_datetime < toDateTime(now()) )