Partitionierung und Clustering in BigQuery

1. Einführung

BigQuery ist ein vollständig verwaltetes, kostengünstiges Data Warehouse für Unternehmen für Analysen im Petabyte-Bereich. BigQuery ist serverlos. Sie müssen keine Cluster einrichten und verwalten.

Ein BigQuery-Dataset befindet sich in einem GCP-Projekt und enthält eine oder mehrere Tabellen. Sie können diese Datasets mit SQL abfragen.

In diesem Codelab nutzen Sie die BigQuery-Web-UI in der GCP Console, um sich mit der Partitionierung und Clustering in BigQuery vertraut zu machen. Mit der Tabellenpartitionierung und dem Clustering von BigQuery können Sie Ihre Daten so strukturieren, dass sie gängigen Datenzugriffsmustern entsprechen. Partition und Clustering sind entscheidend, um die Leistung und Kosten von BigQuery bei Abfragen über einen bestimmten Datenbereich vollständig zu maximieren. Dabei werden weniger Daten pro Abfrage gescannt und der Pruning wird vor dem Beginn der Abfrage festgelegt.

Hinweis: Neben der BigQuery-Web-UI können Sie das bq-Befehlszeilentool verwenden, um Vorgänge für BigQuery-Datasets auszuführen. Weitere Informationen zum bq-Befehlszeilentool finden Sie unter bq-Befehlszeilentool.

Weitere Informationen zu BigQuery finden Sie in der BigQuery-Dokumentation.

Lerninhalte

• Partitionierte und geclusterte Tabellen erstellen und abfragen
• Abfrageleistung mit partitionierten und geclusterten Tabellen vergleichen

Voraussetzungen

Für dieses Lab benötigen Sie Folgendes:

• Die neueste Version von Google Chrome
• Ein Google Cloud Platform-Abrechnungskonto

2. Einrichtung

Wenn Sie mit BigQuery arbeiten möchten, müssen Sie ein GCP-Projekt erstellen oder ein vorhandenes Projekt auswählen.

Projekt erstellen

So erstellen Sie ein neues Projekt:

1. Wenn Sie noch kein Google-Konto (Gmail oder Google Apps) haben, erstellen Sie eines.
2. Melden Sie sich in der Google Cloud Platform Console ( console.cloud.google.com) an und erstellen Sie ein neues Projekt.
3. Wenn Sie keine Projekte haben, klicken Sie auf die Schaltfläche „Projekt erstellen“:

Andernfalls erstellen Sie über das Projektauswahlmenü ein neues Projekt:

4. Geben Sie einen Projektnamen ein und wählen Sie Erstellen aus. Beachten Sie, dass die Projekt-ID ein eindeutiger Name in allen Google Cloud-Projekten ist.

3. Mit öffentlichen Datasets arbeiten

Mit BigQuery können Sie mit öffentlichen Datasets arbeiten, darunter von BBC News, GitHub-Repositories, Stack Overflow und den Datasets der National Oceanic and Atmospheric Administration (NOAA). Sie müssen diese Datasets nicht in BigQuery laden. Sie müssen nur die Datasets öffnen, um sie in BigQuery zu durchsuchen und abzufragen. In diesem Codelab arbeiten Sie mit dem öffentlichen Stack Overflow-Dataset.

Stack Overflow-Dataset ansehen

Das Stack Overflow-Dataset enthält Informationen zu Beiträgen, Tags, Badges, Kommentaren, Nutzern und mehr. So durchsuchen Sie das Stack Overflow-Dataset in der BigQuery-Web-UI:

1. Öffnen Sie das Stack Overflow-Dataset. Die BigQuery-Web-UI wird in der GCP Console geöffnet und zeigt Informationen zum Stack Overflow-Dataset an.
2. Wählen Sie im Navigationsbereich bigquery-public-data aus. Das Menü wird erweitert, um öffentliche Datasets aufzulisten. Jedes Dataset umfasst eine oder mehrere Tabellen.
3. Scrollen Sie nach unten und wählen Sie stackoverflow aus. Das Menü wird erweitert und eine Liste der Tabellen im Stack Overflow-Dataset wird angezeigt.
4. Wählen Sie Logos aus, um das Schema für die Badge-Tabelle zu sehen. Notieren Sie sich die Namen der Felder in der Tabelle.
5. Klicken Sie über den Feldnamen auf Vorschau, um Beispieldaten für die Badge-Tabelle zu sehen.

Weitere Informationen zu allen in BigQuery verfügbaren öffentlichen Datasets finden Sie unter Öffentliche Google BigQuery-Datasets.

Stack Overflow-Dataset abfragen

Durch das Durchsuchen eines Datasets können Sie die Daten, mit denen Sie arbeiten, besser verstehen. BigQuery brilliert jedoch durch das Abfragen von Datasets. In diesem Abschnitt erfahren Sie, wie Sie BigQuery-Abfragen ausführen. Sie müssen zu diesem Zeitpunkt keine SQL-Kenntnisse haben. Sie können die Abfragen unten kopieren und einfügen.

So führen Sie eine Abfrage aus:

1. Wählen Sie rechts oben in der GCP Console Neue Abfrage erstellen aus.
2. Kopieren Sie die folgende SQL-Abfrage und fügen Sie sie in den Textbereich des Abfrageeditors ein. BigQuery validiert die Abfrage und in der Web-UI wird unter dem Textbereich ein grünes Häkchen angezeigt, um anzugeben, dass die Syntax gültig ist.

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

3. Wählen Sie Ausführen aus. Die Abfrage gibt die Anzahl der Stack Overflow-Beiträge oder -Fragen zurück, die pro Jahr veröffentlicht werden.

Hinweis:In dieser Abfrage wird der vollständige Tabellenname der Tabelle „posts_questions“ verwendet, einschließlich Projektname, Dataset-Name und Tabellenname: bigquery-public-data.stackoverflow.posts_questions.

4. Neue Tabelle erstellen

Im vorherigen Abschnitt haben Sie öffentliche Datasets abgefragt, die Ihnen BigQuery zur Verfügung stellt. In diesem Abschnitt erstellen Sie eine neue Tabelle in BigQuery aus einer vorhandenen Tabelle. Sie erstellen eine neue Tabelle mit Stichprobendaten aus der Tabelle posts_questions des öffentlichen Stack Overflow-Datasets und fragen dann die Tabelle ab.

Neues Dataset erstellen

Um Tabellendaten zu erstellen und in BigQuery zu laden, erstellen Sie zuerst ein BigQuery-Dataset für die Daten. Führen Sie dazu die folgenden Schritte aus:

1. Wählen Sie im Navigationsbereich der GCP Console den Projektnamen aus, der im Rahmen der Einrichtung erstellt wurde.
2. Wählen Sie rechts im Detailbereich die Option Dataset erstellen aus.

3. Geben Sie im Dialogfeld Dataset erstellen als Dataset-ID den Wert stackoverflow ein. Lassen Sie alle anderen Standardeinstellungen unverändert und klicken Sie auf OK.

Neue Tabelle mit StackOverflow-Beiträgen 2018 erstellen

Nachdem Sie nun ein BigQuery-Dataset erstellt haben, können Sie eine neue Tabelle in BigQuery erstellen. Um eine Tabelle mit Daten aus einer vorhandenen Tabelle zu erstellen, fragen Sie das Dataset der Stack Overflow-Beiträge von 2018 ab und schreiben die Ergebnisse in eine neue Tabelle. Gehen Sie dazu so vor:

1. Wählen Sie rechts oben in der GCP Console Neue Abfrage erstellen aus.

2. Kopieren Sie die folgende SQL-Abfrage und fügen Sie sie im Textbereich des Abfrageeditors ein, um eine neue Tabelle zu erstellen. Das ist eine DDL-Anweisung.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Wählen Sie Ausführen aus. Mit der Abfrage wird die neue Tabelle questions_2018 im Dataset stackoverflow in Ihrem Projekt mit Daten erstellt, die sich aus einer Abfrage im BigQuery-Stack Overflow-Dataset bigquery-public-data.stackoverflow.posts_questions ergeben.

Hinweis:Dieser Schritt dauert etwa 2 Minuten, um eine neue Tabelle zu erstellen. Bitte warten Sie, bevor Sie mit dem nächsten Schritt fortfahren.

Abfrage der neuen Tabelle mit Stack Overflow-Beiträgen von 2018

Nachdem Sie nun eine BigQuery-Tabelle erstellt haben, können Sie eine Abfrage ausführen, um Stack Overflow-Beiträge mit Fragen und Titeln sowie einige andere Statistiken wie die Anzahl der Antworten, Kommentare, Aufrufe und Favoriten zurückzugeben. Gehen Sie folgendermaßen vor:

1. Wählen Sie rechts oben in der GCP Console Neue Abfrage erstellen aus.
2. Kopieren Sie die folgende SQL-Abfrage und fügen Sie sie in den Textbereich des Abfrageeditors ein.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Wählen Sie Ausführen aus. Die Abfrage gibt zusammen mit der Frage und einigen anderen Statistiken Stack Overflow-Fragen zurück, die im Januar 2018 erstellt und als android gekennzeichnet sind.
4. Standardmäßig speichert BigQuery die Abfrageergebnisse im Cache. Führen Sie dieselbe Abfrage aus. Sie werden feststellen, dass die Rückgabe der Ergebnisse durch BigQuery viel weniger Zeit in Anspruch nimmt, da Ergebnisse aus dem Cache zurückgegeben werden.
5. Führen Sie dieselbe Abfrage noch einmal aus, aber dieses Mal mit deaktiviertem BigQuery-Caching. Für den Rest des Labs deaktivieren wir den Cache, um einen fairen Leistungsvergleich mit partitionierten und geclusterten Tabellen zu gewährleisten. Dies wird in den nächsten Abschnitten beschrieben. Klicken Sie im Abfrageeditor auf More (Mehr) und wählen Sie Query settings (Abfrageeinstellungen) aus.
6. Entfernen Sie unter Cache-Einstellung das Häkchen bei Im Cache gespeicherte Ergebnisse verwenden.
7. In den Abfrageergebnissen sollten Sie die Zeit, die für den Abschluss der Abfrage benötigt wurde, und die Menge der verarbeiteten Daten sehen, um die Ergebnisse zu erhalten.

5. Partitionierte Tabellen erstellen und abfragen

Im vorherigen Abschnitt haben Sie mit dem öffentlichen Stack Overflow-Dataset eine neue Tabelle in BigQuery mit Daten aus der Tabelle posts_questions erstellt. Wir haben dieses Dataset mit deaktiviertem Caching abgefragt und die Abfrageleistung beobachtet. In diesem Abschnitt erstellen Sie eine neue partitionierte Tabelle aus der posts_questions-Tabelle desselben öffentlichen Stack Overflow-Datasets und beobachten die Abfrageleistung.

Eine partitionierte Tabelle ist eine spezielle Tabelle, die in Segmente, sogenannte Partitionen, unterteilt ist, um die Verwaltung und Abfrage Ihrer Daten zu vereinfachen. Sie können große Tabellen in der Regel mithilfe der Datenaufnahmezeit- oder der TIMESTAMP/DATE- oder einer INTEGER-Spalte in viele kleinere Partitionen aufteilen. Es wird eine nach DATE partitionierte Tabelle erstellt.

Weitere Informationen zu partitionierten Tabellen

Eine neue partitionierte Tabelle mit StackOverflow-Beiträgen aus dem Jahr 2018 erstellen

Um eine partitionierte Tabelle mit Daten aus einer vorhandenen Tabelle oder Abfrage zu erstellen, fragen Sie das Dataset der Stack Overflow-Beiträge von 2018 ab und schreiben die Ergebnisse in eine neue Tabelle. Führen Sie dazu die folgenden Schritte aus:

1. Wählen Sie rechts oben in der GCP Console Neue Abfrage erstellen aus.

2. Kopieren Sie die folgende SQL-Abfrage und fügen Sie sie im Textbereich des Abfrageeditors ein, um eine neue Tabelle zu erstellen. Das ist eine DDL-Anweisung.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Wählen Sie Ausführen aus. Mit der Abfrage wird die neue Tabelle questions_2018_partitioned im Dataset stackoverflow in Ihrem Projekt mit Daten erstellt, die sich aus einer Abfrage im BigQuery-Stack Overflow-Dataset bigquery-public-data.stackoverflow.posts_questions ergeben

Hinweis:Dieser Schritt dauert etwa 2 Minuten, um eine neue Tabelle zu erstellen. Bitte warten Sie, bevor Sie mit dem nächsten Schritt fortfahren.

Partitionierte Tabelle mit Stack Overflow-Beiträgen von 2018 abfragen

Nachdem Sie eine nach BigQuery partitionierte Tabelle erstellt haben, führen wir dieselbe Abfrage aus, diesmal für die partitionierte Tabelle, um Stack Overflow-Beiträge mit Fragen und Titeln sowie einige andere Statistiken wie die Anzahl der Antworten, Kommentare, Aufrufe und Favoriten zurückzugeben. Gehen Sie folgendermaßen vor:

1. Wählen Sie rechts oben in der GCP Console Neue Abfrage erstellen aus.
2. Kopieren Sie die folgende SQL-Abfrage und fügen Sie sie in den Textbereich des Abfrageeditors ein.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Wählen Sie Run mit deaktiviertem BigQuery-Caching aus. Informationen zum Deaktivieren des BigQuery-Cache finden Sie im vorherigen Abschnitt. Die Abfrage gibt zusammen mit der Frage und einigen anderen Statistiken Stack Overflow-Fragen zurück, die im Januar 2018 erstellt und als android gekennzeichnet sind.
4. In den Abfrageergebnissen sollten Sie die Zeit, die für den Abschluss der Abfrage benötigt wurde, und die Menge der verarbeiteten Daten sehen, um die Ergebnisse zu erhalten.

Sie sollten sehen, dass die Abfrage mit einer partitionierten Tabelle besser ist als bei der nicht partitionierten Tabelle, da BigQuery die Partitionen bereinigt, d.h. nur die erforderlichen Partitionen durchsucht, die weniger Daten verarbeiten und schneller ausgeführt werden. Dadurch werden die Abfragekosten und die Abfrageleistung optimiert.

6. Geclusterte Tabellen erstellen und abfragen

Im vorherigen Abschnitt haben Sie eine partitionierte Tabelle in BigQuery mit Daten aus der Tabelle posts_questions im öffentlichen Stack Overflow-Dataset erstellt. Wir haben diese Tabelle mit deaktiviertem Caching abgefragt und die Abfrageleistung sowohl bei nicht partitionierten als auch mit partitionierten Tabellen beobachtet. In diesem Abschnitt erstellen Sie eine neue geclusterte Tabelle aus der posts_questions-Tabelle desselben öffentlichen Stack Overflow-Datasets und beobachten die Abfrageleistung.

Wenn eine Tabelle in BigQuery geclustert ist, werden die Tabellendaten automatisch basierend auf dem Inhalt einer oder mehrerer Spalten im Tabellenschema organisiert. In den von Ihnen angegebenen Spalten werden verwandte Daten zusammengestellt. Wenn Daten in eine geclusterte Tabelle geschrieben werden, sortiert BigQuery die Daten anhand der Werte in den Clustering-Spalten. Mithilfe dieser Werte werden die Daten in mehreren Blöcken im BigQuery-Speicher organisiert. Die Reihenfolge der geclusterten Spalten bestimmt die Sortierreihenfolge der Daten. Wenn einer Tabelle oder einer bestimmten Partition neue Daten hinzugefügt werden, führt BigQuery ein automatisches Re-Clustering im Hintergrund durch, um die Sortiereigenschaft der Tabelle oder Partition wiederherzustellen.

Weitere Informationen zum Arbeiten mit geclusterten Tabellen

Neue geclusterte Tabelle mit Stack Overflow-Beiträgen aus dem Jahr 2018 erstellen

In diesem Abschnitt erstellen Sie eine neue Tabelle, die nach creation_date partitioniert und basierend auf dem Abfragezugriffsmuster in der Spalte tags geclustert wird. Um eine geclusterte Tabelle mit Daten aus einer vorhandenen Tabelle oder Abfrage zu erstellen, fragen Sie die Stack Overflow-Beitragstabelle von 2018 ab und schreiben die Ergebnisse in eine neue Tabelle. Gehen Sie dazu so vor:

1. Wählen Sie rechts oben in der GCP Console Neue Abfrage erstellen aus.

2. Kopieren Sie die folgende SQL-Abfrage und fügen Sie sie im Textbereich des Abfrageeditors ein, um eine neue Tabelle zu erstellen. Das ist eine DDL-Anweisung.

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Wählen Sie „Ausführen“ aus. Mit der Abfrage wird die neue Tabelle questions_2018_clustered im Dataset stackoverflow Ihres Projekts mit Daten erstellt, die sich aus einer Abfrage in der BigQuery-Stack Overflow-Tabellebigquery-public-data.stackoverflow.posts_questions ergeben. Die neue Tabelle wird nach dem Erstellungsdatum partitioniert und in der Spalte Tags geclustert.

Hinweis:Dieser Schritt dauert etwa 2 Minuten, um eine neue Tabelle zu erstellen. Bitte warten Sie, bevor Sie mit dem nächsten Schritt fortfahren.

Geclusterte Tabelle mit Stack Overflow-Beiträgen von 2018 abfragen

Nachdem Sie eine geclusterte BigQuery-Tabelle erstellt haben, führen wir dieselbe Abfrage noch einmal aus, diesmal für die partitionierte und geclusterte Tabelle, um Stack Overflow-Beiträge mit Fragen und Titeln sowie einige andere Statistiken wie die Anzahl der Antworten, Kommentare, Aufrufe und Favoriten zurückzugeben. Gehen Sie folgendermaßen vor:

1. Wählen Sie rechts oben in der GCP Console Neue Abfrage erstellen aus.
2. Kopieren Sie die folgende SQL-Abfrage und fügen Sie sie in den Textbereich des Abfrageeditors ein.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Wählen Sie Run mit deaktiviertem BigQuery-Caching aus. Informationen zum Deaktivieren des BigQuery-Cache finden Sie im vorherigen Abschnitt. Die Abfrage gibt zusammen mit der Frage und einigen anderen Statistiken Stack Overflow-Fragen zurück, die im Januar 2018 erstellt und als android gekennzeichnet sind.
4. In den Abfrageergebnissen sollten Sie die Zeit, die für den Abschluss der Abfrage benötigt wurde, und die Menge der verarbeiteten Daten sehen, um die Ergebnisse zu erhalten.

Bei einer partitionierten und geclusterten Tabelle wurden durch die Abfrage weniger Daten gescannt als bei einer partitionierten und einer nicht partitionierten Tabelle. Durch die Art und Weise, wie Daten durch Partitionierung und Clustering organisiert werden, wird die Menge der von Slot-Worker gescannten Daten minimiert, wodurch die Abfrageleistung verbessert und die Kosten optimiert werden.

7. Bereinigen

Wenn Sie nicht vorhaben, weiter mit Ihrem Stack Overflow-Dataset zu arbeiten, sollten Sie es und das Projekt löschen, das Sie für dieses Codelab erstellt haben.

BigQuery-Dataset löschen

Führen Sie die folgenden Schritte aus, um das BigQuery-Dataset zu löschen:

1. Wählen Sie in BigQuery im linken Navigationsbereich das Dataset stackoverflow aus .
2. Wählen Sie im Detailbereich die Option Dataset löschen aus.
3. Geben Sie im Dialogfeld Delete dataset (Dataset löschen) stackoverflow ein und wählen Sie Delete (Löschen) aus, um zu bestätigen, dass Sie das Dataset löschen möchten.

Projekt löschen

Führen Sie die folgenden Schritte aus, um das GCP-Projekt zu löschen, das Sie für dieses Codelab erstellt haben:

1. Wählen Sie im GCP-Navigationsmenü die Option IAM & Verwaltung.
2. Wählen Sie im Navigationsbereich Einstellungen aus.
3. Prüfen Sie im Detailbereich, ob Ihr aktuelles Projekt das Projekt ist, das Sie für dieses Codelab erstellt haben, und wählen Sie Beenden aus.
4. Geben Sie im Dialogfeld Projekt beenden die Projekt-ID (nicht den Projektnamen) für Ihr Projekt ein und wählen Sie zur Bestätigung Beenden aus.

Glückwunsch! Sie haben nun gelernt,

• BigQuery-Web-UI verwenden, um eine neue Tabelle aus vorhandenen Tabellen zu erstellen
• Partitionierte und geclusterte Tabellen erstellen und abfragen
• Optimierung von Abfrageleistung und -kosten durch Partitionierung und Clustering

Beachten Sie, dass Sie keine Cluster einrichten oder verwalten mussten, um mit Datasets zu arbeiten.