Connect R to a SQL Server database engine

Question

At my work I have R-Studio (Microsoft R Open 3.2.5) installed and would like to connect to a Microsoft SQL Server database, to run scripts using the tables I have.

Is there any chance that I can connect to a SQL Server database using Pentaho and then using the object Execute R-Script to make an OLAP Cube? Do I need a package to connect SQL engine? What would be the steps to perform?

I already have the snowflake arquitectura of the data base. With the fact table and the state tables. But I do not know where to start.

score 11 · Accepted Answer · edited May 23 '17 at 12:01

11

You can connect to SQL Server directly from R using at least 4 libraries (RODBC, rsqlserver, RSQLServer, RJDBC).

As long as you have enough RAM, you can import your data into R and do your analysis there using for example amazing dplyr or data.table packages. On the other hand, you can just connect to SQL Server and send SQL queries to the server and do your data wrangling inside your database and then import results to R for further (statistical) analysis and visualization.

edited May 23 '17 at 12:01

Community

1
1

answered Sep 09 '16 at 07:01

Miha Trošt

2,002
22
25

4

As of Feb 2019, the author of RSQLServer recommends using the odbc package. Here's the archive of RSQLServer: https://github.com/imanuelcostigan/RSQLServer and here in the link to the odbc package: https://github.com/r-dbi/odbc – JD Long Feb 28 '19 at 14:57
Can you use these drivers with R Studio Free or do you need R Studio Pro to do it? – Mark Mar 04 '21 at 20:42
You can use the free version. – Miha Trošt Mar 06 '21 at 13:57

score 9 · Answer 2 · edited Feb 28 '19 at 14:59

9

Something like this should work:

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=YourDBName\\SQLEXPRESS; Database=TestDB;Uid=; Pwd=; trusted_connection=yes")
initdata <- sqlQuery(dbconnection,paste("select * from MyTable;"))
odbcClose(dbconnection)

Check out these links:

RODBC odbcDriverConnect() Connection Error

https://andersspur.wordpress.com/2013/11/26/connect-r-to-sql-server-2012-and-14/

Finally, make sure SQL Server has all proper permissions applied.

edited Feb 28 '19 at 14:59

JD Long

59,675
58
202
294

answered Oct 20 '16 at 18:22

ASH

20,759
19
87
200

shouldn't it be `odbcClose(dbconnection )` – Scott Grammilo Jun 01 '18 at 03:13

score 6 · Answer 3 · answered Dec 28 '17 at 20:41

6

Here is another way to connect to SQL Server, using Windows creds.

library("RODBC")
#library("XLConnect")

dbhandle <- odbcDriverConnect('driver={SQL Server};server=Name_Of_Server;database=Name_Of_DB;trusted_connection=true')
currTableSQL<-paste("SELECT * From Your_Table",sep="")
currTableDF<-sqlQuery(dbhandle,currTableSQL)

Uncomment the XLConnect if you want to utilize that library. I feel like a lot of times if you are using SQL Server, you are using Excel as well.

answered Dec 28 '17 at 20:41

ASH

20,759
19
87
200

Are there any security issues if we establish a oracle DB connection to R? Like data getting stolen, etc. How secure is this method? – Naive_Natural2511 May 17 '18 at 12:37
@xkcvk2511 check with your vendor. The connection is only as secure as the database. – JD Long Feb 28 '19 at 14:58

score 0 · Answer 4 · answered Sep 11 '21 at 12:25

SQL Server has had native support for calling R programs since SQL Server 2016. So besides calling SQL Server from R scripts like others here have suggested, you can put R scripts into SQL Server and get native scalable functionality, particularly for machine learning.

Connect R to a SQL Server database engine

4 Answers4

Linked