测试环境训练好模型,正式上场预测前,我们会需要再建立一段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
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'
回传了预测结果集!