[SQL Server][Machine Learning]新功能-PREDICT述詞(原生評分)

2018-06-16

4158
0
R Language

上一篇先筆記Realtime的評分預存程序sp_rxPredict給同事看，這篇來筆記SQL 2017推出的原生(Native)述詞- PREDICT，更直覺簡單的寫法。

適用版本: SQL Server 2017
支援的模型: 使用支援 RevoScaleR 或 MicrosoftML 演算法所建立的特定模型類型

搬移模型

假設我們在測試環境已經訓練好一個以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

先準備好測試資料

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..)

原生評分(預測)

更直覺的寫法，新的述詞PREDICT，直接用在From

DECLARE @model VARBINARY(MAX) = 
 (SELECT top 1 model FROM [ML_Models] WHERE name = 'IcedRedTea Liner Model'  order by mnt_dt desc)

SELECT 
  a.*, p.*
FROM PREDICT(MODEL = @model, DATA = dbo.ML_IcedRedTeaData_Test as a)
WITH(icedTeaSales_Pred float) as p;

Select list還能有Cross apply的效果(a.*,p.*..)

小結:

預測，越來越簡單了。
如果建模預測的佈署是測試機建模、正式機只打算預測並記錄評分結果，可以不需要R的環境!
別忘了模型要以rxSerializeModel方式序列化。

目前適用的演算法:

使用的模型必須使用 RevoScaleR 套件中其中一種支援的演算法建立。

補上建模的語法

DECLARE @model VARBINARY(MAX)
EXEC sp_execute_external_script  
               @language = N'R', 
               @script = N'  
               lm <- rxLinMod(icedTeaSales ~ temperature, data = TrainingData)             
               model <- rxSerializeModel(lm, realtimeScoringOnly = FALSE)
                        ',
               @input_data_1 = N' SELECT *   FROM ML_IcedRedTeaData',
               @input_data_1_name = N'TrainingData',
               @params = N'@model varbinary(max) OUTPUT',
               @model = @model OUTPUT

INSERT [dbo].[TrainedModels](name, mnt_dt, model )
VALUES ( 'IcedRedTea Liner Model',getdate(),  @model )

2014巴西世界盃冠軍