SQL Machine Learning: Modelos

by Maxi Accotto | Dec 24, 2018 | Machine Learning

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