Cloud Intelligence™

Mit ClickHouse die Kosten für BigQuery und Looker senken – Teil 2

By Sayle MatthewsJun 30, 202416 min read

Diese Seite ist auch in English, Español, Français, Italiano, 日本語 und Português verfügbar.

Wie geht es weiter

Im vorherigen Teil haben wir besprochen, was dieser Plan eigentlich leisten soll und wie sich ein einfacher ClickHouse-Service mit dem DBaaS-Angebot von Aiven oder ClickHouse aufsetzen lässt. In diesem Abschnitt steigen wir nun in den Prozess ein, die Daten nach ClickHouse zu bringen und die Replikation zwischen ClickHouse und BigQuery einzurichten.

GitHub-Repository

Im Verlauf dieses Artikels verweise ich auf eine Cloud Function und einen BigQuery-Job. Den Quellcode für beide Artefakte finden Sie hier in diesem GitHub-Repository.

Bitte beachten Sie: Der Code ist bewusst sehr einfach gehalten und als Lernbeispiel gedacht. Mit hoher Wahrscheinlichkeit muss er für den produktiven Einsatz in Ihrem Szenario angepasst werden.

Daten aus BigQuery nach ClickHouse bringen

Aktuell gibt es keine automatisierten Verfahren, um CDC oder Streaming aus BigQuery in ein beliebiges Ziel zu betreiben. Das heißt: Daten müssen entweder erfasst werden, bevor sie in BigQuery landen, oder im Nachgang extrahiert werden. Da es keinen offiziellen Weg gibt, ist das Thema sehr umfangreich. Daher behandle ich in diesem Artikel ausschließlich Batch-Loads.

Dazu kommen wir im nächsten Abschnitt. Zunächst müssen wir aber den initialen Datenbestand aus BigQuery nach ClickHouse bringen, um eine Baseline zu schaffen.

BigQuery bietet eine Exportfunktion, die der einfachste Weg ist, um Daten an einen anderen Ort zu bewegen. Sie hat allerdings zwei wesentliche Nachteile: Sie kann jeweils nur eine einzelne Tabelle exportieren, und sie kann ausschließlich GCS als Ziel ansprechen.

Wegen der Beschränkung auf eine Tabelle pro Vorgang ist jetzt ein guter Zeitpunkt, um zu entscheiden, ob wirklich alle Tabellen nach ClickHouse repliziert werden müssen oder welche Teilmenge sich für die Nutzung durch Looker am besten eignet.

Eine schnelle Möglichkeit, alle Tabellen aufzulisten, ist diese Query, die Sie auf Ihr Dataset anwenden – sie liefert Ihnen die komplette Liste. Falls Sie zudem unsicher sind, welche Tabellen besonders häufig genutzt werden, listet Ihnen diese Query die Anzahl der Abfragen pro Tabelle im Dataset auf. Achtung: Diese Abfrage kann erhebliche Kosten verursachen – prüfen Sie also zuerst die Kostenschätzung in der UI und passen Sie ggf. die Anzahl der gescannten Tage an, bevor Sie sie ausführen.

Für den eigentlichen Export ist es am besten, das bq-CLI-Kommando zu verwenden und eine ganze Tabelle zu ziehen. Der Grund: Beim SQL-Befehl "EXPORT DATA…" fallen Verarbeitungs- bzw. Scan-Kosten für die exportierte Datenmenge an – bzw. Slot-Kosten bei Editions. Das CLI-Kommando bzw. der API-Aufruf hingegen exportiert die gesamte Tabelle ohne zusätzliche Kosten.

Wenn Sie nur einen Teil einer Tabelle benötigen – also typischerweise eine Untermenge an Partitionen – gibt es einen Trick mit dem Befehl bq cp: Sie kopieren eine Partition in eine neue Tabelle, die sich dann direkt in ClickHouse laden lässt. Leider unterstützt dieser Befehl weder Wildcards noch mehrere Partitionen gleichzeitig, Sie müssen ihn also pro Partition ausführen. Das lässt sich relativ einfach skripten, der Befehl dazu lautet:

bq cp –append_table=true `<source_project_id>:<source_dataset>.<source_table>$<source_partition_name>` `<target_project_id>.<target_dataset>.<target_table>`

Wenn die Tabelle nicht partitioniert (und nicht riesig) ist, empfehle ich, sie komplett in ClickHouse zu laden und dort per SQL zu bereinigen, damit keine Verarbeitungskosten in BigQuery anfallen.

Wenn Sie für den initialen Export bereit sind, geht es weiter.

Ich werde die Daten im Parquet-Format ausgeben, da es dem Dateisystem von BigQuery am nächsten kommt und sich mit erhaltenen Spaltentypen problemlos in ClickHouse laden lässt.

Der Befehl zum Exportieren einer Tabelle aus BigQuery lautet:

bq extract — destination_format=PARQUET \
<project_id>:<dataset>.<table_name> \
gs://<bucket_name>/<path_and_filename>

(Hinweis: Wenn Sie die Dateien in einen Unterordner ablegen, stellen Sie sicher, dass dieser im Bucket existiert – sonst erhalten Sie eine sehr kryptische Fehlermeldung zu Positional Arguments.)

Sobald eine Tabelle nach GCS exportiert ist, geht es daran, sie in ClickHouse zu laden – und damit setzen wir unseren Weg fort.

Die Initialdaten in ClickHouse laden

Beim Laden in ClickHouse lautet die offizielle SQL-Abfrage (Stand zum Zeitpunkt dieses Artikels):

CREATE TABLE <table_name>
ENGINE = MergeTree
ORDER BY tuple() AS
SELECT *
FROM S3Cluster(default,
  'https://storage.googleapis.com/<bucket_name>/<path>/*.parquet');

Hinweis: In manchen ClickHouse-Versionen gibt es einen Bug, durch den die obige Abfrage fehlschlägt – die Tabelle aber dennoch erstellt wird. Als Workaround führen Sie einfach ein INSERT INTO

gefolgt vom obigen SELECT-Statement aus, dann läuft das Insert durch.

Der empfohlene und sicherere Weg ist die Nutzung eines HMAC-Keys. Stellen Sie nur sicher, dass Sie diesen zuerst für das verwendete Service-Konto am Bucket hinterlegen – das wird häufig vergessen. Diese Keys werden auch für einige der Replikationsmethoden weiter unten in diesem Artikel benötigt, daher empfiehlt es sich, sie jetzt schon zu erzeugen und in den passenden Buckets zu hinterlegen.

Bei Verwendung dieser Keys nutzen Sie in ClickHouse stattdessen folgendes SQL:

CREATE TABLE <table_name>
ENGINE = MergeTree
ORDER BY tuple() AS
SELECT *
FROM s3Cluster(‘default’,
  ‘https://storage.googleapis.com/<bucket_name>/<path>/*.parquet’,
  <hmac_access_key>,
  <hmac_secret>)

In diesen Abfragen steht das Sternchen (*) im Dateinamen dafür, alle Dateien im Verzeichnis mit der Endung parquet zu erfassen. Verwechseln Sie auch keinesfalls einen Service-Account-Key mit einem HMAC-Key – das sind völlig verschiedene Dinge. HMAC-Keys existieren ausschließlich innerhalb von GCS und nicht im IAM-Service von GCP. Wenn Sie zudem keine Dateiendung an Ihre Dateien anhängen, müssen Sie 'Parquet' als Argument an den s3Cluster-Funktionsaufruf übergeben, da sonst möglicherweise ein Fehler auftritt (ein häufiges Problem bei Diensten, die per HMAC-Key auf GCS zugreifen).

Jetzt wird es etwas wild – und genau hier glänzt ClickHouse. Die Zeile mit ENGINE ist einer der Schlüssel zur Performance. Diese werden Table Engines genannt und füllen für sich genommen mindestens eine komplette ClickHouse-Trainingsreihe, daher gehe ich hier nicht im Detail darauf ein. Stattdessen verlinke ich die offizielle ClickHouse-Dokumentation hier.

Beim Laden der Daten müssen Sie die zu Ihrer Nutzung passende Engine wählen. Die MergeTree-Familie bietet hervorragende Allzweck-Storage-Engines mit einigen spezialisierten Varianten – ich empfehle aber dringend, sich auch die anderen Engines anzusehen und Ihre Nutzung mit der jeweils passenden Engine abzugleichen. Das ist einer der – wenn nicht der – Schlüssel zum Performance-Erfolg mit ClickHouse! Das kann gar nicht oft genug betont werden.

Damit bleibt noch ein letzter wichtiger Schritt beim Initial-Load nach ClickHouse: das Löschen der temporären Daten. Zu viele vergessen diesen Schritt und zahlen am Ende für GCS-Speicher, bis ein Audit das später aufdeckt – tappen Sie nicht in diese Falle und kümmern Sie sich jetzt darum, um Geld zu sparen. Oder setzen Sie sich zumindest eine Erinnerung, damit Sie es nicht vergessen.

Generell ist es empfehlenswert, die Dateien in Ihrem GCS-Bucket zu löschen, um Kosten zu sparen. Falls Sie unsicher sind, ob Sie sie für ein späteres erneutes Laden noch brauchen, kann es sinnvoll sein, eine Lifecycle-Regel einzurichten, die die Dateien nach 30 Tagen (oder mehr) automatisch löscht – dann vergessen Sie es garantiert nicht.

Replikationsmuster

Als Nächstes kommt die Replikation der Daten ins Spiel. Welche Methode geeignet ist, hängt davon ab, wie Sie aktuell Daten in BigQuery einspielen. In diesem Artikel zeige ich, wie das mit Batch-Ingestion funktioniert. Streaming behandle ich in einem zukünftigen, bereits geplanten Artikel, da es sich um ein deutlich komplexeres Szenario handelt.

Das andere große Thema: Es gibt keinen One-size-fits-all-Ansatz – alles hängt stark davon ab, wie Ihre Datenpipeline heute aufgebaut ist. Ich werde die häufigsten Ansätze vorstellen, die wir bei DoiT sehen, und es Ihnen als Implementierer überlassen, die beste Strategie für Ihre Organisation zu wählen.

Voraussetzungen für die Replikation

Für all diese Replikationsmethoden gibt es einige Voraussetzungen. Zum Glück sind sie in den meisten Datasets bereits vorhanden, sodass das in der Regel kein Grund zur Sorge ist – der Vollständigkeit halber muss ich sie aber erwähnen.

Voraussetzung 1: Ingestion-Zeitpunkt oder "inkrementelle Marker"

Zu wissen, wo die Replikation begonnen und/oder gestoppt hat, ist hier entscheidend, um doppelte Inserts und damit fehlerhafte Datenzustände zu verhindern. Das kann ein Ingestion-Zeitpunkt sein oder ein eindeutiger "Primary Key" mit irgendeiner Form von inkrementellem Charakter. In BigQuery deckt sich das oft mit einer Partitionsgrenze.

Voraussetzung 2: Bestimmen Sie die Quelle Ihrer Daten

Kurz gesagt: Finden Sie heraus, woher Ihre Daten stammen, bevor sie in BigQuery geladen werden. Häufig liegen sie für eher batchorientierte Daten in GCS oder S3, oder sie werden direkt per Pub/Sub-Subscription nach BigQuery gestreamt, oder sie kommen aus Dataflow/Beam mit einer Transformation. Zu wissen, woher die Daten kommen, ist entscheidend für die Wahl der besten Replikationsmethode.

Voraussetzung 3: Festlegen, wie schnell die Daten benötigt werden

Das läuft auf die Frage hinaus: Brauchen Sie "Echtzeit"-Daten oder reicht eine "eventuelle" Aktualität, die Ihr BI-Tool aus ClickHouse abfragen kann? Wenn Sie sie innerhalb weniger Minuten brauchen, nennen wir das "Echtzeit". Wenn ein Update alle 30 Minuten oder seltener reicht, nennen wir das "eventuell" – und wählen die Strategie entsprechend.

Batch-Replikation einrichten

Die Batch-Replikation der Daten nach ClickHouse erfolgt in einem festen Intervall, um Daten aus der Quelle oder aus BigQuery in ClickHouse zu bekommen. Diese Strategien sind am einfachsten umzusetzen und kommen zum Einsatz, wenn die Daten in Dateien oder einem anderen Batch-Mechanismus vorliegen, bevor sie in BigQuery geladen werden.

Die andere Variante ist, die Daten aus BigQuery nach GCS zu ziehen und sie von dort in ClickHouse zu laden.

Die allererste Methode lädt die in GCS gespeicherten Daten direkt mittels einer Cloud Function nach ClickHouse. Sie funktioniert nur, wenn die Daten bereits in GCS und in einem von ClickHouse unterstützten Format abgelegt sind.

Diese Methode ist sehr einfach und entspricht im Wesentlichen einem Load-Job, wie ihn die meisten Kunden ohnehin schon Richtung BigQuery durchführen.

Die zweite Methode greift, wenn die Daten bereits in BigQuery liegen und von dort zur Nutzung nach ClickHouse repliziert werden müssen. Diese Methode ist nicht ganz so einfach, hat ein paar mehr bewegliche Teile und Kosten und ist daher nicht die bevorzugte Variante.

Wenn Ihre Daten in einem anderen Datastore wie einer MySQL- oder PostgreSQL-Datenbank liegen, der entweder als Zwischenschritt auf dem Weg zu BigQuery dient oder von dem nach BigQuery repliziert wird, dann ist Methode 2 der richtige Weg. Den Prozess für Nicht-BigQuery-Datastore-Szenarien zeige ich weiter unten.

Batch-Replikation mit Daten in GCS vor dem Laden in BigQuery

Diese Methode ist mit Abstand die einfachste – sie umfasst nur wenige Schritte und minimalen Setup-Aufwand.

Im Kern wird hier nichts anderes gemacht, als Ihre Datendateien aus GCS zu nehmen und einen LOAD dieser Dateien in ClickHouse durchzuführen. So einfach ist es zwar (wie immer) nicht zu 100 %, aber ziemlich nah dran – und den Großteil des Codes stelle ich Ihnen schon bereit.

Der einfachste Weg, den ich bisher gefunden habe, ist, in GCS einen Trigger auf das Finalisieren von Dateien im Bucket zu setzen, der eine Cloud Function aufruft, die die Datei nach ClickHouse lädt. Den Prozess dafür finden Sie hier – nutzen Sie den Quellcode für Ihr Deployment, lesen Sie aber zuerst die Dokumentation, damit alle Voraussetzungen erfüllt sind (insbesondere das Anlegen des Secrets, das Ihre Verbindungsdaten enthält).

Es gibt einen Vorbehalt: Sie wollen vermutlich nur Dateien eines bestimmten Typs laden und die Datei je nach Pfad oder Dateiname in eine andere Tabelle schreiben. Diese Logik müssen Sie für Ihren konkreten Use-Case selbst ergänzen – ich habe in der Datei dokumentiert, wo diese Anpassungen vorzunehmen sind.

Batch-Replikation einrichten, wenn die Daten bereits in BigQuery liegen

Als Nächstes geht es um Batch-Replikation in einem Intervall von BigQuery nach ClickHouse. Diese Methoden eignen sich hervorragend, wenn Sie Daten direkt aus einer anderen Quelle in BigQuery laden und sie nicht abgreifen können, bevor sie dort landen. Häufig ist das der Fall bei Streaming-Inserts, Loads über die BigQuery Storage API oder bei Daten, die aus externen Quellen wie Stitch oder FiveTran nach BigQuery geladen werden.

Hinweis zu Kosten: Diese Methode wird die Daten vermutlich in einem Intervall abfragen, was entsprechende Kosten verursacht. Behalten Sie das im Hinterkopf und stellen Sie sicher, dass Sie Ihre Daten in der zugrundeliegenden Tabelle passend partitionieren und/oder clustern, um diese Kosten zu minimieren. Je nach Datenvolumen kann es sich auch lohnen, hier On-Demand-Billing statt Editions zu nutzen. Häufig empfiehlt es sich, ein separates Projekt für die Exporte aus dem Hauptprojekt anzulegen, um workloads sauber zu trennen. Das erlaubt zudem den Einsatz von On-Demand-Billing oder einer separaten Reservierung bei Editions, sodass Sie für günstigere Kosten die Standard Edition nutzen können.

Der grobe Ablauf dieser Methode: Eine Scheduled Query in BigQuery läuft in einem Intervall und exportiert die neuen Daten in einen GCS-Bucket. Sobald die Datei dort vollständig geschrieben ist, löst eine Cloud Function aus, die die Daten nach ClickHouse lädt.

Diese Methode kann sowohl bei Queries und Storage als auch beim Cloud-Function-Aufrufvolumen ins Geld gehen, lädt die Daten aber in einem sehr planbaren Intervall. Wenn Sie die Daten nicht vor BigQuery abgreifen können, ist das aber wahrscheinlich der beste Weg – und spart bei den Query-Kosten weiterhin Geld.

Um die Kosten zu reduzieren, ist es generell am besten, Ihre Daten-Exporte an Partitionen auszurichten, sodass die Partition direkt nach GCS exportiert werden kann (siehe oben den entsprechenden Befehl). Wenn Sie z. B. stündlich partitionieren, planen Sie den Prozess so, dass er die Daten einer ganzen Stunde am Stück abgreifen kann.

Dann tauchen wir ein und schauen uns an, wie das Ganze funktioniert!

Das praktische Beispiel

Für dieses Beispiel habe ich eine sehr einfache Tabelle mit folgendem Schema in BigQuery angelegt:

Sie ist auf transaction_time partitioniert, um in diesem Fall schnellere Abfragen zu ermöglichen.

Daten werden stündlich in diese Tabelle geladen und enthalten alle Daten der letzten Stunde – und sie enthalten alle Daten dieser Stunde, d. h. nichts wird verpasst, das in einem späteren Load nachkommt. Das ist der Einfachheit halber so – in der realen Welt würde es so kaum vorkommen.

Aus Gründen der Einfachheit und als sehr gute Praxis wird beim Abschluss des Loads eine Nachricht in ein Pub/Sub-Topic gepusht. An dieses Topic ist eine Subscription angebunden, die eine Cloud Function triggert, die die Arbeit übernimmt – so spart man sich den Aufbau einer kleinen Pipeline. Alternativ kann beim Abschluss des Datei-Schreibvorgangs eine Cloud Function automatisch ausgelöst werden, was näher an Echtzeit wäre als ein Intervall-Load.

Hier finden Sie den Link zur Cloud Function, die diesen Load durchführt, samt einer Readme-Datei mit der Nutzungsbeschreibung.

Beachten Sie, dass ich darin die Credentials für ClickHouse in einem Secret-Manager-Secret abgelegt habe und diese Werte als Umgebungsvariablen für die Cloud Function bereitstelle. Das ist der einfachste und einer der sichersten Wege, in einer Cloud Function auf Credentials zuzugreifen. Alles dazu ist in der README.md-Datei zum zugehörigen Python-Code dokumentiert.

Der erste Schritt ist, eine Cloud Function anzulegen. Wichtig: Ausgelöst wird sie durch das "finalized"-Event auf einem GCS-Bucket. Ihr Cloud-Function-Setup sollte etwa so aussehen (beachten Sie auch die Warnung zum Service-Konto und vergeben Sie die nötigen Berechtigungen):

Bevor Sie auf Speichern klicken, klappen Sie das Dropdown "Runtime, build, connections and security settings" auf und scrollen zu Security and Image Repo. Oben gibt es einen Bereich "Secrets", in dem Sie für jeden benötigten Wert ein Secret hinterlegen müssen, etwa wie unten gezeigt. Wichtig: Wählen Sie im Dropdown jeweils "Exposed as environment variable", damit diese Cloud Function funktioniert.

Beachten Sie, dass Sie das für jedes von der Cloud Function benötigte Secret tun müssen (host, port, secure, username und password). Port und secure haben Standardwerte 9019 bzw. True – wenn Sie diese nutzen möchten, sind die zugehörigen Secrets nicht erforderlich. Beachten Sie auch die Warnung zum Service-Konto, die ich stehen gelassen habe: Stellen Sie sicher, dass das Service-Konto Lesezugriff auf den Secret Manager hat, bevor Sie fortfahren.

Im nächsten Schritt nutzen Sie diese Query als Scheduled Query (hier die offizielle Dokumentation für alle, die das noch nie gemacht haben) in BigQuery und planen sie so, dass sie jede Stunde 5 Minuten nach der vollen Stunde läuft.

Sobald das eingerichtet ist, warten Sie bis zur eingestellten Zeit und prüfen dann Ihren GCS-Bucket darauf, ob ein neues Verzeichnis mit den Dateien angelegt wurde. Wenn ja, hat Ihre Cloud Function funktioniert.

Dieses Beispiel im realen Einsatz

Das Beispiel ist SEHR einfach und zur Vereinfachung idealisiert. In der Realität wissen wir, dass Daten nie perfekt innerhalb von Stundengrenzen und ohne Verspätung geladen werden. Diese Bedingungen habe ich bewusst weggelassen, denn ClickHouse hat darauf eine gute Antwort: ReplacingMergeTree und CollapsingMergeTree.

Das ClickHouse-Team hat dazu einen sehr guten Artikel hier verfasst, der erklärt, wie sich diese Engines für Updates und Deletes nutzen lassen. Je nach Datennutzungsmuster ist die eine oder die andere die bessere Wahl.

Außerdem ist die Wahrscheinlichkeit hoch, dass Sie zusätzliche Funktionalität ergänzen wollen – Benachrichtigungen, das Auslösen von ETL/ELT-Jobs usw. Ich empfehle daher, am Ende der Cloud-Function-Ausführung zusätzlichen Code einzubauen, der eine Nachricht in Pub/Sub schickt und so nachgelagerte Funktionen anstößt.

Looker an ClickHouse anbinden

Zum Schluss bleibt nur noch, Ihre Looker-Verbindungen so umzustellen, dass sie ClickHouse statt BigQuery nutzen.

Das ist manchmal etwas versteckt: Wechseln Sie in den Admin-Modus von Looker. Sie finden den Punkt unter Database->Connections im linken Bereich des Admin-Modus.

Admin->Database->Connections

Klicken Sie dann einfach auf Hinzufügen und füllen Sie auf der Verbindungsseite die Informationen für Ihren ClickHouse-Host aus.

Wenn Sie auf Connections klicken und als Dialect ClickHouse auswählen, erscheint folgendes Bild:

Looker: Ansicht zum Anlegen einer neuen ClickHouse-Verbindung

Sobald das geladen ist, kopieren Sie die Informationen aus Ihrer Instanz – etwa wie unten aus der Aiven-Konsole:

Verbindungsinformationen aus der ClickHouse-Konsole von Aiven

Sie suchen in der Regel die Angaben zum JDBC-Connector oder zum HTTPS-Zugriff auf ClickHouse.

Sobald das erledigt ist, prüfen Sie über den Test-Button in Looker, ob die Verbindung funktioniert.

Zum Schluss noch ein sehr wichtiger Sicherheitsaspekt: das IP-Whitelisting in Ihrer ClickHouse-Instanz. Diese Liste legt fest, welche IPs sich überhaupt mit Ihrer Instanz verbinden dürfen. Wenn Sie der Anleitung von Google hier folgen, finden Sie eine Reihe von IP-Adressen, die Sie für den Zugriff freigeben können. Damit verbinden sich ausschließlich die Looker-IPs mit Ihrer ClickHouse-Instanz.

Ansicht der freigegebenen IP-Adressen in der Aiven-Konsole

Mit diesem Muster haben Sie eine ClickHouse-Instanz aufgesetzt, auf die Sie aus Looker statt aus BigQuery zugreifen können – und damit potenziell viel Geld bei Ihren Query-Kosten sparen.

Da es sich um ein einfaches Beispiel handelt, ist es vermutlich keine Out-of-the-Box-Lösung für all Ihre Daten-Lade-Anforderungen. Sehen Sie es vielmehr als ersten Trittstein auf Ihrem Weg, Geld zu sparen, indem Sie die Daten in ClickHouse "cachen", statt in BigQuery pro Query zu zahlen.