metrico/distributed-clickhouse-prometheus

Distributed Metrics for ClickHouse + URL Engine (How-To)

"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! 🔋
• 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:

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:

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

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

📮 Easy and Fun, isn't it? Go ahead, update your metrics from any server/service and scrape away!

Bonus Steps

⚠️ 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))

---

Disclaimers

Terms of Use

No warranties of any kind, either express or implied. Data can be removed or lost at any moment of time. Use at your own risk.

Security

This service does not provide any security or privacy. Traffic is secured by Cloudflare and data stored on Deta cloud. Use at your own risk.

Cookies

This service does not collect, store or use cookies.

Limitations

Worker execution time limits might crop large INSERT queries