Von BigQuery zu ClickHouse: Wie wir unsere Analytics 5× schneller machen konnten

Warum funktionierte BigQuery nicht mehr für uns?

Über Jahre hinweg stellten wir unseren Kunden umfassende Analytics für ihre Alarmierungen, Benachrichtigungen und Bereitschaftsaktivitäten bereit. So erhielten sie einen umfassenden Überblick darüber, wie ihre Teams und Services auf Incidents reagieren.

Diese Funktionen basierten auf einer separaten analytischen Datenbank, die auf Google BigQuery lief. Darin lagen die Zahlen hinter jedem Reporting-Dashboard in ilert. Und lange Zeit war das völlig ausreichend.

Dann wurden drei Probleme zu groß, um sie weiter ignorieren zu können:

• Es war die langsamste Anwendung auf unserer Plattform. Analytics-Dashboards liegen zwar nicht auf dem kritischen Pfad von ilert, waren aber spürbar träge: im Durchschnitt mehrere Sekunden und bei unseren größten Kunden bis zu zwanzig Sekunden. Das war nicht die Leistung, die wir bieten wollten.
• Wir zahlten pro Abfrage. BigQuery rechnet nach gescannten Bytes ab. Unser Workload besteht jedoch aus denselben wenigen Dashboards, die immer wieder ausgeführt werden. Dadurch zahlten wir wieder und wieder dafür, Fragen zu beantworten, die wir bereits gestellt hatten.
• Das Aktualisieren einzelner Zeilen war schwierig. Einige unserer Analytics sind nicht append-only. So ändern sich zum Beispiel der Status einer Alarmierung sowie die TTA (Time to Accept) und die TTR (Time to Resolve), wenn die Alarmierung bestätigt und gelöst wird. Die gespeicherte Zeile muss diese Änderungen entsprechend abbilden. BigQuery ist auf Appends und vollständige Neuschreibungen ausgelegt. Row-Level-Updates sind dort langsam und umständlich.

Und es gab einen zusätzlichen Bonus: BigQuery war zufälligerweise der letzte Workload, der uns noch auf Google Cloud hielt. Durch die Ablösung konnten wir einen ganzen Provider und zugleich einen Subprozessor aus unserem Tech Stack entfernen.

Also haben wir unsere Analytics auf ClickHouse umgestellt, betrieben auf unserer eigenen AWS-Infrastruktur. Abfragen, die früher rund zehn Sekunden dauerten, liefern ihre Ergebnisse jetzt in unter zwei Sekunden. Und so gingen wir vor:

Unser Workload

Unser Analytics-Workload besteht hauptsächlich aus:

1. Events: die rohen Alarmierungs-Events, die ilert aufnimmt und verarbeitet. Ein hochvolumiger, append-only Stream und die Quelldaten, auf denen alles Weitere aufbaut.

2. Statistiken: die Aggregationen hinter unseren Kernfunktionen: Alarmierungen, Benachrichtigungen und Bereitschaftsberichte

3. Entity state transitions: die Historie, wie sich unsere Kernentitäten durch ihren Lifecycle bewegen: Call Flows, Event Flows und Incidents. Wir protokollieren, was im Zeitverlauf passiert ist, wobei jede Transition als eigene, klar getrennte Zeile gespeichert wird: eine Zeile pro Entität und Änderung.

Diese drei Workloads prägten sowohl die oben genannten Probleme als auch das folgende Design. Was wir brauchten, war eine schnelle, spaltenorientierte Datenbank, optimiert für mandantenbezogene Lesezugriffe über bestimmte Zeiträume hinweg, mit einer Table Engine, die auch Mutationen verarbeiten kann. Auf der Shortlist standen außerdem Apache Druid, Apache Pinot, StarRocks und TimescaleDB. ClickHouse passte jedoch am besten zu unserem Zugriffsmuster. 

Und genauso wichtig: Es war kein riskantes Unterfangen. Wir hatten das Projekt seit Jahren verfolgt und beobachtet, wie es immer ausgereifter wurde. ClickHouse hat sich im Lauf der Zeit als etablierte Lösung für große Unternehmen wie Uber, Cloudflare und Cisco durchgesetzt.

Warum nicht einfach BigQuery optimieren?

Das ist eine berechtigte Frage und wir stellten sie uns als Erstes. BigQuery bietet echte Lösungen für langsame interaktive Dashboards: BI Engine, Reservations, Materialized Views. Jede dieser Optionen hätte bei der Geschwindigkeit geholfen, wahrscheinlich auch bei den Kosten. Aber jede davon hätte uns auf Google Cloud gehalten. Zu diesem Zeitpunkt war BigQuery unser einziger verbleibender GCP-Workload. Alles andere lief bereits auf unserer eigenen AWS-Infrastruktur.

Eine Optimierung von BigQuery hätte zwei unserer drei Probleme gelöst, gleichzeitig aber einen zweiten Cloud-Provider und einen zweiten Subprozessor dauerhaft in unserem Stack verankert. Den Workload stattdessen zu migrieren, ermöglichte uns, Geschwindigkeit und Kosten zu verbessern und zwei Provider auf einen zu reduzieren.

Das gab den Ausschlag: migrieren, anstatt zu optimieren.

Datenaufnahme mit Kafka als Zwischenschicht

Wie in der Branche üblich, sind unsere operativen und analytischen Datenspeicher entkoppelt und nur eventual consistent, mit einem zwischengeschalteten Buffer. Kafka nutzten wir bereits für diesen Buffer, daher war es auch die naheliegende Wahl, um Daten in ClickHouse einzuspeisen.

Der Realtime-Flow:

1. Source Services besitzen ihre operativen Daten weiterhin selbst, so wie bisher.
2. Ein dedizierter CDC-Service (Change Data Capture) liest Change Events und exportiert sie in Kafka Topics: ein logischer Stream pro Workload.
3. Consumer Worker lesen aus Kafka, bündeln die Zeilen clientseitig in Batches und schreiben sie in ClickHouse.

Der Backfill-Flow nutzte denselben Pfad, allerdings mit einer anderen Quelle:

1. Ein Export-Job liest die historischen BigQuery-Tabellen nacheinander aus und streamt die Zeilen in ein Kafka Topic.
2. Ein Backfill Consumer bündelt diese Zeilen in Batches und schreibt sie in ClickHouse, genauso wie es die Realtime Consumer tun.

Diese Consumer Worker mussten vor allem eines gut können: Zeilen effizient in ClickHouse schreiben. ClickHouse ist am schnellsten bei großen, weniger häufigen Inserts und langsam bei vielen kleinen. Deshalb bündelt jeder Consumer die Zeilen clientseitig in Batches und schreibt sie, sobald eines von zwei Kriterien erreicht ist: ein definierter Größenwert oder ein kurzer Timer.

Der Kafka Offset wird erst committet, nachdem der Batch erfolgreich geschrieben wurde. Offset und Insert bewegen sich gemeinsam. Andernfalls könnte ein Crash dazu führen, dass Zeilen bestätigt werden, die nie in der Tabelle angekommen sind.

Warum der Weg über Kafka statt Dual-Writing?

• Entkopplung: Die Source Services wissen nicht und müssen auch nicht wissen, dass ClickHouse existiert. Wenn der analytische Datenspeicher wegen Wartungsarbeiten nicht verfügbar ist, werden Events in Kafka in eine Queue geschrieben. Upstream wird dadurch nichts blockiert.
• Replayability: Während einer Migration kann es jederzeit passieren, dass ein Schema oder eine Transformation nicht auf Anhieb stimmt. Da der Stream in Kafka vorgehalten wird, können wir Offsets zurücksetzen und die Daten erneut in eine korrigierte Tabelle einlesen, statt jeden Source Service aufzufordern, seine Historie noch einmal zu senden.
• Backfill und Cutover parallel: Wir haben historische Daten in ClickHouse nachgeladen, während der Live-Stream weiterlief. Sobald beide Datenstände übereinstimmten, haben wir die Reads umgestellt.
• Natürliche Batching-Grenze: ClickHouse ist am schnellsten, wenn Daten in großen Batches und mit weniger Schreibvorgängen eingefügt werden. Ein Kafka Consumer ist der ideale Ort, um einen Batch aufzubauen und ihn dann zu flushen.

Erstellung des ClickHouse Schemas

Eine der wichtigsten Entscheidungen bei der Einführung von ClickHouse ist die Wahl der Table Engine. Im Kern läuft sie auf eine Frage hinaus: Können die Daten verändert werden und wenn ja, auf welche Weise? Neben der Engine folgen unsere Tabellen einigen gemeinsamen Konventionen. Jede Tabelle ist über den Cluster hinweg geshardet und zur Ausfallsicherheit repliziert. Sie ist nach Monaten partitioniert und erst nach Tenant, dann nach Zeit sortiert. So greift eine Datumsbereichsabfrage eines Tenants auf den kleinstmöglichen Datenausschnitt zu. Die Spalten werden mit typgerechten Codecs komprimiert. Außerdem hat jede Tabelle eine TTL, damit Daten gemäß ihrem jeweiligen Retention-Zeitplan automatisch entfernt werden, statt unbegrenzt weiter anzuwachsen.

Bei der Analyse unserer Use Cases gab es einen Fall, der eindeutig Mutability erfordert:

• Alert Analytics: Wenn sich eine Alarmierung von ausgelöst über bestätigt bis gelöst bewegt, fügen wir sie einfach erneut mit ihrem neuen Status ein. ClickHouse behält die neueste Version und entfernt die älteren beim Merge. Reads fragen den aktuellen Status mit FINAL ab, wodurch diese alten Versionen zur Query-Zeit entfernt werden. Das ist ReplicatedReplacingMergeTree.

Mit Blick auf den nötigen Backfill haben wir uns jedoch entschieden, die „Replacing“-Engine auch für alle anderen migrierten Tabellen zu verwenden.

Warum diese Struktur das sichere Beheben von Migrationsproblemen ermöglicht

Datenmigrationen sind selten sauber. Fast immer merkt man mitten im Prozess, dass eine Transformation subtil fehlerhaft war, und muss den gesamten Backfill noch einmal ausführen. Bei einer einfachen append-only Tabelle bedeutet ein erneuter Lauf, dass jede bereits eingefügte Zeile ein zweites Mal geschrieben wird. Das erneute Einlesen des Backfills wird dadurch zu einer Aufgabe nach dem Muster: Tabelle leeren und von vorn beginnen. Und das ist jedes Mal riskant.

Die „Replacing“-Engine ändert das. Da sie anhand des vollständigen ORDER BY-Tuples dedupliziert und die Zeile mit der höchsten Version Column behält, ersetzt das erneute Einlesen derselben logischen Zeilen aus dem Stream diese einfach. Zeilen, die beim ersten Mal fehlten, werden dabei ergänzt.

So ist das erneute Einlesen des Backfills keine Aufräumaktion mehr, sondern ein sicher wiederholbarer Prozess: Consumer zurücksetzen, laufen lassen, und die Tabelle landet im korrekten Zustand. Nichts wird dupliziert, nichts muss geleert werden, und FINAL liefert die bereinigte Sicht, noch bevor die Background Merges aufgeholt haben.

FINAL ist nicht kostenlos. Für uns ist der Overhead aber akzeptabel, weil keine der APIs, die auf diesen Tabellen basieren, auf einem Hot Path der Plattform liegt. In der Praxis ist der Mehraufwand daher vernachlässigbar.

Der knifflige Teil: die Queries portieren

Das Anlegen der Tabellen war der einfache Teil. Die analytischen Queries von BigQuerys SQL-Dialekt auf den von ClickHouse zu portieren, erforderte deutlich mehr Sorgfalt. Die gefährlichen Bugs waren dabei nicht die, die einen Fehler auslösten. Es waren die, die eine absolut plausible Zahl zurückgaben, die trotzdem falsch war.

Selbst mit Unterstützung von AI Assistants brauchten wir mehrere Iterationen, um jede Query sauber zu übertragen. Dabei haben wir uns auf unsere Testdaten gestützt, damit die neuen Queries funktional identisch zu den alten sind.

Fallstricke waren zum Beispiel:

• %M steht für den Monat, nicht für Minute: In formatDateTime gibt %M den vollständigen Monatsnamen aus, während %i für Minute steht. Schließlich verwendeten wir die bereitgestellten Shortcuts: %F = %Y-%m-%d, %T = %H:%i:%S 
• Nicht aggregierte Spalten: BigQuery erlaubt es, eine Spalte per SELECT abzufragen, die nicht im GROUP BY enthalten ist, solange sie funktional davon abhängig ist. ClickHouse lehnt das ab. Fügt man die Spalte einfach zum GROUP BY hinzu, werden Zeilen stillschweigend aufgeteilt und führen zu Double-Counting, sobald diese Spalte innerhalb der Gruppe variiert. Die richtige Lösung ist, sie mit Aggregaten wie any() oder min() zu umschließen.
• Indexing beginnt bei 1: Arrays und Tuples sind in ClickHouse 1-basiert. BigQuerys [OFFSET(0)] für das erste Element wird zu [1], und Tuple-Felder werden als t.1, t.2 angegeben. 
• Durchschnitt über „übersprungene“ Zeilen berechnen: In unseren Daten bedeutet eine TTA oder TTR von 0, dass die Alarmierung nie bestätigt oder gelöst wurde, nicht dass sie null Sekunden gedauert hat. Ein einfaches AVG würde diese Nullen mitzählen und den Wert nach unten ziehen. Deshalb mussten wir folgenden Ausdruck verwenden: COALESCE(ROUND(AVG(CASE WHEN x != 0 THEN x END)), 0).‍

Welche Kompromisse sind wir eingegangen?

ClickHouse selbst zu Hosten bringt eine eigene Komplexität mit sich, die wir bewusst in Kauf genommen haben:

• Wir betreiben ClickHouse jetzt selbst: Replikation, ZooKeeper, Server-Upgrades, Backups, Capacity Planning — all die Dinge, die BigQuery im Hintergrund übernommen hat, liegen heute bei unserem Team. Wir haben uns bewusst dafür entschieden. Das ist der Preis dafür, Performance kontrollieren und auf einen einzigen Provider konsolidieren zu können.
• Wir dimensionieren für Peak-Zeiten: Es gibt keine serverlose Elastizität. Der Cluster ist für unsere besonders ausgelasteten Zeiten vorgesehen und bleibt in der übrigen Zeit weniger stark genutzt. Für einen so vorhersehbaren Workload wie unseren ist das trotzdem günstiger als die Abrechnung pro Query.
  

Die Ergebnisse

Die Kennzahl, die für uns am wichtigsten war, hat sich genau so verbessert, wie wir es uns erhofft hatten: Die p95-Latenz der Dashboard-Queries sank von rund zehn Sekunden auf unter zwei Sekunden. ‍Das entspricht einer Beschleunigung um etwa das 5-Fache.

Alles Weitere ergab sich aus dem Design:

• Kosten: Die Abrechnung nach gescannten Daten pro Query entfällt und Stattdessen haben wir nun fixe, planbare Kosten für einen Cluster, der auf Hardware läuft, die wir ohnehin betreiben.
• Schlankeres Schema: Da wir das Alert-State-Feld über ReplacingMergeTree aktualisieren können, konnten wir auf ein kompakteres Tabellenlayout umstellen, statt die Append-and-Rewrite-Workarounds zu nutzen, die BigQuery erforderte.
• Operative Resilienz: Da Kafka die Ingestion puffert, ist ein ClickHouse-Wartungsfenster eine Queue, die später abgearbeitet wird, kein Datenverlust. Und eine fehlerhafte Transformation ist ein Replay, kein Incident.
• Eine Abhängigkeit weniger: BigQuery war unser letzter Workload auf GCP. Durch die Ablösung konnten wir einen kompletten Cloud-Provider aus unserem Tech Stack entfernen.
  

Alles in allem sind wir mit der Migration sehr zufrieden. Die oben genannten Verbesserungen waren das Ziel. Der größere Gewinn liegt aber darin, was sie möglich machen: Mit schnellen, bezahlbaren Realtime-Analytics als neuer Baseline lassen sich umfangreichere Reports und tiefere Insights plötzlich kosteneffizient umsetzen. 

In Kürze werden daher deutlich mehr Analytics-Funktionen auf der ilert-Plattform verfügbar sein.