GCPのBigQueryを試してみた – データ分析入門シリーズ

データ分析基盤
2021.09.17

Google Cloud Platformも大して使ったことがなかったのに BigQuery を使ってみることになった。調べたり手を動かした結果を記録してます。

BigQueryって何？

大きなデータを扱うためには適切な物理サーバを用意してセットアップし、MySQLなどをインストール・セットアップして、保守性を考慮した設計にしたりバックアップを管理したりする必要がある。また、大きなデータを扱うときはクエリは遅くなってしまう。

BigQueryは、Googleの高性能なインフラを使うことで大量のデータに対しても高速でクエリを実行できるようにしてくれる。そして、フルマネージドサービスだからディスクや仮想マシンを用意したり、何かをデプロイする必要がない。

BigQueryを使う準備

BigQuery をはじめるには

  1. Google Cloud のアカウント
  2. BigQuery を使うための Google Cloud プロジェクト

の2つが必要。この辺は事前にやっときましょう。

そして、プロジェクトで BigQuery を有効にします。
新しくプロジェクトを始める場合はデフォルトで有効になっています。
既存のプロジェクトで有効にするためには BigQuery API を有効にします。

APIを有効にする手順は下記です：

  • メニューから「APIとサービス」を選択
  • ライブラリを選択
  • 「Bigquery API」を検索
  • 有効にする

いくつかの設定が必要です。

本格的に BigQuery を運用していく上では課金情報が必要ですが、サンドボックスがあるので最初は登録しなくても問題ありません。登録されているかどうかを確認するには、メニューから「お支払い」を選択します。下記の表記があれば課金が発生しないので安心して使えます。

このプロジェクトには請求先アカウントがありません

BigQueryに触ってみる

BigQueryを操作する方法は3つあります：

  1. Cloud Console を使う
  2. bq コマンドを使う
  3. クライアントを使う

Cloud Console を使う

これはブラウザ上で操作する方法です。

プロジェクトを開いた状態で、メニューから「BigQuery」を開きます（ビッグデータのセクションにある）。

画面にはエディタが表示されます。エディタにクエリを入力して実行ボタンを押すとクエリを実行することができます。下記のサンプルを実行すると一般公開されたデータに対してクエリを実行して動作を確認することができます。

SELECT
  name, gender,
  SUM(number) AS total
FROM
  `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY
  name, gender
ORDER BY
  total DESC
LIMIT
  10

bqコマンドを使う

これは、Cloud SDKを使ってコマンドラインからBigQueryにアクセスする方法です。

Cloud SDKのインストール手順

こちら から自分の環境に合ったクイックスタートを選びます。Macバージョンの例を示します。

google-cloud-sdk-308.0.0-darwin-x86_64.tar.gz

をダウンロードします。適当なディレクトリで解凍し、中のインストールスクリプトを実行します。

./google-cloud-sdk/install.sh

製品改善のために統計情報を送ってもよいか、PATHに追加するか、必要であればPythonインストールを実行するかを聞かれます。

次にSDKを初期化します。

./google-cloud-sdk/bin/gcloud init

ここでアカウントとの紐付けが行われます。ブラウザが開くので自分のアカウントでログインして、紐付けを許可します。ローカルのコンソールに以下のように表示されていればOKです。

You are logged in as: [アカウント]

次にプロジェクトの選択です。選択肢の中から使用するプロジェクトの番号を選んで入力します。下記のようなメッセージが出ます。

Your current project has been set to: [プロジェクト名].

端末を再起動するか、sourceコマンドでPATHを読み直します。

gcloud help

SDKが有効になり、コマンドラインからgcloudコマンドが使えるようになっているはずです。

bq show

BigQueryで使用する bq コマンドも使えるようになっています。上記のコマンドを実行すると、初回実行時には設定ファイルの初期化が行われます。

これで bq コマンドが有効になりました。

サンプルコードを動かす

ドキュメントにあるサンプルを動かしてみます。BigQueryでは一般に公開されているデータがあり、誰でもアクセスすることができます。次の例では、公開されているシェイクスピアのデータから「raisin」を含む単語の数を数えています。

$ bq query --use_legacy_sql=false \
'SELECT
   word,
   SUM(word_count) AS count
 FROM
   `bigquery-public-data`.samples.shakespeare
 WHERE
   word LIKE "%raisin%"
 GROUP BY
   word'
Waiting on bqjob_r4d4a72f6a6a11060_0000017bf2b4fd8f_1 ... (0s) Current status: DONE
+---------------+-------+
|     word      | count |
+---------------+-------+
| praising      |     8 |
| Praising      |     4 |
| raising       |     5 |
| dispraising   |     2 |
| dispraisingly |     1 |
| raisins       |     1 |
+---------------+-------+

次のコードは、上記で使ったシェイクスピアのデータのテーブル定義を表示しています。

bq show bigquery-public-data:samples.shakespeare
Table bigquery-public-data:samples.shakespeare

   Last modified                  Schema                 Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Labels
 ----------------- ------------------------------------ ------------ ------------- ------------ ------------------- ------------------ --------
  15 Mar 02:16:45   |- word: string (required)           164656       6432064
                    |- word_count: integer (required)
                    |- corpus: string (required)
                    |- corpus_date: integer (required)

bqコマンドでできること

bqコマンドでは他にどんなことができるでしょうか。helpコマンドでbqのサブコマンドを見てみます。

コマンド 説明
add-iam-policy-binding IAMに紐づいた権限付与
cancel 実行中のジョブをキャンセルする
cp テーブルをコピーする
extract Google Cloud Storage URIにデータを抽出する
get-iam-policy IAMポリシーをJSON形式で表示する
head テーブルの行を表示する
help ヘルプを表示
init 認証を行い、デフォルトの.bigqueryrcファイルを作成する
insert テーブルに表を挿入する
load データを読み込んでテーブルに挿入する
ls 特定のプロジェクトまたはデータセットをリスト表示する
mk データセット、テーブル、ビューなどを作成する
mkdef 外部のテーブル定義をJSONで表示する
partition テーブルをパーティション済みテーブルにコピー
query クエリを実行する
remove-iam-policy-binding IAMに紐づいた権限を削除
rm データセット、テーブルなどを削除
set-iam-policy IAMポリシーを紐づける
shell インタラクティブなbqセッションを開始する
show オブジェクトの情報を表示する
truncate テーブル、データセット、プロジェクトを削除する
update データセット、テーブル、ビューなどを更新する
version bqのバージョンを表示
wait ジョブが完了するまで待つ

bqコマンドでテーブルを作成する手順

コマンドラインからテーブルを作成する手順です。

まずはデータセットを作成します。プロジェクト your-sample-project の下に sample-dataset というデータセットを作るコマンドは次のようになります。

$ bq mk sample-dataset
Dataset 'your-sample-project:sample-dataset' successfully created.

次にデータセットの中にテーブルを作成します。テーブルを作成するためにテーブルのスキーマを書きます。JSON形式で次のように作成します。ID、名前、性別を持ったユーザテーブルの想定です。

$ cat schema.json
[
    {"name": "user_id", "type": "integer", "mode": "required"},
    {"name": "user_name", "type": "string", "mode": "required"},
    {"name": "user_gender", "type": "integer", "mode": "required"},
]

このスキーマファイルを使って、次のコマンドでテーブルを作成することができます。

$ bq mk --schema schema.json -t sample-dataset.user
Table 'your-sample-project:sample-dataset.user' successfully created.

確認してみると、スキーマ通りのテーブルが作成できてます。

$ bq show sample-dataset.user
Table your-sample-project:sample-dataset.user

   Last modified                Schema                Total Rows   Total Bytes   Expiration   Labels
 ----------------- --------------------------------- ------------ ------------- ------------ --------
  21 Sep 12:00:00   |- user_id: integer (required)    0            0
                    |- user_name: string (required)
                    |- user_gender: integer (required)

おわりに

BigQueryの基本からコマンドラインでの操作までをやりました。使っているデータが小さくてBigQueryのパフォーマンスなどの恩恵は実感しにくいですが、これでBigQueryを触れるようになりました。

タイトルとURLをコピーしました