๐Ÿ—„๏ธ Cloudflare R2 + ClickHouse

๐Ÿ—„๏ธ Cloudflare R2 + ClickHouse

ยท

2 min read

Play this article

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!


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

Did you find this article valuable?

Support qryn by becoming a sponsor. Any amount is appreciated!

ย