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

# これはなに
- BigQueryでは、Google Sheetsを外部データソースとして定義したtableを作成し、クエリを発行することができる（[参考リンク](https://cloud.google.com/bigquery/external-data-sources?hl=ja)）
- その際、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の設定画面
![スクリーンショット 2020-08-15 2.22.40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147174/67cf214c-7111-166a-f5fe-308d2066f096.png)

**結論としては、今回は案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 foobar@foo-project.iam.gserviceaccount.com \
  --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環境をスケールさせようと思い（強いインスタンスに変更しようと） [この手順](https://cloud.google.com/composer/docs/how-to/managing/updating?hl=ja#upgrading_the_machine_type_for_gke_nodes) に従って、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
```