In the following reprex, I create a BigQuery dataset, create a table with mtcars, create a view, and then try to query the view.
I can query the table, but the view returns no data.
library(DBI)
library(dplyr, warn.conflicts = FALSE)
library(bigrquery)
dataset = bq_dataset(bq_test_project(), "test_dataset")
if (bq_dataset_exists(dataset))
{
bq_dataset_delete(dataset, delete_contents = T)
}
#> Using an auto-discovered, cached token.
#> To suppress this message, modify your code or options to clearly consent to the use of a cached token.
#> See gargle's "Non-interactive auth" vignette for more details:
#> https://gargle.r-lib.org/articles/non-interactive-auth.html
#> The bigrquery package is using a cached token for ariel.balter@gmail.com.
bq_dataset_create(dataset)
#> <bq_dataset> elite-magpie-257717.test_dataset
conn = DBI::dbConnect(
bigrquery::bigquery(),
project = bq_test_project(),
dataset = "test_dataset",
KeyFilePath = "google_service_key.json",
OAuthMechanism = 0
)
mtcars %>% head(5)
#> mpg cyl disp hp drat wt qsec vs am gear carb
#> Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
#> Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
#> Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
#> Hornet 4 Drive 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
#> Hornet Sportabout 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
if (dbExistsTable(conn, "mtcars"))
{
dbRemoveTable(conn, "mtcars")
}
if (dbExistsTable(conn, "mtcars_view"))
{
dbRemoveTable(conn, "mtcars_view")
}
dbWriteTable(conn, "mtcars", mtcars)
dbListTables(conn)
#> [1] "mtcars"
dbExecute(conn,'CREATE VIEW mtcars_view AS SELECT mpg, cyl, disp FROM mtcars')
#> [1] 0
dbListTables(conn)
#> [1] "mtcars" "mtcars_view"
mtcars_table = tbl(conn, "mtcars")
mtcars_table %>% head(5) %>% collect()
#> Warning: `...` is not empty.
#>
#> We detected these problematic arguments:
#> * `needs_dots`
#>
#> These dots only exist to allow future extensions and should be empty.
#> Did you misspecify an argument?
#> # A tibble: 5 x 11
#> carb am vs qsec wt drat cyl disp hp gear mpg
#> <int> <int> <int> <dbl> <dbl> <dbl> <int> <dbl> <int> <int> <dbl>
#> 1 2 0 1 20 3.19 3.69 4 147. 62 4 24.4
#> 2 2 0 1 22.9 3.15 3.92 4 141. 95 4 22.8
#> 3 1 0 1 20.0 2.46 3.7 4 120. 97 3 21.5
#> 4 1 0 1 19.4 3.22 3.08 6 258 110 3 21.4
#> 5 1 0 1 20.2 3.46 2.76 6 225 105 3 18.1
mtcars_view = tbl(conn, "mtcars_view")
mtcars_view %>% head(5) %>% collect()
#> Warning: `...` is not empty.
#>
#> We detected these problematic arguments:
#> * `needs_dots`
#>
#> These dots only exist to allow future extensions and should be empty.
#> Did you misspecify an argument?
#> # A tibble: 0 x 3
#> # ... with 3 variables: mpg <dbl>, cyl <int>, disp <dbl>
mtcars_view %>% head(5) %>% select(everything()) %>% collect()
#> Error: Job 'elite-magpie-257717.job_-275OmiomBA46Ukx5QdteyLlR0TN.US' failed
#> x Table name "mtcars" cannot be resolved: dataset name is missing. [invalid]
dbExecute(conn, "DROP TABLE mtcars")
#> [1] 0
dbExecute(conn, "DROP VIEW mtcars_view")
#> [1] 0
Created on 2020-10-06 by the reprex package (v0.3.0)
