compute() permission denied even for temporary tables.

[rbcavanaugh]

Apologies if this is the wrong place for this. I'm working on a bigquery db (The all of us research program). I can create a temp table if I send raw SQL to the db but not with dplyr::compute(). Is there a way to make compute() work similar to the SQL chunk below that is creating a temporary table from a query? Thanks

---

Brief description of the problem

This works

con <- DBI::dbConnect(bigrquery::bigquery(), ...)

q = "
CREATE TEMP TABLE mytable AS
SELECT `concept_id`,
FROM `fc-aou-cdr-prod-ct.C2022Q2R7.concept` 
LIMIT 10;
SELECT * FROM mytable"

tmptbl_object = bigrquery::bq_project_query(
  Sys.getenv("GOOGLE_PROJECT"),
  query = q
)

tmp_table = paste(tmptbl_object$project, tmptbl_object$dataset, tmptbl_object$table, sep = ("."))
tbl(con, tmp_table) 

Doesn't work

con <- DBI::dbConnect(bigrquery::bigquery(), ...)
tbl(con, "concept") %>%
    select(concept_id)
    head(10) %>%
    compute(temporary = TRUE)
Error in `bq_post()`:
! Access Denied: Project fc-aou-cdr-prod-ct: User does not have bigquery.jobs.create permission in project

sessioninfo::session_info()
─ Session info ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.2.3 (2023-03-15)
 os       Ubuntu 22.04.2 LTS
 system   x86_64, linux-gnu
 ui       RStudio
 language (EN)
 collate  en_US.UTF-8
 ctype    en_US.UTF-8
 tz       Etc/UTC
 date     2024-03-21
 rstudio  2023.03.0+386 Cherry Blossom (server)
 pandoc   2.19.2 @ /usr/local/bin/pandoc

─ Packages ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 package     * version    date (UTC) lib source
 allofus     * 1.0.0.9000 2024-03-21 [1] Github (roux-ohdsi/allofus@3e0146f)
 bigrquery   * 1.5.1      2024-03-14 [1] CRAN (R 4.2.3)
 bit           4.0.5      2022-11-15 [1] CRAN (R 4.2.2)
 bit64         4.0.5      2020-08-30 [1] CRAN (R 4.2.2)
 blob          1.2.4      2023-03-17 [1] RSPM (R 4.2.0)
 brio          1.1.3      2021-11-30 [1] RSPM (R 4.2.0)
 cli           3.6.1      2023-03-23 [1] RSPM (R 4.2.0)
 colorspace    2.1-0      2023-01-23 [1] RSPM (R 4.2.0)
 curl          5.0.0      2023-01-12 [1] RSPM (R 4.2.0)
 DBI           1.1.3      2022-06-18 [1] CRAN (R 4.2.2)
 dbplyr      * 2.3.2      2023-03-21 [1] RSPM
 dplyr       * 1.1.1      2023-03-22 [1] RSPM (R 4.2.0)
 fansi         1.0.4      2023-01-22 [1] RSPM (R 4.2.0)
 forcats     * 1.0.0      2023-01-29 [1] RSPM (R 4.2.0)
 fs            1.6.1      2023-02-06 [1] RSPM (R 4.2.0)
 gargle        1.5.2      2023-07-20 [1] CRAN (R 4.2.3)
 generics      0.1.3      2022-07-05 [1] CRAN (R 4.2.2)
 ggplot2     * 3.4.2      2023-04-03 [1] RSPM (R 4.2.0)
 glue          1.6.2      2022-02-24 [1] RSPM (R 4.2.0)
 gtable        0.3.3      2023-03-21 [1] RSPM (R 4.2.0)
 hms           1.1.3      2023-03-21 [1] RSPM (R 4.2.0)
 httr          1.4.5      2023-02-24 [1] RSPM (R 4.2.0)
 jsonlite      1.8.4      2022-12-06 [1] RSPM (R 4.2.0)
 lifecycle     1.0.3      2022-10-07 [1] RSPM (R 4.2.0)
 lubridate   * 1.9.2      2023-02-10 [1] RSPM (R 4.2.0)
 magrittr      2.0.3      2022-03-30 [1] RSPM (R 4.2.0)
 munsell       0.5.0      2018-06-12 [1] CRAN (R 4.2.2)
 pillar        1.9.0      2023-03-22 [1] RSPM (R 4.2.0)
 pkgconfig     2.0.3      2019-09-22 [1] RSPM (R 4.2.0)
 prettyunits   1.1.1      2020-01-24 [1] RSPM (R 4.2.0)
 purrr       * 1.0.1      2023-01-10 [1] RSPM (R 4.2.0)
 R6            2.5.1      2021-08-19 [1] RSPM (R 4.2.0)
 readr       * 2.1.4      2023-02-10 [1] RSPM (R 4.2.0)
 rlang         1.1.0      2023-03-14 [1] RSPM (R 4.2.0)
 rstudioapi    0.14       2022-08-22 [1] RSPM (R 4.2.0)
 scales        1.2.1      2022-08-20 [1] CRAN (R 4.2.2)
 sessioninfo   1.2.2      2021-12-06 [1] RSPM (R 4.2.0)
 stringi       1.7.12     2023-01-11 [1] RSPM (R 4.2.0)
 stringr     * 1.5.0      2022-12-02 [1] RSPM (R 4.2.0)
 tibble      * 3.2.1      2023-03-20 [1] RSPM (R 4.2.0)
 tidyr       * 1.3.0      2023-01-24 [1] RSPM (R 4.2.0)
 tidyselect    1.2.0      2022-10-10 [1] CRAN (R 4.2.2)
 tidyverse   * 2.0.0      2023-02-22 [1] RSPM
 timechange    0.2.0      2023-01-11 [1] RSPM (R 4.2.0)
 tzdb          0.4.0      2023-05-12 [1] CRAN (R 4.2.3)
 utf8          1.2.3      2023-01-31 [1] RSPM (R 4.2.0)
 vctrs         0.6.2      2023-04-19 [1] RSPM (R 4.2.0)
 withr         2.5.0      2022-03-03 [1] RSPM (R 4.2.0)

[hadley]

Part of #557 — unfortunately enabling temporary table creation via SQL requires a bunch of other work. And while that work is ultimately worthwhile, I'm probably not going to have the time to do it for a while.

[rbcavanaugh]

No worries and thanks so much - I appreciate the update and linked issue which I didn't find before.

[JordanMPDS]

Curious, I was able to run compute() just fine without getting any errors. I can also query the computed table as normal.

@hadley Has anything change that would have made mine work?

Here is my workflow:

library(bigrquery)
library(DBI)
library(dplyr)
library(tidyr)
library(glue)

json_path <- "<json_path>"

Sys.setenv(GOOGLE_APPLICATION_CREDENTIALS = json_path)

bq_auth(path = json_path)

billing <- "<billing>"

con <- dbConnect(
    bigrquery::bigquery(),
    project = "<project>",
    dataset = "<dataset>",
    billing = billing
)

jobs_wide <- tbl(con, "gsearch_jobs_wide")

jobs_wide_test <- jobs_wide %>%
    filter(search_country == 'Albania', as.Date(search_time) == '2023-03-31') %>%
    select(keywords_all) %>%
    compute(temporary = TRUE)