L
Co-Founder and CEO at QXIP, makers of HOMER, HEPIC, QRYN and contributors to many other OSS projects in the monitoring and observability space
Command Palette
Search for a command to run...
Search for a command to run...
Co-Founder and CEO at QXIP, makers of HOMER, HEPIC, QRYN and contributors to many other OSS projects in the monitoring and observability space
Metrics can be lots fun, until you have to share them between a bunch of distributed servers... but fear not!
Let's build a serverless prometheus metrics store & publisher for ClickHouse using urleng.com
UrlEng is a free serverless pastie made for ClickHouse tables.
Let's use it to store a distributed table with a simple schema for our metrics:
| key | help | value | tags | __expires |
| metric name | metric help text | metric value | optional json tags | optional expiration unix ts |
To begin we will keep things basic - performance is not the challenge here but rather flexibiity. A few notes:
__expires column and a future unix timestamp/metrixxx WITH YOUR OWN UNIQUE STRING OR BE DOOMEDSimple it is! Let's INSERT our first little metric:
INSERT INTO FUNCTION url('https://urleng.com/metrixxx', JSONEachRow, 'key String, help String, value Float64, __expire UInt64, tags String') VALUES ('mygauge', 'my little metric', 100, toUnixTimestamp(now()+300), '{"le":"0.05"}')
That's easy. Let's take a closer look at our serverless URL table:
SELECT * FROM url('https://urleng.com/metrixxx', JSONEachRow)
┌─__expires──┬─help──────────────────┬─key──────┬─tags──────────┬─value─┐
│ 1659015366 │ my little metric │ mygauge │ {"le":"0.05"} │ 100 │
└────────────┴───────────────────────┴──────────┴───────────────┴───────┘
Looking good! Our distributed metrics table is ready. Get a materialized view up to insert some fun data.
:tooth: ... but the output is still a bit boring and can't be scraped, so its time to change the output format!
ClickHouse comes with a built in suport for the Prometheus format - its a little strict, but it works, so let's use it!
SELECT ifNull(key, 'undefined') as name, ifNull(toFloat64(value),0) as value, help as help, CAST(JSONExtractKeysAndValues(replaceAll(ifNull(tags,''), '\'','\"'), 'String'), 'Map(String, String)') as labels FROM url('https://urleng.com/metrixxx', JSONEachRow) FORMAT Prometheus
# HELP mygauge my little metric
mygauge{le="0.05"} 100
We're almost there - all we need is an endpoint we can scrape and luckily, ClickHouse does that too!
So let's use all the ClickHouse features we can, shall we?
Create a custom handler for our metrics query, ie: /etc/clickhouse-server/config.d/metric_export.xml
<yandex>
<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>
<custom_urleng>'https://urleng.com/metrixxx</custom_urleng>
<http_handlers>
<rule>
<url>/metrics</url>
<methods>GET</methods>
<handler>
<type>predefined_query_handler</type>
<query>SELECT ifNull(key, 'undefined') as name, ifNull(toFloat64(value),0) as value, help, CAST(JSONExtractKeysAndValues(replaceAll(ifNull(tags,''), '\'','\"'), 'String'), 'Map(String, String)') as labels FROM url(getSetting('custom_urleng'), JSONEachRow) FORMAT Prometheus</query>
</handler>
</rule>
<defaults/>
</http_handlers>
</yandex>
Et Voila'! Our custom endpoint is ready to be scraped. Let's curl a final test:
curl 'http://default:password@localhost:8123/metrics'
# TYPE mygauge gauge
mygauge{le="0.05"} 100
:postbox: Easy and Fun, isn't it? Go ahead, update your metrics from any server/service and scrape away!
:warning: This guide is intended as a firestarter - go crazy adding timestamping, output formats and anything else!
Going to use your URL engine store daily? Extend the schema and setup a URL Engine table for quicker access:
CREATE TABLE default.prometheus_exporter
(
`key` String,
`value` Float64,
`help` String,
`type` String,
`tags` String,
`__expires` UInt64 DEFAULT toUnixTimestamp(now()+300),
)
ENGINE = URL('https://urleng.com/metrixxx', 'JSONEachRow')
INSERT INTO default.prometheus_exporter VALUES ('mygauge', 100, 'my little metric', 'gauge', '{"le":"0.05"}', toUnixTimestamp(now()+300))