PythonでBigQueryへのアクセス、データ操作を行う方法

BigQueryをDWHとして活用する場合、

アプリケーションやバッチプログラムからBigQueryにアクセスしてデータを取り出したり、更新を行えたら便利ですよね。

Google Cloudプロジェクト > APIとサービス > ライブラリ

より、BigQueryと検索すると、BigQuery APIが表示されるので、「有効にする」をクリックして有効化しましょう。

サービスアカウントは、IAMと管理 > サービスアカウント > サービスアカウントの作成

より作成できます。

サービスアカウントの詳細ページの、キー > 鍵を追加　よりJSON形式の鍵ファイルを作成。鍵ファイルは任意の場所に保存し、外部の人間がアクセスできないように注意しましょう。

PythonはPython3、AnacondaなどのPython3環境を準備しておきましょう。

$ pip install google-cloud-bigquery

サービスアカウントの鍵ファイルはプログラムと同階層のフォルダ内に格納します。

import os

os.environ[‘GOOGLE_APPLICATION_CREDENTIALS’] = “xxx.json”

次に以下のコードでクライアントを作成します。（xxxにはBigQuery APIを有効化したプロジェクト名が入ります。）

from google.cloud import BigQuery

client = bigquery.Client(project=”xxx”)

BigQueryに新しいテーブルを作成するには以下のコードを書きます。

clientは前述したBigQueryクライアントです。

# テーブルスキーマを定義する

schema = [

bigquery.SchemaField(“id”, “STRING”, mode=”REQUIRED”),

bigquery.SchemaField(“name”, “STRING”, mode=”REQUIRED”),

bigquery.SchemaField(“age”, “INTEGER”, mode=”REQUIRED”),

]

# 新しいテーブルを作成する

table_id = “my_dataset.my_table”

table = bigquery.Table(table_id, schema=schema)

table = client.create_table(table) # テーブルが既に存在する場合は例外が発生する

print(f”Created table {table_id}”)

BigQueryにクエリを実行するには以下のようなコードを書きます。

SELECT文でデータを読み取る以外にもUPDATEやDELETEなど更新処理も可能です。

# クエリを定義する

query = “””

SELECT *

FROM my_dataset.my_table

WHERE age >= 18

“””

# クエリを実行する

query_job = client.query(query)

# クエリ結果を処理する

for row in query_job:

print(row)

# データを定義する

rows_to_insert = [

(“1”, “Alice”, 25),

(“2”, “Bob”, 30),

(“3”, “Charlie”, 35),

]

# データを書き込む

table_id = “my_dataset.my_table”

table = client.get_table(table_id)

errors = client.insert_rows(table, rows_to_insert) # 書き込みに失敗した場合はエラーが返される

if errors == []:

print(“Rows inserted successfully.”)

else:

print(“Error inserting rows:”, errors)

from google.cloud import bigquery

import pandas as pd

# クエリを定義する

query = “””

SELECT *

FROM my_dataset.my_table

“””

# クエリを実行してデータを取得する

query_job = client.query(query)

results = query_job.result()

# 結果をPandasのDataFrameに変換する

df = pd.DataFrame(

[(row[“id”], row[“name”], row[“age”]) for row in results],

columns=[“id”, “name”, “age”],

)

print(df.head())

BigQueryのテーブルからデータを読み込んでCSVファイルとして出力するには以下のようなコードを書きます。

BigQueryのコンソール画面からCSVファイルをエクスポートするのと同じ要領でプログラム上でも実現できます。

from google.cloud import bigquery

import csv

# クエリを定義する

query = “””

SELECT *

FROM my_dataset.my_table

“””

# クエリを実行してデータを取得する

query_job = client.query(query)

results = query_job.result()

# 結果をCSVファイルに書き込む

with open(“output.csv”, “w”, newline=””) as csvfile:

writer = csv.writer(csvfile)

writer.writerow([“id”, “name”, “age”])

for row in results:

writer.writerow([row[“id”], row[“name”], row[“age”]])

print(“Data written to output.csv.”)

「BigQuery/BIツールを導入したい」

「すでに導入しているがデータ活用、展開が進んでいない」など