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
Cloud Shell で cloudshell edit コマンドを実行して、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
[File] > [Edit] をクリックして CSV ファイルを保存します。
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: 顧客 IDZip:string: 米国の郵便番号Ttime:timestamp: トランザクションが発生した日時Amount:numeric: 取引の金額（数値列には 10 進数形式でデータが格納されます。これは金額の値を扱う場合に便利です）。Fdbk:float: トランザクションに関するフィードバック アンケートの評価Sku:string: 購入したアイテムの識別子
テーブルの詳細を取得する
テーブルのプロパティを表示して、テーブルが読み込まれたことを確認します。
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 クエリを使用して、データと一般公開データセットを結合する。
次のステップ
詳細情報:
bqコマンドライン ツール- BigQuery にデータを読み込むその他の方法。
- BigQuery で利用可能なその他の一般公開データセット。
- TIL with BigQuery の天気、犯罪などのデータ。