このページは Cloud Translation API によって翻訳されました。

BigQuery 用の bq コマンドラインツールを使用してデータを読み込んでクエリを実行する

1. はじめに

BigQuery は、Google が提供する低コスト、NoOps のフルマネージド分析データベースです。BigQuery を使用すると、データベース管理者やインフラストラクチャがなくても、テラバイト規模のデータに対してクエリを実行できます。BigQuery では、使い慣れた SQL と従量課金制の課金モデルが使用されます。このような特長を活かし、ユーザーは有用な情報を得るためのデータ分析に専念できます。この Codelab では、bq コマンドラインツールを使用して、ローカルの CSV ファイルを新しい BigQuery テーブルに読み込みます。

学習内容

BigQuery 用の bq コマンドラインツールの使用方法
ローカルデータファイルを BigQuery テーブルに読み込む方法

必要なもの

Google Cloud プロジェクト
ブラウザ（Google Chrome など）

2. セットアップする

BigQuery を有効にする

Google アカウントをまだお持ちでない場合は、アカウントを作成してください。

Google Cloud コンソールにログインして、BigQuery に移動します。ブラウザに次の URL を入力して、BigQuery ウェブ UI を直接開くこともできます。

https://console.cloud.google.com/bigquery

利用規約に同意します。
BigQuery を使用するには、その前にプロジェクトを作成する必要があります。プロンプトに従って新しいプロジェクトを作成します。

プロジェクト名を選択し、プロジェクト ID をメモします。

プロジェクト ID は、すべての Google Cloud プロジェクトで一意の名前です。以降、このコードラボでは PROJECT_ID と呼びます。

この Codelab では、BigQuery サンドボックスの上限が設定された BigQuery リソースを使用します。請求先アカウントは必要ありません。後でサンドボックスの制限を削除したい場合は、Google Cloud の無料トライアルに登録して請求先アカウントを追加できます。

Cloud Shell

ここでは、Google Cloud で動作するコマンドライン環境である Cloud Shell を使用します。

Cloud Shell をアクティブにする

Cloud Console で、[Cloud Shell をアクティブにする] をクリックします。

Cloud Shell を初めて起動する場合は、その内容を説明する中間画面（スクロールしなければ見えない範囲）が表示されます。その場合は、[続行] をクリックします（今後表示されなくなります）。この中間画面は次のようになります。

Cloud Shell のプロビジョニングと接続に少し時間がかかる程度です。

この仮想マシンには、必要な開発ツールがすべて含まれています。仮想マシンは Google Cloud で稼働し、永続的なホームディレクトリが 5 GB 用意されているため、ネットワークのパフォーマンスと認証が大幅に向上しています。このコードラボでの作業のほとんどは、ブラウザまたは Chromebook から実行できます。

Cloud Shell に接続すると、すでに認証は完了しており、プロジェクトに各自のプロジェクト ID が設定されていることがわかります。

Cloud Shell で次のコマンドを実行して、認証されたことを確認します。

gcloud auth list

コマンド出力

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Cloud Shell で次のコマンドを実行して、gcloud コマンドがプロジェクトを認識していることを確認します。

gcloud config list project

コマンド出力

[core]
project = <PROJECT_ID>

上記のようになっていない場合は、次のコマンドで設定できます。

gcloud config set project <PROJECT_ID>

コマンド出力

Updated property [core/project].

3. データセットを作成する

テーブルを格納するデータセットを作成します。

データセットとは

BigQuery データセットはテーブルのコレクションです。データセット内のすべてのテーブルは、同じデータロケーションに保存されます。カスタムアクセス制御を接続して、データセットとそのテーブルへのアクセスを制限することもできます。

データセットを作成する

Cloud Shell で bq mk コマンドを使用して「bq_load_codelab」というデータセットを作成します。

bq mk bq_load_codelab

データセットのプロパティを表示する

bq show コマンドを使用してデータセットのプロパティを表示し、データセットが作成されたことを確認します。

bq show bq_load_codelab

出力は次のようになります。

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      your-email@example.com
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. データファイルを作成する

BigQuery では、改行区切りの JSON、Avro、CSV など、複数のデータ形式からデータを読み込むことができます。ここではわかりやすくするために CSV を使用します。

CSV ファイルを作成する

Cloud Shell で、空の CSV ファイルを作成します。

touch customer_transactions.csv

cloudshell の編集コマンドを実行して、Cloud Shell のコードエディタで CSV ファイルを開きます。新しいブラウザウィンドウに、コードエディタと Cloud Shell パネルが表示されます。

cloudshell edit customer_transactions.csv

コードエディタで、カンマ区切り値をいくつか入力して、BigQuery に読み込む。

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

[ファイル] >編集。

5. データの読み込み

bq load コマンドを使用して、CSV ファイルを BigQuery テーブルに読み込みます。

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

次のオプションを使用しました。

--source_format=CSV は、データファイルの解析時に CSV データ形式を使用します。
--skip_leading_rows=1 は、CSV ファイルの最初の行はヘッダー行であるため、スキップします。
Bq_load_codelab.customer_transactions—the first positional argument— は、データを読み込むテーブルを定義します。
./customer_transactions.csv - 2 番目の位置引数は、読み込むファイルを定義します。bq load コマンドでは、ローカルファイルに加えて、gs://my_bucket/path/to/file URIs を使用して Cloud Storage からファイルを読み込むこともできます。
スキーマ。JSON スキーマファイルで定義することも、カンマ区切りのリストとして定義することもできます。（わかりやすくするため、カンマ区切りのリストを使用しました）。

customer_transactions テーブルでは、次のスキーマを使用しました。

Id:string: 顧客 ID
Zip:string: 米国の郵便番号
Ttime:timestamp: トランザクションが発生した日時
Amount:numeric: トランザクションの金額（数値列にはデータが 10 進数形式で格納されるため、金額に便利です）。
Fdbk:float: トランザクションに関するフィードバックアンケートからの評価
Sku:string: 購入されたアイテムの ID

テーブルの詳細を取得する

テーブルのプロパティを表示して、テーブルが読み込まれていることを確認します。

bq show bq_load_codelab.customer_transactions

出力:

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. データに対してクエリを実行する

データが読み込まれたので、BigQuery ウェブ UI、bq コマンド、または API を使用してデータをクエリできます。クエリでは、読み取り権限のある任意のデータセット（同じロケーションにある限り、複数のデータセット）に対してデータを結合できます。

データセットを米国の郵便番号のデータセットと結合し、米国の州ごとのトランザクションを合計する標準 SQL クエリを実行します。bq query コマンドを使用してクエリを実行します。

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
'

このコマンドの出力は次のようになります。

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

実行したクエリでは、一般公開データセットと非公開データセットを使用しました。詳しくは、同じクエリのコメント付きバージョンをご覧ください。

#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. クリーンアップ

bq rm コマンドで作成したデータセットを削除します。含まれているテーブルをすべて削除するには、-r フラグを使用します。

bq rm -r bq_load_codelab

8. 完了

BigQuery にテーブルをアップロードしてクエリを実行しました。

学習した内容

bq コマンドラインツールを使用して BigQuery を操作する。
BigQuery クエリを使用してデータと一般公開データセットを結合する。

次のステップ

以下の詳細を確認する

BigQuery 用の bq コマンドライン ツールを使用してデータを読み込んでクエリを実行する

1. はじめに

学習内容

必要なもの

2. セットアップする

BigQuery を有効にする

Cloud Shell

Cloud Shell をアクティブにする

3. データセットを作成する

データセットとは

データセットを作成する

データセットのプロパティを表示する

4. データファイルを作成する

CSV ファイルを作成する

5. データの読み込み

テーブルの詳細を取得する

6. データに対してクエリを実行する

7. クリーンアップ

8. 完了

学習した内容

次のステップ

BigQuery 用の bq コマンドラインツールを使用してデータを読み込んでクエリを実行する