BigQuery mit Python verwenden

1. Übersicht

BigQuery ist das vollständig verwaltete, kostengünstige Data Warehouse von Google für Analysen im Petabyte-Bereich. BigQuery ist ein NoOps-Angebot. Es muss keine Infrastruktur verwaltet werden. Da Sie keinen Datenbankadministrator benötigen, können Sie sich ganz auf die Analyse der Daten konzentrieren und über vertraute SQL-Befehle aussagekräftige Informationen erhalten. Dabei profitieren Sie von unserem Pay-As-You-Go-Modell.

In diesem Codelab verwenden Sie die Google Cloud-Clientbibliotheken für Python, um öffentliche BigQuery-Datasets mit Python abzufragen.

Lerninhalte

Cloud Shell verwenden
BigQuery API aktivieren
API-Anfragen authentifizieren
Python-Clientbibliothek installieren
Werke von Shakespeare abfragen
GitHub-Dataset abfragen
Caching- und Statistikanzeige anpassen

Voraussetzungen

Google Cloud-Projekt
Ein Browser wie Google Chrome oder Mozilla Firefox
Python-Vorkenntnisse

Umfrage

Wie werden Sie diese Anleitung verwenden?

Nur lesen

Lesen und Übungen durchführen

Wie würden Sie Ihre Erfahrung mit Python bewerten?

Anfänger

Mittelstufe

Fortgeschrittene

Wie würden Sie Ihre Erfahrungen mit Google Cloud-Diensten bewerten?

Anfänger

Mittelstufe

Fortgeschritten

2. Einrichtung und Anforderungen

Umgebung zum selbstbestimmten Lernen einrichten

Melden Sie sich in der Google Cloud Console an und erstellen Sie ein neues Projekt oder verwenden Sie ein vorhandenes. Wenn Sie noch kein Gmail- oder Google Workspace-Konto haben, müssen Sie eines erstellen.

Der Projektname ist der Anzeigename für die Teilnehmer dieses Projekts. Es handelt sich um einen String, der nicht von Google APIs verwendet wird und den Sie jederzeit aktualisieren können.
Die Projekt-ID muss für alle Google Cloud-Projekte eindeutig sein und ist unveränderlich (kann nach der Festlegung nicht mehr geändert werden). In der Cloud Console wird automatisch ein eindeutiger String generiert. Normalerweise ist es nicht wichtig, wie dieser aussieht. In den meisten Codelabs müssen Sie auf die Projekt-ID verweisen (die in der Regel als PROJECT_ID angegeben wird). Wenn Ihnen die ID nicht gefällt, können Sie eine andere zufällige ID generieren oder eine eigene ID ausprobieren und sehen, ob sie verfügbar ist. Nachdem das Projekt erstellt wurde, wird es „eingefroren“.
Es gibt einen dritten Wert, die Projektnummer, die von einigen APIs verwendet wird. Weitere Informationen zu diesen drei Werten

Als Nächstes müssen Sie die Abrechnung in der Cloud Console aktivieren, um Cloud-Ressourcen/-APIs verwenden zu können. Die Durchführung dieses Codelabs sollte keine oder nur geringe Kosten verursachen. Wenn Sie Ressourcen herunterfahren möchten, damit nach Abschluss dieses Codelabs keine Gebühren anfallen, folgen Sie den Bereinigungsanweisungen am Ende des Codelabs. Neue Nutzer von Google Cloud kommen für das Programm für kostenlose Testversionen mit einem Guthaben von 300$ infrage.

Cloud Shell starten

Während Sie Google Cloud von Ihrem Laptop aus per Fernzugriff nutzen können, wird in diesem Codelab Google Cloud Shell verwendet, eine Befehlszeilenumgebung, die in der Cloud ausgeführt wird.

Cloud Shell aktivieren

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.

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`

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].

3. API aktivieren

Die BigQuery API sollte standardmäßig in allen Google Cloud-Projekten aktiviert sein. Mit dem folgenden Befehl in der Cloud Shell können Sie prüfen, ob dies der Fall ist. BigQuery sollte aufgeführt sein:

gcloud services list

BigQuery sollte aufgeführt sein:

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

Wenn die BigQuery API nicht aktiviert ist, können Sie sie mit dem folgenden Befehl in der Cloud Shell aktivieren:

gcloud services enable bigquery.googleapis.com

4. API-Anfragen authentifizieren

Für Anfragen an die BigQuery API müssen Sie ein Dienstkonto verwenden. Das Dienstkonto gehört zu Ihrem Projekt und wird von der Google Cloud Python-Clientbibliothek zum Ausführen von BigQuery API-Anfragen verwendet. Wie jedes andere Nutzerkonto wird auch ein Dienstkonto durch eine E‑Mail-Adresse dargestellt. In diesem Abschnitt verwenden Sie das Cloud SDK, um ein Dienstkonto zu erstellen. Anschließend erstellen Sie die Anmeldedaten, die Sie für die Authentifizierung als Dienstkonto benötigen.

Legen Sie zuerst eine Umgebungsvariable PROJECT_ID fest:

export PROJECT_ID=$(gcloud config get-value core/project)

Erstellen Sie als Nächstes ein neues Dienstkonto, um auf die BigQuery API zuzugreifen:

gcloud iam service-accounts create my-bigquery-sa \
  --display-name "my bigquery service account"

Erstellen Sie als Nächstes Anmeldedaten, die der Python-Code verwendet, um sich mit dem neuen Dienstkonto anzumelden. Erstellen Sie die Anmeldedaten und speichern Sie sie mit dem folgenden Befehl als JSON-Datei ~/key.json:

gcloud iam service-accounts keys create ~/key.json \
  --iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com

Richten Sie die Umgebungsvariable GOOGLE_APPLICATION_CREDENTIALS ein. Sie wird von der BigQuery-Python-Clientbibliothek verwendet, die im nächsten Schritt behandelt wird, um die Anmeldedaten zu finden. Die Umgebungsvariable sollte auf den vollständigen Pfad der von Ihnen erstellten JSON-Datei mit den Anmeldedaten festgelegt werden:

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

Weitere Informationen zur Authentifizierung der BigQuery API

5. Zugriffssteuerung einrichten

BigQuery verwendet die Identitäts- und Zugriffsverwaltung (Identity and Access Management, IAM), um den Zugriff auf Ressourcen zu verwalten. BigQuery bietet eine Reihe vordefinierter Rollen (Nutzer, Dateninhaber, Datenbetrachter usw.), die Sie dem Dienstkonto zuweisen können, das Sie im vorherigen Schritt erstellt haben. Weitere Informationen zur Zugriffssteuerung finden Sie in der BigQuery-Dokumentation.

Bevor Sie öffentliche Datasets abfragen können, muss dem Dienstkonto mindestens die Rolle roles/bigquery.user zugewiesen sein. Führen Sie in Cloud Shell den folgenden Befehl aus, um dem Dienstkonto die Nutzerrolle zuzuweisen:

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
  --role "roles/bigquery.user"

Sie können den folgenden Befehl ausführen, um zu prüfen, ob das Dienstkonto die Nutzerrolle hat:

gcloud projects get-iam-policy $PROJECT_ID

Sie sollten Folgendes sehen:

bindings:
- members:
  - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com
  role: roles/bigquery.user
...

6. Clientbibliothek installieren

Installieren Sie die BigQuery-Clientbibliothek für Python:

pip3 install --user --upgrade google-cloud-bigquery

Jetzt können Sie mit der BigQuery API programmieren.

7. Werke von Shakespeare abfragen

Ein öffentliches Dataset ist ein Dataset, das in BigQuery gespeichert ist und der Allgemeinheit zugänglich gemacht wird. Es sind viele andere öffentliche Datasets vorhanden, die Sie abfragen können. Einige Datasets werden von Google gehostet, die meisten jedoch von Drittanbietern. Weitere Informationen finden Sie auf der Seite Öffentliche Datasets.

Zusätzlich zu öffentlichen Datasets stellt BigQuery eine begrenzte Anzahl von Beispieltabellen bereit, die Sie abfragen können. Diese Tabellen sind im Dataset bigquery-public-data:samples enthalten. Die Tabelle shakespeare im Dataset samples enthält einen Wortindex der Werke von Shakespeare. Sie gibt an, wie oft jedes Wort in jedem Korpus vorkommt.

In diesem Schritt fragen Sie die Tabelle shakespeare ab.

Erstellen Sie zuerst in Cloud Shell eine einfache Python-Anwendung, mit der Sie die Translation API-Beispiele ausführen.

mkdir bigquery-demo
cd bigquery-demo
touch app.py

Öffnen Sie den Code-Editor oben rechts in Cloud Shell:

Rufen Sie die Datei app.py im Ordner bigquery-demo auf und ersetzen Sie den Code durch Folgendes.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT corpus AS title, COUNT(word) AS unique_words
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    title = row['title']
    unique_words = row['unique_words']
    print(f'{title:<20} | {unique_words}')

Nehmen Sie sich ein oder zwei Minuten Zeit, um sich den Code anzusehen und zu sehen, wie die Tabelle abgefragt wird.

Führen Sie die App in Cloud Shell aus:

python3 app.py

Es sollte eine Liste mit Wörtern und deren Vorkommen angezeigt werden:

hamlet               | 5318
kinghenryv           | 5104
cymbeline            | 4875
troilusandcressida   | 4795
kinglear             | 4784
kingrichardiii       | 4713
2kinghenryvi         | 4683
coriolanus           | 4653
2kinghenryiv         | 4605
antonyandcleopatra   | 4582

8. GitHub-Dataset abfragen

Um sich besser mit BigQuery vertraut zu machen, führen Sie jetzt eine Abfrage für das öffentliche GitHub-Dataset aus. Die häufigsten Commit-Nachrichten finden Sie auf GitHub. Sie verwenden auch die BigQuery-Webkonsole, um Ad-hoc-Abfragen in der Vorschau anzusehen und auszuführen.

So sehen Sie sich die Daten an: Öffnen Sie das GitHub-Dataset in der BigQuery-Web-UI:

github_repos-Tabelle öffnen

Klicken Sie auf die Schaltfläche „Vorschau“, um sich die Daten anzusehen:

Rufen Sie die Datei app.py im Ordner bigquery_demo auf und ersetzen Sie den Code durch Folgendes.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

Nehmen Sie sich ein bis zwei Minuten Zeit, um sich den Code anzusehen und zu verstehen, wie die Tabelle nach den häufigsten Commit-Nachrichten durchsucht wird.

Führen Sie die App in Cloud Shell aus:

python3 app.py

Sie sollten eine Liste mit Commit-Nachrichten und ihren Vorkommen sehen:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862

9. Caching und Statistiken

BigQuery speichert die Ergebnisse von Abfragen im Cache. Daher dauern nachfolgende Abfragen weniger lange. Mit Abfrageoptionen lässt sich das Caching deaktivieren. BigQuery erfasst auch Statistiken zu Abfragen, z. B. Erstellungszeit, Endzeit und Anzahl der insgesamt verarbeiteten Byte.

In diesem Schritt deaktivieren Sie das Caching und lassen sich Statistiken zu den Anfragen anzeigen.

Rufen Sie die Datei app.py im Ordner bigquery_demo auf und ersetzen Sie den Code durch Folgendes.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
job_config = bigquery.job.QueryJobConfig(use_query_cache=False)
results = client.query(query, job_config=job_config)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

print('-'*60)
print(f'Created: {results.created}')
print(f'Ended:   {results.ended}')
print(f'Bytes:   {results.total_bytes_processed:,}')

Einige Hinweise zum Code: Zuerst wird das Caching deaktiviert, indem QueryJobConfig eingeführt und use_query_cache auf „false“ gesetzt wird. Zweitens haben Sie über das Jobobjekt auf die Statistiken zur Abfrage zugegriffen.

Führen Sie die App in Cloud Shell aus:

python3 app.py

Wie zuvor sollte eine Liste mit Commit-Nachrichten und deren Vorkommen angezeigt werden. Außerdem sollten Sie am Ende einige Statistiken zur Abfrage sehen:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862
------------------------------------------------------------
Created: 2020-04-03 13:30:08.801000+00:00
Ended:   2020-04-03 13:30:15.334000+00:00
Bytes:   2,868,251,894

10. Daten in BigQuery laden

Wenn Sie Ihre eigenen Daten abfragen möchten, müssen Sie sie in BigQuery laden. BigQuery unterstützt das Laden von Daten aus vielen Quellen, darunter Cloud Storage, andere Google-Dienste und andere lesbare Quellen. Sie können Ihre Daten sogar mit Streaming-Insert-Anweisungen streamen. Weitere Informationen finden Sie auf der Seite Daten in BigQuery laden.

In diesem Schritt laden Sie eine in Cloud Storage gespeicherte JSON-Datei in eine BigQuery-Tabelle. Die JSON-Datei befindet sich unter gs://cloud-samples-data/bigquery/us-states/us-states.json.

Wenn Sie sich für den Inhalt der JSON-Datei interessieren, können Sie sie mit dem gsutil-Befehlszeilentool in Cloud Shell herunterladen:

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

Sie sehen, dass sie die Liste der US-Bundesstaaten enthält und jeder Bundesstaat ein JSON-Dokument in einer separaten Zeile ist:

head us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

Wenn Sie diese JSON-Datei in BigQuery laden möchten, rufen Sie die Datei app.py im Ordner bigquery_demo auf und ersetzen Sie den Code durch den folgenden.

from google.cloud import bigquery

client = bigquery.Client()

gcs_uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json'

dataset = client.create_dataset('us_states_dataset')
table = dataset.table('us_states_table')

job_config = bigquery.job.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

load_job = client.load_table_from_uri(gcs_uri, table, job_config=job_config)

print('JSON file loaded to BigQuery')

Sehen Sie sich an, wie der Code die JSON-Datei lädt und eine Tabelle mit einem Schema in einem Dataset erstellt.

Führen Sie die App in Cloud Shell aus:

python3 app.py

In BigQuery werden ein Dataset und eine Tabelle erstellt.

Rufen Sie die BigQuery-Konsole auf, um zu prüfen, ob das Dataset erstellt wurde. Sie sollten ein neues Dataset und eine neue Tabelle sehen. Wechseln Sie zum Vorschau-Tab der Tabelle, um Ihre Daten zu sehen:

11. Glückwunsch!

Sie haben gelernt, wie Sie BigQuery mit Python verwenden.

Aufräumen

So vermeiden Sie, dass Ihrem Google Cloud-Konto die in dieser Anleitung verwendeten Ressourcen in Rechnung gestellt werden:

Wechseln Sie in der Cloud Console zur Seite Ressourcen verwalten.
Wählen Sie in der Projektliste Ihr Projekt aus und klicken Sie auf Löschen.
Geben Sie im Dialogfeld die Projekt-ID ein und klicken Sie auf Beenden, um das Projekt zu löschen.

Weitere Informationen

Google BigQuery: https://cloud.google.com/bigquery/docs/
Python in Google Cloud: https://cloud.google.com/python/
Cloud-Clientbibliotheken für Python: https://googleapis.github.io/google-cloud-python/

Lizenz

Dieser Text ist mit einer Creative Commons Attribution 2.0 Generic License lizenziert.