Reading in a dataset from a SQL server into R

Setting up R to connect to SQL Server

Querying a database from a server running R requires three things:

  1. Network security that allows you to communicate between the machines;
  2. Drivers installed on the R server; and
  3. Configurations that allow you to connect from R.

https://support.rstudio.com/hc/en-us/articles/214510788-Setting-up-R-to-connect-to-SQL-Server-

Connecting to SQL Server from R

Run the following command:

install.packages("RODBC")
library(RODBC)

dbhandle<-odbcDriverConnect('driver={SQL Server};
                            server=my_server;
                            database=my_db;
                            trusted_connection=true') # create connection to SQL sever and db

# run SQL query to extract data
my_results<-sqlQuery(dbhandle, # use the connection we just created
         'SELECT * FROM my_table WHERE ID<100') # SQL query to run



# create data to import into SQL server
data=data.frame(IDs=1:1000,
                values=rnorm(n = 1000),
                names=rep('names',1000)) 


# save new table
sqlSave(channel=dbhandle, # connection
        dat=data, # data to import
        tablename="table_from_R") # name of the data

# run SQL query to modify data
sqlQuery(dbhandle, # use connection
         "alter table_from_R  alter column names varchar(50) collate Latin1_General_100_CS_AS")  # use SQL query to run


# drop table
sqlDrop(dbhandle, # use connection
        "table_from_R") # table to drop

Using your Known SQL commands in R - a useful tool

While you are learning R, you may wish to use to check your R command matches up with SQL Install package “sqldf” with the following command

install.packages("sqldf")

Load the package

library(sqldf)

Then the equivalent command to extract the column ‘Type’ from the data frame myC02:

03 <- sqldf("select Type from myCO2")
r03 <- myCO2[ , "Type"]

More details can be found at http://www.burns-stat.com/translating-r-sql-basics/.