clickhouse.guru
Tutorials· intermediate

ClickHouse and S3: table functions, S3Queue and S3 disks

The ways to combine ClickHouse with S3-compatible storage: ad-hoc queries, loading and exporting, continuous ingestion with S3Queue, refreshable views, and S3 as a storage disk.

Object storage and ClickHouse fit together in four different ways, and it helps to keep them apart because each has different guarantees and costs: S3 as an external table, S3 as a source of data, S3 as a destination, and S3 as the disk that holds a MergeTree table’s parts. The statements here need an S3 bucket (or any S3-compatible store); they were parsed with clickhouse format on 26.4 and the S3 and S3Queue table definitions were checked against the server, but no objects were read. Bucket names and keys are placeholders.

Table function or table engine

Both read S3. The difference is how long the definition lives.

  • A table function such as s3(...) is ad hoc: you write it in a query, and nothing is stored.
  • A table engine such as ENGINE = S3(...) creates a named table whose data lives in the object. It behaves like a table in queries.
-- ad hoc
SELECT * FROM s3('https://my-bucket.s3.eu-central-1.amazonaws.com/reference/devices.parquet', 'Parquet') LIMIT 5;

-- named
CREATE TABLE devices (id UInt64, name String)
ENGINE = S3('https://my-bucket.s3.eu-central-1.amazonaws.com/reference/devices.parquet', 'Parquet');

The format decides how painful it is

ClickHouse can read almost any format from S3, but they are not equal. Parquet (and ORC, Arrow, Avro) carries its own schema with types, so ClickHouse can infer the table structure and you skip the column list. CSV and JSON carry no types. You either let the engine guess from a sample, which can guess wrong across files, or you pass an explicit structure string:

SELECT count()
FROM s3('https://my-bucket.s3.eu-central-1.amazonaws.com/export/2026/*.csv.gz', 'CSV',
        'event_time DateTime, user_id UInt64, event_type String');

Compressed files (gz, zstd and so on) are detected from the extension.

Credentials go in the function call (s3(url, '<access_key_id>', '<secret_access_key>', format, structure)), in named collections defined on the server, or come from the environment (IAM role). Prefer named collections or roles. Never paste keys into queries that end up in logs or query_log.

S3 as a side store

For a dimension that someone else maintains and updates as a file, you can join straight against the object instead of copying it into a table:

SELECT e.event_time, d.name
FROM analytics.events AS e
INNER JOIN s3('https://my-bucket.s3.eu-central-1.amazonaws.com/reference/devices.parquet', 'Parquet') AS d
    ON e.user_id = d.id;

The file is read on every query, so this suits small, rarely queried reference data. For anything you query often, load it into a table or into a dictionary.

S3 as a source

Loading is a normal INSERT ... SELECT:

INSERT INTO analytics.events
SELECT * FROM s3('https://my-bucket.s3.eu-central-1.amazonaws.com/export/events/*.parquet', 'Parquet');

The path accepts globs: * for any characters, ? for one, {a,b} for alternatives and {1..10} for ranges. One server reads the matched files with several threads. To use the whole cluster, s3Cluster splits the file list across the nodes of a cluster, so the ingest rate is the sum of the nodes:

INSERT INTO analytics.events
SELECT * FROM s3Cluster('my_cluster',
    'https://my-bucket.s3.eu-central-1.amazonaws.com/export/{2026,2027}/*.csv.gz',
    'CSV',
    'event_time DateTime, user_id UInt64, event_type LowCardinality(String), url String, duration_ms UInt32, country LowCardinality(String)');

Keep the usual insert rules in mind (batching): this produces blocks sized by max_insert_block_size, which is fine, but a destination with a bad partition key still turns into too many parts.

Continuous ingestion

If files keep arriving in a bucket, there are two options.

S3Queue is an engine that watches a path, processes each new object once and hands the rows to materialized views, in the same three-object shape as the Kafka pipeline:

CREATE TABLE events_s3queue
(
    event_time DateTime, user_id UInt64, event_type String,
    url String, duration_ms UInt32, country String
)
ENGINE = S3Queue('https://my-bucket.s3.eu-central-1.amazonaws.com/incoming/*.json.gz', 'JSONEachRow')
SETTINGS mode = 'unordered', after_processing = 'keep';

CREATE MATERIALIZED VIEW events_s3queue_mv TO analytics.events AS
SELECT event_time, user_id, event_type, url, duration_ms, country
FROM events_s3queue;

Facts to know:

  • mode must be set explicitly. unordered tracks every processed object in Keeper and accepts files in any order. ordered only remembers the highest file name processed, which is cheaper but only right when names increase over time (for example timestamped names). A third, exclusive, uses no Keeper tracking and is for single-consumer setups.
  • State lives in Keeper, so the server needs a Keeper configuration. Creating an S3Queue table on a server without one fails with There is no Zookeeper configuration in server config.
  • Delivery is at least once. Retries and Keeper session losses can produce duplicates, and rows can be lost if the server dies before the data is flushed to disk. Design the target table to tolerate duplicates.
  • system.s3queue_log (when enabled) and system.s3_queue_metadata show which files were processed or failed.
  • The engine was experimental in early releases and is now generally considered production-ready. Check the release notes for your version.

Refreshable materialized views are the other option. They re-run a query on a schedule and replace (or append to) the target table:

CREATE MATERIALIZED VIEW daily_counts_mv
REFRESH EVERY 1 HOUR
TO daily_counts
AS SELECT toDate(event_time) AS day, count() AS events
FROM analytics.events
GROUP BY day;

They suit periodic snapshots, such as re-reading a reference file or aggregating a bucket’s current contents, rather than file-by-file ingestion. They have also left the experimental stage; see system.view_refreshes for state.

S3 as a destination

The reverse also works. Exporting is an INSERT INTO FUNCTION:

INSERT INTO FUNCTION s3('https://my-bucket.s3.eu-central-1.amazonaws.com/export/events_2026_03.parquet', 'Parquet')
SELECT * FROM analytics.events
WHERE event_time >= '2026-03-01' AND event_time < '2026-04-01';

Parquet is the usual choice for exports meant for other tools. A single statement writes one object unless you use a partitioned write (PARTITION BY on the function with {_partition_id} in the path).

S3 as a disk

Everything so far treats S3 as a file source. A different feature uses it as the storage for MergeTree parts. The table behaves normally; its parts live in the bucket. Define a disk in the server config and a policy that uses it (the tiered storage article explains policies):

<clickhouse>
  <storage_configuration>
    <disks>
      <s3_disk>
        <type>object_storage</type>
        <object_storage_type>s3</object_storage_type>
        <metadata_type>local</metadata_type>
        <endpoint>https://my-bucket.s3.eu-central-1.amazonaws.com/clickhouse-data/</endpoint>
        <use_environment_credentials>1</use_environment_credentials>
      </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_cached>
        <volumes>
          <main>
            <disk>s3_cache</disk>
          </main>
        </volumes>
      </s3_cached>
    </policies>
  </storage_configuration>
</clickhouse>
CREATE TABLE events_cold (event_time DateTime, user_id UInt64)
ENGINE = MergeTree
ORDER BY event_time
SETTINGS storage_policy = 's3_cached';

The older spelling <type>s3</type> with access_key_id / secret_access_key elements still exists; the form above is what the current documentation recommends. Avoid keys in the config when you can, and use an instance role (use_environment_credentials). Things to plan for:

  • Local cache. Every read of a cold part is a network request. The cache disk keeps recently used data blocks on local disk and is what makes this usable. Size it for your working set.
  • Metadata. With metadata_type = local, each server keeps its own metadata about which objects belong to which part, in its data directory. With ReplicatedMergeTree, each replica therefore writes its own copy of the data to the bucket, so object storage does not reduce the number of copies. Give every server its own endpoint path and never point two independent servers at the same one.
  • A policy is not a backup. The bucket is only meaningful with the server’s metadata.
  • Pair with TTL moves. The common pattern is a policy with a local hot volume and an S3 cold volume, driven by TTL ... TO VOLUME 'cold'.
  • Try it out first. This is an advanced setup. Test failure handling and restore on a throwaway cluster before using it for real data.

Switching an existing table to an S3-backed policy works exactly as in the tiered storage article: ALTER TABLE ... MODIFY SETTING storage_policy = '...', with the new policy containing the old volumes.

Which one to pick

You want Use
One-off look at files, or a small reference file s3() table function
A named read-only view of an object ENGINE = S3
One-off or scheduled bulk load INSERT ... SELECT FROM s3/s3Cluster
Load every new file once S3Queue + materialized view
Periodic snapshot of a bucket Refreshable materialized view
Export INSERT INTO FUNCTION s3
A big table with cheap cold storage S3 disk behind a storage policy

s3object-storageingestion

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.