How to apply dtplyr with SQL Server database

Question

I am trying to apply dtplyr to a SQL Server database.

I succeeded in applying dplyr as shown below, but I don't know how to apply dtplyr

How can I do this?

library(odbc)
library(DBI)
library(tidyverse)
library(dtplyr)
library(dbplyr)

con <- DBI::dbConnect(odbc::odbc(),
                      Driver    = "SQL Server", 
                      Server    = "address",
                      Database  = "dbname",
                      UID       = "ID",
                      PWD       = "password")

dplyr::tbl(con, dbplyr::in_schema("dbo", "table1"))

The example you provide seems related to [dbplyr](https://dbplyr.tidyverse.org/). What do you need `dtplyr` for? — Waldi, Feb 13 '21 at 17:07
`dtplyr` is for data processing using `data.table`, it doesn't handle the SQL Server connection. — Waldi, Feb 13 '21 at 17:15

score 1 · Accepted Answer · answered Feb 14 '21 at 00:48

The comments by @Waldi capture the essence of the matter. You can not use dtplyr with SQL Server as it only translates commands to data.table objects.

The official dtplyr documentation states:

The goal of dtplyr is to allow you to write dplyr code that is automatically translated to the equivalent ... data.table code

The official dbplyr documentation states:

It allows you to use remote database tables as if they are in-memory data frames by automatically converting dplyr code into SQL

Both dbplyr and dtplyr translate dplyr commands. Which one you use depends on whether you are working with data.table type objects (in R memory) or remote SQL databases (whichever flavor of SQL you prefer).

How to apply dtplyr with SQL Server database

1 Answers1