How to use EXTRACT through dbplyr when connecting

Question

Take this query:

SELECT EXTRACT(month FROM order_date) "Month"
  FROM orders

(simplified example from official oracle doc)

How would you go at integrating such EXTRACT operations above in a dbplyr chain ?

I'm open to any other workaround (even ugly/costly) to extract the month on server side.

Answer 1

More elegant:

tbl(con, "orders") %>% mutate(Month = extract(NULL %month from% order_date))

This results in the following SQL (ANSI SQL):

EXTRACT( MONTH FROM "order_date")

This trick works because the names of operators (what's between the percent signs) are literally translated to SQL. NULL disappears (unlike NA).

Answer 2

Meanwhile I came up with something.

The expected output of given example will be obtained by executing this:

con <- ROracle::dbConnect(drv, username, password, dbname) # your connection parameters
dplyr::tbl(con,"orders") %>%
  extract_o("Month","order_date",append = FALSE,force_upper_case = FALSE)

Here is the function's code, I included some parameters to force upper case columns (default) and to append new column to existing ones (default). Name of new column can be defined or by default will be named as the type of value you want to extract.

#' use Oracle EXTRACT function
#' 
#' Will add a column to the table, containing extracted value,
#' optionally returns only this column
#' @param data tbl_lazy object
#' @param what type of data to extract
#' @param from column to extract from
#' @param new_col name of new column
#' @param append keep existing columns,
#' FALSE ditches them and keep only extracted column
#' @param force_upper_case make new column name uppercase
extract_o <-function(data, what, from, new_col = what,
                     append = TRUE,force_upper_case = TRUE) {
  allowed <- c("day","month","year","hour","minute","second",
                     "timezone_hour","timezone_minute",
                     "timezone_region","timezone_abbr")
  assertthat::assert_that(
    tolower(what) %in% allowed,
    msg=paste("Choose 'what' among",
              paste0("'",allowed,"'",collapse=", ")))
  if(force_upper_case) new_col <- toupper(new_col)
  tbl_query <- as.character(dbplyr::sql_render(data)) # previous query
  append_sql <- if(append)
    paste0(paste(colnames(data),collapse=", "),", ") else ""
  query <- paste0("SELECT ", append_sql,                         # initial cols or none
                  "EXTRACT(",what," FROM ",from,") \"",new_col,  # new col
                  "\" FROM (",tbl_query,")")                     # previous query
  dplyr::tbl(data$src$con,sql(query))
}