Create copy_to equivalent with read-only access

[iangow]

I use a database server where normal users do not have TEMPORARY privileges. This means that connections to the database are read-only and functions such as compute() or dbWriteTable() are not available. I suspect that this is not uncommon in the real world.

One downside of this is that if I have data locally that I want to join with data on the server, the only real option is to collect or otherwise download data from the server. But in a typical use case, the data I have locally are small, while the data on the server are large. So this is very inefficient.

The function below is a quick fix for this issue. It basically turns a data frame into SQL and creates a remote data frame using that SQL. Its use is illustrated here. Performance is (unsurprisingly) worse than dbWriteTable, but it's not too bad.

The function below is obviously incomplete and PostgreSQL-focused (it works for my current use case), but would a function like this belong somewhere in dbplyr?

library(DBI)
library(dplyr, warn.conflicts = FALSE)

df_to_pg <- function(df, conn) {
  
  convert_vec <- function(vec) {
    if (class(vec)=="character") {
      convert_char(vec)
    } else if (class(vec)=="Date") {
      convert_date(vec)
    } else if (class(vec)=="integer") {
      convert_int(vec)
    } else if (class(vec)=="numeric") {
      convert_num(vec)
    }
  }
  
  make_string <- function(vec) {
    paste0("'{", paste(paste0('"', vec, '"'), collapse=","), "}'")
  }
  
  convert_char <- function(vec) {
    paste0(make_string(vec), "::text[]")
  }
  
  convert_int <- function(vec) {
    paste0(make_string(vec), "::integer[]")
  }
  
  convert_date <- function(vec) {
    paste0(make_string(vec), "::date[]")
  }
  
  convert_num <- function(vec) {
    paste0(make_string(vec), "::float8[]")
  }
  
  temp_starter_sql <- list()
  for (i in 1:length(df)) {
    temp_starter_sql[[i]] = paste0("UNNEST (", 
                                   convert_vec(df[[i]]), ") AS ", 
                                   names(df)[[i]])
  }
  
  temp_sql <- paste0("SELECT ", paste0(temp_starter_sql, collapse = ",\n"))
  
  temp_df_sql <- tbl(conn, sql(temp_sql))
  return(temp_df_sql)
}

[hadley]

This just feels too specialised for dbplyr, sorry.

[iangow]

Thanks for considering. My problem is fixed, so I'm good.

I made a StackOverflow question in case others have this issue (I'm surprised there isn't a question on this already … evidence that this is indeed specialised).

[krlmlr]

@hadley: Would you reconsider if the code used DBI::dbQuoteLiteral(), translated as a sequence of UNION ALL ? One advantage over copy_to() is that this remains a lazy query until executed.

We could also offer a simpler building block:

memdb_row <- function(...) {
  db_row(..., .src = src_memdb())
}

db_row <- function(..., .src) {
  data <- tibble(...)
  stopifnot(nrow(data) == 1)
  values <- unlist(map(data, DBI::dbQuoteLiteral, conn = .src$con))

  from <- sql(paste0("SELECT ", paste0(
    values, " AS ", DBI::dbQuoteIdentifier(.src$con, names(values)),
    collapse = ", "
  )))
  dbplyr:::tbl.src_dbi(.src, from, vars = names(values))
}

memdb_row(a = 1, b = "x")
#> # Source:   SQL [?? x 2]
#> # Database: sqlite 3.35.5 [:memory:]
#>       a b    
#>   <int> <chr>
#> 1     1 x
memdb_row(a = 1, b = "x") %>%
  sql_render()
#> <SQL> SELECT 1 AS `a`, 'x' AS `b`

^{Created on 2021-05-14 by the reprex package (v2.0.0)}

[hadley]

@krlmlr I'd want to see a few real cases where this was needed before investing any time thinking about it.

[iangow]

@krlmlr Despite the lack of questions on StackOverflow, I conjecture that there are a lot of people out there who could use this, but don't know it. People with read-only access to a large remote database and a local data frame they want to join with a table in that remote database. (I encountered it in writing a book on how to use the WRDS data, which is provided by Wharton and is a staple of research conducted in business schools, which offers a read-only PostgreSQL database. The current workaround for most would be "use SAS".)

I guess an issue with making this function would be how to advertise and document it so that it doesn't add clutter and complexity.

One option might be to make this the underlying function that is called when copy_to is called with an argument such as read_only = TRUE. So very little to document and no new function.

I'm not sure how good dbplyr is with picking up error messages from databases or if it can detect that a connection is read-only, but a helpful message to the effect of "You are trying to write to a read-only database; try read_only = TRUE" might help when a user tries copy_to with a read-only connection.

[hadley]

I'm sceptical that there are many purely read-only databases in the wild; there's no security risk to enabling temporary tables.

[iangow]

Thanks.

"… there's no security risk to enabling temporary tables." That's what I tried to tell the people at WRDS. They said "the functionality you describe presents significant problems of both security and scale". This is the standard response of IT in the low-value parts of academia (low-value := business school academics aren't curing cancer).

My current (pedagogical) use case is here. Question: Does stock market react positively when Apple holds events (WWDC, etc.)? So I have a small local dataset (scraped from Wikipedia) with event dates and ~20GB of stock price data remotely that I want to join.

My hacky function makes this doable. I may try to adapt @krlmlr's building block to make my function less hacky. If others request this in the future, then some work will have already been done.

[RLesur]

In all professional contexts where I've worked, data where made available to data analysts through read only databases. I agree that there is no security risk but old school sysadmins apply a dummy rule of thumb. From my experience, having the rights to write in temporary tables is a rare exception.

[iangow]

One more data point: Confirmed with brother (economist in public service in Australia) that Redshift databases used in his role are read-only.

[iangow]

(I recognize that adverb "purely" in "purely read-only" is critical. In PostgreSQL, one can have TEMPORARY privileges, yet not be able to make permanent tables. Seems to be that a quick test for dbplyr users is to try compute() with a virtual table. With a purely read-only database connection, this will fail.)

[krlmlr]

Another argument in favor of inline tables: they're much much faster if they contain only "few" rows. Certainly for one row (a frequent use case), but even for 100 rows I wouldn't be surprised if the total query time is faster for an inline table compared to populating a temporary table and then executing the query.

[krlmlr]

See iangow/farr#1 for a discussion of the various options.