How do you bulk-load Parquet files from S3 into ClickHouse with the s3 table function?

## The basic shape ```sql INSERT INTO events SELECT event_time, user_id, action FROM s3( 'https://my-bucket.s3.amazonaws.com/events/2024/*.parquet', 'Parquet' ); ``` `s3(...)` is a **table function**: it makes a set of object-storage files look like a table for the duration of one query. Nothing is registered in the schema and nothing is cached. Because the result is just a table expression, the whole power of SELECT is available — filter rows you do not want, cast types, rename or compute columns, join against a dimension table already in ClickHouse. Credentials can be passed positionally after the URL, but in production the sensible options are an instance role attached to the server or a **named collection** so the secret lives in server configuration rather than in query text and query logs. For public data, `NOSIGN` skips signing entirely. ## Globs are the parallelism The URL supports `*` (within a path segment), `**` (across segments), `?`, alternation `{a,b,c}` and numeric ranges `{1..10}`. This matters beyond convenience: ClickHouse expands the glob into a file list and reads files concurrently, so one statement matching 500 files is a parallel load. A single enormous file is *worse* than a few hundred medium ones because it limits how many readers can work at once — the inverse of the usual small-files warning, and a nice detail to raise in an interview. If a file list is awkward to express as a glob, `s3` also accepts a comma-free brace list, and for reproducible loads many teams generate an explicit list of URLs and load them in chunks so a failure is restartable. ## Scaling past one node `s3` runs entirely on the node that received the query. `s3Cluster('cluster_name', url, ...)` changes that: the initiator resolves the glob, hands slices of the file list to every node named in the cluster configuration, and each node reads and processes its share. For a large historical backfill this is the difference between one machine's network bandwidth and the whole cluster's. ```sql INSERT INTO events SELECT * FROM s3Cluster( 'my_cluster', 'https://my-bucket.s3.amazonaws.com/events/**/*.parquet', 'Parquet' ); ``` ## Tuning the write side A bulk load is still an insert, so it still creates parts, and the part sizing rules from ordinary ingestion apply: - **`max_insert_threads`** raises write-side parallelism. It speeds the load and, as a side effect, produces more parts, so it is a trade against merge pressure. - **`min_insert_block_size_rows` / `min_insert_block_size_bytes`** set the floor on how much data a block must carry before a part is written. Raising them is how you stop a load of many small source files from writing one thin part per file. - **Partition span.** If the loaded data covers many `PARTITION BY` values, each block splits into a part per partition. Loading partition by partition (one glob per month, say) keeps the part count sane and makes the load restartable. - **Memory.** Sorting each block by the target's `ORDER BY` key costs memory proportional to block size times threads. A load that dies on memory usually needs fewer threads or smaller blocks, not a bigger box. For row-oriented text formats such as `JSONEachRow` and `CSV`, `input_format_parallel_parsing` lets one file be parsed by several threads. Parquet is columnar and decoded through its own reader path, so that particular setting is not what tunes it. ## Schema inference and its limits Omitting the structure lets ClickHouse infer column names and types from the file. This is excellent for exploration: ```sql DESCRIBE s3('https://my-bucket.s3.amazonaws.com/events/part-0.parquet', 'Parquet'); SELECT count() FROM s3('https://.../events/*.parquet', 'Parquet'); ``` For a production load, state the structure or select explicit columns instead. Inference reads only a sample, so a type can shift between runs when the data does, and an inferred nullable or wide type quietly costs storage in the target table. ## Continuous ingestion from a bucket The table function is pull-based and one-shot. For a prefix that keeps receiving files, recent ClickHouse versions provide an `S3Queue` table engine that tracks which files have already been consumed and, paired with a materialized view into a MergeTree table, keeps a target table up to date without an external scheduler. The alternative — and still the common one — is an orchestrator that issues `INSERT ... SELECT FROM s3(...)` per batch and records its own watermark. ## Reading, not just writing The same function reads files in place for one-off analysis without loading anything, which is how teams triage a lake dataset before deciding what to ingest, and `INTO OUTFILE` or the `s3` function on the write side handles the export direction.