Modificación de código de R/Python para que se ejecute en instancias de SQL Server (en la base de datos)

Artigo
05/30/2023

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

Este artículo proporciona una guía en detalle sobre cómo modificar el código de R o Python para que se ejecute como procedimiento almacenado de SQL Server con el fin de mejorar el rendimiento al acceder a los datos de SQL.

Cuando se traslada el código de R/Python desde un IDE local u otro entorno a SQL Server, el código funciona generalmente sin más modificaciones. Esto es cierto especialmente para el código simple, como una función que toma algunas entradas y devuelve un valor. También es más fácil portar soluciones que usen los paquetes RevoScaleR/revoscalepy, que admiten la ejecución en distintos contextos de ejecución con el mínimo de cambios. Tenga en cuenta que MicrosoftML se aplica a SQL Server 2016 (13.x), SQL Server 2017 (14.x) y SQL Server 2019 (15.x) y no aparece en SQL Server 2022 (16.x).

Aún así, es posible que tenga que realizar cambios importantes en el código si se da cualquiera de estas condiciones:

Se usan bibliotecas que tienen acceso a la red o que no se pueden instalar en SQL Server.
El código realiza llamadas independientes a orígenes de datos fuera de SQL Server, como hojas de cálculo de Excel, archivos en recursos compartidos y otras bases de datos.
Quiere parametrizar el procedimiento almacenado y ejecutar el código en el parámetro @script de sp_execute_external_script.
La solución original incluye varios pasos que podrían ser más eficaces en un entorno de producción si se ejecutan de forma independiente, como la preparación de datos o la ingeniería de características, frente al entrenamiento del modelo, la puntuación o la creación de informes.
Puede optimizar el rendimiento si cambia las bibliotecas, usa la ejecución en paralelo o descarga algún procesamiento para SQL Server.

Paso 1. Planear requisitos y recursos

Paquetes

Determine qué paquetes se necesitan y asegúrese de que funcionan en SQL Server.
Instale los paquetes con antelación en la biblioteca de paquetes predeterminada que usa Machine Learning Services. No se admiten las bibliotecas de usuarios.

Orígenes de datos

Si tiene previsto insertar el código en sp_execute_external_script, identifique los orígenes de datos principales y secundarios.
- Los orígenes de datos principales son grandes conjuntos de datos, como los datos de entrenamiento del modelo o los datos de entrada para las predicciones. Planee la asignación del conjunto de datos más grande al parámetro de entrada de sp_execute_external_script.
- Los orígenes de datos secundarios suelen ser conjuntos de datos más pequeños, como listas de factores o variables de agrupación adicionales.
Actualmente, sp_execute_external_script solo admite un único conjunto de datos como entrada para el procedimiento almacenado, pero puede agregar varias entradas escalares o binarias.

Las llamadas al procedimiento almacenado precedidas por EXECUTE no se pueden usar como entrada para sp_execute_external_script. Puede usar consultas, vistas o cualquier otra instrucción SELECT válida.
Determine las salidas que necesita. Si ejecuta el código mediante sp_execute_external_script, el procedimiento almacenado puede generar una sola trama de datos como resultado. Pero también puede generar varias salidas escalares, como trazados y modelos en formato binario, así como otros valores escalares derivados del código o de los parámetros SQL.

Tipos de datos

Para obtener una visión detallada de las asignaciones de tipos de datos entre R/Python y SQL Server, consulte los siguientes artículos:

Eche un vistazo a los tipos de datos que se usan en el código de R/Python y haga lo siguiente:

Haga una lista de comprobación de posibles problemas de tipos de datos.

SQL Server Machine Learning Services admite todos los tipos de datos de R/Python. Sin embargo, SQL Server admite una mayor variedad de tipos de datos que R o Python. Por tanto, al mover los datos de SQL Server hacia y desde el código se realizan algunas conversiones implícitas de tipos de datos. También podría necesitar convertir explícitamente algunos datos.

Se admiten valores NULL. Aún así, R usa la construcción de datos de na para representar los valores que faltan, que son similares a los valores NULL.
Le recomendamos que elimine la dependencia de los datos que R no puede usar; por ejemplo, R no puede usar los tipos de datos rowid y GUID de SQL Server y eso producirá errores.

Paso 2. Convertir o volver a empaquetar código

La cantidad de código que cambie dependerá de si quiere enviar el código desde un cliente remoto para que se ejecute en el contexto de proceso de SQL Server o si pretende implementar el código como parte de un procedimiento almacenado. Este último puede proporcionar un mejor rendimiento y seguridad de los datos, aunque exige algunos requisitos adicionales.

Defina los datos de entrada principales como una consulta de SQL siempre que sea posible para evitar el movimiento de datos.

Al ejecutar el código en un procedimiento almacenado, puede acceder directamente a varias entradas escalares. En el caso de parámetros que quiera usar en la salida, agregue la palabra clave OUTPUT.

Por ejemplo, la siguiente entrada escalar @model_name contiene el nombre del modelo, que después también lo modifica el script de R y se representa en su propia columna en los resultados:

-- declare a local scalar variable which will be passed into the R script
DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';

-- The below defines an OUTPUT variable in the scope of the R script, called model_name
-- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
-- of these parameters is very important. Mandatory parameters to sp_execute_external_script
-- must appear first, followed by the additional parameter definitions like @params, etc.
EXECUTE sp_execute_external_script @language = N'R', @script = N'
  model_name <- "Model name from R script"
  OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
  , @input_data_1 = N'SELECT 1 AS c1'
  , @params = N'@model_name nvarchar(50) OUTPUT'
  , @model_name = @local_model_name OUTPUT;

-- optionally, examine the new value for the local variable:
SELECT @local_model_name;

Cualquier variable que se pase como parámetro del procedimiento almacenado sp_execute_external_script debe asignarse a las variables en el código. De forma predeterminada, las variables se asignan por nombre. Todas las columnas del conjunto de datos de entrada deben asignarse también a las variables del script.

Por ejemplo, supongamos que el script de R contiene una fórmula como esta:
```
formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
```
Se produce un error si el conjunto de datos de entrada no contiene columnas con los nombres coincidentes ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour y DayOfWeek.
En algunos casos, es necesario definir un esquema de salida con antelación para los resultados.

Por ejemplo, para insertar los datos en una tabla, debe usar la cláusula WITH RESULT SET para especificar el esquema.

También se necesita el esquema de salida si el script usa el argumento @parallel=1. El motivo es que SQL Server puede crear varios procesos para ejecutar la consulta en paralelo, con los resultados recopilados al final. Por tanto, el esquema de salida debe prepararse antes de que se puedan crear los procesos en paralelo.

En otros casos, se puede omitir el esquema de resultados mediante la opción WITH RESULT SETS UNDEFINED. Esta instrucción devuelve el conjunto de datos a partir del script sin asignar nombres a las columnas ni especificar los tipos de datos de SQL.
Le recomendamos que genere datos de seguimiento o de control de tiempo mediante T-SQL en lugar de R/Python.

Por ejemplo, para pasar la hora del sistema u otra información usada para la auditoría y el almacenamiento, puede agregar una llamada de T-SQL que se pasa a los resultados, en lugar de generar datos similares en el script.

Mejorar el rendimiento y la seguridad

Evite escribir predicciones o resultados intermedios en un archivo. Escriba las predicciones en una tabla para evitar el movimiento de datos.

Ejecute todas las consultas con antelación y revise los planes de consulta de SQL Server para identificar las tareas que se pueden realizar en paralelo.

Si la consulta de entrada se puede paralelizar, establezca @parallel=1 como parte de los argumentos de sp_execute_external_script.

Por lo general, el procesamiento en paralelo con este indicador es posible siempre que SQL Server pueda trabajar con tablas con particiones o distribuir una consulta entre varios procesos y agregar los resultados al final. Normalmente, el procesamiento en paralelo con este indicador no es posible si entrena modelos mediante algoritmos que requieren que se lean todos los datos o si necesita crear agregados.
Revise el código para determinar si hay pasos que se pueden realizar independientemente o de una manera más eficiente, mediante una llamada de procedimiento almacenado independiente. Por ejemplo, conseguirá un mejor rendimiento si realiza ingeniería de características o extracción de características por separado y guarda los valores en una tabla.
Busque la manera de usar T-SQL en lugar de código de R/Python para computaciones basadas en conjuntos.

Por ejemplo, esta solución de R muestra cómo las funciones de T-SQL definidas por el usuario y R pueden realizar la misma tarea de ingeniería de características: Tutorial integral de ciencia de datos.
Póngase en contacto con un desarrollador de bases de datos para averiguar cómo mejorar el rendimiento mediante el uso de características de SQL Server, como tablas optimizadas para memoria o Resource Governor (si tiene Enterprise Edition).
Si usa R, reemplace las funciones de R convencionales por funciones de RevoScaleR que admitan la ejecución distribuida si es posible. Para obtener más información, consulte Comparación de funciones de Base R y RevoScaleR.

Paso 3. Preparar la implementación

Notifique al administrador para que los paquetes se puedan instalar y probar antes de implementar el código.

En un entorno de desarrollo, podría ser conveniente instalar paquetes como parte del código, pero esto es una práctica incorrecta en un entorno de producción.

No se admiten las bibliotecas de usuario, independientemente de si está usando un procedimiento almacenado o si ejecuta código de R/Python en el contexto de proceso de SQL Server.

Empaquetado de código de R/Python en un procedimiento almacenado

Cree una función definida por el usuario de T-SQL e inserte el código mediante la instrucción sp-execute-external-script.
Si tiene un código R complejo, use el paquete de R sqlrutils para convertir el código. Este paquete está diseñado para ayudar a los usuarios de R con experiencia a escribir código de procedimiento almacenado que sea adecuado. Vuelva a escribir el código de R como una sola función con entradas y salidas claramente definidas y, a continuación, use el paquete sqlrutils para generar la entrada y las salidas en el formato correcto. El paquete sqlrutils genera automáticamente el código del procedimiento almacenado completo y también puede registrar el procedimiento almacenado en la base de datos.

Para más información y ejemplos, vea sqlrutils (SQL).

Integración con otros flujos de trabajo

Aproveche las herramientas de T-SQL y los procesos de ETL. Realice ingeniería de características, extracción de características y limpieza de datos con antelación como parte de los flujos de trabajo de datos.

Cuando se trabaja en un entorno de desarrollo dedicado, se pueden extraer datos en el equipo, analizar los datos de forma iterativa y, luego, escribir o mostrar los resultados. En cambio, cuando se migra el código independiente a SQL Server, gran parte de este proceso se puede simplificar o delegar a otras herramientas de SQL Server.
Use estrategias de visualización asincrónicas que sean seguras.

Los usuarios de SQL Server a menudo no pueden acceder a los archivos del servidor y las herramientas de cliente de SQL normalmente no admiten los dispositivos de gráficos de R/Python. Si genera trazados u otros gráficos como parte de la solución, considere la opción de exportar los trazados como datos binarios y guardarlos en una tabla o escribirlos.
Ajuste las funciones de predicción y puntuación en procedimientos almacenados para el acceso directo a las aplicaciones.

Pasos siguientes

Para ver ejemplos de cómo se puede implementar soluciones de R y Python en SQL Server, consulte los siguientes tutoriales:

Tutoriales de R

Desarrollo de SQL para científicos de datos de R

Compartir por