How to create SQL Server table from dplyr pipeline

Question

Due to a bug in dbplyr, copy_to and compute are currently not working for SQL Server connections.

connStr <- "driver=ODBC Driver 13 for SQL Server;server=localhost;..."
db <- DBI::dbConnect(odbc::odbc(), .connection_string=connStr)

copy_to(db, mtcars)
#Error: <SQL> 'CREATE TEMPORARY TABLE "mtcars" (
#  "row_names" varchar(255),
#  "mpg" FLOAT,
#  ...
#  nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement. 

# use raw DBI functionality to create table
DBI::dbWriteTable(db, "mtcars", mtcars)

qry <- tbl(db, "mtcars") %>% group_by(am) %>% summarise(m=mean(mpg))

compute(qry)
#Error: <SQL> 'CREATE TEMPORARY TABLE "isrxofsskr" AS SELECT "am" AS "am", "m" #AS "m"
#FROM (SELECT "am", AVG("mpg") AS "m"
#FROM "mtcars"
#GROUP BY "am") "htrkkxabrn"'
#  nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement.

There is an active PR on the dbplyr repo that solves this problem, but no indication of when this will be merged (or when it will reach CRAN). In the meantime, how would I create a table from the query, without reading the data into R?

@Hack-R correct. Ideally, I'd like to be able to choose whether to create a permanent or temp table. — Hong Ooi
– Hong Ooi, Commented Sep 17, 2017 at 15:33
Hey, this may be a dumb question, but since the bug is just the slightly incorrect SQL syntax, could we not just edit the package source slightly to fix it? I think you just need to remove the word "TEMPORARY" (or use the code in the pull request, maybe by making a fork where you merge it and then use install_github to install your fork?). — Hack-R
– Hack-R, Commented Sep 17, 2017 at 15:43

Hong Ooi · Accepted Answer · 2017-09-17 19:06:04Z

It turns out that the PR on the dbplyr repo is glitched anyway, and will pull the entire table into memory before writing it back.

Fixing the problem requires creating a couple of MSSQL-specific methods for dbplyr generics. These are listed below. I've also posted them to the dbplyr repo so (assuming they work) they should hopefully be merged before too long.

#' @export
`db_compute.Microsoft SQL Server` <- function(con, table, sql, temporary=TRUE,
     unique_indexes=list(), indexes=list(), ...)
{
    # check that name has prefixed '##' if temporary
    if(temporary && substr(table, 1, 1) != "#")
        table <- paste0("##", table)

    if(!is.list(indexes))
        indexes <- as.list(indexes)

    if(!is.list(unique_indexes))
        unique_indexes <- as.list(unique_indexes)

    db_save_query(con, sql, table, temporary=temporary)
    db_create_indexes(con, table, unique_indexes, unique=TRUE)
    db_create_indexes(con, table, indexes, unique=FALSE)
    table
}


#' @export
`db_save_query.Microsoft SQL Server` <- function(con, sql, name, temporary=TRUE, ...)
{
    # check that name has prefixed '##' if temporary
    if(temporary && substr(name, 1, 1) != "#")
        name <- paste0("##", name)

    tt_sql <- build_sql("SELECT * INTO ", ident_q(name),
                        " FROM (", sql, ") ", ident_q(name), con=con)

    dbExecute(con, tt_sql)
    name
}

Note: may not be Bobby Tables-resistant. Testing is advised.

Collectives™ on Stack Overflow

How to create SQL Server table from dplyr pipeline

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related