Object-storage tables

ClickHouse can read and write data living in object storage (S3, GCS, Azure Blob Storage, HDFS) without first ingesting it. Three layers cooperate:

1. Table functions (s3(...), gcs(...), azureBlobStorage(...), hdfs(...), url(...)) — ad-hoc queries.
2. Engine-backed tables (ENGINE = S3 / AzureBlobStorage / HDFS / URL / Iceberg / DeltaLake / Hudi / Hive) — persistent table definitions.
3. Lakehouse engines (Iceberg, DeltaLake, Hudi, Hive) — understand table-format manifests and surface the schema accordingly.

Source: src/Storages/ObjectStorage/, src/Storages/ObjectStorage/StorageObjectStorage.cpp, src/Storages/ObjectStorage/HDFS/, src/Disks/DiskObjectStorage/ObjectStorages/, and table-function definitions under src/TableFunctions/.

Table functions

SELECT * FROM s3(
    'https://bucket.s3.amazonaws.com/path/*.parquet',
    'access_key', 'secret_key',
    'Parquet'
) LIMIT 10;

Glob support (*, ?, {...}, [...]) lets you scan thousands of files. The format is detected from the extension or specified explicitly. Schema inference (see Formats) makes CREATE TABLE t AS s3(...) work.

Engine-backed tables

CREATE TABLE events_s3
ENGINE = S3('https://bucket.s3.amazonaws.com/events/*.parquet', 'Parquet');

Behaves like a regular table for SELECT, supports INSERT for some formats, and integrates with named collections so you don't repeat credentials.

*Cluster variants (s3Cluster, hdfsCluster, azureBlobStorageCluster, urlCluster) fan out the file globbing across cluster nodes.

Caching and metadata

For frequent reads from a remote bucket:

• The filesystem cache (see IO and disks) sits in front of the object store.
• The schema cache caches inferred schemas keyed by URL+format.
• The list cache caches LIST results so repeated globs don't re-list the bucket.

Settings: enable_filesystem_cache, filesystem_cache_size, schema_inference_cache_*, s3_list_object_keys_size.

Lakehouse formats

src/Storages/ObjectStorage/DataLakes/ is the umbrella for table-format engines. Each engine reads its manifest format and produces a snapshot view backed by underlying Parquet/Avro/ORC files.

Engine	Source	Notes
Iceberg	IcebergMetadata.cpp, IcebergFormatVersion.cpp	Iceberg v1 + v2 read; partition pruning, schema evolution, snapshot selection (SETTINGS iceberg_snapshot_id = ...).
DeltaLake	DeltaLakeMetadata.cpp	Reads Delta _delta_log/; supports add/remove transactions and schema evolution.
Hudi	HudiMetadata.cpp	Hudi copy-on-write read.
Hive	Hive/	Hive Metastore → external table. Pulls partitions from HMS Thrift.
Object Storage Queue	S3Queue/, AzureQueue/, IcebergQueue/	Streaming queue mode: read each new object exactly once, with Keeper-coordinated state.

Iceberg and DeltaLake both support time travel (SETTINGS iceberg_timestamp_ms = ..., SETTINGS delta_lake_version = ...) and partition pruning during planning.

Streaming queue mode

S3Queue (and the Azure / Iceberg variants) lets a server tail an object-storage prefix:

CREATE TABLE my_queue
ENGINE = S3Queue('https://bucket.s3.amazonaws.com/incoming/*.json', 'JSONEachRow')
SETTINGS mode = 'unordered', keeper_path = '/clickhouse/queues/my_queue';

A materialized view typically sinks the queue's output into a regular MergeTree:

CREATE MATERIALIZED VIEW mv TO events AS SELECT ... FROM my_queue;

State (which files have been processed) is in Keeper. Multiple replicas coordinate to process each file exactly once.

Authentication

• Static credentials ((access_key, secret_key) in the table definition).
• Named collections (recommended) — CREATE NAMED COLLECTION.
• IAM role / instance metadata — <s3><use_environment_credentials>true</use_environment_credentials>.
• IAM web identity (Kubernetes / EKS).

<s3> server-config block configures defaults: endpoint overrides, header overrides, region, retry policy, throttling, the SSL backend.

Cluster patterns

• s3Cluster(cluster, url, ...) — fan out file scanning to all nodes of cluster.
• INSERT INTO TABLE FUNCTION s3(...) SELECT ... FROM events — write to S3 from a single node.
• INSERT INTO TABLE FUNCTION s3Cluster(...) SELECT ... — parallel write across the cluster.

Diagnostics

• system.s3_queue — queue state.
• system.s3_queue_log — queue history.
• system.filesystem_cache — what's cached locally.
• system.iceberg_history, system.iceberg_metadata_log — Iceberg-specific.
• system.detached_tables — tables that failed to attach (e.g. unreachable bucket).

Related pages

• IO and disks — disks, caches, throttlers.
• Formats — Parquet, Arrow, Avro, JSON.
• Other engines — StorageS3, StorageURL, *Cluster.