Using SeaweedFS

This page isn’t applicable to ClickHouse Cloud. The feature documented here isn’t available in ClickHouse Cloud services. See the ClickHouse Cloud Compatibility guide for more information.

ClickHouse’s s3 table function and S3 disk type are compatible with SeaweedFS, an open-source distributed object store with an S3-compatible gateway. SeaweedFS serves path-style requests natively, so a self-hosted store works without wildcard DNS. SeaweedFS also supports Iceberg tables: its table buckets store the table data as Parquet files, and the built-in Iceberg REST catalog serves the table metadata - see the SeaweedFS catalog guide for querying them through the same endpoint. Use SeaweedFS 4.42 or newer. Earlier versions can delete an object whose write commits while its parent folder is being removed for being empty, which surfaces as Object ... suddenly disappeared right after a successful write.

Running SeaweedFS locally

For a local test setup, create a file s3config.json with the S3 credentials:

{
  "identities": [
    {
      "name": "analyst",
      "credentials": [
        {
          "accessKey": "your_access_key_id",
          "secretKey": "your_secret_access_key"
        }
      ],
      "actions": ["Admin", "Read", "Write", "List", "Tagging"]
    }
  ]
}

Then start the whole SeaweedFS stack in one container - the -bucket flag creates the bucket on startup:

docker run -d --name seaweedfs -p 8333:8333 \
  -v "$(pwd)/s3config.json:/etc/seaweedfs/s3config.json" \
  chrislusf/seaweedfs:latest \
  mini -dir=/data -s3.config=/etc/seaweedfs/s3config.json -bucket=clickhouse

The S3 endpoint listens on port 8333; wait for it to respond before continuing:

until curl -s -o /dev/null http://localhost:8333; do sleep 1; done

More buckets can be created at any time with echo "s3.bucket.create -name mybucket" | docker exec -i seaweedfs weed shell. By default a write is acknowledged once it is handed to the operating system. To fsync every write to disk before acknowledging it, enable fsync on the bucket:

echo "fs.configure -locationPrefix=/buckets/clickhouse/ -fsync -apply" | \
  docker exec -i seaweedfs weed shell

S3-backed MergeTree

The S3-backed merge tree configuration is compatible with minor changes:

<clickhouse>
    <storage_configuration>
        <disks>
            <s3>
                <type>s3</type>
                <endpoint>http://seaweedfs:8333/clickhouse/tables/</endpoint>
                <access_key_id>your_access_key_id</access_key_id>
                <secret_access_key>your_secret_access_key</secret_access_key>
                <region></region>
                <metadata_path>/var/lib/clickhouse/disks/s3/</metadata_path>
            </s3>
            <s3_cache>
                <type>cache</type>
                <disk>s3</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>
                    </main>
                </volumes>
            </s3_main>
        </policies>
    </storage_configuration>
</clickhouse>

The endpoint contains the bucket name (clickhouse) followed by a path prefix (tables/) for the table data. SeaweedFS does not require a region, so the tag can stay empty. Replace seaweedfs with the host running the S3 gateway.

Tables then place their data on SeaweedFS through the storage policy:

CREATE TABLE trips (id UInt64, rider String, fare Float64)
ENGINE = MergeTree
ORDER BY id
SETTINGS storage_policy = 's3_main';

To keep a local cache of frequently read data, create the table with SETTINGS disk = 's3_cache' instead - the cache disk defined above wraps the S3 disk.

The s3 table function

The s3 table function reads and writes objects against the same endpoint:

INSERT INTO FUNCTION s3(
    'http://seaweedfs:8333/clickhouse/sample/trips.parquet',
    'your_access_key_id',
    'your_secret_access_key',
    'Parquet'
)
SELECT number AS id, concat('rider_', toString(number % 10)) AS rider, number * 1.5 AS fare
FROM numbers(1000);

SELECT count()
FROM s3(
    'http://seaweedfs:8333/clickhouse/sample/*.parquet',
    'your_access_key_id',
    'your_secret_access_key',
    'Parquet'
);

Glob patterns work for reading multiple objects.

Backup and restore

BACKUP and RESTORE accept a SeaweedFS endpoint as the S3 destination:

BACKUP TABLE trips
TO S3('http://seaweedfs:8333/clickhouse/backups/trips1', 'your_access_key_id', 'your_secret_access_key');

--- DROP TABLE trips;

RESTORE TABLE trips
FROM S3('http://seaweedfs:8333/clickhouse/backups/trips1', 'your_access_key_id', 'your_secret_access_key');

Data sources

Data visualization

Data ingestion

Data integrations

SQL-clients

Tools

Running SeaweedFS locally

S3-backed MergeTree

The s3 table function

Backup and restore

​Running SeaweedFS locally

​S3-backed MergeTree

​The s3 table function

​Backup and restore

Running SeaweedFS locally

S3-backed MergeTree

The s3 table function

Backup and restore