Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 35 ยท Integrations and Operations

Loading Data and Insert Patterns

Load data into ClickHouse the right way: batch sizes, INSERT formats, clickhouse-client and HTTP loading, asynchronous inserts, schema inference and common loading mistakes.

Fast queries start with healthy inserts. ClickHouse writes every insert as a new immutable part on disk, and merges parts in the background. The main rule of loading follows from that: insert in large batches, not row by row. This lesson covers the batch rules, the input formats, the main ways to send data and the pitfalls.

The server cannot run here, so ClickHouse commands are read-only. SQL stand-ins demonstrate batching and basic validation logic. Related concepts: parts and the sparse index and deduplication on retry.

Batch rules

Each INSERT creates at least one part per partition touched. A thousand one-row inserts create a thousand parts, and ClickHouse has to merge them while queries open every file. When active parts in a partition pass a limit (parts_to_delay_insert, then parts_to_throw_insert, default 3000 in recent versions), inserts slow down and finally fail with Too many parts.

Guidelines:

  • Send at least 1,000 rows per insert, ideally 10,000 to 1,000,000.
  • Keep it to roughly one insert per second per table (more is possible with good hardware, but batching is cheaper).
  • Insert data sorted by the partition key when you can, since each partition value in a batch creates its own part. Inserting a year of data with a monthly partition key creates 12 parts per insert.
  • If the producers cannot batch, put a buffer in front (Kafka, a queue, a small aggregator) or use asynchronous inserts.

Input formats

ClickHouse supports dozens of formats; FORMAT after the table names the one in use.

Format Use
Values small hand-written inserts
CSV, CSVWithNames, TSV, TSVWithNames spreadsheets, exports
JSONEachRow one JSON object per line (logs, APIs)
JSONCompactEachRow arrays per line, smaller than objects
Parquet, ORC, Avro, Arrow columnar files from data lakes
Native ClickHouse's own binary format, fastest
RowBinary compact binary for custom clients
SQL
INSERT INTO events (ts, site, user_id) VALUES
    ('2025-03-01 10:00:00', 'shop', 42),
    ('2025-03-01 10:00:01', 'blog', 43);

INSERT INTO events FORMAT JSONEachRow
{"ts": "2025-03-01 10:00:02", "site": "shop", "user_id": 44}
{"ts": "2025-03-01 10:00:03", "site": "shop", "user_id": 45}

Useful settings: input_format_skip_unknown_fields = 1 ignores extra JSON keys, date_time_input_format = 'best_effort' parses varied date strings, and input_format_allow_errors_num tolerates a number of bad rows during file import. Missing columns get their DEFAULT value.

Sending data

From a file with clickhouse-client:

Terminal
clickhouse-client --query "INSERT INTO events FORMAT CSVWithNames" < events.csv
clickhouse-client --query "INSERT INTO events FORMAT Parquet" < events.parquet

Over HTTP (port 8123), the basis of most language drivers:

Terminal
curl 'http://localhost:8123/?query=INSERT%20INTO%20events%20FORMAT%20JSONEachRow' \
     --data-binary @events.ndjson

Official clients exist for Python (clickhouse-connect), Java, Go, JavaScript and more; they all batch rows into one request. Use the native protocol (port 9000) for the least overhead.

Directly from files with table functions: INSERT INTO events SELECT * FROM file('events*.parquet') reads local files in the server's user_files directory, and similar functions read S3 or URLs (see table functions and integrations). Use DESCRIBE file('x.parquet') and CREATE TABLE ... ENGINE = MergeTree ORDER BY tuple() AS SELECT * FROM file(...) to infer a first schema from a file, then refine types and the sorting key by hand.

Asynchronous inserts

If thousands of small clients cannot be batched, enable async inserts:

SQL
INSERT INTO events SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES ('2025-03-01 10:00:04', 'shop', 46);

The server collects rows from many inserts in memory and flushes them as one part when a size (async_insert_max_data_size), a time (async_insert_busy_timeout_ms, default about 1 second in recent versions) or a query-count threshold is reached. With wait_for_async_insert = 1 the client gets its acknowledgement after the flush (safe; recommended). With 0 it returns immediately, which is faster but you do not learn about failures. Async inserts have deduplication behaviour that depends on settings, so use tokens for retries.

Updates and deletes at load time

ClickHouse is append-first. For corrections, prefer re-inserting with a ReplacingMergeTree, replacing a whole partition (ALTER TABLE ... REPLACE PARTITION) or dropping a partition, rather than many single-row ALTER TABLE ... UPDATE/DELETE mutations, which rewrite parts and are heavy. Lightweight DELETE FROM exists for occasional removals.

Stand-in: batching and validating

The first stand-in shows the difference between one-by-one and batched inserts by counting "parts" created (one per statement). The second validates incoming text rows before loading, like input_format_allow_errors_num. Both are SQLite models.

SQL Stand-in: statements versus parts

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: reject bad rows while loading

Press Run (or Ctrl + Enter) to see the result here.

Common mistakes

  • One row per insert from application code (a "Too many parts" error follows).
  • Partitioning by a high-cardinality or fine-grained key, creating thousands of tiny partitions.
  • Choosing String for everything when inserting from CSV; define real types.
  • Using wait_for_async_insert = 0 and ignoring failures.
  • Updating rows one at a time with mutations.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should you insert in large batches?

    Too many parts causes slow reads and eventually 'Too many parts' errors, so batch thousands of rows per insert.
  2. 2. Which format is a good fit for one JSON object per line from an application log?

    JSONEachRow reads newline-delimited JSON objects.
  3. 3. What does async_insert = 1 do?

    The server batches rows from many inserts and writes them together after a size or time threshold.
  4. 4. How do you load many corrections to existing data most efficiently?

    Mutations rewrite whole parts; re-inserting newer versions or replacing a partition is much cheaper.

Summary

  • Batch inserts (10,000+ rows, about one per second per table) to keep part counts low.
  • Choose a format that fits the source; JSONEachRow, CSV, Parquet and Native are common.
  • Load via client, HTTP or table functions; infer a schema, then refine it.
  • Use async inserts when producers cannot batch; avoid single-row mutations.

Next: Table Functions and Integration Engines