BigQuery の操作

BigQuery は、データ形式や基盤となるリソースを気にせずに、膨大なデータセットを格納およびクエリできる汎用的なツールです。

はじめに

次のリソースを確認する必要があります。

Tools for PowerShellについて学習する。
タスクによっては追加の権限が実行に必要となるため、BigQuery のアクセス制御について理解する。
BigQuery の詳細を確認する。

データセット

Cloud プロジェクトで BigQuery を使用するには、まず New-BqDataset コマンドレットを使用して Dataset を作成します。これにより、基本的な情報を取得し、サーバー側でリソースを作成します。ローカルには、Dataset 参照オブジェクトが返されます。

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

既存のデータセットの参照オブジェクトを取得するには、Get-BqDataset を使用します。

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

このオブジェクト $dataset を変更し、Set-BqDataset などの別のコマンドレットに渡してクラウド上のリソースを操作できます。このコマンドレットでは、-SetLabel を使用してラベルを追加したり、-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"}

ラベルを使用すると、データセットにタグ（キーワードや値）を付け、検索やフィルタリングを実行できます。Get-BqDataset コマンドレットの -Filter フラグを使用すると、他のコマンドレットで処理するデータセットの一覧を表示するときに、より細かい制御を行うことができます。

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

Datasets は Remove-BqDataset コマンドレットで削除できます。このコマンドレットは ShouldProcess（-WhatIf パラメータ）をサポートしており、空でない Dataset を削除する前に確認のプロンプトを表示します。スクリプトを作成するときに -Force パラメータを使用すると、この確認を省略できます。

# Deletes $dataset.
$dataset | Remove-BqDataset

表

各 Dataset には、データを保持するために多数の Tables があります。Tables を作成するには、New-BqTable コマンドレットを使用します。このときに、TableId と、テーブルを入れる Dataset を指定します。Dataset は、オブジェクトまたは -DatasetId パラメータで渡すことができます。Get-BqTable と Set-BqTable は、上記の Get- と 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 は Remove-BqTable コマンドレットで削除できます。このコマンドレットは ShouldProcess（-WhatIf パラメータ）をサポートしているので、削除しようとしている Table の中にデータがある場合はユーザーに確認を求めます。-Force パラメータを使用すると、この確認を省略できます。

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

スキーマ

Tables には、含まれるデータの形式を Schemas に指定する必要があります。スキーマは New-BqSchema コマンドレットと Set-BqSchema コマンドレットで作成します。New-BqSchema に行の書式を指定するときに、パラメータで直接指定することも、行を記述した JSON 配列を使用することもできます。New-BqSchema の結果は常に Set-BqSchema に渡され、Schema オブジェクトを出力するか、既存の Table にスキーマを割り当てることができます。

# 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 オブジェクトは、事前に作成されている場合、Table 作成時にパラメータとして渡すことができます。

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

TableRow

Tables のデータの追加と削除は、行単位で行われます。これらの行には、Add-BqTableRow コマンドレットと Get-BqTableRow コマンドレットでアクセスできます。 Add-BqTableRow は、CSV、JSON、AVRO ファイルを 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 には、クエリ、読み込み、抽出、コピーの 4 種類があります。クエリジョブは、SQL のようなクエリを実行し、結果をテーブルに出力します。

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

読み込みジョブは、Cloud Storage ファイルを 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"

抽出ジョブは、BigQuery テーブルを Cloud Storage にエクスポートします。

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

コピージョブは、既存のテーブルを新しいテーブルまたは既存のテーブルにコピーします。

# 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 は、これらのジョブを非同期オペレーションとして開始します。ジョブが完了するまでコマンドレットをブロックするには、-PollUntilComplete フラグを使用します。Receive-BqJob は、クエリジョブが完了すると結果を返します。Get-BqJob は、ジョブの現在の状態と統計情報を示す参照オブジェクトを返します。Stop-BqJob は、特定のジョブを停止するリクエストをサーバーに送信してすぐに戻ります。

クエリ文字列内のテーブル名の書式に関する注意: BigQuery では、テーブル名をバッククォートで囲む必要がありますが、バッククォートは Powershell のエスケープ演算子です。このため、バッククォートを重ねてエスケープする必要があります。クエリジョブのサンプルコードをご覧ください。