R/cdwSQL.R

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

#' passes a SQL query from a separate .sql file to the CDW.
#'
#' Note - must be a single SQL clause. Queries with multiple clauses should use the TBD function instead,
#' which relies on the snowsql CLI application.
#'
#' @param conn a DBI connection object such as connect_cdw()
#' @param sql  path to a .sql file
#'
#' @return
#' \item{data}{Query results as a data frame}
#' \item{fields}{field names}
#' \item{elapsed_seconds}{elapsed system time to run the query}
#' \item{status_message}{the number of records & variables in the results}
#'
#' @export
#'
#'
#' @examples
#' \dontrun{
#'
#' conn <- connect_cdw()
#' 
#' sql <- read_sql("path/to/some_file.sql")
#' 
#' cdwSQL(conn, sql)
#' 
#' disconnect_cdw(conn)
#' 
#' }



cdwSQL <- function (conn = conn, sql = NULL) {

    if (missing(sql))
        stop("a .sql file must be specified using the sql parameter")
    start_time <- Sys.time()


    sql <- ccdm::read_sql(sql)

    queryResult <- DBI::dbGetQuery(conn, sql)

    elapsed_seconds <- as.numeric(difftime(Sys.time(), start_time,
                                           units = "secs"))

    status_message <- paste0(
        format(nrow(queryResult), big.mark = ",", scientific = FALSE, trim = TRUE),
        " records and ",
        format(length(queryResult), big.mark = ",", scientific = FALSE, trim = TRUE),
        " columns were read in ",
        round(elapsed_seconds, 2), " seconds.")


    results <- list(data = queryResult,
                    fields = names(queryResult),
                    elapsed_seconds = elapsed_seconds,
                    status_message = status_message)

    results

}