如何解决“不足的身份验证范围”在R包中，Bigrquery？

Question

问题

如何使用公共数据来获得简单的“ Bigrquery”功能？我只想获得 'bigrquery'功能，以返回Google Cloud Platform Big查询的数据。

背景

示例如下：

我首先查看 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

项目ID

我知道计费需要是与我在GCP上关联的项目ID，所以我根据“计费项目” 来自我自己的一个测试项目之一，以获取项目ID：

尝试遵循示例

，所以我做了我所做的一切：

library(bigrquery)

bq_auth(email = "[email protected]") # 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控制台中工作时很好。在“身份验证和授权” 我看到了对文件夹中的缓存的引用〜/。 r/gargle/gargle-oauth/，但是我在Windows上，没有找到该文件/文件夹，而是在”详细信息，似乎我不必担心电子邮件或bq_auth（） 。我没有或不认为我需要“ API凭据”，因为我没有编写一个应用程序 - 我只是在rstudio上进行交互使用（嗯，.r脚本 - 但这是一样的，对吗？ ）。我不是要使用服务帐户（我应该吗？我认为我的用户帐户就足够了）。

我想看看bigrquery ::: bq_check_response（状态，类型，内容）的回溯中发生了什么，但是我不知道如何介入该特定代码（我可以踩通过代码，我写了自己 - 但不是包装功能吗？）

我实际上有一个工作“ bigrquery”，但是除非我能弄清楚这个，否则我将永远无法正常工作。我觉得我错过了一些明显的东西，因为我看不到这个问题在我已经搜索过的问题中弹出。

更新6/22/22

也许进展？我觉得这个令牌不应在下面null，但仍然不确定如何修复它。首先，我

library(bigrquery)

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

bq_auth(email = "[email protected]")

billing <- "tidy-XXXXXX"

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

获得的错误与帖子早期相同，但是options（error = reconion）让我访问帧：

> library(bigrquery)

> options(error = recover)

> bq_auth(email = "[email protected]")

> 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

授权：bearer看来我有一个令牌，但也许无法正确设置？

Answer 1

解决了！！！

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

您必须选中我最初吹走的这​​两个框：

犯同样的错误，如何重置此问题

如果 需要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: [email protected]
2: [email protected]

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）是您的叔叔！