はじめに

新田

こんにちは。
今回はBigQueryのテーブルの外部データとしてスプレッドシートを利用する方法をまとめたいと思います。
特にマスタ系のデータを扱うような場合はデータの更新が面倒ですが、この方法を使うことで更新が楽になりました。

外部データとするスプレッドシート

今回は例としてこのようなスプレッドシートを作成しました。

競馬の種牡馬のマスタデータのようなデータです。
stallion_nameは種牡馬の名前、bloodlineはその馬の血統の系統を表していて、country_typeは国タイプを表します。
毎年新たな種牡馬がデビューするのでその度に更新しなくてはなりません。

BigQueryのテーブル作成

さて、BigQueryにテーブルを作成しましょう。
テーブルを追加したいデータセットの三点リーダをクリック、「テーブルを作成」をクリックします。するとフォームが出てくるので入力していきます。

テーブルの作成元はドライブ、ドライブのURIはスプレッドシートのURL、ファイル形式はGoogleスプレッドシート。
シート範囲はシート名を入力するようにするといいです。
あとは大体csvから作成する場合と同じですね。
データセット名、テーブル名を入力、テーブルタイプは外部テーブルでOKです。

スキーマは、自動検出してもいいですが、手動で入力してREQUIREDなどの属性も指定してあげるのがおすすめです。
最後に、詳細オプションでスキップするヘッダー行を1に指定します。これはスプレッドシートでスキップするヘッダー行のことです。
全て入力できたら、テーブルを作成 のボタンを押します。
また、（1ブックではなく、）1シートにつき1テーブルを指定することができます。つまり１ブックで複数のマスタを定義したシートを管理できます。シート範囲に新しく作ったシート名を指定して同じようにするとよいです。

クエリを実行する

作成したテーブルに対してクエリを実行してみましょう。

スプレッドシートの内容をクエリで取得することができました！
指定したスキーマとスプレッドシートのデータで矛盾が発生しているような場合（例えば実際のデータの型が違うとか、REQUIREDを満たしていないとか）はエラーになります。そのような場合はデータを修正しましょう。
スキーマに問題がある場合はスキーマを編集しても構いませんが、型を変えられなかったりフィールドを削除できなかったりするので何かおかしなことになっている場合はテーブルを削除してもう一度作り直すのが確実です。

スプレッドシートへの変更は即時反映される

今度はスプレッドシートに1行追加してみます。

先ほどと同じクエリを実行すると...

スプレッドシートに追加した内容がクエリ結果でも取得できました！

BigQueryでDriveの権限のエラーが出る場合

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

作成したテーブルに対してクエリを実行したときに、このようなエラーが出ることがあります。
これはクエリを実行したユーザーにスプレッドシートを閲覧する権限がない場合に起こります。
スプレッドシート右上の共有ボタンからこのエラーが出たときに実行していたユーザーに閲覧者権限をつけてあげてください。エラーが出なくなるはずです。

Airflow(Composer)から実行する場合

Airflowからクエリを実行している際のユーザー（サービスアカウント）にスプレッドシートの共有ができていても同様のエラーが起こることがあります。

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

AirflowのConnectionsの機能を利用する必要があります。
AirflowのWebUIからAdmin > Connectionsに以下のように追加します。

Airflow側で追加ができたら、今度は実行しているDAGに設定を追加します。Operatorの引数 gcp_conn_idに先ほど設定したConnection Idを指定します。

1BigQueryInsertJobOperator(
2  task_id=task_id, configuration=configuration, params=params,
3  dag=dag, gcp_conn_id="google_sheet"
4)

以上のようにすることで、Airflowからスプレッドシートに外部データを持つテーブルに対してクエリを実行することができるようになります。

おわりに

今回はBigQueryのテーブルの外部データとしてスプレッドシートを利用する方法をまとめました。
権限周りの設定が少し面倒なこともあるかもしれませんが、人が入力しやすいスプレッドシートへの変更がすぐにBigQueryのテーブルに反映されるのはとても便利ですね。