Dbt: Include Drive Scope with BigQuery OAuth connection

[PowdyPowPow]

Include Drive Scope with BigQuery OAuth connection

Issue description

When using OAuth with BigQuery dbt will fail when working with external federated table (Google Sheets/Drive files). The issue with OAuth and external tables seems to be that the Drive API is not explicitly set as the scope of the gcloud SDK.

Google docs with the code sample:
https://cloud.google.com/bigquery/external-data-drive#enable-google-drive

Some extra discussions:
https://github.com/googleapis/google-cloud-python/issues/3067

Results

Running a simple dbt model select * from external_table will produce the following error: Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

System information

The output of dbt --version:

installed version: 0.13.1
   latest version: 0.13.1

The operating system you're running on:
macOS 10.13.6

The python version you're using (probably the output of python --version)
3.7.3

Steps to reproduce

profiles.ymlsetting:

bigquery:
  target: dev
  outputs:

    dev:
      type: bigquery
      method: oauth

[drewbanin]

Thanks for the report @PowdyPowPow. Copy/pasting the results of my investigation from Slack:

This came up internally last week and I took another look at the state of the world. We were able to get this working using separate service accounts, but I agree, it would be great if this worked over OAuth!

We’re actually (trying) to set scopes when we construct the BigQuery client: https://github.com/fishtown-analytics/dbt/blob/dev/wilt-chamberlain/plugins/bigquery/dbt/adapters/bigquery/connections.py#L63-L65

A lot of the examples in this issue aren’t actually that helpful. This one is for service accounts only, for instance: https://github.com/googleapis/google-cloud-python/issues/3067#issuecomment-364103069

I’d definitely love to get this working, but I’m pretty stumped!! If anyone has any experience setting up sheets access over oauth, I'd love to hear about it

[ciscodebs]

You can add Drive scope via OAuth. The Drive scope needs to be added at the end of the OAuth command.

Use:
gcloud auth application-default login --scopes=https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/drive.readonly

Notice the addition of “- -scopes” flag and “https://www.googleapis.com/auth/drive.readonly” at the end. That’s the additional scope. The other two scopes are the default scopes.

Now if your user has view access to a sheets file you should have no trouble running don’t locally.

I learned about the “- -scopes” flag here: https://cloud.google.com/sdk/gcloud/reference/auth/application-default/login

I found the Drive scope here: https://developers.google.com/identity/protocols/googlescopes#drivev3

@PowdyPowPow - Happy to help you debug if you run into any issues. Good luck.

@drewbanin - Could be useful to include this in dbt docs as another OAuth command (and potentially the default recommendation).

[drewbanin]

Thanks @ciscodebs! I just updated the dbt documentation with this information :)

[PowdyPowPow]

Thanks @ciscodebs
I was actually playing with the scopes but almost certainly never used the same combination as you suggest. I tried it just now and can confirm it works. Thank you.

[ciscodebs]

@drewbanin - since this was added to docs should it remain open?

[drewbanin]

@ciscodebs yep! Thanks for the heads up :)

[LubiBel]

I have used the commands to oauth for BigQuery. I can create data sets and tables in UI but when I run dbt then I get access denied. I just upgraded to 0.17 version. Any idea what could go wrong?

[drewbanin]

hey @LubiBel - did you follow the steps shown here? https://docs.getdbt.com/docs/supported-databases/profile-bigquery/#oauth-authorization

What does your access denied error message look like?

[techadmin-getsales]

hey @LubiBel - did you follow the steps shown here? https://docs.getdbt.com/docs/supported-databases/profile-bigquery/#oauth-authorization

This link is broken @drewbanin

[jtcohen6]

@techadmin-getsales Looks like it's here now: https://docs.getdbt.com/reference/warehouse-profiles/bigquery-profile/#oauth-authentication