R/make_table.R

In RollieParrish/ccdm: R functions for the Core Clinical Data Model (CCDM)

#' Copy a remote tbl into a Table.
#'
#' Using dbplyr::compute, this function can be used at the end of a dplyr chain to
#' create a permanent table in Snowflake. Uses the database and schema defined
#' in the connection.
#'
#' @param .data Incoming object from dplyr pipeline or a tbl object
#' @param name The name of the desired table. Can use dplyr::in_schema() to specify a table in a different schema than the one that was defined
#' in the connection.  Note: will be dropped and recreated if a table already exists.
#' @param .conn The connection object (needed to drop the table before 'compute' is called)
#'
#' @importFrom odbc odbc
#' @importFrom dplyr compute
#'
#' @export
#'
#' @examples
#'
#' \dontrun{
#' conn <- connect_cdw()
#'
#' demo <- ccdm_tbl(conn, "HOSPITAL_ENCOUNTERS") %>% head()
#'
#' make_table(demo, name = "DELETEME", conn)
#'
#' demo %>%
#' make_table(in_schema("PHC_DB_DEV.SANDBOX", "DELETEME"), conn)
#'
#' disconnect_cdw(conn)
#'
#' }



make_table <- function(.data, name, .conn = NA) {

    stopifnot(inherits(.conn, "Snowflake"))

    dbplyr::build_sql("DROP TABLE IF EXISTS ", dbplyr::in_schema("df.fda", "fadas"), con = .conn)

    # Save to STG_VQI.VQI_CASES
    #DBI::dbSendStatement(.conn, dbplyr::build_sql("DROP TABLE IF EXISTS ", ident(name), con = .conn))
    DBI::dbSendStatement(.conn, dbplyr::build_sql("DROP TABLE IF EXISTS ",name, con = .conn))
    #DBI::dbClearResult(.conn)
    results <- dplyr::compute(tbl, name = name, temporary = FALSE, analyze = FALSE)

}