bigquery

SQL入門 – 8)PythonからBigQueryを利用

kenichiro

Python API

SQL入門の最終回です。今回はPythonからBigQueryを利用してみます。google-cloud-bigqueryモジュールをインストールすることでPythonからBigQueryを利用することができます。これまでコンソールからSQLを実行して結果を見てきましたが、BigQueryは様々なサービスと連携することはもちろん、いろいろな言語からアクセスすることも可能です。

本記事はFuture Coders独自教材からの引用です。

最初に必要なモジュールをインストールします。


pip install --upgrade google-cloud-bigquery

次に、gcloud CLI コマンドを使ってログイン、プロジェクトの選択を行います。

gcloud CLI コマンドは以下のURLからインストールしてください。CLIとはCommand Line Interfaceの略で、コマンドプロンプトやターミナルなどから命令を実行して、Googleのクラウドサービスをいろいろ管理することができます。

https://cloud.google.com/sdk/docs/install?hl=ja

gcloud CLIをインストールしたら最初にログインをします。以下のコマンドを実行してください。ブラウザが起動して、アカウントを選択し、許可ボタンを押下することでログインします。

gcloud auth login

python2

image 94
SQL入門 – 8)PythonからBigQueryを利用 22

現在選択中のプロジェクトが表示されています。

ログアウトするには以下のコマンドを実行します。

gcloud auth revoke

次に、CLIで対象とするプロジェクトを確認・選択します。プロジェクト一覧を確認するには、gcloud projects list コマンドを実行します。

c:\tmp>gcloud projects list
PROJECT_ID                      NAME                     PROJECT_NUMBER
...
sql-study-377210                sql-study                556222425142

このPROJECT_IDの値はプロジェクトを特定する大切な値です。GCPコンソールからも確認できます。

image 95
SQL入門 – 8)PythonからBigQueryを利用 23

複数のプロジェクトを作成している場合は、その中から作業対象とするプロジェクトを選択します。 作業対象のプロジェクトを変更するには以下のコマンドを実行します。PROJECT_IDは選択対象のIDを指定してください。

gcloud confing set project PROJECT_ID

PythonからBigQueryにアクセスができるか確認するため、以下のプログラムを実行してください。query変数の文字列は適宜自分のプロジェクトID、データセット、テーブル名に置き換えてください。

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = """
    SELECT *
    FROM sql-study-377210.sample.people
"""
query_job = client.query(query)  # Make an API request.

print("The query data:")
for row in query_job:
    # Row values can be accessed by field name or index.
    print(f'row[0]={row[0]}, gender={row["gender"]}, email={row["email"]}')

設定が正しく行われていれば、以下のようにテーブルの内容が列挙されます。

image 96
SQL入門 – 8)PythonからBigQueryを利用 24

ログインが正しく行われていないと以下のようなエラーとなります。gcloud auth loginコマンドを実行してログインしてください。

python4

image 97
SQL入門 – 8)PythonからBigQueryを利用 25

以下のように意図せぬプロジェクトIDを参照してしまう場合があるかもしれません。その際は以下のようなエラーが発生します。 google.api_core.exceptions.BadRequest: 400 POST https://bigquery.googleapis.com/bigquery/v2/projects/sql-study-377210/jobs?prettyPrint=false: Project ‘project:gcp-study-373108’ not found

このときは、以下のコマンドを実行してアプリケーションのデフォルト設定を変更する必要があります。

gcloud auth application-default login

SQLの実行

PythonでBigQueryにアクセスするときは以下のような手順を実行します。

  1. from google.cloud import bigquery でモジュールをインポート
  2. client = bigquery.Client() でクライアントオブジェクトを作成
  3. クエリを作成(変数queryに代入)
  4. query_job = client.query(query) でクエリを実行
  5. 戻り値から結果を取得

Peopleテーブルから一覧を取り出すサンプルです。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT concat(name_first, " ", name_last) as name,
    dob_age as age
    FROM sql-study-377210.sample.people limit 7
"""
query_job = client.query(query) 

for row in query_job:
    print(row)

出力は以下の通りです。

Row(('John Robinson', 59), {'name': 0, 'age': 1})
Row(('Fletcher Anderson', 65), {'name': 0, 'age': 1})
Row(('David Cooper', 75), {'name': 0, 'age': 1})
Row(('Cooper Robinson', 67), {'name': 0, 'age': 1})
Row(('James Zhang', 63), {'name': 0, 'age': 1})
Row(('Caleb Wright', 43), {'name': 0, 'age': 1})
Row(('Archie Smith', 61), {'name': 0, 'age': 1})

queryの戻り値（変数query_job）はイテレータです。for文などで個々の行を取り出します。取り出した行はgoogle.cloud.bigquery.table.Row型のデータです。

https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.table.Row

以下のようなメソッドやプロパティで個々のデータにアクセスできます。

  • Row.get(列名) = 列の値
  • Row[列名] = 列の値
  • Row[インデックス] = 列の値
  • row.keys() = 列名の一覧
  • row.values() = 値の一覧
  • row.items() = 列名と値のタプルのイテレータ

python5

image 98
SQL入門 – 8)PythonからBigQueryを利用 26

検索結果をデータフレームに変換

BigQueryのテーブルは簡単にデータフレームに変換できます。以下のモジュールをインストールしてください。

pip install db_dtypes
image 99
SQL入門 – 8)PythonからBigQueryを利用 27
from google.cloud import bigquery
import pandas as pd

client = bigquery.Client()

query = """
    SELECT * FROM sql-study-377210.sample.people
"""
df = client.query(query).to_dataframe() 
print(df.head())

出力は以下の通りです。

python7{width=100%}

image 100
SQL入門 – 8)PythonからBigQueryを利用 28

query()メソッドの戻り値に対して to_dataframe メソッドを呼び出すだけです。Pandasで検索・加工などの処理をしたい場合は、このようにデータフレームに変換してから作業をするとよいでしょう。

データフレームをBigQueryにアップロード

データフレームをアップロードするときには、データフレームの各列のデータ型（スキーマ）を明示的に指定します。今回はseabornにあるサンプルデータセットirisをアップロードしてみます。まず、もとのデータの型や内容を確認しておきます。

import seaborn as sns
df = sns.load_dataset("iris")
print(df.info())
print(df.head())

出力は以下の通りです。

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 150 entries, 0 to 149
Data columns (total 5 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   sepal_length  150 non-null    float64
 1   sepal_width   150 non-null    float64
 2   petal_length  150 non-null    float64
 3   petal_width   150 non-null    float64
 4   species       150 non-null    object 
dtypes: float64(4), object(1)
memory usage: 6.0+ KB
None
   sepal_length  sepal_width  petal_length  petal_width species
0           5.1          3.5           1.4          0.2  setosa
1           4.9          3.0           1.4          0.2  setosa
2           4.7          3.2           1.3          0.2  setosa
3           4.6          3.1           1.5          0.2  setosa
4           5.0          3.6           1.4          0.2  setosa

以下は、このデータ構造（スキーマ）を指定してデータフレームをアップロードするサンプルです。

from google.cloud import bigquery
import pandas as pd
import seaborn as sns
df = sns.load_dataset("iris")

client = bigquery.Client()

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("sepal_length", "FLOAT64"),
        bigquery.SchemaField("sepal_width", "FLOAT64"),
        bigquery.SchemaField("petal_length", "FLOAT64"),
        bigquery.SchemaField("petal_width", "FLOAT64"),
        bigquery.SchemaField("species", "STRING")
    ]
)

r = client.load_table_from_dataframe(df, 
  "sql-study-377210.sample.iris", 
  job_config=job_config).result()
print(r)

SchemaFieldでは最初の引数に列名、次の引数にデータ型を記述します。STRING, BOOL, INT64, FLOAT64, DATE, DATETIMEなどの値を指定できます。他にもカラムの説明やNULLを受け入れるか否かなど細かい設定が可能です。

出力は以下の通りです。読込みJOBのオブジェクトが返されます。

LoadJob<project=sql-study-377210, location=asia-northeast1, id=ba9c0cdf-eac5-457e-a863-780b1ce52059>

コンソールを確認するとirisがBigQueryにアップロードされていることが確認できます。

image 101
SQL入門 – 8)PythonからBigQueryを利用 29

LoadJobConfigのwrite_dispotion引数を指定することで、書込みモードを指定することも可能です。

  • write_disposition=’WRITE_EMPTY’ … 同名のTableがある時に作成しない
  • write_disposition=’WRITE_TRUNCATE’　… 同名のTableがある時に上書きする
  • write_disposition=’WRITE_APPEND … 同名のTableがある時に追加する

演習

ex-python1.py

squirrel-dataからPark_IDが1のレコードのみを検索し、その結果(Area_Name, Squirrel_ID, Highlights_in_Fur_Color)を出力してください。

UPPER MANHATTAN, A-01-08, Gray
UPPER MANHATTAN, A-01-01, White
UPPER MANHATTAN, A-01-02, White
...

ex-python2.py

ex-python1.pyの内容をDataFrameにして、データフレームを以下のように出力してください。

         Area_Name Squirrel_ID Highlights_in_Fur_Color
0  UPPER MANHATTAN     A-01-08                    Gray
1  UPPER MANHATTAN     A-01-01                   White
2  UPPER MANHATTAN     A-01-02                   White
3  UPPER MANHATTAN     A-01-03                   White
4  UPPER MANHATTAN     A-01-04                   White

ex-python3.py

seabornのデータセットからflightsの内容をBigQueryにアップロードしてください。

解答例

ex-python1.py

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT *
    FROM `sql-study-377210.sample.squirrel-data`
    where Park_ID = 1
"""
query_job = client.query(query) 

for r in query_job:
    print(f"{r['Area_Name']}, {r['Squirrel_ID']}, {r['Highlights_in_Fur_Color']}")

ex-python2.py

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT *
    FROM `sql-study-377210.sample.squirrel-data`
    where Park_ID = 1
"""
df = client.query(query).to_dataframe()

print(df[['Area_Name', 'Squirrel_ID', 'Highlights_in_Fur_Color']].head())

ex-python3.py

from google.cloud import bigquery
import pandas as pd
import seaborn as sns
df = sns.load_dataset("flights")

client = bigquery.Client()

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("year", "INT64"),
        bigquery.SchemaField("month", "STRING"),
        bigquery.SchemaField("passengers", "INT64"),
    ]
)

r = client.load_table_from_dataframe(df, "sql-study-377210.sample.flights", job_config=job_config).result()
print(r)
Categories: Programming, SQL