Wikipedia-Dataset in BigQuery abfragen

1. Übersicht

BigQuery ist die vollständig verwaltete, kostengünstige NoOps-Analysedatenbank von Google. Mit BigQuery können Sie Terabyte und Terabyte an Daten abfragen, ohne eine Infrastruktur verwalten zu müssen und keinen Datenbankadministrator benötigen. BigQuery nutzt vertrautes SQL und bietet die Vorteile eines „Pay as you go“-Modells. 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
• So laden Sie reale Datasets in BigQuery
• Wie Sie eine Abfrage schreiben, um Einblick in ein großes Dataset zu erhalten

Voraussetzungen

• Ein Google Cloud-Projekt
• Ein Browser wie Google Chrome oder Firefox

Umfrage

Wie möchten Sie diese Anleitung nutzen?

<ph type="x-smartling-placeholder"></ph> Schreibgeschützt Lies sie dir durch und absolviere die Übungen

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

<ph type="x-smartling-placeholder"></ph> Neuling Mittel Kompetent

2. Einrichtung und Anforderungen

BigQuery aktivieren

Falls Sie noch kein Google-Konto haben, müssen Sie ein Konto 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 in diesem Codelab später 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-Limits später entfernen möchten, können Sie ein Rechnungskonto hinzufügen, indem Sie sich für den kostenlosen Testzeitraum von Google Cloud registrieren.

Das Wikipedia-Dataset wird im nächsten Abschnitt geladen.

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 Ressourcenbereich 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 Cloud Shell noch nie gestartet haben, wird ein Zwischenbildschirm (below the fold) angezeigt, in dem beschrieben wird, worum es sich dabei handelt. Klicken Sie in diesem Fall auf Weiter. Der Chat wird nie wieder angezeigt. So sieht dieser einmalige Bildschirm aus:

Die Bereitstellung und Verbindung mit Cloud Shell dauert nur einen Moment.

Diese virtuelle Maschine verfügt über alle Entwicklungstools, die Sie benötigen. Es bietet ein Basisverzeichnis mit 5 GB nichtflüchtigem Speicher und wird in Google Cloud ausgeführt. Dadurch werden die Netzwerkleistung und die Authentifizierung erheblich verbessert. Viele, wenn nicht sogar alle Arbeiten in diesem Codelab können Sie ganz einfach mit einem Browser oder Ihrem Chromebook erledigen.

Sobald Sie mit Cloud Shell verbunden sind, sollten Sie sehen, dass Sie bereits authentifiziert sind und dass das Projekt bereits auf Ihre Projekt-ID eingestellt ist.

2. Führen Sie in 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 einheitliches Befehlszeilentool in Google Cloud. Es ist in Cloud Shell vorinstalliert. Sie werden feststellen, dass die Tab-Vervollständigung unterstützt wird. Weitere Informationen finden Sie in der Übersicht zum gcloud-Befehlszeilentool.

3. Führen Sie in Cloud Shell den folgenden Befehl aus, um zu prüfen, ob 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 für den 10. April 2019 aus dem Wikimedia-Seitenaufruf-Dataset sind in Google Cloud Storage unter gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz verfügbar. Die Datendatei ist eine GZip-CSV-Datei. Sie können diese Datei direkt mit dem Befehlszeilendienstprogramm bq laden. Als Teil des Ladebefehls beschreiben Sie auch das Schema der Datei.

Sie finden die Projekt-ID in der API-Konsole oder über die Befehlszeile: „gcloud config list“ und suchen Sie nach dem Wert von „project“.

Wenn Sie bq zum ersten Mal ausführen, werden Sie möglicherweise aufgefordert, ein Standardprojekt auszuwählen. Anschließend können Sie den obigen 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 verwendet, um die Datei mit den Seitenaufrufen zu laden:

• 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 ein einzelnes Leerzeichen zum Trennen von Feldern verwendet wird.
• 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 ohne Anführungszeichen angegeben werden.
• Legen Sie für --max_bad_records 3 fest, dass beim Parsen der CSV-Datei höchstens drei Fehler ignoriert werden sollen.

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 wird auf dem Tab Schema angezeigt. 4. Um herauszufinden, wie viele Daten sich in der Tabelle befinden, navigieren Sie zum Tab Details:

5. Öffnen Sie den Tab Vorschau, um eine Auswahl der Tabellenzeilen zu sehen.

6. Abfrage erstellen

1. Klicken Sie auf Compose new query (Neue Abfrage erstellen):

Daraufhin wird der Abfrageeditor geöffnet:

2. Ermitteln Sie die Gesamtzahl der Wikimedia-Aufrufe zwischen 14 und 15 Uhr am 10. April 2019, indem Sie diese Abfrage schreiben:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Klicken Sie auf Ausführen:

Nach wenigen Sekunden wird das Ergebnis unten angezeigt und Sie erfahren, wie viele Daten verarbeitet wurden:

Diese Abfrage hat 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. Daher kann die verarbeitete Gesamtdatenmenge deutlich kleiner als die Tabellengröße sein. Durch Clustering und Partitionierung kann die verarbeitete Datenmenge noch weiter reduziert werden.

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

7. Erweiterte Abfragen

Aufrufe von Wikipedia-Seiten suchen

Das Wikimedia-Dataset enthält Seitenaufrufe für alle Wikimedia-Projekte, einschließlich Wikipedia, Wiktionary, Wikibooks und Wikiquotes. Grenzen 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

Beachten Sie, dass sich durch die Abfrage der zusätzlichen Spalte wiki die verarbeitete Datenmenge von 124 MB auf 204 MB erhöht hat.

BigQuery unterstützt viele der bekannten SQL-Klauseln wie CONTAINS, GROUP BY,, ORDER BY und eine Reihe von Aggregationsfunktionen. Darüber hinaus können Sie auch reguläre Ausdrücke verwenden, um Textfelder abzufragen. Versuchen Sie es mit einer der folgenden Methoden:

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 Referenzdokumentation zu BigQuery-Abfragen.

Mehrere Tabellen gleichzeitig abfragen

Sie können einen Tabellenbereich auswählen, um mithilfe einer Platzhaltertabelle den Union-Vorgang zu erstellen.

1. Erstellen Sie zuerst eine zweite Tabelle, die abgefragt werden soll. Laden Sie dazu die Seitenaufrufe der nächsten Stunde in eine neue Tabelle:

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 Tabellen aus, die Sie geladen haben. Fragen Sie dazu Tabellen mit „pageviews_2019“ ab. als Präfix:

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 löschen, das Sie mit dem Befehl bq rm erstellt haben. 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 zu Seitenaufrufen abzufragen. Sie haben die Möglichkeit, Datasets im Petabyte-Bereich abzufragen!

Weitere Informationen

• Sehen Sie sich das BigQuery-Subreddit an, um zu erfahren, wie andere Nutzer BigQuery verwenden.
• Suchen Sie nach öffentlichen Datasets, die in BigQuery verfügbar sind.
• Daten in BigQuery laden