[SQL Server][R Language]In-Database R(七)儲存機器學習模型

2016-10-06

1762
0
R Language
2016-10-12

當SQL Server 完成機器學習後，如果能把訓練後的模型直接儲存在SQL資料表，新的交易數據馬上能就近在資料庫內參照模型作預測。這篇先紀錄將訓練後的模型儲存在SQL Server 2016資料庫內，下一篇再筆記使用模型對新數據作出預測。

氣溫升高業績熱!

假使我們認為這當中有其線性關係，當累積了一段時間的氣溫與銷售量數據後，透過簡單的機器學習(ML)，我們就能用天氣預報最高氣溫來預測茶飲銷售量。

選擇機器學習種類

為了把關注點放在儲存及使用模型上，我們拿簡單線性迴歸分析(Simple Linear Regression Analysis)當範例寫筆記，迴歸分析的介紹可以參考Wiki下圖及說明。

回到正題，怎麼儲存各種訓練後的模型?

從MSDN的學習文章中又有新大陸，哥倫布可以把學習後的模型在Client端R Script進行序列化，接著使用RODBC套件(R Package)寫入SQL Server Table中。

這邊稍微調整作法，既然已經在資料庫了，我們就直接將序列化的結果透過T-SQL輸出到SQL Server 指定資料表內。

儲存模型步驟

1.建立儲存模型的SQL資料表(模型名稱、訓練時間、模型內容)

CREATE TABLE ML_Models
(
    name varchar(20) not null,
    mnt_dt datetime not null,
    model varbinary(max) not null
)

2.建立儲存訓練資料的SQL資料表及初始訓練資料(Training Data)

資料表有2個欄位: 天氣溫度與冰紅茶銷售量的統計資料。

(未來可以不斷輸入新資料，讓模型訓練的更好!)

CREATE TABLE ML_IcedRedTeaData
(
    temperature int not null,
	icedTeaSales int not null
)
insert into ML_IcedRedTeaData (temperature,icedTeaSales) 
 values(29,77),(28,62),(34,93),(31,84),(25,59),(29,64),(32,79)
      ,(31,75),(24,58),(33,91),(25,51),(31,73),(26,65),(30,84)

3.建立訓練模型的預存程序

這邊假設資料直接從Table ML_IcedRedTeaData輸入到InputDataSet DataFrame。

create proc usp_lmIcedTeaSales as
execute sp_execute_external_script
  @language = N'R'
, @script =   N' 

# (1)建立線性迴歸模型 
#Y:因變數或從屬變 icedTeaSales
#X:自變數或獨立變數 temperature
trainData <-InputDataSet
colnames(trainData) <- c("temperature", "icedTeaSales")
lmObj <- lm(formula = icedTeaSales ~ temperature, data = trainData) 

# (2)序列化線性迴歸模型 
modelbin <- serialize(lmObj, NULL)  
modelbinstr=paste(modelbin, collapse="")  

OutputDataSet <- data.frame(data=readBin(modelbin,what=raw(),n=1e6))
			   '
, @input_data_1 = N'SELECT * FROM ML_IcedRedTeaData'
WITH RESULT SETS (([model] varbinary(max) NOT NULL));

4.呼叫步驟2的預存程序usp_lmIcedTeaSales，然後將回傳的模型儲存在SQL資料表內。

(當訓練資料不斷的更新，這個步驟就可以視情況從排程中執行，訓練出來的模型也能更貼近實際情況。)

create table #temp
(
	model varbinary(max)
)
insert into #temp
exec usp_lmIcedTeaSales

insert into [dbo].ML_Models
select 'lmIcedRedTeaSales' as name,getdate() as mnt_dt,model from #temp

驗證看看，查詢儲存學習模型的資料表

select * from ML_Models

寫入成功!儲存完模型後，下一步就是使用模型，可以參考這篇：使用模型筆記in SQL Server

通常在跑模型前，可以先用圖表觀察資料:


#Y軸=icedTeaSales  X軸:temperature   
plot(icedTeaSales ~ temperature, main = "依據氣溫預測冰紅茶銷量", xlab = "當日最高氣溫(度)", ylab = "冰紅茶銷量(杯)", family = "STHeiti")
abline(reg = lmIcedTea$coefficients, col = "red", lwd = 2)

看起來有很高的線性關係!