Importing an entire SQL Server table into R

user_2725751 0
Viewed 122

I have a table in SQL Server and would like to import the entire table into R to perform some work.

I'm trying to find the best path for this since the table sizes could be large (~60 million)

There is a bcp Utility command line utility in SQL Server and that's one option.

But does anyone have other suggestions?

data.table r sql
1 Answers

You can use sqlcmd for bulk queries. It is far faster than anything Microsoft's ODBC drivers (or FreeTDS) can do.

If your query is (say)

SELECT Id, Field1, Field2 FROM TableName WHERE Field1 = 'QUUX'

Then your command line would be something like:

sqlcmd -S serveraddress -d databasename \
  -U username -P '*PASSWORD*' \
  -W -s , -o out.csv \
  -Q "SET NOCOUNT ON; SELECT Id, Field1, Field2 FROM TableName WHERE Field1 = 'QUUX'"

(If your server is on a non-standard port, then use -S serveraddress,portnumber.)

I tend to not like R's system (or system2, a thin veneer), instead preferring the processx package for its safer way of handling command line arguments.

bgquery <- processx::run("sqlcmd",
    args = c("-s", "serveraddress", "-d", "databasename",
             "-U", "username", "-P", "*PASSWORD*", "-W", "-s", ",", "-o", "out.csv",
             "-Q", "SET NOCOUNT ON; SELECT Id, Field1, Field2 FROM TableName WHERE Field1 = 'QUUX'"),
    error_on_status = FALSE, echo_cmd = FALSE, echo = FALSE)

# check its stdout, it can be rather verbose
tail(bgquery$results$stdout)

# check the return value, typically "0" for command success
bgquery$results$status

# if error, look at the error output, something might be in the stdout above, too
bgquery$results$stderr

At this point, read in the file:

x <- data.table::fread("out.csv", nrows = 3, na.strings = c("NA", "", "NULL"))

Note: sqlcmd does not quote the values. So for instance,

SELECT 'hello,world' as A

using sqlcmd will return

A
hello,world

which is obviously broken. If this is a problem, you may be stuck. bcp is another option with its own problems, so it's not a perfect replacement.

user_33582720
Related