Python での BigQuery の使用

1. 概要

BigQuery は、Google が提供するペタバイト規模の低料金フルマネージドアナリティクスデータウェアハウスです。BigQuery は NoOps（管理するインフラストラクチャが存在せず、データベース管理者が必要ない）であるため、使い慣れた SQL を使用してデータから有用な情報を見つけ出す作業に集中できます。また、このサービスは使った分だけ課金される従量制です。

この Codelab では、Python 用 Google Cloud クライアントライブラリを使用して、Python で BigQuery の一般公開データセットに対してクエリを実行します。

学習内容

Cloud Shell の使い方
BigQuery API を有効にする方法
API リクエストを認証する方法
Python クライアントライブラリをインストールする方法
シェイクスピア作品のクエリ方法
GitHub データセットに対するクエリ方法
キャッシュ保存と統計情報の表示を調整する方法

必要なもの

Google Cloud プロジェクト
ブラウザ（Chrome、Firefox など）
Python の使用経験

アンケート

このチュートリアルをどのように使用されますか？

通読のみ

通読して演習を行う

Python のご利用経験はどの程度ありますか？

初心者

中級者

上級者

Google Cloud サービスの使用経験はどの程度ありますか？

初心者

中級者

上級者

2. 設定と要件

セルフペース型の環境設定

Google Cloud Console にログインして、プロジェクトを新規作成するか、既存のプロジェクトを再利用します。Gmail アカウントも Google Workspace アカウントもまだお持ちでない場合は、アカウントを作成してください。

プロジェクト名は、このプロジェクトの参加者に表示される名称です。Google API では使用されない文字列で、いつでも更新できます。
プロジェクト ID は、すべての Google Cloud プロジェクトにおいて一意でなければならず、不変です（設定後は変更できません）。Cloud Console により一意の文字列が自動生成されます（通常は内容を意識する必要はありません）。ほとんどの Codelab では、プロジェクト ID を参照する必要があります（通常、プロジェクト ID は「PROJECT_ID」の形式です）。好みの文字列でない場合は、別のランダムな ID を生成するか、独自の ID を試用して利用可能であるかどうかを確認することができます。プロジェクトの作成後、ID は「フリーズ」されます。
3 つ目の値として、一部の API が使用するプロジェクト番号があります。これら 3 つの値について詳しくは、こちらのドキュメントをご覧ください。

次に、Cloud のリソースや API を使用するために、Cloud Console で課金を有効にする必要があります。この Codelab の操作をすべて行って、費用が生じたとしても、少額です。このチュートリアルを終了した後に課金が発生しないようにリソースをシャットダウンするには、Codelab の最後にある「クリーンアップ」の手順を行います。Google Cloud の新規ユーザーは、300 米ドル分の無料トライアルプログラムをご利用いただけます。

Cloud Shell の起動

Google Cloud はノートパソコンからリモートで操作できますが、この Codelab では、Google Cloud Shell（Cloud 上で動作するコマンドライン環境）を使用します。

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. API を有効にする

BigQuery API は、すべての Google Cloud プロジェクトでデフォルトで有効になっています。Cloud Shell で次のコマンドを実行して、これが true かどうかを確認できます。BigQuery が一覧表示されます。

gcloud services list

BigQuery が表示されます。

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

BigQuery API が有効になっていない場合は、Cloud Shell で次のコマンドを使用して有効にできます。

gcloud services enable bigquery.googleapis.com

4. API リクエストを認証する

BigQuery API にリクエストを送信するには、サービスアカウントを使用する必要があります。プロジェクトのサービスアカウントを Google Cloud Python クライアントライブラリで使用して、BigQuery API リクエストを送信します。ほかのユーザーアカウントと同じように、サービスアカウントはメールアドレスで表されます。このセクションでは、Cloud SDK を使用してサービスアカウントを作成し、サービスアカウントの認証で必要になる認証情報を作成します。

まず、PROJECT_ID 環境変数を設定します。

export PROJECT_ID=$(gcloud config get-value core/project)

次に、BigQuery API にアクセスする新しいサービスアカウントを作成します。

gcloud iam service-accounts create my-bigquery-sa \
  --display-name "my bigquery service account"

作成した新しいサービスアカウントとしてログインするために Python コードで使用する認証情報を作成します。次のコマンドを使用して認証情報を作成し、JSON ファイル ~/key.json に保存します。

gcloud iam service-accounts keys create ~/key.json \
  --iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com

最後に、GOOGLE_APPLICATION_CREDENTIALS 環境変数を設定します。この変数は、次のステップで説明する BigQuery Python クライアントライブラリが認証情報を検索する際に使用します。先ほど作成した、認証情報を含む JSON ファイルのフルパスを設定する必要があります。

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

BigQuery API の認証の詳細をご覧ください。

5. アクセス制御を設定する

BigQuery は Identity and Access Management（IAM）を使用してリソースへのアクセスを管理します。BigQuery には、前の手順で作成したサービスアカウントに割り当てることができる事前定義ロール（ユーザー、データオーナー、データ閲覧者など）が多数あります。BigQuery ドキュメントのアクセス制御をご覧ください。

一般公開データセットをクエリする前に、サービスアカウントに少なくとも roles/bigquery.user ロールが付与されていることを確認する必要があります。Cloud Shell で次のコマンドを実行して、サービスアカウントにユーザーロールを割り当てます。

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
  --role "roles/bigquery.user"

次のコマンドを実行して、サービスアカウントにユーザーロールがあることを確認できます。

gcloud projects get-iam-policy $PROJECT_ID

次の結果が表示されます。

bindings:
- members:
  - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com
  role: roles/bigquery.user
...

6. クライアントライブラリをインストールする

BigQuery Python クライアントライブラリをインストールします。

pip3 install --user --upgrade google-cloud-bigquery

これで、BigQuery API を使用してコーディングする準備が整いました。

7. シェイクスピア作品のクエリ

一般公開データセットは、BigQuery に保存され、一般に公開されるデータセットです。クエリを実行できる一般公開データセットは他にも多数あります。一部のデータセットは Google でホストされますが、ほとんどはサードパーティでホストされます。詳細については、一般公開データセットのページをご覧ください。

BigQuery には、一般公開データセットのほかにも、クエリを実行できるサンプルテーブルがあります。これらのテーブルは、bigquery-public-data:samples データセットに含まれています。samples データセットの shakespeare テーブルには、シェイクスピア作品の単語の索引が含まれています。それぞれのコーパスで各単語が出現する回数を示します。

このステップでは、shakespeare テーブルに対してクエリを実行します。

まず、Cloud Shell で、Translation API のサンプルを実行するために使用する簡単な Python アプリケーションを作成します。

mkdir bigquery-demo
cd bigquery-demo
touch app.py

Cloud Shell の右上からコードエディタを開きます。

bigquery-demo フォルダ内の app.py ファイルに移動し、コードを次のコードに置き換えます。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT corpus AS title, COUNT(word) AS unique_words
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    title = row['title']
    unique_words = row['unique_words']
    print(f'{title:<20} | {unique_words}')

コードを 1 ～ 2 分ほど確認して、テーブルがどのようにクエリされているかを確認します。

Cloud Shell に戻ってアプリを実行します。

python3 app.py

単語とその出現回数のリストが表示されます。

hamlet               | 5318
kinghenryv           | 5104
cymbeline            | 4875
troilusandcressida   | 4795
kinglear             | 4784
kingrichardiii       | 4713
2kinghenryvi         | 4683
coriolanus           | 4653
2kinghenryiv         | 4605
antonyandcleopatra   | 4582

8. GitHub データセットに対するクエリ

BigQuery の理解を深めるため、GitHub 一般公開データセットに対してクエリを発行します。最も一般的なコミットメッセージは GitHub で確認できます。また、BigQuery のウェブコンソールを使用して、アドホッククエリをプレビューして実行します。

データの内容を確認するには、BigQuery ウェブ UI で GitHub データセットを開きます。

github_repos テーブルを開きます

[プレビュー] ボタンをクリックして、データの外観を確認します。

bigquery_demo フォルダ内の app.py ファイルに移動し、コードを次のコードに置き換えます。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

コードを 1 ～ 2 分ほど確認して、最も一般的なコミットメッセージについてテーブルがどのようにクエリされているかを確認します。

Cloud Shell に戻ってアプリを実行します。

python3 app.py

コミットメッセージとその発生回数のリストが表示されます。

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862

9. キャッシュ保存と統計情報

BigQuery はクエリの結果をキャッシュに保存します。その結果、後続のクエリの所要時間が短縮されます。クエリオプションを使用してキャッシュ保存を無効にすることもできます。BigQuery は、作成時間、終了時間、処理された合計バイト数など、クエリに関する統計情報も追跡します。

このステップでは、キャッシュ保存を無効にし、クエリに関する統計情報も表示します。

bigquery_demo フォルダ内の app.py ファイルに移動し、コードを次のコードに置き換えます。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
job_config = bigquery.job.QueryJobConfig(use_query_cache=False)
results = client.query(query, job_config=job_config)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

print('-'*60)
print(f'Created: {results.created}')
print(f'Ended:   {results.ended}')
print(f'Bytes:   {results.total_bytes_processed:,}')

コードについて、いくつか注意点があります。まず、QueryJobConfig を導入し、use_query_cache を false に設定することで、キャッシュ保存が無効になります。次に、ジョブオブジェクトからクエリに関する統計情報にアクセスしました。

Cloud Shell に戻ってアプリを実行します。

python3 app.py

以前と同様に、コミットメッセージとその発生回数の一覧が表示されます。また、クエリに関する統計情報も最後に表示されます。

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862
------------------------------------------------------------
Created: 2020-04-03 13:30:08.801000+00:00
Ended:   2020-04-03 13:30:15.334000+00:00
Bytes:   2,868,251,894

10. BigQuery へのデータの読み込み

独自のデータをクエリする場合は、データを BigQuery に読み込む必要があります。BigQuery は、Cloud Storage、他の Google サービス、他の読み取り可能なソースなど、さまざまなソースからのデータの読み込みをサポートしています。ストリーミング挿入を使用してデータをストリーミングすることもできます。詳細については、BigQuery へのデータの読み込みのページをご覧ください。

このステップでは、Cloud Storage に保存されている JSON ファイルを BigQuery テーブルに読み込みます。JSON ファイルは gs://cloud-samples-data/bigquery/us-states/us-states.json にあります。

JSON ファイルの内容を確認するには、Cloud Shell で gsutil コマンドラインツールを使用してダウンロードします。

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

米国の州のリストが含まれており、各州が別の行の JSON ドキュメントになっていることがわかります。

head us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

この JSON ファイルを BigQuery に読み込むには、bigquery_demo フォルダ内の app.py ファイルに移動し、コードを次のコードに置き換えます。

from google.cloud import bigquery

client = bigquery.Client()

gcs_uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json'

dataset = client.create_dataset('us_states_dataset')
table = dataset.table('us_states_table')

job_config = bigquery.job.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

load_job = client.load_table_from_uri(gcs_uri, table, job_config=job_config)

print('JSON file loaded to BigQuery')

コードが JSON ファイルを読み込み、データセットの下にスキーマを含むテーブルを作成する方法を 1 ～ 2 分ほどで確認します。

Cloud Shell に戻ってアプリを実行します。

python3 app.py

BigQuery にデータセットとテーブルが作成されます。

データセットが作成されたことを確認するには、BigQuery コンソールに移動します。新しいデータセットとテーブルが表示されます。テーブルの [プレビュー] タブに切り替えて、データを表示します。

11. 完了

Python で BigQuery を使用する方法を学びました。

クリーンアップ

このチュートリアルで使用したリソースについて、Google Cloud アカウントに課金されないようにする手順は次のとおりです。

Cloud コンソールで、[リソースの管理] ページに移動します。
プロジェクトリストで、プロジェクトを選択し、[削除] をクリックします。
ダイアログでプロジェクト ID を入力し、[シャットダウン] をクリックしてプロジェクトを削除します。

詳細

Google BigQuery: https://cloud.google.com/bigquery/docs/
Google Cloud 上の Python: https://cloud.google.com/python/
Python 用 Cloud クライアントライブラリ: https://googleapis.github.io/google-cloud-python/

ライセンス

この作業はクリエイティブ・コモンズの表示 2.0 汎用ライセンスにより使用許諾されています。

Python での BigQuery の使用

1. 概要

学習内容

必要なもの

アンケート

このチュートリアルをどのように使用されますか？

Python のご利用経験はどの程度ありますか？

Google Cloud サービスの使用経験はどの程度ありますか？

2. 設定と要件

セルフペース型の環境設定

Cloud Shell の起動

Cloud Shell をアクティブにする

3. API を有効にする

4. API リクエストを認証する

5. アクセス制御を設定する

6. クライアント ライブラリをインストールする

7. シェイクスピア作品のクエリ

8. GitHub データセットに対するクエリ

9. キャッシュ保存と統計情報

10. BigQuery へのデータの読み込み

11. 完了

クリーンアップ

詳細

ライセンス

6. クライアントライブラリをインストールする