Spezielle ClickHouse-Engines: Wenn MergeTree nicht benötigt wird

Spezielle ClickHouse-Engines: Wenn MergeTree nicht passt

1. Memory ENGINE – RAM-Tabelle für temporäre Daten

Stellen Sie sich vor, Sie müssen schnell einen Batch von Wetten verarbeiten – gruppieren, Zwischensummen berechnen und dann an die Haupttabelle senden. Sie möchten nicht auf die Festplatte schreiben, weil die Daten temporär sind und nur für die Dauer der Abfrage benötigt werden.

Memory ENGINE speichert Daten vollständig im RAM. Es ist die schnellste Engine – keine Festplattenoperationen, keine Komprimierung, keine Indizes (außer dem Primärschlüssel). Aber es gibt einen Nachteil: Wenn ClickHouse neu startet, wird die Tabelle leer. Daten werden NICHT persistiert.

Wann verwenden:

• Staging-Tabellen für ETL-Prozesse. Zum Beispiel haben Sie eine Million Wetten aus Kafka geladen, dedupliziert und erst dann in die Haupt-MergeTree-Tabelle eingefügt.
• Cache für Live-Quoten – Quoten ändern sich jede Sekunde, keine Notwendigkeit, Verlauf zu speichern, nur der aktuelle Snapshot wird benötigt.
• Kleine Nachschlagetabellen (bis zu 10–15 Millionen Zeilen), die bei jedem Skriptlauf neu erstellt werden.

Beispiel für Live-Quoten-Cache:

-- Tabelle für aktuelle Quoten (lebt im RAM)
CREATE TABLE live_odds_cache
(
    event_id     UInt64,          -- Ereignis-ID (Spiel)
    market_id    UInt32,          -- Markt-ID
    selection_id UInt32,          -- Auswahl-ID
    odds         Decimal(10,3),   -- Quote
    updated_at   DateTime
)
ENGINE = Memory()
ORDER BY (event_id, market_id, selection_id);   -- ORDER BY ist obligatorisch, aber der Index ist ineffizient

Daten einfügen (z.B. aus einem Stream):

-- Neue Quote eingetroffen, einfügen
INSERT INTO live_odds_cache VALUES (100500, 10, 200, 1.85, now());

-- Aktuelle Quote für eine Wette lesen
SELECT odds FROM live_odds_cache 
WHERE event_id = 100500 AND market_id = 10 AND selection_id = 200;

Fallstricke:

• Memory-Tabelle unterstützt keine Merges – wenn Sie viele UPDATEs durchführen (via Insert mit Stornierung), bläht sich der Speicher auf. Verwenden Sie TRUNCATE zum Löschen.
• Bei einem Neustart von ClickHouse gehen Daten verloren. Speichern Sie hier nichts Kritisches.
• Die Tabellengröße ist durch den verfügbaren RAM begrenzt. Wenn die Tabelle auf einem Server mit 64 GB RAM auf 50 GB anwächst, stürzt der Server ab.

Analogie: Memory ENGINE ist wie ein Whiteboard. Schnell zu beschreiben, schnell zu lesen, aber nach dem Putzmann (Neustart) ist die Tafel leer.

2. Buffer ENGINE – INSERTs vor dem Schreiben puffern

Sie haben 10.000 Wetten pro Sekunde. Jede Wette ist ein einzelner INSERT. Wenn Sie jeden direkt in eine MergeTree-Tabelle schreiben, erstellt ClickHouse Tausende winziger Teile, was Hintergrund-Merges verlangsamt und die Leistung beeinträchtigt.

Buffer ENGINE löst das: Es sammelt Einfügungen in einem Speicherpuffer und schreibt sie in großen Batches in die Zieltabelle, wenn Bedingungen erfüllt sind (nach Zeilenanzahl, Größe oder Zeit).

Syntax mit Parametern:

CREATE TABLE bets_buffer AS bets   -- kopiert die Struktur der bets-Tabelle
ENGINE = Buffer(
    'default',        -- Name der Zieltabelle-Datenbank
    'bets',           -- Name der Zieltabelle (Daten werden hierhin gespült)
    16,               -- Anzahl paralleler Flush-Threads
    10,               -- minimale Verzögerung in Sekunden (min_time)
    100,              -- maximale Verzögerung in Sekunden (max_time)
    10000,            -- minimale Anzahl Zeilen für Flush
    1000000,          -- maximale Anzahl Zeilen für Flush
    10000000,         -- minimale Größe in Bytes für Flush
    100000000         -- maximale Größe in Bytes für Flush
);

Flush-Parameter:

Parameter	Wert	Bedeutung
min_time	10 Sek	Nicht früher als 10 Sekunden spülen
max_time	100 Sek	Spätestens nach 100 Sekunden spülen
min_rows	10.000	Wenn 10k Zeilen angesammelt, kann gespült werden
max_rows	1.000.000	Wenn 1 Million Zeilen angesammelt, dringend spülen
min_bytes	10 MB	Wenn 10 MB angesammelt, kann gespült werden
max_bytes	100 MB	Wenn 100 MB angesammelt, dringend spülen

Wie es in der Praxis funktioniert:

1. Sie fügen in bets_buffer ein (schnell, nur Schreiben in den Speicher).
2. ClickHouse wartet, bis genügend Daten angesammelt sind (z.B. 100k Zeilen oder 30 Sekunden vergangen).
3. Dann spült es asynchron (im Hintergrund) den Batch in die Haupttabelle bets (MergeTree).
4. Dadurch erhält bets große Teile (100k Zeilen), was Hintergrund-Merges beschleunigt.

Warum nicht direkt in MergeTree einfügen? Jeder INSERT in MergeTree erzeugt ein Mini-Teil. Wenn Sie 10.000 INSERTs pro Sekunde durchführen, haben Sie nach einer Minute 600.000 Teile. Der Hintergrund-Merge kann nicht mithalten. ClickHouse beschwert sich über Too many parts, und Einfügungen werden langsamer.

Fallstrick: Bei einem Neustart von ClickHouse geht der Puffer verloren. Daten, die noch nicht in bets gespült wurden, verschwinden. Verwenden Sie Buffer ENGINE also nur, wenn der Verlust einiger Sekunden Daten akzeptabel ist (z.B. für Analysen, nicht für Kontostände).

3. Null ENGINE – Schwarzes Loch für Daten

Null ENGINE absorbiert Daten einfach. Es wird nirgendwo geschrieben, nicht gespeichert, nicht indiziert. Aber es gibt einen Trick: Wenn eine Tabelle mit Null ENGINE Materialized Views hat, erhalten diese Views die Daten und verarbeiten sie.

Muster: Kafka → Null + Materialized View → MergeTree

Dies ist eine klassische Architektur für hochlastige Einfügungen aus Kafka.

-- Schritt 1: Senken-Tabelle (schwarzes Loch)
CREATE TABLE bets_null
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = Null;   -- speichert nichts

-- Schritt 2: Ziel-Tabelle (wo wir tatsächlich speichern)
CREATE TABLE bets
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, user_id);

-- Schritt 3: Materialized View (Brücke)
CREATE MATERIALIZED VIEW bets_mv TO bets AS
SELECT * FROM bets_null;   -- alle Daten, die in bets_null gehen, landen in bets

Was jetzt passiert:

-- Client (oder Kafka-Konsument) fügt in bets_null ein
INSERT INTO bets_null VALUES (123, 100.00, now());   -- sofort

-- Daten durchlaufen den Materialized View und werden in bets gespeichert
-- Sie werden nicht in bets_null selbst gespeichert

Warum das tun?

• bets_null ist eine sehr leichte Tabelle; sie erstellt keine Dateien auf der Festplatte.
• Alle Abonnenten (Materialized Views) empfangen Daten gleichzeitig.
• Sie können mehrere Materialized Views an eine Null-Tabelle anhängen: einen für Rohdaten in MergeTree, einen für Aggregate in AggregatingMergeTree, einen für Deduplizierung in ReplacingMergeTree.

Analogie: Null ENGINE ist wie ein Briefkasten mit einem Loch im Boden. Briefe fallen hinein, bleiben aber nicht. Aber alle Ihre Sekretäre (Materialized Views) schaffen es, sie zu lesen und in ihre Ordner zu kopieren.

4. Log/TinyLog/StripeLog – Einfache Engines für kleine Datenmengen

Diese Familie von Engines ist für kleine Tabellen (bis zu 1–2 Millionen Zeilen), bei denen hohe Leistung und Indizes nicht benötigt werden.

Engine	Eigenschaften	Wann verwenden
TinyLog	Eine Datei pro Spalte	Sehr kleine Tabellen (<100k Zeilen), Staging
Log	Jede Spalte in einer separaten Datei, hat einen Marker für paralleles Lesen	Tabellen bis zu 1 Million Zeilen, schnelles Lesen erforderlich
StripeLog	Alle Spalten in einer Datei (kompakt)	Platzersparnis, seltenes Lesen

Beispiel für eine Liga-Nachschlagetabelle (200 Zeilen):

-- Fußball-Liga-Nachschlagetabelle (ändert sich einmal im Monat)
CREATE TABLE leagues_ref
(
    league_id   UInt32,
    name        String,
    country     String,
    updated_at  Date
)
ENGINE = TinyLog();   -- so einfach wie möglich, kein ORDER BY

Warum nicht MergeTree? MergeTree erstellt Indizes, Partitionen, Komprimierung – das ist für 200 Zeilen übertrieben. TinyLog benötigt weniger Platz und ist einfacher zu warten.

Fallstrick: Diese Engines unterstützen kein ALTER DELETE und ALTER UPDATE. Wenn Sie Daten ändern müssen, müssen Sie die Tabelle neu erstellen.

5. URL ENGINE – Tabelle als HTTP-Endpunkt

URL ENGINE ermöglicht es, Daten direkt von einer HTTP-Quelle (API) zu lesen und sogar Daten per PUT einzufügen.

CREATE TABLE currency_rates_url
(
    base   String,
    rate   Decimal(10,4),
    date   Date
)
ENGINE = URL('https://api.exchangerate.com/latest?base=USD', CSV)
SETTINGS 
    method = 'GET',
    format = 'CSV',
    headers = 'Authorization: Bearer token123';

Verwendung:

-- Aktuelle Kurse direkt von der API lesen
SELECT * FROM currency_rates_url;

Reales Szenario: Eine kleine Analyseaufgabe, bei der Sie kein ETL einrichten möchten. Zum Beispiel lesen Sie einmal pro Stunde Wechselkurse von einer kostenlosen API, verknüpfen sie mit Wetten und berechnen Beträge neu.

Fallstricke:

• Keine Indizes; jede Abfrage führt einen vollständigen Scan der Quelle durch.
• Wenn die API einen Fehler zurückgibt, schlägt die Abfrage fehl.
• Nicht geeignet für hochlastige Abfragen (die Annahme ist, dass Daten in ClickHouse zwischengespeichert werden, nicht jedes Mal von der API gelesen).

6. File ENGINE – Tabelle als Datei auf der Festplatte

Ermöglicht das Lesen und Schreiben von Dateien im lokalen Dateisystem des ClickHouse-Servers. Unterstützt Formate CSV, TSV, JSONEachRow, Parquet.

-- Tabelle, die eine CSV-Datei liest
CREATE TABLE imported_players
(
    user_id UInt64,
    username String
)
ENGINE = File(CSV, '/var/lib/clickhouse/user_files/players.csv');

Wann verwenden:

• Laden von Daten aus Dateien (Admin hat eine CSV mit neuen Benutzern abgelegt).
• Exportieren von Abfrageergebnissen in eine Datei via INSERT INTO ... SELECT.

Fallstrick: ClickHouse muss Zugriff auf den Ordner haben (normalerweise /var/lib/clickhouse/user_files/ aus Sicherheitsgründen).

7. S3 ENGINE – Direkte Abfragen an S3

Liest Daten direkt von einem Amazon S3-Bucket (oder MinIO, Yandex Object Storage). Kopiert keine Daten nach ClickHouse.

CREATE TABLE logs_s3
(
    timestamp DateTime,
    message String
)
ENGINE = S3(
    'https://mybucket.s3.amazonaws.com/logs/*.parquet',
    'AWS_ACCESS_KEY', 'AWS_SECRET_KEY',
    'Parquet'
);

Wann verwenden:

• Sie haben Terabytes an Logs in S3 und möchten gelegentlich Analyseabfragen durchführen, ohne sie nach ClickHouse zu kopieren.
• Kalte Daten (S3 ist günstiger als ClickHouse-Festplatten).

Fallstrick: Jede Abfrage lädt Daten von S3 herunter, was langsam und teuer sein kann (für ausgehenden Traffic). Nur für seltene Abfragen geeignet.

8. PostgreSQL ENGINE – Live-Daten aus PostgreSQL

PostgreSQL ENGINE ermöglicht das Lesen und Schreiben in PostgreSQL-Tabellen, als ob es ClickHouse-Tabellen wären.

CREATE TABLE pg_players
(
    user_id UInt64,
    balance Decimal(18,2)
)
ENGINE = PostgreSQL(
    'postgres-host:5432',   -- Host und Port
    'betting',               -- Datenbank
    'players',               -- Tabelle in PostgreSQL
    'clickhouse_user',       -- Benutzer
    'password'               -- Passwort
);

Verwendung:

-- Aktuelle Kontostände aus PostgreSQL lesen
SELECT * FROM pg_players WHERE user_id = 123;

-- Sie können sogar mit ClickHouse-Tabellen JOINen
SELECT b.user_id, b.amount, p.balance
FROM bets b
JOIN pg_players p ON b.user_id = p.user_id;

Wann verwenden:

• Sie migrieren schrittweise von PostgreSQL zu ClickHouse, und einige Daten leben noch in der alten DB.
• Sie benötigen Live-Daten, die von einer externen Anwendung aktualisiert werden, und möchten kein ETL einrichten.

Fallstricke:

• Jede Abfrage geht zu PostgreSQL, was bei großen Datenmengen langsam sein kann.
• ClickHouse kann keinen effizienten Ausführungsplan für eine solche Tabelle erstellen (kein Pushdown).

9. Architekturmuster für Wetten: Kafka → Buffer → MergeTree

Lassen Sie uns nun alles zusammenführen. Stellen Sie sich vor, Sie empfangen 10.000+ Nachrichten pro Sekunde aus Kafka für jedes Wettereignis. Sie müssen sie mit minimaler Latenz in ClickHouse speichern, ohne Tausende winziger Teile zu erzeugen.

Fertige Architektur:

-- 1. Senken-Tabelle (Null) für Kafka
CREATE TABLE bets_kafka
(
    user_id     UInt64,
    event_id    UInt64,
    amount      Decimal(18,2),
    bet_time    DateTime
)
ENGINE = Null;

-- 2. Ziel-MergeTree-Tabelle
CREATE TABLE bets
(
    user_id     UInt64,
    event_id    UInt64,
    amount      Decimal(18,2),
    bet_time    DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(bet_time)
ORDER BY (bet_time, user_id);

-- 3. Buffer-Tabelle zum Glätten von Einfügungen
CREATE TABLE bets_buffer AS bets
ENGINE = Buffer('default', 'bets', 16, 5, 60, 10000, 1000000, 10000000, 100000000);

-- 4. Materialized View: Kafka → Null → Buffer (via View)
CREATE MATERIALIZED VIEW bets_kafka_mv TO bets_buffer AS
SELECT * FROM bets_kafka;

-- 5. Ein weiterer View für Echtzeit-Aggregate (optional)
CREATE MATERIALIZED VIEW bets_stats_mv TO bets_hourly_agg AS
SELECT 
    toStartOfHour(bet_time) AS hour,
    countState() AS bet_count,
    sumState(amount) AS total_amount
FROM bets_kafka
GROUP BY hour;

Datenfluss:

1. Kafka Connect fügt Nachrichten in bets_kafka ein (Null ENGINE).
2. bets_kafka_mv (Materialized View) leitet Daten an bets_buffer weiter.
3. bets_buffer sammelt Batches im Speicher (z.B. 100k Zeilen oder 60 Sekunden).
4. Der Puffer spült in großen Teilen in bets (MergeTree).
5. Parallel dazu erstellt der zweite Materialized View stündliche Aggregate für Dashboards.

Warum dies optimal ist:

• Kafka schreibt in Null (sofort, kein Overhead).
• Buffer verhindert, dass sich Tausende winziger Teile vermehren.
• MergeTree erhält große Teile, Merges arbeiten effizient.
• Aggregate werden in Echtzeit über den zweiten View erstellt.

Was passiert, wenn Sie direkt von Kafka in MergeTree schreiben: Bei 10k Ereignissen/Sekunde werden in einer Minute 600k Teile erstellt. ClickHouse stürzt mit Too many parts ab. Buffer ENGINE bewahrt Sie davor.

Wann welche Engine wählen – Spickzettel

Aufgabe	Engine	Warum
Dauerhafte Speicherung, Analysen	MergeTree (oder *MergeTree)	ClickHouse-Grundlage, Indizes, Komprimierung
Puffern hoher Lasten	Buffer	Klebt kleine INSERTs zu großen Teilen zusammen
Temporäre Daten (Session, Staging)	Memory	Maximale Geschwindigkeit, Daten nicht kritisch
Kafka-Konsument ohne Speicherung	Null + MV	Daten nur für Views
Kleine statische Nachschlagetabelle	TinyLog / Log	Einfachheit, weniger Metadaten
Verbindung zu PostgreSQL	PostgreSQL ENGINE	Live-Daten ohne ETL
Seltene Abfragen an S3	S3 ENGINE	Günstiger Kalt-Speicher
Import aus Dateien	File ENGINE	Einmaliger Kopiervorgang

Was kommt als Nächstes

Sie haben spezielle Engines kennengelernt, die Probleme jenseits von regulärem MergeTree lösen. Jetzt wissen Sie:

• Memory für Cache und Staging,
• Buffer zum Schutz vor zu häufigen INSERTs,
• Null zum „Verzweigen“ von Daten in mehrere Streams via MV,
• URL / File / S3 / PostgreSQL für externe Daten.

Nächste Themen zum Vertiefen:

• Wie man Kafka Engine einrichtet – eingebauter Connector zu Kafka ohne separaten Connector.
• Fortgeschrittene Materialized Views – View-Ketten für komplexes ETL.
• Distributed Tables – wie man Daten über Server shardet.

Fazit: Nicht alle Aufgaben in ClickHouse werden mit MergeTree gelöst. Manchmal braucht man Buffer, um den Server nicht mit Einfügungen zu überlasten, manchmal Null + MV, um Daten auf verschiedene Aggregate zu verteilen, und manchmal Memory für temporäres Hashing. Die Hauptregel: Entwerfen Sie zuerst den Datenfluss, dann wählen Sie die Engine, nicht umgekehrt.

---

← Vorherige: Wörterbücher in ClickHouse: Schnelle Lookups ohne JOIN
→ Nächste: Materialisierte Sichten in ClickHouse: Die Macht der inkrementellen Verarbeitung

— Editorial Team