Daten in ClickHouse laden: Wie ich aufhörte, Zeile für Zeile einzufügen und die Aufnahme um das 500-fache beschleunigte
Eine Katastrophengeschichte: 1000 Abfragen pro Sekunde und ein Produktionsausfall
Ich hatte ein Projekt – Echtzeit-Wettanalysen. Täglich gingen 50 Millionen Ereignisse ein. Ich dachte, INSERT INTO bets VALUES (...) ein Ereignis nach dem anderen sei in Ordnung. ClickHouse verarbeitete 500 Inserts pro Sekunde, aber wir brauchten 2000. Der Server erstickte: Die Warteschlangen der Festplatte schossen in die Höhe, und Hintergrund-Merges erzeugten Tausende winziger Parts. Die Produktion fiel aus.
Es stellte sich heraus, dass ClickHouse nicht MySQL ist. Es ist für Bulk-Inserts gebaut, nicht für einzelne INSERTs. Nachdem ich den Lader umgeschrieben hatte, um 100.000 Datensätze auf einmal zu bündeln, erreichte ich 10.000 Inserts pro Sekunde ohne Leistungseinbußen.
Im Folgenden finden Sie alle Lademethoden, von kleinen CSVs bis hin zu produktionsreifen Batchern, zusammen mit den Fallstricken, die mir schlaflose Nächte bereitet haben.
1. Warum eine einzelne Zeile böse ist (selbst wenn man es möchte)
ClickHouse erstellt für jedes INSERT ein neues Part auf der Festplatte. Parts enthalten Granulen von 8192 Zeilen, aber selbst wenn Sie nur eine Zeile einfügen, wird physisch ein neues Part mit winzigen .bin-Dateien erstellt.
Konsequenzen:
- 10.000
INSERTs mit je 1 Zeile = 10.000 Parts - Eine
SELECT-Abfrage muss 10.000 Dateien öffnen - Hintergrund-Merges versuchen, sie zu kombinieren, was die CPU belastet
- Festplattenspeicher wird für Part-Metadaten verschwendet
Regel aus meiner Erfahrung: Ein Batch sollte mindestens 1000 Zeilen oder 1 MB komprimierte Daten umfassen.
2. INSERT ... VALUES – Für Tests, nicht für die Produktion
-- Nur für lokale Experimente
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 der Produktion sollten Sie dies niemals tun. Selbst wenn Sie 1000 Zeilen in VALUES bündeln, wird das SQL-Parsing zum Engpass.
3. INSERT ... FORMAT CSV / JSONEachRow / TSV – Lebensretter für Dateien
ClickHouse versteht mehrere Formate direkt.
Beispiel mit CSV-Datei 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,winLaden:
INSERT INTO betting.bets FORMAT CSVAber Sie müssen die Daten an stdin weiterleiten. Verwenden Sie in Skripten daher:
clickhouse-client --query="INSERT INTO betting.bets FORMAT CSV" < bets.csvJSONEachRow – ideal für 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"}Laden:
clickhouse-client --query="INSERT INTO betting.bets FORMAT JSONEachRow" < bets.jsonTabSeparated – am schnellsten (weniger Parsing):
clickhouse-client --query="INSERT INTO betting.bets FORMAT TSV" < bets.tsv4. Laden über clickhouse-client aus Datei – Mein Favorit für Backups
# Kleine Datei
clickhouse-client --query="INSERT INTO betting.bets FORMAT CSV" < /tmp/daily_bets.csv
# Riesige Datei mit Fortschrittsanzeige
clickhouse-client \
--query="INSERT INTO betting.bets FORMAT CSV" \
--progress \
--max_insert_block_size=100000 \
< /data/historical_bets_2023.csvWichtiges Flag: --max_insert_block_size=100000 – teilt die Datei in Blöcke von 100.000 Zeilen auf. Dies ermöglicht das Einfügen von terabytegroßen Dateien ohne Speicherüberlauf.
Was ich gelernt habe: FORMAT definiert die Struktur der Eingabedaten, nicht der Ausgabe. Beachten Sie, dass die Abfrage selbst keine Daten enthält; sie kommen über stdin.
5. HTTP-API mit POST – Wenn Sie keinen Zugriff auf die Befehlszeile haben
# CSV per 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 (spart Bandbreite)
gzip -c bets.csv | curl -X POST "http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+CSV" \
--data-binary @- \
--header "Content-Encoding: gzip"Warum POST, nicht GET: GET-Anfragen werden vollständig protokolliert, einschließlich der Daten. Wenn Sie über ?query=INSERT... einfügen, können das Passwort und die Daten in Proxy-Logs landen.
6. INSERT SELECT – Daten innerhalb von ClickHouse verschieben
Der schnellste Weg zum Massen-Neuladen ist nicht der Export nach außen.
-- Januardaten in die Archivtabelle kopieren
INSERT INTO betting.bets_archive
SELECT * FROM betting.bets
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
-- On-the-fly aggregieren
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;Geschwindigkeit: INSERT SELECT vermeidet den Client-Server-Austausch; es ist eine lokale Übertragung. Milliarden von Zeilen können in Minuten verschoben werden.
7. Async Insert: Wenn Tausende von Ereignissen pro Sekunde von Spielern kommen
Klassisches INSERT ist synchron: Der Client wartet, bis die Daten auf die Festplatte geschrieben sind. Bei 10.000 Inserts pro Sekunde tut das weh.
async_insert puffert Daten im Arbeitsspeicher und fügt sie in Batches ein.
Aktivieren Sie es in config.xml oder über SETTINGS:
-- Sitzungsebene
SET async_insert = 1;
SET wait_for_async_insert = 0; -- nicht auf Abschluss warten
SET async_insert_max_data_size = 10000000; -- 10 MB Puffer
SET async_insert_busy_timeout_ms = 200; -- alle 200 ms leeren
INSERT INTO betting.bets FORMAT JSONEachRow
{"user_id":1001,"amount":50,"odds":2.1}
{"user_id":1002,"amount":100,"odds":1.8}So funktioniert es:
- Der Client sendet kleine Inserts
- ClickHouse sammelt sie in einem Speicherpuffer
- Wenn der Puffer voll ist (10 MB) oder das Timeout abläuft (200 ms), erfolgt ein einzelner Bulk-Insert
Was mich verbrannt hat: wait_for_async_insert=0 bedeutet, dass der Client nicht weiß, ob das Insert fehlgeschlagen ist. Netzwerkprobleme können zu Datenverlust führen. Setzen Sie wait_for_async_insert=1, wenn Zuverlässigkeit wichtig ist. Die Leistung sinkt, aber nicht katastrophal.
8. Einstellungen: max_insert_block_size und max_batch_size
<!-- config.xml -->
<max_insert_block_size>1048576</max_insert_block_size> <!-- 1M Zeilen -->
<max_block_size>65536</max_block_size>max_insert_block_size – die maximale Blockgröße, die ClickHouse auf einmal verarbeitet. Wenn Ihr Insert größer ist, wird es in Blöcke aufgeteilt.
Produktionsregel: Setzen Sie max_insert_block_size auf das 2- bis 4-fache kleiner als Ihr erwarteter Batch. So kann selbst ein versehentlich riesiges INSERT den Speicher nicht killen.
# Für ein einzelnes Insert erzwingen
clickhouse-client --query="INSERT INTO bets FORMAT CSV" \
--max_insert_block_size=500000 < huge_file.csv9. Überwachung von Inserts über system.query_log
Raten Sie nie, wie viele Zeilen eingefügt wurden. Überprüfen Sie system.query_log.
-- Aktuelle INSERT-Abfragen
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;Mein Überwachungs-Dashboard: Zeigt Metriken für die letzte Stunde – durchschnittliche Batch-Größe, Anzahl fehlgeschlagener Inserts, Zeilen/Sekunde-Geschwindigkeit.
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-Skript für Batch-Laden historischer Daten
In einem echten Projekt haben wir 3 Jahre Wettverlauf aus Parquet-Dateien in S3 geladen. Hier ist ein Skript, das 2 Milliarden Zeilen verarbeitet hat:
#!/usr/bin/env python3
from clickhouse_driver import Client
import pandas as pd
import glob
from tqdm import tqdm
# Mit ClickHouse verbinden
client = Client(
host='clickhouse.prod.internal',
port=9000,
user='loader',
password='strong_password',
database='betting'
)
# Optimierungen für große 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'):
"""DataFrame als Batch über Native-Protokoll einfügen"""
# clickhouse-driver konvertiert Typen automatisch
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="Verarbeite Dateien"):
# Parquet in Chunks lesen, um nicht alles im Speicher zu halten
for chunk in pd.read_parquet(file, chunksize=batch_size):
# Typen für ClickHouse umwandeln
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 à 500k = 2,5 Mio. Zeilen
df_batch = pd.concat(buffer, ignore_index=True)
insert_batch(df_batch)
buffer = []
# Restliche Daten
if buffer:
df_batch = pd.concat(buffer, ignore_index=True)
insert_batch(df_batch)
if __name__ == '__main__':
stream_parquet_files()Alternative über requests (ohne 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 fehlgeschlagen: {response.text}")
print(f"{filepath} eingefügt")
# Beispiel für das Batch-Senden mehrerer Dateien
for file in glob.glob('/data/bets_*.csv.gz'):
insert_via_http_csv_gz(file)Häufige Ladefehler (und wie ich sie behoben habe)
Fehler: Memory limit (total) exceeded
Ursache: Insert-Block zu groß.
Lösung: Reduzieren Sie max_insert_block_size oder teilen Sie die Datei in Stücke.
Fehler: Too many parts
Ursache: Zu viele kleine INSERTs oder async_insert bei hohem Durchsatz deaktiviert.
Lösung: Aktivieren Sie async_insert, erhöhen Sie min_rows_for_wide_part.
Fehler: Code: 252. DB::Exception: Too many simultaneous inserts
Ursache: Parallele Inserts in dieselbe Partition.
Lösung: Setzen Sie max_insert_threads = 1 für diese Tabelle.
Fehler: Partitionen werden nicht zusammengeführt
Ursache: Daten außerhalb der ORDER BY-Reihenfolge eingefügt.
Lösung: Stellen Sie sicher, dass die Daten genauso sortiert sind wie das ORDER BY der Tabelle.
Was kommt als Nächstes
Sie wissen jetzt, wie Sie Daten mit jeder Methode laden – von kleinen CSVs bis hin zu Produktions-Streams. Der nächste Artikel behandelt Abfrageoptimierung und erweiterte Aggregationen.
Abschiedsratschlag: Kein INSERT sollte Ihr Skript ohne ein Paar max_insert_block_size und async_insert verlassen, wenn Sie mit der Produktion arbeiten. Ich bin drei Mal auf diesen Rechen getreten – genug.
← Vorherige: MergeTree in ClickHouse: Wie die Engine Analysen in Granula schneidet und Teile zusammenführt
→ Nächste: SELECT-Abfragen in ClickHouse: Wie ich mein Gehirn nach 10 Jahren PostgreSQL umtrainierte
— Editorial Team
Noch keine Kommentare.