ClickHouse Metrics

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:

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