[SQL Server][R Language]資料科學用戶端(二)使用RxSqlServerData函數建立SQL Server 資料物件

除了已經在SQL Server資料庫內的資料,有時候也會有其他來源收集的整批資料,這時候就可以在R用戶端呼叫RevoScaleR函數直接在SQL Server內建立資料物件並且匯入資料。

 

這邊我們會用 Microsoft R 所含的範例資料(格式為.csv,兩個各1萬筆模擬的信用卡詐騙交易,第一個有答案作為訓練資料,第二個沒答案作為預測),但在此之前,我們先在伺服器端的SQL 2016建立一個新的資料庫!

 

1.建立資料庫

CREATE DATABASE [RDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'RDB', FILENAME = N'F:\DATA\RDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'RDB_log', FILENAME = N'F:\LOG\RDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

暫且命名為R的資料庫RDB。

 

2.回到用戶端,檢查範例資料檔的.csv是否存在

打開Visual Studio,新建一個R專案,新增一支R Script,並在程式碼內容中輸入以下指令,然後執行!

file.path(rxGetOption("sampleDataDir"), "ccFraudSmall.csv")

從R Interactive可以查看到檔案目錄位置:

用Windows檔案總管確認檔案Size。

3.再新增一支R Script,準備執行物件建立及資料匯入

#使用 RxSqlServerData 建立 SQL Server 資料物件

#連線字串
sqlConnString = "driver={SQL Server};server=資料庫IP\\MSSQLSERVER,8080;database=RDB;uid=帳號;pwd=密碼"
#Table名稱
sqlFraudTable <- "ccFraudSmall"
#控制每個批次讀取的資料列數目
sqlRowsPerRead = 5000
#準備資料表建立語法及參數
sqlFraudDS <- RxSqlServerData(connectionString = sqlConnString, table = sqlFraudTable, rowsPerRead = sqlRowsPerRead)
#取得Microsoft R 所含的範例資料(信用卡偽件交易資料)
ccFraudCsv <- file.path(rxGetOption("sampleDataDir"), "ccFraudSmall.csv")
#設定資料表欄位型別
inTextData <- RxTextData(file = ccFraudCsv, colClasses = c(
"custID" = "integer", 
"gender" = "integer", 
"state" = "integer",
"cardholder" = "integer", 
"balance" = "integer",
"numTrans" = "integer",
"numIntlTrans" = "integer", 
"creditLine" = "integer",
"fraudRisk" = "integer"))
#執行上傳
rxDataStep(inData = inTextData, outFile = sqlFraudDS, overwrite = TRUE)

Ctrl + Enter執行後,從R Interactive視窗可以發現執行成功,匯入1萬筆共花了3.33秒

4.回到資料庫端確認物件及資料是否都有正確新增

打開SSMS物件總管,多了一組ccFraudSmall資料表名稱

確認資料表內容

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [custID]
      ,[gender]
      ,[state]
      ,[cardholder]
      ,[balance]
      ,[numTrans]
      ,[numIntlTrans]
      ,[creditLine]
      ,[fraudRisk]
  FROM [RDB].[dbo].[ccFraudSmall

資料內容:

成功建立資料表結構!也成功匯入資料了!

 

5.接著再新增一支R Script,繼續匯入要評分的交易資料( Microsoft R 所含的範例資料(1萬筆模擬的信用卡詐騙交易,但沒答案等著做預測))

sqlScoreTable <- "ccFraudScoreSmall"
 sqlScoreDS <- RxSqlServerData(connectionString = sqlConnString, table = sqlScoreTable, rowsPerRead = sqlRowsPerRead)
 ccScoreCsv <- file.path(rxGetOption("sampleDataDir"), "ccFraudScoreSmall.csv")
 inTextData <- RxTextData(file = ccScoreCsv, colClasses = c(
 "custID" = "integer", 
 "gender" = "integer", 
 "state" = "integer",
 "cardholder" = "integer", 
 "balance" = "integer",
 "numTrans" = "integer",
 "numIntlTrans" = "integer", 
 "creditLine" = "integer"))
> rxDataStep(inData = inTextData, sqlScoreDS, overwrite = TRUE)

 

從R Interactive視窗可以發現執行成功,匯入1萬筆共花了2.425秒

 

模擬的信用卡詐騙交易欄位說明:

Fieldname

中文說明

Code value

Custid

持卡人id

 

Gender

持卡人性別

1:Male

2:Female

State

持卡人居住州別

("AK", "AL", "AR", "AZ", "CA", "CO", "CT", "DC", "DE",

"FL", "GA", "HI","IA", "ID", "IL", "IN", "KS", "KY", "LA", 

"MA", "MD", "ME", "MI", "MN", "MO", "MS", "MT", "NB",

"NC", "ND",   "NH", "NJ", "NM", "NV", "NY", "OH", "OK", "OR",

"PA", "RI","SC", "SD", "TN", "TX", "UT", "VA", "VT", "WA",

"WI", "WV", "WY")

 

Cardholder

正附卡註記

1: Principal

2: Secondary

Balance

帳上款項餘額

單位(元)

Numtrans

國內交易筆數

 

numIntlTrans

國際交易筆數

 

creditline

信用額度

單位(千)

fraudRisk

是否為偽冒交易

0:正常交易

1:詐欺交易

 

小結:

  • 匯入速度很快!
  • 一次就把資料表結構和資料同時建起來很方便!

 

參考:

使用 RxSqlServerData 建立 SQL Server 資料物件

Equivalent rxLogit() and glm() calls