Wikipedia-Dataset in BigQuery abfragen

1. Übersicht

BigQuery ist eine vollständig verwaltete, managementfreie und kostengünstige Analysedatenbank von Google. Mit diesem Tool können Sie mehrere Terabyte an Daten abfragen und müssen dabei weder eine Infrastruktur verwalten noch benötigen Sie einen Datenbankadministrator. BigQuery basiert auf dem vertrauten SQL und die Abrechnung erfolgt nach dem „Pay as you go“-Modell. Mithilfe von BigQuery können Sie sich auf die Datenanalyse konzentrieren und wichtige Informationen erhalten.

In diesem Codelab untersuchen Sie das Wikipedia-Dataset mit BigQuery.

Lerninhalte

• BigQuery verwenden
• Einen realen Datensatz in BigQuery laden
• Abfrage schreiben, um Erkenntnisse aus einem großen Dataset zu gewinnen

Voraussetzungen

• Ein Google Cloud-Projekt
• Ein Browser, z. B. Google Chrome oder Firefox

Umfrage

Wie werden Sie diese Anleitung verwenden?

Nur lesen Lesen und Übungen durchführen

Wie würden Sie Ihre Erfahrung mit Google Cloud bewerten?

Anfänger Mittelstufe Fortgeschritten

2. Einrichtung und Anforderungen

BigQuery aktivieren

Wenn Sie noch kein Google-Konto haben, müssen Sie eines erstellen.

1. Melden Sie sich in der Google Cloud Console an und rufen Sie BigQuery auf. Sie können die BigQuery-Web-UI auch direkt öffnen, indem Sie die folgende URL in Ihren Browser eingeben.

https://console.cloud.google.com/bigquery

2. Akzeptieren Sie die Nutzungsbedingungen.
3. Bevor Sie BigQuery verwenden können, müssen Sie ein Projekt erstellen. Folgen Sie dazu den Eingabeaufforderungen.

Wählen Sie einen Projektnamen aus und notieren Sie sich die Projekt-ID.

Die Projekt-ID ist ein Name, der übergreifend über alle Google Cloud-Projekte nur einmal vergeben wird. Sie wird später in diesem Codelab als PROJECT_ID bezeichnet.

In diesem Codelab werden BigQuery-Ressourcen innerhalb der BigQuery-Sandbox-Limits verwendet. Ein Rechnungskonto ist nicht erforderlich. Wenn Sie die Sandbox-Beschränkungen später entfernen möchten, können Sie ein Rechnungskonto hinzufügen, indem Sie sich für den kostenlosen Testzeitraum von Google Cloud registrieren.

Im nächsten Abschnitt laden Sie das Wikipedia-Dataset.

3. Dataset erstellen

Erstellen Sie zuerst ein neues Dataset im Projekt. Ein Dataset besteht aus mehreren Tabellen.

1. Klicken Sie zum Erstellen eines Datasets im Bereich „Ressourcen“ auf den Projektnamen und dann auf Dataset erstellen:

2. Geben Sie lab als Dataset-ID ein:

3. Klicken Sie auf Dataset erstellen, um ein leeres Dataset zu erstellen.

4. Daten mit dem bq-Befehlszeilenprogramm laden

Cloud Shell aktivieren

1. Klicken Sie in der Cloud Console auf Cloud Shell aktivieren .

Wenn Sie die Cloud Shell zuvor noch nicht gestartet haben, wird ein Fenster mit einer Beschreibung eingeblendet. Klicken Sie in diesem Fall einfach auf Weiter. So sieht dieses Fenster aus:

Das Herstellen der Verbindung mit der Cloud Shell sollte nur wenige Augenblicke dauern.

Auf dieser virtuellen Maschine sind alle Entwicklungstools installiert, die Sie benötigen. Sie bietet ein Basisverzeichnis mit 5 GB nichtflüchtigem Speicher und läuft in Google Cloud, was die Netzwerkleistung und Authentifizierung erheblich verbessert. Die meisten, wenn nicht sogar alle Aufgaben in diesem Codelab können mit einem Browser oder Ihrem Chromebook erledigt werden.

Sobald die Verbindung mit der Cloud Shell hergestellt ist, sehen Sie, dass Sie bereits authentifiziert sind und für das Projekt schon Ihre Projekt-ID eingestellt ist.

2. Führen Sie in der Cloud Shell den folgenden Befehl aus, um zu prüfen, ob Sie authentifiziert sind:

gcloud auth list

Befehlsausgabe

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Hinweis:Das gcloud-Befehlszeilentool ist ein leistungsstarkes und vereinheitlichtes Befehlszeilentool in Google Cloud. Das Tool ist in der Cloud Shell vorinstalliert. Sie werden feststellen, dass die Tab-Vervollständigung unterstützt wird. Weitere Informationen finden Sie in der Übersicht über das gcloud-Befehlszeilentool.

3. Führen Sie den folgenden Befehl in Cloud Shell aus, um zu bestätigen, dass der gcloud-Befehl Ihr Projekt kennt:

gcloud config list project

Befehlsausgabe

[core]
project = <PROJECT_ID>

Ist dies nicht der Fall, können Sie die Einstellung mit diesem Befehl vornehmen:

gcloud config set project <PROJECT_ID>

Befehlsausgabe

Updated property [core/project].

Daten in BigQuery laden

Einige der Daten vom 10. April 2019 aus dem Wikimedia-Dataset für Seitenaufrufe sind zur besseren Übersicht in Google Cloud Storage unter gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz verfügbar. Die Datendatei ist eine mit GZip komprimierte CSV-Datei. Sie können diese Datei direkt mit dem Befehlszeilentool bq laden. Im Rahmen des Ladebefehls beschreiben Sie auch das Schema der Datei.

Sie finden die Projekt-ID in der API Console oder in der Befehlszeile: „gcloud config list“. Suchen Sie nach dem Wert von „project“.

Wenn Sie „bq“ zum ersten Mal ausführen, werden Sie möglicherweise aufgefordert, ein Standardprojekt auszuwählen. Nachdem Sie die Datenquelle ausgewählt haben, können Sie den oben genannten Befehl noch einmal ausführen, um die Daten zu laden.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

Das Laden der Daten kann einige Minuten dauern. Warten Sie, bis der Vorgang abgeschlossen ist.

Sie haben einige erweiterte Optionen zum Laden der Datei mit Seitenaufrufen verwendet:

• Legen Sie --source_format CSV fest, um anzugeben, dass die Datei als CSV-Datei geparst werden soll. Dieser Schritt ist optional, da CSV das Standardformat ist.
• Legen Sie --field_delimiter " " fest, um anzugeben, dass Felder durch ein einzelnes Leerzeichen getrennt werden.
• Legen Sie --allow_jagged_rows fest, um die Zeilen mit weniger Spalten einzuschließen und die Fehler beim Laden der CSV-Datei zu ignorieren.
• Legen Sie --quote "" fest, um anzugeben, dass Strings nicht in Anführungszeichen stehen.
• Legen Sie --max_bad_records 3 fest, um beim Parsen der CSV-Datei maximal drei Fehler zu ignorieren.

Weitere Informationen zur bq-Befehlszeile finden Sie in der Dokumentation.

5. Dataset als Vorschau ansehen

Öffnen Sie in der BigQuery-Konsole eine der Tabellen, die Sie gerade geladen haben.

1. Maximieren Sie das Projekt.
2. Maximieren Sie das Dataset.
3. Wählen Sie die Tabelle aus.

Das Tabellenschema finden Sie auf dem Tab Schema. 4. So sehen Sie, wie viele Daten in der Tabelle enthalten sind:

5. Öffnen Sie den Tab Vorschau, um eine Auswahl von Zeilen aus der Tabelle zu sehen.

6. Abfrage erstellen

1. Klicken Sie auf Neue Abfrage erstellen:

Dadurch wird der Abfrageeditor aufgerufen:

2. Mit der folgenden Abfrage können Sie die Gesamtzahl der Wikimedia-Aufrufe zwischen 14:00 und 15:00 Uhr am 10. April 2019 ermitteln:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Klicken Sie auf Ausführen:

Nach einigen Sekunden wird das Ergebnis unten aufgeführt. Außerdem wird angezeigt, wie viele Daten verarbeitet wurden:

Bei dieser Abfrage wurden 123,9 MB verarbeitet, obwohl die Tabelle 691,4 MB groß ist. BigQuery verarbeitet nur die Byte aus den Spalten, die in der Abfrage verwendet werden. Die insgesamt verarbeitete Datenmenge kann daher deutlich geringer sein als die Tabellengröße. Mit Clustering und Partitionierung lässt sich die verarbeitete Datenmenge noch weiter reduzieren.

Sie können jeden Monat kostenlos bis zu 1 TB an Daten verarbeiten/abfragen. Weitere Informationen finden Sie auf der BigQuery-Preisseite.

7. Erweiterte Suchanfragen

Wikipedia-Seitenaufrufe finden

Das Wikimedia-Dataset enthält Seitenaufrufe für alle Wikimedia-Projekte (einschließlich Wikipedia, Wiktionary, Wikibooks und Wikiquote). Schränken Sie die Abfrage auf englischsprachige Wikipedia-Seiten ein, indem Sie eine WHERE-Anweisung hinzufügen:

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

Durch das Abfragen einer zusätzlichen Spalte, wiki, hat sich die Menge der verarbeiteten Daten von 124 MB auf 204 MB erhöht.

BigQuery unterstützt viele der bekannten SQL-Klauseln wie CONTAINS, GROUP BY, und ORDER BY sowie eine Reihe von Aggregationsfunktionen. Außerdem können Sie auch reguläre Ausdrücke verwenden, um Textfelder abzufragen. Hier ein paar Beispiele:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Weitere Informationen zur BigQuery-Syntax finden Sie in der BigQuery-Abfragereferenz.

Mehrere Tabellen abfragen

Sie können einen Bereich von Tabellen auswählen, die mit einer Platzhaltertabelle zusammengeführt werden sollen.

1. Erstellen Sie zuerst eine zweite Tabelle, die Sie abfragen können, indem Sie die Seitenaufrufe der nächsten Stunde in eine neue Tabelle laden:

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. Führen Sie im Abfrageeditor eine Abfrage für beide geladenen Tabellen aus, indem Sie Tabellen mit dem Präfix „pageviews_2019“ abfragen:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Sie können die Tabellen mit der Pseudospalte _TABLE_SUFFIX selektiver filtern. Diese Abfrage ist auf Tabellen beschränkt, die dem 10. April entsprechen.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. Bereinigen

Optional können Sie das Dataset, das Sie erstellt haben, mit dem Befehl bq rm löschen. Verwenden Sie das Flag -r, um alle darin enthaltenen Tabellen zu entfernen.

bq rm -r lab

9. Glückwunsch!

Sie haben BigQuery und SQL verwendet, um das reale Wikipedia-Dataset mit Seitenaufrufen abzufragen. Sie können Datasets im Petabyte-Bereich abfragen.

Weitere Informationen

• Im BigQuery-Subreddit erfahren Sie, wie andere BigQuery heute verwenden.
• Öffentliche Datasets in BigQuery
• Weitere Informationen zum Laden von Daten in BigQuery