Tricks in R to Boost Your Productivity (Part 2)

db_query.R

# Connect DB function
connect_db <- function() {

  pgsql <- RJDBC::JDBC("com.amazon.redshift.jdbc42.Driver",
                       "./jdbc_driver/RedshiftJDBC42-1.2.16.1027.jar",
                       "`")

  host <- "redshift-cluster-1.abc1234.us-east-2.redshift.amazonaws.com"
  port <- '5439'
  user <- 'abc1234'
  pw <- 'abc1234'
  dbname <- 'prod'
  dbLink <- paste0('jdbc:redshift://', host, ':',
                   port, '/',dbname,
                   '?user=',user,
                   '&password=',pw)
  con <- DBI::dbConnect(pgsql, url = dbLink)

  return(con)
}

# Parse SQL files
get_sql <- function(filepath)
{
  con = file(filepath, "r")
  sql.string <- ""

  while ( TRUE )
  {
    line <- readLines(con, n = 1)
    if ( length(line) == 0 ) { break }

    line <- gsub("\\t", " ", line)

    if(grepl("--",line) == TRUE)
    {
      line <- paste(sub("--","/*",line),"*/")
    }

    sql.string <- paste(sql.string, line)
  }

  close(con)
  return(sql.string)
}

# DB query function
db_query <- function(sql_path, ...) {

  # Check the type of variable `file`. If it is file, read it. If it is
  # string, use it directly
  if(file.exists(sql_path)) {
    statement <- get_sql(sql_path)
  } else if (is.character(sql_path)) {
    statement <- sql_path
  } else {
    stop('no file exists or bad query!')
  }

  con <- connect_db()
  # Interpolate paramter
  statement <- DBI::sqlInterpolate(con, statement, ...)
  
  # Query
  value <- tryCatch(DBI::dbGetQuery(con, statement) %>% setDT(),
                    error=function(cond) {
                      DBI::dbDisconnect(con)
                      stop('Error in Query')
                    })
  
  DBI::dbDisconnect(con)
  return(value)
}