SQL Server Machine Learning: Trabajando con modelos.
Autor: Maximiliano Accotto (MVP Data Platform).
www.triggerdb.com
TriggerDB Consulting SRL
Trabajar con modelos es lo más habitual para las tareas de Machine Learning.
En este Lab usaremos el store procedure sp_execute_external_script el cual vimos en el LAB01 para poder crear y guardar modelos.
Paso 1: Creación de tablas
En este paso lo que haremos es crear una tabla en SQL Server en la cual luego guardaremos los modelos de R o Python, como así también una segunda tabla en la cual guardaremos datos de velocidades y distancias de frenados de autos usando el Dataset Cars de R.
Para ello usaremos el siguiente código desde el SSMS
-- CREACION DE BASE DE DATOS
DROP DATABASE IF EXISTS MLTRIGGERDB
CREATE DATABASE MLTRIGGERDB
USE MLTRIGGERDB
GO
DROP TABLE IF EXISTS DBO.TRIGGERDB_MODEL
CREATE TABLE DBO.TRIGGERDB_MODEL
(
ID INT IDENTITY PRIMARY KEY,
MODELNAME VARCHAR(255),
MODEL VARBINARY(MAX)
)
DROP TABLE IF EXISTS DBO.CARSPEED
CREATE TABLE DBO.CARSPEED ([SPEED] INT NOT NULL,
[DISTANCE] INT NOT NULL);
Paso 2: Cargar la tabla CarSpeed
En este paso cargaremos la tabla CarSpeed usando un dataset de R (Cars)
INSERT INTO CARSPEED
EXEC SP_EXECUTE_EXTERNAL_SCRIPT
@LANGUAGE = N'R'
, @SCRIPT = N'CAR_SPEED <- cars;'
, @INPUT_DATA_1 = N''
, @OUTPUT_DATA_1_NAME = N'CAR_SPEED'
SELECT * FROM CARSPEED
Paso 3: Crear un modelo de regresión lineal
En este paso crearemos un Store Procedure el cual invocará a sp_execute_external_script pero para la creación de un modelo de regresión lineal, este SP que crearemos nos retornara como resultado un binario el cual contiene la representación de dicho modelo.
DROP PROCEDURE IF EXISTS generate_linear_model;
GO
CREATE PROCEDURE generate_linear_model
AS
BEGIN
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'lrmodel <- rxLinMod(formula = distance ~ speed, data = CarsData);
trained_model <- data.frame(payload = as.raw(serialize(lrmodel, connection=NULL)));'
, @input_data_1 = N'SELECT [speed], [distance] FROM CarSpeed'
, @input_data_1_name = N'CarsData'
, @output_data_1_name = N'trained_model'
WITH RESULT SETS ((model varbinary(max)));
END;
GO
Paso 4: Guardar el modelo en una tabla
En este paso guardaremos el modelo en la tabla que hemos creado en el paso 1
INSERT INTO DBO.TRIGGERDB_MODEL (model)
EXECUTE generate_linear_model
UPDATE DBO.TRIGGERDB_MODEL SET MODELNAME = 'ModeloCars'
WHERE ID = SCOPE_IDENTITY()
SELECT * FROM DBO.TRIGGERDB_MODEL
Paso 5: Usar el modelo para predicciones
En este paso usaremos al modelo creado con R y guardado en una tabla SQL Server para poder hacer predicciones, para ello crearemos una nueva tabla con velocidades para que nos determine la distancia de frenado en base al modelo.
DROP TABLE IF EXISTS [dbo].[NewSpeed]
CREATE TABLE [dbo].[NewSpeed] (
[speed] [int] NOT NULL,
[distance] [int] NULL) ON [PRIMARY]
GO
INSERT [dbo].[NewSpeed] (speed)
VALUES (40), (50), (60), (70), (80), (90), (100),(110),(133),(200)
-- predecimos con el modelo guardado anteriormente en la tabla
DECLARE @speedmodel varbinary(max) =
(SELECT model FROM [dbo].[TRIGGERDB_MODEL] WHERE MODELNAME = 'ModeloCars');
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'
current_model <- unserialize(as.raw(speedmodel));
new <- data.frame(NewData);
predicted.distance <- rxPredict(current_model, new);
str(predicted.distance);
OutputDataSet <- cbind(new, ceiling(predicted.distance));
'
, @input_data_1 = N' SELECT speed FROM [dbo].[NewSpeed] '
, @input_data_1_name = N'NewData'
, @params = N'@speedmodel varbinary(max)'
, @speedmodel = @speedmodel
WITH RESULT SETS (([new_speed] INT, [predicted_distance] INT))
Creando un SP para predicciones
Creamos un Store Procedure el cual recibe como parámetro la velocidad y en base al modelo nos predice la distancia.
Para esto usamos la posibilidad de pasarle parametros a sp_execute_external_script
-- CREAMOS UN SP QUE RECIBE UNA VELOCIDAD
DROP PROCEDURE IF EXISTS DBO.USP_PREDICT_VELOCIDAD;
CREATE PROCEDURE DBO.USP_PREDICT_VELOCIDAD
@VELOCIDAD INT
AS
DECLARE @speedmodel varbinary(max) =
(SELECT model FROM [dbo].[TRIGGERDB_MODEL] WHERE MODELNAME = 'ModeloCars');
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'
current_model <- unserialize(as.raw(speedmodel));
new <- data.frame(NewData);
predicted.distance <- rxPredict(current_model, new);
str(predicted.distance);
OutputDataSet <- cbind(new, ceiling(predicted.distance));
'
, @input_data_1 = N'select @vSpeed as speed '
, @input_data_1_name = N'NewData'
, @params = N'@speedmodel varbinary(max),@vSpeed int'
, @speedmodel = @speedmodel
,@vSpeed = @VELOCIDAD
WITH RESULT SETS (([new_speed] INT, [predicted_distance] INT))
go
-- PRUEBAS DE PREDICCION
EXECUTE DBO.USP_PREDICT_VELOCIDAD 300
EXECUTE DBO.USP_PREDICT_VELOCIDAD 77