Kapitel 4. Daten in BigQuery laden
Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: translation-feedback@oreilly.com
Im vorherigen Kapitel haben wir die folgende Abfrage geschrieben:
SELECT
state_name
FROM `bigquery-public-data`.utility_us.us_states_area
WHERE
ST_Contains(
state_geom,
ST_GeogPoint(-122.33, 47.61))
Wir haben auch erfahren, dass die Stadt am Standort (-122.33, 47.61) im Bundesstaat Washington liegt. Woher stammen die Daten für state_name und state_geom?
Beachte die FROM Klausel in der Abfrage. Die Eigentümer des bigquery-public-data Projekts hatten die Informationen über die Staatsgrenzen bereits in eine Tabelle namens us_states_area in einem Datensatz namens utility_us geladen. Da das Team den Datensatz utility_us für alle authentifizierten Nutzer von BigQuery freigegeben hat (es sind auch restriktivere Berechtigungen möglich), konnten wir die Tabelle us_states_area in diesem Datensatz abfragen.
Aber wie haben sie die Daten überhaupt in BigQuery bekommen? In diesem Kapitel sehen wir uns verschiedene Möglichkeiten an, Daten in BigQuery zu laden, angefangen bei den Grundlagen.
Die Grundlagen
Datenwerte wie die Grenzen der US-Bundesstaaten ändern sich selten,1 und die Änderungen sind so gering, dass die meisten Anwendungen es sich leisten können, sie zu ignorieren. Im Data-Warehousing-Jargon nennen wir das eine sich langsam verändernde Dimension. Die letzte Änderung der Grenzen der US-Bundesstaaten fand am 1. Januar 2017 statt und betraf 19 Hausbesitzer und eine Tankstelle.2
Daten zu den Staatsgrenzen sind daher die Art von Daten, die oft nur einmal geladen werden. Analysten fragen die einzelne Tabelle ab und ignorieren die Tatsache, dass sich die Daten im Laufe der Zeit ändern können. Ein Einzelhandelsunternehmen interessiert sich zum Beispiel nur dafür, in welchem Bundesstaat ein Haus liegt, um sicherzustellen, dass der richtige Steuersatz auf Einkäufe in diesem Haus angewendet wird. Wenn sich also eine Änderung ergibt, z. B. durch ein Abkommen zwischen Staaten oder durch eine Änderung des Verlaufs eines Flusses, könnten die Eigentümer des Datensatzes beschließen, die Tabelle durch aktuellere Daten zu ersetzen. Die Tatsache, dass Abfragen nach einer Aktualisierung möglicherweise etwas andere Ergebnisse liefern könnten als vor der Aktualisierung, wird ignoriert.
Es ist nicht immer möglich, den Einfluss der Zeit auf die Korrektheit der Daten zu ignorieren. Wenn die Daten zu den Staatsgrenzen von einem Grundbuchamt verwendet werden sollen, das die Eigentumsverhältnisse von Grundstücken nachverfolgen muss, oder wenn ein Wirtschaftsprüfungsunternehmen die in verschiedenen Jahren gezahlten Steuern überprüfen muss, ist es wichtig, dass es eine Möglichkeit gibt, die Staatsgrenzen abzufragen, wie sie in den vergangenen Jahren bestanden. Auch wenn im ersten Teil dieses Kapitels beschrieben wird, wie man eine einmalige Abfrage durchführt, solltest du sorgfältig abwägen, ob es nicht besser wäre, die Daten regelmäßig zu aktualisieren und den Nutzern der Daten die Möglichkeit zu geben, die Version der Daten zu kennen, die sie abfragen.
Laden aus einer lokalen Quelle
Die US-Regierung gibt eine "Scorecard" für Colleges heraus, um den Verbrauchern zu helfen, die Kosten und den wahrgenommenen Wert der Hochschulbildung zu vergleichen. Zur Veranschaulichung wollen wir diese Daten in BigQuery laden. Die Rohdaten sind auf catalog.data.gov verfügbar. Der Einfachheit halber haben wir sie auch als 04_load/college_scorecard.csv.gz im GitHub-Repository für dieses Buch verfügbar. Die CSV-Datei (Comma-Separated Values) wurde von data.gov heruntergeladen und mit dem Open-Source-Softwareprogramm gzip komprimiert.
Tipp
Warum haben wir die Datei komprimiert? Die rohe, unkomprimierte Datei ist etwa 136 MB groß, während die gzipped Datei nur 18 MB groß ist. Da wir die Datei über das Internet an BigQuery senden werden, ist es sinnvoll, die übertragene Bandbreite zu optimieren. Der BigQuery-Ladebefehl kann mit gzipped Dateien umgehen, aber er kann keine Teile einer gzipped Datei parallel laden. Das Laden von wäre viel schneller, wenn wir BigQuery eine aufteilbare Datei übergeben würden, entweder eine unkomprimierte CSV-Datei, die sich bereits auf der Cloud Speicherung befindet (so dass der Netzwerkübertragungsaufwand minimiert wird) oder Daten in einem Format wie Avro, bei dem jeder Block intern komprimiert ist, die Datei als Ganzes aber auf die Worker aufgeteilt werden kann.
Eine aufteilbare Datei kann von verschiedenen Workern geladen werden, die an verschiedenen Stellen der Datei beginnen. Das setzt aber voraus, dass die Worker in der Lage sind, einen vorhersehbaren Punkt in der Mitte der Datei zu finden, ohne sie von Anfang an lesen zu müssen. Bei der Komprimierung der gesamten Datei mit gzip ist das nicht möglich, aber bei einer blockweisen Komprimierung wie Avro schon. Daher ist die Verwendung eines komprimierten, aufteilbaren Formats wie Avro ein unbedingtes Plus. Wenn du jedoch CSV- oder JSON-Dateien hast, die nur im unkomprimierten Zustand geteilt werden können, solltest du abwägen, ob die schnellere Netzwerkübertragung durch die längere Ladezeit ausgeglichen wird.
Von der Cloud Shell aus kannst du mit zless durch die gzipped Datei blättern:
zless college_scorecard.csv.gz
Hinweis
Hier sind die genauen Schritte:
-
Öffne Cloud Shell in deinem Browser, indem du https://console.cloud.google.com/cloudshell aufrufst .
-
Gib im Terminalfenster Folgendes ein:
git clone https://github.com/GoogleCloudPlatform/bigquery-oreilly-book. -
Navigiere zu dem Ordner, der die College Scorecard-Datei enthält:
cd bigquery-oreilly-book/04_load. -
Gib den Befehl
zless college_scorecard.csv.gzein und benutze dann die Leertaste, um durch die Daten zu blättern. Tippe den Buchstabenqum zu beenden.
Die Datei enthält eine Kopfzeile mit den Namen der Spalten. Jede der auf die Kopfzeile folgenden Zeilen enthält eine Zeile mit Daten.
Um die Daten in BigQuery zu laden, erstellst du zunächst unter ein Dataset namens ch04, das die Daten enthält:
bq --location=US mk ch04
Das Befehlszeilentool bq bietet einen bequemen Einstiegspunkt, um mit dem BigQuery-Dienst auf der Google Cloud Platform (GCP) zu interagieren, obwohl alles, was du mit bq machst, auch über die REST-API erledigt werden kann. Und die meisten Dinge kannst du über die GCP Cloud Console erledigen. Wir bitten sie hier, (mk) ein Dataset namens ch04 zu erstellen.
Datasets in BigQuery funktionieren wie übergeordnete Ordner, mit denen der Zugriff auf Tabellen, Ansichten und Machine-Learning-Modelle organisiert und kontrolliert wird. Das Dataset wird im aktuellen Projekt erstellt,3 und diesem Projekt werden die Kosten für die Speicherung der Tabellen in diesem Dataset in Rechnung gestellt (Abfragen werden dem Projekt des Abfragenden in Rechnung gestellt).
Wir geben auch an, dass der Datensatz in den USA erstellt werden soll (das ist die Standardeinstellung, wir hätten das also auch weglassen können). Zur Auswahl stehen multiregionale Standorte (wie US, EU) und bestimmte Regionen (z. B. us-east4, europe-west2 und australia-southeast1).4 Sei vorsichtig, wenn du eine Region für das Laden von Daten auswählst: Zum jetzigen Zeitpunkt können Abfragen keine Tabellen in verschiedenen Regionen verbinden. In diesem Buch werden wir die Multiregion US verwenden, damit unsere Abfragen Tabellen in den öffentlichen Datensätzen in den Vereinigten Staaten verknüpfen können.
Dann lädst du die Daten aus dem Verzeichnis, das deinen Klon des GitHub-Repositorys enthält, als Tabelle in BigQuery:
bq --location=US \ load \ --source_format=CSV --autodetect \ ch04.college_scorecard \ ./college_scorecard.csv.gz
In diesem Fall bitten wir bq, den Datensatz zu laden, und teilen dem Tool mit, dass das Quellformat CSV ist und dass wir möchten, dass das Tool das Schema (d. h. die Datentypen der einzelnen Spalten) automatisch erkennt. Dann geben wir an, dass die zu erstellende Tabelle im Dataset ch04 college_scorecard heißt und dass die Daten aus college_scorecard.csv.gz im aktuellen Verzeichnis geladen werden sollen.
Als wir das taten, stießen wir allerdings auf ein Problem:
Could not parse 'NULL' as int for field HBCU (position 26) starting at location 11945910
Dies führte dazu, dass der Ladeauftrag mit folgender Fehlermeldung fehlschlug:5
CSV table encountered too many errors, giving up. Rows: 591; errors: 1.
Das Problem ist, dass die Schemaerkennung von BigQuery auf der Grundlage der meisten Daten in der CSV-Datei erwartet, dass die 26. Spalte (deren Name HBCU ist) eine ganze Zahl sein sollte, aber die 591. Zeile der Datei hat den Text NULL in diesem Feld - was normalerweise bedeutet, dass die betreffende Hochschule die diesem Feld entsprechende Umfrage nicht beantwortet hat.6
Es gibt mehrere Möglichkeiten, wie wir dieses Problem lösen können. Wir könnten zum Beispiel die Datendatei selbst bearbeiten, wenn wir wüssten, wie der Wert lauten sollte. Eine andere Möglichkeit wäre, das Schema für jede Spalte explizit anzugeben und den Spaltentyp der Spalte HBCU in einen String zu ändern, damit NULL ein akzeptabler Wert ist. Alternativ könnten wir BigQuery bitten, ein paar fehlerhafte Datensätze zu ignorieren, indem wir z. B. --max_bad_records=20 angeben. Schließlich könnten wir das BigQuery-Ladeprogramm anweisen, dass diese Datei die Zeichenkette NULL verwendet, um Nullen zu markieren (in CSV werden standardmäßig leere Felder verwendet, um Nullen darzustellen).
Wenden wir die letzte Methode an, denn sie scheint die geeignetste zu sein:7
bq --location=US \ load --null_marker=NULL \ --source_format=CSV --autodetect \ ch04.college_scorecard \ ./college_scorecard.csv.gz
Du kannst die vollständige Liste der bq load Optionen finden, indem du bq load --help eingibst. Standardmäßig wird bq load an eine Tabelle angehängt. Hier willst du die bestehende Tabelle ersetzen, also solltest du --replace hinzufügen:
bq --location=US \ load --null_marker=NULL --replace \ --source_format=CSV --autodetect \ ch04.college_scorecard \ ./college_scorecard.csv.gz
Du kannst auch --replace=false angeben, um Zeilen an eine bestehende Tabelle anzuhängen.
Es ist erwähnenswert, dass du einmalige Ladungen über die BigQuery-Web-Benutzeroberfläche (UI) durchführen kannst. Wenn du auf dein Projekt klickst, wird dir eine Schaltfläche angezeigt, mit der du einen Datensatz erstellen kannst (in unserem Fallch04). Wenn du auf den Datensatz klickst, wird dir eine Schaltfläche zum Erstellen einer Tabelle angezeigt. Du kannst dann den Eingabeaufforderungen folgen, um die Datei als BigQuery-Tabelle hochzuladen. Zum jetzigen Zeitpunkt ist die Verwendung der Web-UI zum Laden von Daten aus einer lokalen Datei jedoch auf Daten mit einer Größe von weniger als 10 MB und 16.000 Zeilen beschränkt. Daher würde es für den Datensatz der College Scorecard nicht funktionieren, wenn wir ihn nicht vorher in Google Cloud Storage gespeichert hätten.
Auch wenn du die Daten nicht über die Web-UI geladen hast (oder laden kannst), ist es eine gute Idee, die erstellte Tabelle über die Web-UI anzusehen, um sicherzustellen, dass die Details der Tabelle und das automatisch erkannte Schema korrekt sind. Es ist auch möglich, einige Details der Tabelle zu bearbeiten, selbst nachdem sie erstellt wurde. Es ist zum Beispiel möglich, festzulegen, dass die Tabelle nach einer bestimmten Anzahl von Tagen automatisch abläuft, Spalten hinzuzufügen oder ein erforderliches Feld so zu ändern, dass es gelöscht werden kann.
Hinweis
Du kannst auch ein Verfallsdatum mit der Anweisung ALTER TABLE SET OPTIONS festlegen - zum Beispiel:
ALTER TABLE ch04.college_scorecard
SET OPTIONS (
expiration_timestamp=
TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
description="College Scorecard table that expires
seven days from now"
)
Weitere Informationen findest du unter https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#alter_table_set_options_statement.
Unabhängig davon, wie die Tabelle geladen wird, kann jeder, der auf das Dataset zugreifen darf, in dem sich die Tabelle befindet, sie abfragen. In der Standardeinstellung ist ein neu erstelltes Dataset nur für Personen mit Ansichtsrechten auf Projektebene sichtbar. Du kannst das Dataset jedoch auch9 für bestimmte Personen (identifiziert durch ihr Google-Konto), eine Domain (z. B. xyz.com) oder eine Google-Gruppe freigeben. Wir besprechen die Verwendung von Identitäts- und Zugriffsmanagement (IAM) zur Freigabe von Datensätzen in Kapitel 10. Im Moment kann jedoch jeder, der Zugriff auf das Projekt hat, das den Datensatz enthält, diesen abfragen:
SELECT INSTNM , ADM_RATE_ALL , FIRST_GEN , MD_FAMINC , MD_EARN_WNE_P10 , SAT_AVG FROM ch04.college_scorecard WHERE SAFE_CAST(SAT_AVG AS FLOAT64) > 1300 AND SAFE_CAST(ADM_RATE_ALL AS FLOAT64) < 0.2 AND SAFE_CAST(FIRST_GEN AS FLOAT64) > 0.1 ORDER BY CAST(MD_FAMINC AS FLOAT64) ASC
Diese Abfrage ermittelt den Namen der Einrichtung (INSTNM), die Zulassungsquote und andere Informationen für Hochschulen, deren durchschnittliche SAT-Punktzahl über 1300 liegt und deren Zulassungsquote weniger als 20% beträgt, was eine plausible Definition von "Elite"-Hochschulen ist. Die Abfrage filtert außerdem nach Hochschulen, die mehr als 10 % Studierende der ersten Generation aufnehmen, und ordnet sie in aufsteigender Reihenfolge nach dem mittleren Familieneinkommen ein, um Elite-Hochschulen zu finden, die kulturell oder wirtschaftlich benachteiligte Studierende aufnehmen. Die Abfrage bezieht sich auch auf das durchschnittliche Einkommen der Studierenden 10 Jahre nach ihrem Eintritt:
| Reihe | INSTNM | ADM_RATE_ALL | ERSTE_GEN | MD_FAMINC |
MD_EARN _WNE_P10 |
SAT_AVG |
|---|---|---|---|---|---|---|
| 1 | Universität von Kalifornien-Berkeley | 0.1692687830816 | 0.3458005249 | 31227 | 64700 | 1422 |
| 2 | Columbia University in der Stadt New York | 0.06825366802669 | 0.2504905167 | 31310.5 | 83300 | 1496 |
| 3 | Universität von Kalifornien-Los Angeles | 0.17992627069775 | 0.3808913934 | 32613.5 | 60700 | 1334 |
| 4 | Harvard Universität | 0.05404574677902 | 0.25708061 | 33066 | 89700 | 1506 |
| 5 | Princeton Universität | 0.06521516568269 | 0.2773972603 | 37036 | 74700 | 1493 |
Schau dir jedoch die Abfrage selbst an. Beachte, dass mehrere der WHERE Klauseln einen Cast benötigen:
SAFE_CAST(ADM_RATE_ALL AS FLOAT64)
Hätten wir nicht mit einbezogen, hätten wir einen Fehler erhalten:
No matching signature for operator > for argument types: STRING, INT64.
Hätten wir einfach als Float gecastet, wäre es in einer Zeile fehlgeschlagen, in der der Wert ein String (PrivacySuppressed) war, der nicht als Float gecastet werden kann:
Bad double value: PrivacySuppressed; while executing the filter ...
Das liegt daran, dass die automatische Schemaerkennung die Spalte "Zulassungsquote" nicht als numerisch identifiziert hat. Stattdessen wird diese Spalte als String behandelt, weil der Wert in einigen Zeilen aus Datenschutzgründen unterdrückt (z. B. wenn die Zahl der Bewerbungen sehr gering ist) und durch den Text PrivacySuppressed ersetzt wird. Sogar der Median des Familieneinkommens ist eine Zeichenkette (bei Hochschulen, die die von uns beschriebenen Kriterien erfüllen, ist er immer numerisch) und muss daher vor der Bestellung umgewandelt werden.10
Festlegen eines Schemas
In realen Datensätzen müssen wir zwangsläufig einige Bereinigungen und Transformationen vornehmen, bevor wir die Daten in BigQuery laden. Obwohl wir uns später in diesem Kapitel mit dem Aufbau von anspruchsvolleren Datenverarbeitungspipelines befassen, besteht eine einfache Möglichkeit darin, Unix-Tools zu verwenden, um datenschutzunterdrückte Daten durch NULLs zu ersetzen:
zless ./college_scorecard.csv.gz | \
sed 's/PrivacySuppressed/NULL/g' | \
gzip > /tmp/college_scorecard.csv.gz
Hier verwenden wir einen String-Editor (sed), um alle Vorkommen von PrivacySuppressed durch NULL zu ersetzen, das Ergebnis zu komprimieren und in einen temporären Ordner zu schreiben. Anstatt die Originaldatei zu laden, können wir nun die bereinigte Datei laden.
Wenn die bereinigte Datei angezeigt wird, erkennt BigQuery viele weitere Spalten korrekt als Ganzzahlen oder Fließkommazahlen, aber nicht SAT_AVG oder ADM_RATE_ALL. Diese Spalten werden immer noch automatisch als Zeichenketten erkannt. Das liegt daran, dass der Algorithmus zur automatischen Erkennung des Schemas nicht alle Zeilen in der Datei untersucht, sondern nur eine Auswahl davon. Da eine große Anzahl von Zeilen eine Null hat SAT_AVG (weniger als 20% der Colleges melden SAT-Punkte), konnte der Algorithmus nicht auf den Typ des Feldes schließen. Die sichere Wahl ist, jede Spalte, bei der sich das Tool nicht sicher ist, als String zu behandeln.
Es ist daher die bewährte Methode, das Schema von Dateien, die du in der Produktion erhältst, nicht automatisch zu erkennen - du bist den Daten ausgeliefert, die zufällig abgerufen wurden. Bestehe bei der Produktion auf dem Datentyp einer Spalte, indem du ihn zum Zeitpunkt des Ladens angibst.
Du kannst die Autodetect-Funktion nutzen, um zu vermeiden, dass du ein Schema von Grund auf neu schreiben musst. Du kannst das Schema der Tabelle so anzeigen, wie es derzeit existiert:
bq show --format prettyjson --schema ch04.college_scorecard
Du kannst das Schema auch in einer Datei speichern:
bq show --format prettyjson --schema ch04.college_scorecard > schema.json
Jetzt kannst du die Schemadatei in deinem bevorzugten Texteditor öffnen (wenn du keine Präferenz hast, benutze das Stiftsymbol in Cloud Shell , um den Standardeditor zu öffnen) und den Typ der Spalten ändern, die dich interessieren. Ändere vor allem die vier Spalten in der WHERE Klausel (SAT_AVG, ADM_RATE_ALL, FIRST_GEN und MD_FAMINC) in FLOAT64:
{
"mode": "NULLABLE",
"name": "FIRST_GEN",
"type": "FLOAT64"
},
Ändere außerdem (vorerst) T4APPROVALDATE in eine Zeichenkette um, da es sich um ein nicht standardisiertes Datumsformat handelt:11
{
"mode": "NULLABLE",
"name": "T4APPROVALDATE",
"type": "STRING"
},
Nachdem das Schema aktualisiert wurde, können wir die Daten mit diesem Schema laden und nicht mit der automatischen Erkennung:
bq --location=US \ load --null_marker=NULL --replace \ --source_format=CSV \ --schema=schema.json --skip_leading_rows=1 \ ch04.college_scorecard \ ./college_scorecard.csv.gz
Da wir ein Schema liefern, müssen wir BigQuery anweisen, die erste Zeile der CSV-Datei (die die Kopfdaten enthält) zu ignorieren.
Nachdem die Tabelle geladen wurde, können wir die Abfrage aus dem vorherigen Abschnitt wiederholen:
SELECT INSTNM , ADM_RATE_ALL , FIRST_GEN , MD_FAMINC , MD_EARN_WNE_P10 , SAT_AVG FROM ch04.college_scorecard WHERE SAT_AVG > 1300 AND ADM_RATE_ALL < 0.2 AND FIRST_GEN > 0.1 ORDER BY MD_FAMINC ASC
Da SAT_AVG, ADM_RATE_ALL und die anderen keine Strings mehr sind, ist unsere Abfrage viel sauberer, weil wir sie nicht mehr in Fließkommazahlen umwandeln müssen. Der Grund dafür, dass es sich nicht mehr um Strings handelt, ist, dass wir während des Extrahier-, Transformier- und Ladevorgangs (ETL) eine Entscheidung getroffen haben, wie wir mit den datenschutzunterdrückten Daten umgehen (sie als nicht verfügbar behandeln).
Kopieren in eine neue Tabelle
Die geladene Tabelle enthält viele Spalten, die wir nicht brauchen. Es ist möglich, aus der ursprünglichen Tabelle eine übersichtlichere, zweckmäßigere Tabelle zu erstellen, indem du die Anweisung CREATE TABLE verwendest und die neue Tabelle nur mit den Spalten füllst, die von Interesse sind:
CREATE OR REPLACE TABLE ch04.college_scorecard_etl AS
SELECT
INSTNM
, ADM_RATE_ALL
, FIRST_GEN
, MD_FAMINC
, SAT_AVG
, MD_EARN_WNE_P10
FROM ch04.college_scorecard
Durch den Einsatz einer robusten ETL-Pipeline und frühzeitigen Entscheidungen sind die nachgelagerten Abfragen sauberer und präziser. Der Kompromiss besteht darin, dass der ETL-Prozess zusätzliche Arbeit bedeutet (Bestimmung der Datentypen und Festlegung des Schemas) und möglicherweise unwiderrufliche Entscheidungen mit sich bringt (z. B. gibt es keine Möglichkeit, zurückzubekommen, ob ein Feld nicht verfügbar ist, weil es nicht erfasst wurde, weil es aus Datenschutzgründen unterdrückt wurde oder weil es gelöscht wurde). Später in diesem Kapitel werden wir erörtern, wie eine ELT-Pipeline in SQL uns dabei helfen kann, unwiderrufliche Entscheidungen zu verzögern.
Datenmanagement (DDL und DML)
Warum wird die Datenverwaltung in einem Kapitel über das Laden von Daten behandelt? Weil das Laden von Daten in der Regel nur ein Teil der Aufgabe der Datenverwaltung ist. Wenn Daten versehentlich geladen werden, musst du sie vielleicht löschen. Manchmal musst du Daten aufgrund von Vorschriften und Bestimmungen löschen.
Warnung
Auch wenn wir normalerweise wollen, dass du alle Befehle und Abfragen in diesem Buch ausprobierst, solltest du die in diesem Abschnitt nicht ausprobieren, weil du sonst deine Daten verlierst!
Der einfachste Weg, eine Tabelle (oder Ansicht) als Ganzes zu löschen, ist über die BigQuery-Benutzeroberfläche. Du kannst den Löschvorgang auch über das Kommandozeilentool bq durchführen:
bq rm ch04.college_scorecard bq rm -r -f ch04
Die erste Zeile entfernt eine einzelne Tabelle, während die zweite Zeile rekursiv (-r) und ohne Eingabeaufforderung (-f, zur Erzwingung) das Dataset ch04 und alle darin enthaltenen Tabellen entfernt.
Du kannst auch eine Tabelle (oder Ansicht) über mit SQL löschen:
DROP TABLE IF EXISTS ch04.college_scorecard_gcs
Es ist auch möglich, festzulegen, dass eine Tabelle zu einem bestimmten Zeitpunkt in der Zukunft ablaufen muss. Das kannst du mit der Anweisung ALTER TABLE SET OPTIONS tun:
ALTER TABLE ch04.college_scorecard
SET OPTIONS (
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),
INTERVAL 7 DAY),
description="College Scorecard expires seven days from now"
)
Die Anweisungen DROP TABLE und ALTER TABLE sind, wie die Anweisung CREATE TABLE, Beispiele für DDL-Anweisungen (Data Definition Language).
Es ist möglich, nur bestimmte Zeilen aus einer Tabelle zu löschen - zum Beispiel:
DELETE FROM ch04.college_scorecard WHERE SAT_AVG IS NULL
Genauso ist es möglich, INSERT Zeilen in eine bestehende Tabelle einzufügen, anstatt die gesamte Tabelle zu ersetzen. Es ist zum Beispiel möglich, weitere Werte in die college_scorecard Tabelle einzufügen, indem du Folgendes tust:
INSERT ch04.college_scorecard
(INSTNM
, ADM_RATE_ALL
, FIRST_GEN
, MD_FAMINC
, SAT_AVG
, MD_EARN_WNE_P10
)
VALUES ('abc', 0.1, 0.3, 12345, 1234, 23456),
('def', 0.2, 0.2, 23451, 1232, 32456)
Es ist möglich, mit einer Subquery Werte aus einer Tabelle zu extrahieren und sie in eine andere zu kopieren:
INSERT ch04.college_scorecard SELECT * FROM ch04.college_scorecard_etl WHERE SAT_AVG IS NULL
Die Anweisungen DELETE, INSERT und MERGE sind Beispiele von Data Manipulation Language (DML) Anweisungen.
Tipp
Zum jetzigen Zeitpunkt unterstützt BigQuery die Anweisung SQL COPY nicht. Um Tabellen zu kopieren, verwendest du bq cp, um eine Tabelle in eine andere zu kopieren:
bq cp ch04.college_scorecard someds.college_scorecard_copy
Das Ausführen einer Abfrage wird dir nicht in Rechnung gestellt, wohl aber die Speicherung der neuen Tabelle. Der Befehl bq cp unterstützt das Anhängen (gib -a oder --append_table an) und Ersetzen (gib -noappend_table).
Du kannst auch die idiomatische Standard-SQL-Methode verwenden, indem du entweder CREATE TABLE AS SELECT oder INSERT VALUES benutzt, je nachdem, ob das Ziel bereits existiert. bq cp ist jedoch schneller (weil es nur die Metadaten der Tabelle kopiert) und verursacht keine Abfragekosten.
Effizientes Laden von Daten
Obwohl BigQuery Daten aus CSV-Dateien laden kann, sind CSV-Dateien ineffizient und nicht sehr aussagekräftig (zum Beispiel gibt es keine Möglichkeit, Arrays und Structs in CSV darzustellen). Wenn du die Wahl hast, solltest du dich für den Export deiner Daten in einem anderen Format entscheiden. Welches Format solltest du wählen?
Ein effizientes und aussagekräftiges Format ist Avro. Avro verwendet selbstbeschreibende Binärdateien, die in Blöcke unterteilt sind und Block für Block komprimiert werden können. Dadurch ist es möglich, das Laden von Daten aus Avro-Dateien und den Export von Daten in Avro-Dateien zu parallelisieren. Da die Blöcke komprimiert werden, sind auch die Dateigrößen kleiner, als es die Datengröße vermuten ließe. Das Avro-Format ist hierarchisch und kann verschachtelte und sich wiederholende Felder darstellen - etwas, das BigQuery unterstützt, das aber in CSV-Dateien nicht so einfach zu speichern ist. Da Avro-Dateien selbstbeschreibend sind, musst du nie ein Schema angeben.
Es gibt zwei Nachteile von Avro-Dateien. Der eine ist, dass sie nicht für Menschen lesbar sind. Wenn du Wert auf Lesbarkeit und Aussagekraft legst, solltest du JSON-Dateien mit Newline-Trennung verwenden12 um deine Daten zu speichern. JSON unterstützt die Möglichkeit, hierarchische Daten zu speichern, erfordert aber, dass die binären Spalten base-64 kodiert sind. JSON-Dateien sind jedoch größer als die entsprechenden CSV-Dateien, da der Name jedes Feldes in jeder Zeile wiederholt wird. Der zweite Nachteil ist, dass Avro-Dateien Zeile für Zeile gespeichert werden. Das macht Avro-Dateien für Verbundabfragen nicht so effizient.
Das Parquet-Dateiformat wurde von Googles ursprünglichem Dremel ColumnIO-Format inspiriert,13 und wie Avro ist Parquet binär, blockorientiert, kompakt und in der Lage, hierarchische Daten darzustellen. Während Avro-Dateien jedoch zeilenweise gespeichert werden, werden Parquet-Dateien spaltenweise gespeichert. Columnar-Dateien sind für das Lesen einer Teilmenge der Spalten optimiert; zum Laden von Daten müssen alle Spalten gelesen werden, daher sind Columnar-Formate etwas weniger effizient beim Laden von Daten. Das Columnar-Format macht Parquet jedoch zu einer besseren Wahl als Avro für föderierte Abfragen, ein Thema, auf das wir gleich eingehen werden. Optimized Row Columnar (ORC)-Dateien sind ein weiteres spaltenförmiges Open-Source-Dateiformat. ORC ist in Bezug auf Leistung und Effizienz mit Parquet vergleichbar.
Wenn du also die Wahl zwischen verschiedenen Dateiformaten hast, empfehlen wir Avro, wenn du die Daten in BigQuery laden und die Dateien verwerfen willst. Wir empfehlen Parquet, wenn du die Dateien für föderierte Abfragen aufbewahren willst. Verwende JSON für kleine Dateien, bei denen die Lesbarkeit für Menschen wichtig ist.
Auswirkungen von Komprimierung und Staging über Google Cloud Storage
Bei Formaten wie CSV und JSON, die keine interne Komprimierung haben, solltest du überlegen, ob du die Dateien mit gzip komprimieren solltest. Komprimierte Dateien lassen sich schneller übertragen und nehmen weniger Platz ein, aber sie werden langsamer in BigQuery geladen. Je langsamer dein Netzwerk ist, desto eher solltest du zur Komprimierung der Daten tendieren.
Wenn du in einem langsamen Netzwerk arbeitest oder wenn du viele oder sehr große Dateien hast, ist es möglich, einen Multithread-Upload der Daten mit gsutil cp einzurichten. Nachdem die Daten alle auf Google Cloud Storage gespeichert sind, kannst du bq load von der Cloud Speicherung aus aufrufen:
gsutil -m cp *.csv gs://BUCKET/some/location bqload … gs://BUCKET/some/location/*.csv
Dieses Experiment zeigt die verschiedenen Kompromisse auf, die mit der Komprimierung und der Speicherung der College-Scorecard-Daten auf der Cloud-Speicherung verbunden sind, bevor bq load aufgerufen wird. In Tabelle 4-1 wird dies näher untersucht. Deine Ergebnisse hängen natürlich von deinem Netzwerk und den Daten ab, die du lädst.14 Deshalb solltest du eine ähnliche Messung für deinen Ladeauftrag durchführen und die Methode wählen, die dir die beste Leistung bei den für dich wichtigen Kennzahlen liefert.
| Komprimierte Datei | Stufe auf GCS? | GCS-Größe | Netzwerkzeit (falls separat) | Zeit zum Laden in BigQuery | Gesamtzeit |
|---|---|---|---|---|---|
| Ja | Nein | Keine | N/A | 105 Sekunden | 105 Sekunden |
| Nein | Nein | Keine | N/A | 255 Sekunden | 255 Sekunden |
| Ja | Ja | 16 MB | 47 sec | 42 Sekunden | 89 Sekunden |
| Nein | Ja | 76 MB | 139 sec | 28 sec | 167 sec |
Wenn du die Datei auf Google Cloud Storage bereitstellst, musst du mindestens so lange für die Speicherung zahlen, bis der BigQuery-Ladeauftrag abgeschlossen ist. Die Kosten für die Speicherung sind jedoch in der Regel recht niedrig, so dass es bei diesem Datensatz und dieser Netzwerkverbindung (siehe Tabelle 4-1) die beste Option ist, die komprimierten Daten in Cloud Storage bereitzustellen und von dort zu laden. Es ist zwar schneller, unkomprimierte Dateien in BigQuery zu laden, aber die Netzwerkzeit für die Übertragung der Dateien übersteigt die Vorteile, die du durch ein schnelleres Laden hättest.
Zum jetzigen Zeitpunkt ist das Laden von komprimierten CSV- und JSON-Dateien auf Dateien mit einer Größe von weniger als 4 GB beschränkt, da BigQuery die Dateien während des Betriebs auf Workern dekomprimieren muss, deren Speicherplatz begrenzt ist. Wenn du größere Datensätze hast, kannst du sie auf mehrere CSV- oder JSON-Dateien aufteilen. Wenn du die Dateien selbst aufteilst, kannst du die Ladevorgänge bis zu einem gewissen Grad parallelisieren, aber je nachdem, wie groß die Dateien sind, kann dies zu suboptimalen Dateigrößen in der Tabelle führen, bis BigQuery sich entscheidet, die Speicherung zu optimieren.
Preis und Quote
BigQuery erhebt keine Gebühren für das Laden von Daten. Das Ingesting findet auf einer Reihe von Workern statt, die nicht mit dem Cluster verbunden sind, der die Slots für die Abfragen bereitstellt. Daher werden deine Abfragen (sogar für dieselbe Tabelle, in die du Daten einspeist) nicht dadurch verlangsamt, dass Daten eingespeist werden.
Datenladungen sind atomar. Abfragen auf eine Tabelle spiegeln entweder das Vorhandensein aller Daten wider, die durch die bq load Operation geladen werden, oder keine davon. Du wirst keine Abfrageergebnisse für einen Teil der Daten erhalten.
Der Nachteil beim Laden von Daten mit einem "freien" Cluster ist, dass die Ladezeiten unvorhersehbar werden und durch bereits bestehende Aufträge zu Engpässen führen können. Zum jetzigen Zeitpunkt sind die Ladeaufträge auf 1.000 pro Tabelle und 100.000 pro Projekt pro Tag begrenzt. Bei CSV- und JSON-Dateien sind die Zellen und Zeilen auf 100 MB begrenzt, während die Blöcke in Avro auf 16 MB begrenzt sind. Die Größe der Dateien darf 5 TB nicht überschreiten. Wenn du einen größeren Datensatz hast, teile ihn auf mehrere Dateien auf, die jeweils kleiner als 5 TB sind. Ein einzelner Ladeauftrag kann jedoch maximal 15 TB an Daten übermitteln, die auf maximal 10 Millionen Dateien verteilt sind. Der Ladeauftrag muss in weniger als sechs Stunden ausgeführt werden, sonst wird er abgebrochen.
Föderierte Abfragen und externe Datenquellen
Du kannst BigQuery verwenden, ohne die Daten vorher zu laden. Es ist möglich, die Daten an Ort und Stelle zu belassen, die Struktur der Daten festzulegen und BigQuery nur als Abfragemaschine zu verwenden. Im Gegensatz zu den bisherigen Abfragen, bei denen BigQuery seine eigene native Speicherung abgefragt hat, besprechen wir in diesem Abschnitt die Verwendung von "federated queries" zur Abfrage "externer Datenquellen" und erklären, wann du solche Abfragen verwenden solltest.
Zu den derzeit unterstützten externen Datenquellen gehören Google Cloud Storage, Cloud Bigtable, Cloud SQL und Google Drive. Du wirst feststellen, dass all diese Quellen zwar außerhalb von BigQuery liegen, sich aber dennoch innerhalb des Google Cloud-Umfelds befinden. Das ist notwendig, weil der Netzwerk-Overhead und die Sicherheitsüberlegungen die Abfragen sonst entweder langsam oder undurchführbar machen würden.
Wie man Federated Queries verwendet
Es gibt drei Schritte zur Abfrage von Daten in einer externen Datenquelle:
-
Erstelle eine Tabellendefinition mit
bq mkdef. -
Erstelle eine Tabelle mit
bq mkund gib die externe Tabellendefinition ein. -
Frag die Tabelle wie gewohnt ab.
Wie bei der Abfrage von Daten in der nativen Speicherung kannst du dies entweder über die Web-UI oder über eine programmatische Schnittstelle tun. Um die Web-UI zu verwenden, befolgst du die eben aufgeführten Schritte zum Erstellen einer Tabelle. Achte aber darauf, dass du eine externe und keine native Tabelle anlegst, wie in Abbildung 4-1 gezeigt.
Abbildung 4-1. Du kannst eine externe Tabelle von der Web-UI aus erstellen, indem du dem Arbeitsablauf "Tabelle erstellen" folgst, aber "Externe Tabelle" als Tabellentyp angibst
Über die Befehlszeilenschnittstelle erstellt mit bq mkdef eine Tabellendefinition. Wie bei bq load hast du auch hier die Möglichkeit, --autodetect zu verwenden:
bq mkdef --source_format=CSV \ --autodetect \ gs://bigquery-oreilly-book/college_scorecard.csv
Dadurch wird eine Tabellendefinitionsdatei auf der Standardausgabe ausgegeben. Normalerweise wird diese Datei in eine Datei umgeleitet und diese Tabellendefinition verwendet, um eine Tabelle mit bq mk zu erstellen:
bq mkdef --source_format=CSV \ --autodetect \ gs://bigquery-oreilly-book/college_scorecard.csv \ > /tmp/mytable.json bq mk --external_table_definition=/tmp/mytable.json \ ch04.college_scorecard
Mit diesen beiden Schritten kannst du die Tabelle college_scorecard wie im vorherigen Abschnitt abfragen, mit dem Unterschied, dass die Abfragen auf der CSV-Datei erfolgen, die im Google Cloud Storage gespeichert ist - die Daten werden nicht in die native Speicherung von BigQuery aufgenommen.
Wildcards
Viele Big-Data-Frameworks wie, Apache Spark, Apache Beam und andere verteilen ihren Output auf Hunderte von Dateien mit Namen wie course_grades.csv-00095-of-00313. Wenn wir solche Dateien laden, wäre es praktisch, wenn wir nicht jede Datei einzeln auflisten müssten.
In der Tat ist es möglich, einen Platzhalter im Pfad zu bq mkdef (und bq load) zu verwenden, so dass du mehrere Dateien abgleichen kannst:
bq mkdef --source_format=CSV \ --autodetect \ gs://bigquery-oreilly-book/college_* \ > /tmp/mytable.json
Dadurch wird eine Tabelle erstellt, die auf alle Dateien verweist, auf die das Muster zutrifft.
Temporäre Tabelle
Es ist auch möglich, die drei Schritte (mkdef, mk und query) zu verkürzen, indem du die Definitionsparameter der Tabelle zusammen mit einer Abfrage übergibst und so sicherstellst, dass die Tabellendefinition nur für die Dauer der Abfrage verwendet wird:
LOC="--location US"
INPUT=gs://bigquery-oreilly-book/college_scorecard.csv
SCHEMA=$(gsutil cat $INPUT | head -1 | awk -F, '{ORS=","}{for (i=1; i <= NF; i++){
print $i":STRING"; }}' | sed 's/,$//g'| cut -b 4- )
bq $LOC query \
--external_table_definition=cstable::${SCHEMA}@CSV=${INPUT} \
'SELECT SUM(IF(SAT_AVG != "NULL", 1, 0))/COUNT(SAT_AVG) FROM cstable'
In der vorangegangenen Abfrage besteht die externe Tabellendefinition aus dem temporären Tabellennamen (cstable), zwei Doppelpunkten, dem Schema-String, dem @-Symbol, dem Format (CSV), einem Gleichheitszeichen und der Google Cloud Storage URL, die der/den Datendatei(en) entspricht. Wenn du bereits eine Tabellendefinitionsdatei hast, kannst du sie direkt angeben:
--external_table_definition=cstable::${DEF}
Es ist möglich, eine JSON-Schemadatei anzugeben sowie JSON, Avro und andere unterstützte Formate direkt von Cloud Storage, Cloud Bigtable und anderen unterstützten Datenquellen abzufragen.
Obwohl sie unbestreitbar praktisch sind, lassen föderierte Abfragen in Bezug auf die Leistung viel zu wünschen übrig. Da CSV-Dateien zeilenweise gespeichert werden und die Zeilen selbst in einer willkürlichen Reihenfolge abgelegt werden, geht ein Großteil der Effizienz, die wir normalerweise mit BigQuery verbinden, verloren. Außerdem kann BigQuery nicht abschätzen, wie viele Daten es vor dem Ausführen der Abfrage durchsuchen muss.
Laden und Abfragen von Parquet und ORC
Wie bereits erwähnt, sind Parquet und ORC spaltenbasierte Datenformate. Daher bietet die föderierte Abfrage dieser Formate eine bessere Abfrageleistung, als wenn die Daten in zeilenbasierten Formaten wie CSV oder JSON gespeichert werden (die Abfragen sind jedoch immer noch langsamer als die native Capacitor Speicherung von BigQuery).
Da Parquet und ORC selbstbeschreibend sind (d.h. das Schema ist implizit in den Dateien selbst enthalten), ist es möglich, Tabellendefinitionen zu erstellen, ohne ein Schema anzugeben:
bq mkdef --source_format=PARQUET gs://bucket/dir/files* > table_def.json bq mk --external_table_definition=table_def.json <dataset>.<table>
Wie bei der Abfrage von externen Tabellen, die aus CSV-Dateien erstellt wurden, funktioniert die Abfrage dieser Tabelle wie die Abfrage jeder anderen Tabelle in BigQuery.
Auch wenn Parquet- und ORC-Dateien eine bessere Abfrageleistung bieten als zeilenbasierte Dateiformate, unterliegen sie immer noch den Einschränkungen externer Tabellen.
Laden und Abfragen von Hive-Partitionen
Apache Hive ermöglicht das Lesen, Schreiben und Verwalten eines Apache Hadoop-basierten Data Warehouse mit einer vertrauten SQL-ähnlichen Abfragesprache. Cloud Dataproc in der Google Cloud ermöglicht es der Hive-Software, mit verteilten Daten zu arbeiten, die in Hive-Partitionen auf Google Cloud Storage gespeichert sind. Ein gängiges Migrationsmuster für die öffentliche Cloud ist, dass Hive-Workloads vor Ort zu Cloud Dataproc verschoben werden und neue Workloads mit der BigQuery-Federated Querying-Funktion geschrieben werden. Auf diese Weise arbeiten die aktuellen Hive-Workloads so wie sie sind, während neuere Workloads die Vorteile der serverlosen, groß angelegten Abfragefunktionen von BigQuery nutzen können.
Du kannst Hive-Partitionen auf Google Cloud Storage laden, indem du einen Hive Partitionierungsmodus auf bq load angibst:
bq load --source_format=ORC --autodetect \ --hive_partitioning_mode=AUTO <dataset>.<table> <gcs_uri>
Bei Hive-Tabellen muss die URI des Cloud-Speichers den Tabellenpfad-Präfix kodieren, ohne Partitionsschlüssel in den Platzhalter aufzunehmen. Wenn also der Partitionsschlüssel für eine Hive-Tabelle ein Feld mit dem Namen datestamp ist, sollte die URI des Cloud-Speichers die folgende Form haben:
gs://some-bucket/some-dir/some-table/*
Das gilt auch dann, wenn die Dateien selbst alle mit dem folgenden Namen beginnen:
gs://some-bucket/some-dir/some-table/datestamp=
Im Moment kann der AUTO Partitionierungsmodus die folgenden Typen erkennen: STRING, INTEGER, DATE und TIMESTAMP. Es ist auch möglich, anzufordern, dass die Partition Schlüssel als Strings erkannt werden (das kann bei der Erkundung hilfreich sein):
bq load --source_format=ORC --autodetect \ --hive_partitioning_mode=STRINGS <dataset>.<table> <gcs_uri>
Wie bei CSV-Dateien aus Google Cloud Storage muss auch für die föderierte Abfrage von Hive-Partitionen eine Tabellendefinitionsdatei erstellt werden, und die Optionen entsprechen weitgehend denen von Load:
bq mkdef --source_format=ORC --autodetect \
--hive_partitioning_mode=AUTO <gcs_uri> > table_def.json
Nachdem die Tabellendefinitionsdatei erstellt wurde, ist die Abfrage gleich, egal ob der zugrunde liegende externe Datensatz aus CSV-Dateien oder Hive-Partitionen besteht.
Neben ORC werden, wie bereits gezeigt, auch Daten in anderen Formaten unterstützt. So kannst du zum Beispiel mit eine Tabellendefinition von Daten erstellen, die in newline-delimited JSON gespeichert sind:
bq mkdef --source_format=NEWLINE_DELIMITED_JSON --autodetect -- hive_partitioning_mode=STRINGS <gcs_uri> <schema> > table_def.json
Beachte, dass im vorangegangenen Befehl die Partitionsschlüssel automatisch erkannt werden, nicht aber die Datentypen der Partitionsschlüssel, da wir explizit angeben, dass sie als Strings behandelt werden sollen und nicht die Datentypen der anderen Spalten, da wir ein explizites Schema übergeben.
Zu Beginn dieses Abschnitts haben wir gesagt, dass ein häufiger Anwendungsfall für die Abfrage von Hive-Partitionen darin besteht, Cloud-Migrationsbemühungen zu unterstützen, bei denen bereits erhebliche Hive-Workloads existieren, aber zukünftige Workloads mit BigQuery implementiert werden können. Während Apache Hive die vollständige Verwaltung (Lesen und Schreiben) der Daten ermöglicht, sind die externen Tabellen von BigQuery schreibgeschützt. Außerdem kann BigQuery zwar mit Daten umgehen, die geändert werden (z. B. von Hive), während eine föderierte Abfrage läuft, aber es unterstützt derzeit keine Konzepte wie das Lesen von Daten zu einem bestimmten Zeitpunkt. Da externe Tabellen in BigQuery diese Einschränkungen haben, ist es im Laufe der Zeit besser, die Daten in die native Speicherung von BigQuery zu verschieben und die Hive-Workloads in BigQuery umzuschreiben. Wenn sich die Daten in der nativen Speicherung von BigQuery befinden, werden Funktionen wie DML, Streaming, Clustering, Kopien der Tabelle und vieles mehr möglich.
Wann sollten Federated Queries und externe Datenquellen verwendet werden?
Die Abfrage externer Quellen ist langsamer als die Abfrage von Daten, die nativ in BigQuery enthalten sind. Daher sind föderierte Abfragen auf lange Sicht für Daten, auf die häufig zugegriffen wird, normalerweise nicht zu empfehlen. Es gibt jedoch Situationen, in denen föderierte Abfragen vorteilhaft sein können:
-
Durchführung von Sondierungsarbeiten mit föderierten Abfragen, um festzustellen, wie die Rohdaten am besten umgewandelt werden können, bevor sie in BigQuery geladen werden. Zum Beispiel könnten die Erkenntnisse aus der tatsächlichen Analysearbeit die Umwandlungen in den Produktionstabellen vorgeben. Du könntest auch ursprüngliche, externe Datenquellen als Staging behandeln und föderierte Abfragen verwenden, um die Daten umzuwandeln und sie in Produktionstabellen zu schreiben.
-
Behalte die Daten in Google Sheets, wenn die Tabelle interaktiv bearbeitet wird, und verwende ausschließlich föderierte Abfragen, wenn die Ergebnisse dieser Abfragen die Live-Daten in dieser Tabelle widerspiegeln müssen.
-
Daten in einer externen Datenquelle aufbewahren, wenn die Ad-hoc-SQL-Abfrage der Daten relativ selten ist. Du könntest die Daten zum Beispiel in Cloud Bigtable speichern, wenn die Daten hauptsächlich für Streaming-Ingest mit niedriger Latenz und hohem Volumen verwendet werden und die meisten Abfragen der Daten mit Schlüsselpräfixen durchgeführt werden können.
Für große, relativ stabile und gut verstandene Datensätze, die regelmäßig aktualisiert und häufig abgefragt werden, ist die native Speicherung von BigQuery die bessere Wahl. Im weiteren Verlauf dieses Abschnitts gehen wir auf die Implementierungsdetails für jede dieser Situationen ein und beginnen mit der explorativen Arbeit mit föderierten Abfragen.
Erkundungsarbeit mit föderierten Abfragen
Die automatische Erkennung ist eine praktische Funktion, bei der einige wenige Zeilen (in der Größenordnung von Hunderten) der Eingabedateien abgetastet werden, um den Typ einer Spalte zu bestimmen. Sie ist nicht narrensicher, es sei denn, du verwendest selbstbeschreibende Dateiformate wie Avro, Parquet oder ORC. Um sicherzustellen, dass deine ETL-Pipeline richtig funktioniert, solltest du den Wert jeder Zeile überprüfen, um sicherzustellen, dass der Datentyp für jede Spalte korrekt ist. Es ist zum Beispiel möglich, dass eine Spalte ganze Zahlen enthält, mit Ausnahme einer Handvoll Zeilen, die Fließkommazahlen enthalten. Wenn das der Fall ist, ist es ziemlich wahrscheinlich, dass die automatische Erkennung die Spalte als Ganzzahl erkennt, weil die Wahrscheinlichkeit, dass eine der Zeilen mit dem Fließkommawert ausgewählt wird, eher gering ist. Du wirst das Problem erst bemerken, wenn du eine Abfrage durchführst, die die Werte dieser Spalte in der Tabelle überprüft.
Die bewährte Methode ist, selbstbeschreibende Dateiformate zu verwenden. In diesem Fall musst du dir keine Gedanken darüber machen, wie BigQuery die Daten interpretiert. Wenn du CSV oder JSON verwenden musst, empfehlen wir dir, explizit ein Schema anzugeben. Es ist zwar möglich, das Schema in einer begleitenden JSON-Datei anzugeben, aber es ist auch möglich, das Schema in der Befehlszeile von bq mkdef zu übergeben, indem du einen String mit diesem Format erstellst:
FIELD1:DATATYPE1,FIELD2:DATATYPE2,...
Wenn du dir über die Qualität deiner Daten unsicher bist, solltest du alles als STRING angeben. Beachte, dass dies der Standarddatentyp ist, so dass der Formatierungsbefehl einfach so aussieht:
FIELD1,FIELD2,FIELD3,,...
Warum alles als String behandeln? Selbst wenn du glaubst, dass einige der Felder Ganzzahlen und andere Fließkommazahlen sind, ist es am besten, diese Annahme zu überprüfen. Definiere alles als String und erfahre, welche Transformationen du durchführen musst, wenn du die Daten abfragst und Fehler entdeckst.
Wir können die Spaltennamen extrahieren, indem wir die erste Zeile der CSV-Datei verwenden, um einen Schema-String im gewünschten Format zu erstellen:15
INPUT=gs://bigquery-oreilly-book/college_scorecard.csv SCHEMA=$(gsutil cat $INPUT | head -1 | cut -b 4- )
Wenn wir das Schema angeben, sollten wir verlangen, dass die erste Zeile übersprungen wird und dass das Programm leere Zeilen in der Datei zulässt. Das können wir erreichen, indem wir die Tabellendefinition über sed in einen Zeileneditor einfügen:16
LOC="--location US" OUTPUT=/tmp/college_scorecard_def.json bq $LOC \ mkdef \ --source_format=CSV \ --noautodetect \ $INPUT \ $SCHEMA \ | sed 's/"skipLeadingRows": 0/"skipLeadingRows": 1/g' \ | sed 's/"allowJaggedRows": false/"allowJaggedRows": true/g' \ > $OUTPUT
Wir legen fest, dass wir in den USA arbeiten und dass wir die Ausgabe (die Tabellendefinition) im Ordner /tmp speichern wollen.
Jetzt haben wir eine Tabelle, die wir abfragen können. Beachte zwei Dinge: Diese Tabelle ist in einer externen Datenquelle definiert, so dass wir mit der Abfrage der Daten beginnen können, ohne darauf warten zu müssen, dass die Daten eingelesen werden; und alle Spalten sind Strings - wir haben keine irreversiblen Änderungen an den Rohdaten vorgenommen.
Beginnen wir unsere Datenexploration, indem wir versuchen, einen Wurf zu machen:
SELECT MAX(CAST(SAT_AVG AS FLOAT64)) AS MAX_SAT_AVG FROM `ch04.college_scorecard_gcs`
Die Abfrage schlägt mit der folgenden Fehlermeldung fehl:
Bad double value: NULL
Das bedeutet, dass wir mit der nicht standardmäßigen Art und Weise umgehen müssen, wie fehlende Daten in der Datei kodiert werden. In den meisten CSV-Dateien werden fehlende Daten als leere Zeichenfolge kodiert, aber in dieser Datei werden sie als die Zeichenfolge NULL kodiert.
Wir können dieses Problem beheben, indem wir vor dem Wurf prüfen:
WITH etl_data AS ( SELECT SAFE_CAST(SAT_AVG AS FLOAT64) AS SAT_AVG FROM `ch04.college_scorecard_gcs` ) SELECT MAX(SAT_AVG) AS MAX_SAT_AVG FROM etl_data
Beachte, dass wir eine WITH Klausel gestartet haben, die alle ETL-Operationen enthält, die mit dem Datensatz durchgeführt werden müssen. Während wir den Datensatz untersuchen und mit der Abfrage aus dem vorherigen Abschnitt abschließen, erfahren wir, dass wir eine wiederverwendbare Funktion benötigen, um numerische Daten zu bereinigen:
CREATE TEMP FUNCTION cleanup_numeric(x STRING) AS
(
IF ( x != 'NULL' AND x != 'PrivacySuppressed',
CAST(x as FLOAT64),
NULL )
);
WITH etl_data AS (
SELECT
INSTNM
, cleanup_numeric(ADM_RATE_ALL) AS ADM_RATE_ALL
, cleanup_numeric(FIRST_GEN) AS FIRST_GEN
, cleanup_numeric(MD_FAMINC) AS MD_FAMINC
, cleanup_numeric(SAT_AVG) AS SAT_AVG
, cleanup_numeric(MD_EARN_WNE_P10) AS MD_EARN_WNE_P10
FROM
`ch04.college_scorecard_gcs`
)
SELECT
*
FROM
etl_data
WHERE
SAT_AVG > 1300
AND ADM_RATE_ALL < 0.2
AND FIRST_GEN > 0.1
ORDER BY
MD_FAMINC ASC
LIMIT 10
Jetzt können wir die bereinigten Daten (beachte SELECT *) in eine neue Tabelle (beachte CREATE TABLE) exportieren, die nur die Spalten enthält, die uns interessieren, indem wir die folgende Abfrage ausführen:
CREATE TEMP FUNCTION cleanup_numeric(x STRING) AS
(
IF ( x != 'NULL' AND x != 'PrivacySuppressed',
CAST(x as FLOAT64),
NULL )
);
CREATE TABLE ch04.college_scorecard_etl
OPTIONS(description="Cleaned up college scorecard data") AS
WITH etl_data AS (
SELECT
INSTNM
, cleanup_numeric(ADM_RATE_ALL) AS ADM_RATE_ALL
, cleanup_numeric(FIRST_GEN) AS FIRST_GEN
, cleanup_numeric(MD_FAMINC) AS MD_FAMINC
, cleanup_numeric(SAT_AVG) AS SAT_AVG
, cleanup_numeric(MD_EARN_WNE_P10) AS MD_EARN_WNE_P10
FROM
`ch04.college_scorecard_gcs`
)
SELECT * FROM etl_data
Es ist auch möglich, dies zu skripten, indem du die Anweisung CREATE TABLE aus der vorhergehenden Abfrage entfernst, bq query aufrufst und eine --destination_table einfügst.
ELT in SQL zum Experimentieren
In vielen Unternehmen gibt es viel mehr Datenanalysten als Ingenieure. Daher übersteigt der Bedarf der Datenanalyse-Teams in der Regel bei weitem das, was die Dateningenieure leisten können. In solchen Fällen kann es hilfreich sein, wenn die Datenanalysten selbst einen experimentellen Datensatz in BigQuery erstellen und mit den Analyseaufgaben beginnen können.
Das Unternehmen kann dann anhand des tatsächlichen Analyseaufwands Prioritäten setzen, auf die sich die Dateningenieure konzentrieren. Als Dateningenieur weißt du zum Beispiel vielleicht noch nicht, welche Felder du aus einer Logdatei extrahieren musst. Du könntest also versuchsweise eine externe Datenquelle einrichten und den Datenanalysten erlauben, die Rohdaten auf Google Cloud Storage direkt abzufragen.
Wenn die rohen Logdateien im JSON-Format vorliegen und jede Zeile eine andere Struktur hat, weil die Logs von verschiedenen Anwendungen stammen, könnten die Analysten die gesamte Logmeldung als eine einzige BigQuery-Stringspalte definieren und JSON_EXTRACT und Funktionen zur Stringmanipulation verwenden, um die erforderlichen Daten herauszuziehen. Am Ende eines Monats könntest du die BigQuery-Abfrageprotokolle daraufhin analysieren, auf welche Felder tatsächlich zugegriffen wurde und wie der Zugriff erfolgte, und dann eine Pipeline erstellen, um diese Felder routinemäßig in BigQuery zu laden.
Du kannst zum Beispiel BigQuery-Audit-Logs von Stackdriver im JSON-Format exportieren, wobei die gesamte Logmeldung in einer verschachtelten Spalte mit dem Namen protopayload_auditlog.metadataJson enthalten ist. Hier ist eine Abfrage, um die Logmeldungen mit dem Root-Element tableDataRead zu zählen und die Anzahl zu verwenden, um die Datensätze nach der Anzahl der Zugriffe auf die einzelnen Datensätze zu ordnen:
SELECT
REGEXP_EXTRACT(protopayload_auditlog.resourceName,
'^projects/[^/]+/datasets/([^/]+)/tables') AS datasetRef,
COUNTIF(JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableDataRead")
IS NOT NULL) AS dataReadEvents,
FROM `ch04.cloudaudit_googleapis_com_data_access_2019*`
WHERE
JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableDataRead")
IS NOT NULL
GROUP BY datasetRef
ORDER BY dataReadEvents DESC
LIMIT 5
Die Methode JSON_EXTRACT nimmt den Spaltennamen (protopayload_auditlog.metadataJson) als ersten Parameter und einen JSONPath17 als zweiten Parameter.
Wenn sich die Originaldaten in einem relationalen Datenbankmanagementsystem (RDBMS) befinden, ist es möglich, die Daten regelmäßig als tabulatorgetrennte Wertedatei (TSV) in Google Cloud Storage zu exportieren. Wenn du zum Beispiel MySQL mit einer Datenbank namens somedb verwendest, lautet der entsprechende Befehl wie folgt:
mysql somedb < select_data.sql | \
gsutil cp - gs://BUCKET/data_$(date -u "+%F-%T").tsv
Die select_data.sql würde eine Abfrage enthalten, die nur die aktuellsten Datensätze abfragt (hier die der letzten 10 Tage):
select * from my_table where transaction_date >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)
Mit diesen regelmäßig exportierten Dateien ist es für einen Analysten ein Leichtes, die Daten mit föderierten Abfragen abzufragen. Nachdem der Wert des Datensatzes bewiesen ist, können die Daten routinemäßig und/oder in Echtzeit über eine Datenpipeline geladen werden.
Der Grund, warum dies nicht immer für die Operationalisierung geeignet ist, liegt darin, dass es den Fall von Mutationen in der Datenbank nicht behandelt. Wenn Daten, die mehr als 10 Tage alt sind, aktualisiert werden, werden die tabulatorgetrennten Dumps nicht synchronisiert. Realistischerweise funktionieren Dumps in TSV-Dateien nur für kleine Datensätze (in der Größenordnung von einigen Gigabyte), bei denen die ursprünglichen Datenbankfelder selbst nicht umgewandelt oder korrigiert werden müssen, bevor sie für Analyseabfragen verwendet werden.
Wenn du die Synchronisierung von einer operativen Datenbank mit BigQuery durchführen möchtest, gibt es eine Reihe von Drittanbietern, die mit Google zusammenarbeiten und jeweils eine Reihe von Konnektoren und Transformationsoptionen anbieten.18 Diese Tools können Change Data Capture (CDC) durchführen, damit du Änderungen aus einer Datenbank in eine BigQuery-Tabelle übertragen kannst.
Externe Abfrage in Cloud SQL
BigQuery unterstützt externe Abfragen, nicht nur föderierte Abfragen. Während du mit einer federated query eine externe Datenquelle mit BigQuery abfragen kannst, kannst du mit einer externen Abfrage die Abfrage in der externen Datenbank ausführen und die Ergebnisse nahtlos mit den Daten in BigQuery verknüpfen. Zum Zeitpunkt der Erstellung dieses Artikels werden MySQL- und PostgresSQL-Datenbanken in Cloud SQL (dem verwalteten relationalen Datenbankdienst in Google Cloud) unterstützt.
Es ist eine einmalige Einrichtung erforderlich, um eine Verbindungsressource in BigQuery zu erstellen und den Nutzern die Berechtigung zur Nutzung dieser Verbindungsressource zu erteilen. Sobald diese Verbindungsressource eingerichtet ist, kann sie von einer EXTERNAL_QUERY wie folgt verwendet werden:
SELECT * FROM EXTERNAL_QUERY(connection_id, cloud_sql_query);
In diesem Beispiel ist connection_id der Name der Datenbankverbindungsressource, die du in BigQuery über die Web-UI, eine REST-API oder das Kommandozeilentool erstellt hast.
Die Leistung der externen Abfrage hängt von der Geschwindigkeit der externen Datenbank ab und ist in der Regel langsamer als Abfragen, die nur in Cloud SQL oder BigQuery durchgeführt werden, da eine temporäre Zwischentabelle verwendet wird. Dennoch ist es von großem Vorteil, Daten in einem RDBMS in Echtzeit abfragen zu können, ohne sie verschieben zu müssen, und so unnötige ETL-, Zeitplanungs- und Orchestrierungsarbeiten zu vermeiden.
Nehmen wir zum Beispiel an, wir möchten einen Bericht über Geschenkkarten erstellen, die Kunden gehören, die in letzter Zeit keine Einkäufe getätigt haben. Das Datum der letzten Bestellung für jeden Kunden ist in Cloud SQL verfügbar und wird in Echtzeit aktualisiert. Der Kontostand für jede Geschenkkarte, die unser Laden jemals ausgegeben hat, ist jedoch in BigQuery verfügbar. Wir können das Ergebnis einer externen Abfrage der Bestelldaten in Cloud SQL mit den Geschenkkarten-Saldo-Daten in BigQuery verknüpfen, um einen aktuellen Bericht zu erstellen, ohne Daten verschieben zu müssen:
SELECT
c.customer_id
, c.gift_card_balance
, rq.latest_order_date
FROM ch04.gift_cards AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
'connection_id',
'''SELECT customer_id, MAX(order_date) AS latest_order_date
FROM orders
GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
WHERE c.gift_card_balance > 100
ORDER BY rq.latest_order_date ASC;
Interaktive Erkundung und Abfrage von Daten in Google Sheets
Google Sheets ist Teil der G Suite, einer Reihe von Produktivität und Tools für die Zusammenarbeit aus der Google Cloud. Es ermöglicht das Erstellen, Anzeigen, Bearbeiten und Veröffentlichen von Tabellenkalkulationen. Eine Tabellenkalkulation enthält tabellarische Werte in einzelnen Zellen; einige dieser Werte sind Daten und andere sind das Ergebnis von Berechnungen, die mit den Werten anderer Zellen durchgeführt wurden. Google Sheets bringt Tabellenkalkulationen online - mehrere Personen können eine Tabelle gemeinsam bearbeiten und du kannst von verschiedenen Geräten aus darauf zugreifen.
Laden von Google Sheets-Daten in BigQuery
Da Google Sheets eine externe Quelle ist, ist das Laden und Abfragen einer Google Sheets-Tabelle eine federated query; sie funktioniert ähnlich wie die Abfrage einer CSV-Datei aus Google Cloud Storage. Wir erstellen eine Tabellendefinition in BigQuery, die auf die Daten in Google Sheets verweist, und können diese Tabelle dann abfragen, als wäre sie eine native BigQuery-Tabelle.
Beginnen wir damit, eine Google Sheets-Tabelle zu erstellen, die wir abfragen können. Öffne einen Webbrowser und gib dann in der URL-Navigationsleiste ein https://sheets.new --. Wenn du diese URL aufrufst, wird ein leeres Arbeitsblatt geöffnet.
Gib die folgenden Daten ein (oder lade die entsprechende CSV-Datei von GitHub herunter und führe einen Datei > Import der Daten in Google Sheets aus):
| Student | Heimatland | SAT-Punktzahl |
|---|---|---|
| Aarti | KS | 1111 |
| Billy | LA | 1222 |
| Cao | MT | 1333 |
| Dalia | NE | 1444 |
Als Nächstes navigierst du zum BigQuery-Bereich der GCP Cloud Console, erstellst ein Dataset (falls nötig) und erstellst eine Tabelle, wobei du angibst, dass die Quelle der Tabelle auf Drive und ihre URL ist und dass es sich um ein Google Sheet handelt. Bitte darum, dass das Schema automatisch erkannt wird, wie in Abbildung 4-2 gezeigt.
Abbildung 4-2. Im Dialogfeld "Tabelle erstellen" kannst du angeben, dass die externe Datenquelle Google Sheets ist
Danach kannst du das Arbeitsblatt wie jede andere BigQuery-Tabelle abfragen:
SELECT * from advdata.students
Versuche, die Tabelle zu ändern, und überprüfe, ob die zurückgegebenen Ergebnisse den aktuellen Stand der Tabelle widerspiegeln (die Ergebnisse von Verbundabfragen auf externen Datensätzen werden nicht zwischengespeichert).
Obwohl es möglich ist, eine Tabellenkalkulation mit SQL abzufragen, ist es unwahrscheinlich, dass du dies tun möchtest, da es normalerweise bequemer ist, die in Google Sheets integrierten interaktiven Filter- und Sortieroptionen zu verwenden. Du kannst zum Beispiel auf die Schaltfläche "Erkunden" klicken und die natürlichsprachliche Abfrage "average SAT score of students in KS" eingeben, die die in Abbildung 4-3 gezeigten Ergebnisse liefert.
Abbildung 4-3. Natürlichsprachliche Abfrage in Google Sheets
Es gibt mehrere Anwendungsfälle für die Verbindung zwischen Google Sheets und BigQuery:
-
Tabellenkalkulation mit Daten aus BigQuery füllen
-
BigQuery-Tabellen mit Sheets erkunden
-
Sheets-Daten mit SQL abfragen
Schauen wir uns diese drei Fälle an.
Befüllen einer Google Sheets-Tabelle mit Daten aus BigQuery
Der BigQuery-Datenkonnektor in Google Sheets ermöglicht es dir, BigQuery-Tabellen abzufragen19 abzufragen und die Ergebnisse zum Auffüllen eines Tabellenblatts zu verwenden. Das kann sehr nützlich sein, wenn du Daten mit technisch nicht versierten Nutzern teilen möchtest. In den meisten Unternehmen wissen fast alle Büroangestellten, wie man Tabellenkalkulationen liest und interpretiert. Sie müssen nichts mit BigQuery oder SQL zu tun haben, um Google Sheets zu nutzen und mit den Daten in der Tabelle zu arbeiten.
Klicke in Google Sheets auf Daten > Datenkonnektoren > BigQuery, wähle dein Projekt aus und schreibe eine Abfrage, um die Tabelle aus der BigQuery-Tabelle mit den College-Scorecard-Daten zu füllen:
SELECT * FROM ch04.college_scorecard_etl
BigQuery-Tabellen mit Sheets erkunden
Einer der Gründe, warum du eine Google Sheets-Tabelle mit Daten aus einer BigQuery-Tabelle füllen möchtest, ist, dass Sheets eine vertraute Oberfläche für Geschäftsanwender ist, die Diagramme, Formeln und Pivot-Tabellen erstellen. Aus den Daten der College Scorecard in Sheets lässt sich zum Beispiel ganz einfach eine Formel erstellen, die eine Rangfolge der Colleges nach dem Anstieg des Medianeinkommens ihrer Absolventen erstellt:
-
Gib in eine neue Spalte die folgende Formel ein:
=ArrayFormula(IF(ISBLANK(D2:D), 0, F2:F/D2:D))
Beachte, dass die Tabelle nun mit dem Verhältnis zwischen dem Wert in der F-Spalte und dem Wert in der D-Spalte aufgefüllt wurde - also mit dem Anstieg des Einkommens.
-
Erstelle im Menü Daten einen Filter für die neu erstellte Spalte und schalte Leerzeichen und Nullen aus.
-
Sortiere das Arbeitsblatt anhand dieser Spalte von Z nach A.
Wenn du die ersten Zeilen des Blattes auswählst, kannst du schnell ein Diagramm erstellen, das die besten Colleges in Bezug auf die wirtschaftliche Verbesserung der Schülerschaft zeigt (siehe Abbildung 4-4).
Abbildung 4-4. Grafik, die zeigt, welche Hochschulen ihren Absolventen die größte wirtschaftliche Verbesserung bieten
Du kannst nicht nur interaktiv die gewünschten Diagramme erstellen, sondern auch die maschinellen Lernfunktionen von Google Sheets nutzen, um deine Daten weiter zu untersuchen.
In Google Sheets klickst du auf die Schaltfläche "Erkunden" und siehst die Diagramme, die automatisch durch maschinelles Lernen erstellt werden.20 Die automatisch erstellte Übersicht in Abbildung 4-5 zeigt zum Beispiel eine auffällige Ungleichheit.
Abbildung 4-5. Google Sheets generiert automatisch die Erkenntnis, dass Colleges, die Studenten der ersten Generation aufnehmen, auch eine ärmere Studentenschaft haben; für jede 10%ige Zunahme der Studenten der ersten Generation sinkt das mediane Familieneinkommen um $11.400
Abbildung 4-6 zeigt ein automatisch erstelltes Diagramm, das die SAT_AVG in einen Kontext stellt.
Abbildung 4-6. Colleges, an denen Studierende der ersten Generation studieren, haben tendenziell niedrigere SAT-Durchschnittswerte
Wir können sogar mit natürlicher Sprache nach bestimmten Diagrammen fragen. Wenn du "histogram of sat_avg where first_gen more than 0.5" in das Feld "Ask a question" eingibst, erhältst du die in Abbildung 4-7 dargestellte Antwort.
Abbildung 4-7. Wir erhalten die gewünschten Diagramme, indem wir sie in Google Sheets einfach abfragen
BigQuery-Tabellen als Datenblätter in Google Sheets erkunden
Im vorigen Abschnitt haben wir die gesamte BigQuery-Tabelle in Google Sheets geladen, aber das war nur möglich, weil unser College-Scorecard-Datensatz klein genug war. Das Laden der gesamten BigQuery-Tabelle in Google Sheets ist bei größeren BigQuery-Tabellen natürlich nicht möglich.
Mit Google Sheets kannst du sogar große BigQuery-Datensätze als BigQuery-Datenblatt aufrufen, analysieren, visualisieren und teilen. Um dies auszuprobieren, beginne ein neues Google Sheets-Dokument und navigiere über das Menü, indem du auf Daten > Datenkonnektoren > BigQuery-Datenblatt klickst.
Wähle dein Cloud-Projekt (das abgerechnet werden sollte) und navigiere über das Menü zu der Tabelle, die du in das Datenblatt laden möchtest, indem du auf bigquery-public-data > usa_names > usa_1910_current > Connect klickst. Diese Tabelle enthält fast sechs Millionen Zeilen und ist zu groß, um sie in ihrer Gesamtheit zu laden. Stattdessen fungiert BigQuery als Cloud-Backend für die in Sheets angezeigten Daten.
Anders als beim Laden der gesamten Tabelle in Blätter (wie im vorherigen Abschnitt) werden nur die ersten 500 Zeilen eines Datenblatts in die Benutzeroberfläche geladen. Diese 500 Zeilen kann man sich am besten als Vorschau des gesamten Datensatzes vorstellen. Ein weiterer Unterschied liegt in der Bearbeitung: Wenn die gesamte Tabelle geladen wird, enthält Google Sheets eine Kopie der Daten; du kannst also Zellen bearbeiten und das geänderte Arbeitsblatt speichern. Wenn BigQuery dagegen als Cloud-Backend fungiert, können die Zellen nicht bearbeitet werden - die Nutzer/innen können das BigQuery-Datenblatt filtern und schwenken, aber sie können die Daten nicht bearbeiten. Wenn Nutzer/innen filtern und schwenken, werden diese Aktionen in der gesamten BigQuery-Tabelle durchgeführt, nicht nur in der Vorschau, die in Sheets angezeigt wird.
Als Beispiel für die Art von Analyse, die möglich ist, lass uns eine Pivot-Tabelle erstellen, indem wir auf die Schaltfläche Pivot-Tabelle klicken. Im Pivot-Tabellen-Editor wählst du state als Rows und year als Columns. Für Values wählst du number und bittest Sheets, nach COUNTUNIQUE zusammenzufassen und als Default anzuzeigen, wie in Abbildung 4-8 gezeigt.
Abbildung 4-8. Erstellen einer Pivot-Tabelle aus einem BigQuery-Datenblatt
Wie Abbildung 4-8 zeigt, erhalten wir eine Tabelle mit der Anzahl der einzigartigen Babynamen in jedem Bundesland, aufgeschlüsselt nach Jahren.
Sheets-Daten mit einem großen Datensatz in BigQuery verknüpfen
Sowohl BigQuery als auch Google Sheets sind in der Lage, tabellarische Daten zu speichern und zugänglich zu machen. Allerdings ist BigQuery in erster Linie ein analytisches Data Warehouse, während Google Sheets in erster Linie ein interaktives Dokument ist. Wie wir in den vorangegangenen Abschnitten gesehen haben, ist das Laden von BigQuery-Daten in Sheets aufgrund der Vertrautheit mit Sheets und der Explorations- und Diagrammfunktionen sehr leistungsstark.
Allerdings gibt es eine praktische Beschränkung für die Größe der BigQuery-Datensätze, die du in Sheets laden kannst. BigQuery enthält zum Beispiel Informationen über Fragen, Antworten und Nutzer von Stack Overflow. Selbst mit BigSheets sind diese Petabyte großen Datensätze viel zu groß, um sie direkt in Google Sheets zu laden. Dennoch ist es möglich, Abfragen zu schreiben, die einen kleinen Datensatz in Sheets mit solch großen Datensätzen in BigQuery verbinden und von dort aus weiterarbeiten. Schauen wir uns ein Beispiel an.
Aus dem vorherigen Abschnitt haben wir eine Tabelle mit College-Scorecard-Daten. Gehen wir davon aus, dass wir die Daten noch nicht in BigQuery haben. Wir könnten eine Tabelle in BigQuery erstellen, indem wir das Arbeitsblatt als Quelle verwenden und die resultierende Tabelle college_scorecard_gs nennen, wie in Abbildung 4-9 dargestellt.
Abbildung 4-9. Erstellen einer Tabelle in BigQuery mit einer Google Sheets-Tabelle als Quelle
Jetzt können wir eine Abfrage in BigQuery erstellen, die diese relativ kleine Tabelle (7.700 Zeilen) mit einer riesigen Tabelle aus Stack Overflow-Daten (10 Millionen Zeilen) verbindet, um herauszufinden, welche Hochschulen am häufigsten in den Profilen von Stack Overflow-Nutzern aufgeführt sind:
SELECT INSTNM, COUNT(display_name) AS numusers FROM `bigquery-public-data`.stackoverflow.users, ch04.college_scorecard_gs WHERE REGEXP_CONTAINS(about_me, INSTNM) GROUP BY INSTNM ORDER BY numusers DESC LIMIT 5
Daraus ergibt sich das Folgende:21
| Reihe | INSTNM | numusers |
|---|---|---|
| 1 | Institut für Technologie | 2364 |
| 2 | Nationale Universität | 332 |
| 3 | Carnegie Mellon Universität | 169 |
| 4 | Stanford Universität | 139 |
| 5 | Universität von Maryland | 131 |
Die ersten beiden Einträge sind verdächtig,22 aber es scheint, dass Carnegie Mellon und Stanford auf Stack Overflow gut vertreten sind.
Das Ergebnis dieser Abfrage ist wiederum klein genug, um es direkt in Google Sheets zu laden und eine interaktive Filterung und Diagrammerstellung durchzuführen. Daher ist die SQL-Abfrage von Sheets-Daten aus BigQuery besonders nützlich, um einen kleinen, von Menschen bearbeitbaren Datensatz (in Google Sheets) mit großen Unternehmensdaten (in BigQuery) zu verbinden.
SQL-Abfragen auf Daten in Cloud Bigtable
Cloud Bigtable ist ein vollständig verwalteter NoSQL-Datenbankdienst, der bis zu Petabytes an Daten skaliert. Cloud Bigtable ist für Situationen gedacht, in denen eine Kombination aus niedriger Latenz (in der Größenordnung von Millisekunden), hohem Durchsatz (Millionen von Operationen pro Sekunde), Replikation für hohe Verfügbarkeit und nahtloser Skalierbarkeit (von Gigabyte bis Petabyte) gewünscht ist. Cloud Bigtable wird daher häufig im Finanzwesen (Handelsabgleich und -analyse, Erkennung von Zahlungsbetrug usw.), bei Internet of Things (IoT)-Anwendungen (für die zentrale Speicherung und Verarbeitung von Echtzeit-Sensordaten) und in der Werbung (Echtzeitgebote, Platzierung und Verhaltensanalyse) eingesetzt. Obwohl Cloud Bigtable selbst nur auf GCP verfügbar ist, unterstützt es die Open-Source-API von Apache HBase und ermöglicht so eine einfache Migration von Arbeitslasten in einer Hybrid-Cloud-Umgebung.
NoSQL-Abfragen basierend auf einem Zeilenschlüssel-Präfix
Cloud Bigtable bietet hochleistungsfähige Abfragen, die nach Zeilen oder Zeilengruppen suchen, die einem bestimmten Zeilenschlüssel, einem Zeilenschlüssel-Präfix oder einem Bereich von Präfixen entsprechen. Obwohl Cloud Bigtable eine Instanz, bestehend aus einem oder mehreren logischen Clustern, benötigt, die in deinem Projekt bereitgestellt und verfügbar sein muss, wird dieser Cluster nur für die Berechnung (und nicht für die Speicherung) verwendet - die Daten selbst werden auf Colossus gespeichert und die Knoten selbst müssen nur wissen, wo sich die Zeilenbereiche auf Colossus befinden. Da die Daten nicht auf den Cloud Bigtable-Knoten gespeichert werden, ist es möglich, den Cloud Bigtable-Cluster ohne teure Datenmigration zu vergrößern oder zu verkleinern.
In der Finanzanalyse ist es üblich, Zeitreihendaten in Cloud Bigtable zu speichern, sobald sie in Echtzeit eintreffen, und Abfragen mit niedriger Latenzzeit auf der Grundlage des Zeilenschlüssels zu unterstützen (z. B. alle Kaufaufträge für die GOOG-Aktie in den letzten 10 Minuten, falls vorhanden). So können Dashboards, die aktuelle Daten benötigen, automatische Warnungen und Aktionen auf der Grundlage der jüngsten Aktivitäten bereitstellen. Cloud Bigtable ermöglicht es auch, schnell eine Reihe von Daten abzurufen (z. B. alle Kaufaufträge für GOOG-Aktien an einem bestimmten Tag), was für Finanzanalysen und Berichte unerlässlich ist. Die Vorhersagealgorithmen selbst müssen auf historischen Daten trainiert werden (z. B. die Zeitreihe der Briefkurse für GOOG in den letzten fünf Jahren), und das ist möglich, weil Machine-Learning-Frameworks wie TensorFlow direkt von und zu Cloud Bigtable lesen und schreiben können. Diese drei Workloads (Echtzeit-Alarmierung, Reporting und maschinelles Lernen) können auf denselben Daten ausgeführt werden, wobei der Cluster aufgrund der Trennung von Rechenleistung und Speicherung bei Lastspitzen auf- und abwärts skaliert werden kann.
Bei allen drei Workloads im vorherigen Absatz geht es darum, die Briefkurse für Google-Aktien zu ermitteln. Cloud Bigtable ermöglicht einen effizienten Abruf von Datensätzen, wenn der Zeilenschlüssel, mit dem die Zeitreihendaten gespeichert werden, die Form GOOG#buy#20190119-090356.0322234hat, d. h. den Namen des Wertpapiers und den Zeitstempel. Dann werden bei der Abfrage der Briefkurse, egal ob für die letzten 10 Minuten oder die letzten fünf Jahre, nur Datensätze abgefragt, die in einen Bereich von Präfixen fallen.
Was aber, wenn wir Ad-hoc-Analysen über alle Cloud Bigtable-Daten durchführen wollen und unsere Abfrage nicht so gestaltet ist, dass sie nur eine Teilmenge der Datensätze abruft - mit anderen Worten, wenn unsere Abfrage nicht nach dem Zeilenschlüsselpräfix filtert? Dann fällt das NoSQL-Paradigma von Cloud Bigtable in sich zusammen und es ist besser, stattdessen auf die Ad-hoc-SQL-Abfragefunktionen von BigQuery zurückzugreifen, auch wenn die Ergebnisse von BigQuery eine höhere Latenz aufweisen.
Ad-hoc-SQL-Abfragen auf Cloud Bigtable-Daten
Genauso wie BigQuery Dateien in bestimmten Formaten (CSV, Avro usw.) in Google Cloud Storage direkt abfragen kann, indem es diese als externe Datenquelle behandelt, kann BigQuery Daten in Cloud Bigtable direkt abfragen. Genau wie bei den Daten in der Cloud Speicherung können die Daten in Cloud Bigtable entweder über eine permanente Tabelle oder eine temporäre Tabelle abgefragt werden. Eine permanente Tabelle kann geteilt werden, indem der Datensatz, zu dem sie gehört, geteilt wird; eine temporäre Tabelle ist nur für die Dauer einer Abfrage gültig und kann daher nicht geteilt werden.
Eine Tabelle in Cloud Bigtable wird auf eine Tabelle in BigQuery abgebildet. In diesem Abschnitt verwenden wir zur Veranschaulichung eine Zeitreihe mit Kassendaten. Führe dazu das Skript setup_data.sh im GitHub-Repository für dieses Buch aus, um eine Cloud Bigtable-Instanz mit Beispieldaten zu erstellen. Da das Setup-Skript eine Cloud Bigtable-Instanz mit einem Cluster erstellt, erinnere dich daran, die Instanz anschließend zu löschen.
Wir beginnen mit der BigQuery-Benutzeroberfläche, um eine externe Tabelle in BigQuery zu erstellen, die auf die Daten in Cloud Bigtable verweist, wie in Abbildung 4-10 gezeigt. Der Speicherort ist ein String der Form https://googleapis.com/bigtable/projects/[PROJECT_ID]/instances/[INSTANCE_ID]/tables/[TABLE_NAME]. Die Zeichen PROJECT_ID, INSTANCE_ID und TABLE_NAME verweisen auf das Projekt, die Instanz und die Tabelle in Cloud Bigtable.23
Abbildung 4-10. Erstellen einer externen Tabelle in BigQuery, die auf Daten in Cloud Bigtable verweist.24
Die Daten in Cloud Bigtable bestehen aus Datensätzen, von denen jeder einen Zeilenschlüssel und mit dem Zeilenschlüssel verknüpfte Daten hat, die in Spaltenfamilien organisiert sind, d.h. Schlüssel/Wert-Paare, bei denen der Schlüssel der Name der Spaltenfamilie und der Wert eine Reihe von zugehörigen Spalten ist.
Cloud Bigtable verlangt nicht, dass jeder Datensatz jede Spaltenfamilie und jede in einer Spaltenfamilie erlaubte Spalte hat; tatsächlich kann das Vorhandensein oder Fehlen einer bestimmten Spalte selbst als Daten betrachtet werden. Deshalb kannst du mit BigQuery eine Tabelle erstellen, die mit Daten in Cloud Bigtable verknüpft ist, ohne dass du explizit Spaltennamen angibst. Wenn du das tust, stellt BigQuery die Werte in einer Spaltenfamilie als Array von Spalten dar und jede Spalte als Array von Werten, die zu verschiedenen Zeitpunkten geschrieben wurden.
In vielen Fällen sind die Spaltennamen bereits bekannt. Wenn das der Fall ist, ist es besser, die bekannten Spalten in der Tabellendefinition anzugeben. In unserem Fall kennen wir das Schema eines jeden Datensatzes in der logs-table von Cloud Bigtable:
-
Ein Zeilenschlüssel, d.h. die Markt-ID, gefolgt vom Zeitstempel der jeweiligen Transaktion
-
Eine Spaltenfamilie namens "
sales" zur Erfassung von Verkaufstransaktionen an der Kasse -
Innerhalb der
salesSäulenfamilie erfassen wir:-
Die Artikel-ID (eine Zeichenkette)
-
Der Preis, zu dem der Artikel verkauft wurde (eine Gleitkommazahl)
-
Die Anzahl der gekauften Artikel in dieser Transaktion (eine ganze Zahl)
-
In Abbildung 4-10 siehst du, dass wir alle diese Informationen im Abschnitt Column Families der Tabellendefinition angegeben haben.
Cloud Bigtable behandelt alle Daten einfach als Byte-Strings. Das Schema (String, Float, Integer) ist also eher für BigQuery gedacht, damit wir die Werte in unseren Abfragen nicht jedes Mal casten müssen. Das Vermeiden des Casts ist auch der Grund, warum wir verlangen, dass der Zeilenschlüssel als String behandelt wird. Wenn die BigQuery-Tabelle erstellt wird, wird jede Spalte in Cloud Bigtable auf eine Spalte in BigQuery mit dem entsprechenden Typ abgebildet:
| verkauf.preis | REKORD | NULLABLE | Beschreibe dieses Feld... |
|---|---|---|---|
| verkauf.preis.zelle | REKORD | NULLABLE | Beschreibe dieses Feld... |
| verkauf.preis.zelle.zeitstempel | TIMESTAMP | NULLABLE | Beschreibe dieses Feld... |
| verkauf.preis.zelle.wert | FLOAT | NULLABLE | Beschreibe dieses Feld... |
Mit der BigQuery-Tabelle ist es nun möglich, eine gute, altmodische SQL-Abfrage zu stellen, um die Gesamtzahl der verkauften itemid 12345 zu ermitteln:
SELECT SUM(sales.qty.cell.value) AS num_sold FROM ch04.logs WHERE sales.itemid.cell.value = '12345'
Die Leistung verbessern
Wenn wir eine Verbundabfrage auf Daten in Google Cloud Storage stellen, wird die Arbeit von BigQuery Workern ausgeführt. Wenn wir hingegen eine Verbundabfrage auf Daten in Cloud Bigtable stellen, wird die Arbeit auf dem Cloud Bigtable-Cluster ausgeführt. Die Leistung der zweiten Abfrage wird daher durch die Kapazität des Cloud Bigtable-Clusters und die Last, die zum Zeitpunkt der Abfrage auf ihm lastet, begrenzt.
Wie bei jeder analytischen Abfrage hängt die Gesamtgeschwindigkeit der Abfrage auch von der Anzahl der zu lesenden Zeilen und der Größe der zu lesenden Daten ab. BigQuery versucht, die Menge der zu lesenden Daten zu begrenzen, indem es nur die Spaltenfamilien liest, auf die in der Abfrage verwiesen wird, und Cloud Bigtable teilt die Daten auf die Knoten auf, um die Verteilung der Zeilenschlüsselpräfixe über den gesamten Datensatz zu nutzen.
Hinweis
Wenn du Daten mit einer hohen Aktualisierungshäufigkeit hast oder Punktabfragen mit geringer Latenz benötigst, bietet Cloud Bigtable die beste Leistung für Abfragen, die nach einer Reihe von Zeilenschlüsselpräfixen filtern können. Es ist verlockend zu denken, dass BigQuery die Leistung von Cloud Bigtable umgeht, indem es Ad-hoc-Punktabfragen von Cloud Bigtable-Daten unterstützt, die nicht durch Zeilenschlüssel begrenzt sind. Dieses Muster führt jedoch oft zu einer enttäuschenden Leistung, und du solltest es mit deiner Arbeitslast vergleichen, bevor du dich für eine Produktionsarchitektur entscheidest.
BigQuery speichert Daten in einer spaltenorientierten Reihenfolge, die für Tabellenscans optimiert ist, während Cloud Bigtable Daten in einer zeilenorientierten Reihenfolge speichert, die für kleine Lese- und Schreibvorgänge optimiert ist. Abfragen von externen Daten, die in Cloud Bigtable gespeichert sind, bieten nicht die Vorteile der internen spaltenbasierten Speicherung von BigQuery und sind nur dann performant, wenn sie eine Teilmenge von Zeilen lesen, nicht aber, wenn sie einen vollständigen Tabellenscan durchführen. Daher solltest du darauf achten, dass deine BigQuery-Federated-Abfragen nach dem Bigtable-Zeilenschlüssel filtern, da sie sonst jedes Mal die gesamte Cloud Bigtable-Tabelle lesen müssen.
Der Drehknopf, den du unter Kontrolle hast, ist die Anzahl der Knoten in deinem Cloud Bigtable-Cluster. Wenn du routinemäßig SQL-Abfragen gegen deine Cloud Bigtable-Daten stellst, solltest du die CPU-Auslastung von Cloud Bigtable überwachen und die Anzahl der Cloud Bigtable-Knoten bei Bedarf erhöhen.
Wie bei föderierten Abfragen über Google Cloud Storage solltest du auch hier überlegen, ob es vorteilhaft ist, eine ELT-Pipeline einzurichten, wenn du Analysen über Daten in Cloud Bigtable durchführst, d. h., du solltest in Erwägung ziehen, Daten mit einer föderierten Abfrage aus Cloud Bigtable zu extrahieren und sie für weitere Analysen und Transformationen in eine BigQuery-Tabelle zu laden. Dieser Ansatz, der in Abbildung 4-11 dargestellt ist, ermöglicht es dir, deine Analysen in einer Umgebung durchzuführen, in der du nicht von der Betriebslast von Cloud Bigtable abhängig bist. Analysen auf einer internen BigQuery-Tabelle können auf Tausenden von Rechnern durchgeführt werden, anstatt auf einem viel kleineren Cluster. Die Analyseabfragen werden daher in BigQuery schneller abgeschlossen (vorausgesetzt, diese Analysen können nicht mit Row-Key-Präfixen durchgeführt werden), als wenn du föderierte Abfragen auf einer externen Tabelle verwendest. Der Nachteil ist natürlich, dass die extrahierten Daten sowohl in Cloud Bigtable als auch in BigQuery dupliziert werden. Dennoch ist die Speicherung in der Regel kostengünstig und die Vorteile der Skalierung und der Geschwindigkeit könnten ein ausreichender Ausgleich sein.
Abbildung 4-11. Verwenden Sie eine föderierte Abfrage, um ausgewählte Tabellen in eine BigQuery-interne Tabelle zu exportieren und Ihre Analyse-Workloads die interne Tabelle abfragen zu lassen
Es ist möglich, den Dateningest in die internen BigQuery-Tabellen so zu planen, dass er regelmäßig stattfindet. Wir sehen uns das im nächsten Abschnitt an.
Transfers und Exporte
Bisher haben wir uns mit dem einmaligen Laden von Daten und der Vermeidung von Datenverschiebungen durch föderierte Abfragen beschäftigt. In diesem Abschnitt sehen wir uns schlüsselfertige Dienste an, mit denen regelmäßig Daten aus verschiedenen Quellen in BigQuery übertragen kann.
Datenübertragungsdienst
Mit dem BigQuery Data Transfer Service kannst du wiederkehrende Datenladungen aus einer Vielzahl von Datenquellen in BigQuery planen. Wie bei den meisten BigQuery-Funktionen kannst du auf den BigQuery Data Transfer Service über die Web-UI, das Kommandozeilen-Tool oder über eine REST-API zugreifen. Aus Gründen der Wiederholbarkeit zeigen wir dir das Kommandozeilen-Tool.
Nachdem du eine Datenübertragung konfiguriert hast, lädt BigQuery die Daten automatisch nach dem von dir festgelegten Zeitplan. Falls es jedoch ein Problem mit den ursprünglichen Daten gibt, kannst du auch eine Rücksicherung der Daten veranlassen, um Ausfälle oder Lücken zu beheben. Dies wird als Auffrischung bezeichnet, die du über die Web-UI einleiten kannst.
Der Datenübertragungsdienst unterstützt das Laden von Daten aus einer Reihe von Software-as-a-Service (SaaS)-Anwendungen, wie Google Ads, Google Play, Amazon Redshift und YouTube, sowie aus Google Cloud Storage. Wir sehen uns an, wie man einen Routine-Ingest von Dateien einrichtet, die in der Cloud Speicherung auftauchen, und gehen dabei auf die Unterschiede bei der Datenübertragung von SaaS-Datensätzen ein, wobei wir die YouTube-Kanalberichte als Beispiel verwenden.
Datenort
Wie wir bereits in diesem Kapitel besprochen haben, werden die BigQuery -Datensätze in einer bestimmten Region (z. B. asia-northeast1, was Tokio ist) oder an einem multiregionalen Standort (z. B. EU) erstellt.25 Wenn du einen Datentransferdienst für ein Dataset einrichtest, verarbeitet und stellt er die Daten an demselben Ort bereit wie das Ziel-BigQuery-Dataset.
Wenn sich dein Bucket für die Cloud Speicherung in der gleichen Region befindet wie dein BigQuery-Datensatz, fallen für die Datenübertragung keine Gebühren an. Bei der Übertragung von Daten zwischen Regionen (z. B. von einem Bucket des Cloud-Speichers in einer Region zu einem BigQuery-Datensatz in einer anderen Region) fallen Netzwerkgebühren an, unabhängig davon, ob die Übertragung durch Laden, Exportieren oder Übertragen von Daten erfolgt.
Der BigQuery Data Transfer Service muss aktiviert sein (du kannst dies über die BigQuery Web UI tun), und muss dir die Rolle bigquery.admin zugewiesen worden sein, damit du Übertragungen erstellen und Daten in das Zieldataset schreiben kannst.
Einrichten der Zieltabelle
Der Datenübertragungsdienst hat nicht die Möglichkeit, eine neue Tabelle zu erstellen, das Schema automatisch zu erkennen und so weiter. Stattdessen musst du eine Vorlagentabelle mit dem gewünschten Schema bereitstellen. Wenn du alle Daten in eine spaltenpartitionierte Tabelle schreibst, gib die Partitionierungsspalte als TIMESTAMP oder DATE Spalte an, wenn du das Schema der Zieltabelle erstellst. Wir behandeln Partitionen im Detail in Kapitel 7.
Hier veranschaulichen wir den Prozess anhand des College-Scorecard-Datensatzes. Wir haben ihn in der Multiregion USA gespeichert. Du solltest also ein Dataset in der Multiregion USA erstellen, wenn du die folgenden Schritte ausprobieren möchtest.
Führe in BigQuery die folgende Abfrage aus:
CREATE OR REPLACE TABLE ch04.college_scorecard_dts AS SELECT * FROM ch04.college_scorecard_gcs LIMIT 0
Dies ist ein Beispiel für eine DDL-Anweisung. Sie speichert das Ergebnis der Abfrage SELECT (die keine Zeilen enthält und keine Kosten verursacht) als Tabelle namens college_scorecard_dts im Dataset ch04.
Einen Transferauftrag erstellen
Gib in der Befehlszeile von den folgenden Befehl ein, um einen Übertragungsauftrag einzurichten:
bq mk --transfer_config --data_source=google_cloud_storage \
--target_dataset=ch04 --display_name ch04_college_scorecard \
--params='{"data_path_template":"gs://bigquery-oreilly-book/college_*.csv",
"destination_table_name_template":"college_scorecard_dts", "file_format":"CSV",
"max_bad_records":"10", "skip_leading_rows":"1", "allow_jagged_rows":"true"}'
Mit diesem Befehl wird festgelegt, dass die Quelle der Daten Google Cloud Storage sein soll (wenn du z.B. von YouTube Channel überträgst, wäre die Datenquelle youtube_channel) und dass der Zieldatensatz ch04 ist. Der Anzeigename wird auf verschiedenen Benutzeroberflächen als menschenlesbarer Name verwendet, um auf den Übertragungsauftrag hinzuweisen.
Bei YouTube werden die Zieltabellen zum Zeitpunkt des Imports automatisch partitioniert und entsprechend benannt. Im Falle der Cloud Speicherung musst du dies jedoch explizit im Namen der Zieltabelle angeben. Wenn du z. B. mytable_{run_time|"%Y%m%d"} als Vorlage für den Namen der Zieltabelle angibst, beginnt der Tabellenname mit mytable und wird von der Laufzeit des Auftrags unter Verwendung der unter datetime angegebenen Formatierungsparameter angehängt.26 Eine praktische Abkürzung ist ytable_{run_date}. Dabei wird einfach das Datum im Format JJJJMMTT verwendet. Es ist auch möglich, einen Zeitversatz anzugeben. Um zum Beispiel die Tabelle nach dem Zeitstempel 45 Minuten nach der Laufzeit zu benennen, könnten wir Folgendes angeben:
{run_time+45m|"%Y%m%d"}_mytable_{run_time|"%H%M%s"}
Daraus ergibt sich ein Tabellenname der Form 20180915_mytable_004500.
Die Parameter selbst sind spezifisch für die Datenquelle. Im Fall der Übertragung von Dateien aus Google Cloud Storage sollten wir Folgendes angeben:
-
Der Pfad der Eingabedaten, mit einem optionalen Platzhalter.
-
Die Vorlage für den Namen der Zieltabelle.
-
Das Dateiformat. Der Übermittlungsdienst von Cloud Storage unterstützt alle Datenformate, die auch von der föderierten Abfrage unterstützt werden (CSV, JSON, Avro, Parquet, etc.). Wenn das Dateiformat CSV ist, können wir CSV-spezifische Optionen angeben, z. B. die Anzahl der zu überspringenden Kopfzeilen.
Zu den Parametern für die Datenübertragung des YouTube-Kanals gehören die page_id (in YouTube) und table_suffix (in BigQuery).
Wenn du den Befehl bq mk ausführst, erhältst du, wie gerade gezeigt, eine URL als Teil eines OAuth2-Workflows; gib das notwendige Token an, indem du dich über den Browser anmeldest, und der Übertragungsauftrag wird erstellt.
Du kannst einen Datentransfer-Service auch über die Web-Oberfläche initiieren. Initiiere eine Übertragung und wähle die Datenquelle, wie in Abbildung 4-12 dargestellt.
Abbildung 4-12. Du kannst eine Datenübertragung auch über die Web-UI initiieren
Beachte, dass wir keinen Zeitplan angegeben haben; standardmäßig wird der Auftrag alle 24 Stunden ausgeführt, beginnend "jetzt". Es ist möglich, den Zeitplan des Übertragungsjobs über die BigQuery-Web-UI zu bearbeiten, wie in Abbildung 4-13 gezeigt.
Abbildung 4-13. Bearbeiten des Zeitplans des Übertragungsauftrags über die Web-UI
Der Preis für die Datenübertragung variiert je nach Quelle. Zum jetzigen Zeitpunkt kosten Datenübertragungen von YouTube-Kanälen 5 US-Dollar pro Kanal und Monat, während Datenübertragungen von Cloud-Speichern kostenlos sind. Da der Datentransfer-Service jedoch Ladeaufträge verwendet, um Daten aus der Cloud Speicherung in BigQuery zu laden, unterliegt dies den BigQuery-Limitierungen für Ladeaufträge.
Geplante Abfragen
BigQuery unterstützt die Planung von Abfragen, die in regelmäßigen Abständen ausgeführt werden, und das Speichern der Ergebnisse in BigQuery-Tabellen. Du kannst insbesondere eine föderierte Abfrage verwenden, um Daten aus einer externen Datenquelle zu extrahieren, sie umzuwandeln und in BigQuery zu laden. Da solche geplanten Abfragen DDL- und DML-Anweisungen enthalten können, ist es möglich, anspruchsvolle Workflows rein in SQL zu erstellen.
Du kannst das Dialogfeld zum Einrichten einer geplanten Abfrage öffnen, indem du auf die Schaltfläche Abfrage planen in der BigQuery-Benutzeroberfläche klickst, wie in Abbildung 4-14 dargestellt.27
Abbildung 4-14. Planen einer Abfrage über die BigQuery-Benutzeroberfläche
Geplante Abfragen bauen auf dem Datenübertragungsdienst auf, so dass viele der Funktionen ähnlich sind. So kannst du die Zieltabelle mit denselben Parametereinstellungen (z. B. run_date und run_time) angeben wie beim Datenübertragungsdienst (siehe vorheriger Abschnitt).
Regionsübergreifende Datensatzkopie
BigQuery unterstützt die Planung von regionsübergreifenden Datensatzkopien über den Datentransferdienst. Wähle in der Web-UI des Datentransferdienstes als Quelle die regionsübergreifende Kopie. Außerdem musst du als Quell-Dataset den Namen des Datasets angeben, aus dem die Tabellen in das Zieldataset kopiert werden sollen (siehe Abbildung 4-15).
Da es sich bei den Quell- und Zieldatensätzen um BigQuery-Datensätze handelt, muss der Initiator die Berechtigung haben, Datenübertragungen zu initiieren, Tabellen im Quelldatensatz aufzulisten, den Quelldatensatz anzuzeigen und den Zieldatensatz zu bearbeiten.
Eine regionsübergreifende Kopie kann auch von bq mk initiiert werden, indem cross_region_copy als Datenquelle angibt.
Abbildung 4-15. Initiiere eine geplante regionsübergreifende Datensatzkopie über die Benutzeroberfläche des Datentransferdienstes, indem du angibst, dass die Quelle eine regionsübergreifende Kopie ist
Exportieren von Cloud-Logging-Protokollen
Logdaten von virtuellen Maschinen (VMs) und Diensten im GCP28 können in den Cloud Logging-Protokollen gespeichert, überwacht und analysiert werden. Cloud Logging dient also als einheitliche Sicht auf alle Aktivitäten in deinem GCP-Konto. Daher ist es hilfreich, Cloud Logging- und Firebase-Protokolle nach BigQuery zu exportieren. Dazu kannst du die Befehlszeilenschnittstelle, eine REST-API oder die Web-UI verwenden, die in Abbildung 4-16 dargestellt ist.
Um alle Logs des BigQuery-Dienstes zu exportieren, klicke oben im Cloud Logging Logs Viewer auf die Schaltfläche Export erstellen und gib dann die folgenden Informationen ein:
-
Wähle BigQuery und Alle Logs, um die Logs von BigQuery anzuzeigen. Siehst du deine letzten Aktivitäten?
-
Gib einen Namen für das Waschbecken an, zum Beispiel
bq_logs. -
Gib den Sink-Service an: BigQuery, denn wir wollen nach BigQuery exportieren.
-
Gib das Ziel der Senke an:
ch04, den Datensatz, in den wir exportieren wollen.
Abbildung 4-16. Um die Protokolle der BigQuery-Ingest-Aufträge aus dem vorherigen Abschnitt anzuzeigen, gehst du zum Beispiel zum Abschnitt Cloud Logging in der GCP Cloud Console
Schauen wir uns die Logs an, die beim Ausführen einer Abfrage erstellt werden. Gehe zur BigQuery-Benutzeroberfläche und versuche, eine Abfrage auszuführen:
SELECT gender, AVG(tripduration / 60) AS avg_trip_duration FROM `bigquery-public-data`.new_york_citibike.citibike_trips GROUP BY gender HAVING avg_trip_duration > 14 ORDER BY avg_trip_duration
Wenn du jetzt in der BigQuery-Benutzeroberfläche (das Datum entsprechend ändern)
SELECT protopayload_auditlog.status.message FROM ch04.cloudaudit_googleapis_com_data_access_20190128
findest du eine Liste mit BigQuery-Protokollmeldungen, einschließlich einer Meldung über das Lesen der Ergebnisse der vorangegangenen Abfrage. Abhängig von deinem Datumsfilter solltest du auch die Protokolle früherer Operationen sehen, die du durchgeführt hast.
Beachte ein paar Dinge über die Exportfähigkeit:
-
Das Schema und sogar der Tabellenname wurden von Cloud Logging festgelegt. Wir haben einfach den Zieldatensatz angegeben.
-
Die Daten wurden fast in Echtzeit aktualisiert. Dies ist ein Beispiel für einen Streaming-Puffer - eine BigQuery-Tabelle, die von Cloud Logging in Echtzeit aktualisiert wird (obwohl die typische Latenzzeit von BigQuery-Abfragen bedeutet, dass die Daten, die du siehst, schon ein paar Sekunden alt sind).
Cloud Dataflow zum Lesen/Schreiben aus BigQuery verwenden
Wie wir bereits besprochen haben, unterstützt BigQuery föderierte Abfragen aus Quellen wie Google Sheets. Der Data Transfer Service unterstützt Quellen wie Google Ads und YouTube. Produkte wie Stackdriver Logging und Firestore bieten die Möglichkeit, ihre Daten nach BigQuery zu exportieren.
Was ist, wenn du ein Produkt wie MySQL verwendest, das keine Exportfunktion bietet und vom Datenübertragungsdienst nicht unterstützt wird? Eine Möglichkeit ist die Nutzung von Cloud Dataflow. Cloud Dataflow ist ein vollständig verwalteter Dienst auf GCP, der die Ausführung von Datenpipelines vereinfacht, die mit der Open-Source-API Apache Beam erstellt wurden, indem er sich um betriebliche Details wie Leistung, Skalierung, Verfügbarkeit, Sicherheit und Compliance kümmert, so dass sich die Nutzer auf die Programmierung konzentrieren können, anstatt Server-Cluster zu verwalten. Du kannst Dataflow für die Umwandlung und Anreicherung von Daten sowohl im Streaming-Modus (Echtzeit) als auch im Batch-Modus (historisch) verwenden, und zwar mit demselben wiederverwendbaren Code für Streaming- und Batch-Pipelines.
Eine Dataflow-Vorlage verwenden, um direkt von MySQL zu laden
Du kannst zwar deine eigenen Cloud-Dataflow-Pipelines schreiben (das tun wir in "Einen Dataflow-Auftrag schreiben"), aber auf GitHub sind Dataflow-Vorlagen für viele gängige Anforderungen verfügbar. Ein Blick auf die Liste der verfügbaren Templates zeigt, dass das Jdbc to BigQuery-Template für unsere Anforderungen geeignet ist und es uns ermöglicht, Daten von unserer MySQL-Datenbank zu BigQuery zu übertragen.
Öffne die GCP Cloud Console und navigiere zum Abschnitt "Cloud Dataflow". Als Nächstes wählst du "Auftrag aus Vorlage erstellen", wählst "Jdbc zu BigQuery" und füllst dann das Formular mit Informationen über die Quelldatenbanktabelle in MySQL und die Zieltabelle in BigQuery aus, wie in Abbildung 4-17 dargestellt.
Abbildung 4-17. Erstellen eines Dataflow-Jobs aus einer Vorlage zur Übertragung von Daten von MySQL zu BigQuery
Wenn du auf die Schaltfläche "Auftrag ausführen" klickst, wird ein Dataflow-Auftrag gestartet. Er führt die von dir angegebene JDBC-Abfrage aus und schreibt die resultierenden Zeilen in BigQuery.
Einen Dataflow-Auftrag schreiben
Wenn du ein Format hast, für das es keine föderierte Abfrage, keinen Datentransferdienst, keine Exportmöglichkeit und keine vorgefertigte Dataflow-Vorlage gibt, kannst du deine eigene Dataflow-Pipeline schreiben, um die Daten in BigQuery zu laden.
Obwohl es sowohl eine föderierte Abfrage als auch einen Datenübertragungsdienst für CSV-Dateien auf Google Cloud Storage gibt, werden wir CSV-Dateien verwenden, um zu demonstrieren, wie dies aussieht. Der Code ist für die Apache Beam API geschrieben und kann in Python, Java oder Go geschrieben werden. Hier verwenden wir Python.
Der Kern des Codes besteht darin, die Eingabedaten zu extrahieren, sie umzuwandeln, indem die gewünschten Felder extrahiert und bereinigt werden, und sie in BigQuery zu laden:
INPATTERNS = 'gs://bigquery-oreilly-book/college_*.csv'
RUNNER = 'DataflowRunner'
with beam.Pipeline(RUNNER, options = opts) as p:
(p
| 'read' >> beam.io.ReadFromText(INPATTERNS, skip_header_lines=1)
| 'parse_csv' >> beam.FlatMap(parse_csv)
| 'pull_fields' >> beam.FlatMap(pull_fields)
| 'write_bq' >> beam.io.gcp.bigquery.WriteToBigQuery(bqtable, bqdataset,
schema=get_output_schema())
)
In diesem Code erstellen wir eine Beam-Pipeline und geben an, dass sie von Cloud Dataflow ausgeführt werden soll. Andere Optionen für RUNNER sind DirectRunner (Ausführung auf dem lokalen Rechner) und SparkRunner (Ausführung durch Apache Spark auf einem Hadoop-Cluster, wie Cloud Dataproc auf GCP).
Der erste Schritt der Pipeline besteht darin, alle Dateien zu lesen, die mit den angegebenen Eingabemustern übereinstimmen. Diese Dateien können sich auf der lokalen Festplatte oder auf Google Cloud Storage befinden. Die Daten aus den Textdateien werden Zeile für Zeile an den nächsten Schritt der Pipeline weitergeleitet, wo die Methode parse_csv auf jede Zeile angewendet wird:
def parse_csv(line):
try:
values = line.split(',')
rowdict = {}
for colname, value in zip(COLNAMES, values):
rowdict[colname] = value
yield rowdict
except:
logging.warn('Ignoring line ...')
Die Methode parse_csv trennt die Zeile anhand von Kommas und wandelt die Werte in ein Wörterbuch um, wobei der Schlüssel der Name der Spalte und der Wert der Wert der Zelle ist.
Dieses Wörterbuch wird dann an die Methode pull_fields gesendet, die die Daten von Interesse (die Spalte INSTNM und einige numerische Felder) extrahiert und umwandelt:
def pull_fields(rowdict):
result = {}
# required string fields
for col in 'INSTNM'.split(','):
if col in rowdict:
result[col] = rowdict[col]
else:
logging.info('Ignoring line missing {}', col)
return
# float fields
for col in \
'ADM_RATE_ALL,FIRST_GEN,MD_FAMINC,SAT_AVG,MD_EARN_WNE_P10'.split(','):
try:
result[col] = (float) (rowdict[col])
except:
result[col] = None
yield result
Diese Dictionaries mit den extrahierten Feldern werden Zeile für Zeile in BigQuery übertragen. Die BigQuery-Senke (beam.io.gcp.bigquery.WriteToBigQuery) benötigt den Namen der Tabelle, den Namen des Datensatzes und ein Ausgabeschema der folgenden Form:
INSTNM:string,ADM_RATE_ALL:FLOAT64,FIRST_GEN:FLOAT64,...
Die BigQuery-Tabelle wird bei Bedarf erstellt und die Zeilen werden angehängt. Es gibt auch andere Optionen, z. B. die Möglichkeit, die Tabelle abzuschneiden (d. h. zu ersetzen).
Das Ausführen des Python-Programms29 wird ein Dataflow-Auftrag gestartet, der die CSV-Datei liest, sie Zeile für Zeile analysiert, die erforderlichen Felder ausliest und die umgewandelten Daten in BigQuery schreibt.
Obwohl wir das Dataflow-Programm an einer Batch-Pipeline demonstriert haben (d.h. die Eingabe ist nicht unbegrenzt), kannst du im Grunde dieselbe Pipeline verwenden, um Datensätze, die du im Streaming-Modus (z.B. von Cloud Pub/Sub) erhältst, zu parsen, umzuwandeln und auszuschreiben, wie es in vielen Logging- und IoT-Anwendungen der Fall sein wird. Der Dataflow-Ansatz bietet also eine Möglichkeit, Daten im laufenden Betrieb umzuwandeln und in BigQuery zu laden.
Beachte, dass Dataflow Streaming-Inserts verwendet, um die Daten in BigQuery zu laden, unabhängig davon, ob du im Batch-Modus oder im Streaming-Modus arbeitest. Streaming-Inserts haben den Vorteil, dass die Daten zeitnah in einem Streaming-Puffer erscheinen und schon beim Schreiben der Daten abgefragt werden können. Der Nachteil ist, dass Streaming-Inserts im Gegensatz zu BigQuery-Ladeaufträgen nicht kostenlos sind. Erinnere dich daran, dass das Laden von Daten in BigQuery zwar kostenlos ist, es aber aus Leistungsgründen nur eine begrenzte Anzahl von Ladeaufträgen geben kann. Streaming-Inserts bieten eine Möglichkeit, die Beschränkungen und Quoten für Ladeaufträge zu umgehen, ohne die Abfrageleistung zu beeinträchtigen.
Die Streaming-API direkt verwenden
Wir haben Apache Beam auf Cloud Dataflow als eine Möglichkeit vorgestellt, Daten zu extrahieren, umzuwandeln und im Streaming-Modus in BigQuery zu laden, aber es ist nicht das einzige Datenverarbeitungs-Framework, das in der Lage ist, in BigQuery zu schreiben. Wenn dein Team mit Apache Spark besser vertraut ist, ist das Schreiben der ETL-Pipeline in Spark und die Ausführung auf einem Hadoop-Cluster (wie Cloud Dataproc auf GCP) eine echte Alternative zu Dataflow. Das liegt daran, dass es Client-Bibliotheken für eine Vielzahl von Sprachen gibt und BigQuery eine Streaming-API unterstützt.
Wir behandeln die Client-Bibliothek und das Streaming in Kapitel 5 ausführlicher, aber hier ist ein Schnipsel, der zeigt, wie du Daten mit der Streaming-API in Python laden kannst, nachdem du einen Client hast:
# create an array of tuples and insert as data becomes available
rows_to_insert = [
(u'U. Puerto Rico', 0.18,0.46,23000,1134,32000),
(u'Guam U.', 0.43,0.21,28000,1234,33000)
]
errors = client.insert_rows(table, rows_to_insert) # API request
Sobald neue Daten verfügbar sind, wird die Methode insert_rows() auf dem BigQuery-Client aufgerufen. Diese Methode wiederum ruft die tabledata.insertAll Methode der REST API auf. Die Daten werden von BigQuery in einem Streaming-Puffer gespeichert und stehen sofort für Abfragen zur Verfügung, obwohl es bis zu 90 Minuten dauern kann, bis die Daten für den Export verfügbar sind.
Verschieben von Daten vor Ort
In Kapitel 1 haben wir besprochen, dass einer der Schlüsselfaktoren für BigQuery die Trennung von Datenverarbeitung und Speicherung in einem Netzwerk mit einer Bandbreite von Petabit pro Sekunde ist. BigQuery funktioniert am besten mit Datensätzen, die sich im Rechenzentrum und hinter der Google Cloud-Firewall befinden - wenn BigQuery seine Daten über das öffentliche Internet oder eine langsamere Netzwerkverbindung lesen müsste, wäre es nicht so leistungsfähig. Damit BigQuery gut funktioniert, müssen sich die Daten also in der Cloud befinden.
BigQuery ist eine hoch skalierbare Analyseplattform und der empfohlene Ort, um strukturierte Daten zu speichern, mit Ausnahme von Daten, die für Echtzeit-Transaktionen bestimmt sind. Wenn BigQuery also der richtige Ort ist, um alle strukturierten Daten zu speichern, die für die Datenanalyse verwendet werden, wie kannst du dann deine lokalen Daten in BigQuery übertragen?
Methoden der Datenmigration
Wenn du über ein gutes Netzwerk mit schnellen Verbindungsgeschwindigkeiten zur Google Cloud verfügst, kannst du bq load verwenden, um die Daten in BigQuery zu laden. Wie in diesem Kapitel beschrieben, ist es besser, wenn die zu ladenden Daten bereits auf Google Cloud Storage vorhanden sind. Du kannst das Kommandozeilen-Tool gsutil verwenden, um die Daten von der lokalen Speicherung in die Cloud Speicherung zu kopieren.
Wenn du viele, vor allem große Dateien in die Google Cloud Speicherung kopierst, solltest du die Option -m verwenden, um Multithreading zu aktivieren. Durch Multithreading kann das Tool gsutil Dateien parallel kopieren:
gsutil -m cp /some/dir/myfiles*.csv gs://bucket/some/dir
Da es wahrscheinlich ist, dass weiterhin Daten gesammelt werden, ist das Verschieben von Daten oft kein einmaliger Vorgang, sondern ein laufender. Eine Möglichkeit, damit umzugehen, besteht darin, eine Cloud-Funktion zu starten, die automatisch bq load aufruft, wenn eine Datei in der Cloud Speicherung auftaucht.30 Je häufiger eine Datei ankommt (und je kleiner sie wird), desto besser ist es, wenn du Cloud Pub/Sub31 statt Cloud Storage zu verwenden, um die eingehenden Daten als Nachrichten zu speichern, die von einer Cloud Dataflow Pipeline verarbeitet und direkt in BigQuery gestreamt werden.
Diese drei Ansätze -gsutil, Cloud Functions und Cloud Dataflow - sind in den ersten drei Zeilen von Tabelle 4-2 aufgeführt und funktionieren, wenn die Netzwerkverbindung recht gut ist.
| Was du migrieren willst | Empfohlene Migrationsmethode |
|---|---|
| Relativ kleine Dateien | gsutil cp -mbq load |
| Gelegentliches Laden (z. B. einmal pro Tag) von Dateien in BigQuery, wenn sie verfügbar sind | gsutil cpCloud Function ruft auf bq load |
| Laden von Streaming-Nachrichten in BigQuery | Poste Daten an Cloud Pub/Sub und verwende dann Cloud Dataflow, um sie in BigQuery zu streamen. Normalerweise musst du die Pipeline in Python, Java, Go, etc. implementieren. Alternativ kannst du auch die Streaming-API der Client-Bibliothek verwenden. Dies wird in Kapitel 5 genauer behandelt. |
| Hive-Partitionen | Hive-Workload auf Cloud Dataproc migrieren Abfrage von Hive-Partitionen als externe Tabelle |
| Petabytes an Daten oder schlechtes Netzwerk | Gerät übertragenbq load |
| Von Region zu Region oder aus anderen Wolken | Übertragung von Speicherung in der Cloud |
| Aus einem MySQL-Dump laden | Open Source Dataflow Templates, die konfiguriert und ausgeführt werden können |
| Übertragung von Google Cloud Storage, Google Ads, Google Play, etc. zu BigQuery | BigQuery Datenübertragungsdienst Richte dies in BigQuery ein. Alle Funktionen des Datenübertragungsdienstes funktionieren auf ähnliche Weise. |
| Migriere von anderen Data Warehouses wie Amazon Redshift, Teradata, etc. | Der BigQuery Data Transfer Service migriert Daten und Schemata. Je nach Quell-Datawarehouse können Migrationsagenten verwendet werden, die sich im Quell-Datawarehouse befinden und sowohl einmalige als auch inkrementelle Übertragungen unterstützen können. |
| Stackdriver Logging, Firestore, etc. | Diese Tools bieten die Möglichkeit, nach BigQuery zu exportieren. Richte dies in dem anderen Tool ein (Stackdriver, Firestore, etc.). |
Auch wenn die Datenmigration mit gsutil, um die Daten auf dem Cloud-Speicher bereitzustellen, und , um bq load aufzurufen, einfach ist, wenn du nur ein paar kleine Datensätze hast, ist es schwieriger, wenn du viele Datensätze hast oder wenn deine Datensätze groß sind. Mit zunehmender Datengröße steigt auch die Fehleranfälligkeit. Deshalb musst du bei der Migration großer Datensätze auf die Details achten - z. B. die Prüfsumme der Daten bei der Erfassung und beim Einlesen überprüfen, mit Firewalls arbeiten, damit sie Übertragungen nicht blockieren oder Pakete verwerfen, die Exfiltration sensibler Daten vermeiden und sicherstellen, dass deine Daten während und nach der Migration verschlüsselt und vor Verlust geschützt sind.
Ein weiteres Problem bei der gsutil Methode ist, dass dein Unternehmen wahrscheinlich nicht in der Lage sein wird, Bandbreite für Datenübertragungen zur Verfügung zu stellen, da eine solche Bandbreite oft zu teuer ist und Routineabläufe, die Daten über das Unternehmensnetzwerk transportieren, stören würde.
In Fällen, in denen es aufgrund von Datengröße oder Netzwerkbeschränkungen nicht möglich ist, Daten in die Google Cloud zu kopieren, kannst du die Transfer Appliance verwenden. Dabei handelt es sich um einen rackfähigen Speicher-Server mit hoher Kapazität, der zu dir geliefert wird. Du füllst ihn auf und schickst ihn an Google Cloud oder einen seiner autorisierten Partner zurück. Die Transfer Appliance eignet sich am besten für große Datenmengen (Hunderte von Terabytes bis Petabytes), für die dein Netzwerk nicht ausreicht.
Wenn deine Daten nicht vor Ort, sondern in einer anderen öffentlichen Cloud gespeichert sind (z. B. in einem Amazon Web Services Simple Storage Service Bucket), kannst du den Cloud Storage Transfer Service nutzen, um die Daten zu migrieren. Häufige Anwendungsfälle sind z. B. Anwendungen, die auf Amazon Web Services laufen, deren Protokolldaten aber in BigQuery analysiert werden. Der Cloud Storage Transfer Service ist auch eine gute Möglichkeit, um große Datenmengen zwischen verschiedenen Regionen bei Google zu übertragen.
Der BigQuery Data Transfer Service automatisiert das Laden von Daten in BigQuery aus Google-Eigenschaften wie YouTube, Google Ads und mehr. Andere Tools wie Stackdriver Logging und Firestore bieten die Möglichkeit, Daten nach BigQuery zu exportieren.
Der BigQuery Data Transfer Service unterstützt auch die automatische Migration von Daten und Schemata aus anderen Data Warehouse-Produkten wie Amazon Redshift und Teradata. Im Falle von Teradata verbindet sich ein Migrationsagent vor Ort mit dem lokalen Data Warehouse und kopiert die Daten nach BigQuery. Es werden sowohl einmalige als auch inkrementelle Übertragungen unterstützt. Die Extraktion nach Partitionen wird ebenfalls unterstützt. Derzeit werden nur Daten und Schemata migriert, nicht aber ETL-Pipelines und Stored Procedures, obwohl es Partner-Tools gibt, die eine automatische SQL-Übersetzung und Data Warehouse-Virtualisierung durchführen können. In der Dokumentation kannst du nachlesen, was für die einzelnen Quell-Data Warehouses unterstützt wird.
Auch wenn du die Datenmigration selbst durchführen kannst, ist es unwahrscheinlich, dass deine IT-Abteilung viel Erfahrung damit hat, da die Migration oft nur eine einmalige Aufgabe ist. Es könnte von Vorteil sein, einen von GCP autorisierten Partner32 der die Datenmigration durchführt.
Zusammenfassung
Das Kommandozeilen-Tool bq bietet einen zentralen Einstiegspunkt für die Interaktion mit dem BigQuery-Dienst auf GCP. Nachdem deine Daten auf Google Cloud Storage gespeichert sind, kannst du sie mit dem Dienstprogramm bq load einmalig laden. Es unterstützt die automatische Schemaerkennung, kann aber auch ein bestimmtes, von dir bereitgestelltes Schema verwenden. Je nachdem, ob dein Ladeauftrag CPU- oder I/O-gebunden ist, kann es von Vorteil sein, die Daten entweder zu komprimieren oder sie unkomprimiert zu lassen.
Es ist möglich, die Daten an Ort und Stelle zu belassen, die Struktur der Daten festzulegen und BigQuery nur als Abfragemaschine zu verwenden. Diese Daten werden als externe Datensätze bezeichnet, und Abfragen über externe Datensätze werden als föderierte Abfragen bezeichnet. Verwende föderierte Abfragen für explorative Arbeiten oder wenn die Daten hauptsächlich im externen Format verwendet werden (z. B. Abfragen mit geringer Latenz in Cloud Bigtable oder interaktives Arbeiten in Sheets). EXTERNAL_QUERY bietet die Möglichkeit, Echtzeit-Joins mit MySQL- und Postgres-Datenbanken durchzuführen, ohne dass die Daten bewegt werden. Für große, relativ stabile und gut verstandene Datensätze, die regelmäßig aktualisiert und häufig abgefragt werden, ist die native Speicherung von BigQuery die bessere Wahl. Föderierte Abfragen sind auch in einem ELT-Workflow (Extrahieren, Laden und Transformieren) nützlich, bei dem die Daten noch nicht gut bekannt sind.
Es ist möglich, eine geplante Übertragung von Daten aus einer Vielzahl von Plattformen in BigQuery einzurichten. Auch andere Tools unterstützen Mechanismen zum Export ihrer Daten in BigQuery. Für das routinemäßige Laden von Daten solltest du Cloud Functions verwenden; für laufende Streaming-Ladungen kannst du Cloud Dataflow nutzen. Es ist auch möglich, Abfragen (einschließlich föderierter Abfragen) so zu planen, dass sie regelmäßig laufen und Daten in Tabellen laden.
1 Sechs bis acht Änderungen alle zehn Jahre - siehe https://oreil.ly/Merow.
2 Siehe https://abc7ny.com/news/border-of-north-and-south-carolina-shifted-on-january-1st/1678605/ und https://www.nytimes.com/2014/08/24/opinion/sunday/how-the-carolinas-fixed-their-blurred-lines.html.
3 Dies wird über ein Dropdown-Feld in der GCP-Cloud-Konsole eingestellt oder als du das letzte Mal eine gcloud init gemacht hast. Normalerweise entspricht ein Projekt einem Arbeitsvolumen oder einem kleinen Team.
4 Eine aktualisierte Liste findest du unter https://cloud.google.com/bigquery/docs/locations.
5 Der Autodetect-Algorithmus verarbeitet immer mehr Eckfälle, so dass dies bei dir vielleicht nicht der Fall ist. Im Allgemeinen wird die automatische Schemaerkennung jedoch nie perfekt sein. Unabhängig von den Details, welcher Aspekt des Schemas nicht korrekt erfasst wurde, gilt: Verwende das automatisch erkannte Schema als Ausgangspunkt und baue darauf auf, wie wir es in diesem Abschnitt tun.
6 Es ist möglich, dass eine Integer-Spalte nullbar ist, aber die Datei kodiert NULL Werte auf eine nicht standardisierte Weise. BigQuery interpretiert den Text NULL als String, weshalb das Laden fehlschlägt.
7 Die Zeichenfolge NULL in der Datei bedeutet, dass für dieses Feld keine Daten vorhanden sind, und genau das sollte auch ein NULL Wert in unserer BigQuery-Tabelle bedeuten.
8 Wie wir bereits in früheren Kapiteln erwähnt haben, gehen wir davon aus, dass alle Preisangaben zum Zeitpunkt der Erstellung dieses Buches korrekt sind. Bitte sieh dir jedoch die entsprechenden Richtlinien und Preislisten(https://cloud.google.com/bigquery/pricing) an, da diese sich ändern können.
9 Zum jetzigen Zeitpunkt gibt es diese Funktion in der "neuen" Benutzeroberfläche nicht; du musst sie über das Befehlszeilentool bq aufrufen.
10 Zeichenketten werden lexikalisch sortiert. Wenn sie als Zeichenkette gespeichert würden, wäre "100" kleiner als "20", weil "abc" vor "de" steht, wenn die beiden Zeichenketten sortiert werden. Bei einer numerischen Sortierung ist 20 kleiner als 100, wie du es erwarten würdest.
11 Die Datei enthält D/M/YYYY, während das Standardformat für ein Datum YYYY-MM-DD ist (was ISO 8601 entspricht). Obwohl Autodetect mehrere Zeilen betrachten und daraus schließen kann, ob der 11.12.1965 der 12. November oder der 11. Dezember ist, wollen wir nicht, dass die schemabasierte BigQuery-Ladung solche Annahmen macht. Die Transformationspipeline, die wir später in diesem Kapitel aufbauen, wandelt die Daten in das Standardformat um. Für den Moment wollen wir sie einfach als String behandeln.
12 Newline-delimited JSON wird oft unter dem Namen jsonl oder "JSON lines format" verwendet.
13 Siehe https://blog.twitter.com/engineering/en_us/a/2013/dremel-made-simple-with-parquet.html. In Kapitel 6 gehen wir auf Capacitor ein, das Backend-Speicherformat von BigQuery, das der Nachfolger von ColumnIO ist.
14 Probiere es aus, indem du die load_*.sh-Skripte im 04_load des GitHub-Repository für dieses Buch ausführst.
15 Diese Datei enthält einen "Byte Order Marker" (\u0eff) als erstes Zeichen, also entfernen wir die ersten paar Bytes mit cut: cut -b 4-.
16 Das vollständige Skript heißt load_external_gcs.sh und befindet sich im GitHub-Repository für dieses Buch.
17 Für die Grammatik eines JSONPaths siehe https://restfulapi.net/json-jsonpath/.
18 Zu diesen Partnern gehören Alooma, Informatica und Talend. Eine vollständige und aktuelle Liste der BigQuery-Partner findest du unter https://cloud.google.com/bigquery/partners/.
19 Zum jetzigen Zeitpunkt gibt es Größenbeschränkungen für die BigQuery-Tabelle.
20 Aufgrund ständiger Änderungen und Verbesserungen an den Produkten können die Grafiken, die du siehst, anders aussehen.
21 Diese Abfrage wird langsam sein, weil wir einen regulären Ausdruck abgleichen und dies 77 Milliarden Mal tun.
22 Höchstwahrscheinlich enthalten die Zeilen Daten von mehreren Hochschulen, z. B. der National University of Singapore, der National University of Ireland, dem Massachusetts Institute of Technology, dem Georgia Institute of Technology und so weiter.
23 Wenn du die Datei setup_data.sh im GitHub-Repository ausgeführt hast, ist project_id deine eindeutige Projekt-ID, instance_id ist bqbook-instance, und table_name ist logs-table.
24 Zum jetzigen Zeitpunkt ist diese Funktion nur in der "alten" Benutzeroberfläche auf https://bigquery.cloud.google.com/ verfügbar und nicht in der "neuen" Benutzeroberfläche, die Teil der GCP Cloud Console(https://console.cloud.google.com/bigquery) ist.
25 Siehe https://cloud.google.com/bigquery/docs/locations für die Standorte der BigQuery-Datensätze und https://cloud.google.com/storage/docs/bucket-locations für die Standorte der Cloud-Speicherung.
26 Eine Liste der verfügbaren Formatierungsoptionen findest du in den BigQuery-Dokumenten zur Formatierung von Datumsspalten.
27 Zurzeit ist dies nur in der "klassischen Benutzeroberfläche" verfügbar.
28 Auch von VMs und Services, die in Amazon Web Services laufen.
29 Siehe 04_load/dataflow.ipynb im GitHub-Repository des Buches.
30 Wie du das programmatisch machen kannst, erfährst du in Kapitel 5.
31 Ein Nachrichtenbusdienst - siehe https://cloud.google.com/pubsub/.
32 Siehe https://cloud.google.com/bigquery/providers/. Als dieses Buch geschrieben wurde, gab GCP seine Absicht bekannt, Alooma zu übernehmen, einen Anbieter von Cloud-Migrationsdiensten - siehe https://cloud.google.com/blog/topics/inside-google-cloud/google-announces-intent-to-acquire-alooma-to-simplify-cloud-migration.
Get Google BigQuery: Der endgültige Leitfaden now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
