Implement dbWithTransaction

[ablack3]

It looks like bigquery supports transactions but bigrquery does not currently support dbWithTransation(). I would like to be able to use DBI::dbWithTransaction with a BigQueryConnection.

For some reason the code below works fine when executed with source but not when executed using the reprex package. I'm not sure why. I've copied the output below the code.

library(bigrquery)
library(DBI)

sql <- "SELECT count(*) AS n FROM `publicdata.samples.natality`"

bigrquery::bq_auth(path = Sys.getenv("BIGQUERY_SERVICE_ACCOUNT_JSON_PATH"))

con <- dbConnect(
  bigrquery::bigquery(),
  project = "publicdata",
  dataset = "samples",
  billing = Sys.getenv("BIGQUERY_TEST_PROJECT")
)

# this works
dbGetQuery(con, sql)
# # A tibble: 1 × 1
# n
# <int>
# 137826763

# this does not work
dbWithTransaction(con, {
  dbGetQuery(con, sql)
})
# Error: Reason: Not yet implemented: dbBegin(Connection)
dbDisconnect(con)

R version 4.2.0
Package versions DBI_1.1.3 bigrquery_1.4.0

[hadley]

Now part of #557 since using a transaction requires activating a session.