Connect R to a SQL Server database engine

sql-server r pentaho
D.Roca picture D.Roca · Sep 9, 2016 · Viewed 37.6k times · Source

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.

Answer

Miha Trošt picture Miha Trošt · Sep 9, 2016

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.

Related questions

R: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

setwd("/mnt/mountpoint/abc/") sqlServerConnString <- "SERVER=server;DATABASE=sqldwdb;UID=xyz;PWD=abc;" sqlServerDataDS <- RxSqlServerData(sqlQuery = "SELECT * FROM xyz", connectionString = sqlServerConnString) sqlServerDataDF <- rxImport(sqlServerDataDS) This is my code. I am getting the followin error …

Read More
How to quickly export data from R to SQL Server

The standard RODBC package's sqlSave function even as a single INSERT statement (parameter fast = TRUE) is terribly slow for large amounts of data due to non-minimal loading. How would I write data to my SQL server with minimal logging so …

Read More
How do I connect to an SQL server database in R

I'm trying to connect to the SQL Sever database using R but not sure on the details for the query string. I normally use SQL server management studio on SQL Server 2008 and connnect using single sign on. I found the …

Read More