# 🗄️ Cloudflare R2 + ClickHouse

# Cloudflare R2 + ClickHouse

[Cloudflare R2](https://developers.cloudflare.com/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](https://clickhouse.com/docs/en/engines/table-engines/integrations/s3/), [**qryn**](https://qryn.dev) can leverage R2 as *(cold)* storage

## Instructions

#### CloudFlare

* Sign in to your CloudFlare account and create an R2 bucket, ie:
    

```bash
https://abcdefghi.r2.cloudflarestorage.com/somebucket
```

* Generate a token pair with write permissions to the bucket, ie:
    

```bash
Access Key ID = XXXXXXXXXXXXXXXXXXXXXXXX
Secret Access Key = YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
```

#### ClickHouse

* Configure an S3 table in ClickHouse using `CSV/gzip` format
    

```sql
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
    

```sql
INSERT INTO r2_cloudflare VALUES ('one', 1), ('two', 2), ('three', 3);
```

```sql
SELECT * FROM r2_cloudflare LIMIT 2;
```

### R2 Storage for qryn

Manual queries are fun - next let's configure R2 as a clickhouse *storage disk*

```xml
<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](https://github.com/metrico/qryn/issues)!

---

#### Reference Links

*Interested in this subject?* Check out the following links for further information

* https://clickhouse.com/docs/en/engines/table-engines/integrations/s3/
    
* https://altinity.com/blog/tips-for-high-performance-clickhouse-clusters-with-s3-object-storage
    

### Updates:

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">Thanks to <a target="_blank" rel="noopener noreferrer nofollow" href="https://www.linkedin.com/in/jenkinsdaniel/" style="pointer-events: none">Dan Jenkins</a> for noticing this and providing an updated example: <a target="_blank" rel="noopener noreferrer nofollow" href="https://github.com/ClickHouse/ClickHouse/issues/31093#issuecomment-1465077479" style="pointer-events: none">https://github.com/ClickHouse/ClickHouse/issues/31093#issuecomment-1465077479</a></div>
</div>