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

1. Einführung

BigQuery ist die vollständig verwaltete, automatisierte und kostengünstige Analysedatenbank von Google. Mit BigQuery können Sie Terabyte an Daten abfragen, ohne dass Sie einen Datenbankadministrator oder eine Infrastruktur benötigen. BigQuery nutzt vertrautes SQL und ein Abrechnungsmodell, bei dem nur für die tatsächliche Nutzung bezahlt wird. 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.

Aufgaben in diesem Lab

• 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

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.

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 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. 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 eines Datasets werden am selben Datenstandort gespeichert. Sie können auch benutzerdefinierte Zugriffssteuerungen hinzufügen, um den Zugriff auf ein Dataset und dessen Tabellen einzuschränken.

Dataset erstellen

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

bq mk bq_load_codelab

Dataset-Attribute ansehen

Prüfen Sie, ob Sie das Dataset erstellt haben. Rufen Sie dazu die Attribute des Datasets mit dem Befehl bq show auf.

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, einschließlich durch Zeilenumbruch getrenntes 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. Führen Sie dazu den Cloud Shell-Befehl „Bearbeiten“ aus. Dadurch wird ein neues Browserfenster mit einem Code-Editor und einem Cloud Shell-Bereich geöffnet.

cloudshell edit customer_transactions.csv

Hinweis:Für den Codeeditor sind Drittanbieter-Cookies erforderlich, da er von einer anderen Domain als der der 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.

5. Daten laden

Laden Sie die CSV-Datei mit dem Befehl „bqload“ in eine BigQuery-Tabelle.

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.
• --skip_leading_rows=1 überspringt die erste Zeile in der CSV-Datei, da es sich um eine Kopfzeile handelt.
• Bq_load_codelab.customer_transactions—the first positional argument—definiert, in welche Tabelle die Daten geladen werden sollen.
• ./customer_transactions.csv, das zweite Positionsargument, definiert, welche Datei geladen werden soll. Neben lokalen Dateien kann der Befehl „bqload“ auch Dateien aus Cloud Storage auch mit gs://my_bucket/path/to/file URIs laden.
• Ein Schema, das in einer JSON-Datei 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: US-Postleitzahl
• Ttime:timestamp: Datum und Uhrzeit der Transaktion
• Amount:numeric: Der Betrag einer Transaktion. In einer numerischen Spalte werden Daten im Dezimalformat gespeichert, was für Geldwerte nützlich ist.
• Fdbk:float: die Bewertung aus einer Feedback-Umfrage zur Transaktion
• Sku:string: Eine Kennzeichnung für den gekauften Artikel

Tabellendetails abrufen

Prüfen Sie anhand der Tabelleneigenschaften, ob die Tabelle geladen wurde.

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 die Daten geladen wurden, können Sie sie über die BigQuery-Web-UI, den bq-Befehl oder die API abfragen. Mit Ihren Abfragen können Sie Ihre Daten mit jedem Dataset (oder allen Datasets, die sich am selben Standort befinden) zusammenführen, für die Sie eine Leseberechtigung haben.

Führen Sie eine Standard-SQL-Abfrage aus, die das Dataset mit dem Dataset für US-Postleitzahlen verknüpft und Transaktionen nach US-Bundesstaat zusammenfasst. Verwenden Sie den bq-Abfragebefehl, um die Abfrage auszuführen.

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         |
+--------------+------------+

Für die ausgeführte Abfrage wurden ein öffentliches Dataset 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

• bq-Befehlszeilentool zur Interaktion mit BigQuery verwenden
• Ihre Daten und ein öffentliches Dataset mit einer BigQuery-Abfrage zusammenführen.

Nächste Schritte

Hier findest du weitere Informationen:

• Das bq-Befehlszeilentool
• Andere Möglichkeiten zum Laden von Daten in BigQuery
• Andere öffentliche Datasets, die über BigQuery verfügbar sind.
• Wetter-, Kriminaldaten und andere Datentypen in TIL mit BigQuery.