Skip to content

ClickHouse Metrics

Lorenzo Mangani edited this page Jun 11, 2022 · 28 revisions


ClickHouse Metrics (CliQL)

qryn provides a simplified query model for generating metrics and tags dynamically out of clickhouse tables.

This approach requires no preparation and poses no discrimination as of how data is inserted into clickhouse.

Grafana handles qryn endpoints as native loki datasources, so no additional plugins are required either.

Query Options

The clickhouse qryn macro accepts the following parameters:

parameter description
db clickhouse database name
table clickhouse table name
tag column*(s)* for tags, comma separated
metric function for metric values
where where condition (optional)
interval interval in seconds (optional)
timefield time/date field name (optional)

Example

In this example, we'll query database my_database and table my_table to extract the average value of column mos as metric, and source_ip as one of many possible grouping tags. We can optionally specify a WHERE clause and specify the interval for our response resolution.

clickhouse({ 
  db="my_database", 
  table="my_table", 
  tag="source_ip", 
  metric="avg(mos)", 
  where="mos > 0", 
  interval="60" 
  timefield="record_datetime"
})
Clickhouse Query

The qryn subset query is translated into a native clickhouse query using the relative parameters and time boundaries.

SELECT source_ip, groupArray((t, c)) AS groupArr 
FROM (
  SELECT (intDiv(toUInt32(record_datetime), 60) * 60) * 1000 AS t, source_ip, avg(mos) c 
  FROM my_database.my_table 
  PREWHERE record_datetime BETWEEN 1610533076 AND 1610536677 AND mos > 0 
  GROUP BY t, source_ip ORDER BY t, source_ip
) 
GROUP BY source_ip ORDER BY source_ip

The results are formatted emulating the loki matrix format for tagged timeseries:

qryn

Single Point Query

When dealing with tables with no timestamp or timefield columns, use the following reference for single point queries:

clickhouse({ 
  db="system", 
  table="disks", 
  tag="name", 
  metric="avg((total_space-free_space)/total_space*100)", 
  where="((total_space-free_space)/total_space*100) < 50", 
  timefield="false"
})

Timeseries

Convert columns to tagged timeseries using the emulated loki 2.0 query format

<aggr-op> by (<labels,>) (<function>(<metric>[range_in_seconds])) from <database>.<table> where <optional condition>
Examples
avg by (source_ip) (rate(mos[60])) from my_database.my_table
sum by (ruri_user, from_user) (rate(duration[300])) from my_database.my_table where duration > 10

Alerting

qryn supports Alert Manager and Grafana Alerting APIs for transpiled clickhouse queries

image

image

Clone this wiki locally