"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 urleng.com

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

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
• REPLACE /metrixxx WITH YOUR OWN UNIQUE STRING OR BE DOOMED

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

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

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

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

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('https://urleng.com/metrixxx', 'JSONEachRow')

INSERT INTO default.prometheus_exporter VALUES ('mygauge', 100, 'my little metric', 'gauge', '{"le":"0.05"}', toUnixTimestamp(now()+300))

---