Can't query VIEW in BigQuery with dbplyr

Question

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 [email protected].

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)}

Answer 1

The error message said:

#> Error: Job 'elite-magpie-257717.job_-275OmiomBA46Ukx5QdteyLlR0TN.US' failed
#> x Table name "mtcars" cannot be resolved: dataset name is missing. [invalid]

I'm not sure why such view could be created in the first place, but the SQL for creating the view should be:

CREATE VIEW mtcars_view AS SELECT mpg, cyl, disp FROM test_dataset.mtcars