dbReadTable() returns 0 rows when used on views

[WouterDH-UZL]

Not sure whether this is expected behaviour, but dbReadTable() returns 0 rows when used on any view within the BigQuery environment. Using dbReadTable() with odbc on an MSSQL-database does return all rows when querying a view.

[hadley]

It would be super helpful if you could create a reprex for this, i.e. give me some SQL that will create a simple table, create a view on that table, and then show me the R code that returns a 0 row output.

[WouterDH-UZL]

Apologies for not including a reprex. Here you go:

library(bigrquery)
library(DBI)
library(dplyr)

param_project <- "my_project" #placeholder, needs to be filled in 
param_dataset <- "my_dataset" #placeholder, needs to be filled in 

bq_auth()

con1 <- dbConnect(bigquery(), project = param_project, billing = param_project, dataset = param_dataset)

# Create a table and a view
dbWriteTable(con1, "mtcars_test", mtcars)
dbExecute(con1, paste0("CREATE VIEW mtcars_View AS SELECT * FROM ", param_dataset, ".mtcars_test"))

# Try dbReadTable on both
nrow(mtcars)
res_table <- dbReadTable(con1, "mtcars_test")
nrow(res_table)
res_view <- dbReadTable(con1, "mtcars_View")
nrow(res_view)
# And dbGetQuery
res_select <- dbGetQuery(con1, "SELECT * FROM mtcars_View")
nrow(res_select)

# Cleanup
dbExecute(con1, "DROP TABLE mtcars_test")
dbExecute(con1, "DROP VIEW mtcars_View")

dbDisconnect(con1)

This returns the following result for me

[hadley]

Perfect, thank you! I'm unlikely to be working on bigrquery for a little while, but this will make it super easy to figure it out when I next am.