BigQueryでGoogleドライブデータへのクエリでエラーが出るときの対処

基本的には下記の公式ドキュメントに記載されている通りに進めれば大丈夫なはずです。

• Google ドライブデータのクエリ

しかしあるエラーに悩まされて結構時間を消費してしまったので、対処方法をメモしておきます。
もし間違った部分があればコメントでご指摘いただけると幸いです。

エラー発生まで

今回、あるGoogleスプレッドシートを外部データソースとしたBigQueryテーブルが必要でした。
このテーブルを作ること自体は簡単で、スプレッドシートを閲覧できるGoogleアカウントでGCPコンソールにログインし、BigQueryのUIから以下のように作成できます。（ここでは適当なダミーファイルを使っています）

このテーブルに対してそのままGCPコンソール上でクエリを実行することは可能ですが、bqコマンドや各種APIからクエリを実行すると、以下のようなエラーが発生しました。（GCPプロジェクト名とデータセット名は隠しています）

$ bq query --nouse_legacy_sql 'select * from `[project].[dataset].test`'

Waiting on bqjob_r6ec7dc603884dd48_00000177a442f5bd_1 ... (0s) Current status: DONE
BigQuery error in query operation: Error processing job '[project]:bqjob_r6ec7dc603884dd48_00000177a442f5bd_1': Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

このAccess Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.というエラーメッセージを解決していきます。

bqコマンドの場合

冒頭にリンクを載せた公式ドキュメントにあるように、以下のコマンドを実行してGoogleドライブの認証を行う必要があります。

$ gcloud auth login --enable-gdrive-access

このコマンドを実行すると自動的にブラウザが開いて認証画面に遷移しますが、オプションなしの場合と比較するとGoogleドライブへのアクセス権限が含まれていることが確認できます。

オプションなし	オプションあり

なお、再現はできていないのですが、この認証を行なってすぐにはまだ同じエラーが発生する場合があります。時間をおいてみるか、あるいは一度bqコマンドに--enable_gdriveというオプションをつけてクエリを実行してみると解決するかもしれません。一度認証が通ればエラーは発生しないようです。

ユーザーアカウントでAPIを使う場合

冒頭にリンクを載せた公式ドキュメントには、以下のPythonサンプルコードがあります。

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)

コメントに記載されているように事前にGoogleDriveAPIを有効化しておく必要はありますが、ここではすでに有効化されています。これをそのまま実行してからクエリを実行してみると、やはり同じエラーが発生します。（エラーメッセージは適当に省略しています）

>>> client.query("select * from `[project].[dataset].test`").result()

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

この場合の解決方法は、事前に以下のコマンドを実行してApplication Default Credentials（ADC）をGoogleドライブのスコープ付きで作成しておくことです。ADCの場合は、先ほどの--enable-gdrive-accessというオプションは存在しません。

$ gcloud auth application-default login \
    --scopes="https://www.googleapis.com/auth/drive","https://www.googleapis.com/auth/cloud-platform"

ブラウザの認証画面をスコープを指定しない場合と比較すると、Googleドライブへのアクセス権限が含まれていることが確認できます。

デフォルトのスコープ	＋Googleドライブのスコープ

ここからは推測ですが、pythonのgoogle.auth.default()の関数で指定するscopes引数は、事前にADCに含まれたスコープの中でどれを使用するか制限をかけるのみでスコープを追加できるものではないのだと思います。

• google.auth package

サービスアカウントでAPIを使う場合

まず、データソースとするGoogleスプレッドシートの共有設定として、サービスアカウントのIDとなるメールアドレスに閲覧権限を付与します。
サービスアカウントに与える必要があるGCP上の権限については、冒頭に載せた公式ドキュメントを参照してください。
サービスアカウントの場合は、ここまで準備すれば公式ドキュメントにあるサンプルコードが問題なく動きます。

終わりに

今回背景にあるやりたかったことはCloud FunctionsからBigQueryのクエリを実行してGoogleスプレッドシートにあるデータを取得することだったので、実はサービスアカウントでAPIを使う場合で十分でした。それに気づかずあれこれ調べて遠回りしてしまったのですが、ADCについて理解が進んだ気がします。