How can I create or alter SQL view using R code?-CodePudding

Is it possible to create or alter a SQL view using R code?

I'm using SQL Server and so far I haven't found any way to do this with R code. I was hoping there would be a solution in dbplyr, but I didn't find anything.

My goal is to bind multiple tables together so nothing complicated.

Seems to me that the only option at the moment is to use SQL code, such as:

DBI::dbExecute(conn, 
    CREATE OR ALTER VIEW dbo.MyTestView
AS  
    SELECT
        ColA, ColB, ColC, GETDATE() AS CurrentTimeStamp  
    FROM 
        dbo.MyTable1 

    UNION ALL  

    SELECT 
        ColA, ColB, ColC, GETDATE() AS CurrentTimeStamp  
    FROM
        dbo.MyTable2 

    UNION ALL  

    SELECT 
        ColA, ColB, ColC, GETDATE() AS CurrentTimeStamp
    FROM 
        dbo.MyTable3;
)

Any suggestions?

CodePudding user response：

You cannot create a view directly, but the query itself can be created with:

library(dplyr)
# library(dbplyr)
union_all(
  tbl(con, "MyTable1") %>%
    select(ColA, ColB, ColC) %>%
    mutate(CurrentTimeStamp = today()),
  tbl(con, "MyTable2") %>%
    select(ColA, ColB, ColC) %>%
    mutate(CurrentTimeStamp = today())
  ) %>%
  show_query()
# <SQL>
# (SELECT TOP 1 "ColA", "ColB", "ColC", CAST(SYSDATETIME() AS DATE) AS "CurrentTimeStamp"
# FROM "MyTable1")
# UNION ALL
# (SELECT TOP 1 "ColA", "ColB", "ColC", CAST(SYSDATETIME() AS DATE) AS "CurrentTimeStamp"
# FROM "MyTable2")

One could always capture this output (%>% capture.output) and replace the leading <SQL> with your CREATE OR ..., then call dbExecute to send the create-statement.

... %>%
  capture.output() %>%
  `[<-`(1, "CREATE OR ALTER VIEW dbo.MyTestView") %>%
  paste(collapse = " \n ") -> viewquery
DBI::dbExecute(con, viewquery)

CodePudding user response：

I do this using a custom function that mixes the dbplyr and DBI packages. A cut down version of this function is below. You can find the full function along with other helpers here.

create_view <- function(tbl_name, db_connection, view_name) {
  # SQL query
  sql_query <- glue::glue(
    "CREATE VIEW {view_name} AS\n",
    "{dbplyr::sql_render(tbl_name)}\n"
  )

  # run query
  result <- DBI::dbExecute(db_connection, as.character(sql_query))
}

The idea of this function is to use dplyr commands to create the desired table, before writing it as a view. For example:

db_con = DBI::dbConnect(...)

input_table = tbl(db_con, "my table")

prepared_table = input_table %>%
  mutate(new_col = ...) %>%
  filter(old_col != ...)

create_view(prepared_table, db_con, "my_view")

prepared_view = tbl(db_con, "my_view")

Note that your database connection needs to be with the database you are going to write the view to. You can not write the view to a different database.

I am not aware of any dbplyr method for UNION ALL. I also do this with a custom function, similar to the below.

union_all <- function(db_con, table_a, table_b, list_of_columns) {
  table_a <- table_a %>% dplyr::ungroup() %>% dplyr::select(all_of(list_of_columns))
  table_b <- table_b %>% dplyr::ungroup() %>% dplyr::select(all_of(list_of_columns))

  sql_query <- dbplyr::build_sql(
    con = db_con,
    dbplyr::sql_render(table_a),
    "\nUNION ALL\n",
    dbplyr::sql_render(table_b)
  )
  return(dplyr::tbl(db_connection, dbplyr::sql(sql_query)))
}