When loading into ClickHouse, how do Native, Parquet and JSONEachRow formats differ in cost?

## Two costs, not one When you compare input formats, separate the two things you are paying for: 1. **Bytes on the wire** — how compact the encoding is, and whether it compresses. 2. **Server CPU to materialise blocks** — parsing text, decoding encodings, converting types, assembling columns. A format can win one and lose the other. JSON compresses reasonably well but costs enormous CPU. Parquet is compact *and* moderately cheap to decode but never free. Native wins both, at the price of being ClickHouse-specific. ## Native `Native` is ClickHouse's internal block format: columnar, typed, and exactly the shape the server works with in memory. Data arrives already laid out per column with its type known, so the server does essentially no conversion — it takes the block, sorts it by the table's key, compresses the columns and writes the part. This is what the native TCP protocol and `clickhouse-client` use by default, and what the official language drivers speak. Use it when both ends are ClickHouse or a ClickHouse driver: server-to-server copies, `clickhouse-client` pipelines, an application using the native driver. The catch is that it is a ClickHouse-specific binary format tied to the server's type system; it is not an interchange format and not something to archive data in. ```bash # fastest hop between two ClickHouse servers clickhouse-client --query "SELECT * FROM events WHERE event_date = '2024-08-01' FORMAT Native" \ | clickhouse-client --host target --query "INSERT INTO events FORMAT Native" ``` ## Parquet Parquet is columnar, self-describing and heavily compressed, which makes it the default currency of a data lake. Loading it into ClickHouse means reading its column chunks, undoing its encodings, and mapping its logical types onto ClickHouse types. That is real CPU, but it is bounded, parallelisable per column chunk, and it moves far fewer bytes than any text format. The practical friction is **type mapping**. Parquet's timestamps, decimals, nullability and nested structures have to land on concrete ClickHouse types. Letting inference choose gives you wide, often nullable columns that cost storage forever in the target table; declaring the target schema and casting explicitly in the `SELECT` is the disciplined approach. Choose Parquet when the data already exists as lake files, when the same files must be readable by other engines, or when transfer bytes dominate — a cross-region load, for example. ## JSONEachRow One JSON object per line. Every field name is repeated on every row, every value is parsed from text, and every value is coerced to the column's type. On a wide table this is comfortably the most expensive ingestion path, and it is also the least compact before compression. It survives because it is universally producible, human-readable in a log, and tolerant of shape changes — a new field in the payload does not break a load that does not select it. Mitigations worth naming: `input_format_parallel_parsing` (on by default) splits parsing of a single stream across threads, which is exactly where JSON's cost lives; keeping payloads narrow; and using `JSONCompactEachRow`, which drops the repeated key names, when the producer can guarantee column order. ## RowBinary and the middle ground `RowBinary` writes values in a compact binary row-by-row encoding with no field names and no text parsing. It is a strong choice for a bespoke producer that is not using a ClickHouse driver: far cheaper than JSON, far simpler to emit than Native, and no schema negotiation beyond agreeing on column order and types. `RowBinaryWithNamesAndTypes` adds a header so column order is verified rather than assumed. ## What the format does *not* change This is the point interviewers most want to hear: **format choice does not change part semantics.** Whatever the encoding, an insert still becomes one part per block per partition, and a thousand small JSON inserts and a thousand small Native inserts produce identical part explosion. Batch size, insert rate and partition granularity dominate ingestion health; format choice is a CPU and bandwidth optimisation on top of a write path that must already be sane. A candidate who answers a `Too many parts` question with "switch to Native" has misread the problem. ## Choosing, briefly - ClickHouse on both ends, or an official driver → **Native**. - Files already in a lake, or bytes on the wire are the constraint → **Parquet**. - Custom producer, want cheap and simple → **RowBinary**. - Interoperability, debuggability, changing payload shapes, and CPU is not your bottleneck → **JSONEachRow**. Measure rather than assume: `system.query_log` records the bytes read and the CPU time for each insert, which turns this whole comparison into a number for your actual data.