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 サービスの使用経験はどの程度ありますか？

<ph type="x-smartling-placeholder"></ph> 初心者 中級 上達 をご覧ください。

2. 設定と要件

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

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

b35bf95b8bf3d5d8.png

a99b7ace416376c4.png

bd84a6d3004737c5.png

  • プロジェクト名は、このプロジェクトの参加者に表示される名称です。Google API では使用されない文字列で、いつでも更新できます。
  • プロジェクト ID は、すべての Google Cloud プロジェクトにおいて一意でなければならず、不変です（設定後は変更できません）。Cloud Console により一意の文字列が自動生成されます（通常は内容を意識する必要はありません）。ほとんどの Codelab では、プロジェクト ID を参照する必要があります（通常、プロジェクト ID は「PROJECT_ID」の形式です）。好みの文字列でない場合は、別のランダムな ID を生成するか、独自の ID を試用して利用可能であるかどうかを確認することができます。プロジェクトの作成後、ID は「フリーズ」されます。
  • 3 つ目の値として、一部の API が使用するプロジェクト番号があります。これら 3 つの値について詳しくは、こちらのドキュメントをご覧ください。
  1. 次に、Cloud のリソースや API を使用するために、Cloud Console で課金を有効にする必要があります。この Codelab の操作をすべて行って、費用が生じたとしても、少額です。このチュートリアルを終了した後に課金が発生しないようにリソースをシャットダウンするには、Codelab の最後にある「クリーンアップ」の手順を行います。Google Cloud の新規ユーザーは、300 米ドル分の無料トライアル プログラムをご利用いただけます。

Cloud Shell の起動

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

Cloud Shell をアクティブにする

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

55efc1aaa7a4d3ad.png

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

9c92662c6a846a5c.png

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

9f0e51b578fecce5.png

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

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

  1. 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`
  1. 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 で次のコマンドで確認できます。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 にはいくつかの事前定義ロール（user、dataOwner、dataViewer など）があり、前のステップで作成したサービス アカウントに割り当てることができます。アクセス制御の詳細については、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 の右上からコードエディタを開きます。

b648141af44811a3.png

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 テーブルを開く

[プレビュー] ボタンをクリックすると、データがどのように表示されるか確認できます。

d3f0dc7400fbe678.png

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 分かけてコードを学習し、最も一般的な commit メッセージがテーブルに対してどのように実行されるかを確認します。

Cloud Shell に戻り、アプリを実行します。

python3 app.py

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

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

前と同様に、commit メッセージとその発生回数のリストが表示されます。さらに、最後にクエリに関するいくつかの統計情報が表示されるはずです。

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 ファイルの内容については、gsutil コマンドライン ツールを使用して Cloud Shell でダウンロードできます。

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

米国の州のリストが含まれており、各州が 1 行に 1 つずつ 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')

2 分ほど時間を取って、このコードが JSON ファイルを読み込み、データセットの下にスキーマを使用してテーブルを作成する方法を学びましょう。

Cloud Shell に戻り、アプリを実行します。

python3 app.py

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

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

8c7d2621820a5ac4.png

11. 完了

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

クリーンアップ

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

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

詳細

ライセンス

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