Analytics PipelineClickHouse & Materialized Views
Guide Real-Time Data Ingestion Pipeline Implementation

Bulk Inserting with clickhouse-connect

The clickhouse-connect client reaches its highest insert throughput when you hand it column-oriented data and let it serialize straight to ClickHouse’s binary Native format — bypassing the text parsing, per-row Python overhead, and type-guessing that quietly cap a naive client.insert() loop. This guide shows how to drive client.insert() with tuples and columns, push pandas and Arrow through insert_df/insert_arrow, pin column_names and column_types so the wire format is unambiguous, turn on transport compression, and chunk frames too large to hold in one block — the client-side execution half of batch insert optimization.

Prerequisites

How clickhouse-connect serializes an insert

clickhouse-connect is a columnar client. Whatever shape you give it — a list of row tuples, a pandas DataFrame, or an Arrow Table — it transposes into columns, encodes each column with the type-specific writer for the destination schema, and streams the result to ClickHouse as Native (or RowBinary) rather than text. That is why passing explicit column_names and column_types matters: it lets the client skip inference, pick the exact binary writer per column, and stream without buffering the whole payload as Python objects. The path below is the shortest one from a source frame to a merge-friendly part.

How clickhouse-connect turns source data into a Native-format insert Three source shapes — a list of Python row tuples via client.insert, a pandas DataFrame via insert_df, and an Arrow Table via insert_arrow — all enter the clickhouse-connect client. The client transposes rows to columns, binds each column to the destination column type, and applies optional LZ4 compression, then streams the result to the ClickHouse HTTP interface in binary Native format, which lands as one merge-friendly MergeTree part. list of tuples client.insert pandas DataFrame insert_df Arrow Table insert_arrow clickhouse-connect rows → columns bind column_types optional LZ4 Native HTTP interface forms one block merge-friendly part

Step 1 — Insert column-oriented data with client.insert

The base call takes a table name, a data payload, and a column list. The single most impactful choice is how you shape data. Row-major lists of tuples are the common case, but if your source is already columnar, pass column_oriented=True and hand the client a list of columns — it skips the transpose entirely.

python
import clickhouse_connect

client = clickhouse_connect.get_client(host="clickhouse", port=8123, database="analytics")

# Row-oriented: list of row tuples in column order.
rows = [
    ("2026-07-17 09:00:00.000", 4711, "checkout", "{}"),
    ("2026-07-17 09:00:01.250", 4712, "view",     "{}"),
]
client.insert(
    "events",
    rows,
    column_names=["event_ts", "user_id", "event_type", "payload"],
)

# Column-oriented: one list per column, no per-row tuple overhead.
columns = [
    ["2026-07-17 09:00:00.000", "2026-07-17 09:00:01.250"],  # event_ts
    [4711, 4712],                                            # user_id
    ["checkout", "view"],                                    # event_type
    ["{}", "{}"],                                            # payload
]
client.insert(
    "events",
    columns,
    column_names=["event_ts", "user_id", "event_type", "payload"],
    column_oriented=True,
)

Both calls return a QuerySummary you can log; a successful insert reports written_rows matching your payload length. Column-oriented insertion avoids materializing N tuples and is measurably faster once batches exceed a few hundred thousand rows.

Step 2 — Pin column_types to skip inference

When any column can be NULL, is an enum-like LowCardinality(String), or is a DateTime64(3) fed from strings, let the client infer nothing. Passing column_types binds each column to the exact ClickHouse type writer, which both eliminates guess-wrong errors and removes an inference pass over the data.

python
from clickhouse_connect.datatypes.format import format_by_type

client.insert(
    "events",
    rows,
    column_names=["event_ts", "user_id", "event_type", "payload"],
    column_types=[
        "DateTime64(3)",
        "UInt64",
        "LowCardinality(String)",
        "String",
    ],
)

If you omit column_types, the client issues a DESCRIBE TABLE on first insert to learn the schema — fine for a one-off, wasteful in a hot loop that reconnects. Supplying the types explicitly makes each insert() self-contained and cache-free.

Step 3 — Insert pandas and Arrow frames directly

For analytics ETL the source is usually a DataFrame or an Arrow Table. insert_df and insert_arrow skip the Python-object round trip: they read the frame’s own column buffers. Arrow is the fastest path because its columnar memory maps almost one-to-one onto ClickHouse’s Native format.

python
import pandas as pd
import pyarrow as pa

df = pd.DataFrame({
    "event_ts":   pd.to_datetime(["2026-07-17 09:00:00.000", "2026-07-17 09:00:01.250"]),
    "user_id":    pd.array([4711, 4712], dtype="uint64"),
    "event_type": ["checkout", "view"],
    "payload":    ["{}", "{}"],
})
client.insert_df("events", df)

# Arrow — the leanest hot path for large columnar batches.
table = pa.table({
    "event_ts":   pa.array([1_752_742_800_000, 1_752_742_801_250], type=pa.timestamp("ms")),
    "user_id":    pa.array([4711, 4712], type=pa.uint64()),
    "event_type": pa.array(["checkout", "view"]),
    "payload":    pa.array(["{}", "{}"]),
})
client.insert_arrow("events", table)

Match the frame’s dtypes to the destination types: a pandas object column of Python strings for a LowCardinality(String) column is correct, but a pandas datetime64[ns] inserted into a DateTime64(3) column truncates from nanoseconds to milliseconds — verify the precision matches before you rely on it.

Step 4 — Turn on transport compression

Insert payloads are highly compressible column data. Enabling lz4 (or zstd) on the connection shrinks the bytes on the wire, which is almost always a net win on any non-local link — CPU spent compressing is repaid many times over in reduced transfer time.

python
client = clickhouse_connect.get_client(
    host="clickhouse",
    port=8123,
    database="analytics",
    compress="lz4",          # 'lz4' (fast) or 'zstd' (denser); True picks a default
    query_limit=0,           # no implicit row cap on reads from this client
)

lz4 is the pragmatic default: it compresses fast enough to keep the insert CPU-bound elsewhere. Reserve zstd for bandwidth-constrained links where the extra ratio pays off. Compression is negotiated per connection, so set it once at client construction rather than per call.

Step 5 — Chunk frames too large for one block

A single insert_df on a 50-million-row frame builds one enormous block, spikes memory on both client and server, and can trip MEMORY_LIMIT_EXCEEDED. Split the frame into chunks aligned with the server’s max_insert_block_size so each call produces one well-sized part. Reuse the same client — connection pooling amortizes the cost across chunks.

python
def insert_in_chunks(client, table, df, chunk_rows=500_000, settings=None):
    total = len(df)
    for start in range(0, total, chunk_rows):
        chunk = df.iloc[start:start + chunk_rows]
        client.insert_df(table, chunk, settings=settings)
    return total

insert_in_chunks(
    client, "events", big_df, chunk_rows=500_000,
    # Per-insert settings ride along with the request.
    settings={"max_insert_block_size": 500_000, "insert_deduplication_token": None},
)

Sizing chunk_rows near max_insert_block_size keeps the server from re-splitting or coalescing your blocks; the deeper trade-offs live in tuning max_insert_block_size for high throughput. Any key in settings is applied to that insert only, which is how you attach an insert_deduplication_token or override block size without changing server config.

Verification

Confirm the client actually wrote the rows you expect, at merge-friendly part sizes, by reading system.parts on the destination:

sql
SELECT
    count()                                   AS parts,
    sum(rows)                                 AS rows,
    formatReadableSize(avg(bytes_on_disk))    AS avg_part_size
FROM system.parts
WHERE database = 'analytics' AND table = 'events' AND active;

A healthy bulk load shows a small number of parts, each averaging tens of megabytes — not thousands of tiny parts. Cross-check what the server recorded per insert (flush logs first with SYSTEM FLUSH LOGS):

sql
SELECT event_time, written_rows,
       formatReadableSize(written_bytes) AS wire_bytes,
       query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish' AND query_kind = 'Insert'
  AND has(tables, 'analytics.events')
  AND event_time > now() - INTERVAL 15 MINUTE
ORDER BY event_time DESC
LIMIT 10;

written_rows should equal your batch sizes and written_bytes should be visibly smaller than the raw payload when compression is on.

Gotchas and edge cases

  • Column order is positional, not by name. client.insert maps data to column_names by index; a mismatched order silently writes user_id values into event_type. Always pass column_names explicitly and keep it in sync with the tuple order, rather than relying on the table’s declared order.
  • DateTime64 precision truncates silently. A pandas datetime64[ns] column loses sub-millisecond precision when written to DateTime64(3), and naive (tz-less) timestamps are interpreted in the column’s timezone. Normalize to the target precision and timezone before insert, not after.
  • insert_df on object-dtype numeric columns is slow. A pandas column of Python ints (dtype object) forces per-value boxing. Cast to a concrete NumPy/pandas dtype (uint64, Int64) so the client reads a contiguous buffer instead of iterating Python objects.
  • One giant insert is not more efficient than several sized ones. Beyond max_insert_block_size the server splits the block anyway, but only after buffering it, so a single 50M-row call costs peak memory for no throughput gain. Chunking at the block boundary is strictly better. Making those retries safe against duplicates is covered in deduplicating inserts with insert deduplication.

Up: Batch Insert Optimization