BigQuery für Data Warehouse-Experten

Aktualisiert: September 2017

In diesem Artikel wird erläutert, wie Sie BigQuery als Data Warehouse verwenden. Dazu werden zuerst die gängigen Data Warehouse-Konzepte denen in BigQuery zugeordnet. Anschließend wird beschrieben, wie Sie in BigQuery Standard-Data Warehousing-Aufgaben ausführen.

Dienstmodelle im Vergleich

Die folgende Tabelle weist standardmäßige Data Warehouse-Konzepte jenen in BigQuery zu:

Data Warehouse BigQuery
Data Warehouse Der BigQuery-Dienst ersetzt das typische Hardware-Setup für ein herkömmliches Data Warehouse. Das heißt, es dient als Sammelpunkt für alle analytischen Daten in einer Organisation.
Data Mart Datasets sind Tabellensammlungen, die nach Geschäftsfeldern oder nach einem bestimmten analytischen Bereich aufgeteilt werden können. Jedes Dataset ist mit einem Google Cloud-Projekt verknüpft.
Data Lake Ihr Data Lake enthält möglicherweise Dateien in Cloud Storage oder Google Drive oder Transaktionsdaten in Cloud Bigtable. BigQuery kann ein Schema definieren und Abfragen direkt für externe Daten als Verbunddatenquellen ausführen.
Tabellen und Ansichten Tabellen und Ansichten funktionieren in BigQuery genauso wie in einem traditionellen Data Warehouse.
Google Grants Mit Identity and Access Management (IAM) gewähren Sie Berechtigungen zum Ausführen bestimmter Aktionen in BigQuery.

Datasets

BigQuery organisiert Datentabellen in Einheiten, auch Datasets genannt. Diese Datasets gelten für Ihr Google Cloud-Projekt. Wenn Sie über die Befehlszeile, in SQL-Abfragen oder im Code auf eine Tabelle verweisen, verweisen Sie mit dem folgenden Konstrukt darauf:

project.dataset.table

Mit diesen mehreren Bereichen (Projekt, Dataset und Tabelle) können Sie Ihre Informationen logisch strukturieren. Sie können mehrere Datasets verwenden, um Tabellen voneinander zu trennen, die sich auf verschiedene analytische Domains beziehen, und Sie können die Bereichsbildung auf Projektebene vornehmen, um Datasets entsprechend Ihren Geschäftsanforderungen zu isolieren.

Hier ist eine strukturelle Übersicht über BigQuery:

Diagramm: BigQuery – Struktureller Überblick

Bereitstellung und Systemgröße

Sie müssen im Gegensatz zu vielen RDBMS-Systemen keine Ressourcen bereitstellen, bevor Sie BigQuery verwenden. BigQuery weist Speicher- und Abfrageressourcen basierend auf Ihren Nutzungsmustern dynamisch zu.

  • Speicherressourcen werden nach Ihrem Verbrauch zugeordnet und wieder entfernt, wenn Sie Daten löschen oder Tabellen verwerfen.
  • Abfrageressourcen werden nach Abfragetyp und Komplexität zugeordnet. Jede Abfrage verwendet eine bestimmte Anzahl an Slots, bei denen es sich um Berechnungseinheiten für eine gewisse CPU- und RAM-Größe handelt.

Sie müssen keine Mindestverwendungsverpflichtung eingehen, um BigQuery zu verwenden. Der Dienst teilt Ressourcen auf der Grundlage Ihrer tatsächlichen Nutzung zu und berechnet diese. Standardmäßig haben alle BigQuery-Kunden Zugriff auf 2.000 Slots für Abfragevorgänge. Sie können auch eine feste Anzahl von Slots für Ihr Projekt reservieren. Ausführliche Informationen zur Vorgehensweise finden Sie im Abschnitt Kosten.

Speicherverwaltung

Intern speichert BigQuery Daten in einem proprietären Spaltenformat namens Capacitor, das eine Reihe von Vorteilen für Data Warehouse-Arbeitslasten bietet. BigQuery verwendet ein proprietäres Format, da es zusammen mit der Abfrage-Engine weiterentwickelt werden kann. Dabei werden fundierte Kenntnisse des Datenlayouts genutzt, um die Abfrageausführung zu optimieren. BigQuery verwendet Abfragezugriffsmuster, um die optimale Anzahl physischer Shards und deren Codierung zu ermitteln.

Die Daten werden in Googles verteiltem Dateisystem namens Colossus gespeichert. Dies sichert die Beständigkeit durch die Verwendung der Löschcodierung, wodurch redundante Datenblöcke auf mehreren Datenträgern gespeichert werden können. Darüber hinaus werden die Daten in mehrere Rechenzentren repliziert.

Sie können BigQuery-Abfragen auch für Daten außerhalb des BigQuery-Speichers, z. B. in Cloud Storage, Google Drive oder Cloud Bigtable gespeicherte Daten, mit Verbunddatenquellen ausführen. Diese Quellen sind jedoch nicht für BigQuery-Vorgänge optimiert, sodass sie möglicherweise nicht so gut funktionieren wie die im BigQuery-Speicher gespeicherten Daten.

Wartung

BigQuery ist ein vollständig verwalteter Dienst, das heißt, das BigQuery-Engineering-Team kümmert sich für Sie um Updates und Wartung. Upgrades sollten keine Ausfallzeiten erfordern oder die Systemleistung beeinträchtigen.

Viele traditionelle Systeme erfordern ressourcenintensive Vakuumprozesse, die in verschiedenen Intervallen ausgeführt werden müssen, um Datenblöcke neu zu mischen und zu sortieren und Speicherplatz freizugeben. BigQuery hat keine Entsprechung zum Vakuumprozess, da die Speicher-Engine kontinuierlich verwaltet und optimiert, wie Daten gespeichert und repliziert werden. Da BigQuery keine Indexe für Tabellen verwendet, müssen Sie auch keine neu erstellen.

Sicherung und Wiederherstellung

BigQuery befasst sich mit Sicherungen und Notfallwiederherstellung auf Dienstebene. Da BigQuery einen vollständigen 7-Tages-Verlauf der Änderungen an Ihren Tabellen verwaltet, können Sie außerdem einen Snapshot zu einem bestimmten Zeitpunkt mithilfe von Tabellen-Decorators oder SYSTEM_TIME AS OF in der FROM-Klausel abfragen. Sie können Änderungen problemlos rückgängig machen, ohne eine Wiederherstellung von Sicherungen anfordern zu müssen. (Wenn eine Tabelle explizit gelöscht wird, wird ihr Verlauf nach 7 Tagen gelöscht.)

Workflows verwalten

In diesem Abschnitt werden administrative Aufgaben erläutert, z. B. das Organisieren von Datasets, das Erteilen von Berechtigungen und die Onboarding-Arbeit in BigQuery. In diesem Abschnitt wird auch erläutert, wie Sie gleichzeitige Arbeitslasten verwalten, den Zustand Ihres Data Warehouse überwachen und den Nutzerzugriff prüfen können.

Datasets organisieren

Sie können Datasets basierend auf der Datenklasse oder der Geschäftseinheit in separate Projekte unterteilen oder sie der Einfachheit halber zu gemeinsamen Projekten zusammenfassen.

Sie können einen Datenanalysten einladen, an einem vorhandenen Dataset in einer von Ihnen definierten eingeschränkten Rolle zusammenzuarbeiten. Wenn sich Datenanalysten bei der BigQuery-Web-UI anmeldet, werden nur die Datasets angezeigt, die projektübergreifend für sie freigegeben wurden. Die Aktivitäten, die sie für Datasets ausführen können, hängen von ihrer Rolle für jedes Dataset ab.

Berechtigungen gewähren

In einem traditionellen RDBMS-System erteilen Sie die Berechtigungen, Tabellen anzuzeigen und zu ändern, indem Sie SQL-Berechtigungen erstellen und diese auf einen bestimmten Nutzer innerhalb des Datenbanksystems anwenden. Zusätzlich erlauben Ihnen einige RDBMS-Systeme, Nutzern in einem externen Verzeichnis wie LDAP Berechtigungen zu erteilen. Das BigQuery-Modell zum Verwalten von Nutzern und Berechtigungen ähnelt dem letztgenannten Modell.

BigQuery bietet vordefinierte Rollen zur Steuerung des Zugriffs auf Ressourcen. Sie können auch benutzerdefinierte IAM-Rollen erstellen, die aus Ihren definierten Berechtigungen bestehen, und diese Rollen dann Nutzern oder Gruppen zuweisen. Sie können einer Google-E-Mail-Adresse oder einer Google-Arbeitsbereichsgruppe eine Rolle zuweisen.

Ein wichtiger Aspekt beim Betrieb eines Data Warehouse ist die Ermöglichung des gemeinsamen, aber kontrollierten Zugriffs auf dieselben Daten für verschiedene Benutzergruppen. Beispielsweise greifen alle Abteilungen für Finanzen, Personal und Marketing auf dieselben Tabellen zu, die Zugriffsebenen unterscheiden sich jedoch. Herkömmliche Data-Warehousing-Tools ermöglichen dies durch Erzwingen der Sicherheit auf Zeilenebene. Sie können dieselben Ergebnisse in BigQuery erzielen, wenn Sie autorisierte Ansichten und Berechtigungen auf Zeilenebene definieren.

Onboarding

Das Einbinden neuer Datenanalysten war traditionell mit einer erheblichen Vorlaufzeit verbunden. Damit Analysten einfache Abfragen ausführen können, mussten Sie ihnen den Speicherort der Datenquellen anzeigen und ODBC-Verbindungen und -Tools sowie Zugriffsrechte einrichten. Mit Google Cloud können Sie die Zeit eines Analysten bis zur Produktivität erheblich verkürzen.

Wenn Sie einen Analysten in Google Cloud einbinden möchten, gewähren Sie ihm Zugriff auf relevante Projekte, führen ihn in die Google Cloud Console und die BigQuery-Web-UI ein und geben einige Abfragen frei, um ihn mit den Daten vertraut zu machen:

  • Die Cloud Console bietet eine zentrale Ansicht aller Assets in Ihrer Google Cloud-Umgebung. Das wichtigste Asset für Datenanalysten sind möglicherweise Cloud-Storage-Buckets, in denen sie an Dateien zusammenarbeiten können.
  • Die BigQuery-Web-UI zeigt die Liste der Datasets an, auf die der Analyst Zugriff hat. Analysten können Aufgaben in der Cloud Console entsprechend der ihnen zugewiesenen Rolle ausführen, z. B. Metadaten und eine Vorschau der Daten anzeigen lassen sowie Abfragen ausführen, speichern und freigeben.

Arbeitslasten und Gleichzeitigkeit verwalten

BigQuery begrenzt die maximale Anzahl eingehender Anfragen und erzwingt entsprechende Kontingente auf Projektbasis. Bestimmte Richtlinien variieren je nach Ressourcenverfügbarkeit, Nutzerprofil, Dienstnutzungsverlauf und anderen Faktoren. Weitere Informationen finden Sie in der Richtlinie zu BigQuery-Kontingenten.

BigQuery bietet zwei Arten von Abfrageprioritäten: interaktiv und batchweise. BigQuery führt standardmäßig interaktive Abfragen aus. Dies bedeutet, dass die Abfrage so bald wie möglich ausgeführt wird. Interaktive Abfragen werden auf Abfragekontingente angerechnet. Batchabfragen werden in die Warteschlange gestellt und ausgeführt, sobald freie Ressourcen verfügbar sind, normalerweise innerhalb weniger Minuten.

BigQuery unterstützt keine differenzierte Priorisierung von interaktiven Abfragen oder Batchabfragen. Angesichts der Geschwindigkeit und des Umfangs, in dem BigQuery ausgeführt wird, sind viele herkömmliche Arbeitslastprobleme nicht anwendbar. Wenn Sie eine explizite Priorisierung von Abfragen benötigen, können Sie Ihre vertraulichen Arbeitslasten in ein Projekt mit einer expliziten Anzahl reservierter Slots unterteilen. Wenden Sie sich an Ihren Google-Vertreter, um Flatrate-Kunde zu werden.

Monitoring und Prüfung

Sie können BigQuery mit Monitoring überwachen. Dabei werden verschiedene Diagramme und Warnungen anhand von BigQuery-Messwerten definiert. Beispielsweise können Sie den Systemdurchsatz mithilfe des Messwertes "Abfragezeit" überwachen oder Abfrageanforderungstrends basierend auf dem Messwert "Zugewiesene Slots" anzeigen lassen. Wenn Sie eine anspruchsvolle Abfrage im Voraus planen müssen, können Sie den Messwert "Verfügbare Slots" verwenden. Um die Systemintegrität proaktiv zu gestalten, können Sie Warnungen basierend auf von Ihnen festgelegten Schwellenwerten erstellen. Monitoring bietet ein webbasiertes Self-Service-Portal. Sie können den Zugriff auf das Portal über einen Monitoring-Arbeitsbereich steuern.

BigQuery erstellt automatisch Audit-Logs von Nutzeraktionen. Sie können Audit-Logs in einem Batch oder als Datenstrom in ein anderes BigQuery-Dataset exportieren und die Logs mit Ihrem bevorzugten Analysetool visualisieren. Weitere Informationen finden Sie unter Audit-Logs mit BigQuery analysieren.

Daten verwalten

In diesem Abschnitt werden Überlegungen zum Schemaentwurf, zur Denormalisierung, zur Funktionsweise der Partitionierung und Methoden zum Laden von Daten in BigQuery erläutert. Der Abschnitt wird mit einem Blick auf die Handhabung von Änderungen im Warehouse beendet, ohne dass Ausfallzeiten für die Analyse auftreten.

Schemaentwurf

Befolgen Sie diese allgemeinen Richtlinien, um das optimale Schema für BigQuery zu entwerfen:

  • Denormalisieren Sie eine Dimensionstabelle, die größer als 10 Gigabyte ist, es sei denn, Sie sehen deutliche Anzeichen dafür, dass die Kosten für Datenbearbeitung, UPDATE- und DELETE-Vorgänge die Vorteile optimaler Abfragen überwiegen.
  • Behalten Sie eine Dimensionstabelle bei, die kleiner als 10 Gigabyte ist, es sei denn, die Tabelle durchläuft selten UPDATE- und DELETE-Vorgänge.
  • Nutzen Sie die Vorteile verschachtelter und wiederholter Felder in denormalisierten Tabellen.

Denormalisierung

Bei der herkömmlichen Methode zur Denormalisierung von Daten wird ein Fakt mit all seinen Dimensionen in eine flache Tabellenstruktur geschrieben. Bei Verkaufstransaktionen schreiben Sie beispielsweise jeden Fakt zusammen mit den zugehörigen Dimensionen, wie z. B. Auftrags- und Kundeninformationen, in einen Datensatz.

Im Gegensatz dazu nutzt die bevorzugte Methode zur Denormalisierung von Daten die native Unterstützung von BigQuery für verschachtelte und wiederholte Strukturen in JSON- oder Avro-Eingabedaten. Drückt man Datensätze mit verschachtelten und sich wiederholenden Strukturen aus, kann dies eine natürlichere Darstellung der zugrundeliegenden Daten bewirken. Im Fall des Kundenauftrags enthält der äußere Teil einer JSON-Struktur die Auftrags- und Kundeninformationen und der innere Teil die einzelnen Positionen des Auftrags, die als verschachtelte, wiederholte Elemente dargestellt werden.

{
  "orderID": "ORDER",
  "custID": "EMAIL",
  "custName": "NAME",
  "timestamp": "TIME",
  "location": "LOCATION",
  "purchasedItems": [
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    },
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    }
  ]
}

Das Ausdrücken von Datensätzen mithilfe von verschachtelten und wiederholten Feldern vereinfacht das Laden von Daten mithilfe von JSON- oder Avro-Dateien. Nachdem Sie ein solches Schema erstellt haben, können Sie SELECT, INSERT, UPDATE und DELETE auf beliebigen einzelnen Felder mit einer Punktnotation ausführen, z. B. Order.Item.SKU. Beispiele finden Sie in der BigQuery-Dokumentation.

Vorteile der Denormalisierung

BigQuery ist im Wesentlichen eine analytische Engine. Es unterstützt DML-Aktionen, ist jedoch nicht für die Verwendung als OLTP-Speicher (Online Transaction Processing) vorgesehen. Die Diskussion zum Ändern von Daten enthält Richtlinien für den Umgang mit Änderungen, bei denen keine Ausfallzeiten für die Analyse auftreten und eine optimale Leistung für die Online-Analyseverarbeitung (OLAP) erzielt wird. Normalisierte oder teilweise normalisierte Datenstrukturen wie Sternschema oder Schneeflocke sind zwar für Aktualisierungs-/Löschvorgänge geeignet, für OLAP-Arbeitslasten jedoch nicht optimal. Wenn Sie OLAP-Vorgänge für normalisierte Tabellen ausführen, müssen mehrere Tabellen durch JOIN verknüpft sein, um die erforderlichen Aggregationen durchzuführen. JOINs sind mit BigQuery möglich und werden manchmal für kleine Tabellen empfohlen. Sie sind jedoch typischerweise nicht so leistungsfähig wie denormalisierte Strukturen.

Das folgende Diagramm vergleicht die Abfrageleistung mit JOINs mit einfachen Filtern in Bezug auf die Tabellengröße. Die Abfrageleistung zeigt bei Vorhandensein von JOINs einen viel steileren Abfall.

Grafik: Abfragezeit mit JOINS im Vergleich zu Filtern

Nachteile der Denormalisierung

Denormalisierte Schemas sind nicht speicheroptimal, aber die geringen Speicherkosten von BigQuery betreffen die Ineffizienz des Speichers. Sie können die Kosten mit dem Anstieg der Abfragegeschwindigkeit vergleichen, um festzustellen, warum der Speicher kein wesentlicher Faktor ist.

Eine Herausforderung bei der Arbeit mit denormalisierten Schemas ist die Aufrechterhaltung der Datenintegrität. Abhängig von der Häufigkeit und Verbreitung von Änderungen kann die Aufrechterhaltung der Datenintegrität eine längere Maschinen- und manchmal auch Test- und Überprüfungszeit durch Menschen erfordern.

Tabellen partitionieren

BigQuery unterstützt das Partitionieren von Tabellen nach Datum. Sie aktivieren die Partitionierung während der Tabellenerstellung. BigQuery erstellt automatisch neue datumsbasierte Partitionen, ohne dass zusätzliche Wartung erforderlich ist. Außerdem können Sie eine Ablaufzeit für Daten in den Partitionen angeben.

Neue Daten, die in eine partitionierte Tabelle eingetragen werden, werden zum Zeitpunkt des Einfügens in die Rohpartition geschrieben. Um explizit zu steuern, auf welche Partition die Daten geladen werden, kann Ihr Ladeauftrag eine bestimmte Datumspartition angeben.

Daten laden

Bevor Daten für analytische Arbeitslasten in BigQuery geladen werden können, werden sie normalerweise in einem Cloud Storage-Produkt und in einem Format gespeichert, das ihrem Ursprung entspricht. In den frühen Phasen der Migration zu Google Cloud besteht das übliche Muster darin, vorhandene Extrahierungs-, Transformations- und Ladetools (ETL) zu verwenden, um Daten in das ideale Schema für BigQuery zu transformieren. Nachdem die Daten umgewandelt worden sind, werden sie zu Cloud Storage als CSV-, JSON- oder Avro-Dateien übertragen und von dort unter Verwendung von Ladejobs oder Streaming in BigQuery geladen. Alternativ können Sie Dateien in das Cloud Storage-Schema übertragen, das dem vorhandenen lokalen Datenspeicher eigen ist, in eine Reihe von Staging-Tabellen in BigQuery geladen wird und dann mit BigQuery-SQL-Befehlen in das ideale Schema für BigQuery umgewandelt wird. Diese beiden Ansätze werden hier visualisiert:

Diagramm: Erster Ansatz zum Laden von Daten

Diagramm: Zweiter Ansatz zum Laden von Daten

Wenn Sie Ihren Footprint in Google Cloud erweitern, erfassen Sie Ihre Quelldaten wahrscheinlich direkt in Cloud Bigtable, Datastore oder Cloud Spanner und verwenden Dataflow, um bei Daten in Batch oder Streams in BigQuery ETL auszuführen.

Quellcode direkt erfassen

Ladejobs verwenden

In diesem Abschnitt wird davon ausgegangen, dass sich Ihre Daten im Cloud-Speicher als Sammlung von Dateien in einem unterstützten Dateiformat befinden. Weitere Informationen zu jedem Datenformat sowie zu den bei der Formatauswahl zu berücksichtigenden spezifischen Anforderungen und Funktionen finden Sie unter BigQuery-Datenformate.

Zusätzlich zu CSV können Sie auch Datendateien mit anderen Trennzeichen als Kommas verwenden, wenn Sie das Flag --field_delimiter verwenden. Einzelheiten finden Sie unter bq-Lade-Flags.

BigQuery unterstützt das Laden von komprimierten gzip-Dateien. Das Laden komprimierter Dateien ist jedoch nicht so schnell wie das Laden nicht komprimierter Dateien. Bei zeitkritischen Szenarien oder Szenarien, in denen die Übertragung von nicht komprimierten Dateien an Cloud Storage in der Bandbreite oder zeitlich begrenzt ist, sollten Sie einen kurzen Ladetest ausführen, um zu sehen, welche Alternative am besten funktioniert.

Da Ladejobs asynchron sind, müssen Sie keine Clientverbindung aufrechterhalten, während der Job ausgeführt wird. Noch wichtiger ist, dass sich Ladejobs nicht auf Ihre anderen BigQuery-Ressourcen auswirken.

Ein Ladejob erstellt eine Zieltabelle, falls noch keine vorhanden ist.

So bestimmt BigQuery das Datenschema:

Sie können ein Schema explizit spezifizieren, indem Sie das Schema als ein Argument an den Ladejob übergeben. Laufende Ladejobs können mit dem gleichen Verfahren wie beim anfänglichen Laden an dieselbe Tabelle angehängt werden, wobei das Schema jedoch nicht mit jedem Job übergeben werden muss.

Wenn Ihre CSV-Dateien immer eine Kopfzeile enthalten, die nach dem ersten Laden und dem Erstellen der Tabelle ignoriert werden muss, können Sie das Flag --skip_leading_rows verwenden, um die Zeile zu ignorieren. Einzelheiten finden Sie unter bq-Lade-Flags.

BigQuery legt tägliche Grenzwerte für die Anzahl und Größe der Ladejobs fest, die Sie pro Projekt und pro Tabelle ausführen können. Zusätzlich setzt BigQuery Grenzen bei den Größen der individuellen Ladedateien und Datensätze. Weitere Informationen finden Sie unter Kontingent-Richtlinie.

Sie können Ladejobs über die BigQuery-Web-UI starten. Um den Prozess zu automatisieren, können Sie Cloud Functions einrichten, um ein Cloud Storage-Ereignis abzuhören, das mit dem Eintreffen neuer Dateien in einem bestimmten Bucket verbunden ist, und um den BigQuery-Ladejob zu starten.

Streaming-Einsätze nutzen

Für einen alternativen und ergänzenden Ansatz können Sie Daten auch direkt in BigQuery streamen. Gestreamte Daten werden sofort zur Verfügung gestellt und können neben vorhandenen Tabellendaten in Echtzeit abgefragt werden.

In Situationen, die von Echtzeitinformationen profitieren können, wie etwa die Betrugserkennung oder die Überwachung von Systemmesswerten, kann das Streaming ein maßgebliches Unterscheidungsmerkmal sein. Im Gegensatz zu Ladejobs, die in BigQuery kostenlos sind, wird für das Streaming von Daten eine Gebühr erhoben. Daher ist es wichtig, Streaming in Situationen zu verwenden, in denen die Vorteile die Kosten überwiegen.

Wenn Sie Daten in die BigQuery-Tabellen streamen, senden Sie Ihre Datensätze mithilfe der BigQuery API direkt an BigQuery. Wenn Sie Cloud Logging verwenden, können Sie die Logs Ihres Google Cloud-Projekts auch direkt in BigQuery streamen, einschließlich der von App Engine gesendeten Anforderungslogs und der benutzerdefinierten Loginformationen, die an Cloud Logging gesendet werden.

Umgang mit Veränderungen

Viele Data Warehouses unterliegen strengen Service Level Agreements (SLAs), die nur geringe bis gar keine Ausfallzeiten erfordern. Während Google die Betriebszeit von BigQuery verwaltet, steuern Sie die Verfügbarkeit und Reaktionsfähigkeit Ihrer Datasets mit Ihrem Ansatz, Änderungen in den Daten widerzuspiegeln.

Alle Tabellenänderungen in BigQuery sind ACID-konform. Dies gilt für DML-Vorgänge, Abfragen mit Zieltabellen und Ladejobs. Eine Tabelle, die Einfügungen, Aktualisierungen und Löschvorgänge durchläuft, während Nutzerabfragen verarbeitet werden, verarbeitet die Gleichzeitigkeit ordnungsgemäß und wechselt atomar von einem Zustand zum nächsten. Das Ändern einer Tabelle erfordert daher keine Ausfallzeit. Ihr interner Prozess erfordert jedoch möglicherweise eine Test- und Validierungsphase, bevor neu aktualisierte Daten für die Analyse zur Verfügung gestellt werden. Da DML-Vorgänge mit der analytischen Arbeitslast über Slots konkurrieren, ziehen Sie es möglicherweise vor, sie zu isolieren. Aus diesen Gründen kann es zu Ausfallzeiten kommen. In diesem Artikel wird der Begriff "Ausfallzeit zwecks Analyse" verwendet, um Verwechslungen mit Ausfallzeiten des BigQuery-Dienstes zu vermeiden.

Sie können die meisten der alten und bewährten Techniken zum Umgang mit Ausfallzeiten bei Analysen anwenden. In diesem Abschnitt werden einige der bekannten Herausforderungen und Abhilfemaßnahmen erläutert.

Fließendes Zeitfenster

Ein herkömmliches Data Warehouse speichert Daten im Gegensatz zu einem Data Lake nur für einen festgelegten Zeitraum, z. B. die letzten 5 Jahre. Bei jedem Aktualisierungszyklus werden neue Daten zum Warehouse hinzugefügt und die ältesten Daten werden gelöscht, wobei die Dauer fest bleibt. Dieses Konzept wurde größtenteils angewendet, um die Einschränkungen älterer Technologien zu umgehen.

BigQuery wurde für die Skalierung entwickelt und kann mit zunehmender Größe des Warehouse skaliert werden, sodass ältere Daten nicht gelöscht werden müssen. Durch Aufzeichnung des gesamten Verlaufs erhalten Sie mehr Einblick in Ihr Unternehmen. Wenn die Speicherkosten von Belang sind, können Sie die langfristigen Speicherpreise von BigQuery nutzen, indem Sie ältere Daten archivieren und bei Bedarf für spezielle Analysen verwenden. Wenn Sie immer noch gute Gründe haben, ältere Daten zu löschen, können Sie die native Unterstützung von BigQuery für datumspartitionierte Tabellen und den Partitionsablauf verwenden. Mit anderen Worten, BigQuery kann ältere Daten automatisch löschen.

Schemas ändern

Während ein Data Warehouse entworfen und entwickelt wird, werden Tabellenschemas in der Regel durch Hinzufügen, Aktualisieren oder Löschen von Spalten oder sogar durch Einfügen oder Löschen ganzer Tabellen optimiert. Wenn die Änderung nicht in Form einer hinzugefügten Spalte oder Tabelle erfolgt, können gespeicherte Abfragen und Berichte unterbrochen werden, die auf eine gelöschte Tabelle, eine umbenannte Spalte usw. verweisen.

Nachdem das Data Warehouse in Produktion ist, werden solche Änderungen einer strengen Änderungskontrolle unterzogen. Möglicherweise entscheiden Sie sich, kleinere Schemaänderungen während einer Ausfallzeit der Analyse durchzuführen, aber zum größten Teil werden Änderungen des Schemas als Versionsupgrades geplant. Sie entwerfen, entwickeln und testen das Upgrade parallel, während die vorherige Version des Data Warehouse die Analyse-Arbeitslasten bereitstellt. Sie folgen demselben Ansatz, wenn Sie Schemaänderungen auf ein BigQuery-Data-Warehouse anwenden.

Sich langsam verändernde Dimensionen

Ein normalisiertes Datenschema minimiert die Auswirkung von sich langsam ändernden Dimensionen (SCD), indem die Änderung in den Dimensionstabellen isoliert wird. Dies ist im Allgemeinen günstiger als ein denormalisiertes Schema, bei dem SCD umfassende Aktualisierungen der flachen Faktentabelle verursachen kann. Verwenden Sie die Normalisierung für BigQuery jedoch sorgfältig, wie im Abschnitt zum Schemaentwurf erläutert.

Wenn es um SCD geht, gibt es keine einheitliche Lösung. Es ist wichtig, die Art der Änderung zu verstehen und die relevantesten Lösungen oder Lösungskombinationen für Ihr Problem anzuwenden. Der Rest dieses Abschnitts beschreibt einige Lösungen und deren Anwendung auf SCD-Typen.

Technik 1: Ansicht wechseln

Diese Technik basiert auf zwei Ansichten der Daten: "Haupt" oder "Schatten". Der Trick besteht darin, die eigentliche Tabelle auszublenden und die "Hauptansicht" den Nutzern zugänglich zu machen. Bei Aktualisierungszyklen wird die "Schatten"-Ansicht erstellt/aktualisiert und durchläuft Datenkorrektheitstests, während die Nutzer mit der "Haupt"-Ansicht arbeiten. Zum Umschaltzeitpunkt wird die "Hauptansicht" durch "Schatten" ersetzt. Die alten "Haupt"- und jetzt "Schatten"-Ansichten könnten bis zum nächsten Aktualisierungszyklus abgerissen oder für einige Workflows beibehalten werden, abhängig von den von der Organisation definierten Regeln und Prozessen.

Die beiden Ansichten könnten auf einer gemeinsamen Tabelle basieren und durch eine Spalte, beispielsweise "view_type", unterschieden werden oder auf unterschiedlichen Tabellen basieren. Die erstere Methode wird nicht empfohlen, da DML-Vorgänge in der "Schatten"-Ansicht der Tabelle Nutzerabfragen in der "Haupt"-Ansicht verlangsamen können, ohne dass echte Vorteile erzielt werden.

Während die Ansichtsumschaltung keine Ausfallzeiten für die Analyse bietet, sind die Kosten höher, da während des Aktualisierungszyklus zwei Kopien der Daten vorhanden sind. Noch wichtiger ist, dass diese Vorgehensweise Ihr Unternehmen daran hindern kann, die langfristigen Speicherpreise zu nutzen, wenn Aktualisierungszyklen mit einer höheren Rate als 90 Tagen durchgeführt werden. Neunzig Tage basieren auf der Preisrichtlinie zum Zeitpunkt der Veröffentlichung dieses Dokuments. Sehen Sie sich unbedingt die neueste Richtlinie an.

Manchmal ändern sich verschiedene Datensegmente in ihrem eigenen Tempo. Beispielsweise werden die Verkaufsdaten in Nordamerika täglich aktualisiert, während die Daten für den asiatisch-pazifischen Raum alle zwei Wochen aktualisiert werden. In solchen Situationen ist es am besten, die Tabelle basierend auf dem treibenden Faktor für die Änderung zu partitionieren, in diesem Beispiel Land. Die Ansichtsumschaltung wird dann auf die betroffenen Partitionen und nicht auf das gesamte Data Warehouse angewendet. Zum Zeitpunkt der Veröffentlichung dieses Dokuments können Sie nur auf der Grundlage eines benutzerdefinierten Datenattributs, z. B. Land, partitionieren, indem Sie die Daten explizit in mehrere Tabellen aufteilen.

Technik 2: Laden der Partition an Ort und Stelle

Wenn die Datenänderung durch eine Partition isoliert werden kann und eine kurze Ausfallzeit der Analyse toleriert wird, ist das Wechseln der Ansicht möglicherweise zu viel. Stattdessen können Daten für die betroffenen Partitionen in anderen BigQuery-Tabellen gespeichert oder in Dateien im Cloud-Speicher exportiert werden, wo sie während der Ausfallzeit der Analyse ersetzt werden können.

So ersetzen Sie Daten in einer Zielpartition durch Daten aus einer Abfrage einer anderen Tabelle:

bq query --use_legacy_sql=false --replace \
         --destination_table 'flight_data.fact_flights_part$20140910' \
         'select * from `ods.load_flights_20140910`

So ersetzen Sie Daten in einer Zielpartition durch Laden aus dem Cloud-Speicher:

bq load  --replace \
         --source_format=NEWLINE_DELIMITED_JSON
         'flight_data.fact_flights_part$20140910' \
         gs://{bucket}/load_flights_20140910.json
Technik 3: Aktualisieren der Datenmaskierung

Eine kleine und sich häufig ändernde Dimension ist ein Hauptkandidat für die Normalisierung. Bei dieser Technik werden Aktualisierungen einer solchen Dimension in einer isolierten Tabelle oder Ansicht bereitgestellt, die mit den übrigen Daten bedingt verknüpft ist:

SELECT f.order_id as order_id, f.customer_id as customer_id,
    IFNULL(u.customer_first_name, f.customer_first_name) as customer_first_name,
    IFNULL(u.customer_last_name, f.customer_last_name) as customer_last_name
FROM fact_table f
LEFT OUTER JOIN pending_customer_updates u
ON f.customer_id = u.customer_id
SCD-Typ 1: Attributwert überschreiben

SCD vom Typ 1 überschreibt den Wert eines Attributs mit neuen Daten, ohne den Verlauf beizubehalten. Wenn zum Beispiel das Produkt "Tolle Feuchtigkeitscreme" Teil der Kategorie "Gesundheit und Schönheit" war und jetzt als "Kosmetik" eingestuft wird, sieht die Änderung folgendermaßen aus:

Vorher:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC tolle Feuchtigkeitscreme – 100 oz Gesundheit und Schönheitspflege

Nachher:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC tolle Feuchtigkeitscreme – 100 oz Gesundheit- und Schönheitsprodukte

Wenn sich das Attribut in einer normalisierten Dimensionstabelle befindet, ist die Änderung sehr isoliert. Sie aktualisieren einfach die betroffene Zeile in der Dimensionstabelle. Verwenden Sie für kleinere Dimensionstabellen mit häufigen Aktualisierungen des Typs 1 die Methode 3: Aktualisieren der Datenmaskierung.

Wenn das Attribut denormalisiert in die Faktentabelle eingebettet ist, ist die Änderung ziemlich weit verbreitet. Sie müssen alle Faktzeilen aktualisieren, in denen sich das Attribut wiederholt. Verwenden Sie in diesem Fall entweder Technik 2: Laden der Partition an Ort und Stelle oder Technik 1: Ansichtswechsel.

SCD Typ 2: Attributwert ändern und Historie pflegen

Diese Methode verfolgt unbegrenzte historische Daten. Dazu werden mehrere Datensätze für einen bestimmten natürlichen Schlüssel mit separaten Ersatzschlüsseln erstellt. Dieselbe Änderung, die in SCD-Typ 1 dargestellt ist, wird beispielsweise so bearbeitet:

Vorher:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC tolle Feuchtigkeitscreme – 100 oz Gesundheit und Schönheitspflege 31. Januar 2009 NULL

Nachher:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC tolle Feuchtigkeitscreme – 100 oz Gesundheit und Schönheitspflege 31. Januar 2009 18. Juli 2017
124 ABC tolle Feuchtigkeitscreme – 100 oz Kosmetik 19. Juli 2017 NULL

Befindet sich das Attribut in einer normalisierten Dimensionstabelle, wird die Änderung isoliert. Sie aktualisieren einfach die vorherige Zeile und fügen eine neue in die Dimensionstabelle ein. Verwenden Sie für kleinere Dimensionstabellen mit häufigen Aktualisierungen des Typs 1 die Methode 3: Aktualisieren der Datenmaskierung.

Wenn das Attribut denormalisiert in die Faktentabelle eingebettet ist, kann die Situation günstiger sein, sofern Sie keine expliziten Start- und Enddaten für den Wert pflegen und stattdessen die Transaktionsdaten verwenden. Da der vorherige Wert für das Datum und die Uhrzeit der vorherigen Transaktionen gültig bleibt, müssen Sie die vorherigen Faktentabellenzeilen nicht ändern. Die Faktentabelle würde folgendermaßen aussehen:

TRANSACTION_DATE PRD_SK PRD_ID PRD_DESC PRD_CATEGORY UNITS AMOUNT
18. Juli 2017 123 ABC tolle Feuchtigkeitscreme – 100 oz Gesundheit und Schönheitspflege 2 25,16
19. Juli 2017 124 ABC tolle Feuchtigkeitscreme – 100 oz Kosmetik 1 13,50

Daten abfragen

BigQuery unterstützt standardmäßige SQL-Abfragen und ist mit ANSI SQL 2011 kompatibel. Die SQL-Referenz von Big Query bietet eine umfassende Beschreibung aller unterstützten Funktionen, Operatoren und Regex-Fähigkeiten.

Da BigQuery verschachtelte und sich wiederholende Felder als Teil des Datenmodells unterstützt, wurde sein SQL-Support ausgeweitet, um speziell diese Feldtypen zu unterstützen. Beispielsweise können Sie bei der Nutzung des öffentlichen GitHub-Datasets den UNNEST-Befehl ausführen, der Ihnen eine Iteration über ein wiederholtes Feld ermöglicht:

SELECT
  name, count(1) as num_repos
FROM
  `bigquery-public-data.github_repos.languages`, UNNEST(language)
GROUP BY name
ORDER BY num_repos
DESC limit 10

Interaktive Abfragen

Die BigQuery-Web-UI ermöglicht die interaktive Abfrage von Datasets und bietet eine konsolidierte Ansicht von Datasets für alle Projekte, auf die Sie zugreifen können. Die Konsole bietet außerdem mehrere nützliche Funktionen, wie etwa das Speichern und Teilen von Ad-hoc-Abfragen, das Abstimmen und Bearbeiten von historischen Abfragen, das Entdecken von Tabellen und Schemata und das Erfassen von Tabellenmetadaten. Weitere Informationen finden Sie in der BigQuery-Web-UI.

Bild: Screenshot BigQuery-Web-UI

Automatisierte Abfragen

Es ist gängige Praxis, die Ausführung von Abfragen basierend auf einem Zeitplan/Ereignis zu automatisieren und die Ergebnisse für einen späteren Gebrauch zwischenzuspeichern.

Wenn Sie mit Airflow andere automatisierte Aktivitäten orchestrieren und bereits mit dem Tool vertraut sind, verwenden Sie zu diesem Zweck die Apache Airflow API für BigQuery. In diesem Blogbeitrag werden Sie durch den Installationsprozess von Airflow und das Erstellen eines Workflows für BigQuery geführt.

Für einfachere Orchestrierungen können Sie sich auf Cronjobs verlassen. In diesem Blogbeitrag erfahren Sie, wie Sie eine Abfrage als App Engine-Anwendung verkapseln und als geplanten Cronjob ausführen.

Abfrageoptimierung

Jedes Mal, wenn BigQuery eine Abfrage ausführt, wird ein Vollspalten-Scan ausgeführt. BigQuery verwendet oder unterstützt keine Indexe. Da die Leistung von BigQuery und die Abfragekosten von der während einer Abfrage durchsuchten Datenmenge abhängen, sollten Sie Ihre Abfragen so gestalten, dass sie nur auf die für die Abfrage relevanten Spalten verweisen. Stellen Sie bei Verwendung von datumspartitionierten Tabellen sicher, dass nur die relevanten Partitionen gescannt werden. Sie können dies erreichen, wenn Sie Partitionsfilter verwenden, die auf PARTITIONTIME oder PARTITIONDATE basieren.

Um die Leistungsmerkmale nach Ausführung einer Abfrage zu verstehen, steht Ihnen eine ausführliche Erläuterung eines Abfrageplans zur Verfügung. Diese Erläuterung schlüsselt die Stufen, die eine Abfrage durchläuft, sowie die in jeder Stufe bearbeiteten Eingabe-/Ausgabezeilen und das Zeitprofil innerhalb jeder Stufe auf. Die Ergebnisse der Erläuterung können Ihnen dabei helfen, Ihre Abfragen zu verstehen und zu optimieren.

Bild: Screenshot der BigQuery-Ergebnisse

Externe Quellen

Sie können auch Abfragen für Daten durchführen, die sich außerhalb von BigQuery befinden, indem Sie Verbunddatenquellen verwenden. Dieser Ansatz hat jedoch Auswirkungen auf die Leistung. Nutzen Sie Verbunddatenquellen nur, wenn die Daten extern gepflegt werden müssen. Sie können auch den Abfrageverbund verwenden, um ETL von einer externen Quelle an BigQuery durchzuführen. Mit diesem Ansatz können Sie ETL mithilfe der bekannten SQL-Syntax definieren.

Benutzerdefinierte Funktionen

BigQuery unterstützt auch benutzerdefinierte Funktionen (UDFs) für Abfragen, die die Komplexität von SQL übersteigen. Mit UDFs können Sie die integrierten SQL-Funktionen erweitern. Sie verwenden eine Liste von Werten, die Arrays oder Strukturen sein können, und geben einen einzelnen Wert zurück, der auch ein Array oder eine Struktur sein kann. UDFs sind in JavaScript geschrieben und können externe Ressourcen wie Verschlüsselung oder andere Bibliotheken enthalten.

Abfragen freigeben

Mit BigQuery können Mitarbeiter Abfragen speichern und zwischen Teammitgliedern austauschen. Diese Funktion kann besonders bei der Datenexplorationsübungen nützlich sein oder als Mittel dienen, um jederzeit über ein neues Dataset oder Abfragemuster auf dem Laufenden zu bleiben. Weitere Informationen finden Sie unter Abfragen speichern und freigeben.

Daten analysieren

In diesem Abschnitt werden verschiedene Möglichkeiten vorgestellt, wie Sie eine Verbindung zu BigQuery herstellen und die Daten analysieren können. Um BigQuery als Analyse-Engine optimal nutzen zu können, sollten Sie die Daten im BigQuery-Speicher speichern. Für Ihren speziellen Anwendungsfall kann es jedoch von Vorteil sein, externe Quellen entweder einzeln zu analysieren oder mit Daten im BigQuery-Speicher zu verknüpfen.

Standardtools

Google Data Studio, das zum Zeitpunkt der Veröffentlichung dieses Dokuments in der Betaversion verfügbar war, sowie viele bereits in BigQuery integrierte Partnertools können zum Zeichnen von Analysen aus BigQuery und zum Erstellen komplexer interaktiver Datenvisualisierungen verwendet werden.

Wenn Sie sich in einer Situation befinden, in der Sie ein Tool auswählen müssen, finden Sie im Gartner Magic Quadrant-Bericht und im G2 Score-Bericht von G2 Crowd einen umfassenden Vergleich der Anbieter. Der Bericht von Gartner kann von vielen unserer Partner-Websites bezogen werden, beispielsweise von Tableau.

Bild: Partnerlogos

Benutzerdefinierte Entwicklung

Um benutzerdefinierte Anwendungen und Plattformen auf BigQuery aufzubauen, können Sie Clientbibliotheken verwenden, die für die meisten gängigen Programmiersprachen verfügbar sind, oder Sie können die REST API von BigQuery direkt verwenden.

Ein konkretes Beispiel finden Sie in dieser Anleitung, in der mithilfe von Python-Bibliotheken eine Verbindung zu BigQuery hergestellt und benutzerdefinierte interaktive Dashboards erstellt werden.

Drittanbieteranschlüsse

Um sich von einer Anwendung mit BigQuery zu verbinden, die ursprünglich nicht auf API-Ebene in BigQuery eingebunden ist, können Sie die BigQuery JDBC- und ODBC-Treiber verwenden. Die Treiber agieren als Brücke, um bei alten Anwendungen oder unveränderlichen Anwendungen, wie Microsoft Excel, mit BigQuery zu interagieren. Obwohl ODBC und JDBC die Interaktion mit BigQuery mithilfe von SQL unterstützen, sind die Treiber nicht so aussagekräftig wie der direkte Umgang mit der API.

Kosten

Die meisten Data Warehouses bedienen mehrere Unternehmenseinheiten innerhalb der Organisation. Eine häufige Herausforderung besteht darin, die Betriebskosten pro Unternehmenseinheit zu analysieren. Anleitungen zum Aufteilen Ihrer Rechnung und zum Zuordnen von Kosten zum Verbrauch finden Sie unter Google Cloud-Abrechnung mit BigQuery und Data Studio visualisieren.

Es gibt drei Hauptkostendimensionen für BigQuery: Lade-, Speicher- und Abfragekosten. Dieser Abschnitt behandelt jede Dimension im Detail.

Daten speichern

Die Berechnung der Speicherkosten erfolgt pro MB/s.

Wenn eine Tabelle innerhalb von 90 aufeinander folgenden Tagen nicht bearbeitet wurde, reduziert sich der Preis für die Speicherung dieser Tabelle um 50 % auf 0,01 $ pro GB und Monat. Wenn eine Tabelle in die Langzeitspeicherung übergeht, kommt es zu keiner Beeinträchtigung von Leistung, Langlebigkeit, Verfügbarkeit oder anderer Funktionen. Wenn die Daten in einer Tabelle geändert werden, setzt BigQuery den Timer für die Tabelle zurück und alle Daten in der Tabelle werden auf den normalen Speicherpreis zurückgesetzt. Aktionen, die die Daten nicht direkt bearbeiten, z. B. das Abfragen und Erstellen von Ansichten, setzen den Timer nicht zurück.

Weitere Details finden Sie unter BigQuery-Speicherpreise.

Daten laden

Sie können Daten mit einem herkömmlichen Ladeauftrag kostenlos in BigQuery laden. Nach dem Laden der Daten bezahlen Sie den Speicherplatz wie oben beschrieben.

Streaming-Insert-Anweisungen werden basierend auf der Datenmenge berechnet, die gestreamt wird. Weitere Informationen finden Sie unter BigQuery-Speicherpreise in "Kosten für Streaming-Insert-Anweisungen".

Daten abfragen

Für Abfragen bietet BigQuery zwei Preismodelle: On-Demand- und Pauschalpreise.

On-Demand-Preise

Beim On-Demand-Modell berechnet BigQuery den Preis nach der Datenmenge, auf die während der Abfrage zugegriffen wird. Da BigQuery ein säulenartiges Speicherformat nutzt, wird nur auf die Säulen zugegriffen, die für Ihre Abfrage relevant sind. Wenn Sie nur wöchentliche oder monatliche Berichte erstellen und weniger als 1 TB Ihrer Daten abfragen, sind die Kosten für Abfragen auf Ihrer Rechnung möglicherweise sehr niedrig. Weitere Informationen zu den Preisen für Abfragen finden Sie unter BigQuery-Abfragepreise.

Mithilfe der Abfrageüberprüfung in der Web-UI können Sie vorab ermitteln, wie viele Daten von einer bestimmten Abfrage gescannt werden. Im Fall einer benutzerdefinierten Entwicklung können Sie das Flag dryRun in der API-Anfrage setzen und BigQuery den Job nicht ausführen lassen. Geben Sie stattdessen Statistiken zum Job zurück, z. B. die Anzahl der zu verarbeitenden Byte. Weitere Informationen finden Sie in der Abfrage-API.

Grafik: Abfrage-API

Pauschalpreise

Kunden, die mehr Konsistenz bei den monatlichen Ausgaben bevorzugen, können die Pauschalpreise aktivieren. Weitere Informationen finden Sie unter BigQuery-Pauschalpreise.

Weitere Informationen