Chargement de données dans ClickHouse : comment j'ai arrêté d'insérer ligne par ligne et accéléré l'ingestion par 500
Une histoire de désastre : 1000 requêtes par seconde et une panne de production
J'avais un projet — des analytics de paris en temps réel. 50 millions d'événements arrivaient chaque jour. Je pensais que INSERT INTO bets VALUES (...) un événement à la fois était correct. ClickHouse gérait 500 insertions par seconde, mais nous avions besoin de 2000. Le serveur a étouffé : les files d'attente disque ont explosé, et les fusions en arrière-plan ont généré des milliers de petites partitions. La production est tombée.
Il s'avère que ClickHouse n'est pas MySQL. Il est conçu pour les insertions par lots, pas pour les INSERT ponctuels. Après avoir réécrit le chargeur pour regrouper 100 000 enregistrements à la fois, j'ai obtenu 10 000 insertions par seconde sans perte de performance.
Ci-dessous, toutes les méthodes de chargement, des petits CSV aux chargeurs de production, ainsi que les pièges qui m'ont coûté des nuits blanches.
1. Pourquoi une seule ligne est nuisible (même si vous le voulez)
ClickHouse crée une nouvelle partie sur le disque pour chaque INSERT. Les parties contiennent des granules de 8192 lignes, mais même si vous insérez une seule ligne, une nouvelle partie avec de minuscules fichiers .bin est physiquement créée.
Conséquences :
- 10 000
INSERTde 1 ligne chacun = 10 000 parties - Une requête
SELECTdoit ouvrir 10 000 fichiers - Les fusions en arrière-plan tentent de les combiner, ce qui sollicite le CPU
- L'espace disque est gaspillé pour les métadonnées des parties
Règle tirée de mon expérience : un lot doit contenir au moins 1000 lignes ou 1 Mo de données compressées.
2. INSERT ... VALUES — Pour les tests, pas la production
-- Uniquement pour des expériences locales
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');En production, ne faites jamais cela. Même si vous regroupez 1000 lignes dans VALUES, l'analyse SQL devient le goulot d'étranglement.
3. INSERT ... FORMAT CSV / JSONEachRow / TSV — Sauveur pour les fichiers
ClickHouse comprend plusieurs formats à la volée.
Exemple avec un fichier 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,winChargez-le :
INSERT INTO betting.bets FORMAT CSVMais vous devez rediriger les données vers stdin. Donc dans les scripts, utilisez :
clickhouse-client --query="INSERT INTO betting.bets FORMAT CSV" < bets.csvJSONEachRow — idéal pour les 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"}Chargement :
clickhouse-client --query="INSERT INTO betting.bets FORMAT JSONEachRow" < bets.jsonTabSeparated — le plus rapide (moins d'analyse) :
clickhouse-client --query="INSERT INTO betting.bets FORMAT TSV" < bets.tsv4. Chargement via clickhouse-client depuis un fichier — Mon préféré pour les sauvegardes
# Petit fichier
clickhouse-client --query="INSERT INTO betting.bets FORMAT CSV" < /tmp/daily_bets.csv
# Gros fichier avec progression
clickhouse-client \
--query="INSERT INTO betting.bets FORMAT CSV" \
--progress \
--max_insert_block_size=100000 \
< /data/historical_bets_2023.csvOption importante : --max_insert_block_size=100000 — divise le fichier en blocs de 100 000 lignes. Cela permet d'insérer des fichiers de plusieurs téraoctets sans dépassement de mémoire.
Ce que j'ai appris : FORMAT définit la structure des données d'entrée, pas de sortie. Remarquez que la requête elle-même ne contient pas de données ; elles viennent via stdin.
5. API HTTP avec POST — Quand vous n'avez pas accès à la ligne de commande
# 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 à la volée (économise la bande passante)
gzip -c bets.csv | curl -X POST "http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+CSV" \
--data-binary @- \
--header "Content-Encoding: gzip"Pourquoi POST, pas GET : Les requêtes GET sont entièrement journalisées, y compris les données. Si vous insérez via ?query=INSERT..., le mot de passe et les données peuvent se retrouver dans les logs du proxy.
6. INSERT SELECT — Déplacer des données à l'intérieur de ClickHouse
La façon la plus rapide de recharger en masse n'est pas d'exporter à l'extérieur.
-- Copier les données de janvier vers la table d'archive
INSERT INTO betting.bets_archive
SELECT * FROM betting.bets
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
-- Agréger à la volée
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;Vitesse : INSERT SELECT évite l'échange client-serveur ; c'est un transfert local. Un milliard de lignes peut être déplacé en quelques minutes.
7. Insertion asynchrone : quand des milliers d'événements par seconde arrivent des joueurs
L'INSERT classique est synchrone : le client attend que les données soient écrites sur le disque. À 10 000 insertions par seconde, ça fait mal.
async_insert met en mémoire tampon les données et les insère par lots.
Activez-le dans config.xml ou via SETTINGS :
-- Niveau session
SET async_insert = 1;
SET wait_for_async_insert = 0; -- ne pas attendre la fin
SET async_insert_max_data_size = 10000000; -- tampon de 10 Mo
SET async_insert_busy_timeout_ms = 200; -- vider toutes les 200 ms
INSERT INTO betting.bets FORMAT JSONEachRow
{"user_id":1001,"amount":50,"odds":2.1}
{"user_id":1002,"amount":100,"odds":1.8}Comment ça fonctionne :
- Le client envoie de petites insertions
- ClickHouse les accumule dans un tampon mémoire
- Lorsque le tampon est plein (10 Mo) ou que le délai expire (200 ms), une seule insertion en masse se produit
Ce qui m'a brûlé : wait_for_async_insert=0 signifie que le client ne sait pas si l'insertion a échoué. Des problèmes réseau peuvent entraîner une perte de données. Mettez wait_for_async_insert=1 si la fiabilité compte. Les performances chutent, mais pas de manière catastrophique.
8. Paramètres : max_insert_block_size et max_batch_size
<!-- config.xml -->
<max_insert_block_size>1048576</max_insert_block_size> <!-- 1M lignes -->
<max_block_size>65536</max_block_size>max_insert_block_size — la taille maximale de bloc que ClickHouse traite à la fois. Si votre insertion est plus grande, elle est divisée en blocs.
Règle de production : définissez max_insert_block_size à 2 à 4 fois plus petit que votre lot attendu. Ainsi, même un INSERT accidentellement énorme ne tuera pas la mémoire.
# Forcer pour une seule insertion
clickhouse-client --query="INSERT INTO bets FORMAT CSV" \
--max_insert_block_size=500000 < huge_file.csv9. Surveillance des insertions via system.query_log
Ne devinez jamais combien de lignes ont été insérées. Vérifiez system.query_log.
-- Requêtes INSERT récentes
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;Mon tableau de bord de surveillance : affiche les métriques de la dernière heure — taille moyenne des lots, nombre d'insertions échouées, vitesse en lignes/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. Script Python pour le chargement par lots de données historiques
Dans un projet réel, nous avons chargé 3 ans d'historique de paris à partir de fichiers parquet dans S3. Voici un script qui a géré 2 milliards de lignes :
#!/usr/bin/env python3
from clickhouse_driver import Client
import pandas as pd
import glob
from tqdm import tqdm
# Connexion à ClickHouse
client = Client(
host='clickhouse.prod.internal',
port=9000,
user='loader',
password='strong_password',
database='betting'
)
# Optimisations pour les grandes insertions
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 Mo
def insert_batch(df, table='bets'):
"""Insère un DataFrame comme un lot via le protocole Native"""
# clickhouse-driver convertit les types automatiquement
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="Traitement des fichiers"):
# Lecture du parquet par morceaux pour éviter de tout garder en mémoire
for chunk in pd.read_parquet(file, chunksize=batch_size):
# Conversion des types pour 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 morceaux de 500k = 2,5M lignes
df_batch = pd.concat(buffer, ignore_index=True)
insert_batch(df_batch)
buffer = []
# Données restantes
if buffer:
df_batch = pd.concat(buffer, ignore_index=True)
insert_batch(df_batch)
if __name__ == '__main__':
stream_parquet_files()Alternative via requests (sans 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"Échec de l'insertion : {response.text}")
print(f"Fichier inséré : {filepath}")
# Exemple d'envoi par lots de plusieurs fichiers
for file in glob.glob('/data/bets_*.csv.gz'):
insert_via_http_csv_gz(file)Erreurs de chargement courantes (et comment je les ai corrigées)
Erreur : Memory limit (total) exceeded
Cause : Bloc d'insertion trop grand.
Solution : Réduire max_insert_block_size ou diviser le fichier en morceaux.
Erreur : Too many parts
Cause : Trop de petites insertions ou async_insert désactivé sous un débit élevé.
Solution : Activer async_insert, augmenter min_rows_for_wide_part.
Erreur : Code: 252. DB::Exception: Too many simultaneous inserts
Cause : Insertions parallèles dans la même partition.
Solution : Définir max_insert_threads = 1 pour cette table.
Erreur : Les partitions ne fusionnent pas
Cause : Données insérées dans un ordre différent de ORDER BY.
Solution : S'assurer que les données sont triées selon le ORDER BY de la table.
Prochaines étapes
Vous savez maintenant comment charger des données par n'importe quelle méthode — des petits CSV aux flux de production. Le prochain article couvre l'optimisation des requêtes et les agrégations avancées.
Conseil d'adieu : Aucun INSERT ne devrait quitter votre script sans une paire de max_insert_block_size et async_insert si vous travaillez en production. J'ai marché sur ce râteau trois fois — assez.
← Précédente: MergeTree dans ClickHouse : comment le moteur découpe l'analyse en granules et fusionne les parties
→ Suivante: Requêtes SELECT dans ClickHouse : Comment j'ai rééduqué mon cerveau après 10 ans de PostgreSQL
— Editorial Team
Aucun commentaire pour le moment.