2016-06-10

About-R_ConnectToSqlServer

This post will demonstrate how to connect R to Microsoft SQL Server, so that data can be extracted directly from a database by using SQL-statements. The approach described in this post is supported by both SQL Server 2012 and the upcoming SQL Server “14”. You can connect to SQL Server using different techniques – one of them is by using ODBC. This post will use ODBC.
The first time you need to connect to a database, you need to perform some one-time tasks, which are:
  • Create a ODBC DSN data source
  • Install necessary R-packages from CRAN
The screenshot below shows a table containing the well-known data set ‘weather.nomnial’. The table is part of a database named ‘MiningDataSets’. The goal of this tutorial is to load all this data into R.
SQLR00
Create DSN
First we need to setup a user DSN data source pointing at our SQL Server using ODBC. The data source will be called from R using the package “RODBC”
  1. Open “Administrative Tools” and “ODBC Data Sources (32 bit)”
WEKASQL02
  1. On the tab “User DSN” press “Add”
  2. Select “SQL Server” in the provider list
  3. Now give the data source a name and a description. Remember this name – you will need it later on in the process. Last provide the name of the server where the SQL Server is installed. Press “Next”.
WEKASQL03
  1. Select the way in which you will authenticate against the SQL Server. In this case we use Integrated Security. Press “Next”.
WEKASQL04
  1. You now have the possibility to select the default database for the data source. Here we choose to point at the ‘MiningDataSets’. Press “Next” and the “Finish”.
WEKASQL05
  1. On the last page remember to press the “Test Connection” button and ensure that the connection could be established.
  2. The User DSN is now created and active.
WEKASQL06

Install and load RODBC

Support for SQL Server is not possible using native R – therefore we have to install the RODBC package from CRAN.
  1. Open up R and in the console window type: install.packages(“RODBC”)
    SQLR01
  2. The RODBC packages is now downloaded and installed on your system. Next step is to load the package into R so the functions of the package can be used. In the console window type: library(“RODBC”)
    SQLR02
  3. The RODBC package is now loaded and ready
Connect with R
Now it is time to connect to the SQL Server database from R and retrieve the nominal weather dataset.
  1. When calling the database you first have to open a connection to the database. And after you have performed you operations, you have to close the connection again. This is done by using the commands odbcConnect() and odbcClose(). The name specified as the parameter is the name of the ODBC user data source.
    SQLR03
  2. First we want to read an entire table into R – this can be done by using the sqlFetch command.
    SQLR04
  3. If you want to load the data into a data.frame, this can easily be done
    SQLR05
  4. If you want to execute a specific SQL-query, this can be done using the sqlQuery-command.
    SQLR06
You can use many more SQL-statements against the database by using different RODBC-functions. You can get more help about the RODBC-package by typing RShowDoc(“RODBC”, package=”RODBC”)
SQLR07
I hope this little tutorial helped you in connecting R to SQL Server
SOURCE https://andersspur.wordpress.com/2013/11/26/connect-r-to-sql-server-2012-and-14/

2016-06-09

About-R_DataTypeConversionToSqlServer

The following table shows the changes in data types and values when data from SQL Server is used in an R script and then returned to SQL Server.
SQL Server typeR classType in RESULT SETComments
smalldatetimePOSIXctdatetimeRepresented as GMT
smallmoneynumericfloat
datetimePOSIXctdatetimeRepresented as GMT
moneynumericfloat
uniqueidentifiercharactervarchar(max)
numeric(p,s)numericfloat
decimal(p,s)numericfloat
datePOSIXctdatetimeRepresented as GMT
tinyintintegerint
bitlogicalbit
smallintintegerint
intintegerint
floatnumericfloat
realnumericfloat
bigintnumericfloat
binary(n)

n <= 8000
rawvarbinary(max)Only allowed as input parameter and output
char(n)

n <= 8000
charactervarchar(max)
varbinary(n)

n <= 8000
rawvarbinary(max)Only allowed as input parameter and output
varchar(n)

n <= 8000
charactervarchar(max)
varbinary(max)rawvarbinary(max)Only allowed as input parameter and output
https://msdn.microsoft.com/en-us/library/mt590948.aspx

2016-06-07

About-R_DataScienceR

R Data Science Tutorials


Important Questions


Common DataFrame Operations

Learning R

Caret Package in R

R Cheatsheets

Reference Slides

Using R for Multivariate Analysis

Time Series Analysis

Bayesian Inference

Machine Learning using R

Neural Networks in R

Sentiment Analysis

Imputation in R

NLP and Text Mining in R

Visualisation in R

Statistics with R

Useful R Packages

Market Basket Analysis in R


SPECIAL THANKS TO M. KARN https://github.com/ujjwalkarn/DataScienceR

2016-06-01

About-R_PrincipalComponentsRegression

Principal Components Regression, Pt.1: The Standard Method

http://www.win-vector.com/blog/2016/05/pcr_part1_xonly/

Principal Components Regression, Pt. 2: Y-Aware Methods

http://www.win-vector.com/blog/2016/05/pcr_part2_yaware/

Principal Components Regression, Pt. 3: Picking the Number of Components

http://www.win-vector.com/blog/2016/05/pcr_part3_pickk/

Principal Components Regression in R, an operational tutorial

http://blog.revolutionanalytics.com/2016/05/principal-components-tutorial.html

Principal Components Regression in R: Part 2

http://blog.revolutionanalytics.com/2016/05/principal-components-regression-part-2.html

Principal Components Regression in R: Part 3

http://blog.revolutionanalytics.com/2016/05/principal-components-regression-in-r-part-3.html

HTMLCode

HTMLCode Content