Einleitung
Im ersten Teil dieses Artikels wurden Unterschiede zwischen Snowflake und BigQuery als Data Warehouses aufgezeigt. Dabei wurde erklärt, wie Speicher und Rechenkapazität in beiden Systemen gemanagt werden müssen. In diesem Teil soll darauf eingegangen werden, wie benutzerfreundlich die Lösungen sind und wie weitere Aufgaben gelöst werden können: Wie funktionieren Snowflake und BigQuery als Data Platforms? Dies wird zusätzlich anhand von Case Studies besprochen.
Usability
BigQuery und Snowflake bieten verschiedene Optionen für die Interaktion. Es existieren Client Libraries für die gängigen Sprachen. Außerdem können Command Line Interfaces (CLI) und grafische Benutzeroberflächen für beide Systeme genutzt werden.
Graphical User Interface
Auf der grafischen Benutzeroberfläche von BigQuery öffnen sich häufig Fenster und Tabs aus verschiedenen Richtungen, sodass der nutzbare Bildschirm immer weiter schrumpft. An einigen Stellen ist das Verhalten unerwartet, Tabs ersetzen einander oder öffnen sich plötzlich in einem geteilten View. Es passiert schnell, dass eine unübersichtliche Anzahl an Tabs für Queries, Details von Tabellen (mit Subtabs) und Details von Datasets geöffnet sind. Ein Klick auf weitere Features wie Transfer Jobs hat zur Folge, dass sich alle Tabs schließen. Query History und Query Performance öffnen sich in zusätzlichen Unterfenstern. Insgesamt gibt es noch viel Raum für eine verbesserte User Experience. BigQuery testet häufig neue Features, sodass es sein kann, dass es hier noch Änderungen gibt.
Snowflake verwendet Workbooks, in denen sowohl Queries als auch administrative Aufgaben wie das Starten von Warehouses oder die Verwaltung von Berechtigungen erledigt werden kann. Diese werden automatisch persistiert und können geteilt werden. Der Wechsel zu anderen Fenstern wie Query History und Query Profile ist einfach und die Anzeigen sind detailliert und übersichtlich.
Die grafische Benutzeroberfläche von Snowflake integriert alle relevanten Features auf eine kompakte und übersichtliche Weise, die Produktivität verbessert und es ermöglicht, Arbeitsschritte zu dokumentieren und zu wiederholen. Im Gegensatz dazu ist die grafische Oberfläche von BigQuery ungeeignet, um Prozesse abzubilden und zu dokumentieren.
Siehe Appendix für weitere Details zur grafischen Benutzeroberfläche.
Scripting
Sessions, Stored Procedures und Funktionen können in beiden Systemen genutzt werden. Dennoch ist ihre Verwendung in Snowflake durch Workbooks viel natürlicher. In Kombination mit den zusätzlichen Objekten wie Tasks und Streams können innerhalb von Snowflake komplexe Prozesse abgebildet werden, die das Management von Data Lifecycle, Data Quality und Change Data Capture betreffen.
In BigQuery werden diese Prozesse meist nicht mit SQL gemanagt, sondern eher ausgelagert unter Verwendung von weiteren Services wie Dataflow, IAM, BigQuery Transfer Service, Terraform etc.
Access
BigQuery arbeitet mit Google Cloud Identity and Access Management (IAM), also rollenbasierten Zugriff. Rollen können mit feingranulären Berechtigungen geschaffen werden, die dann an Personen und Gruppen vergeben werden. Zudem kann Zugriff auf Tabellen und Datensätze an beliebige Google Accounts vergeben werden. Datensätze können auch öffentlich zur Verfügung gestellt oder im Analytics Hub mit Personen innerhalb und außerhalb der Organisation geteilt werden.
In Snowflake sind Rollen hierarchisch strukturiert und können Berechtigungen erhalten. Verschiedene Snowflake-Objekte können gleichermaßen als Securable Objects behandelt werden: Eine Rolle ist immer Objekt-Eigentümer und kann anderen Rollen Berechtigungen auf das Objekt erteilen. Das gilt beispielsweise für Tabellen, Datenbanken, Virtual Warehouses, Stored Procedures aber auch Nutzer und Rollen. Nutzer müssen die geeignete Rolle auswählen, wenn mehrere zur Verfügung stehen. Im Gegensatz dazu hat in Google Cloud ein Nutzer immer die Summe seiner Berechtigungen. Wenn in BigQuery spezifische Berechtigungen verwendet werden sollen, so können dafür Dienstkonten (Service Accounts) verwendet werden. Über den Snowflake Data Marketplace können Datenbanken mit Accounts in derselben Region veröffentlicht und geteilt werden. Leserkonten ermöglichen Datenzugriff für Personen, die keine Snowflake Kunden sind.
Data Import
Beide Lösungen haben diverse Optionen, Daten zu laden. So können Daten direkt über SQL geladen werden, Dateien in diversen Formaten können hochgeladen werden oder auch als externe Datenquelle in einem Object Storage von Google, AWS oder Azure genutzt werden. Im Fall von BigQuery können auch Google Sheets als externe Datenquelle dienen.
BigQuery Transfer Service ermöglicht das Laden von Daten von diversen Quellen als geplanten serverlosen Batch Job. Das ist möglich für diverse Google Dienste (z.B. Google Ads, Google Analytics, YouTube, Google Play), Query Jobs und externe Datenquellen wie Redshift und Teradata. Dabei werden automatisch diverse Tabellen und Views angelegt. Zudem kann Google Cloud Logging so konfiguriert werden, dass eine beliebige Auswahl der erzeugten Logs nach BigQuery geschrieben wird.
Mit Google Dataflow Templates können typische Batch- und Streaming-Jobs innerhalb von Google Cloud abgebildet werden. Der Nutzer muss lediglich die Transformationslogik als User Defined Function (UDF) übergeben.
In Snowflake kann eine ähnliche Funktionalität über Snowpipe und Tasks abgebildet werden. Diese können direkt innerhalb von Workbooks definiert und gemanagt werden. Zudem werden neue Dateien immer in Stage-Objekte geladen, bevor in Tabellen geschrieben wird. Diese stellen eine vereinheitlichte Schnittstelle für Daten aus verschiedenen Quellen dar und vereinfachen das Management des Datenimports.
Integration & Data Platform
Als Data Warehouse Lösung von Google Cloud ist BigQuery mit allen relevanten Services der Plattform integriert und weiterhin mit einigen Google Services außerhalb von Google Cloud. Google Sheets Connected Sheets können verwendet werden, um Daten einer Tabelle oder Query in einer Excel-artigen Umgebung zu verarbeiten. Transformationen werden von BigQuery ausgeführt (Pushdown) – allerdings hat das zur Folge, dass eine effiziente Nutzung nur möglich ist, wenn der zusätzliche Service BI Engine für die genutzten Daten aktiviert ist. Andernfalls ist die Latenz zu groß. Gleiches gilt für Looker Studio, den Business Intelligence Service von Google.
BigQuery ist gut integriert mit Google Diensten und auch weiteren 3rd-Party Anwendungen. Wenn aber eine größere Unabhängigkeit von Cloud Providern erreicht werden soll, ist Snowflake offensichtlich die bessere Wahl: Snowflake ist breit aufgestellt bei der Integration mit 3rd-Party Anwendungen. Mit Client Libraries für die Snowflake Snowpark API können Operationen auf den Daten serverseitig ausgeführt und optimiert werden (Pushdown). Die Syntax ist vergleichbar mit funktionaler Programmierung, Spark oder Pandas. [12, 13]
Als Data Platform gewinnt Snowflake den Vergleich: Die meisten Aufgaben können direkt und zentral über die Workbooks erledigt werden, ohne in eine andere Applikation zu wechseln. BigQuery wird erst zur Data Platform, wenn weitere Google Cloud Dienste verwendet werden. Die starke Integration von BigQuery in Google Cloud hat Vor- und Nachteile: IAM, Dataflow und Pub/Sub können nicht nur für die Data Platform, sondern auch für viele weitere Anwendungen genutzt werden. Auf der anderen Seite führt das dazu, dass verschiedene Dienste häufig gemeinsam eingesetzt, betrieben und weiterentwickelt werden müssen.
Snowflake hat zunächst zwar eine höhere Komplexität durch mehr Features und Kontrolle – allerdings können einzelne Anwendungen wiederum eine deutlich geringere Komplexität und bessere Wartbarkeit haben.
Dementsprechend kann die Entscheidung für Snowflake oder BigQuery basierend auf der Relevanz der Data Platform geschehen: BigQuery ist die Lösung für Unternehmen, die ein Data Warehouse als Teil ihres Cloud-Betriebes benötigen und gemeinsam mit anderen Cloud Services managen wollen. Snowflake hingegen sollte von Unternehmen genutzt werden, für die die Data Platform das zentrale Business darstellt.
Case Studies
Wie wirkt sich die Wahl von BigQuery oder Snowflake nun in der Praxis aus? Im Folgenden sollen einige Szenarien betrachtet werden und wie diese typischerweise gelöst werden können.
Web Data ETL
Ein Medienunternehmen hat eine große Menge an Server-Log Daten aus unterschiedlichen Quellen und möchte anhand von Cookies und weiteren Metadaten verschiedene Key Performance Indicators (KPIs) von Kampagnen bestimmen. Die Server-Logs sind bereits im Parquet Format im Data Lake in Cloud Storage verfügbar und sollen nun mittels eines täglichen Batch Jobs verarbeitet werden, sodass die KPIs auslesbar sind. Dazu ist es nötig, informative Daten zu filtern und Aggregationen und Joins auszuführen. Verschiedene Zwischenergebnisse des Prozesses können für weitere Jobs und Analysen genutzt werden. Es muss davon ausgegangen werden, dass sich das Schema der Quelldaten unerwartet ändert und Datenlieferungen mitunter mehrere Tage zu spät erfolgen. Daten, die älter als zwei Jahre sind, sollen gelöscht werden.
BigQuery
Für die Orchestrierung des Batch Jobs ist Cloud Composer das richtige Tool: Cloud Composer ist ein Google Cloud Service, der Apache Airflow managt. Hier können voneinander abhängige Arbeitsschritte über mehrere Dienste hinweg geplant werden. Der erste Schritt ist die Überprüfung der Vollständigkeit und Qualität der Quelldaten. Dazu kann ein BigQuery Operator verwendet werden, der beliebige Tests (Queries) auf den Daten ausführt. Falls die Vorbedingungen nicht erfüllt sind, scheitert der Job und eine Nachricht wird in einen Slack Channel und an eine E-Mail gesendet. Sobald das Problem an den Quelldaten behoben wurde, kann der Job zu einem späteren Zeitpunkt manuell über Airflow erneut gestartet werden. Für die Verarbeitung der Daten wird als weiterer Schritt ein Apache Spark Cluster in Dataproc gestartet. Dieses liest und verarbeitet die Daten, schreibt Zwischen- und Endergebnisse nach BigQuery und wird anschließend wieder gestoppt. Alternativ kann auch Dataflow oder (in weniger komplexen Fällen) mehrere BigQuery Jobs verwendet werden. Wenn nötig, können auch weitere Prozesse mittels Airflow abgebildet werden, beispielsweise das Kopieren von Daten aus externen Quellen nach Cloud Storage.
Da die Airflow Jobs wiederholbar sein sollen, muss sichergestellt werden, dass sie idempotent sind. Dazu müssen vor dem Laden der Daten eventuell bereits für diesen Tag vorhandene gelöscht werden. Im Fall von größeren Tabellen lässt sich dies einfach durch eine Partitionierung der Tabelle anhand des Zeitstempels erreichen. Mittels WRITE_TRUNCATE wird beim Laden immer die aktuell geschriebene Partition überschrieben.
Zudem können die Tabellen konfiguriert werden, sodass Partitionen nach zwei Jahren automatisch gelöscht werden.
Snowflake
In Snowflake kann grundsätzlich das gleiche Setup verwendet werden. Um die Idempotenz der Airflow Tasks zu gewährleisten, können neue Daten als Datei in einen Stagingbereich (Stage Objekt) geschrieben werden. Mittels den Objekten Tasks und Stored Procedures können Routinen definiert werden, die abgelaufene Mikropartitionen löschen.
Alternativ ist es auch möglich, sowohl die Orchestrierung als auch den ELT Prozess innerhalb von Snowflake abzubilden: Wie in Airflow können Snowflake Tasks als voneinander abhängige Prozesse gestaltet werden. Tasks können dann Stored Procedures nutzen, um komplexe Transformationen durchzuführen.
Laden von Log Daten
Ein E-Commerce Unternehmen hat Server Logs von verschiedenen Webshops, die nach Cloud Storage exportiert werden. Diese sollen nun im Data Warehouse zur weiteren Analyse für Search Engine Optimization (SEO) und Operations zur Verfügung gestellt werden. SEO benötigt nur Logs, die von Crawler Bots von Google und Bing erzeugt wurden und darf aus Datenschutzgründen nicht auf IP-Adressen zugreifen. Hier sollen in Tableau bestimmte Kennzahlen dargestellt werden. Beispielsweise soll die Anzahl von Zugriffen pro Webseite über den gesamten verfügbaren Zeitraum dargestellt werden, aber es soll auch möglich sein, Daten im Detail zu betrachten. Die Operations-Abteilung hingegen benötigt auch die IP-Adressen, um Auffälligkeiten beobachten und analysieren zu können. Verschiedene Metriken sollen in Grafana visualisiert werden.
Die Log Dateien werden viertelstündlich in Cloud Storage hochgeladen. Für jeden Serverknoten wird eine Datei erzeugt, deren Größe mit der Nutzeraktivität variiert. Somit werden immer 10 bis 20 komprimierte Dateien mit einer Größe zwischen 0 und 100 Megabyte hochgeladen. Daten sollen innerhalb von einer Stunde nach Anlieferung in Cloud Storage im Data Warehouse verfügbar sein. Die Daten müssen aus Kostengründen nach 30 Tagen gelöscht werden.
BigQuery
Für das Laden von Daten von Cloud Storage nach BigQuery existieren von Google verwaltete Apache Beam Templates für Google Dataflow. Unter Verwendung der Dataflow API kann dieses genutzt werden. Dabei kann eine User Defined Function in JavaScript übergeben werden, die die Transformationslogik von Server Logs zu tabellarischen Daten beschreibt. Das Template für Streaming erzeugt einen Job, der permanent läuft und neue Dateien aus Cloud Storage verarbeitet. Dabei werden Fehler in eine eigene Tabelle geschrieben. Alternativ kann das Template für Batch Jobs genutzt werden, was regelmäßig gestartet werden muss, um den nächsten Datensatz zu laden. In diesem Fall führt ein Fehler zum Abbruch des Jobs. Der Batch Job kann beispielsweise mit Hilfe von Cloud Scheduler und Cloud Functions gestartet werden oder auch per Apache Airflow verwaltet werden, wenn ohnehin eine Instanz in Gebrauch ist. Dataflow ist skaliert elastisch, das heißt, variable Datenmengen sind unproblematisch.
Zu Stoßzeiten werden stündlich mehrere Gigabyte geschrieben, daher ist eine Partitionierung anhand des Zeitstempels nach Stunde sinnvoll. Partitionen werden so konfiguriert, dass sie nach 30 Tagen gelöscht werden, somit hat die Datenbank ein Maximum von 720 Partitionen. Die Daten aus Cloud Storage werden nach 7 Tagen gelöscht, um gegebenenfalls Fehler beheben und nachvollziehen zu können.
Um SEO zu ermöglichen, auf die spezifizierte Teilmenge der Daten zuzugreifen, werden Views genutzt. Hierzu wird für SEO eine Rolle angelegt, die berechtigt, auf den View zuzugreifen. Zudem muss der View berechtigt werden, auf die Tabelle zuzugreifen. Da SEO in den meisten Fällen nur Zugriff auf bestimmte aggregierte Kennzahlen benötigt, sollten hierfür Materialized Views verwendet werden. Da SEO am Gesamtzeitraum interessiert ist, müssten, wenn normale Views genutzt werden, jedes Mal mehrere Terabyte Daten gescannt werden. Zusätzlich kann BI Engine verwendet werden.
Für die Dashboards des Operationsteams ist das gleiche Vorgehen möglich. Für Grafana existieren BigQuery Plug-ins, die genutzt werden können, um mittels Service Accounts auf BigQuery zuzugreifen.
Snowflake
Anstelle von Dataflow können in Snowflake Snowpipes verwendet werden. Snowpipe ist ein Snowflake Objekt, das auf den Upload von Dateien in einem spezifizierten Cloud Storage Bucket reagiert und kontinuierliches Laden von Daten in Mini-Batches ermöglicht. Die Transformationslogik kann innerhalb von Snowflake abgebildet werden.
Ähnlich wie in BigQuery können anschließend Views und Materialized Views verwendet werden, um die Daten bereitzustellen. Für die SEO Abteilung sollten Secure Views verwendet werden. Snowflake lässt sich ebenfalls einfach mit Grafana und Tableau integrieren. Hierfür sollten jeweils spezifische Rollen mit den entsprechenden Leseberechtigungen geschaffen werden. Für die Beantwortung der Queries von Tableau und Grafana muss ein Virtual Warehouse in angemessener Größe zur Verfügung gestellt werden.
Disclaimer
Der vorgenommene Vergleich basiert auf persönlicher Erfahrung sowie Recherche. Sowohl Snowflake als auch BigQuery haben noch sehr viele weitere Features und es ist möglich, die Datenplattformen grundsätzlich anders zu verwenden als hier beschrieben. Als Beispiel kann der Fokus mehr auf Low-Code oder Integration mit existierender On-Premise-Dateninfrastruktur liegen. Für jeden Fall ist eine individuelle Anforderungsanalyse notwendig.
Appendix
BigQuery Graphical User Interface
Der linke Bereich wird normalerweise ausgeblendet. Hier finden sich weitere BigQuery Features, wie das Anlegen von geplanten Jobs. Rechts daneben finden sich verfügbare Projekte und Datensätze. Diese können mit einem Stern markiert werden, sodass sie auch sichtbar bleiben, wenn der Nutzer ein anderes Projekt ausgewählt hat (das ausgewählte Projekt ist rechts oben neben dem Google Logo sichtbar). bigquery-public-data ist ein Projekt, das viele öffentliche Datensätze enthält, die zur Nutzung bereitstehen. Im rechten Bereich öffnen sich Tabellen und andere Objekte als Tabs. Hier können verschiedene Metadaten und Auszüge der Daten betrachtet werden. Zudem werden im gleichen Bereich Query Editors geöffnet. Die Tabs lassen sich auch in zwei Bereichen nebeneinander anzeigen. Unterhalb der Tabs kann die Query History angezeigt werden. Wird eine Query ausgeführt, so wird das Ergebnis im Bereich zwischen den Tabs und der Query History eingeblendet.
Snowflake Graphical User Interface
Unten ist die Worksheet-Anzeige von Snowsight. Die Oberfläche ist aufgebaut wie eine typische Entwicklungsumgebung: Links sind Ressourcen, man kann sich die verschiedenen Worksheets und Datenbanken anzeigen lassen. Den Großteil des Bildschirms nimmt der Code ein. Hier ist es möglich, mehrere Worksheets in Tabs offen zu haben. Im unteren Bereich werden Ausgaben von Operationen und Queries angezeigt, dazu Statistiken (rechts unten). Oben recht kann die aktuelle Rolle gewählt werden. Oben links ermöglicht ein Klick den Wechsel zu weiteren Oberflächen von Snowsight (siehe unteres Bild).
Constraints
Beide Systeme bieten nur begrenzt Möglichkeiten, SQL Constraints zu verwenden. In Snowflake ist es zwar möglich, Eindeutigkeit, Fremd- und Primärschlüssel zu definieren, erzwungen werden kann aber nur das NOT NULL Constraint. In BigQuery ist es lediglich möglich, für Spalten im Tabellenschema einen Modus zu deklarieren. Grundeinstellung ist NULLABLE, REQUIRED entspricht NOT NULL und REPEATED ist der Array-Typ.
(Autor: Augustin Wilberg)
[1] https://cloud.google.com/bigquery/pricing [2] https://cloud.google.com/bigquery/docs/querying-partitioned-tables [3] https://cloud.google.com/bigquery/docs/running-queries [4] https://cloud.google.com/bigquery/quotas [5] Overview of Warehouses — Snowflake Documentation [6] BQ slow on non-large data sets (~3s initiation time) [67021275] – Visible to Public – Issue Tracker [7] BigQuery-Speicher | Google Cloud [8] Spaltenorientierte Datenbank – Wikipedia [9] https://cloud.google.com/bigquery/docs/partitioned-tables [10] Micro-partitions & Data Clustering — Snowflake Documentation [11] Pricing Guide | Snowflake [12] Snowpark API — Snowflake Documentation [13] Snowflake Ecosystem — Snowflake Documentation [14] Google-provided templates | Cloud Dataflow | Google Cloud [15] Overview of Access Control — Snowflake Documentation [16] BigQuery public datasets | Google Cloud [17] Provide credentials for Application Default Credentials | Authentication | Google Cloud [18] https://cloud.google.com/bigquery/docs/bi-engine-intro [19] Warehouse Considerations — Snowflake Documentation [20] Introduction to partitioned tables | BigQuery | Google Cloud [21] Using the Search Optimization Service — Snowflake Documentation
