#' 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)
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.