Use sessions

[hadley]

https://cloud.google.com/bigquery/docs/sessions-intro

Will allow temporary tables and transactions.

[hadley]

This is a bit more involved than I had hoped because temporary tables seem to be only part of the SQL API, not the REST api. So if we enable sessions, it'll mean switching implementations for many of the DBI methods and I don't have much sense of what the performance implications will be.

[hadley]

Since no one has actually asked for this, and it looks like it's going to be a decent amount of work I'll delay to the next release. If this issue sounds interesting to you, please thumbs up it!

---

What exactly does a temp table look like from the API? need to inspect result of job — can’t see anything obvious in the docs that give created table name. Can we get info from INFORMATION_SCHEMA.TABLES?

bq_perform_query() needs session argument — goes in connectionProperties field

• Display in print method
• Need to pass to resault
• dbDisconnect() should terminate session, setting session to NULL
• dbIsValid() should check session field
• dbWriteTable needs call dbCreateTable() then dbAppendTable()
• dbCreateTable() needs to call `sqlCreateTable() then execute it
• dbAppendTable() needs to call sqlAppendTable() then execute it
• dbReadTable() needs to switch to perform query? Otherwise need to figure out if the table is temporary, which might be available in INFORMATION_SCHEMA.
• dbListTables needs to query INFORMATION_SCHEMA? Similarly with dbExistsTable. Need to see if temporary tables are even there.
• dbRemoveTable() needs to use DROP TABLE;

Given that reading and writing table might have performance differences, it might be necessary to make this option, even if it's on by default.

To create a session:

bq_session_create <- function(project) {
  check_string(project)

  url <- bq_path(project, jobs = "")
  body <- list(
    configuration = list(
      query = list(
        query = "SELECT 1;",
        createSession = list(
          value = unbox(TRUE)
        )
      )
    )
  )

  res <- bq_post(url, body = bq_body(body))
  res$statistics$sessionInfo$sessionId
}