ClickHouse S3 integration: querying and loading files

How ClickHouse reads and writes S3 files with the S3 table engine

Files already land in an S3 or S3-compatible bucket, and the team wants to query them from a self-hosted ClickHouse cluster without building a separate pipeline first. The documented methods are the S3 table engine, which creates a named table over a set of objects, and the s3 table function, which reads the same objects in a single query without creating a table. Both accept a URL that can use wildcards. The wildcard path (*, **, ?, {abc,def} and {N..M}) is supported for reads, and the engine runs in read-only mode for those paths, so a query cannot write back into the bucket through them. Writes to S3 are a separate capability with its own parameters and should be tested on a non-production bucket before anyone relies on them. This page covers two decisions: whether to query files in place, and when to load them into a MergeTree table instead. The S3 table engine documentation lists the exact syntax and option names for your ClickHouse version.

Querying in place suits files that are read occasionally, that are owned by another team or pipeline, or that you want to inspect before deciding on a schema. Each query scans the matching objects again, so the cost is paid in network reads and object requests every time the query runs. Compression is detected from the file extension by default, and the compression parameter can be set explicitly when extensions are missing or misleading. Check that the file format, column names and types in the files match what your table definition or query expects, because a mismatch shows up as a query error or as silently wrong values, depending on the format. Confirm with your storage vendor how their S3-compatible endpoint handles listing large prefixes and range reads, since those affect latency more than the ClickHouse settings do.

Loading into MergeTree is the better choice when the same data is queried repeatedly, when dashboards depend on it, or when you need ordering keys, partitions, skip indexes or retention rules that only a MergeTree table provides. A common pattern is a scheduled INSERT ... SELECT from the s3 table function into a MergeTree table, which keeps the source files as the system of record. Plan the load window, the duplicate handling if a job reruns, and the ownership of the target table before the first load. Replicated tables also need the cluster design covered in the high availability section, so decide early whether the loaded data must survive a node loss.

What a ClickHouse S3 integration needs before the first query

Most S3 problems in ClickHouse trace back to decisions made before the first query runs: which release line is in production, how the bucket is laid out, who owns the credentials, and whether the cluster can reach the endpoint at all. The cards below cover those decisions in the order a team usually needs to settle them.

Pin the ClickHouse release line

Choose a release line that is still receiving security updates before you write table definitions. The security policy currently lists 26.9, 26.8, 26.7 and 26.3 as supported, and everything else is unsupported. Confirm how the S3 table engine and its options behave in the exact version you run, because read paths, settings and defaults can change between releases. Test your wildcards and compression settings on that version first.

Design the bucket and object layout

Decide how files are named and partitioned by date, source, or tenant before any query uses a wildcard. Predictable prefixes let you match exactly the files you need, while mixed layouts force broad scans and make the schema harder to evolve. Confirm that the file formats (Parquet, CSV, JSON) are consistent within each prefix, and that compression suffixes such as .gz or .zst match the actual encoding so auto-detection works.

Assign IAM roles or access keys

Settle who owns the credentials and how they are scoped. Read-only table engines need only read permissions on the prefixes they query, while loading or writing needs write access on a separate prefix. Ask the storage team whether the cluster should use an IAM role, an access key, or a key held in named collections, and who rotates it. Avoid giving the ClickHouse service account bucket-wide rights that a single misconfigured query could exploit.

Map network paths to the endpoint

Trace every route from each ClickHouse node to the S3 endpoint, including DNS resolution, egress firewall rules, and proxies. For S3-compatible storage such as Ceph, confirm whether a private endpoint exists inside your network and whether the certificate chain is trusted by every node. Traffic that leaves your network unnecessarily creates both cost and exposure, so verify the path from a single node before rolling the configuration out.

Build a representative sample file set

Assemble a test set that matches production in file count, file size, schema drift, compression, and malformed rows. Small clean samples hide problems that appear at full volume, such as schema differences between producers or files that are too small to read efficiently. Keep a copy of this set in a separate bucket so tests do not touch live data, and record the expected row counts so every query can be checked against them.

Confirm the storage class for reads

Check the storage class of every object before you point a table at it. The S3 engine's storage_class_name option supports only classes that allow immediate retrieval, such as STANDARD, STANDARD_IA, INTELLIGENT_TIERING, and GLACIER_IR. Archival classes such as GLACIER and DEEP_ARCHIVE are not supported, so files that lifecycle rules have moved there must be restored first. Ask your storage team to document which lifecycle transitions apply to the prefixes you plan to query.

Configuring ClickHouse S3 access in the right order

This sequence proves each layer of the S3 path works, from network reach to storage identity to ClickHouse grants, before any wildcard reads touch a full dataset. Each step ends with a check you can record, so a failure points to one layer rather than the whole chain.

Confirm network reach to the S3 endpoint
The network or platform team confirms that every ClickHouse node can open an HTTPS connection to the S3 or S3-compatible endpoint on the required port, including any proxy, DNS entry or private endpoint your environment uses. Run the test from each node, not only from a workstation, because firewall rules often differ between hosts. The step is complete when a plain request to the endpoint returns an expected HTTP response from every node. If your endpoint is a private storage service, confirm the TLS certificate chain is trusted by the ClickHouse hosts before continuing.
Create a dedicated storage identity
The cloud or storage administrator creates a dedicated access identity for ClickHouse, separate from personal accounts and from any identity used by other applications. The policy grants read access only to the bucket prefixes the analytics team needs, and write access only where you have agreed that ClickHouse will export data. Ask the vendor or cloud provider to confirm the exact action names your platform requires for listing and reading objects, since they differ between providers. The step is complete when a listing of the allowed prefix succeeds with this identity and a listing of a prefix outside the policy is denied.
Store credentials outside default configuration
The platform team stores the access key and secret in a named collection or a configuration file that only the ClickHouse service account can read, rather than in SQL statements that land in query logs. Confirm with your own security standards whether credentials should come from a secrets manager and be rotated on a schedule. Verify that the credentials do not appear in server logs or in the output of queries against system tables after you create a test object. The step is complete when a test query runs with the stored credentials and the secret is absent from logs you sampled.
Create a non-default ClickHouse user with S3 grants
The ClickHouse administrator creates a named user with the minimum grants needed to read the S3 source, and restricts the default user, which starts with all permissions. Grant the S3 source privilege only on the databases and tables that hold the external definitions, and assign the user to a role so that future users can inherit the same rights. Check that the new user cannot create tables or read other sources beyond what the plan lists. The step is complete when the new user can run the intended read and the same user receives an access denied error for a query outside its grants.
Define the table and read one file
The data engineer defines a table with the S3 table engine, or runs an s3 table function query against a single known object, and sets the format and compression explicitly for the first test. Choose one representative file, such as one Parquet or CSV object with known row count, and avoid wildcards at this stage. Compression is auto-detected from the file extension by default, so set it explicitly if the extension does not match the content. The step is complete when the row count and a sample of column values match the source file, which you can verify by computing the same count in the source system.
Verify the object listing before widening
Before you use a wildcard pattern, the data engineer checks which objects the pattern matches by running a count or a selection of file names against the prefix. Compare the returned object names with the expected list from the storage owner, and look for unexpected folders, partial uploads or files in a different format that the pattern might pick up. Wildcards supported in read-only mode include *, **, ?, {abc,def} and {N..M}, so test each pattern you intend to use. The step is complete when the matched set equals the agreed list, with no extra objects.
Widen to wildcards and reconcile counts
The data engineer expands the pattern to the full set of files for the agreed prefix and runs a row count and a checksum-style aggregate, such as totals of a numeric column, against counts produced by the source system for the same period. Record the run time and any files that fail to parse, and decide whether bad files are excluded or fixed before a load. Repeat this reconciliation after any change to file naming or schema. The step is complete when row counts and totals match the source within the tolerance your data owner has agreed, and the recorded pattern, grants and credentials are documented for operations.

When to query S3 files in place and when to load MergeTree

Query S3 files in place when the access pattern is exploratory, when you are checking a one-off backfill, or when the data is read rarely enough that copying it into a table would cost more than it saves. The S3 table engine and the s3 table function read Parquet, CSV, JSON and other formats directly from the bucket, so nothing has to be prepared before a question is asked. Load the data into a MergeTree table when the same queries run repeatedly, such as dashboards refreshed through the day, when queries scan large volumes and benefit from a sorting key chosen for those filters, or when the data needs partitions and retention rules that drop old rows on a schedule. Before deciding, confirm with your own query logs how often each dataset is read and how much data a typical query touches, since those two numbers usually settle the question.

Be clear about what the wildcard path can do. Reads through the S3 engine are read-only, so the path and its wildcards describe where ClickHouse looks for files, not where it writes them. Writes go through the table engine or table function built for that purpose, and the write behavior, file naming and overwrite rules should be checked against the version you run before any pipeline depends on them. A table that loads into MergeTree gives you a copy you control, which means its ordering, compression and retention are set by your schema rather than by the layout of files in the bucket.

Keep file reads separate from backups. Querying files in S3 and protecting your cluster are different jobs. Backups to S3-compatible storage are made with BACKUP and RESTORE, which capture tables, partitions or databases from the cluster, and they do not make the bucket a queryable copy of your data. Replication also does not replace a backup, because it does not protect against an accidental delete or a corrupted table. Plan the backup target, retention and restore tests as their own work, and treat any S3 files you query in place as source data whose durability belongs to the bucket owner.

Which wildcards, compression and storage classes to use

Wildcards decide which objects in a bucket the S3 engine or the s3 table function reads. ClickHouse supports asterisk, double asterisk, question mark, brace alternatives and numeric ranges. Each pattern widens or narrows the set of keys that are listed and read, so the path you choose sets the scan size before any query filter applies. Path structure matters more than pattern choice: keys that embed dates or source names let you narrow the match to the partitions you need.

**Asterisk (*)**: matches any characters within one path segment, which keeps a match inside a single folder level.
Double asterisk ()**: matches across nested folders, which is convenient but can list far more objects than intended on a large bucket.
Question mark (?): matches exactly one character, useful for fixed-width numbered file names.
Brace alternatives and numeric ranges ({abc,def} and {N..M}): select named or numbered sets, such as one day or a range of part numbers, and keep the match bounded.
Read-only scope: the wildcard path is used for reads in this mode, so write behavior should be checked separately before relying on a pattern.

Ask your team to list the buckets and prefixes the pipeline will read, and to confirm the pattern for each against a sample of keys before the table goes into production.

How file formats, paths and columns map to ClickHouse types

This table maps each element of an S3 object layout, such as key prefixes, file columns and nested fields, to the ClickHouse side where it must be declared. Read each row as a decision to settle before the first query runs, with the owner and the check that proves the mapping holds. The main takeaway is that most wrong results come from type and path assumptions that nobody wrote down.

Source element	ClickHouse mapping	Who decides	Check that confirms it
Object key prefix	Table path pattern or glob in the S3 engine or s3 function, such as year and month folders	Data owner with the engineer	Count rows per matched prefix and compare with the source system's file manifest
Parquet columns	Declared column names and types in the table or query, matched by name	Engineer	Run DESCRIBE on the function output and compare types with the Parquet schema
CSV columns	Declared types in order, with the header row skipped or mapped by name	Engineer	Load one sample file and compare a few typed values against the raw file
CSV delimiter and quoting	Format settings for the delimiter, quote character and escape rules	Data owner who produced the files	Check that embedded commas and newlines parse into the expected column count
JSON top-level fields	Declared columns for each top-level key, with defaults for missing keys	Engineer	Query for missing or default values per column after a sample load
Nested JSON fields	Flattened columns, or a Nested or Tuple type where the structure is kept	Data owner and engineer together	Compare a sample of nested records against flattened output for dropped or duplicated values
Nullable handling	Nullable wrapper only where the source can omit a value, to avoid storage and query overhead	Data owner	Count nulls per column in the source and in the loaded table
Partition-like prefixes	Filters on virtual columns or path segments so queries read only matching files	Engineer with the analyst	Run an EXPLAIN or check bytes read on a filtered query against an unfiltered one
Schema changes in source files	Agreed process for new or renamed columns before the table definition changes	Data owner	Test a file with the new field against the table and confirm it fails or loads as expected

Securing S3 credentials, bucket access and query users

Querying S3 from ClickHouse creates two access paths: the storage identity ClickHouse uses to reach the bucket, and the ClickHouse users who can trigger those reads and writes. The cards below cover both paths, from the storage side to the query side, so that a leaked key or an over-privileged user cannot reach more data than the workload needs.

Read-only storage identities scoped to prefixes

Create a dedicated storage identity for ClickHouse that can only list and read the prefixes it queries. Use a separate identity with write permissions for any INSERT INTO FUNCTION s3(...) jobs, so an accidental export cannot overwrite source files. Confirm in your cloud provider's IAM documentation which list and get actions a prefix-scoped policy needs for wildcard reads, because a policy that allows listing the wrong prefix can make globbing return unexpected files.

Access keys kept out of query text

Credentials written inline in a CREATE TABLE or SELECT statement end up in query logs, system tables and client history. Store the key in a named collection or a server-side configuration file with restricted file permissions, and reference it by name. Check which system tables and log levels in your version record query text, and confirm how your deployment tooling passes secrets to the ClickHouse process.

Default user restricted

The default user starts with all permissions, so any client that connects without a named account can read or write S3 data. Disable or restrict the default user, create named users for applications and analysts, and grant S3 table functions only to those accounts. Confirm the exact steps for your installation in the access control documentation before changing the default account on a running cluster.

S3 access through named roles

Grant the S3 source privilege to roles rather than to individual users, then assign roles to the people and services that need them. Pair this with row policies where only part of a shared bucket should be visible. Review the grants periodically, since a role added for one backfill often outlives the job. Confirm the privilege names and their scope in the access control documentation for your version.

TLS to the storage endpoint

Requests to S3 or an S3-compatible endpoint carry object data and signed headers across the network. Use HTTPS endpoints, verify the certificate chain for private endpoints such as on-premises object storage, and avoid plain HTTP URLs in table definitions. Confirm with your storage vendor how it issues and rotates certificates and whether the ClickHouse trust store needs the internal certificate authority added.

Versioning and deletion protection on source buckets

ClickHouse can read a file that someone overwrites or deletes, and a bad export can replace good data. Enable object versioning on source buckets that feed analytics, and apply deletion protection or object lock where your storage platform supports it. Confirm how your provider handles version retention and lifecycle rules, because a lifecycle rule that expires old versions can remove the copies you would need for a restore.

How storage class, request volume and caching change the bill

The cost of querying S3 from ClickHouse is driven by how much data each query touches, how often it touches it, and which storage class holds the objects. The S3 table engine and s3 table function read files on every query unless you load them elsewhere, so a dashboard that refreshes a wildcard path such as logs/*/*.parquet repeats object listing, request and data transfer charges each time it runs. Wildcards that match thousands of small files multiply request volume even when the total bytes are modest. Before choosing a design, measure how many files and bytes a typical query scans, how often that query runs, and whether the same files are read repeatedly. Those three numbers decide most of the bill.

Storage class changes both the retrieval side and the minimum-duration side of the charge. The S3 engine's storage_class_name option only supports classes that allow immediate retrieval, such as STANDARD, STANDARD_IA, INTELLIGENT_TIERING and GLACIER_IR, so archival classes such as GLACIER and DEEP_ARCHIVE cannot be read through it. Infrequent-access and instant-retrieval classes typically carry per-retrieval and minimum-storage-duration charges that favor data read occasionally and penalize data that is rewritten or deleted early. Confirm the current AWS pricing for each class in your region with your account owner, and confirm the exact option behavior in the ClickHouse S3 table engine documentation for the version you run.

Loading data into a MergeTree table moves the cost from S3 request and transfer charges to cluster storage and compute. You pay once for the ingest, then for disk and memory on your ClickHouse nodes, and repeated queries no longer call S3. That trade favors frequently queried data and dashboards with tight latency needs. Caching behavior for S3 reads is a configuration area that changes between releases, so confirm the cache settings and their defaults in the ClickHouse S3 documentation for your version before you model cost on them. Ask for a quote that states the expected scan volume per query and the assumed refresh frequency, so the trade-off can be checked against your own measurements.

Why S3 queries fail and how to fix them

This table maps the failures teams most often hit when querying or loading S3 files from ClickHouse to their likely causes, fixes and the checks to run before retrying. Read each row as a symptom you can match against a failed query, then confirm the listed item in your own bucket and cluster before changing the table definition.

Symptom	Likely cause	Fix	What to confirm
Wildcard returns zero rows	Prefix, folder name or letter case in the pattern does not match the object keys	Copy an exact key from the bucket listing, then widen the pattern one segment at a time	Object keys match the pattern character for character, including case and trailing slashes
Compressed file read as garbage or fails	File has no extension ClickHouse can map to a codec, so auto-detection does not apply	Set the compression parameter explicitly to the codec used when the file was written	The codec name is one of the documented values and the file was actually written with it
Columns shift or types mismatch	Schema inference sampled a subset of files that differ from the rest	Declare the column structure in the table definition instead of relying on inference	Declared types match the source files across all objects matched by the wildcard
Access denied on read or write	Bucket policy or IAM role lacks list or get permissions, or the access key has expired or been rotated	Grant read on the bucket and prefix, then recreate or update the credentials in the server or named collection	The principal used by ClickHouse can list the prefix, and the key is current and not restricted by policy
Errors on archived objects	Objects are in GLACIER or DEEP_ARCHIVE, which the S3 engine does not support for storage_class_name	Restore or copy the data to an immediate-retrieval class before querying, or load it through a separate restore process	Storage class of each object, which the S3 engine docs list as supported only for immediate-retrieval classes
Query times out on large reads	Network path between ClickHouse and the bucket is slow or routed through a constrained egress point	Keep compute in the same region as the bucket, check the route, and split the read into smaller prefixes	Network path, endpoint configuration for S3-compatible storage, and timeout settings on the client side
Duplicate rows after reloading	The same files were inserted again by a repeated job or a wildcard that matched files already loaded	Track loaded file names in a control table, or load into MergeTree with a deduplication plan keyed to file identity	Whether the load job is idempotent and which files each run matched

What does a ClickHouse S3 integration cost and what does it need from our team?

Can our own team build a ClickHouse S3 integration without a partner?

Yes, many teams with ClickHouse and cloud storage experience can build the S3 table engine and s3 table function setup themselves, and the documentation covers the syntax in detail. The questions that usually decide whether to do it in-house are how many formats and bucket layouts you have, whether you need to load results into MergeTree tables, and whether your engineers can own the credential model and the test environment over time. If those answers are already clear and your team has the capacity, the build is a contained task. If the inventory is large or spans several owners, a fixed-scope engagement can take that planning and documentation off your team's plate.

What drives the effort of a ClickHouse S3 integration?

Effort rises with four main factors:

The number of file formats (Parquet, CSV, JSON) and their compression types, since each needs its own schema and parsing checks.
Bucket layout, meaning how many buckets and prefixes exist and how well they match wildcard patterns such as *, ** and {N..M}.
The credential model, such as instance roles, access keys per team, or S3-compatible storage with its own authentication.
Whether query results feed MergeTree tables, which adds ingestion design, ordering keys and a refresh process.

Each factor multiplies the test cases, so a short list of datasets with one owner is far simpler than many datasets spread across teams.

What does our team need to provide for the work to start?

Plan to provide access and information before the build begins:

Read access to the relevant buckets, plus a decision on which storage account or role the ClickHouse servers will use.
Representative sample files for every format and compression type in scope, including edge cases such as missing columns or changed schemas.
A named owner for each dataset who can confirm meaning, freshness and who may read it.
A pre-production ClickHouse environment with representative data and queries, so changes can be tested before production.

Delays usually come from missing owners and sample files rather than from the ClickHouse work itself.

How long will a ClickHouse S3 integration take?

The duration depends on the scoped inventory, so no reliable figure can be given before it exists. Duration follows the number of datasets, formats, owners to consult, and whether MergeTree loading and access controls are included. Ask for a written scope that lists each dataset, format, bucket, credential model and acceptance check, then ask for the timeline against those items. Any estimate given before the inventory is complete should be treated as provisional.

What should a written quote for this work include?

A written quote should itemize the work so you can compare it with an internal plan:

The inventory of buckets, prefixes, formats and compression types in scope.
The table design for each dataset, including whether it is a read-only S3 engine table or a MergeTree copy.
Credential and access setup, including read-only users and how secrets are stored.
Test cases and the pre-production verification steps.
Documentation and handover, and any recurring support or retainer terms.
Assumptions, client dependencies and what changes would require a new quote.

DeployOpen agrees scope, deliverables and timelines in writing before work starts, so the quote should match that document line by line.

Is querying S3 files in place cheaper to run than loading them into MergeTree?

Querying in place usually has lower setup and storage cost, because the files stay where they are and no ingestion pipeline is needed. The trade-off is that every query reads from object storage, so repeated dashboards or heavy joins can become slow and costly in request and transfer charges. Loading into MergeTree adds ingestion design, storage on the cluster and a refresh process, but it gives faster repeated queries and better control over ordering and retention. Choose based on query frequency, dataset size and how often results feed dashboards. Confirm your storage provider's request and transfer pricing before comparing the two options.

What does the operating cost look like after the integration goes live?

After delivery, costs recur in three places: the ClickHouse servers that run queries, the object storage and request charges from your storage provider, and the staff time to review access, schema changes and new files. Reads from S3 are billed by the storage provider, so wildcard patterns that scan large prefixes can raise that bill. Storage classes also matter: the S3 engine's storage_class_name option supports only classes that allow immediate retrieval, such as STANDARD, STANDARD_IA, INTELLIGENT_TIERING and GLACIER_IR, while GLACIER and DEEP_ARCHIVE are not supported. Ask for a support model that covers upgrades and ongoing reviews if you need them after handover.

How DeployOpen scopes and delivers a ClickHouse S3 integration

DeployOpen starts with the files you already keep in S3 or S3-compatible storage and the questions you need answered from them. We then define which files stay as external sources, which get loaded into MergeTree tables, and how the cluster in your infrastructure is secured, tested and handed over.

How we scope

Inventory the buckets, file formats, compression types and folder layouts that the ClickHouse integration must read.
Agree which queries stay on files through the S3 engine and which datasets move into MergeTree tables.
Record storage class, access model and network path to your S3 endpoint in the written scope before work starts.
Engagements are fixed-scope, with deliverables and timelines agreed in writing before work begins.

How we build

Create read-only credentials and named collections so ClickHouse reads only the prefixes in scope.
Build and test wildcard patterns against representative file sets, including schema and compression checks.
Prototype load jobs into MergeTree tables and validate row counts and types against the source files.
Run tests in a pre-production environment with representative data before any change reaches production.

How we hand over

Document table definitions, wildcard rules, credential handling and the load procedure for your engineers.
Provide runbooks for failed reads, schema drift in source files and storage class restrictions.
Walk your team through the configuration in your own environment and record the open questions.
Deliver the scope outputs in the form agreed in writing, so your team can extend the integration later.

How we support

Ongoing support, upgrades and continuous testing are available as a retainer after handover.
Plan upgrades against the ClickHouse release channel and the changelog before each version change.
Re-run the file read and load tests after bucket policy, schema or ClickHouse version changes.
Ask any provider or DeployOpen for written scope, deliverables and support terms before committing.

ClickHouse and Amazon S3: querying and loading files