事象

先日こんなことがありました。

BigQuery に Google スプレッドシートを参照する外部テーブルがある
その外部テーブルを参照して別のテーブルを更新したい
BigQuery のジョブは Workflows から発行する

図にするとこんな感じです。

ワークフローを実行すると、クエリが失敗。
アクセスが拒否されたとのこと。

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

試しにコンソールから自分でクエリを実行してみると成功します。
みなさんは真っ先にこう考えるでしょう。
「サービスアカウントにスプレッドシートの閲覧権限がないんでしょ」と。

確かに、ワークフローを動かすサービスアカウントにスプレッドシートの閲覧権限をつけなければなりません。
そうでないとクエリ実行時に外部テーブルを通してスプレッドシートにアクセスができないのです。

しかし今回はきちんとサービスアカウントに閲覧権限がついています。

原因

もう一度エラー文を見てみましょう。

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

ドライブの資格情報を取得中にアクセスが拒否されました。

そう、スプレッドシートではなく Google Drive へアクセスできていなかったのです。

スプレッドシートは Google Drive 上に存在します。
外部からアクセスするには、Google Drive API を通さなければならないようです。

対処法

これは修正前のワークフローの yaml です。

main:
  steps:
    # 1. GCSからSQLファイルを取得する
    - get_sql_file:
        call: googleapis.storage.v1.objects.get
        args:
          bucket: "sql_for_workflow"
          object: "replace_table.sql"
          alt: "media"
        result: sql_file_data

    # 2. SQLをデコードする
    - decode_sql:
        assign:
          - sql_query: ${text.decode(sql_file_data)}

    # 3. BigQuery でクエリを実行する
    - run_bigquery:
        call: googleapis.bigquery.v2.jobs.insert
        args:
          projectId: ${sys.get_env("PROJECT_ID")}
          body:
            configuration:
              query:
                query: ${sql_query}
                useLegacySql: false
            jobReference:
              location: "asia-northeast1"
        result: query_result

BigQuery から Google Drive へのアクセスできるようにコネクタのスコープを明示的に指定します。

https://www.googleapis.com/auth/bigquery : BigQuery API にアクセスするためのスコープ
https://www.googleapis.com/auth/drive.readonly : Google Drive の読み取り専用アクセスを許可するスコープ

スコープは他にも色々あり、適切なものを選びます。詳細はこちらで確認できます。

修正後の yaml

main:
  steps:
    # 1. GCSからSQLファイルを取得する
    - get_sql_file:
        call: googleapis.storage.v1.objects.get
        args:
          bucket: "sql_for_workflow"
          object: "replace_table.sql"
          alt: "media"
        result: sql_file_data

    # 2. SQLをデコードする
    - decode_sql:
        assign:
          - sql_query: ${text.decode(sql_file_data)}

    # 3. BigQuery でクエリを実行する
    - run_bigquery:
        call: googleapis.bigquery.v2.jobs.insert
        args:
          projectId: ${sys.get_env("PROJECT_ID")}
          body:
            configuration:
              query:
                query: ${sql_query}
                useLegacySql: false
            jobReference:
              location: "asia-northeast1"
          # 次の2行を追加
          connector_params:
            scopes: "https://www.googleapis.com/auth/bigquery, https://www.googleapis.com/auth/drive.readonly"
        result: query_result