[SQL Server][Machine Learning]Realtime評分(預測)

2018-06-16

4549
0
R Language

測試環境訓練好模型，正式上場預測前，我們會需要再建立一段R的評分程式碼，並且以字串的方式包在外部預存程序介面內(sp_execute_external_script)，透過讀取序列化後的模型在R的環境執行預測。

但這樣的方式還是多了點麻煩，第一個是程式的可讀性，第二個則是正式環境的資料庫必需依賴R的環境作運算，要解決這個問題，也許可以試試看SQL2016就推出的Realtime評分(sp_rxPredict)或是SQL 2017推出的原生(Native)評分方式，更即時更原生的方式執行。

這篇先筆記微軟以C++開發出來的Realtime評分預存程序sp_rxPredict給同事看，下一篇再筆記SQL 2017新的原生Predicate- PREDICT。

sp_execute_external_script

原先透過部預存程序介面(sp_execute_external_script)執行的範例:

DECLARE @lmModel varbinary(max) = (SELECT TOP 1 model FROM ML_Models);  
EXEC sp_execute_external_script @language = N'R',  @script = N'  
       mod <- unserialize(as.raw(model));  
	   testData <- data.frame(temperature = temp) 
	   result <- predict(mod, newdata = testData) 
       OutputDataSet <- data.frame(icedTeaSales = result)
       )',  
	   @input_data_1 = N'',
	   @params = N'@model varbinary(max),
	   @temp int',
	   @model = @lmModel,
       @temp = 30
 WITH RESULT SETS (([icedTeaSales] int NOT NULL));

找不到預存程序 'sp_rxPredict'

如果我們直接在SQL2016或SQL2017執行sp_rxPredict，我們會先收到以下的訊息:

訊息 2812，層級 16，狀態 62，行 6

找不到預存程序 'sp_rxPredict'。

我們需要

1.啟用SQL CLR

2.使用RegisterRExt.exe註冊R的擴充程序

啟用SQL CLR

sp_configure 'clr enabled', 1 
GO 
RECONFIGURE 
GO

啟用Realtime Scoring擴充預存程序(by Database)

Windows鍵+Q啟動搜尋，輸入cmd，然後按住Ctrl + Shift後再按Enter，以管理員身份打開cmd.exe

(1)切換到R Server目錄(看實際的安裝目錄)

Cd E:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\R_SERVICES\library\RevoScaleR\rxLibs\x64

(2)啟用擴充預存程序extended stored procedures

語法是: RegisterRExt.exe /installRts [/instance:name] /database:databasename

RegisterRExt.exe /installRts [/SQL2017] /database:RDB

安裝成功訊息!Command RTSInstall succeeded!

再執行一次sp_rxPredict，在資料庫RDB下，多了相關的擴充預存程序，我們認識她了！

移動模型

假設我們在測試環境已經訓練好一個預測模型(以rxLinMod訓練的冰紅茶銷售量)。下一步，我們先在預備執行評分的資料庫建好存放模型的資料表:

CREATE TABLE [dbo].[TrainedModels](
        [name] [varchar](100) NOT NULL,
        [mnt_dt] [datetime] NOT NULL,
        [model] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

直接把序列好的模型新增到模型資料表內。(必須以rxSerializeModel函數序列化)

INSERT [dbo].[TrainedModels] ([name], [mnt_dt], [model]) 
VALUES (
N'IcedRedTea Liner Model', 
CAST(N'2018-06-16T11:57:27.647' AS DateTime), 
0x626C6F62F8203A6290FA3C518F01EE185EFBC8A2F8684A27DD1F8B1CE003C44582C8C60F01000000470700000E472E9C26354AFC8A4474CFD49695C6B900000046060000000000000900000800000072784C696E4D6F640005010002000000010B000009020000000B00000028496E7465726365707429000B00000074656D7065726174757265000B01000802000000C095894237E541C0D3EDAF4C14BA0D40020200010B030005000000000604000C000000000000000B05000300000000090600180000006963656454656153616C65737E74656D706572617475726500090700000000000B080009010000000C0000006963656454656153616C6573000100789CD5584B881C4518EE796EF6319B5D134C640944BCC4C70E21881234C924B359B271932C3393E041D09AEE9ADD4A7ABA3AD535B33304629420780A1E8298802482828AA88740F462C093DE24070F5E242062404DBCA8883B5675D7DF5DDD3B9B759125D8D0D35D7F55FD8FAFFE57CFF3438661A48D4C2663A433E235B349FC4C08D2A8A4DFD8FFC3D3075EBF78A354F87CF3ECC73FDF348C6C5AD05346D618948B2DD2148F82CF22B85389251BC4120735B127DE37A93563FE33981FDE31E370CC4CECF24781C471D3C50CF116C36A35701B2126B66A185591ED735C528AA64A9D1F9FBBB0FBBC9B903E54649D59BA7888CC2F88916FD2CB77C4AF31F00E3C133B86C58E5AD7C5334E8326541D68D814F1A79E54C3FC146DD56D1C68E1F34EEDFBB527AF2585492AB0373DA4EC56574C5E0EC0591B286078BA34F95EFD8D470EBFB0EFFB9D9FED79668B3227D5EB7F2CE9DEBA1E43FA4666AEF2DDF5FC07A56D2FBD5DDB9EFBF2FE6AB3EFCAF56FE9B3DF9CDB7BEBDAEF5B0F01F4F7499B54E9B773639103647AF776C86C6F0DDEA7045C9D30D657C09691771FBEF4E157EB27A07469D78B673EBAB20E025480DE9E9C5C7A7FD6068181671CDEBD5E0233257522A51DEA6984D72A4E13281C2D5708CDB817F7FE7292AFC71148DF9F4C28B65557ECECCA5E6F28A5E53DA4144F2584C78D5C31AD493532EA995D05277DAE4F4CAE4DB2442C07886501310BBB2710835D03C4B1F421B26D6D98A78D8687B966C8FFD980356A10939E01864DE4BAD882668032324F1C64AB71B68E3C0897ACF2A2BB6A2CD1DB167955AC688ECB22CD90E335286B1E74DA8409493DCD0557B963CC0659A78A1AB8C6BA6242B6407F42C4411351C16D5A3585E11528DABB8BBB8A3B137CB2B8E30A3D8CBF12F4BC476CECF04460484DF2DAAAD41923A83C3A710367DD32E2E6423037D44B360F9831CA929B1A2DC7E4843AC1A6D886140E14F4C58B89B19E92DA171822CB1E6BB93C90B0B11771F4376A8CE4FC48AF8F0982499B7824C832CB263DCE5AA6726028BB303971DCC36C7BA803B6B6E30E365BD2B2621207D3465E329C060578930090EE0069E35F3BCA7FBAE1B80D6379AE5C6DACEFD5DD26A862770CB8942CBF6B371231682C7374BA588476B3102DD80C1BC2AED9421C151B4C2C35B45AF44524B69F7AF77AF7F7EF39FDE65BAF4CDE1E0FC7F9F3975FBBFAF8E5C4DA505EE94209E4E9DA65F6CF9413A4E132158988134EDAB88FB6C9F14A8794A8B84A0B23B66F050B63C04F42729D16E9A965234810D3221100C40DF15EED7A228302654A805EA52DD1672ACA680537691B1F219E479C7948BA1B18467A06DFD4088484F9F00872011635A5ADCE60A7AD5E0B1C764C8B84B18CA86D9A88118FD20A5DAC621B07492658B27101797DC8237D680347F1A2C67C28541C28B93926A23E02C1A58CCF313ACFB0E7857C7D6A8D340532E0DE126DF1B187994E382ECA0F10F2D3C8B2CA73606ADDA6E6296F0EB38A4014943369BB4C71431BCA7381132CB7EA703A1BE5FB1CC316F18D0B2B681BB11AB5C3C32A53C78AC68316A3EE34611E98976F62BE40C3FAD8449D2AC72E302B08FF70CB8C881418E1372479CC519B985D451993DBC8FC2C6E63BB46F75BC06E5CA013D1A7C4360095E106E1C7DA98551731066AC1A71E44E682D401A43565FDA96894C229B1458E254EE19979B23E4295390578711C5AFA6075812E4E618E84E35B335CB60D1A6CA37256A306592DBF42821A508E9D48702345BF17A04E33A8B77FAB980DABDF5903BEF8FA7763B13F0296B557F12ACB3AB3C439228E2EAE43A49A5CBE4595C4ADEAF9906A52E28D834CB7BA56222288231C7B2AA0EB4D7190920EFF71E0D3571F2BAEDF67522277C2D7CB0A4D5EAE8DEC161C7ECE69352D889F9C851D31D059FFF4C9DD9BB71EF83A64FD445C94115DFA8967650F96103F017899C2111BC424E2D4C19FC644B220560BD945EF740BB1B0651D33453C12BF87107AD6C3D490154908BC56F4C444F4A51045A3927DD1E35631682634A1455ED44D0F686E8C56E0942FD322D7156B58183A64BE0919663CD45B2C20C8098BC120533CC25C2124E982D221E8035D55E5D5B063CB0410D60F0BB775C6194420F9E75D246ABE97F464859008704863C30D8F8B40F53831C35485AC93C5A48E83CE0981A575AC1E7E3138AA9A45241FDE5954C77695776D9C0C27503E0881A4071A51382D4B125A841A4BFF00E4FE809E)
GO

關閉SQL Server Launchpad

為了確定Realtime評分預測(sp_rxPredict)不需要在R的環境下執行，我們打開SQL Server組態管理員，關閉SQL Server launchpad

從 SQL Server 資料庫中執行 R 指令碼架構圖

Realtime評分(預測)

(1)先準備好測試資料

Drop Table if Exists ML_IcedRedTeaData_Test 
 CREATE TABLE ML_IcedRedTeaData_Test
(
    temperature int not null
)
insert into ML_IcedRedTeaData_Test (temperature) 
 values(29),(28),(34),(31),(25),(29),(32),(31),(24),(33),(25),(31),(26),(30)

準備要預測冰紅茶銷量的測試資料(溫度s)

(2)取得模型後，就可以直接使用sp_rxPredict擴充預存程序執行預測

DECLARE @modelInRaw VARBINARY(MAX) = 
 (SELECT top 1 model FROM TrainedModels WHERE name = 'IcedRedTea Liner Model'  order by mnt_dt desc)
EXEC sp_rxPredict  @model = @modelInRaw,@inputData = 'SELECT * FROM ML_IcedRedTeaData_Test'

回傳了預測結果集!

小結

如果建模預測的佈署是測試機建模、正式機只打算預測並記錄評分結果，可以不需要R的環境!
使用sp_rxPredict可以少寫一隻預存程序，但要使用預測的每個使用者資料庫都要這樣啟用擴充程序有點麻煩。
限制特定RevoScaleR當中的演算法才行，像是rxLinMod 、rxLogit 、rxBTrees 、rxDtree 、rxdForest ，不過rxGlm就不支援了。
SQL 2017的原生評分更威，請看下篇。

2010年南非世界盃冠軍