R/database.R

In joshmuncke/redbulltools: Helper Functions for Red Bull Data Science

#' Create an object in a remote datasource from a tbl
#'
#' This function will create a remote object (view or table) in
#' a remote data source based on a dbplyr pipe chain.
#'
#' Essentially it puts a "CREATE VIEW/TABLE <name> AS ()" wrapper
#' around the SQL code that is generated by the dbplyr chain.
#'
#' @param x dbplyr pipe / remote tbl object
#' @param name name for new remote object (including schema)
#' @param dest remote data source connection
#' @param table specify whether the object is a view (default) or table
#'
#' @export
#' @importFrom magrittr %>%
create_database_object <- function(x, name, dest, table = F) {

  # Get the raw query string for the query object
  raw_query <- dbplyr::remote_query(x)

  # Strip the "<SQL>" header text that remote_query adds
  strip_header <- stringr::str_replace_all(raw_query, "<SQL>", "")

  # What are we creating?
  if(table == FALSE) {
    create_text <- "CREATE VIEW "
  }
  else {
    create_text <- "CREATE TABLE "
  }

  # SQL code to create the new object
  object_create_sql <- paste0(create_text, name," AS (", strip_header ,");")

  # Execute the create code
  # Need to test if already exists??
  safe_query <- purrr::safely(DBI::dbGetQuery)
  executed <- safe_query(dest, object_create_sql)

  if(!is.null(executed$error)) {
    stop(executed$error, call. = FALSE)
  }

  invisible(executed$result)
}