Cloud Composerから、Google Sheetsが外部データソースとして定義されているBigQuery Tableに対するアクセスする

これはなに

• BigQueryでは、Google Sheetsを外部データソースとして定義したtableを作成し、クエリを発行することができる（参考リンク）
• その際、CloudComposer側で、BigQueryOperatorを使う場合、権限周りで意識しないといけないことがあるので、その点についてのメモである。

起こったこと

CloudComposer(Airflow)のDAGで、BigQueryOperatorを使い、Google Sheetsを外部データソースとして定義したBigQuery Tableにクエリを発行しようとしたところ、以下のようなエラーになった。

ERROR - BigQuery job failed. Final error was: {'reason': 'invalid', 'location': 'xxx', 'message': 'Error while reading table: foo-project.bar-dataset.foobar_table, error message: Failed to read the spreadsheet. Errors: No OAuth token with Google Drive scope was found.'}

以下の点は確認したが、問題なかった。

• BigQuery OperatorはCloud Composerの環境作成時に指定されていたService Accountを利用して、BigQuery（かつ今回はGoogle Sheetsにアクセスを行う）
• Cloud Composer 環境を作成するときに指定していたService AccountをGoogle Sheetsの共有設定にはちゃんと追加していた。

原因と対策案

• いろいろ試行錯誤した結果GCPのCloud API アクセス スコープの指定の問題ではないかという結論に至った。
• 例えば、GCEインスタンス作成時には、Service Accountとアクセススコープの指定を行う
  • 例:https://qiita.com/shase428q/items/87f87d88c68efaede8d7
• CloudComposerを作ったときにはそういった指定をしていなかった。アクセススコープの指定をすればよいのでは？（案1）
• また、BigQuery Operatorでは、AirflowのConnectionを指定することができる。適切なScopeを定義したコネクションを作成し、BigQuery Operatorで指定すれば、良いのではないか？（案２）

参考：案2のConnectionの設定画面

結論としては、今回は案1でうまくいったので案2を試していない

案1:CloudComposer作成時に適切なScope設定をする

既存のComposerの設定をUpdateできればそれが良いのだが、gcloudコマンドのリファレンスによると

--oauth-scopes=[SCOPE,…]
The set of Google API scopes to be made available on all of the node VMs. Defaults to ['https://www.googleapis.com/auth/cloud-platform']. Cannot be updated.

とあるので、
gcloud composer environments create
で、oauth-scopes 指定できるけど
gcloud composer environments update
では指定できないらしい...

というわけで、以下のようなコマンドで環境を再作成してみた。

gcloud composer environments create foo-environments \
  --location asia-northeast1 \
  --zone asia-northeast1-a \
  --image-version composer-1.11.1-airflow-1.10.9 \
  --python-version 3 \
  --project foo-project \
  --node-count 3 \
  --machine-type=n1-standard-1 \
  --service-account [e-mail] \
  --oauth-scopes=https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/drive \
  --async

^ --oauth-scopes=https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/drive \ の部分が重要

まとめ

• これで作成したCloud ComposerのBigQuery Operatorで無事、Google Sheetsを外部データソースとして定義したBigQuery Tableにクエリを発行できるようになった。
• （環境が作り直しできないケースでは案2を試してみたいとは思っている）

補足1: ComposerのGKEのdefault-poolの入れ替えを行ったところ、Google Sheetsに接続できなくなった件

現象

Composer環境をスケールさせようと思い（強いインスタンスに変更しようと） この手順 に従って、default-poolの入れ替えを行った所、Google Sheetsを参照しているBigQueryテーブルに接続できなくなった。

以下のような雑なエラーメッセージしか表示されず結構難儀した。

error message: Failed to read the spreadsheet. Error code: PERMISSION_DENIED

原因

上記手順で作業すると、 default-poolを構成する、インスタンスのScopeに https://www.googleapis.com/auth/drive が設定されなかった。

gcloud container node-pools describe default-pool --cluster=foobar

^ 上記で確認したときに、あるべきoauthScopesは以下 V だが

config:
（略）
  oauthScopes:
  - https://www.googleapis.com/auth/cloud-platform
  - https://www.googleapis.com/auth/drive

実際は以下 V のように https://www.googleapis.com/auth/drive が無くなっていた。

config:
（略）
  oauthScopes:
  - https://www.googleapis.com/auth/cloud-platform

対策

少なくともConsole上からは、 oauthScopes の指定をしたdefault-poolの作成ができないので、gcloud コマンドでdefault-poolの入れ替えをする

既存default-poolの削除

gcloud container node-pools delete default-pool --cluster=foobar \
--async

新規default-poolの作成
（大事なのは --scopes=https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/drive \ のところ）

gcloud container node-pools create default-pool --cluster='foobar' \
  --disk-size=100 \
  --disk-type=pd-standard \
  --image-type=cos \
  --machine-type=n1-standard-1 \
  --preemptible \
  --metadata disable-legacy-endpoints='true' \
  --scopes=https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/drive \
  --service-account=foobar \
  --shielded-integrity-monitoring \
  --num-nodes=3 \
  --node-locations=asia-northeast1-a \
  --enable-autorepair \
  --enable-autoupgrade \
  --max-surge-upgrade=1 \
  --max-unavailable-upgrade=0 \
  --node-version=1.15.12-gke.2