Early attempt at creating materialized SQL views from dplyr

gistfile1.txt

separate_sql <- function(expression) {
  s <- paste(deparse(expression), collapse = "\n")
  s <- stringr::str_replace(s, "%>%", "%>%\n  ")
  s <- stringr::str_split(s, "\n")[[1]]
  
  val <- eval(expression)
  
  list(expression = expression,
       dplyr_code = s,
       sql = as.character(dbplyr:::remote_query(val)),
       value = val)
}

create_materialized_view <- function(name, chain) {
  s <- separate_sql(rlang::enexpr(chain))

  nrows <- as.integer(dplyr::collect(dplyr::count(s$value))$n)
  
  message("The ", name, " query successfully creates a table with ", nrows, " rows")
  
  dplyr_code <- paste0("-- ", s$dplyr_code, collapse = "\n")

  template <- "-- Drop the table
DROP TABLE IF EXISTS %s;

-- Create the table

-- This was autogenerated from the following dplyr code:
%s

CREATE TABLE %s AS (
  %s
);"
  
  content <- sprintf(template, name, dplyr_code, name, s$sql)
  # Normally I'd write this to a file
  cat(content)
}

# example
create_materialized_view("views.live_courses",
                         tbl_redshift("main_app.courses") %>%
                           filter(aasm_state %in% c("live", "soft_launch")))