ClickHouse into Space
In our previous episode we had fun assembling a portable ClickHouse binary with a thin API layer ready to be used in a serverless function with remote cloud storage.
This is a free experimental ride for demo purposes. Not a final product!
The goal is to showcase the flexibility of Deta and power of ClickHouse
Sign up for Deta Space Alpha
Deta is your own personal computer in the cloud: private, secure & always online
🟣 All apps on Deta Space run in your own sandboxed 'personal cloud'
🟣 App developers cannot see or touch your personal infrastructure
🟣 App users run clones and remain in the drivers seat with all the data
Deta Space Micros, Base and Drive are perfect for modern web applications.
But can they run something as complex as ClickHouse? TLDR; HELL YEAH!
Launch your ClickHouse Space
Ready? Browse to the ClickHouse Space page using Discovery and Install
Space will generate a unique secure URL for your ClickHouse Space endpoint
Fire up your Browser - you should be welcomed by a familiar interface 🌞🌑
Awesome! Your serverless, stateless and soul-less ClickHouse is ready! 🔥
Deta ClickHouse + AWS S3
Our ClickHouse has no storage attached. It only works with remote datasets.
Let run our first Space query and fetch some Taxi rides from AWS S3
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 JSONCompact
Congratulations! You're now a ClickHouse Space Cadet 🏆🚀
Deta ClickHouse + Deta S3
Your ClickHouse Space ship offers a virtual S3 endpoint on top of Deta Drive
To use it, just deploy a personal s3rver-deta instance using Discovery
Space will generate a unique secure URL for your personal S3 Drive endpoint
INSERT some data into our Space bucket
INSERT INTO FUNCTION s3('https://S3RVER-SPACE-URL.deta.app/test/test.csv.gz', 'CSV', 'name String, value UInt32', 'gzip') VALUES ('one', 1), ('two', 2),('three', 3), ('four', 4) SETTINGS s3_truncate_on_insert=1
Query id: 523a1130-a1fa-4212-aaac-f24486d2e71e Ok. 4 rows in set. Elapsed: 0.121 sec.
SELECT our data back - note authentication is handled by the edge
SELECT * FROM s3('https://S3RVER-SPACE-URL.deta.app/test/test.csv.gz', 'CSV', 'name String, value UInt32') FORMAT JSONCompact
All of your database data is exclusively stored in your own Deta Space Drive
Deta ClickHouse + URLENG
Our S3 Space bucket is secure and only accessible to our Space instance.
To share data between instances or with remote ClickHouse servers, we can switch to the public Serverless URL Engine (also running on Deta Space) using a Secret URL
Replace SUPERSECRETURL with your own random string
INSERT INTO FUNCTION url('https://urleng.com/SUPERSECRETURL', JSONEachRow, 'key String, value UInt64') VALUES ('hello', 1)
SELECT * FROM url('https://urleng.com/SUPERSECRETURL', JSONEachRow)
That's it. Now your ClickHouse Space instances can exchange persistent data.
Next: Beyond ClickHouse
The experiment continues and there's so much more coming!
In our next article we'll showcase InfluDB Flux running on Deta Space at ludicrous speeds, with APIs compatible with Grafana and ready to run in seconds ... for free!
The Flux and ClickHouse Space containers can communicate and exchange data!
Just imagine the possibilities for this little big serverless chain ...
This is not an image. It's a real Clickhouse serverless shell.
This ClickHouse does not exist. Unless you're talking to it. From Space!
Lots of work is up next in order to make this POC faster, lighter and stronger!
The question is: What will you build with it? 🚀 🚀 🚀
Thanks to the Deta Team for their dedication and for their support!
Enjoy and Share your Comments! 👋
Did you find this article valuable?
Support qryn by becoming a sponsor. Any amount is appreciated!