Prometheus + URLEng

Prometheus + URLEng

Frugal Metrics with URLEng

"Frugal Metrics"

Metrics can be lots fun, until you have to share them between a bunch of distributed servers... but fear not!

Distributed timeseries/metrics endpoint with UrlEng + ClickHouse

Let's build a serverless prometheus metrics store & publisher for ClickHouse using

  • no coding, no signups, no logins - batteries included! :battery:
  • zero config distributed table, simply accessible from anywhere
  • custom HTTP prometheus /metrics scraping endpoint w/ auto-expiration
  • persistent storage courtesy of deta cloud

Let's get started!

Storing metrics in UrlEng

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:

metric namemetric help textmetric valueoptional json tagsoptional expiration unix ts

To begin we will keep things basic - performance is not the challenge here but rather flexibiity. A few notes:

  • INSERTs for the same key are considered UPDATEs in URL tables
  • JSON Tags must be present or empty stringed
  • Metrics can auto-expire using the __expires column and a future unix timestamp

Simple it is! Let's INSERT our first little metric:

INSERT INTO FUNCTION url('', 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('', JSONEachRow)

│ 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!

FORMAT Prometheus

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('', 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!

Metrics HTTP Handler

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

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

Scrape it and Shake it!

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!

Bonus Steps

:warning: This guide is intended as a firestarter - go crazy adding timestamping, output formats and anything else!

URL Engine Table

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('', 'JSONEachRow')
INSERT INTO default.prometheus_exporter VALUES ('mygauge', 100, 'my little metric', 'gauge', '{"le":"0.05"}', toUnixTimestamp(now()+300))