Daten mit dem bq-Befehlszeilentool für BigQuery laden und abfragen

1. Einführung

BigQuery ist eine vollständig verwaltete, kostengünstige NoOps-Analysedatenbank von Google. Mit BigQuery können Sie Terabytes an Daten abfragen, ohne einen Datenbankadministrator oder eine Infrastruktur zu benötigen. BigQuery basiert auf 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 verwenden Sie das bq-Befehlszeilentool, um eine lokale CSV-Datei in eine neue BigQuery-Tabelle zu laden.

Lerninhalte

• bq-Befehlszeilentool für BigQuery verwenden
• Lokale Datendateien in eine BigQuery-Tabelle laden

Voraussetzungen

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

2. Einrichten

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.

Cloud Shell

Sie verwenden Cloud Shell, eine Befehlszeilenumgebung, die in Google Cloud ausgeführt wird.

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

3. Dataset erstellen

Erstellen Sie ein Dataset, das Ihre Tabellen enthält.

Was ist ein Dataset?

Ein BigQuery-Dataset ist eine Sammlung von Tabellen. Alle Tabellen in einem Dataset werden am selben Datenspeicherort gespeichert. Sie können auch benutzerdefinierte Zugriffssteuerungen anhängen, um den Zugriff auf ein Dataset und seine Tabellen einzuschränken.

Dataset erstellen

Erstellen Sie in Cloud Shell mit dem Befehl „bq mk“ ein Dataset mit dem Namen „bq_load_codelab“.

bq mk bq_load_codelab

Dataset-Attribute ansehen

Prüfen Sie, ob Sie das Dataset erstellt haben, indem Sie die Dataset-Eigenschaften mit dem Befehl „bq show“ aufrufen.

bq show bq_load_codelab

Die Ausgabe sollte in etwa so aussehen:

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      [e-mail]
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. Datendatei erstellen

BigQuery kann Daten aus verschiedenen Datenformaten laden, darunter zeilenumbruchgetrenntes JSON, Avro und CSV. Der Einfachheit halber verwenden Sie CSV.

CSV-Datei erstellen

Erstellen Sie in Cloud Shell eine leere CSV-Datei.

touch customer_transactions.csv

Öffnen Sie die CSV-Datei im Code-Editor in Cloud Shell, indem Sie den Befehl „cloudshell edit“ ausführen. Dadurch wird ein neues Browserfenster mit einem Code-Editor und einem Cloud Shell-Bereich geöffnet.

cloudshell edit customer_transactions.csv

Hinweis:Der Code-Editor erfordert Drittanbieter-Cookies, da er von einer anderen Domain als die Google Cloud Console geladen wird.

Geben Sie im Code-Editor einige durch Kommas getrennte Werte ein, die in BigQuery geladen werden sollen.

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

Speichern Sie die CSV-Datei, indem Sie auf Datei > Bearbeiten klicken.

5. Daten laden

Verwenden Sie den Befehl „bq load“, um Ihre CSV-Datei in eine BigQuery-Tabelle zu laden.

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

Sie haben die folgenden Optionen verwendet:

• --source_format=CSV verwendet beim Parsen der Datendatei das CSV-Datenformat.
• Mit --skip_leading_rows=1 wird die erste Zeile in der CSV-Datei übersprungen, da es sich um eine Kopfzeile handelt.
• Mit Bq_load_codelab.customer_transactions—the first positional argument— wird festgelegt, in welche Tabelle die Daten geladen werden sollen.
• ./customer_transactions.csv – das zweite Positionsargument – definiert, welche Datei geladen werden soll. Neben lokalen Dateien können mit dem Befehl „bq load“ auch Dateien aus Cloud Storage mit gs://my_bucket/path/to/file URIs geladen werden.
• Ein Schema, das in einer JSON-Schemadatei oder als durch Kommas getrennte Liste definiert werden kann. (Sie haben der Einfachheit halber eine durch Kommas getrennte Liste verwendet.)

Sie haben das folgende Schema in der Tabelle „customer_transactions“ verwendet:

• Id:string: Eine Kundenkennung
• Zip:string: Eine US-Postleitzahl
• Ttime:timestamp: Das Datum und die Uhrzeit, zu der die Transaktion stattgefunden hat.
• Amount:numeric: Der Betrag einer Transaktion. In einer numerischen Spalte werden Daten in Dezimalform gespeichert, was für Geldwerte nützlich ist.
• Fdbk:float: Die Bewertung aus einer Feedbackumfrage zur Transaktion
• Sku:string: Eine Kennung für den gekauften Artikel

Tabellendetails abrufen

Prüfen Sie, ob die Tabelle geladen wurde, indem Sie die Tabelleneigenschaften aufrufen.

bq show bq_load_codelab.customer_transactions

Ausgabe:

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. Daten abfragen

Nachdem Ihre Daten geladen wurden, können Sie sie über die BigQuery-Web-UI, den bq-Befehl oder die API abfragen. In Ihren Abfragen können Sie Ihre Daten mit beliebigen Datasets (oder Datasets, sofern sie sich am selben Standort befinden) zusammenführen, für die Sie die Leseberechtigung haben.

Führen Sie eine Standard-SQL-Abfrage aus, mit der Ihr Dataset mit dem Dataset für US-Postleitzahlen zusammengeführt und die Transaktionen nach US-Bundesstaat summiert werden. Führen Sie die Abfrage mit dem Befehl „bq query“ aus.

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

Die Ausgabe dieses Befehls sollte in etwa so aussehen:

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

Bei der Ausführung der Abfrage wurden ein öffentliches und Ihr privates Dataset verwendet. Weitere Informationen finden Sie in dieser kommentierten Version derselben Abfrage:

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. Bereinigen

Löschen Sie das Dataset, das Sie mit dem Befehl „bq rm“ erstellt haben. Verwenden Sie das Flag „-r“, um alle darin enthaltenen Tabellen zu entfernen.

bq rm -r bq_load_codelab

8. Glückwunsch!

Sie haben eine Tabelle in BigQuery hochgeladen und abgefragt.

Behandelte Themen

• Mit dem bq-Befehlszeilentool mit BigQuery interagieren
• Ihre Daten und ein öffentliches Dataset mit einer BigQuery-Abfrage zusammenführen

Nächste Schritte

Hier findest du weitere Informationen:

• Das bq-Befehlszeilentool
• Weitere Möglichkeiten zum Laden von Daten in BigQuery
• Andere über BigQuery verfügbare öffentliche Datasets.
• Wetter-, Kriminalitäts- und andere Datentypen in TIL mit BigQuery.