compute() permission denied even for temporary tables.
rbcavanaugh opened this issue · 3 comments
rbcavanaugh commented
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 commented
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 commented
No worries and thanks so much - I appreciate the update and linked issue which I didn't find before.
JordanMPDS commented
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)