Integrating S3 with ClickHouse

### Handling replication[​](#handling-replication "Direct link to Handling replication")

Replication with S3 disks can be accomplished by using the `ReplicatedMergeTree` table engine. See the [replicating a single shard across two AWS regions using S3 Object Storage](#s3-multi-region) guide for details.

### Read & writes[​](#read--writes "Direct link to Read & writes")

The following notes cover the implementation of S3 interactions with ClickHouse. Whilst generally only informative, it may help the readers when [Optimizing for Performance](#s3-optimizing-performance):

- By default, the maximum number of query processing threads used by any stage of the query processing pipeline is equal to the number of cores. Some stages are more parallelizable than others, so this value provides an upper bound. Multiple query stages may execute at once since data is streamed from the disk. The exact number of threads used for a query may thus exceed this. Modify through the setting [max\_threads](/docs/operations/settings/settings#max_threads).
- Reads on S3 are asynchronous by default. This behavior is determined by setting `remote_filesystem_read_method`, set to the value `threadpool` by default. When serving a request, ClickHouse reads granules in stripes. Each of these stripes potentially contain many columns. A thread will read the columns for their granules one by one. Rather than doing this synchronously, a prefetch is made for all columns before waiting for the data. This offers significant performance improvements over synchronous waits on each column. You won't need to change this setting in most cases - see [Optimizing for Performance](#s3-optimizing-performance).
- Writes are performed in parallel, with a maximum of 100 concurrent file writing threads. `max_insert_delayed_streams_for_parallel_write`, which has a default value of 1000, controls the number of S3 blobs written in parallel. Since a buffer is required for each file being written (~1MB), this effectively limits the memory consumption of an INSERT. It may be appropriate to lower this value in low server memory scenarios.

## Use S3 object storage as a ClickHouse disk[​](#configuring-s3-for-clickhouse-use "Direct link to Use S3 object storage as a ClickHouse disk")

If you need step-by-step instructions to create buckets and an IAM role, please refer to ["How to create an AWS IAM user and S3 bucket"](/docs/integrations/s3/creating-iam-user-and-s3-bucket)

### Configure ClickHouse to use the S3 bucket as a disk[​](#configure-clickhouse-to-use-the-s3-bucket-as-a-disk "Direct link to Configure ClickHouse to use the S3 bucket as a disk")

The following example is based on a Linux Deb package installed as a service with default ClickHouse directories.

1. Create a new file in the ClickHouse `config.d` directory to store the storage configuration.

```
vim /etc/clickhouse-server/config.d/storage_config.xml
```

2. Add the following for storage configuration; substituting the bucket path, access key and secret keys from earlier steps

```
<clickhouse>
  <storage_configuration>
    <disks>
      <s3_disk>
        <type>s3</type>
        <endpoint>https://mars-doc-test.s3.amazonaws.com/clickhouse3/</endpoint>
        <access_key_id>ABC123</access_key_id>
        <secret_access_key>Abc+123</secret_access_key>
        <metadata_path>/var/lib/clickhouse/disks/s3_disk/</metadata_path>
      </s3_disk>
      <s3_cache>
        <type>cache</type>
        <disk>s3_disk</disk>
        <path>/var/lib/clickhouse/disks/s3_cache/</path>
        <max_size>10Gi</max_size>
      </s3_cache>
    </disks>
    <policies>
      <s3_main>
        <volumes>
          <main>
            <disk>s3_disk</disk>
          </main>
        </volumes>
      </s3_main>
    </policies>
  </storage_configuration>
</clickhouse>
```

Note

The tags `s3_disk` and `s3_cache` within the `<disks>` tag are arbitrary labels. These can be set to something else but the same label must be used in the `<disk>` tab under the `<policies>` tab to reference the disk.
The `<S3_main>` tag is also arbitrary and is the name of the policy which will be used as the identifier storage target when creating resources in ClickHouse.

The configuration shown above is for ClickHouse version 22.8 or higher, if you're using an older version please see the [storing data](/docs/operations/storing-data#using-local-cache) docs.

For more information about using S3:
Integrations Guide: [S3 Backed MergeTree](#s3-backed-mergetree)

3. Update the owner of the file to the `clickhouse` user and group

```
chown clickhouse:clickhouse /etc/clickhouse-server/config.d/storage_config.xml
```

4. Restart the ClickHouse instance to have the changes take effect.

```
service clickhouse-server restart
```

### Testing[​](#testing "Direct link to Testing")

1. Log in with the ClickHouse client, something like the following

```
clickhouse-client --user default --password ClickHouse123!
```

2. Create a table specifying the new S3 storage policy

```
CREATE TABLE s3_table1
           (
               `id` UInt64,
               `column1` String
           )
           ENGINE = MergeTree
           ORDER BY id
           SETTINGS storage_policy = 's3_main';
```

3. Show that the table was created with the correct policy

```
SHOW CREATE TABLE s3_table1;
```

```
┌─statement────────────────────────────────────────────────────
│ CREATE TABLE default.s3_table1
(
    `id` UInt64,
    `column1` String
)
ENGINE = MergeTree
ORDER BY id
SETTINGS storage_policy = 's3_main', index_granularity = 8192
└──────────────────────────────────────────────────────────────
```

4. Insert test rows into the table

```
INSERT INTO s3_table1
           (id, column1)
           VALUES
           (1, 'abc'),
           (2, 'xyz');
```

```
INSERT INTO s3_table1 (id, column1) FORMAT Values

Query id: 0265dd92-3890-4d56-9d12-71d4038b85d5

Ok.

2 rows in set. Elapsed: 0.337 sec.
```

5. View the rows

```
SELECT * FROM s3_table1;
```

```
┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ xyz     │
└────┴─────────┘

2 rows in set. Elapsed: 0.284 sec.
```

6. In the AWS console, navigate to the buckets, and select the new one and the folder.
   You should see something like the following:

## Replicating a single shard across two AWS regions using S3 Object Storage[​](#s3-multi-region "Direct link to Replicating a single shard across two AWS regions using S3 Object Storage")

Tip

Object storage is used by default in ClickHouse Cloud, you don't need to follow this procedure if you're running in ClickHouse Cloud.

### Plan the deployment[​](#plan-the-deployment "Direct link to Plan the deployment")

This tutorial is based on deploying two ClickHouse Server nodes and three ClickHouse Keeper nodes in AWS EC2. The data store for the ClickHouse servers is S3. Two AWS regions, with a ClickHouse Server and an S3 Bucket in each region, are used in order to support disaster recovery.

ClickHouse tables are replicated across the two servers, and therefore across the two regions.

### Install software[​](#install-software "Direct link to Install software")

#### ClickHouse server nodes[​](#clickhouse-server-nodes "Direct link to ClickHouse server nodes")

Refer to the [installation instructions](/docs/install) when performing the deployment steps on the ClickHouse server nodes.

#### Deploy ClickHouse[​](#deploy-clickhouse "Direct link to Deploy ClickHouse")

Deploy ClickHouse on two hosts, in the sample configurations these are named `chnode1`, `chnode2`.

Place `chnode1` in one AWS region, and `chnode2` in a second.

#### Deploy ClickHouse Keeper[​](#deploy-clickhouse-keeper "Direct link to Deploy ClickHouse Keeper")

Deploy ClickHouse Keeper on three hosts, in the sample configurations these are named `keepernode1`, `keepernode2`, and `keepernode3`. `keepernode1` can be deployed in the same region as `chnode1`, `keepernode2` with `chnode2`, and `keepernode3` in either region but a different availability zone from the ClickHouse node in that region.

Refer to the [installation instructions](/docs/install) when performing the deployment steps on the ClickHouse Keeper nodes.