サービスアカウントに Spreadsheet を BigQuery の外部テーブルとしてクエリする権限を渡す

以前に nodejs からサービスアカウントの権限でドライブデータをCSVで受け取る方法をメモったが、BigQueryの外部テーブルとして bq コマンドからクエリする場合はもう一手間必要だったようなのでメモっておく。

ただし、新規の外部テーブルを追加したときにでた権限エラーだったので、ひょっとしたらサービスアカウントの権限というよりも、新規テーブルを追加するたびに必要な工程だったのかもしれない。

権限が足りなくて弾かれる様子

$ bq query "$(cat ~/GoogleDrive/tmp/myapp/sql/_.sql)"
BigQuery error in query operation: Error processing job 'bqjob_r21c2ba7b22b1d0be_000001858b2d32e2_1': Access Denied: BigQuery BigQuery: Permission denied while getting
Drive credentials.

前提

• サービスアカウントで認証してGoogleSpreadsheetからデータを取得 の手順が済んでいる
  • spreadsheet 側でのサービスアカウントのメアドへの共有設定は済んでいる
  • GCPプロジェクトのDriveAPIが有効になっている
• サービスアカウントのクレデンシャルで bq コマンドを使おうとしている
  • 今回は $GOOGLE_APPLICATION_CREDENTIALS が環境変数に設定されている

こんなコマンドでサービスアカウントのクレデンシャルにドライブへのアクセスを追加している。

$ gcloud auth login --cred-file=$GOOGLE_APPLICATION_CREDENTIALS --enable-gdrive-access

うまくいくとこんな出力が返る。

Authenticated with service account credentials for: [app-rw@myapp-123456.iam.gserviceaccount.com].
Your current project is [myapp-123456].  You can change this setting by running:
  $ gcloud config set project PROJECT_ID

これで問題なくクエリできるようになった。

$ bq query "$(cat ~/GoogleDrive/tmp/myapp/sql/_.sql)" | head -1
+----------+------------------------------+------+---------------------+------------+--------+---------+---------+---------------------+--------------+------------+-------------+----------------+
Exception ignored in: <_io.TextIOWrapper name='<stdout>' mode='w' encoding='UTF-8'>
BrokenPipeError: [Errno 32] Broken pipe

参考

• ドライブデータをクエリする  |  BigQuery  |  Google Cloud
• gcloud auth login  |  Google Cloud CLI Documentation