Compartir a través de

Compilación de un modelo de R y almacenamiento en SQL Server (tutorial)

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores

En este paso, aprenderá a generar un modelo de Machine Learning y a guardarlo en SQL Server. Cuando un modelo se guarda, puede llamarlo directamente con código de Transact-SQL mediante el procedimiento almacenado del sistema, sp_execute_external_script o la función PREDICT (T-SQL).

Requisitos previos

En este paso se da por supuesto que hay una sesión de R en curso basada en los pasos anteriores de este tutorial. Usaremos las cadenas de conexión y los objetos de origen de datos creados en esos pasos. También emplearemos las siguientes herramientas y paquetes para ejecutar el script.

  • Rgui.exe para ejecutar comandos de R
  • Management Studio para ejecutar T-SQL
  • Paquete ROCR
  • Paquete RODBC

Creación de un procedimiento almacenado para guardar modelos

En este paso se usa un procedimiento almacenado para guardar un modelo entrenado en SQL Server. La tarea será más sencilla si se crea un procedimiento almacenado para realizar esta operación.

Ejecute el siguiente código de T-SQL en una ventana de consulta de Management Studio para crear el procedimiento almacenado.

USE [NYCTaxi_Sample]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PersistModel')
  DROP PROCEDURE PersistModel
GO

CREATE PROCEDURE [dbo].[PersistModel] @m nvarchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	insert into nyc_taxi_models (model) values (convert(varbinary(max),@m,2))
END
GO

Nota

Si recibe un error, asegúrese de que su inicio de sesión tiene permiso para crear objetos. Para conceder permisos explícitos para crear objetos, ejecute una instrucción T-SQL como esta: exec sp_addrolemember 'db_owner', '<user_name>'.

Creación de un modelo de clasificación mediante rxLogit

El modelo es un clasificador binario que predice si es probable que el taxista obtenga una propina en un trayecto determinado. Usará el origen de datos que creó en la lección anterior para entrenar el clasificador de propinas mediante la regresión logística.

  1. Llame a la función rxLogit , incluida en el paquete RevoScaleR , para crear un modelo de regresión logística.

    system.time(logitObj <- rxLogit(tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance, data = featureDataSource));

    La llamada que compila el modelo está incluida en la función system.time. Esto le permite disponer del tiempo necesario para compilar el modelo.

  2. Después de generar el modelo, se puede inspeccionar con la función summary y ver los coeficientes.

    summary(logitObj);

    Resultados

     *Logistic Regression Results for: tipped ~ passenger_count + trip_distance + trip_time_in_secs +*
 direct_distance* 
 *Data: featureDataSource (RxSqlServerData Data Source)*
 *Dependent variable(s): tipped*
 *Total independent variables: 5*
 *Number of valid observations: 17068*
 *Number of missing observations: 0*
 *-2\*LogLikelihood: 23540.0602 (Residual deviance on 17063 degrees of freedom)*
 *Coefficients:*
 *Estimate Std. Error z value Pr(>|z|)*
 *(Intercept)       -2.509e-03  3.223e-02  -0.078  0.93793*
 *passenger_count   -5.753e-02  1.088e-02  -5.289 1.23e-07 \*\*\**
 *trip_distance     -3.896e-02  1.466e-02  -2.658  0.00786 \*\**
 *trip_time_in_secs  2.115e-04  4.336e-05   4.878 1.07e-06 \*\*\**
 *direct_distance    6.156e-02  2.076e-02   2.966  0.00302 \*\**
 *---*
 *Signif. codes:  0 '\*\*\*' 0.001 '\*\*' 0.01 '\*' 0.05 '.' 0.1 ' ' 1*
 *Condition number of final variance-covariance matrix: 48.3933*
 *Number of iterations: 4*

Usar el modelo de regresión logística para la puntuación

Ahora que ha generado el modelo, puede usarlo para predecir si es probable que el taxista obtenga una propina en un viaje determinado.

  1. Primero, use la función RxSqlServerData para definir un objeto de origen de datos donde almacenar el resultado de la puntuación.

    scoredOutput <- RxSqlServerData(
  connectionString = connStr,
  table = "taxiScoreOutput"  )

    • Para simplificar este ejemplo, la entrada en el modelo de regresión logística es el mismo origen de datos de características (sql_feature_ds) que usó para entrenar el modelo. La mayoría de las veces tendrá datos nuevos con los que puntuar, o podría haber reservado algunos datos para realizar las pruebas y no el entrenamiento.

    • Los resultados de predicción se guardarán en la tabla taxiscoreOutput. Tenga en cuenta que el esquema de esta tabla no está definido cuando se crea mediante rxSqlServerData. Dicho esquema se obtiene de la salida de rxPredict.

    • Para crear la tabla que almacena los valores predichos, el inicio de sesión de SQL que ejecuta la función de datos rxSqlServer debe tener privilegios DDL en la base de datos. Si el inicio de sesión no puede crear tablas, se producirá un error en la instrucción.

  2. Llame a la función rxPredict para generar resultados.

    rxPredict(modelObject = logitObj,
    data = featureDataSource,
    outData = scoredOutput,
    predVarNames = "Score",
    type = "response",
    writeModelVars = TRUE, overwrite = TRUE)

    Si la instrucción se ejecuta correctamente, debe tardar algo de tiempo en ejecutarse. Cuando finalice, puede abrir SQL Server Management Studio y comprobar que la tabla se ha creado y que contiene la columna de puntuación (Score) y otra salida prevista.

Trazado de la precisión del modelo

Para hacerse una idea de la precisión del modelo, puede usar la función rxRoc para trazar la curva de funcionamiento del receptor. Dado que rxRoc es una de las nuevas funciones proporcionadas por el paquete RevoScaleR que es compatible con contextos de proceso remoto, tiene dos opciones:

  • Puede usar la función rxRoc para ejecutar el trazado en el contexto del equipo remoto y, después, devolver el trazado al cliente local.

  • También puede importar los datos en el equipo cliente de R y usar otras funciones de trazado de R para crear el gráfico de rendimiento.

En esta sección, experimentará con ambas técnicas.

Ejecutar un trazado en el contexto de cálculo remoto (SQL Server)

  1. Llame a la función rxRoc y facilite los datos definidos anteriormente como entrada.

    scoredOutput = rxImport(scoredOutput);
rxRoc(actualVarName= "tipped", predVarNames = "Score", scoredOutput);

    Esta llamada devuelve los valores usados para calcular el gráfico de ROC. La columna de etiqueta es tipped, que contiene los resultados reales que está intentando predecir, mientras que la columna Score contiene la predicción.

  2. Para trazar el gráfico de facto, puede guardar el objeto ROC y, después, dibujarlo con la función de trazado. El gráfico se crea en el contexto de cálculo remoto y luego se devuelve al entorno de R.

    scoredOutput = rxImport(scoredOutput);
rocObjectOut <- rxRoc(actualVarName= "tipped", predVarNames = "Score", scoredOutput);
plot(rocObjectOut);

    Para verlo, abra el dispositivo de gráficos de R o haga clic en la ventana Trazar en RStudio.

    Trazado ROC para el modelo

Crear los trazados en el contexto de cálculo local con datos de SQL Server

Para comprobar que el contexto de cálculo es local, ejecute rxGetComputeContext() en el símbolo del sistema. El valor devuelto debe ser "RxLocalSeq Compute Context".

  1. En el contexto de cálculo local, el proceso es prácticamente el mismo. Para incluir los datos especificados del entorno local de R se usa la función rxImport.

    scoredOutput = rxImport(scoredOutput)

  2. Usando los datos de la memoria local, hay que cargar el paquete ROCR y usar la función de predicción de ese paquete para crear algunas predicciones nuevas.

    library('ROCR');
pred <- prediction(scoredOutput$Score, scoredOutput$tipped);

  3. Genere un trazado local según los valores almacenados en la variable de salida pred.

    acc.perf = performance(pred, measure = 'acc');
plot(acc.perf);
ind = which.max( slot(acc.perf, 'y.values')[[1]] );
acc = slot(acc.perf, 'y.values')[[1]][ind];
cutoff = slot(acc.perf, 'x.values')[[1]][ind];

    Trazado del rendimiento del modelo mediante R

Nota

Sus gráficos pueden tener un aspecto diferente al de estos, dependiendo de cuántos puntos de datos haya usado.

Implementación del modelo

Después de haber creado un modelo y de asegurarse de que funciona bien, lo más probable es que quiera implementarlo en un sitio en el que los usuarios o las personas de la organización puedan usarlo o, quizás, volver a entrenarlo y recalibrarlo de forma periódica. Este proceso se denomina a veces operacionalizar un modelo. En SQL Server, la operacionalización se logra al insertar código de R en un procedimiento almacenado. Como el código reside en el procedimiento, se puede llamar desde cualquier aplicación que pueda conectarse a SQL Server.

Antes de llamar al modelo desde una aplicación externa, debe guardarlo en la base de datos que se usa en producción. Los modelos entrenados se almacenan en formato binario, en una sola columna de tipo varbinary(max) .

Un flujo de trabajo de implementación típico consta de los siguientes pasos:

  1. Serializar el modelo en una cadena hexadecimal
  2. Transmitir el objeto serializado a la base de datos
  3. Guardar el modelo en una columna varbinary(max)

En esta sección, aprenderá a usar un procedimiento almacenado para conservar el modelo y hacer que esté disponible para las predicciones. El procedimiento almacenado que se usa en esta sección es PersistModel. La definición de PersistModel se encuentra en los requisitos previos.

  1. Cambie al entorno local de R si aún no lo está usando, serialice el modelo y guárdelo en una variable.

    rxSetComputeContext("local");
modelbin <- serialize(logitObj, NULL);
modelbinstr=paste(modelbin, collapse="");

  2. Abra una conexión ODBC con RODBC. La llamada a RODBC se puede omitir si ya tiene el paquete cargado.

    library(RODBC);
conn <- odbcDriverConnect(connStr);

  3. Llame al procedimiento almacenado PersistModel en SQL Server para transmitir el objeto serializado a la base de datos y almacenar la representación binaria del modelo en una columna.

    q <- paste("EXEC PersistModel @m='", modelbinstr,"'", sep="");
sqlQuery (conn, q);

  4. Use Management Studio para confirmar que el modelo existe. En el Explorador de objetos, haga clic con el botón derecho en la tabla nyc_taxi_models y haga clic en Seleccionar las primeras 1000 filas. En Resultados, debería ver una representación binaria en la columna models.

Para guardar un modelo en una tabla, solo hace falta una instrucción INSERT. Con todo, esto a menudo resulta más fácil cuando se incluyen en un procedimiento almacenado, como PersistModel.

Pasos siguientes

En la siguiente (y última) lección aprenderá a realizar la puntuación en el modelo guardado con Transact-SQL.

Implementación del modelo de R y uso en SQL