TopolearnIngestion is where most ClickHouse problems start; batching and the right format avoid them.
INSERT INTO t VALUES (...) works, but real pipelines usually send data in a format: INSERT INTO t FORMAT CSV, JSONEachRow (one JSON object per line), Parquet, TabSeparated and dozens more. Clients stream the rows after the statement, and ClickHouse parses them in parallel.
Table functions read external data as if it were a table, so loading is just INSERT ... SELECT:
file('data/*.csv')in clickhouse-local,url('https://...', CSVWithNames)over HTTP;s3('https://bucket.s3.amazonaws.com/logs/*.parquet')for object storage;postgresql(...),mysql(...)to copy from operational databases.
Schema inference usually works out the column types for you; DESCRIBE file('x.csv') shows what it guessed.
The golden rule is batch size. Aim for inserts of at least a few thousand rows, ideally 10k to 100k+, and no more than about one insert per second per table. If many clients send small inserts (log shippers, IoT), turn on asynchronous inserts: with async_insert = 1 the server buffers small inserts and flushes them together as one part. With wait_for_async_insert = 1 (the default) the client gets its acknowledgement only after the flush, so data isn't lost silently.
Inserts are atomic per block: a block either lands entirely or not at all, and identical blocks retried into a replicated table are deduplicated.
Example
CREATE TABLE page_views (ts DateTime, url String, ms UInt32)
ENGINE = MergeTree ORDER BY (url, ts);
-- Many rows per INSERT, sent in a text format
INSERT INTO page_views FORMAT JSONEachRow
{"ts": "2025-03-01 10:00:00", "url": "/home", "ms": 120}
{"ts": "2025-03-01 10:00:05", "url": "/cart", "ms": 340}
-- INSERT ... SELECT from a table function (here: generated data)
INSERT INTO page_views
SELECT now() - number, concat('/p/', toString(number % 50)), rand() % 1000
FROM numbers(100000);
-- Many small inserts from many clients? Let the server batch them:
INSERT INTO page_views SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES (now(), '/home', 99);
SELECT count() FROM page_views; -- 100003Remember
- Send big batches in a format such as JSONEachRow, CSV or Parquet
- Table functions (file, url, s3, postgresql) turn loading into INSERT ... SELECT
- Use async_insert when you can't batch on the client
Watch out for
- Setting wait_for_async_insert = 0 and assuming every insert was written.