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 und Sie benötigen keinen Datenbankadministrator. Sie können sich also ganz auf die Datenanalyse konzentrieren, um aussagekräftige Informationen zu erhalten, vertrautes SQL verwenden und unser Pay-as-you-go-Modell nutzen.

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

Aufgaben in diesem Lab

• Cloud Shell verwenden
• BigQuery API aktivieren
• API-Anfragen authentifizieren
• Python-Clientbibliothek installieren
• Abfrage der Werke von Shakespeare
• GitHub-Dataset abfragen
• Caching und Anzeige von Statistiken anpassen

Voraussetzungen

• Ein Google Cloud-Projekt
• Ein Browser, z. B. Chrome oder Firefox
• Vertrautheit bei der Verwendung von Python

Umfrage

Wie möchten Sie diese Anleitung nutzen?

<ph type="x-smartling-placeholder"></ph> Nur bis zum Ende lesen Lies sie dir durch und absolviere die Übungen

Wie würden Sie Ihre Erfahrung mit Python bewerten?

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

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

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

2. Einrichtung und Anforderungen

Umgebung für das selbstbestimmte Lernen einrichten

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

• Der Projektname ist der Anzeigename für die Projektteilnehmer. Es handelt sich um eine Zeichenfolge, die von Google APIs nicht verwendet wird und jederzeit aktualisiert werden kann.
• Die Projekt-ID muss für alle Google Cloud-Projekte eindeutig sein und ist unveränderlich. Sie kann nach dem Festlegen nicht mehr geändert werden. Die Cloud Console generiert automatisch einen eindeutigen String. ist Ihnen meist egal, was es ist. In den meisten Codelabs musst du auf die Projekt-ID verweisen, die in der Regel als PROJECT_ID identifiziert wird. Wenn es dir nicht gefällt, kannst du eine weitere zufällige Projekt-ID generieren. Du kannst aber auch selbst eine andere testen, um zu sehen, ob sie verfügbar ist. Dann ist es „eingefroren“ nachdem das Projekt erstellt wurde.
• Es gibt einen dritten Wert, die Projektnummer, die von einigen APIs verwendet wird. Weitere Informationen zu allen drei Werten finden Sie in der Dokumentation.

Achtung:Eine Projekt-ID muss global eindeutig sein und kann nach der Auswahl von niemandem mehr verwendet werden. Sie sind der einzige Nutzer dieser ID. Wenn ein Projekt gelöscht wird, kann diese ID nicht mehr verwendet werden.

Hinweis:Wenn Sie ein Gmail-Konto verwenden, können Sie die Standardeinstellung Keine Organisation beibehalten. Wenn Sie ein Google Workspace-Konto verwenden, wählen Sie einen Standort aus, der für Ihre Organisation sinnvoll ist.

2. Als Nächstes müssen Sie in der Cloud Console die Abrechnung aktivieren, um Cloud-Ressourcen/APIs verwenden zu können. Dieses Codelab sollte möglichst wenig kosten. Wenn Sie Ressourcen beenden möchten, damit über diese Anleitung hinaus keine Kosten anfallen, führen Sie eine Bereinigung durch am Ende des Codelabs. Neue Google Cloud-Nutzer haben Anspruch auf eine kostenlose Testversion mit 300$Guthaben.

Cloud Shell starten

Sie können Google Cloud zwar von Ihrem Laptop aus der Ferne bedienen, in diesem Codelab verwenden Sie jedoch Google Cloud Shell, eine Befehlszeilenumgebung, die in der Cloud ausgeführt wird.

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

3. API aktivieren

Die BigQuery API sollte in allen Google Cloud-Projekten standardmäßig aktiviert sein. Mit dem folgenden Befehl in Cloud Shell können Sie überprüfen, ob dies zutrifft: Sie sollten BigQuery aufgelistet sein:

gcloud services list

Sie sollten BigQuery sehen:

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

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

gcloud services enable bigquery.googleapis.com

Hinweis: Tritt ein Fehler auf, kehren Sie zum vorherigen Schritt zurück und überprüfen Sie die Einrichtung.

4. API-Anfragen authentifizieren

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

Legen Sie zuerst eine PROJECT_ID-Umgebungsvariable fest:

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

Erstellen Sie als Nächstes ein neues Dienstkonto, um mit folgendem Befehl 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, mit denen Ihr Python-Code sich als Ihr neues Dienstkonto anmeldet. Erstellen Sie diese 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

Legen Sie schließlich die Umgebungsvariable GOOGLE_APPLICATION_CREDENTIALS fest, die von der BigQuery-Python-Clientbibliothek verwendet wird (siehe nächster Schritt), um Ihre Anmeldedaten zu finden. Die Umgebungsvariable sollte auf den vollständigen Pfad der JSON-Datei mit den Anmeldedaten festgelegt werden, indem Sie Folgendes verwenden:

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

Hier finden Sie weitere Informationen zur Authentifizierung der BigQuery API.

5. Zugriffssteuerung einrichten

BigQuery nutzt Identity and Access Management (IAM), um den Zugriff auf Ressourcen zu verwalten. BigQuery verfügt über eine Reihe vordefinierter Rollen (user, dataOwner, dataViewer usw.), die Sie dem im vorherigen Schritt erstellten Dienstkonto zuweisen können. Weitere Informationen zur Zugriffssteuerung finden Sie in der BigQuery-Dokumentation.

Bevor Sie öffentliche Datasets abfragen können, müssen Sie dafür sorgen, dass das Dienstkonto mindestens die Rolle roles/bigquery.user hat. 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"

Mit dem folgenden Befehl können Sie 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

Sie können jetzt mit der BigQuery API programmieren.

7. Die Werke von Shakespeare abfragen

Ein öffentliches Dataset ist ein Dataset, das in BigQuery gespeichert ist und der Allgemeinheit zugänglich gemacht wird. Es gibt viele weitere öffentliche Datasets, die Sie abfragen können. Einige Datensätze werden zwar 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 zur Verfügung, 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.

Hinweis: Die Details der Tabelle shakespeare in der BigQuery-Konsole finden Sie hier.

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 oben rechts in Cloud Shell den Code-Editor:

Gehen Sie zur Datei app.py im Ordner bigquery-demo und ersetzen Sie den Code durch den folgenden Code.

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 bis zwei Minuten Zeit, um den Code zu untersuchen und zu sehen, wie die Tabelle abgefragt wird.

Führen Sie die Anwendung in Cloud Shell aus:

python3 app.py

Es sollte eine Liste mit Wörtern und ihrer Vorkommnisse angezeigt werden:

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

Hinweis: Wenn der Fehler PermissionDenied (403) angezeigt wird, überprüfen Sie die Schritte, die im Schritt API-Anfragen authentifizieren ausgeführt wurden.

• Prüfen Sie, ob die Umgebungsvariable für Anmeldedaten definiert ist: echo $GOOGLE_APPLICATION_CREDENTIALS
• Sie sollten den vollständigen Pfad zu Ihrer Anmeldedatendatei sehen: /home/$USER/key.json
• Prüfen Sie dann, ob die Anmeldedaten erstellt wurden: cat $GOOGLE_APPLICATION_CREDENTIALS
• Die Ausgabe sollte in etwa so aussehen: {"type": "service_account","project_id": "PROJECT_ID",...}

Wenn etwas falsch ist, kehren Sie zum Schritt API-Anfragen authentifizieren zurück.

8. GitHub-Dataset abfragen

Um sich mit BigQuery besser vertraut zu machen, führen Sie nun eine Abfrage für das öffentliche GitHub-Dataset aus. Die häufigsten Commit-Nachrichten finden Sie auf GitHub. Außerdem verwenden Sie die Webkonsole von BigQuery, um eine Vorschau anzuzeigen und Ad-hoc-Abfragen auszuführen.

Öffnen Sie das GitHub-Dataset in der BigQuery-Web-UI, um zu sehen, wie die Daten dargestellt werden:

Tabelle github_repos öffnen

Klicken Sie auf die Schaltfläche Vorschau , um die Daten anzuzeigen:

Gehen Sie zur Datei app.py im Ordner bigquery_demo und ersetzen Sie den Code durch den folgenden Code.

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 sehen, wie die Tabelle nach den häufigsten Commit-Nachrichten abgefragt wird.

Führen Sie die Anwendung in Cloud Shell aus:

python3 app.py

Es sollte eine Liste der Commit-Nachrichten und deren Vorkommen angezeigt werden:

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

Hinweis: Dieses Dataset wird regelmäßig aktualisiert. Ihre Ergebnisse können daher variieren.

9. Caching und Statistiken

BigQuery speichert die Ergebnisse von Abfragen im Cache. Dadurch dauern nachfolgende Abfragen weniger Zeit. Es ist möglich, das Caching mit Abfrageoptionen zu deaktivieren. BigQuery verfolgt auch Statistiken zu Abfragen, wie z. B. Erstellungszeit, Endzeit und insgesamt verarbeitete Byte.

In diesem Schritt deaktivieren Sie das Caching und zeigen auch Statistiken zu den Abfragen an.

Gehen Sie zur Datei app.py im Ordner bigquery_demo und ersetzen Sie den Code durch den folgenden Code.

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:,}')

Wichtige 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 Anwendung in Cloud Shell aus:

python3 app.py

Wie zuvor sollten Sie eine Liste der Commit-Nachrichten und deren Vorkommen sehen. Darüber hinaus sollten am Ende einige Statistiken zur Abfrage angezeigt werden:

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 Ihre Daten in BigQuery laden. BigQuery unterstützt das Laden von Daten aus vielen Quellen, einschließlich Cloud Storage, anderen Google-Diensten und anderen lesbaren 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 mehr über den Inhalt der JSON-Datei erfahren möchten, können Sie das gsutil-Befehlszeilentool verwenden, um sie in Cloud Shell herunterzuladen:

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

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

head us-states.json

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

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

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')

Nehmen Sie sich zwei Minuten Zeit, um zu sehen, wie der Code die JSON-Datei lädt und eine Tabelle mit einem Schema unter einem Dataset erstellt.

Führen Sie die Anwendung in Cloud Shell aus:

python3 app.py

In BigQuery werden ein Dataset und eine Tabelle erstellt.

Rufen Sie die BigQuery-Konsole auf, um zu überprüfen, ob das Dataset erstellt wurde. Sie sollten ein neues Dataset und eine neue Tabelle sehen. Wechseln Sie zum Tab „Vorschau“ 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:

• Rufen Sie in der Cloud Console die Seite Ressourcen verwalten auf.
• Wählen Sie Ihr Projekt in der Projektliste 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.