Mit BigQuery arbeiten

BigQuery ist ein vielseitiges Tool, mit dem umfassende Datasets gespeichert und abgefragt werden können, ohne sich um Datenformate, zugrunde liegende Ressourcen und andere Dinge kümmern zu müssen. Sie können sich voll und ganz auf die Analyse konzentrieren.

Vorbereitung

Es wird empfohlen, die folgenden Ressourcen zu überprüfen:

Dataset

Wenn Sie BigQuery in einem Google Cloud-Projekt verwenden möchten, erstellen Sie zuerst ein Dataset mit dem Cmdlet New-BqDataset. Dieses nimmt grundlegende Informationen auf und erstellt die serverseitige Ressource. Lokal wird ein Dataset-Referenzobjekt zurückgegeben.

# Makes a new dataset with DatasetId "page_views".
$dataset = New-BqDataset "page_views" -Name "Page Views" `
    -Description "Page views from 2014 onwards"

Verwenden Sie Get-BqDataset, um ein Referenzobjekt für ein vorhandenes Dataset abzurufen.

# Two ways to get a Dataset: by DatasetId and by Dataset object.
$dataset = Get-BqDataset "page_views"
$dataset = $dataset | Get-BqDataset

Dieses Objekt $dataset kann geändert und an weitere Cmdlets wie Set-BqDataset übergeben werden, um Cloudressourcen zu bearbeiten. Dieses Cmdlet verarbeitet auch das Hinzufügen und Entfernen von Labels mit -SetLabel und -ClearLabel.

# Updates the Name field of $dataset.
$dataset.Name = "PageView Data"
$dataset = Set-BqDataset "page_views"
# Adds the labels 'department' and 'purpose' to $dataset.
$dataset = Set-BqDataset "page_views" -SetLabel `
    @{"department" = "statistics"; "purpose" = "metrics"}

Mit Labels werden Datasets mit Suchbegriffen und/oder Werten gekennzeichnet, um sie später filtern und suchen zu können. Das Cmdlet Get-BqDataset verfügt über ein integriertes -Filter-Flag, das eine detaillierte Steuerung ermöglicht, wenn Datasets zur Verarbeitung mit anderen Cmdlets aufgelistet werden.

# Filter Datasets by the department label.
$stats = Get-BqDataset -Filter "department:statistics"

Datasets kann vom Cmdlet Remove-BqDataset gelöscht werden. Dieses Cmdlet unterstützt ShouldProcess (den Parameter -WhatIf) und fordert vor dem Löschen eines nicht leeren Dataset eine Bestätigung des Nutzers an. Diese Sicherheitsmaßnahme kann beim Skripting mit dem Parameter -Force umgangen werden.

# Deletes $dataset.
$dataset | Remove-BqDataset

Table

Jede Dataset hat eine Anzahl von Tables für Daten. Tables werden mit dem Cmdlet New-BqTable erstellt, indem eine TableId und die Dataset übergeben werden, in der sich die Tabelle befindet. Dataset kann vom Objekt oder mit dem Parameter -DatasetId übergeben werden. Get-BqTable und Set-BqTable funktionieren genauso wie die obigen Dataset-Cmdlets Get- und Set-.

# Creates a new table in the dataset from above.
$table = $dataset | New-BqTable "logs2014" `
    -Description "Log data from Jan 2014 to Dec 2014 inclusive"

# Gets a reference object for "page_views:logs2014".
$table = Get-BqTable "logs2014" -DatasetId "page_views"

# Modifies the Name attribute of logs2014.
$table.Name = "Logs 2014"
$table = $table | Set-BqTable

Tables kann vom Cmdlet Remove-BqTable gelöscht werden. Dieses Cmdlet unterstützt ShouldProcess (den Parameter -WhatIf) und fordert vor dem Löschen eines Table, der Daten enthält, eine Bestätigung des Nutzers an. Diese Sicherheitsmaßnahme kann mit dem Parameter -Force umgangen werden.

# Deletes $table.
$table | Remove-BqTable -Force

Schema

Tables benötigen Schemas, um das Format der darin enthaltenen Daten zu beschreiben. Schemas werden mit den Cmdlets New-BqSchema und Set-BqSchema erstellt. New-BqSchema kann die Formate für Zeilen direkt als Parameter oder als JSON-Array von Zeilenbeschreibungen verwenden. Die Ergebnisse von New-BqSchema werden immer an Set-BqSchema übergeben, das entweder ein Schema-Objekt ausgeben oder das Schema einem vorhandenen Table zuweisen kann.

# Assigns a Schema to $table
$table = Get-BqTable "logs2014" -DatasetId "page_views"
New-BqSchema "Page" "STRING" | New-BqSchema "Referrer" "STRING" |
    New-BqSchema "Timestamp" "DATETIME" | Set-BqSchema $table

# Creates a schema object to be used in multiple tables.
$schema = New-BqSchema "Page" "STRING" | New-BqSchema "Referrer" "STRING" |
    New-BqSchema "Timestamp" "DATETIME" | Set-BqSchema

Schema-Objekte können als Parameter bei der Table-Erstellung übergeben werden, wenn sie vorab erstellt werden.

# Creates a new table with the Schema object from above.
$table = $dataset | New-BqTable "logs2014" -Schema $schema

TableRow

Daten werden in Zeilen zu Tables hinzugefügt und daraus entfernt. Auf diese Zeilen kann über die Cmdlets Add-BqTableRow und Get-BqTableRow zugegriffen werden. Add-BqTableRow verwendet CSV-, JSON- und AVRO-Dateien für den Import in BigQuery.

# Ingests a CSV file and appends its rows onto the table 'my_dataset:my_table'.
$table = New-BqTable "logs2014" -DatasetId "page_views"
$table | Add-BqTableRow CSV $filename -SkipLeadingRows 1 `
                                      -WriteMode WriteAppend

# Returns a list of the rows in 'page_views:logs2014'.
$list = Get-BqTable "logs2014" -DatasetID "page_views" | Get-BqTableRow

Jobs

Es gibt vier Arten von Jobs: Abfrage, Laden, Extrahieren und Kopieren. Abfragejobs führen Abfragen im SQL-Stil aus und geben Ergebnisse in Tabellen aus.

# Query Job: starts a query and outputs results into $table.
Start-BqJob -Query "SELECT * FROM ``page_views:logs2014``" `
            -Destination $table

Ladejobs importieren Google Cloud Storage-Dateien in BigQuery.

# Load Job: adds TableRows to $table from the file specified
# on Cloud Storage.
$job = $dest_table | Start-BqJob `
    -Load CSV "gs://page_views/server_logs_raw_2014.csv"

Extrahierungsjobs exportieren BigQuery-Tabellen in Google Cloud Storage.

# Extract Job: exports $src_table to a Cloud Storage file.
$job = $src_table | Start-BqJob `
    -Extract CSV "gs://page_views/logs2014.csv"

Kopierjobs kopieren eine vorhandene Tabelle in eine neue oder vorhandene Tabelle.

# Copy Job: Starts a copy job, cancels it, and polls until the job is completely done.
$job = $table | Start-BqJob -Copy $dest_table
$result = $job | Stop-BqJob
while ($result.Status.State -ne "DONE") {
    $result = $result | Get-BqJob
}

Start-BqJob startet diese Art von Jobs als asynchronen Vorgang. Verwenden Sie das Flag -PollUntilComplete, um den Cmdlet-Block zu verwenden, bis der Job abgeschlossen ist. Receive-BqJob gibt die Ergebnisse eines Abfragejobs zurück, sobald dieser abgeschlossen ist. Get-BqJob gibt ein Referenzobjekt zurück, das den aktuellen Status und die Statistiken zum Job enthält. Stop-BqJob sendet zum Beenden eines bestimmten Jobs eine Anfrage an den Server und kehrt dann sofort zurück.

Hinweis zum Formatieren von Tabellennamen in Abfragestrings: Das BigQuery-Format gibt an, dass Tabellennamen von Graviszeichen umschlossen sein müssen. Graviszeichen sind aber auch die Escapezeichen-Operatoren von Powershell. Aus diesem Grund muss dem Graviszeichen ein Escapezeichen vorangestellt werden, indem ein zweites Graviszeichen hinzugefügt wird. Der Beispielcode der Abfragejobs enthält ein entsprechendes Beispiel.