Cloudflare R2 + ClickHouse
Cloudflare R2 is an S3 compatible distributed object storage offering no charges for egress bandwidth, 10GB of storage and 1M requests per month as free tier. This example shows how to use R2 buckets with the ClickHouse S3 Table engine.
๐ Using the ClickHouse S3 Table Engine, qryn can leverage R2 as (cold) storage
Instructions
CloudFlare
- Sign in to your CloudFlare account and create an R2 bucket, ie:
https://abcdefghi.r2.cloudflarestorage.com/somebucket
- Generate a token pair with write permissions to the bucket, ie:
Access Key ID = XXXXXXXXXXXXXXXXXXXXXXXX
Secret Access Key = YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
ClickHouse
- Configure an S3 table in ClickHouse using
CSV/gzip
format
CREATE TABLE r2_cloudflare (name String, value UInt32)
ENGINE=S3('https://abcdefghi.r2.cloudflarestorage.com/somebucket/sometable.csv', 'XXXXXXXXXXXXXXXXXXXXXXXX', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 'CSV', 'gzip')
SETTINGS max_threads=8, max_insert_threads=8, input_format_parallel_parsing=0, input_format_with_names_use_header=0;
INSERT
&SELECT
data using the R2 table
INSERT INTO r2_cloudflare VALUES ('one', 1), ('two', 2), ('three', 3);
SELECT * FROM r2_cloudflare LIMIT 2;
R2 Storage for qryn
Manual queries are fun - next let's configure R2 as a clickhouse storage disk
<yandex>
<storage_configuration>
<disks>
<r2>
<type>s3</type>
<endpoint>https://abcdefghi.r2.cloudflarestorage.com/somebucket/fakekey</endpoint>
<access_key_id>XXXXXXXXXXXXXXXXXXXXXXXX</access_key_id>
<secret_access_key>YYYYYYYYYYYYYYYYYYYY</secret_access_key>
</r2>
</disks>
<policies>
<external>
<volumes>
<s3>
<disk>r2</disk>
</s3>
</volumes>
</external>
</policies>
</storage_configuration>
</yandex>
That's it! Note performance may vary based on network and data conditions
If you have feedback or use R2 with ClickHouse and qryn, please consider sharing your test results with our community!
Reference Links
Interested in this subject? Check out the following links for further information
Updates:
๐ก
Thanks to Dan Jenkins for noticing this and providing an updated example: https://github.com/ClickHouse/ClickHouse/issues/31093#issuecomment-1465077479
ย