Allow the Stata driver access to Google Drive files

[Dimitriy V. Masterov]

I am trying to load a Big Query table that is based on a Google Spreadsheet into Stata. I am using the Simba ODBC driver for Mac (version 2.1.6), but I get the following error from a simple "select * from table" query:
The ODBC driver reported the following diagnostics [Simba][BigQuery] (20) Query execution failed: Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found. SQLSTATE=HY000
In my odbc.ini file, I have set RequestGoogleDriveScope=1, and am using user authentication for OAuth Mechanism.

I can query this table in Big Query web user interface and in R, and I can load other non-GS tables via ODBC in Stata. I think I need to give Stata/Simba/iodbc permission in https://myaccount.google.com/permissions (like BQ Client Tools and bigquery R has), but I am not sure how to do that or even that it necessary.

I would love to know what I am missing here.

Cross-posted here.

[Dimitriy V. Masterov]

Actually, this does not work in R either:

> library(bigrquery)
> data <- query_exec("SELECT * FROM my_table;", project = "XXXX", useLegacySql = FALSE)
Error: Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

To summarize, this query works in BQ web UI, and does not work with R (using the API) or Stata (using ODBC). The common denominator is that neither Stata or R have google drive permissions in https://myaccount.google.com/permissions, whereas the BQ Client Tools has them. That is my best guess as to where the problem lies. I guess the question is how to give Stata such permissions, if that is possible.

[Sergiy Radyakin]

Dimitriy,
see if this thread helps.
Best, Sergiy

[Dimitriy V. Masterov]

Thanks for your suggestion. I tried that approach before posting my question, but it led to:

WARNING  `gcloud auth login` no longer writes application default credentials.
If you need to use ADC, see:
  gcloud auth application-default --help

Then I tried to use the Application Default Credentials (ADC) approach, but it only allows you to create a service account and have your application use it for API access, whereas I am using user authentication for Big Query. There is an option to temporarily use your own user credentials, but this takes you through a web flow to acquire new user credentials, which I am eager to avoid. I could certainly be missing something here.

[wbuchanan]

Dimitriy V. Masterov
are you somehow passing the OAuth token and key through the ODBC driver config? I’m not completely sure if ODBC is aware of how to authenticate using OAuth, but perhaps that is where the problem lies?

[Dimitriy V. Masterov]

wbuchanan

There's a setting in my odbc.ini file

# OAuth Mechanism: The OAuth mechanism to use. There are two choices:
# 0 = Service Authentication
# 1 = User Authentication
#
# This is a required setting.
OAuthMechanism=1

If I understood the documentation correctly, the OAuth 2.0 authentication mechanism is to authenticate the driver through my Google user account.

[wbuchanan]

Interesting. I’ve only used Simba drivers when working with Tableau and don’t remember having much luck using them with any other software, so maybe it is an issue with the driver itself?