Loading data into ClickHouse: batches, async_insert, monitoring

Loading Data into ClickHouse: How I Stopped Inserting One Row at a Time and Accelerated Ingestion by 500x

A Disaster Story: 1000 Queries per Second and a Production Outage

I had a project — real-time betting analytics. 50 million events came in daily. I thought INSERT INTO bets VALUES (...) one event at a time was fine. ClickHouse handled 500 inserts per second, but we needed 2000. The server choked: disk queues went through the roof, and background merges spawned thousands of tiny parts. Production went down.

Turns out, ClickHouse is not MySQL. It's built for bulk inserts, not point INSERTs. After rewriting the loader to batch 100,000 records at a time, I saw 10,000 inserts per second with no performance loss.

Below are all the loading methods, from small CSVs to production-grade batchers, along with the pitfalls that cost me sleepless nights.

1. Why a Single Row Is Evil (Even If You Want To)

ClickHouse creates a new part on disk for every INSERT. Parts contain granules of 8192 rows, but even if you insert just 1 row, a new part with tiny .bin files is physically created.

Consequences:

• 10,000 INSERTs of 1 row each = 10,000 parts
• A SELECT query must open 10,000 files
• Background merges try to combine them, straining the CPU
• Disk space is wasted on part metadata

Rule from my experience: a batch should be at least 1000 rows or 1 MB of compressed data.

2. INSERT ... VALUES — For Testing, Not Production

-- Only for local experiments
INSERT INTO betting.bets (user_id, created_at, amount, odds, sport, outcome)
VALUES 
    (1001, now(), 50.00, 2.1, 'football', 'win'),
    (1002, now(), 100.00, 1.8, 'basketball', 'loss'),
    (1003, now(), 75.00, 3.0, 'tennis', 'win');

In production, never do this. Even if you batch 1000 rows in VALUES, SQL parsing becomes the bottleneck.

3. INSERT ... FORMAT CSV / JSONEachRow / TSV — Lifesaver for Files

ClickHouse understands several formats on the fly.

Example with CSV file bets.csv:

1001,2024-03-15 10:00:00,50.00,2.1,football,win
1002,2024-03-15 10:01:00,100.00,1.8,basketball,loss
1003,2024-03-15 10:02:00,75.00,3.0,tennis,win

Load it:

INSERT INTO betting.bets FORMAT CSV

But you need to pipe the data to stdin. So in scripts, use:

clickhouse-client --query="INSERT INTO betting.bets FORMAT CSV" < bets.csv

JSONEachRow — ideal for logs:

{"user_id":1001,"created_at":"2024-03-15 10:00:00","amount":50.00,"odds":2.1,"sport":"football","outcome":"win"}
{"user_id":1002,"created_at":"2024-03-15 10:01:00","amount":100.00,"odds":1.8,"sport":"basketball","outcome":"loss"}

Loading:

clickhouse-client --query="INSERT INTO betting.bets FORMAT JSONEachRow" < bets.json

TabSeparated — the fastest (less parsing):

clickhouse-client --query="INSERT INTO betting.bets FORMAT TSV" < bets.tsv

4. Loading via clickhouse-client from File — My Favorite for Backups

# Small file
clickhouse-client --query="INSERT INTO betting.bets FORMAT CSV" < /tmp/daily_bets.csv

# Huge file with progress
clickhouse-client \
  --query="INSERT INTO betting.bets FORMAT CSV" \
  --progress \
  --max_insert_block_size=100000 \
  < /data/historical_bets_2023.csv

Important flag: --max_insert_block_size=100000 — splits the file into blocks of 100k rows. This allows inserting terabyte-sized files without memory overflow.

What I learned: FORMAT defines the structure of input data, not output. Notice the query itself contains no data; it comes via stdin.

5. HTTP API with POST — When You Don't Have Command Line Access

# CSV via curl
curl -X POST "http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+CSV" \
  --data-binary @bets.csv

# JSONEachRow
curl -X POST "http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+JSONEachRow" \
  --data-binary @bets.json \
  -u analyst:password

# GZIP on the fly (saves bandwidth)
gzip -c bets.csv | curl -X POST "http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+CSV" \
  --data-binary @- \
  --header "Content-Encoding: gzip"

Why POST, not GET: GET requests are fully logged, including data. If you insert via ?query=INSERT..., the password and data may end up in proxy logs.

6. INSERT SELECT — Moving Data Inside ClickHouse

The fastest way to mass reload is not to export externally.

-- Copy January data to archive table
INSERT INTO betting.bets_archive
SELECT * FROM betting.bets
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

-- Aggregate on the fly
INSERT INTO betting.daily_stats (date, total_bets, total_amount)
SELECT 
    toDate(created_at) AS date,
    count() AS total_bets,
    sum(amount) AS total_amount
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY date;

Speed: INSERT SELECT avoids client-server exchange; it's a local transfer. A billion rows can be moved in minutes.

7. Async Insert: When Thousands of Events per Second Come from Players

Classic INSERT is synchronous: the client waits until data is written to disk. At 10,000 inserts per second, that hurts.

async_insert buffers data in memory and inserts in batches.

Enable it in config.xml or via SETTINGS:

-- Session level
SET async_insert = 1;
SET wait_for_async_insert = 0;  -- don't wait for completion
SET async_insert_max_data_size = 10000000;  -- 10 MB buffer
SET async_insert_busy_timeout_ms = 200;     -- flush every 200 ms

INSERT INTO betting.bets FORMAT JSONEachRow
{"user_id":1001,"amount":50,"odds":2.1}
{"user_id":1002,"amount":100,"odds":1.8}

How it works:

1. Client sends small inserts
2. ClickHouse accumulates them in a memory buffer
3. When the buffer is full (10 MB) or timeout expires (200 ms), a single bulk insert occurs

What burned me: wait_for_async_insert=0 means the client doesn't know if the insert failed. Network issues can cause data loss. Set wait_for_async_insert=1 if reliability matters. Performance drops, but not catastrophically.

8. Settings: max_insert_block_size and max_batch_size

<!-- config.xml -->
<max_insert_block_size>1048576</max_insert_block_size>  <!-- 1M rows -->
<max_block_size>65536</max_block_size>

max_insert_block_size — the maximum block size ClickHouse processes at once. If your insert is larger, it's split into blocks.

Production rule: set max_insert_block_size to 2–4 times smaller than your expected batch. That way, even an accidentally huge INSERT won't kill memory.

# Force for a single insert
clickhouse-client --query="INSERT INTO bets FORMAT CSV" \
  --max_insert_block_size=500000 < huge_file.csv

9. Monitoring Inserts via system.query_log

Never guess how many rows were inserted. Check system.query_log.

-- Recent INSERT queries
SELECT 
    event_time,
    query_duration_ms,
    read_rows,
    written_rows,
    result_rows,
    memory_usage,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query LIKE '%INSERT INTO betting.bets%'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY event_time DESC
LIMIT 20;

My monitoring dashboard: shows metrics for the last hour — average batch size, number of failed inserts, rows/sec speed.

SELECT 
    toStartOfMinute(event_time) AS minute,
    count() AS inserts,
    avg(written_rows) AS avg_batch_size,
    sum(written_rows) AS total_rows,
    avg(query_duration_ms) AS avg_latency_ms
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query LIKE '%INSERT INTO betting.bets%'
  AND event_time >= now() - INTERVAL 1 HOUR
GROUP BY minute
ORDER BY minute DESC;

10. Python Script for Batch Loading Historical Data

In a real project, we loaded 3 years of betting history from parquet files in S3. Here's a script that handled 2 billion rows:

#!/usr/bin/env python3
from clickhouse_driver import Client
import pandas as pd
import glob
from tqdm import tqdm

# Connect to ClickHouse
client = Client(
    host='clickhouse.prod.internal',
    port=9000,
    user='loader',
    password='strong_password',
    database='betting'
)

# Optimizations for large inserts
client.execute("SET max_insert_block_size = 1000000")
client.execute("SET min_insert_block_size_rows = 500000")
client.execute("SET min_insert_block_size_bytes = 100000000")  # 100 MB

def insert_batch(df, table='bets'):
    """Insert DataFrame as a batch via Native protocol"""
    # clickhouse-driver converts types automatically
    client.execute(f"INSERT INTO {table} FORMAT TabSeparated", 
                   df.to_csv(sep='\t', header=False, index=False),
                   types_check=True)

def stream_parquet_files(pattern='/data/bets_*.parquet'):
    files = glob.glob(pattern)
    batch_size = 500000
    buffer = []
    
    for file in tqdm(files, desc="Processing files"):
        # Read parquet in chunks to avoid holding everything in memory
        for chunk in pd.read_parquet(file, chunksize=batch_size):
            # Cast types for ClickHouse
            chunk['created_at'] = pd.to_datetime(chunk['created_at'])
            chunk['amount'] = chunk['amount'].astype('float64')
            chunk['odds'] = chunk['odds'].astype('float64')
            
            buffer.append(chunk)
            if len(buffer) >= 5:  # 5 chunks of 500k = 2.5M rows
                df_batch = pd.concat(buffer, ignore_index=True)
                insert_batch(df_batch)
                buffer = []
    
    # Remaining data
    if buffer:
        df_batch = pd.concat(buffer, ignore_index=True)
        insert_batch(df_batch)

if __name__ == '__main__':
    stream_parquet_files()

Alternative via requests (without pandas):

import requests
import gzip
import io

def insert_via_http_csv_gz(filepath):
    with open(filepath, 'rb') as f:
        compressed = gzip.compress(f.read())
    
    response = requests.post(
        'http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+CSV',
        data=compressed,
        headers={'Content-Encoding': 'gzip'},
        auth=('loader', 'password')
    )
    
    if response.status_code != 200:
        raise Exception(f"Insert failed: {response.text}")
    
    print(f"Inserted {filepath}")

# Example batch sending multiple files
for file in glob.glob('/data/bets_*.csv.gz'):
    insert_via_http_csv_gz(file)

Common Loading Errors (and How I Fixed Them)

Error: Memory limit (total) exceeded
Cause: Insert block too large.
Solution: Reduce max_insert_block_size or split the file into pieces.

Error: Too many parts
Cause: Too many small INSERTs or async_insert disabled under high throughput.
Solution: Enable async_insert, increase min_rows_for_wide_part.

Error: Code: 252. DB::Exception: Too many simultaneous inserts
Cause: Parallel inserts into the same partition.
Solution: Set max_insert_threads = 1 for that table.

Error: Partitions not merging
Cause: Data inserted out of ORDER BY order.
Solution: Ensure data is sorted the same as the table's ORDER BY.

What's Next

You now know how to load data by any method — from small CSVs to production streams. The next article covers query optimization and advanced aggregations.

Parting advice: No INSERT should leave your script without a pair of max_insert_block_size and async_insert if you're working with production. I stepped on that rake three times — enough.

---

← Previous: MergeTree in ClickHouse: How the Engine Slices Analytics into Granules and Merges Parts
→ Next: SELECT Queries in ClickHouse: How I Retrained My Brain After 10 Years of PostgreSQL

— Editorial Team