r - 如何解析 R 包 "insufficient authentication scopes"中的 'bigrquery'？

Question

问题

如何使使用公共(public)数据的简单“bigrquery”函数起作用？我只想获取任何“bigrquery”函数以从 Google Cloud Platform Big Query 返回数据。

背景

示例如下:

我从查看 this example from bigrquery 开始作为起点(我已经安装了软件包):

library(bigrquery)
billing <- bq_test_project() # replace this with your project ID 
sql <- "SELECT year, month, day, weight_pounds FROM `publicdata.samples.natality`"

tb <- bq_project_query(billing, sql)
bq_table_download(tb, n_max = 10)
#> First chunk includes all requested rows.
#> # A tibble: 10 x 4
#>     year month   day weight_pounds
#>    <int> <int> <int>         <dbl>
#>  1  1969     1    20          7.00
#>  2  1969     1    27          7.69
#>  3  1969     6    19          6.75
#>  4  1969     5    30          6.19
#>  5  1969    11     9          7.87
#>  6  1969     5    25          7.06
#>  7  1969     7    25          7.94
#>  8  1969     9    11          7.06
#>  9  1969     7    13          6.00
#> 10  1969     9    27          8.13

项目编号

我知道 billing 需要是与我在 GCP 上的帐户相关联的项目 ID，因此我按照 "Billing project" 下的说明进行了导航。从我自己的一个测试项目中获取项目 ID:

尝试效仿

这就是我所做的:

library(bigrquery)

bq_auth(email = "my-google-account@gmail.com") # Not a step listed, 
# but I saw this elsewhere and thought it might be necessary.

billing <- "tidy-XXXXX" # my project ID from the screenshot above
sql <- "SELECT year, month, day, weight_pounds FROM `publicdata.samples.natality`"

tb <- bq_project_query(billing, sql) # Cannot get past this point without Error
bq_table_download(tb, n_max = 10)

运行 bq_auth() 导致我的浏览器激活并让我在浏览器中验证自己一次，但在后续运行时不会(我认为这是预期的行为)。

运行 bq_project_query() 得到这个:

> tb <- bq_project_query(billing, sql)
Error in `signal_reason()`:
! Request had insufficient authentication scopes. [insufficientPermissions] 
Run `rlang::last_error()` to see where the error occurred.

> last_error()
<error/bigrquery_insufficientPermissions>
Error in `signal_reason()`:
! Request had insufficient authentication scopes. [insufficientPermissions] 
---
Backtrace:
 1. bigrquery::bq_project_query(billing, sql)
 2. bigrquery::bq_perform_query(...)
 3. bigrquery:::bq_post(url, body = bq_body(body, ...), query = list(fields = "jobReference"))
 4. bigrquery:::process_request(req)
 5. bigrquery:::bq_check_response(status, type, content)
 6. bigrquery:::signal_reason(json$error$errors[[1L]]$reason, json$error$message)

SQL 查询很好，因为它可以在 GCP 控制台中运行。在 "Authentication and authorization" 下我在文件夹 ~/.R/gargle/gargle-oauth/ 中看到对缓存的引用，但我在 Windows 上并没有找到该文件/文件夹，但在 "Details" 下，看来我根本不需要担心电子邮件或 bq_auth()。我没有或认为我不需要“API 凭据”，因为我不是在编写应用程序——我只是在 RStudio 中以交互方式使用它(好吧，.R 脚本——但那是一样的，对吧？ ).我没有尝试使用服务帐户(应该吗？我认为我的用户帐户就足够了)。

我想看看在 bigrquery:::bq_check_response(status, type, content) 中的回溯中发生了什么，但我不知道如何进入该特定代码 (我可以单步执行自己编写的代码——但不是包函数？)

我实际上有一个使用“bigrquery”的工作，但除非我能弄明白，否则我永远不会让它工作。我觉得我遗漏了一些明显的东西，因为我没有看到这个问题出现在我已经搜索过的 SO 问题中。

22 年 6 月 22 日更新

也许进步？我感觉这个token不应该是下面的NULL，但是还是不知道怎么解决。首先，我

library(bigrquery)

options(error = recover)  # ADDED TO ALLOW BROWSING OF CALL FRAMES

bq_auth(email = "my-google-account@gmail.com")

billing <- "tidy-XXXXXX"

# Error comes on next line:
tb <- bq_project_query(x = billing,
  "SELECT count(*) FROM publicdata.samples.natality"
)

我得到的错误与帖子前面的错误相同，但是 options(error = recover) 允许我访问框架:

> library(bigrquery)

> options(error = recover)

> bq_auth(email = "my-google-account@gmail.com")

> billing <- "tidy-XXXXXX"

> tb <- bq_project_query(
+   x = billing,
+   "SELECT count(*) FROM publicdata.samples.natality"
+ )
Error in `signal_reason()`:
! Request had insufficient authentication scopes. [insufficientPermissions] 
Run `rlang::last_error()` to see where the error occurred.

Enter a frame number, or 0 to exit   

 1: source("C:/SO_QuestionScript.R")
 2: withVisible(eval(ei, envir))
 3: eval(ei, envir)
 4: eval(ei, envir)
 5: GoogleCloudPlatformQuery_Stack_Overflow_Question.R#9: bq_project_query(x = billing, "SELECT count(*) FROM publicdata.samples.natality")
 6: bq_perform_query(query, billing = x, destination_table = destination_table, ...)
 7: bq_post(url, body = bq_body(body, ...), query = list(fields = "jobReference"))
 8: process_request(req)
 9: bq_check_response(status, type, content)
10: signal_reason(json$error$errors[[1]]$reason, json$error$message)
11: rlang::abort(message, class = paste0("bigrquery_", reason))
12: signal_abort(cnd, .file)

Selection: 7
Called from: eval(substitute(browser(skipCalls = skip), list(skip = 7 - which)), 
    envir = sys.frame(which))
Browse[1]> dput(token)  
structure(list(method = NULL, url = NULL, headers = NULL, fields = NULL, 
    options = list(), auth_token = <environment>, output = NULL), class = "request")
Browse[1]> 

如果我深入了解 bq_post() 的内部结构，我可以看到 req 变量给我状态码 403，这是我的问题:

Browse[1]> bq_post
function (url, body, ..., query = NULL, token = bq_token()) 
{
    json <- jsonlite::toJSON(body, pretty = TRUE, auto_unbox = TRUE)
    req <- POST(paste0(base_url, url), body = json, httr::user_agent(bq_ua()), 
        token, add_headers(`Content-Type` = "application/json"), 
        ..., query = prepare_bq_query(query))
    invisible(process_request(req))
}



Browse[1]> req$status_code
[1] 403
Browse[1]> req$request
<request>
POST https://www.googleapis.com/bigquery/v2/projects/tidy-orbit-173301/jobs/?fields=jobReference
Output: write_memory
Options:
* post: TRUE
* postfieldsize: 177
* postfields: as.raw(c(0x7b, 0x0a, 0x20, 0x20, 0x22, 0x63, 0x6f, 0x6e, 0x66, 0x69, 0x67, 0x75, 0x72, 0x61, 0x74, 0x69, 0x6f, 0x6e, 0x22, 0x3a, 0x20, 0x7b, 0x0a, 0x20, 0x20, 0x20, 0x20, 0x22, 0x71, 0x75, 0x65, 0x72, 0x79, 0x22, 0x3a, 0x20, 0x7b, 0x0a, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x22, 0x71, 0x75, 0x65, 0x72, 0x79, 0x22, 0x3a, 0x20, 0x22, 0x53, 0x45, 0x4c, 0x45, 0x43, 0x54, 0x20, 0x63, 0x6f, 0x75, 0x6e, 0x74, 0x28, 0x2a, 0x29, 0x20, 0x46, 0x52, 0x4f, 0x4d, 0x20, 0x70, 0x75, 0x62, 0x6c, 0x69, 0x63, 0x64, 
0x61, 0x74, 0x61, 0x2e, 0x73, 0x61, 0x6d, 0x70, 0x6c, 0x65, 0x73, 0x2e, 0x6e, 0x61, 0x74, 0x61, 0x6c, 0x69, 0x74, 0x79, 0x22, 0x2c, 0x0a, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x22, 0x75, 0x73, 0x65, 0x4c, 0x65, 0x67, 0x61, 0x63, 0x79, 0x53, 0x71, 0x6c, 0x22, 0x3a, 0x20, 0x66, 0x61, 0x6c, 0x73, 0x65, 0x2c, 0x0a, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x22, 0x70, 0x72, 0x69, 0x6f, 0x72, 0x69, 0x74, 0x79, 0x22, 0x3a, 0x20, 0x22, 0x49, 0x4e, 0x54, 0x45, 0x52, 0x41, 0x43, 0x54, 0x49, 0x56, 0x45, 0x22, 0x0a, 
0x20, 0x20, 0x20, 0x20, 0x7d, 0x0a, 0x20, 0x20, 0x7d, 0x0a, 0x7d))
* useragent: bigrquery/1.4.0 (GPN:RStudio; ) gargle/1.2.0 httr/1.4.2
Headers:
* Accept: application/json, text/xml, application/xml, */*
* Content-Type: application/json
* Authorization: Bearer ya29.a0ARrdaM_Z0AIQ7950HcZ87goJJd8NorjH8m5JJ3BTTjj3hwAxH_PrXlTfWKjbYLXx-OfCiIk2BwAtN5trJh7FHf--[MORE STRING YOU DON'T NEED]
Auth token: Gargle2.0

Authorization: Bearer 看起来我有一个 token ，但它可能设置不正确？

Answer 1

已解决!!!!

好吧，我发现在从 RStudio 到浏览器然后再返回的初始身份验证期间，我没有正确授权 Google。

你必须选中我最初取消的这两个框:

如果你犯了同样的错误如何重置:

在 R 中，您需要 bq_deauth() 您现有的凭据，当您运行另一个 BigRQuery 函数时，它会再次询问您是否要使用存储的凭据或创建新凭据输入“0”。选择“0”。所以它应该是这样的:

> bq_deauth()
> con <- dbConnect(
+   bigrquery::bigquery(),
+   project = "bigquery-public-data",
+   dataset = "baseball",
+   billing = billing
+ )
> dbListTables(con)
The bigrquery package is requesting access to your Google account.
Select a pre-authorised account or enter '0' to obtain a new token.
Press Esc/Ctrl + C to cancel.

1: scott.jackson@workemail.com
2: scott.jackson@gmail.com

Selection: 0
Waiting for authentication in browser...
Press Esc/Ctrl + C to abort
Authentication complete.
[1] "games_post_wide" "games_wide"      "schedules"      # OUTPUT I WANTED!!!!!!

选择'0'后，浏览器让你重新认证，Bob就是你的叔叔了!