Ładowanie danych do ClickHouse: jak przestałem wstawiać pojedyncze wiersze i przyspieszyłem przyjmowanie 500 razy
Miałem projekt – analityka zakładów w czasie rzeczywistym. Dziennie przychodziło 50 mln zdarzeń. Uznałem, że INSERT INTO bets VALUES (...) dla każdego zdarzenia to norma. ClickHouse utrzymywał 500 wstawień na sekundę, a potrzebowaliśmy 2000. Serwer się zakrztusił: kolejki dyskowe poszybowały w kosmos, a tła mergów wygenerowały tysiące małych części. Produkcja padła.
Okazało się, że ClickHouse to nie MySQL. Jest stworzony do wsadowego wstawiania, a nie do punktowych INSERT. Przepisując ładowarkę na paczki po 100 000 rekordów, uzyskałem 10 000 wstawień na sekundę bez utraty wydajności.
Poniżej – wszystkie sposoby ładowania, od małych CSV po produkcyjne batchy, z pułapkami, które kosztowały mnie nerwy.
1. Dlaczego jeden wiersz to zło (nawet jeśli chcesz)
ClickHouse przy każdym INSERT tworzy nową część na dysku. Części zawierają granulki po 8192 wierszy, ale nawet jeśli wstawisz 1 wiersz – fizycznie tworzy się nowa część z malutkimi plikami .bin.
Konsekwencje:
- 10 000
INSERTpo 1 wierszu = 10 000 części - Zapytanie
SELECTmusi otworzyć 10 000 plików - Tło merge próbuje je skleić, obciążając CPU
- Miejsce na dysku jest marnowane na metadane części
Zasada z mojego doświadczenia: paczka powinna mieć co najmniej 1000 wierszy lub 1 MB skompresowanych danych.
2. INSERT ... VALUES – do testów, nie na produkcję
-- Tylko do lokalnych eksperymentów
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');Na produkcji tak nie wolno. Nawet batchując po 1000 wierszy w VALUES – parsowanie SQL stanie się wąskim gardłem.
3. INSERT ... FORMAT CSV / JSONEachRow / TSV – ratunek dla plików
ClickHouse rozumie kilka formatów w locie.
Przykład z plikiem CSV 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Ładujemy:
INSERT INTO betting.bets FORMAT CSVAle trzeba podać dane na stdin. Dlatego w skryptach używa się:
clickhouse-client --query="INSERT INTO betting.bets FORMAT CSV" < bets.csvJSONEachRow – idealny do logów:
{"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"}Ładowanie:
clickhouse-client --query="INSERT INTO betting.bets FORMAT JSONEachRow" < bets.jsonTabSeparated – najszybszy (mniej parsowania):
clickhouse-client --query="INSERT INTO betting.bets FORMAT TSV" < bets.tsv4. Ładowanie przez clickhouse-client z pliku – mój ulubiony sposób do backupów
# Mały plik
clickhouse-client --query="INSERT INTO betting.bets FORMAT CSV" < /tmp/daily_bets.csv
# Ogromny plik z postępem
clickhouse-client \
--query="INSERT INTO betting.bets FORMAT CSV" \
--progress \
--max_insert_block_size=100000 \
< /data/historical_bets_2023.csvWażna flaga: --max_insert_block_size=100000 – dzieli plik na bloki po 100k wierszy. Pozwala to wstawiać terabajtowe pliki bez przepełnienia pamięci.
Co wyniosłem: FORMAT określa strukturę danych wejściowych, a nie wyjściowych. Zwróć uwagę – zapytanie samo w sobie nie zawiera danych, przychodzą one przez stdin.
5. HTTP API z POST – gdy nie ma dostępu do wiersza poleceń
# CSV przez 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 w locie (oszczędność transferu)
gzip -c bets.csv | curl -X POST "http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+CSV" \
--data-binary @- \
--header "Content-Encoding: gzip"Dlaczego POST, a nie GET: Zapytania GET są w pełni logowane, włączając dane. Jeśli wstawiasz przez ?query=INSERT..., hasło i dane mogą trafić do logów proxy.
6. INSERT SELECT – przenoszenie danych wewnątrz ClickHouse
Najszybszy sposób masowego przeładowania – nie eksportować na zewnątrz.
-- Skopiuj dane za styczeń do tabeli archiwalnej
INSERT INTO betting.bets_archive
SELECT * FROM betting.bets
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
-- Agreguj w locie
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;Szybkość: INSERT SELECT odbywa się bez komunikacji klient-serwer, tylko lokalny transfer. Miliard wierszy przenosi się w minutach.
7. Async insert: gdy tysiące zdarzeń na sekundę od graczy
Klasyczny INSERT jest synchroniczny: klient czeka, aż dane zapiszą się na dysk. Przy 10 000 wstawień na sekundę to boli.
async_insert buforuje dane w pamięci i wstawia paczkami.
Włączamy w config.xml lub przez SETTINGS:
-- Na poziomie sesji
SET async_insert = 1;
SET wait_for_async_insert = 0; -- nie czekaj na zakończenie
SET async_insert_max_data_size = 10000000; -- 10 MB bufor
SET async_insert_busy_timeout_ms = 200; -- zrzut co 200 ms
INSERT INTO betting.bets FORMAT JSONEachRow
{"user_id":1001,"amount":50,"odds":2.1}
{"user_id":1002,"amount":100,"odds":1.8}Jak to działa:
- Klient wysyła małe wstawienia
- ClickHouse odkłada je do bufora w pamięci
- Gdy bufor jest pełny (10 MB) lub upłynie limit czasu (200 ms), następuje jedno wsadowe wstawienie
Na czym się sparzyłem: wait_for_async_insert=0 oznacza, że klient nie wie, czy wstawienie się powiodło. Przy problemach sieciowych można stracić dane. Ustaw wait_for_async_insert=1, jeśli ważna jest niezawodność. Wydajność spada, ale nie katastrofalnie.
8. Ustawienia max_insert_block_size i max_batch_size
<!-- config.xml -->
<max_insert_block_size>1048576</max_insert_block_size> <!-- 1M wierszy -->
<max_block_size>65536</max_block_size>max_insert_block_size – maksymalny rozmiar bloku, który ClickHouse przetwarza za jednym razem. Jeśli twoje wstawienie jest większe – jest cięte na bloki.
Zasada z produkcji: ustaw max_insert_block_size 2–4 razy mniejsze niż oczekiwana paczka. Wtedy nawet przypadkowo ogromny INSERT nie zabije pamięci.
# Wymuszenie dla jednego wstawienia
clickhouse-client --query="INSERT INTO bets FORMAT CSV" \
--max_insert_block_size=500000 < huge_file.csv9. Monitorowanie wstawień przez system.query_log
Nigdy nie zgaduj, ile wierszy się wstawiło. Sprawdź system.query_log.
-- Ostatnie zapytania INSERT
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;Mój dashboard do monitorowania: pokazuję metryki z ostatniej godziny – średni rozmiar paczki, liczba nieudanych wstawień, prędkość wierszy/s.
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. Skrypt Python do wsadowego ładowania danych historycznych
W prawdziwym projekcie ładowaliśmy 3 lata historii zakładów z parkietów w S3. Oto skrypt, który wytrzymał 2 mld wierszy:
#!/usr/bin/env python3
from clickhouse_driver import Client
import pandas as pd
import glob
from tqdm import tqdm
# Połączenie z ClickHouse
client = Client(
host='clickhouse.prod.internal',
port=9000,
user='loader',
password='strong_password',
database='betting'
)
# Optymalizacje dla dużych wstawień
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'):
"""Wstawienie DataFrame paczką przez protokół Native"""
# ClickHouse-driver sam konwertuje typy
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"):
# Czytamy parquet kawałkami, aby nie trzymać wszystkiego w pamięci
for chunk in pd.read_parquet(file, chunksize=batch_size):
# Dopasowujemy typy do 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 kawałków po 500k = 2.5M wierszy
df_batch = pd.concat(buffer, ignore_index=True)
insert_batch(df_batch)
buffer = []
# Pozostałe dane
if buffer:
df_batch = pd.concat(buffer, ignore_index=True)
insert_batch(df_batch)
if __name__ == '__main__':
stream_parquet_files()Alternatywa przez requests (bez 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}")
# Przykład wsadowego wysyłania wielu plików
for file in glob.glob('/data/bets_*.csv.gz'):
insert_via_http_csv_gz(file)Typowe błędy przy ładowaniu (i jak je naprawiałem)
Błąd: Memory limit (total) exceeded
Przyczyna: zbyt duży blok wstawienia.
Rozwiązanie: zmniejsz max_insert_block_size lub potnij plik na kawałki.
Błąd: Too many parts
Przyczyna: zbyt wiele małych INSERT lub wyłączony async_insert przy wysokim strumieniu.
Rozwiązanie: włącz async_insert, zwiększ min_rows_for_wide_part.
Błąd: Code: 252. DB::Exception: Too many simultaneous inserts
Przyczyna: równoległe wstawienia do tej samej partycji.
Rozwiązanie: ustaw max_insert_threads = 1 dla tej tabeli.
Błąd: partycje się nie scalają
Przyczyna: dane są wstawiane niezgodnie z ORDER BY.
Rozwiązanie: upewnij się, że dane są posortowane tak samo jak ORDER BY w tabeli.
Co dalej
Umiesz ładować dane dowolnym sposobem – od małych CSV po produkcyjne strumienie. Następny artykuł – o optymalizacji zapytań i zaawansowanych agregacjach.
Główna rada na pożegnanie: żaden INSERT nie powinien opuścić twojego skryptu bez pary max_insert_block_size i async_insert, jeśli pracujesz z produkcją. Na tę pułapkę nadepnąłem trzy razy – wystarczy.
← Poprzedni: MergeTree w ClickHouse: jak silnik kroi analitykę na granulki i scala części
→ Następny: Zapytania SELECT w ClickHouse: jak przestawiałem mózg po 10 latach PostgreSQL
— Editorial Team
Brak komentarzy.