metrico/urleng

Distributed URL Engine for ClickHouse (public service)

Decentralized URL Engine for ClickHouse

Cloudflare + Deta Worker serving a decentralized, stateful, serverless URL Engine platform for ClickHouse.

About

The serverless API enables separate ClickHouse servers to access external distributed tables hosted on the cloud and share any type of data between nodes and 3rd party applications in real-time through the native URL Engine and Functions available in ClickHouse.

Serverless Platforms

The distributed URL engine platform is running on Cloudflare Workers and utilizing Deta Cloud as backend.

• Cloudflare Worker NDJSON Proxy (limited to 100k req/day) *
• Cloudflare Worker API Handler (limited to 100k req/day) *
• Deta Micros + Base Storage (unlimited requests, max 10k/inserts/query) *

---

Public Access Point

• https://urleng.com/{yoursupersecretkeygoeshere}

---

Usage

The following examples illustrates usage for distributed INSERT and SELECT statements

INSERT

:) INSERT INTO FUNCTION url('https://urleng.com/supersecret', JSONEachRow, 'key String, value UInt64') VALUES ('hello', 1), ('world', 2)

SELECT

:) SELECT * FROM url('https://urleng.com/supersecret', JSONEachRow);

┌─key───┬─value─┐
│ hello │     1 │
│ world │     2 │
└───────┴───────┘

2 rows in set. Elapsed: 0.126 sec. 

URL ENGINE

:) CREATE TABLE default.url_engine_distributed
   (
       `key` String,
       `value` UInt64,
   )
   ENGINE = URL('https://urleng.com/supersecret', 'JSONEachRow')

:) INSERT INTO url_engine_distributed VALUES ('hello', 1), ('world', 2)
:) SELECT * FROM url_engine_distributed

┌─key───┬─value─┐
│ hello │     1 │
│ world │     2 │
└───────┴───────┘

2 rows in set. Elapsed: 0.185 sec. 

Expiration

Items can be set to expire by including an __expires key in the object carrying a future Unix timestamp:

:) CREATE TABLE default.url_engine_expire
   (
       `key` String,
       `value` UInt64,
       `__expires` UInt64 DEFAULT toUInt64(toUnixTimestamp(now() + interval 24 hour))
   )
   ENGINE = URL('https://urleng.com/supersecret', 'JSONEachRow')

clickhouse-local

Get data into clickhouse-local with zero efforts:

clickhouse-local -q "select count() from url('https://urleng.com/supersecret', JSONEachRow)"

chdb

Get data using chdb in-memory engine:

python -m chdb "SELECT * from url('https://urleng.com/xxx', JSONEachRow, 'key String, value UInt64');" Pretty

CURL

Insert and query data using curl or any other HTTP/S GET/POST capable client.

POST ndjson

curl -s -XPOST https://urleng.com/supersecret \
     -H 'Content-Type:application/x-ndjson' --data-binary @ndjson.txt

POST json

curl -X POST https://url-engine.metrico.in/supersecret \
     -H 'Content-Type: application/json' -d '[{"key":"curl","value":1}]'

GET

curl -X GET https://urleng.com/supersecret

---

Notes

• INSERTS are updates. Existing data will be replaced. Runtime limit ~1000 records per INSERT.
• SELECT pulls the full table, pending X-Header extensions to prefilter WHERE statements.
• Because there is no sign-up, the URL /path is essentially a password, so pick something unique.

---

Examples

• URLENG Prometheus Metrics Storage + Exporter
• URLENG Stand Alone example for self-hosted caches (in memory)
• URLENG ClickHouse Fiddle example

(C) 2022 QXIP BV, for more info visit the qxip metrico repository

urleng's Issues

Unknown setting custom_url on 22.11.1

Hello there.

Great idea of utilizing url table function : )

There is one small issue tho, on the main page, it says we can do this:

SET custom_url = 'https://urleng.com/4c5u94cw592bsq9wypba'

which is actually not possible.

SET custom_url = 'https://urleng.com/4c5u94cw592bsq9wypba'

Query id: 88fbda2b-f2ad-4b99-ba1c-39e2b5f141e3


0 rows in set. Elapsed: 0.001 sec. 

Received exception from server (version 22.11.1):
Code: 115. DB::Exception: Received from localhost:9000. DB::Exception: Unknown setting custom_url. (UNKNOWN_SETTING)

But this works:

:) SET param_url = 'https://urleng.com/abc'

SET param_url = https://urleng.com/abc

Query id: 1a4c6ab9-e3bf-436f-9723-335a9d55f7c8

Ok.

0 rows in set. Elapsed: 0.001 sec. 

:) INSERT INTO FUNCTION url({url:String}, JSONEachRow, 'key String, value UInt64') VALUES ('hello', 1)

INSERT INTO FUNCTION url({url:String}, JSONEachRow, 'key String, value UInt64') FORMAT Values

Query id: c6e52e51-963b-4233-aa65-e0792e29b3e1

Ok.

1 row in set. Elapsed: 0.802 sec. 

:) SELECT * FROM url({url:String}, JSONEachRow, 'key String, value UInt64')

SELECT *
FROM url({url:String}, JSONEachRow, 'key String, value UInt64')

Query id: aa0d6fbf-8738-47f5-a365-b89213a023bc

┌─key───┬─value─┐
│ hello │     1 │
└───────┴───────┘

1 row in set. Elapsed: 0.551 sec. 

:)