# Wrapping a Serverless ClickHouse pt.1

<mark>LATEST UPDATE: </mark> [<mark>https://blog.qryn.dev/chdb-clickhouse-as-a-library</mark>](https://blog.qryn.dev/chdb-clickhouse-as-a-library)

# ClickHouse, The Database

**ClickHouse** is our database of choice and the baseline we love to use for all of our products at [qxip/metrico](https://metrico.in) including our observability tools [qryn](https://qryn.dev) and [hepic](https://hepic.tel).

We 💛 ClickHouse - Alexey Milovidov and his Team built a fantastic community around the project, later joined by VC partners to form ClickHouse Inc.

### ClickHouse Life

For most of us ClickHouse is deployed as a standard database service.

Having an **always-on** *server or cluster* is very much required by most deployments and integrations for of this amazing technology coupling *high-speed* storage and processing. This means predictable costs and performance for those maximizing their own infrastructure.

There are also many great Clickhouse Hosting companies we work with delivering *state-of-the-art managed service instances* and great benchmark results with predictable costs at any scale - all backed by *extremely skilled* technical teams.

💜 [Gigapipe](https://gigapipe.com) *(featuring a hosted qryn:cloud integration)*  
💙 [Altinity](https://altinity.com) *(supporting the community and many of our customers)*  
💚 [DoubleCloud](https://double.cloud) *(very kind sponsor of our public demo resources)*

Life is good. But...

Sometimes it would be nice to just run a query against our own cloud data without the need for a dedicated ClickHouse server in *standby*. This would have an infinite amount of both practical *and* educational purposes.  
*Do we let this one go, or...*

![](https://i.gifer.com/96wX.gif align="left")

### Let's Serverless

We'll keep the complexities for later. All we want is a stateless function we can call when needed with a fast API to query our remote S3/URL storage, **in one-shot**. We want to use the latest `clickhouse` codebase, so no major patches or changes are allowed - a good prototype should be easy to port and recreate in other languages. K.I.S.S. We're going back to the roots of ClickHouse.

### Hello, ClickHouse-Local

> The `clickhouse-local` program performs fast processing on local files, without having to deploy a ClickHouse server. `clickhouse-local` supports most features, formats and table engines but has no data or storage

Great! `clickhouse-local` is a stripped down build of `clickhouse` itself and can be greatly customized and extended *(or shrunk)* to meet specific feature profiles. This is just the perfect **building block** for our prototype, but it needs a bit of love ...

#### The Good

👍 standalone clickhouse functionality  
👍 support for remote storage *(s3, urleng, etc)*  
👍 easy overlay API with any language *(node, go, python)*  
👍 as fast as clickhouse and *zero cpu waste*

#### The Bad

👎 too many components redundant to the serverless role  
👎 dynamic ELF won't work on lambdas and serverless *(docker excluded)*  
👎 static ELF binaries bake super large *(~200mb uncompressed)*  
👎 slow ELF startup time when compressed *(~40mb w/ upx)*

### Slim Faster

In order to work our new binary, we'll have to strip things down a little and create a portable `clickhouse` binary without the server parts, maintaining the functionality required for working with remote data. ClickHouse Issue #29378 helped lots.

#### Less is More 🍈🍉🍋

Let's start by disabling optional features using available `cmake` build parameters `$ cmake .. \ -DCMAKE_BUILD_TYPE=Release \ -DENABLE_CLICKHOUSE_ALL=OFF \ -DENABLE_CLICKHOUSE_LOCAL=ON \ -DENABLE_CLICKHOUSE_SELF_EXTRACTING=OFF \ -DENABLE_CLICKHOUSE_BENCHMARK=OFF \ -DENABLE_CLICKHOUSE_KEEPER=OFF \ -DENABLE_CLICKHOUSE_KEEPER_CONVERTER=OFF \ -DENABLE_CLICKHOUSE_INSTALL=OFF \ -DENABLE_CLICKHOUSE_TEST=OFF \ -DENABLE_CLICKHOUSE_COPIER=OFF \ -DENABLE_CLICKHOUSE_COMPRESSOR=OFF \ -DENABLE_CLICKHOUSE_STATIC_FILES_DISK_UPLOADER=OFF \ -DENABLE_CLICKHOUSE_ODBC_BRIDGE=OFF \ -DENABLE_AVX=ON \ -DENABLE_AVX2=OFF \ -DENABLE_LIBRARIES=OFF \ -DENABLE_PROTOBUF=OFF \ -DENABLE_SIMDJSON=ON \ -DENABLE_RAPIDJSON=ON \ -DENABLE_UTILS=OFF \ -DUSE_UNWIND=ON \ -DUSE_SENTRY=0 \ -DUSE_STATIC_LIBRARIES=ON \ -DUSE_AWS_S3=ON \ -DENABLE_TESTS=OFF \ -DENABLE_EMBEDDED_COMPILER=0 \ -DENABLE_RDKAFKA=0 \ -DENABLE_POCO_MONGODB=0 \ -DENABLE_POCO_REDIS=0 \ -DENABLE_ODBC=0 \ -DENABLE_MYSQL=0 \ -DENABLE_S3=1 \ -DENABLE_GRPC=0 \ -DENABLE_HDFS=0 \ -DENABLE_AZURE_BLOB_STORAGE=0 \ -DENABLE_CASSANDRA=0 \ -DENABLE_KRB5=0 \ -DENABLE_ODBC=0 \ -DENABLE_AMQPCPP=0 \ -DENABLE_LDAP=0 \ -DENABLE_CCACHE=0 \ -DENABLE_SSL=ON`

> This is just a reference. There are many further optimizations possible  
> `AVX2` must be disabled for our function to run on virtual/emulated CPUs

#### 💀 `PR_SET_NAME`

A minor road blocker is the lack of support for `PR_SET_NAME` on AWS Lambdas.  
Pending PR, let's *patch* our build to allow this function to fail unharmed `$ sed -i '/Cannot set thread name/c\' /ClickHouse/src/Common/setThreadName.cpp`

Now we're ready to compile *(with Docker alpine in our case)* and *patiently wait*

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1665240798875/BHrmIuwnz.png align="left")

```bash
Alpine clang version 14.0.6
Target: x86_64-alpine-linux-musl

$ ./ClickHouse/build/programs/clickhouse

Use one of the following commands:
clickhouse local [args]
```

> ✨ Nice. Our portable clickhouse binary is ready to run *almost anywhere!*

## Pinocchio Layer

In order for our custom binary to work in real life we'll need to **lie** to our clients and wrap everything with a thin API compatibility layer (a technique we mastered while [developing qryn](https://qryn.dev) and [fluxpipe](https://github.com/metrico/fluXpipe)) to spawn process executions and handle incoming queries.

If you're thinking *"What? We just removed this from ClickHouse and now we're adding it back?"* - that is *precisely* what we're doing with this *ELF proxy hybrid*

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1665242214477/XU9MPZ0ip.png align="left")

To avoid reading/decompressing the file each time we need it and to minimize startup time and disk reads, we will also load the compressed binary in anonymous memory ([memfd](https://man7.org/linux/man-pages/man2/memfd_create.2.html)) and execute via a shared descriptor for the lifetime of the container or process execution. *Just like a friendly virus!* ⚠️

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1665310085595/_PLR4nvLt.png align="left")

#### V is in the House

Let's live on the edge and write our wrapper HTTP API in ~30 lines of *vlang* and then compile it into a *tiny static executable &lt; 100kb*

> Same can be achieved with NodeJS, Python, etc... choose your own poison

##### `vhouse.v`

```sql
module main
import os
import memfd
import vweb

struct App {
    vweb.Context
}

fn (mut app App) index() vweb.Result {
    query := app.query['query'] or { '' }
    if query == '' { return app.html('No Query') }
    args := ['/proc/$os.getpid()/fd/3', 'local', '-q', '"' + query + '"']
    result := os.execute(args.join(' '))
    return app.text(result.output.str())
}

fn main() {
    data := os.read_file('./clickhouse') or {
        panic('error reading ELF')
        return
    }
    res := memfd.vmemfd_new('clickhouse')
    os.fd_write(res, data)
    vweb.run(&App{}, 8123)
}
```

*That's all we need*, one GET handler. Embedding a POST [play](https://raw.githubusercontent.com/ClickHouse/ClickHouse/master/programs/server/play.html) is optional.

> 😉 hidden bonus: no chunked responses or NDJSON handlers!

Let's compile and test our application using [v](https://github.com/vlang/v)

#### Compile a static binary

```sql
$ v -prod vhouse.v
```

#### Execute in the same folder as our custom `clickhouse`

```sql
$ ./vhouse
[vweb] Running app on http://localhost:8123/
```

#### Test with CURL

```sql
$ curl -G "http://localhost:8123" --data-urlencode "query=SELECT version()"

22.9.1.1
```

Great. But is it fast enough?

```sql
       {
             "elapsed": 0.000624628,
             "rows_read": 0,
             "bytes_read": 0
        }
```

That's promising, now let's try with some **real data** off a public **S3** Bucket!

```sql
$ curl -G "http://localhost:8123" \
--data-urlencode "query=SELECT * FROM s3('https://s3.us-east-1.amazonaws.com/altinity-clickhouse-data/nyc_taxi_rides/data/tripdata/data-20*.csv.gz', 'CSVWithNames', 'pickup_date Date, id UInt64, vendor_id String, tpep_pickup_datetime DateTime, tpep_dropoff_datetime DateTime, passenger_count UInt8, trip_distance Float32, pickup_longitude Float32, pickup_latitude Float32, rate_code_id String, store_and_fwd_flag String, dropoff_longitude Float32, dropoff_latitude Float32, payment_type LowCardinality(String), fare_amount Float32, extra String, mta_tax Float32, tip_amount Float32, tolls_amount Float32, improvement_surcharge Float32, total_amount Float32, pickup_location_id UInt16, dropoff_location_id UInt16, junk1 String, junk2 String', 'gzip') LIMIT 20 FORMAT JSON"
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1665317917205/9pq4u2mDW.png align="left")

> ✨ There we go, now that's something!

```sql
        {
             "elapsed": 1.195765097,
             "rows_read": 0,
             "bytes_read": 0
        }
```

Your request mileage may vary - it should take about `1-2s` to complete the average S3 query, depending on the compute/network factor. The row and bytes counters will be `0` until we add a few `HTTP headers` later on.

### Moment of Truth

> ✨ glitch app

Let's run our newborn function on a *free* glitch instance and compare

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1665253205961/_JEb7hISH.png align="left")

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1665253231622/-lL7QkNIK.png align="left")

> ✨ deta micro

Let's deploy on [deta space](https://deta.space) and push this even further!

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1665395434869/2NrTuI25H.png align="left")

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1665395526876/trq8khDix.png align="left")

That's just as fast as other "serverless" commercial options - *and 100% free!*  
Not bad for our *poor man's* wrapper running on free resources!

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1665323452724/77UGpJVju.png align="left")

#### 💀 Lazy Mode = ON

Try it out right now using docker. The image includes the `play` app, too! `docker run -d --name vhouse -p 8123:8080 --rm qxip/vhouse:22.9.1.1`

#### 💸 No S3? No Problem!

Try querying using our [free URLENG service](https://urleng.com) or Cloudflare R2 endpoints `sql SELECT count() from url('https://urleng.com/loadtest', JSONEachRow)`

### Live Demo

This is not an image. It's a real Clickhouse serverless shell.

<iframe src="https://clickhouse.glitch.me" width="100%" height="500"></iframe>

## Conclusion

Did that just work? Yes, with a bundled HTTP ClickHouse client, too 🖖

You now have a *hybrid* serverless `clickhouse` function you can have fun chaining into your logic for fun and profit 🚀 We have a fleet of these babies running on `glitch`, `aws lambda` and `deta space` just to mention a few *low-powered* resources you can leverage in your experiments.

At [qxip/metrico](https://metrico.in) we use this to power our demos without requiring a backend.

This is by no mean trying to challenge ClickHouse Cloud or their products. It's just a fun prototype for those interested in building hybrid API wrappers *the wrong way*. Modifying ClickHouse local to expose a pico server function would also be possible, but outside the scope of this `clickhacking` experiment.

### What's Next?

There's a lot more coming!

* [Serverless ClickHouse pt. 2](https://blog.qryn.dev/wrapping-a-serverless-clickhouse-pt2)
    
* [Serverless Flux / InfluxDB](https://blog.qryn.dev/fluxpipe-serverless-flux-influxdb)
    

We're working on more pieces for this puzzle, such as Lambda templates, **FLUX** piping, **S3** and [URLENG](https://blog.qryn.dev/serverless-url-engine) + [Deta](https://deta.space) storage bundles to provide truly *personal, free forever* serverless ClickHouse instances for anyone learning the art and for those building new cool ideas with it.

> Enjoy and Share your Comments! 👋

*Did this get you excited? Join our team,* [*we're hiring*](mailto:info@qxip.net)*!*  
*Working with ClickHouse? Try our* [*polyglot observability stack*](https://qryn.dev)