[MSR] Tutorial: Data Science with SQL Server R Services

2016. 12. 18. 15:05

/*******************************************************************************************************************
-- Title : [MSR] Tutorial: Data Science with SQL Server R Services
-- Reference : microsoft.com
-- Key word : install installation msdn powershell 파워쉘 R Studio RGUI.exe ggmap mapproj nyctaxi
뉴욕 뉴욕시 택시 taxi 데이터 과학 data science
*******************************************************************************************************************/

/*****************************

-- Scenario
*****************************/

-- URL
* https://msdn.microsoft.com/ko-kr/mt612857
* http://blog.revolutionanalytics.com/2015/12/sql-rre-tutorial.html

/*****************************
-- Prepare Configuration
*****************************/
-- Reference URL
* https://msdn.microsoft.com/ko-kr/mt612858

-- Download the Data and Script
* Open PowerShell as Administrator
* Run the script like below:

PS C:\WINDOWS\system32> Set-ExecutionPolicy Unrestricted -Scope Process -Force

PS C:\WINDOWS\system32> $source = 'https://raw.githubusercontent.com/Azure/Azure-MachineLearning-DataScience/master/Misc/RSQL/Download_Scripts_R_Walkthrough.ps1'

PS C:\WINDOWS\system32> $ps1_dest = "$pwd\Download_Scripts_R_Walkthrough.ps1"

PS C:\WINDOWS\system32> $wc = New-Object System.Net.WebClient

PS C:\WINDOWS\system32> $wc.DownloadFile($source, $ps1_dest)

PS C:\WINDOWS\system32> .\Download_Scripts_R_Walkthrough.ps1 –DestDir 'C:\tempR'

-- See what's included

-- Install Required Packages on the Client
* Open R Studio
* Open the script file "RSQL_R_Walkthrough.R"
* Highlight and execute these lines.

-- Install Required Packages on the Server
* On the SQL Server computer, open RGui.exe as administrator
* Run two scripts below:
> install.packages("ggmap", lib=grep("Program Files", .libPaths(), value=TRUE)[1])
> install.packages("mapproj", lib=grep("Program Files", .libPaths(), value=TRUE)[1])

/*****************************
-- Create DB and Tables
*****************************/
-- Create Database "TaxiNYC_Sample"
* open script "create-db-tb-upload-data.sql" on SSMS
* change "{db_name}" to "TaxiNYC_Sample"
* change "{tb_name}" to "nyctaxi_sample"
* execute changed script and save

-- Run the others Scripts
* sp02_fnCalculateDistance.sql
* sp03_fnEngineerFeatures.sql
* sp04_PersistModel.sql
* sp05_PredictTipBatchMode.sql
* sp06_PredictTipSingleMode.sql

-- See DB, Tables, SP and FN

/*****************************
-- Load "nyctaxi1pct.csv"
*****************************/
-- Load "nyctaxi1pct.csv" to table "nyctaxi_sample" on SSIS
* if have some non-matching type errors, upload temporary table first
* and insert it by script.

-- Downloaded Files on "C:\tempR"