Can I run a BigQuery SQL query and then continue wrangling the data using dbplyr?

Question

In another project working with Amazon Athena I could do this:

con <- DBI::dbConnect(odbc::odbc(), Driver = "path-to-driver", 
                 S3OutputLocation = "location", 
                 AwsRegion = "eu-west-1", AuthenticationType = "IAM Profile", 
                 AWSProfile = "profile", Schema = "prod")


tbl(con,
    # Run SQL query
    sql('SELECT *
    FROM TABLE')) %>%
  # Without having collected the data, I could further wrangle the data inside the database
  # using dplyr code
  select(var1, var2) %>%
  mutate(var3 = var1 + var2)

However, now using BigQuery I get the following error

con <- DBI::dbConnect(bigrquery::bigquery(),
                      project = "project")

tbl(con,
    sql(
      'SELECT *
    FROM TABLE'
    ))

Error: dataset is not a string (a length one character vector).

Any idea if with BigQuery is not possible to do what I'm trying to do?

Answer 1

Not a BigQuery user, so can't test this, but from looking at this example it appears unrelated to how you are piping queries (%>%). Instead it appears BigQuery does not support receiving a tbl with an sql string as the second argument.

So it is likely to work when the second argument is a string with the name of the table:

tbl(con, "db_name.table_name")

But you should expect it to fail if the second argument is of type sql:

query_string = "SELECT * FROM db_name.table_name"
tbl(con, sql(query_string))

Other things to test:

• Using odbc::odbc() to connect to BigQuery instead of bigquery::bigquery(). The problem could be caused by the bigquery package.
• The second approach without the conversation to sql: tbl(con, query_string)